string wherestr = "";
string starttime = Web.DLL.Utility.RequestUtility.GetString("starttime");
string endtime = Web.DLL.Utility.RequestUtility.GetString("endtime");
if (!string.IsNullOrEmpty(starttime.Trim()) && !string.IsNullOrEmpty(endtime.Trim()))
{
wherestr += " and addtime >= '" + Convert.ToDateTime(starttime).AddDays(-1) + "' and addtime <= '" + Convert.ToDateTime(endtime).AddDays(1) + "' ";
}
string onecity_check = Web.DLL.Utility.RequestUtility.GetString("onecity_check");
string twocity_check = Web.DLL.Utility.RequestUtility.GetString("twocity_check");
if (!string.IsNullOrEmpty(twocity_check))
{
wherestr += " and twocity_check='" + twocity_check + "'";
}
string leibie = Web.DLL.Utility.RequestUtility.GetString("leibie");
if (leibie != "0")
{
wherestr += " and leibie='" + leibie + "'";
}
int status = Web.DLL.Utility.RequestUtility.GetInt("status", -99);
if (status != -99)
{
wherestr += " and status =" + status;
}
List<Web.DLL.wxItem.cityadmin_mendian> excel_list = Web.DLL.wxItem.cityadmin_mendian.GetListbywhere(GlobalData.GetDBHelper(), wherestr + " order by id desc");
IWorkbook workbook = new HSSFWorkbook();//创建Excel工作簿(Excel97-2003)
ISheet sheet = workbook.CreateSheet("sheet1");//在工作簿中创建工作表
#region 第一行是标题行
IRow row = sheet.CreateRow(0);//创建标题行
row.Height = 30 * 20;
ICell cellTitle0 = row.CreateCell(0); //创建标题列
ICell cellTitle1 = row.CreateCell(1);
ICell cellTitle2 = row.CreateCell(2);
ICell cellTitle3 = row.CreateCell(3);
ICell cellTitle4 = row.CreateCell(4);
ICell cellTitle5 = row.CreateCell(5);
ICell cellTitle6 = row.CreateCell(6);
ICell cellTitle7 = row.CreateCell(7);
ICell cellTitle8 = row.CreateCell(8); //创建标题列
ICell cellTitle9 = row.CreateCell(9);
ICell cellTitle10 = row.CreateCell(10);
ICell cellTitle11 = row.CreateCell(11);
ICell cellTitle12 = row.CreateCell(12);
ICell cellTitle13 = row.CreateCell(13);
ICell cellTitle14 = row.CreateCell(14);
ICell cellTitle15 = row.CreateCell(15);
ICell cellTitle16 = row.CreateCell(16); //创建标题列
#region 表头行样式
ICellStyle CellStyle = workbook.CreateCellStyle();
CellStyle.WrapText = true;//设置换行
CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中
CellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
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;
IFont titleFont = workbook.CreateFont();
titleFont.FontHeight = 13 * 13;
titleFont.Color = HSSFColor.Black.Index;
titleFont.Boldweight = (short)FontBoldWeight.Bold;
CellStyle.SetFont(titleFont);
#endregion
cellTitle0.SetCellValue("数据记录(总条数:" + excel_list.Count + ")"); //设置标题行内容
cellTitle0.CellStyle = CellStyle;
cellTitle1.CellStyle = CellStyle;
cellTitle2.CellStyle = CellStyle;
cellTitle3.CellStyle = CellStyle;
cellTitle4.CellStyle = CellStyle;
cellTitle5.CellStyle = CellStyle;
cellTitle6.CellStyle = CellStyle;
cellTitle7.CellStyle = CellStyle;
cellTitle8.CellStyle = CellStyle;
cellTitle9.CellStyle = CellStyle;
cellTitle10.CellStyle = CellStyle;
cellTitle11.CellStyle = CellStyle;
cellTitle12.CellStyle = CellStyle;
cellTitle13.CellStyle = CellStyle;
cellTitle14.CellStyle = CellStyle;
cellTitle15.CellStyle = CellStyle;
cellTitle16.CellStyle = CellStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 16));//合并单元格
#endregion
#region 表头行
row = sheet.CreateRow(1);//在工作表中创建第2行
ICell cell_0 = row.CreateCell(0);//在行中创建单元格
cell_0.SetCellValue("门店经营名称");
cell_0.CellStyle = CellStyle;
sheet.AutoSizeColumn(0);
ICell cell_1 = row.CreateCell(1);//在行中创建单元格
cell_1.SetCellValue("门店面积");
cell_1.CellStyle = CellStyle;
sheet.AutoSizeColumn(1);
ICell cell_2 = row.CreateCell(2);//在行中创建单元格
cell_2.SetCellValue("经营地址");
cell_2.CellStyle = CellStyle;
sheet.AutoSizeColumn(2);
ICell cell_3 = row.CreateCell(3);//在行中创建单元格
cell_3.SetCellValue("营业执照注册名称");
cell_3.CellStyle = CellStyle;
sheet.AutoSizeColumn(3);
ICell cell_4 = row.CreateCell(4);//在行中创建单元格
cell_4.SetCellValue("营业执照信用代码");
cell_4.CellStyle = CellStyle;
sheet.AutoSizeColumn(4);
ICell cell_5 = row.CreateCell(5);//在行中创建单元格
cell_5.SetCellValue("门店所属省");
cell_5.CellStyle = CellStyle;
sheet.AutoSizeColumn(5);
ICell cell_6 = row.CreateCell(6);//在行中创建单元格
cell_6.SetCellValue("门店所属市");
cell_6.CellStyle = CellStyle;
sheet.AutoSizeColumn(6);
ICell cell_7 = row.CreateCell(7);//在行中创建单元格
cell_7.SetCellValue("门店位置");
cell_7.CellStyle = CellStyle;
sheet.AutoSizeColumn(7);
ICell cell_8 = row.CreateCell(8);//在行中创建单元格
cell_8.SetCellValue("门店主营产品类别");
cell_8.CellStyle = CellStyle;
sheet.AutoSizeColumn(8);
ICell cell_9 = row.CreateCell(9);//在行中创建单元格
cell_9.SetCellValue("2022年门店销售额");
cell_9.CellStyle = CellStyle;
sheet.AutoSizeColumn(9);
ICell cell_10 = row.CreateCell(10);//在行中创建单元格
cell_10.SetCellValue("2023年门店销售额");
cell_10.CellStyle = CellStyle;
sheet.AutoSizeColumn(10);
ICell cell_11 = row.CreateCell(11);//在行中创建单元格
cell_11.SetCellValue("2022销售冠军产品名称与销售额");
cell_11.CellStyle = CellStyle;
sheet.AutoSizeColumn(11);
ICell cell_12 = row.CreateCell(12);//在行中创建单元格
cell_12.SetCellValue("2023销售冠军产品名称与销售额");
cell_12.CellStyle = CellStyle;
sheet.AutoSizeColumn(12);
ICell cell_13 = row.CreateCell(13);//在行中创建单元格
cell_13.SetCellValue("法定代表人姓名");
cell_13.CellStyle = CellStyle;
sheet.AutoSizeColumn(13);
ICell cell_14 = row.CreateCell(14);//在行中创建单元格
cell_14.SetCellValue("法定代表人联系电话");
cell_14.CellStyle = CellStyle;
sheet.AutoSizeColumn(14);
ICell cell_15 = row.CreateCell(15);//在行中创建单元格
cell_15.SetCellValue("门店联系人");
cell_15.CellStyle = CellStyle;
sheet.AutoSizeColumn(15);
ICell cell_16 = row.CreateCell(16);//在行中创建单元格
cell_16.SetCellValue("门店联系人电话");
cell_16.CellStyle = CellStyle;
sheet.AutoSizeColumn(16);
#endregion
#region 内容行样式
ICellStyle CellStyle_value = workbook.CreateCellStyle();
CellStyle_value.WrapText = true;//设置换行
CellStyle_value.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中
CellStyle_value.VerticalAlignment = VerticalAlignment.Center;//垂直居中
CellStyle_value.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
CellStyle_value.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
CellStyle_value.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
CellStyle_value.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
IFont titleFont_value = workbook.CreateFont();
titleFont_value.FontHeight = 13 * 13;
//titleFont_value.Color = HSSFColor.Grey80Percent.Index;
//titleFont_value.Boldweight = 500;
CellStyle_value.SetFont(titleFont_value);
#endregion
int rowindex = 1;
foreach (Web.DLL.wxItem.cityadmin_mendian item in excel_list)
{
rowindex++;
row = sheet.CreateRow(rowindex);//从第2行开始创建行
row.Height = 30 * 30;//内容行高
for (int j = 0; j <= 16; j++)
{
ICell cell_00 = row.CreateCell(j);//在这行创建列单元格
cell_00.CellStyle = CellStyle_value;
if (j == 0)
{
cell_00.SetCellValue(item.name);
//sheet.SetColumnWidth(j, (item.tidan_no.ToString().Length * 2) * 256);
sheet.SetColumnWidth(j, 12 * 256);
}
else if (j == 1)
{
cell_00.SetCellValue(item.mianji);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 256);
}
else if (j == 2)
{
cell_00.SetCellValue(item.address);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 256);
}
else if (j == 3)
{
cell_00.SetCellValue(item.zhizhaoname);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 256);
}
else if (j == 4)
{
cell_00.SetCellValue(item.daima);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 5)
{
cell_00.SetCellValue(item.onecityname_check);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 6)
{
cell_00.SetCellValue(item.twocityname_check);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 7)
{
cell_00.SetCellValue(item.weizhi);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 8)
{
cell_00.SetCellValue(item.leibie);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 9)
{
cell_00.SetCellValue(item.saleprice_2022);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 10)
{
cell_00.SetCellValue(item.saleprice_2023);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 11)
{
cell_00.SetCellValue(item.salepricename_2022);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 12)
{
cell_00.SetCellValue(item.salepricename_2023);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 13)
{
cell_00.SetCellValue(item.peoplename);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 14)
{
cell_00.SetCellValue(item.peoplephone);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 15)
{
cell_00.SetCellValue(item.linkname);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
else if (j == 16)
{
cell_00.SetCellValue(item.linkphone);
//sheet.SetColumnWidth(j, (item.typename.Length * 2) * 256);
sheet.SetColumnWidth(j, 15 * 2 * 256);
}
}
}
//写入文件 弹出文件保存
string fileName = string.Format("{0:yyyyMMddHHmmssffff}", DateTime.Now);
HttpResponse httpResponse = HttpContext.Current.Response;
httpResponse.Clear();
httpResponse.Buffer = true;
httpResponse.Charset = Encoding.UTF8.BodyName;
httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
httpResponse.ContentEncoding = Encoding.UTF8;
httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";
workbook.Write(httpResponse.OutputStream);
httpResponse.End();
NPOI导出excel实例,替换查询语句即用
最新推荐文章于 2024-07-14 16:32:48 发布