Andy Lanng
ОК-попытка №2
Использование Инструкции OpenXML: Галерея NuGet / DocumentFormat.Функция OpenXML 2.5.0[^]
Все документы xslx - это всего лишь несколько XML-документов, застегнутых на молнию (серьезно, переименуйте любой документ. xlsx в. zip и откройте его, чтобы увидеть весь xml)
Документы оптимизированы для экономии места, поэтому их довольно сложно читать и писать. Я придумал несколько способов помочь.
Это довольно медленно для очень больших наборов данных, поэтому его можно оптимизировать:
метод расширения:
public static Class Extensions{
public static void SaveAsExcel<T>(this IEnumerable<T> source, string filePath, string worksheetName)
{
//Spread
ExportToExcelUtility.CreateExcelDocument(ExportToExcelUtility.ToDataTable(source), filePath, worksheetName,
new Dictionary<string, string>(), new Dictionary<string, int>());
}
}
остальную часть кода я думаю, что получил отсюда:
Экспорт в Excel с помощью C# / Sharmili unplugged.......[
^]
public sealed class ExportToExcelUtility
{
/// <summary>
/// Convert the list to Data table.
/// </summary>
/// <param name="items">List</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable<T>(IEnumerable<T> items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
dataTable.Columns.Add(prop.Name);//Setting column names
}
foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
values[i] = Props[i].GetValue(item, null);//Insert values.
}
dataTable.Rows.Add(values);
}
return dataTable;
}
/// <summary>
/// Call this method from the Page providing the desired information
/// </summary>
/// <param name="dataTable">The records to be written in excel</param>
/// <param name="excelFilename">Name of the file</param>
/// <param name="sheetName">Name of the sheet</param>
/// <param name="filters">Search key and value based on which the datatable is generated</param>
/// <param name="columnSize">column name and size</param>
/// <returns></returns>
public static bool CreateExcelDocument(DataTable dataTable, string excelFilename, string sheetName, Dictionary<string, string> filters, Dictionary<string, int> columnSize)
{
try
{
bool exists = File.Exists(excelFilename);
SpreadsheetDocument objExcelDoc;
if (exists)
{
try
{
objExcelDoc = SpreadsheetDocument.Open(excelFilename, true);
}
catch
{
File.Delete(excelFilename);
objExcelDoc = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook);
}
}
else
objExcelDoc = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook);
int cellSize;
WorkbookPart wbp = objExcelDoc.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet workSheet = new Worksheet();
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
Columns columns = new Columns();
for (int i = 0; i < columnSize.Count(); i++)
{
columnSize.TryGetValue(columnSize.Keys.ElementAt(i).ToString(), out cellSize);
columns.Append(CreateColumnData(Convert.ToUInt32(i - 1), Convert.ToUInt32(i - 1), cellSize));
}
workSheet.Append(columns);
SheetData sheetData = new SheetData();
for (UInt32 i = 2; i <= 1 + filters.Count(); i++)
{
sheetData.Append(CreateFilters(i, filters));
}
sheetData.Append(CreateColumnHeader(Convert.ToUInt32(filters.Count() + 3), columnSize));
UInt32 index = Convert.ToUInt32(filters.Count() + 4);
foreach (DataRow dr in dataTable.Rows)
{
sheetData.Append(CreateContent(index, dr, columnSize.Count()));
index++;
}
workSheet.Append(sheetData);
wsp.Worksheet = workSheet;
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = sheetName;
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
objExcelDoc.WorkbookPart.Workbook = wb;
objExcelDoc.WorkbookPart.Workbook.Save();
objExcelDoc.Close();
}
catch (Exception ex)
{
throw;
}
return true;
}
/// <summary>
/// Create column for storing data by passing the start column index, end column index and column width
/// </summary>
/// <param name="StartColumnIndex">start column index</param>
/// <param name="EndColumnIndex">end column index</param>
/// <param name="ColumnWidth">width of each column</param>
/// <returns>column</returns>
private static Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
{
Column column;
column = new Column();
column.Min = StartColumnIndex;
column.Max = EndColumnIndex;
column.Width = ColumnWidth;
column.CustomWidth = true;
return column;
}
/// <summary>
/// Writes the row to the excel by reading each datarow from the datatable
/// </summary>
/// <param name="index">row index</param>
/// <param name="dr">data row</param>
/// <param name="columns">number of columns</param>
/// <returns></returns>
private static Row CreateContent(UInt32 index, DataRow dr, int columns)
{
Row objRow = new Row();
Cell objCell;
try
{
objRow.RowIndex = index;
for (int i = 0; i < columns; i++)
{
objCell = new Cell();
objCell.StyleIndex = 5;
objCell.DataType = CellValues.String;
objCell.CellReference = (char)(i + 65) + index.ToString();
objCell.CellValue = new CellValue(dr.ItemArray[i].ToString());
objRow.Append(objCell);
}
}
catch (Exception ex)
{
throw;
}
return objRow;
}
/// <summary>
/// Defines the Style sheet for the excel.
/// </summary>
/// <returns>Stylesheet</returns>
private static Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Palatino Linotype");
ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fill