最近项目中有许多地方需要将数据导出成Excel文件，因此写了个公共模块做Excel的导出！
代码：
/// <summary>
/// 将IList对象导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="columnTitle"></param>
/// <param name="title"></param>
/// <param name="path"></param>
/// <returns></returns>
public string DataToExcel<T>(IList<T> list, List<DataKeyValue> columnTitle, string title,string path)
where T : class
{
DataTable dt = ListToDataTable(list);
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheet = book.CreateSheet("Sheet1") as HSSFSheet;
HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
dataRow = sheet.CreateRow(0) as HSSFRow;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, columnTitle.Count - 1);
sheet.AddMergedRegion(region);
IRow hrow = sheet.CreateRow(0);
hrow.Height = 20 * 20;
ICell icellltop0 = hrow.CreateCell(0);
IFont font12 = book.CreateFont();
font12.FontHeightInPoints = 12;
font12.FontName = "微软雅黑";
font12.Boldweight = short.MaxValue;
font12.Color = Black.Index;
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sheet);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = cellStyle;
}
}
cellStyle.SetFont(font12);
cellStyle.FillBackgroundColor = BlueGrey.Index;
icellltop0.CellStyle = cellStyle;
icellltop0.SetCellValue(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"));
ICellStyle TitleStyle = book.CreateCellStyle();
TitleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
TitleStyle.FillPattern = FillPattern.SolidForeground;
TitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
TitleStyle.BorderBottom = BorderStyle.Thin;
TitleStyle.BorderLeft = BorderStyle.Thin;
TitleStyle.BorderRight = BorderStyle.Thin;
TitleStyle.BorderTop = BorderStyle.Thin;
IRow TitleRow = sheet.CreateRow(1);
TitleRow.Height = 20 * 15;
for (int i = 0; i < columnTitle.Count; i++)
{
ICell cell = TitleRow.CreateCell(i);
cell.CellStyle = TitleStyle;
cell.SetCellValue(columnTitle[i].Value);
sheet.SetColumnWidth(i, columnTitle[i].ColumnWidth);
}
// data
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{
}
for (int j = 0; j < columnTitle.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][columnTitle[j].key].ToString());
}
}
ICellStyle borStyle = book.CreateCellStyle();
borStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
borStyle.BorderBottom = BorderStyle.Thin;
borStyle.BorderLeft = BorderStyle.Thin;
borStyle.BorderRight = BorderStyle.Thin;
borStyle.BorderTop = BorderStyle.Thin;
for (int i = 1; i <= dt.Rows.Count+1; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sheet);
row.Height = 20 * 16;
for (int j = 0; j < columnTitle.Count; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = borStyle;
}
}
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = title + DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
using (FileStream fsWrite = File.OpenWrite(path + fileName))
{
book.Write(fsWrite);
}
return fileName;
}
IList To DataTable:
/// <summary>
/// 实体列表转换成DataTable
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="list"> 实体列表</param>
/// <returns></returns>
public DataTable ListToDataTable<T>(IList<T> list)
where T : class
{
if (list == null || list.Count <= 0)
{
return null;
}
DataTable dt = new DataTable(typeof(T).Name);
DataColumn column;
DataRow row;
PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
int length = myPropertyInfo.Length;
bool createColumn = true;
foreach (T t in list)
{
if (t == null)
{
continue;
}
row = dt.NewRow();
for (int i = 0; i < length; i++)
{
PropertyInfo pi = myPropertyInfo[i];
string name = pi.Name;
if (createColumn)
{
column = new DataColumn(name, typeof(string));
dt.Columns.Add(column);
}
row[name] = pi.GetValue(t,null) is null ?"": pi.GetValue(t, null).ToString();
}
if (createColumn)
{
createColumn = false;
}
dt.Rows.Add(row);
}
return dt;
}
配置列信息：
public class DataKeyValue
{
public string key { get; set; }
public string Value { get; set; }
public int ColumnWidth { get; set; }
}
调用方法：
ExcelHelper excelHelper = new ExcelHelper();
List<DataKeyValue> list = new List<DataKeyValue>() {
new DataKeyValue(){key="ID",Value="编号",ColumnWidth=256*10},
new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*20},
new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*20},
new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*10},
new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*35},
new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*45},
new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*45},
new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*45},
new DataKeyValue(){key="XXX",Value="XXX",ColumnWidth=256*45},
};
string path = ConfigurationManager.AppSettings["ExportPath"].ToString();
string result = excelHelper.DataToExcel(allCustomerList, list, "Customer_", path);
