open xml sdk 将dataTable写入Excel 并下载 FileStrem

阅读原文时间：2021年04月21日阅读：1

1 ///

2 /// 将集合类转换成DataTable 3 ///

4 /// 集合
5 ///
6 public static DataTable ToDataTableTow(List list) 7 {
8 DataTable result = new DataTable(); 9 if (list.Count > 0) 10 { 11 PropertyInfo[] propertys = list[0].GetType().GetProperties(); 12
13 foreach (PropertyInfo pi in propertys) 14 { 15 result.Columns.Add(pi.Name, pi.PropertyType); 16 } 17 for (int i = 0; i < list.Count; i++) 18 { 19 ArrayList tempList = new ArrayList(); 20 foreach (PropertyInfo pi in propertys) 21 { 22 object obj = pi.GetValue(list[i], null); 23 tempList.Add(obj); 24 } 25 object[] array = tempList.ToArray(); 26 result.LoadDataRow(array, true); 27 } 28 } 29 return result; 30 }

///

/// 创建excel,并且把dataTable导入到excel中 ///

/// 保存路径
/// 数据源
public static MemoryStream CreateExcel(DataTable table)
{
MemoryStream stream = new MemoryStream(); using (var workbook = SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{ var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); uint sheetId = 1; bool isAddStyle = false; //foreach (DataTable table in dataTables) //{
var sheetPart = workbook.WorkbookPart.AddNewPart(); var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(); if (!isAddStyle)
{ var stylesPart = workbook.WorkbookPart.AddNewPart();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
isAddStyle = true;
} //Columns headColumns = CrateColunms(table); //sheetPart.Worksheet.Append(headColumns);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); if (sheets.Elements().Count() > 0)
{
sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() +
1;
} string sheetName = "电子单";

            DocumentFormat.OpenXml.Spreadsheet.Sheet sheet \= new DocumentFormat.OpenXml.Spreadsheet.Sheet()
            {
                Id \= relationshipId,
                SheetId \= sheetId,
                Name \= sheetName
            };
            sheets.Append(sheet);//创建表

DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

            List<String> columns = new List<string\>(); foreach (DataColumn column in table.Columns)
            {
                columns.Add(column.ColumnName);

                DocumentFormat.OpenXml.Spreadsheet.Cell cell \= new DocumentFormat.OpenXml.Spreadsheet.Cell();
                cell.DataType \= DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                cell.StyleIndex \= 11;
                cell.CellValue \= new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                headerRow.AppendChild(cell);
            }
            sheetData.AppendChild(headerRow);//创建表头

            foreach (DataRow dsrow in table.Rows)
            {
                DocumentFormat.OpenXml.Spreadsheet.Row newRow \= new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String col in columns)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Cell cell \= new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType \= DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.StyleIndex \= 10;
                    cell.CellValue \= new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow\[col\].ToString()); //

newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Append(sheetData);//创建单元格
workbook.Close();
}
stream.Position = 0; return stream;
}

//字符流的形式下载Excel
1 DataTable table = ElectronicReceiptView.ToDataTableTow(proList); 2 var stream= ElectronicReceiptView.CreateExcel(table); 3 return File(stream, "application/ms-excel", name);

转载于:https://www.cnblogs.com/longshanshan/p/7943065.html

手机扫一扫

移动阅读更方便

你可能感兴趣的文章