第三种:利用 Npoi 插件,只需要在项目里引用DLL即可,无需在服务器安装任何插件。
注意:
1、NOPI4.0 支持excel2003和excel2007
2、03和07使用的对象不同,需要区分开来。代码需要写两套。
3、可以保存为文件,也可以直接输出。
public string ListToExcel(List<VmExceldgExcelOut> items, string fileName)
{
string url="";
int rows = items.Count;//不包括字段名
int cols = 0;
int colIndex = 0;
if (rows == 0)
{
return "没有数据!";
}
fileName = "批量导出"+fileName + ".xlsx";// + DateTime.Now.ToString("yyyyMMddhhmmss")
url = fileUrl + fileName;//返回下载路径
fileName = filepath + fileName;
try
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("tempData");
ICellStyle cellStyle = xssfworkbook.CreateCellStyle();
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
/*IFont font12 = xssfworkbook.CreateFont();
font12.FontHeightInPoints = 12;
font12.FontName = "宋体";*/
//return url;
#region 生成Excel
//表头
IRow row = sheet.CreateRow(0);
VmExceldgExcelOut temp = new VmExceldgExcelOut();
PropertyInfo[] propertys = temp.GetType().GetProperties();
foreach (PropertyInfo property in propertys)
{
string code = ((DescriptionAttribute)Attribute.GetCustomAttribute(property, typeof(DescriptionAttribute))).Description;// 属性值
if (string.IsNullOrEmpty(code))
continue;
ICell cell = row.CreateCell(colIndex);
cell.CellStyle = cellStyle;
cell.SetCellValue(code);
colIndex++;
}
cols = colIndex;
//数据
for (int r = 0; r < rows; r++)
{
IRow row1 = sheet.CreateRow(r + 1);
temp = items[r];
for (int c = 0; c < cols; c++)
{
propertys = temp.GetType().GetProperties();
object o = propertys[c].GetValue(temp, null);
ICell cell = row1.CreateCell(c);
cell.CellStyle = cellStyle;
if (o == null) o = "";
cell.SetCellValue(o.ToString());
}
}
#endregion
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
/*HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
xssfworkbook = null;
stream.Close();
stream.Dispose();*/
//保存为Excel文件
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
catch (Exception e)
{
return "导出Excel失败,错误信息:" + e.Message;
}
return url;
}
public string DataTableToExcel(DataTable dt, string fileName)
{
string url