- Excel模板
- Helper类
public class Helper<T> where T : class, new()
{
public static byte[] TemplateOutput(List<T> list, string path, int index)
{
if (list.Count() == 0)
{
return new byte[] { };
}
using (MemoryStream ms = new MemoryStream())
{
var entityProperties = list.FirstOrDefault().GetType().GetProperties();
string extension = Path.GetExtension(path);
FileStream fs = File.OpenRead(path);
IWorkbook workbook = null;
if (extension.Equals(".xls"))
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = new XSSFWorkbook(fs);
}
fs.Close();
ISheet sheet = workbook.GetSheetAt(0);
IRow rows = null;
int i = 0;
foreach (var dbitem in list)
{
rows = sheet.CreateRow(i++ + index);
for (int j = 0; j < entityProperties.Length; j++)
{
var prop = entityProperties[j];
var value = prop.GetValue(dbitem)?.ToString();
rows.CreateCell(j).SetCellValue(value ?? "");
}
}
workbook.Write(ms);
return ms.GetBuffer();
}
}
}
- 导出方法以及涉及对象
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
public void ExcelExport()
{
string templetPath = Context.Server.MapPath("/content/test.xls");
List<TableStructure> tableStructure = new List<TableStructure>() {
new TableStructure()
{
Field = "Id",
Type = "int",
Desdescribe = "Desdescribe",
Remark = "Remark"
}
};
byte[] file = Helper<TableStructure>.TemplateOutput(tableStructure, templetPath, 2);
Context.Response.ContentType = "application/vnd.ms-excel";
Context.Response.AddHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmsss") + ".xls");
Context.Response.AddHeader("Content-Transfer-Encoding", "binary");
Context.Response.ContentType = "application/octet-stream";
Context.Response.ContentEncoding = System.Text.Encoding.UTF8;
Context.Response.BinaryWrite(file);
}
public class TableStructure
{
public string Field { get; set; }
public string Type { get; set; }
public string Desdescribe { get; set; }
public string Remark { get; set; }
}