首先安装NPOI 选择项目
如果项目版本过低可以选择低版本的NPOI安装
public void ExportExecl()
{
#region 创建excel
//创建excel工作薄
IWorkbook wb = new HSSFWorkbook();
//IWorkbook wb2 = new XSSFWorkbook();
//创建excel 单元格式
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//设置字体
IFont font = wb.CreateFont();
font.FontHeightInPoints = 18;
font.FontName = "微软雅黑";
cellStyle.SetFont(font);
#endregion
#region 创建表
//创建表
ISheet sh = wb.CreateSheet("学生表");
//设置第一列的宽度
sh.SetColumnWidth(0, 15 * 256);
//创建第一行
IRow row0 = sh.CreateRow(0);
//创建四个单元格
ICell row0ICell0 = row0.CreateCell(0);
ICell row0ICell1 = row0.CreateCell(1);
ICell row0ICell2 = row0.CreateCell(2);
ICell row0ICell3 = row0.CreateCell(3);
//给第一单元格添加内容
row0ICell0.SetCellValue("序号");
row0ICell0.CellStyle = cellStyle;
row0ICell1.SetCellValue("姓名");
row0ICell1.CellStyle = cellStyle;
row0ICell2.SetCellValue("年龄");
row0ICell2.CellStyle = cellStyle;
row0ICell3.SetCellValue("性别");
row0ICell3.CellStyle = cellStyle;
//创建第二行
IRow row1 = sh.CreateRow(1);
//创建四个单元格
ICell row1ICell0 = row1.CreateCell(0);
ICell row1ICell1 = row1.CreateCell(1);
ICell row1ICell2 = row1.CreateCell(2);
ICell row1ICell3 = row1.CreateCell(3);
//给第一单元格添加内容
row1ICell0.SetCellValue("1");
row1ICell0.CellStyle = cellStyle;
row1ICell1.SetCellValue("张三");
row1ICell1.CellStyle = cellStyle;
row1ICell2.SetCellValue("18");
row1ICell2.CellStyle = cellStyle;
row1ICell3.SetCellValue("男");
row1ICell3.CellStyle = cellStyle;
//创建第三行
IRow row2 = sh.CreateRow(2);
//创建四个单元格
ICell row2ICell0 = row2.CreateCell(0);
ICell row2ICell1 = row2.CreateCell(1);
ICell row2ICell2 = row2.CreateCell(2);
ICell row2ICell3 = row2.CreateCell(3);
//给第一单元格添加内容
row2ICell0.SetCellValue("2");
row2ICell0.CellStyle = cellStyle;
row2ICell1.SetCellValue("李花");
row2ICell1.CellStyle = cellStyle;
row2ICell2.SetCellValue("19");
row2ICell2.CellStyle = cellStyle;
row2ICell3.SetCellValue("女");
row2ICell3.CellStyle = cellStyle;
#endregion
try
{
using (FileStream filestream = File.OpenWrite(@"E:\学生信息.xls"))
{
wb.Write(filestream);
Response.Write("<script>alert('导出成功')</script>");
}
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
protected void Button1_Click(object sender, EventArgs e)
{
ExportExecl();
}
导出根据浏览器直接下载或者弹出保存文件框
public void ExportExecl(DataTable dt,string type,string exceldate)
{
int SumMoney = 0;
int SumSingular = 0;
#region 创建excel
//创建excel工作薄
//IWorkbook wb = new HSSFWorkbook();
HSSFWorkbook wb = new HSSFWorkbook();
//创建excel 单元格式
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//设置字体
IFont font = wb.CreateFont();
font.FontHeightInPoints = 18;
font.FontName = "微软雅黑";
cellStyle.SetFont(font);
#endregion
#region 创建表
//创建表
//ISheet sh = wb.CreateSheet("中奖纪录");
ISheet sh = (NPOI.HSSF.UserModel.HSSFSheet)wb.CreateSheet("中奖纪录");
//设置列的宽度
sh.SetColumnWidth(0, 15 * 300);
sh.SetColumnWidth(1, 15 * 300);
sh.SetColumnWidth(2, 15 * 300);
sh.SetColumnWidth(3, 15 * 300);
sh.SetColumnWidth(4, 15 * 300);
//创建表头
IRow dataRow = sh.CreateRow(0);
//创建3个单元格
ICell row0ICell0 = dataRow.CreateCell(0);
ICell row0ICell1 = dataRow.CreateCell(1);
ICell row0ICell2 = dataRow.CreateCell(2);
ICell row0ICell3 = dataRow.CreateCell(3);
ICell row0ICell4 = dataRow.CreateCell(4);
row0ICell0.SetCellValue("中奖号码");
row0ICell0.CellStyle = cellStyle;
row0ICell1.SetCellValue("奖品内容");
row0ICell1.CellStyle = cellStyle;
row0ICell2.SetCellValue("充值金额");
row0ICell2.CellStyle = cellStyle;
row0ICell3.SetCellValue("抽奖日期");
row0ICell3.CellStyle = cellStyle;
row0ICell4.SetCellValue("活动类型");
row0ICell4.CellStyle = cellStyle;
//循环数据从第二行开始追加
for (int i = 0; i < dt.Rows.Count; i++)
{
SumMoney += Convert.ToInt32(dt.Rows[i]["product_money"]);
dataRow = sh.CreateRow(i + 1);
for (int j = 0; j < 5; j++)
{
string td = dt.Rows[i][j].ToString();
dataRow.CreateCell(j).SetCellValue(td);
}
}
SumSingular = dt.Rows.Count;
//追加合计行
dataRow = sh.CreateRow(dt.Rows.Count + 1);
dataRow.CreateCell(0).SetCellValue("合计:");
dataRow.CreateCell(1).SetCellValue("合计单数:"+ SumSingular);
dataRow.CreateCell(2).SetCellValue("合计金额:"+SumMoney);
dataRow.CreateCell(3).SetCellValue(" ");
dataRow.CreateCell(4).SetCellValue(" ");
//dataRow.CreateCell(4).CellStyle = cellStyle;
#endregion
try
{
string formatType = "";
if (type == "1")
{
formatType = "有奖问答";
}
else if (type == "2")
{
formatType = "问卷调查";
}
else if (type == "3")
{
formatType = "每日签到";
}
else if (type == "4")
{
formatType = "全部";
}
string ExcelName = exceldate + "_" + formatType + "_" + DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd") + DateTime.Now.ToString("HH") + DateTime.Now.ToString("mm") + DateTime.Now.ToString("ss") + ".xls";
DownLoad(wb, ExcelName);
}
catch (Exception ex)
{
Response.Write("<script>alert('导出失败请联系管理员')</script>");
}
}
#region 输出到下载文件
/// <summary>
/// 下载文件
/// </summary>
/// <param name="hssfWorkbook">excel工作簿</param>
/// <param name="fileName">文件名</param>
private void DownLoad(HSSFWorkbook hssfWorkbook, string fileName)
{
using (MemoryStream memoryStram = new MemoryStream())
{
//把工作簿写入到内存流中
hssfWorkbook.Write(memoryStram);
//设置输出编码格式
Response.ContentEncoding = System.Text.Encoding.UTF8;
//设置输出流
Response.ContentType = "application/octet-stream";
//防止中文乱码
fileName = HttpUtility.UrlEncode(fileName);
//设置输出文件名
Response.AppendHeader("Content-Disposition", "filename=" + fileName + ".xls");
//输出
Response.BinaryWrite(memoryStram.GetBuffer());
}
}
#endregion
表头也根据表循环
//创建表
ISheet sh = (NPOI.HSSF.UserModel.HSSFSheet)wb.CreateSheet("学生信息");
sh.SetColumnWidth(1, 15 * 350);
sh.SetColumnWidth(4, 15 * 200);
sh.SetColumnWidth(8, 15 * 300);
sh.SetColumnWidth(10, 15 * 300);
//创建表头
IRow dataRow = sh.CreateRow(0);
for (var i = 0; i < dt.Columns.Count; i++)
{
var cell = dataRow.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)
{
dataRow = sh.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}