protected void btnExcel_Click(object sender, EventArgs e)
{
#region
IWorkbook book = new HSSFWorkbook();
ISheet sheet1 = book.CreateSheet("Sheet1");
IRow row1 = sheet1.CreateRow(0);
row1.HeightInPoints = 20;
#endregion
#region 表头样式及字体
ICellStyle headStyle = book.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = book.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 500;
headStyle.SetFont(font);
#endregion
#region
ICellStyle bodyStyle = book.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
//headStyle.Alignment = CellHorizontalAlignment;
IFont fontbody = book.CreateFont();
fontbody.FontHeightInPoints = 10;
fontbody.Boldweight = 500;
bodyStyle.SetFont(fontbody);
#endregion
#region 添加背景颜色
ICellStyle cellStyle = book.CreateCellStyle();
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
cellStyle.Alignment = HorizontalAlignment.Center;
#endregion
#region 把数据给npoi
DataTable dt = new DataTable();
if (!txtWinBidDateTimeStart.Text.Trim().Equals("") && !txtWinBidDateTimeEnd.Text.Trim().Equals(""))
{
string strWhere = " and HH_LensusAmount.WinBidDateTime>='" + txtWinBidDateTimeStart.Text.Trim() + "' and HH_LensusAmount.WinBidDateTime<='" + txtWinBidDateTimeEnd.Text.Trim() + "'";
dt = _bll.WinBidData(strWhere.ToString()).Tables[0];
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Script", "alert(\'请选择中标日期区间\');", true);
return;
}
if (dt.Rows.Count > 0)
{
string strs = "序号,客户,项目名称,编号,中标日期,报价人,审核人,中标金额,成本价,备注";
string[] strArry = strs.Split(',');
for (int i = 0; i < strArry.Length; i++)
{
row1.CreateCell(i).SetCellValue(strArry[i]);
row1.GetCell(i).CellStyle = headStyle;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.HeightInPoints = 15;
rowtemp.CreateCell(0).SetCellValue(i + 1); //序号
rowtemp.GetCell(0).CellStyle = bodyStyle;
sheet1.SetColumnWidth(0, 5 * 256);
rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["客户"].ToString()); //客户
rowtemp.GetCell(1).CellStyle = bodyStyle;
sheet1.SetColumnWidth(1, 20 * 256);
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["项目名称"].ToString()); //项目名称
rowtemp.GetCell(2).CellStyle = bodyStyle;
sheet1.SetColumnWidth(2, 25 * 256);
rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["编号"].ToString()); //编号
rowtemp.GetCell(3).CellStyle = bodyStyle;
sheet1.SetColumnWidth(3, 25 * 256);
rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["中标日期"].ToString()); //中标日期
rowtemp.GetCell(4).CellStyle = bodyStyle;
sheet1.SetColumnWidth(4, 10 * 256);
rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["报价人"].ToString()); //报价人
rowtemp.GetCell(5).CellStyle = bodyStyle;
sheet1.SetColumnWidth(5, 10 * 256);
rowtemp.CreateCell(6).SetCellValue(dt.Rows[i]["审核人"].ToString()); //审核人
rowtemp.GetCell(6).CellStyle = bodyStyle;
sheet1.SetColumnWidth(6, 10 * 256);
rowtemp.CreateCell(7).SetCellValue(dt.Rows[i]["中标金额"].ToString()); //中标金额
rowtemp.GetCell(7).CellStyle = bodyStyle;
sheet1.SetColumnWidth(7, 10 * 256);
rowtemp.CreateCell(8).SetCellValue(dt.Rows[i]["成本价"].ToString()); //成本价
rowtemp.GetCell(8).CellStyle = bodyStyle;
sheet1.SetColumnWidth(8, 10 * 256);
rowtemp.CreateCell(9).SetCellValue(dt.Rows[i]["备注"].ToString()); //备注
rowtemp.GetCell(9).CellStyle = bodyStyle;
sheet1.SetColumnWidth(9, 25 * 256);
}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Script", "alert(\'无该日期中标数据\');", true);
return;
}
#endregion
#region 返回工作流
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string FileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Disposition", "attachment;fileName=" + FileName);
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
// context.Response.End();
#endregion
}