由于Excel中提供了透视表PivotTable，许多项目都使用它来作为数据分析报表。 在有些情况下，我们需要在Excel中设计好模板，包括数据源表，透视表等， 当数据导入到数据源表时，自动更新透视表。本篇主要讲述导出数据到Excel的过程。
假设我们需要从Sql Server 中读取数据到DataTable中，然后把DataTable中的数据写入到Excel.
那这个导入过程大致有如下逻辑步骤：
1. 读取数据到DataTable中。
2. 读取Excel指定Sheet中的数据字段名。 一般情况下，我们使用表格(Sheet)的第一行作为数据字段名，则如下代码读取WorkSheet中的字段定义. 以下示例代码为实际应用中我使用自定义类来匹配相应字段
public class Mapping
{
public string SourceField { get; set; }
public string DestinationCellHeader { get; set; }
public string DestinationReference { get; set; }
public CellType CellType { get; set; }
} public List<Mapping> GetRawMappings(string fileName,string sheetName)
{
List<Mapping> mappings = new List<Mapping>();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart workbookPart = document.WorkbookPart;
Sheet dataSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => string.Compare(s.Name, sheetName, true) == ).FirstOrDefault();//sheetName为你要导入数据的工作表名称
if (dataSheet != null)
{
WorksheetPart worksheetPart = workbookPart.GetPartById(dataSheet.Id.Value) as WorksheetPart;
var headerRow = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>().
FirstOrDefault(c => c.RowIndex == );//读取首行
mappings = headerRow.Elements<Cell>().Select(c => new Mapping()
{
DestinationReference = c.CellReference.Value.Replace("", ""),
DestinationCellHeader = ExcelHelper.GetCellValue(workbookPart, c)
}).ToList();
}
}
}
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) == )).ToList();
foreach (Mapping ma in colsMapped)
{
ma.SourceField = ma.DestinationCellHeader;
System.Data.DataColumn col = sourceDataColumns.FirstOrDefault(c => string.Compare(c.ColumnName,ma.SourceField,true)==);
ma.CellType = GetCellType(col);
}
5. 循环读取每个DataRow，并根据DataColumn的字段类型，循环写入Excel中的相应字段，并设置字段类型。
private static Row CreateContentRow(SpreadsheetDocument document, int index, System.Data.DataRow dr,List<Mapping> mappings,Nullable<uint> dateStyleID)
{
//Create the new row.
Row row = new Row();
row.RowIndex = (UInt32)index;
//First cell is a text cell, so create it and append it.
//Cell firstCell = CreateTextCell(referenceHeaders[0],index);
//r.AppendChild(firstCell);
//Create the cells that contain the data.
foreach(var mapping in mappings)
{
Cell cell = null;
string source = string.Empty; if (!string.IsNullOrEmpty(mapping.SourceField))
source = dr[mapping.SourceField].ToString(); cell = CreateCell(document, mapping.CellType, mapping.DestinationReference, index, source, dateStyleID); row.AppendChild(cell);
} return row;
} public static Cell CreateCell(SpreadsheetDocument document, CellType type, string header, int index, string text, Nullable<uint> dateStyleID)
{
Cell cell = new Cell();
cell.CellReference = header + index; CellValue value = null;
if (type == CellType.Text )
{
//int stringIndex = ExcelHelper.AppendOrGetSharedStringItem(text, document);
//stringIndex.ToString()
value = new CellValue(text);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
}
if (type == CellType.Date)
{
if (!string.IsNullOrEmpty(text))
{
DateTime dt = DateTime.Parse(text);
value = new CellValue(dt.ToOADate().ToString());
}
cell.StyleIndex = dateStyleID;
cell.DataType = new EnumValue<CellValues>(CellValues.Number); //new EnumValue<CellValues>(CellValues.Date);
}
if (type == CellType.Number)
{
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
value = new CellValue(text);
} cell.CellValue = value;
//cell.AppendChild(value);
return cell;
} Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
var dateStyleId = ExcelHelper.CreateCellFormat(styleSheet, null, null, UInt32Value.FromUInt32());
//var references = headerRow.Elements<Cell>().Select(c => c.CellReference.Value.Replace("1", "")).ToList();
var i = ;
foreach (System.Data.DataRow row in data.Rows)
{
Row contentRow = CreateContentRow(document, ((int)maxRowIndex) + i++, row, cols, dateStyleId);
//Append new row to sheet data.
sheetData.AppendChild(contentRow);
} 下一篇,我将使用Open Xml修改Pivot table 数据源定义