添加引用NOPI,官网下载地址NOPI下载地址选择合适的版本,根据你自己的项目的.net版本选择引用的dll有2.0和4.0的,vs2010以上可以选4.0的 剩下的都选2.0的吧,引用一下NPOI.dll就行其他的,不用引用,如果用到nopi的其他功能自行百度引用
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
private void Excel(DataTable dt, string FileName)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
//FileStream file = new FileStream(FileName, FileMode.OpenOrCreate);
System.IO.MemoryStream file = new System.IO.MemoryStream();
workbook.Write(file);
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(FileName) + ".xls");
Response.BinaryWrite(file.ToArray());
workbook = null;
file.Close();
file.Dispose();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</scrit>");
}
}