一、引入NPOl
安装NPOL库。
二、创建ExcelExportHelper公共类
代码如下(示例):
/// <summary>
/// List转Excel
/// </summary>
public static class ExcelExportHelper
{
public static void RenderToExcel<T>(List<T> datas,string sheetName= "导出数据",string url= "D:\\Test.xls")
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
int rowIndex = 1, piIndex = 0;
Type type = typeof(T);
PropertyInfo[] pis = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
int pisLen = pis.Length;
PropertyInfo pi = null;
string displayName = string.Empty;
while (piIndex < pisLen)
{
pi = pis[piIndex];
displayName = pi.GetCustomAttribute<DisplayNameAttribute>().DisplayName;
if (!displayName.Equals(string.Empty))
{//如果该属性指定了DisplayName,则输出
try
{
headerRow.CreateCell(piIndex).SetCellValue(displayName);
}
catch (Exception)
{
headerRow.CreateCell(piIndex).SetCellValue("");
}
}
piIndex++;
}
foreach (T data in datas)
{
piIndex = 0;
IRow dataRow = sheet.CreateRow(rowIndex);
while (piIndex < pisLen)
{
pi = pis[piIndex];
try
{
if (pi?.GetValue(data, null)?.ToString() != null)
{
dataRow.CreateCell(piIndex).SetCellValue(pi?.GetValue(data, null).ToString());
}
else
{
dataRow.CreateCell(piIndex).SetCellValue("");
}
}
catch (Exception)
{
dataRow.CreateCell(piIndex).SetCellValue("");
}
piIndex++;
}
rowIndex++;
}
workbook.Write(ms);
FileStream dumpFile = new FileStream(url, FileMode.Create, FileAccess.ReadWrite);
ms.WriteTo(dumpFile);
ms.Flush();
ms.Position = 0;
dumpFile.Close();
dumpFile.Dispose();
}
}
1.创建TestItem实体
代码如下(示例):
public class TestItem
{
[DisplayName("名字")]
public string Name { get; set; }
[DisplayName("唯一键")]
public string Id { get; set; }
[DisplayName("数据")]
public string Date { get; set; }
}
2.调用
List<TestItem> s = new List<TestItem>();
for (int i = 0; i < 10; i++)
{
s.Add(new TestItem()
{
Name = "彭希炜"+i,
Id = i.ToString(),
Date = "1"+i
});
}
ExcelExportHelper.RenderToExcel<TestItem>(s,url:"D:\\Test.xls");
注意 传入的Excel文件一定要存在,并且要有读写权限,可以右键文件属性->安全->编辑->添加一个Everyone用户权限全开应用就ok了。