DLL 下载地址：https://files.cnblogs.com/files/xujunbao/NPOI.rar
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
protected void btn_export_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt = null; //这里为导出的数据源
string path = Server.MapPath("~/FileUpload/Excel/导出" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
TableToExcel2(dt, path); //进行后台文件下载
FileInfo fileInfo = new FileInfo(path);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=报价表" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
Response.WriteFile(fileInfo.FullName);
Response.Flush();
Response.End();
}
public static void TableToExcel2(DataTable dt, string file)
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
int Excel_row = ;
int Excel_col = ;
BLL.jk_activatedTestingItem_base bll = new BLL.jk_activatedTestingItem_base();
DataTable dt_item = new DataTable();
ICellStyle style = workbook.CreateCellStyle();
//设置单元格的样式：水平对齐居中
style.Alignment = HorizontalAlignment.Center;
//垂直居中
style.VerticalAlignment = VerticalAlignment.Center;
//新建一个字体样式对象
IFont font = workbook.CreateFont();
//设置字体加粗样式
font.Boldweight = short.MaxValue;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//数据
for (int i = ; i < dt.Rows.Count; i++)
{
long activateId = TypeConverter.ObjectToLong(dt.Rows[i]["activateId"]);
dt_item = bll.GetList("activateId=" + activateId + " and testingItemLevel=1").Tables[];
if (dt_item == null || dt_item.Rows.Count == )
{
continue;
}
IRow row1 = sheet.CreateRow(Excel_row);
//表头
if (i == )
{
row1 = sheet.CreateRow(Excel_row);
ICell cell = row1.CreateCell();
cell.SetCellValue("产品大类");
sheet.SetColumnWidth(, * );
cell.CellStyle = style; //绑定样式到单元格上
cell = row1.CreateCell();
cell.SetCellValue("检测对象");
sheet.SetColumnWidth(, * );
cell.CellStyle = style;
cell = row1.CreateCell();
cell.SetCellValue("检测标准");
sheet.SetColumnWidth(, * );
cell.CellStyle = style;
cell = row1.CreateCell();
cell.SetCellValue("收样说明");
sheet.SetColumnWidth(, * );
cell.CellStyle = style;
}
string value = "";
for (int j = ; j < dt_item.Rows.Count; j++)
{
if (j == && i == )
{
ICell cell = row1.CreateCell();
cell.SetCellValue("检测项目");
sheet.SetColumnWidth(, * );
cell.CellStyle = style;
cell = row1.CreateCell();
cell.SetCellValue("检测周期");
cell.CellStyle = style;
cell = row1.CreateCell();
cell.SetCellValue("检测费用");
cell.CellStyle = style;
cell = row1.CreateCell();
cell.SetCellValue("检测部门");
sheet.SetColumnWidth(, * );
cell.CellStyle = style;
Excel_row++;
}
row1 = sheet.CreateRow(Excel_row);
string deptName = TypeConverter.ObjectToString(dt.Rows[i]["parameterDeptName"]);
for (int ij = ; ij < dt.Columns.Count; ij++)
{
string ColumnName = dt.Columns[ij].ColumnName;
switch (ColumnName)
{
case "productTypeName":
value = TypeConverter.ObjectToString(dt.Rows[i]["productTypeName"]);
ICell cell0 = row1.CreateCell();
cell0.SetCellValue(value);
cell0.CellStyle = style;
break;
case "testingObject":
value = TypeConverter.ObjectToString(dt.Rows[i]["testingObject"]);
ICell cell1 = row1.CreateCell();
cell1.SetCellValue(value);
cell1.CellStyle = style;
break;
case "testingbasisId":
value = TypeConverter.ObjectToString(dt.Rows[i]["testingbasisCode"]) + TypeConverter.ObjectToString(dt.Rows[i]["testingbasisChiName"]) + TypeConverter.ObjectToString(dt.Rows[i]["parameterComment"]);
ICell cell2 = row1.CreateCell();
cell2.SetCellValue(value);
cell2.CellStyle = style;
break;
case "remark":
value = TypeConverter.ObjectToString(dt.Rows[i]["remark"]);
ICell cell3 = row1.CreateCell();
cell3.SetCellValue(value);
cell3.CellStyle = style;
break;
default:
value = "";
break;
}
}
for (int jj = ; jj < dt_item.Columns.Count; jj++)
{
string ColumnName = dt_item.Columns[jj].ColumnName;
switch (ColumnName)
{
case "testingItemName":
value = TypeConverter.ObjectToString(dt_item.Rows[j]["testingItemName"]);
ICell cell4 = row1.CreateCell();
cell4.SetCellValue(value);
break;
case "testingTotalDay":
value = TypeConverter.ObjectToString(dt_item.Rows[j]["testingTotalDay"]);
ICell cell5 = row1.CreateCell();
cell5.SetCellValue(value);
break;
case "testingFee":
value = TypeConverter.ObjectToString(dt_item.Rows[j]["testingFee"]);
ICell cell6 = row1.CreateCell();
cell6.SetCellValue(value);
break;
default:
value = "";
break;
}
}
ICell cell7 = row1.CreateCell();
cell7.SetCellValue(deptName);
Excel_row++;
}
sheet.AddMergedRegion(new CellRangeAddress(Excel_row - dt_item.Rows.Count, Excel_row - , , )); //跨行 ：开始行，结束行，开始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(Excel_row - dt_item.Rows.Count, Excel_row - , , ));
sheet.AddMergedRegion(new CellRangeAddress(Excel_row - dt_item.Rows.Count, Excel_row - , , ));
sheet.AddMergedRegion(new CellRangeAddress(Excel_row - dt_item.Rows.Count, Excel_row - , , ));
} //转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray(); //保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, , buf.Length);
fs.Flush();
}
}