使用Open xml 操作Excel系列之二从data table导出数据到Excel

由于Excel中提供了透视表PivotTable，许多项目都使用它来作为数据分析报表。在有些情况下，我们需要在Excel中设计好模板，包括数据源表，透视表等，当数据导入到数据源表时，自动更新透视表。本篇主要讲述导出数据到Excel的过程。

假设我们需要从Sql Server 中读取数据到DataTable中，然后把DataTable中的数据写入到Excel.

那这个导入过程大致有如下逻辑步骤：

1. 读取数据到DataTable中。

2. 读取Excel指定Sheet中的数据字段名。一般情况下，我们使用表格(Sheet)的第一行作为数据字段名，则如下代码读取WorkSheet中的字段定义. 以下示例代码为实际应用中我使用自定义类来匹配相应字段

 1  public class Mapping
 2     {
 3         public string SourceField { get; set; }
 4         public string DestinationCellHeader { get; set; }
 5         public string DestinationReference { get; set; }
 6         public CellType CellType { get; set; }
 9     }
10 
11     public List<Mapping> GetRawMappings(string fileName,string sheetName)
12         {
13             List<Mapping> mappings = new List<Mapping>();
14             using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
15             {
16                 WorkbookPart workbookPart = document.WorkbookPart;
17                 Sheet dataSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => string.Compare(s.Name, sheetName, true) == 0).FirstOrDefault();//sheetName为你要导入数据的工作表名称
18                 if (dataSheet != null)
19                 {
20                     WorksheetPart worksheetPart = workbookPart.GetPartById(dataSheet.Id.Value) as WorksheetPart;
21                     var headerRow = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>().
22                                             FirstOrDefault(c => c.RowIndex == 1);//读取首行
23                     mappings = headerRow.Elements<Cell>().Select(c => new Mapping()
24                     {
25                         DestinationReference = c.CellReference.Value.Replace("1", ""),
26                         DestinationCellHeader = ExcelHelper.GetCellValue(workbookPart, c)
27                     }).ToList();
28                 }
29             }
30         }

3. 读取DataTable 中的数据字段名及数据类型。

4. 匹配DataTable中的数据字段名到Excel中的数据字段名。

   public enum CellType
    {
        Text,
        Number,
        Date,
        Boolean
    }

       private static CellType GetCellType(System.Data.DataColumn col)
        {
            if (col.DataType == typeof(System.Decimal))
            {
                return CellType.Number;
            }
            if (col.DataType == typeof(System.Boolean ))
            {
                return CellType.Boolean ;
            }
            if (col.DataType == typeof(System.DateTime) )
            {
                return CellType.Date ;
            }
            return CellType.Text;
        }
  
//data为从sqlserver中读取得相应的数据表
 var cols = GetRawMappings("文件名","工作表名");
 List<Mapping> allColumnMappings = new List<Mapping>();
   var sourceDataColumns = data.Columns.Cast<System.Data.DataColumn>();
   //自动匹配的列
   var colsMapped = cols.Where(c => sourceDataColumns.
                        Any(cl => string.Compare(cl.ColumnName,c.DestinationCellHeader,true) == 0)).ToList();
   foreach (Mapping ma in colsMapped)
   {
       ma.SourceField = ma.DestinationCellHeader;
       System.Data.DataColumn col = sourceDataColumns.FirstOrDefault(c => string.Compare(c.ColumnName,ma.SourceField,true)==0);
                        ma.CellType = GetCellType(col);
   }

5. 循环读取每个DataRow，并根据DataColumn的字段类型，循环写入Excel中的相应字段，并设置字段类型。

 1         private static Row CreateContentRow(SpreadsheetDocument document, int index, System.Data.DataRow dr,List<Mapping> mappings,Nullable<uint> dateStyleID)
 2         {
 3             //Create the new row.
 4             Row row = new Row();
 5             row.RowIndex = (UInt32)index;
 6             //First cell is a text cell, so create it and append it.
 7             //Cell firstCell = CreateTextCell(referenceHeaders[0],index);
 8             //r.AppendChild(firstCell);
 9             //Create the cells that contain the data.
10             foreach(var mapping in mappings)
11             {
12                 Cell cell = null;
13                 string source = string.Empty;
14                 
15                 if (!string.IsNullOrEmpty(mapping.SourceField))
16                     source = dr[mapping.SourceField].ToString();
17                 
18                 cell = CreateCell(document, mapping.CellType, mapping.DestinationReference, index, source, dateStyleID);
19 
20                 row.AppendChild(cell);
21             }
22           
23             return row;
24         }
25 
26    
27         public static Cell CreateCell(SpreadsheetDocument document, CellType type, string header, int index, string text, Nullable<uint> dateStyleID)
28         {
29             Cell cell = new Cell();
30             cell.CellReference = header + index;
31 
32             CellValue value = null;
33             if (type == CellType.Text  )
34             {
35                 //int stringIndex = ExcelHelper.AppendOrGetSharedStringItem(text, document);
36                 //stringIndex.ToString()
37                 value = new CellValue(text);
38                 cell.DataType = new EnumValue<CellValues>(CellValues.String);
39             }
40             if (type == CellType.Date)
41             {
42                 if (!string.IsNullOrEmpty(text))
43                 {
44                     DateTime dt = DateTime.Parse(text);
45                     value = new CellValue(dt.ToOADate().ToString());
46                 }
47                 cell.StyleIndex = dateStyleID;
48                 cell.DataType = new EnumValue<CellValues>(CellValues.Number); //new EnumValue<CellValues>(CellValues.Date);
49             }
50             if (type == CellType.Number)
51             { 
52                  cell.DataType = new EnumValue<CellValues>(CellValues.Number);
53                  value = new CellValue(text);
54             }
55             
56             cell.CellValue = value;
57             //cell.AppendChild(value);
58             return cell;
59         }
60 
61  Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
62  var dateStyleId = ExcelHelper.CreateCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(14));
63  //var  references = headerRow.Elements<Cell>().Select(c => c.CellReference.Value.Replace("1", "")).ToList();
64  var i = 1;
65  foreach (System.Data.DataRow row in data.Rows)
66  {
67     Row contentRow = CreateContentRow(document, ((int)maxRowIndex) + i++, row, cols, dateStyleId);
68     //Append new row to sheet data.
69     sheetData.AppendChild(contentRow);
70  }

下一篇,我将使用Open Xml修改Pivot table 数据源定义