Статьи

Превращение Excel в путь CarlosAg

Апрель 14, 2019

Один вопрос, который мы часто видим на форумах Sitepoint ASP.NET : «Эй, мой начальник хочет, чтобы я сделал электронную таблицу Excel из этого набора данных. Пожалуйста помоги. Ты моя последняя надежда, прежде чем меня уволят!

Теперь есть несколько способов создать таблицу Excel. Очевидный способ использовать COM-взаимодействие и собственную объектную модель Excel для выполнения этой работы. Это, возможно, худший план. Во-первых, для работы требуется одна установка Excel на веб-сервере. Во-вторых, Excel предназначен для работы в пользовательском режиме и будет открывать модальные диалоговые окна, эффективно замораживая ваши приложения. Я буду игнорировать «забавные» задачи, связанные с COM-взаимодействием, и чистую радость от работы с объектной моделью Excel. Не обращайте внимания на проблемы масштабируемости и тому подобное.

Второй набор очевидных вариантов может стоить немного дороже — различные коммерческие библиотеки поколения Excel. Другой вариант в этом ключе — и, возможно, самый полезный — Sql Server 2005 Reporting Services . Но только в том случае, если вы уже используете его для других задач по составлению отчетов, его реализация только для экспорта в Excel является пустой тратой. Другой старый резерв — создать HTML-таблицу и сказать браузеру: «Да, к счастью, это превосходно». Но это в лучшем случае ограниченное решение. Что делать разработчику?

Введите CarlosAg

Но есть еще один вариант. Современные версии [2002 / XP или новее] Excel поддерживают спецификацию Xml Spreasheet . И если .NET хорош только для одной вещи, это будет XML. Войдите в библиотеку CarlosAg ExcelXmlWriter . Я использовал это в нескольких проектах, и я должен сказать, что это очень гладко. Это единственная бесплатная библиотека, которую я видел, которая позволяет разработчику создавать практически полнофункциональную электронную таблицу — включая формулы, ячейки с большим количеством элементов и сводные таблицы — без пересечения моста COM или значительных денежных средств.

Теперь не без его проблем. Наиболее важным является то, что разработчик должен быть очень осторожным, чтобы правильно форматировать данные, потому что нет фильтрации типов на уровне библиотеки. Как и в любой операции экспорта, необходимо тщательно проверить вывод, чтобы убедиться, что он может быть обработан. На момент написания этой статьи также не существует собственного двоичного файла .NET 2.0. Но я еще не столкнулся с ошибкой остановки показа. Это определенно солидная библиотека и, безусловно, довольно приятная по цене — бесплатная.

А теперь мы подошли к хорошей части — к образцам кода. Допустим, вам нужно, о, просто выбросить набор данных в писателя Карлоса для потомков.

Ну, во-первых, вам нужен класс для работы с набором данных в целом: 

using System.Data; using CarlosAg.ExcelXmlWriter; namespace WWB.ExcelDatasetWriter { /// <summary> /// Creates an excel-friendly Xml Document from a dataset using the CarlosAg.ExcelXmlWriter library. /// </summary> public class ExcelDatasetWriter { public ExcelDatasetWriter() {} public Workbook CreateWorkbook(DataSet data) { //ensure valid data if (data==null) { throw new ArgumentNullException("data", "Data cannot be null."); } ensureTables(data); //Variable declarations //our workbook Workbook wb=new Workbook(); //Our worksheet container Worksheet ws; //Our DataTableWriter ExcelDataTableWriter edtw=new ExcelDataTableWriter(); //Our sheet name string wsName; //Our counter int tCnt=0; //Loop through datatables and create worksheets foreach (DataTable dt in data.Tables) { //set the name of the worksheet if (dt.TableName!=null && dt.TableName.Length>0 && dt.TableName!="Table") { wsName=dt.TableName; } else { //Go to generic Sheet1 . . . SheetN wsName="Sheet" + (tCnt+1).ToString(); } //Instantiate the worksheet ws=wb.Worksheets.Add(wsName); //Populate the worksheet edtw.PopulateWorksheet(dt, ws); tCnt++; } return wb; } private void ensureTables(DataSet data) { if (data.Tables.Count==0) { throw new ArgumentOutOfRangeException("data", "DataSet does not contain any tables."); } } } }

Тогда вам нужен класс для работы с каждым из DataTables 

using System; using System.Data; using CarlosAg.ExcelXmlWriter; namespace WWB.ExcelDatasetWriter { /// <summary> /// Writes a datatable to a worksheet using the CarlosAG.ExcelXmlWriter library. /// </summary> public class ExcelDataTableWriter { public ExcelDataTableWriter() {} public void PopulateWorksheet(DataTable dt, Worksheet toPopulate) { PopulateWorksheet(dt, toPopulate, true); } public void PopulateWorksheet(DataTable dt, Worksheet toPopulate, bool makeHeader) { //check valid input if (toPopulate==null) { throw new ArgumentNullException("toPopulate", "Worksheet cannot be null."); } if (dt==null) { throw new ArgumentNullException("dt", "DataTable cannot be null"); } //Parse the columns ColumnType[] colDesc=parseColumns(dt); //Create header row if (makeHeader) { toPopulate.Table.Rows.Insert(0, makeHeaderRow(colDesc)); } //Create rows foreach (DataRow row in dt.Rows) { toPopulate.Table.Rows.Add(makeDataRow(colDesc, row)); } } #region row + cell making private WorksheetRow makeHeaderRow(ColumnType[] cols) { WorksheetRow ret=new WorksheetRow(); foreach(ColumnType ctd in cols) { ret.Cells.Add(ctd.GetHeaderCell()); } return ret; } private WorksheetRow makeDataRow(ColumnType[] ctds, DataRow row) { WorksheetRow ret=new WorksheetRow(); WorksheetCell tmp=null; for (int i=0; i<row.Table.Columns.Count; i++) { tmp=ctds[i].GetDataCell(row[i]); ret.Cells.Add(tmp); } return ret; } #endregion #region column parsing private ColumnType[] parseColumns(DataTable dt) { ColumnType[] ret=new ColumnType[dt.Columns.Count]; ColumnType ctd=null; for (int i=0; i<dt.Columns.Count; i++) { ctd=new ColumnType(); ctd.Name=dt.Columns[i].ColumnName; getDataType(dt.Columns[i], ctd); ret[i]=ctd; } return ret; } private void getDataType(DataColumn col, ColumnType desc) { if (col.DataType==typeof(DateTime)) { desc.ExcelType=DataType.DateTime; } else if (col.DataType==typeof(string)) { desc.ExcelType=DataType.String; } else if (col.DataType==typeof(sbyte) || col.DataType==typeof(byte) || col.DataType==typeof(short) || col.DataType==typeof(ushort) || col.DataType==typeof(int) || col.DataType==typeof(uint) || col.DataType==typeof(long) || col.DataType==typeof(ulong) || col.DataType==typeof(float) || col.DataType==typeof(double) || col.DataType==typeof(decimal) ) { desc.ExcelType=DataType.Number; } else { desc.ExcelType=DataType.String; } } #endregion } } с using System; using System.Data; using CarlosAg.ExcelXmlWriter; namespace WWB.ExcelDatasetWriter { /// <summary> /// Writes a datatable to a worksheet using the CarlosAG.ExcelXmlWriter library. /// </summary> public class ExcelDataTableWriter { public ExcelDataTableWriter() {} public void PopulateWorksheet(DataTable dt, Worksheet toPopulate) { PopulateWorksheet(dt, toPopulate, true); } public void PopulateWorksheet(DataTable dt, Worksheet toPopulate, bool makeHeader) { //check valid input if (toPopulate==null) { throw new ArgumentNullException("toPopulate", "Worksheet cannot be null."); } if (dt==null) { throw new ArgumentNullException("dt", "DataTable cannot be null"); } //Parse the columns ColumnType[] colDesc=parseColumns(dt); //Create header row if (makeHeader) { toPopulate.Table.Rows.Insert(0, makeHeaderRow(colDesc)); } //Create rows foreach (DataRow row in dt.Rows) { toPopulate.Table.Rows.Add(makeDataRow(colDesc, row)); } } #region row + cell making private WorksheetRow makeHeaderRow(ColumnType[] cols) { WorksheetRow ret=new WorksheetRow(); foreach(ColumnType ctd in cols) { ret.Cells.Add(ctd.GetHeaderCell()); } return ret; } private WorksheetRow makeDataRow(ColumnType[] ctds, DataRow row) { WorksheetRow ret=new WorksheetRow(); WorksheetCell tmp=null; for (int i=0; i<row.Table.Columns.Count; i++) { tmp=ctds[i].GetDataCell(row[i]); ret.Cells.Add(tmp); } return ret; } #endregion #region column parsing private ColumnType[] parseColumns(DataTable dt) { ColumnType[] ret=new ColumnType[dt.Columns.Count]; ColumnType ctd=null; for (int i=0; i<dt.Columns.Count; i++) { ctd=new ColumnType(); ctd.Name=dt.Columns[i].ColumnName; getDataType(dt.Columns[i], ctd); ret[i]=ctd; } return ret; } private void getDataType(DataColumn col, ColumnType desc) { if (col.DataType==typeof(DateTime)) { desc.ExcelType=DataType.DateTime; } else if (col.DataType==typeof(string)) { desc.ExcelType=DataType.String; } else if (col.DataType==typeof(sbyte) || col.DataType==typeof(byte) || col.DataType==typeof(short) || col.DataType==typeof(ushort) || col.DataType==typeof(int) || col.DataType==typeof(uint) || col.DataType==typeof(long) || col.DataType==typeof(ulong) || col.DataType==typeof(float) || col.DataType==typeof(double) || col.DataType==typeof(decimal) ) { desc.ExcelType=DataType.Number; } else { desc.ExcelType=DataType.String; } } #endregion } }
using System; using System.Data; using CarlosAg.ExcelXmlWriter; namespace WWB.ExcelDatasetWriter { /// <summary> /// Writes a datatable to a worksheet using the CarlosAG.ExcelXmlWriter library. /// </summary> public class ExcelDataTableWriter { public ExcelDataTableWriter() {} public void PopulateWorksheet(DataTable dt, Worksheet toPopulate) { PopulateWorksheet(dt, toPopulate, true); } public void PopulateWorksheet(DataTable dt, Worksheet toPopulate, bool makeHeader) { //check valid input if (toPopulate==null) { throw new ArgumentNullException("toPopulate", "Worksheet cannot be null."); } if (dt==null) { throw new ArgumentNullException("dt", "DataTable cannot be null"); } //Parse the columns ColumnType[] colDesc=parseColumns(dt); //Create header row if (makeHeader) { toPopulate.Table.Rows.Insert(0, makeHeaderRow(colDesc)); } //Create rows foreach (DataRow row in dt.Rows) { toPopulate.Table.Rows.Add(makeDataRow(colDesc, row)); } } #region row + cell making private WorksheetRow makeHeaderRow(ColumnType[] cols) { WorksheetRow ret=new WorksheetRow(); foreach(ColumnType ctd in cols) { ret.Cells.Add(ctd.GetHeaderCell()); } return ret; } private WorksheetRow makeDataRow(ColumnType[] ctds, DataRow row) { WorksheetRow ret=new WorksheetRow(); WorksheetCell tmp=null; for (int i=0; i<row.Table.Columns.Count; i++) { tmp=ctds[i].GetDataCell(row[i]); ret.Cells.Add(tmp); } return ret; } #endregion #region column parsing private ColumnType[] parseColumns(DataTable dt) { ColumnType[] ret=new ColumnType[dt.Columns.Count]; ColumnType ctd=null; for (int i=0; i<dt.Columns.Count; i++) { ctd=new ColumnType(); ctd.Name=dt.Columns[i].ColumnName; getDataType(dt.Columns[i], ctd); ret[i]=ctd; } return ret; } private void getDataType(DataColumn col, ColumnType desc) { if (col.DataType==typeof(DateTime)) { desc.ExcelType=DataType.DateTime; } else if (col.DataType==typeof(string)) { desc.ExcelType=DataType.String; } else if (col.DataType==typeof(sbyte) || col.DataType==typeof(byte) || col.DataType==typeof(short) || col.DataType==typeof(ushort) || col.DataType==typeof(int) || col.DataType==typeof(uint) || col.DataType==typeof(long) || col.DataType==typeof(ulong) || col.DataType==typeof(float) || col.DataType==typeof(double) || col.DataType==typeof(decimal) ) { desc.ExcelType=DataType.Number; } else { desc.ExcelType=DataType.String; } } #endregion } }

И наконец, этот класс опирается на класс для работы с отдельными типами столбцов. 

using System; using CarlosAg.ExcelXmlWriter; namespace WWB.ExcelDatasetWriter { /// <summary> /// Creates a Column for CarlosAg.ExcelXmlWriter /// </summary> internal class ColumnType { public ColumnType() {} private string name; public string Name { get {return name;} set {name=value;} } private DataType excelType; public DataType ExcelType { get {return excelType;} set {excelType=value;} } public WorksheetCell GetHeaderCell() { WorksheetCell head=new WorksheetCell(Name, DataType.String); return head; } private string getDataTypeFormatString() { if (ExcelType==DataType.DateTime) { return "s"; } return null; } public WorksheetCell GetDataCell(object data) { WorksheetCell dc=new WorksheetCell(); dc.Data.Type=ExcelType; if (ExcelType==DataType.DateTime && data is DateTime) { DateTime dt=(DateTime)data; dc.Data.Text=dt.ToString("s"); } else { string dataString=data.ToString(); if (dataString == null || dataString.Length==0) { dc.Data.Type=DataType.String; dc.Data.Text=string.Empty; } else { dc.Data.Text=dataString; } } return dc; } } }
using System; using CarlosAg.ExcelXmlWriter; namespace WWB.ExcelDatasetWriter { /// <summary> /// Creates a Column for CarlosAg.ExcelXmlWriter /// </summary> internal class ColumnType { public ColumnType() {} private string name; public string Name { get {return name;} set {name=value;} } private DataType excelType; public DataType ExcelType { get {return excelType;} set {excelType=value;} } public WorksheetCell GetHeaderCell() { WorksheetCell head=new WorksheetCell(Name, DataType.String); return head; } private string getDataTypeFormatString() { if (ExcelType==DataType.DateTime) { return "s"; } return null; } public WorksheetCell GetDataCell(object data) { WorksheetCell dc=new WorksheetCell(); dc.Data.Type=ExcelType; if (ExcelType==DataType.DateTime && data is DateTime) { DateTime dt=(DateTime)data; dc.Data.Text=dt.ToString("s"); } else { string dataString=data.ToString(); if (dataString == null || dataString.Length==0) { dc.Data.Type=DataType.String; dc.Data.Text=string.Empty; } else { dc.Data.Text=dataString; } } return dc; } } }

Теперь первые два примера кода довольно просты. Но последний имеет дело с несколькими причудами библиотеки. Основная проблема заключается в том, что свойство WorksheetCell.Data.Text на самом деле является строкой, а не объектом. Он буквально записывает свою ценность в электронную таблицу. И иногда это может конфликтовать со свойством DataType, приводя к интересным (и трудно точно определить) ошибкам Excel. В любом случае метод GetCellData (object) достаточно эффективно переносится для общего использования.

Теперь, чтобы Excel открывал электронную таблицу, вам просто нужно помнить, чтобы она передавалась обратно в браузер с соответствующим типом контента: 

ExcelDatasetWriter edw=new ExcelDatasetWriter(); Workbook wb=CreateWorksheet(MyDataSet); Response.ContentType = "application/vnd.ms-excel"; wb.Save(Response.OutputStream);
ExcelDatasetWriter edw=new ExcelDatasetWriter(); Workbook wb=CreateWorksheet(MyDataSet); Response.ContentType = "application/vnd.ms-excel"; wb.Save(Response.OutputStream);

Итак, подведем итог:

  1. Написание Excel является общей потребностью.
  2. Использование библиотек объектов Excel на веб-сервере — плохая идея.
  3. Написание Excel с использованием управляемого кода — хорошая идея.
  4. Excel-писатель CarlosAg помогает вам создавать Excel, даже не вступая в мир взаимодействия.

Наслаждайтесь и счастливого кодирования.

39
Share: