思路:
- List数据转换成DataTable
- 然后将DataTable的行列写进Excel里
第一步 将List数据转换成DataTable
这里注意枚举值,如果不对枚举值进行转换,那么最后生成的数据是int类型的数据。
private static System.Data.DataTable ToDataTable<T>(IList<T> data)
{
PropertyDescriptorCollection props =
TypeDescriptor.GetProperties(typeof(T));
System.Data.DataTable table = new System.Data.DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
if (prop.PropertyType.IsEnum)
{
table.Columns.Add(prop.Name, typeof(string));
}
else
{
table.Columns.Add(prop.Name, prop.PropertyType);
}
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
if (props[i].GetValue(item) is Enum)
{
values[i] = props[i].GetValue(item).ToString();
}
else
{
values[i] = props[i].GetValue(item);
}
}
table.Rows.Add(values);
}
return table;
}
第二步 然后将DataTable的行列写进Excel里
注意事项
- 日期的转换,可以把日期转换成字符串
- 布尔值的转换,在Excel里,布尔值实际上是0或者1
private static void CreateExcel<T>(string filePath, string sheetName,List<T> models)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
//添加workbook文件,并创建workbook节点
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
//在workbook里添加sheetdata节点
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
//添加sheets文件夹
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
//添加sheet文件
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName };
sheets.Append(sheet);
//给SheetData添加数据
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
System.Data.DataTable table = ToDataTable<T>(models);
Row headerRow = new Row();
List<String> columns = new List<string>();
//标题名全是string
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = new EnumValue<CellValues>(CellValues.String);
cell.CellValue = new CellValue(column.ColumnName);
headerRow.Append(cell);
}
sheetData.Append(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (dsrow[col].GetType().Name == "Int32"|| dsrow[col].GetType().Name == "Double")
{
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.CellValue = new CellValue(dsrow[col].ToString());
}
else if (dsrow[col].GetType().Name == "DateTime")
{
cell.DataType = new EnumValue<CellValues>(CellValues.String);
DateTime dtValue = (DateTime)dsrow[col];
cell.CellValue = new CellValue(dtValue.ToShortDateString().ToString());
}
else if (dsrow[col].GetType().Name == "Boolean")
{
cell.DataType = new EnumValue<CellValues>(CellValues.Boolean);
cell.CellValue = new CellValue((bool)dsrow[col]==true?"1":"0");
}
else
{
cell.DataType = new EnumValue<CellValues>(CellValues.String);
cell.CellValue = new CellValue(dsrow[col].ToString());
}
newRow.Append(cell);
}
sheetData.Append(newRow);
}
workbookPart.Workbook.Save();
}
}