效果图： 周月季效果一样
protected void btn_impor()
{
#region 样式
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = CellHorizontalAlignment.CENTER;
headStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
headStyle.WrapText = true;
HSSFSheet sheet = null;
string HeadcolumnName = "";
//第一行样式
HSSFCellStyle style2 = workbook.CreateCellStyle();
style2.BorderTop = NPOI.HSSF.UserModel.CellBorderType.THIN;
style2.BorderBottom = NPOI.HSSF.UserModel.CellBorderType.THIN;
style2.BorderLeft = NPOI.HSSF.UserModel.CellBorderType.THIN;
style2.BorderRight = NPOI.HSSF.UserModel.CellBorderType.THIN;
HSSFFont font2 = workbook.CreateFont();
font2.FontHeightInPoints = 10;
//font.Color=HSSFColor.RED.index;
font2.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font2.FontName = "宋体";
style2.Alignment = CellHorizontalAlignment.CENTER;
style2.VerticalAlignment = CellVerticalAlignment.CENTER;
style2.SetFont(font2);
//中间数据样式
HSSFCellStyle style1 = workbook.CreateCellStyle();
//style1.BorderTop = NPOI.HSSF.UserModel.CellBorderType.THIN;
//style1.BorderBottom = NPOI.HSSF.UserModel.CellBorderType.THIN;
//style1.BorderLeft = NPOI.HSSF.UserModel.CellBorderType.THIN;
//style1.BorderRight = NPOI.HSSF.UserModel.CellBorderType.THIN;
style1.Alignment = CellHorizontalAlignment.CENTER;
style1.VerticalAlignment = CellVerticalAlignment.CENTER;
//数据样式
HSSFCellStyle style3 = workbook.CreateCellStyle();
style3.Rotation = 90;
HSSFFont font3 = workbook.CreateFont();
font3.FontHeightInPoints = 10;
//font.Color=HSSFColor.RED.index;
font3.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font3.FontName = "宋体";
style3.Alignment = CellHorizontalAlignment.CENTER;
style3.VerticalAlignment = CellVerticalAlignment.CENTER;
style3.SetFont(font3);
//表头样式
HSSFCellStyle style = workbook.CreateCellStyle();
style.BorderTop = NPOI.HSSF.UserModel.CellBorderType.THIN;
style.BorderBottom = NPOI.HSSF.UserModel.CellBorderType.THIN;
style.BorderLeft = NPOI.HSSF.UserModel.CellBorderType.THIN;
style.BorderRight = NPOI.HSSF.UserModel.CellBorderType.THIN;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
//font.Color=HSSFColor.RED.index;
font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
font.FontName = "宋体";
style.Alignment = CellHorizontalAlignment.CENTER;
style.VerticalAlignment = CellVerticalAlignment.CENTER;
style.SetFont(font);
GridPanel gp;
string wordType;
GetGrid(out gp, out wordType);
List<ArtificialWeekOverhaulPlanEntity> weekPlanlist = new List<ArtificialWeekOverhaulPlanEntity>();
List<ArtificialWeekOverhaulPlanEntity> weekDeviceIdlist = new List<ArtificialWeekOverhaulPlanEntity>();
List<ArtificialDeviceYearOverhaulPlanEntity> monthPlanList = new List<ArtificialDeviceYearOverhaulPlanEntity>();
List<ArtificialDeviceYearOverhaulPlanEntity> monthDeviceIdList = new List<ArtificialDeviceYearOverhaulPlanEntity>();
List<ArtificialSeasonOverhaulPlanEntity> seasonPlanList = new List<ArtificialSeasonOverhaulPlanEntity>();
List<ArtificialSeasonOverhaulPlanEntity> deviceIdList = new List<ArtificialSeasonOverhaulPlanEntity>();
string type = Convert.ToString(select_type.SelectedItem.Text);//设备种类
string DeviceType = Inspect_box.SelectedItem.Text;//设备类别
#endregion
switch (wordType)
{
#region 周计划
case "周计划":
//weekPlanlist = ArtificialWeekPlanDomain.FindWeekBaoBiaoData("周计划", Convert.ToInt32(Convert.ToDateTime(week_year.Value.ToString()).Year), 0);
//weekDeviceIdlist = ArtificialWeekPlanDomain.FindWeekdeviceIdData(Convert.ToInt32(Convert.ToDateTime(week_year.Value.ToString()).Year), 0);
int lenzhou = 0;
int lenzhou1 = 0;
int zhouYear = Convert.ToDateTime(week_year.Value.ToString()).Year;
//一年的周数
int zhouSum = 54;
HeadcolumnName = "设备巡检周计划年度报表";
sheet = workbook.CreateSheet("统计报表");
sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, 9));
HSSFRow rowZhou = sheet.CreateRow(0);
HSSFCell cellZhou = rowZhou.CreateCell(0);
cellZhou.SetCellValue(HeadcolumnName);
cellZhou.CellStyle.Alignment = CellHorizontalAlignment.CENTER;
cellZhou.CellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
cellZhou.CellStyle = style;
for (int zhou = 1; zhou <= zhouSum + 1; zhou++)
{
List<ArtificialWeekOverhaulPlanEntity> listZhou = new List<ArtificialWeekOverhaulPlanEntity>();
List<ArtificialWeekOverhaulPlanEntity> listZhou1 = new List<ArtificialWeekOverhaulPlanEntity>();
int jhSum = 0;
int wcSum = 0;
int fsgzSum = 0;
int wcgzSum = 0;
int rowLenYue = 0;
if (zhou != zhouSum + 1)
{
if (zhou == 1)
{
listZhou = ArtificialWeekPlanDomain.FindWeekNianDuBaoBiaoData("周计划", zhouYear, zhou);
lenzhou = 3;
lenzhou1 = 3 + listZhou.Count() + zhou * 4 - 1;
rowLenYue = 6;
}
else
{
listZhou = ArtificialWeekPlanDomain.FindWeekNianDuBaoBiaoData("周计划", zhouYear, zhou);
listZhou1 = ArtificialWeekPlanDomain.FindWeekNianDuBaoBiaoData("周计划", zhouYear, zhou - 1);
lenzhou = 3 + listZhou1.Count() + (zhou - 1) * 4;
lenzhou1 = 3 + listZhou.Count() + zhou * 4 - 1;
rowLenYue = 6 + listZhou1.Count() + (zhou - 1) * 4;
}
weekPlanlist = ArtificialWeekPlanDomain.FindWeekBaoBiaoData("周计划", Convert.ToInt32(Convert.ToDateTime(week_year.Value.ToString()).Year), zhou);
weekDeviceIdlist = ArtificialWeekPlanDomain.FindWeekdeviceIdData(Convert.ToInt32(Convert.ToDateTime(week_year.Value.ToString()).Year), zhou);
}
else
{
weekPlanlist = ArtificialWeekPlanDomain.FindWeekBaoBiaoData("周计划", Convert.ToInt32(Convert.ToDateTime(week_year.Value.ToString()).Year), 0);
weekDeviceIdlist = ArtificialWeekPlanDomain.FindWeekdeviceIdData(Convert.ToInt32(Convert.ToDateTime(week_year.Value.ToString()).Year), 0);
listZhou = ArtificialWeekPlanDomain.FindWeekNianDuBaoBiaoData("周计划", zhouYear, zhou);
listZhou1 = ArtificialWeekPlanDomain.FindWeekNianDuBaoBiaoData("周计划", zhouYear, zhou);
lenzhou = 3 + listZhou1.Count() + (zhou - 1) * 4;
lenzhou1 = 3 + listZhou.Count() + zhou * 4 + weekPlanlist.Count() - 1;
rowLenYue = 6 + listZhou1.Count() + (zhou - 1) * 4;
}
HSSFRow row1 = sheet.CreateRow(lenzhou);
sheet.AddMergedRegion(new CellRangeAddress(lenzhou, lenzhou + 1, 2, 2));
HSSFCell row1Cell0 = row1.CreateCell(2);
row1Cell0.SetCellValue("项目设备分类");
row1Cell0.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenzhou, lenzhou, 3, 4));
HSSFCell row1Cell9 = row1.CreateCell(3);
row1Cell9.SetCellValue("巡检");
row1Cell9.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenzhou, lenzhou, 5, 6));
HSSFCell row1Cell13 = row1.CreateCell(5);
row1Cell13.SetCellValue("故障修");
row1Cell13.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenzhou, lenzhou + 1, 7, 7));
HSSFCell row1Cell14 = row1.CreateCell(7);
row1Cell14.SetCellValue("设备质量分析");
row1Cell14.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenzhou, lenzhou + 1, 8, 8));
HSSFCell row1Cell15 = row1.CreateCell(8);
row1Cell15.SetCellValue("设备变更情况");
row1Cell15.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenzhou, lenzhou + 1, 9, 9));
HSSFCell row1Cell16 = row1.CreateCell(9);
row1Cell16.SetCellValue("备注");
row1Cell16.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenzhou, lenzhou1, 0, 0));
HSSFCell row1Ce2 = row1.CreateCell(0);
row1Ce2.SetCellValue("设备巡检");
row1Ce2.CellStyle = style3;
sheet.AddMergedRegion(new CellRangeAddress(lenzhou, lenzhou1, 1, 1));
HSSFCell row1Ce3 = row1.CreateCell(1);
if (zhou == 55)
{
row1Ce3.SetCellValue("1-54周(全年)");
}
else
{
row1Ce3.SetCellValue("" + zhou + "周");
}
row1Ce3.CellStyle = style3;
string[] row2columns = new string[4] { "计划台/次", "完成台/次", "发生故障件/次", "完成故障件/次" };
HSSFRow row2 = sheet.CreateRow(lenzhou + 1);
for (int i = 0; i < row2columns.Length; i++)
{
sheet.AddMergedRegion(new CellRangeAddress(lenzhou + 1, lenzhou + 1, i + 3, i + 3));
HSSFCell row2Cell0 = row2.CreateCell(i + 3);
row2Cell0.SetCellValue(row2columns[i]);
row2Cell0.CellStyle = style1;
}
for (int i = 0; i < weekPlanlist.Count(); i++)//周计划
{
HSSFRow row = sheet.CreateRow(i + rowLenYue);//创建行号
ArtificialWeekOverhaulPlanEntity entity = weekPlanlist[i];
List<ArtificialWeekOverhaulPlanEntity> entityList = weekDeviceIdlist.Where(p => p.Type == entity.Type).ToList();
bx_infoDomain bx = new bx_infoDomain();
string startTime = "";
string endTime = "";
string deviceId = "";
for (int a = 0; a < entityList.Count(); a++)
{
if (a == entityList.Count() - 1)
{
deviceId += "'" + entityList[a].DeviceId + "'";
}
else
{
deviceId += "'" + entityList[a].DeviceId + "'" + ",";
}
}
DateTime mDatetime = new DateTime(Convert.ToInt32(Convert.ToDateTime(week_year.Value.ToString()).Year), 1, 1);//year为要求的那一年
if (zhou != 55)
{
startTime = mDatetime.AddDays((zhou - 1) * 7).ToString();//第N周第一天
endTime = mDatetime.AddDays((zhou - 1) * 7 + 6).ToString();//第N周最后一天
}
else
{
startTime = mDatetime.AddDays((1 - 1) * 7).ToString();
endTime = mDatetime.AddDays((54 - 1) * 7).ToString();
}
List<bx_infoEntity> ds = bx.getNumberByTimeNo(wordType, startTime, endTime, deviceId);
List<bx_infoEntity> dt = ds.Where(p => p.jindu == 4).ToList();
string[] array = new string[8];
array[0] = entity.Type;
array[1] = entity.sumCount;
array[2] = entity.wcCount;
array[3] = ds.Count().ToString();
array[4] = dt.Count().ToString();
array[5] = "";
array[6] = "";
array[7] = "";
for (int j = 0; j < array.Length; j++)
{
HSSFCell cell = row.CreateCell(j + 2);
cell.SetCellValue(array[j]);
cell.CellStyle = style1;
}
jhSum += Convert.ToInt32(entity.sumCount);
wcSum += Convert.ToInt32(entity.wcCount);
fsgzSum += ds.Count();
wcgzSum += dt.Count();
}
HSSFRow rowzhou = sheet.CreateRow(lenzhou1);//创建行号
string[] arrayzhou = new string[8];
arrayzhou[0] = "总计";
arrayzhou[1] = jhSum.ToString();
arrayzhou[2] = wcSum.ToString();
arrayzhou[3] = fsgzSum.ToString();
arrayzhou[4] = wcgzSum.ToString();
arrayzhou[5] = "";
arrayzhou[6] = "";
arrayzhou[7] = "";
for (int j = 0; j < arrayzhou.Length; j++)
{
HSSFCell cell = rowzhou.CreateCell(j + 2);
cell.SetCellValue(arrayzhou[j]);
cell.CellStyle = style1;
}
}
#endregion
break;
#region 月计划
case "月计划":
int lenyue = 0;
int lenyue1 = 0;
int YueYear = DateTime.Now.Year;
if (date_year.Value.ToString() != "0001/1/1 0:00:00" && date_year.Value != null)
{
YueYear = Convert.ToDateTime(date_year.Value.ToString()).Year;
}
string staYue = YueYear + "-01";
string endYue = "";
string endYue1 = "";
HeadcolumnName = "设备巡检月计划年度报表";
sheet = workbook.CreateSheet("统计报表");
sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, 9));
HSSFRow rowYue = sheet.CreateRow(0);
HSSFCell cellYue = rowYue.CreateCell(0);
cellYue.SetCellValue(HeadcolumnName);
cellYue.CellStyle.Alignment = CellHorizontalAlignment.CENTER;
cellYue.CellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
cellYue.CellStyle = style;
for (int ji = 1; ji <= 13; ji++)
{
List<ArtificialDeviceYearOverhaulPlanEntity> lstYue = new List<ArtificialDeviceYearOverhaulPlanEntity>();
List<ArtificialDeviceYearOverhaulPlanEntity> lstYue1 = new List<ArtificialDeviceYearOverhaulPlanEntity>();
if (ji != 13)
{
if (ji < 10)
{
endYue = YueYear + "-0" + ji + "";
}
else
{
endYue = YueYear + "-" + ji + "";
}
lstYue = ArtificialPlanDomain.GetNianduBaoBiaodataBySearch(staYue, endYue);
if (ji < 11)
{
endYue1 = YueYear + "-0" + (ji - 1) + "";
}
else
{
endYue1 = YueYear + "-" + (ji - 1) + "";
}
if (ji != 1)
{
lstYue1 = ArtificialPlanDomain.GetNianduBaoBiaodataBySearch(staYue, endYue1);
}
if (ji == 1)
{
lenyue = 3;
lenyue1 = 3 + lstYue.Count() + ji * 4 - 1;
}
else
{
lenyue = 3 + lstYue1.Count() + (ji - 1) * 4;
lenyue1 = 3 + lstYue.Count() + ji * 4 - 1;
}
}
else
{
lstYue = ArtificialPlanDomain.GetNianduBaoBiaodataBySearch(YueYear + "-01", YueYear + "-12");
lstYue1 = ArtificialPlanDomain.GetNianduBaoBiaodataBySearch(YueYear + "-01", YueYear + "-12");
monthPlanList = ArtificialPlanDomain.GetBaoBiaodataBySearch(YueYear + "-01", YueYear + "-12");
lenyue = 3 + lstYue1.Count() + (ji - 1) * 4;
lenyue1 = 3 + lstYue.Count() + ji * 4 + monthPlanList.Count() - 1;
}
#region 样式
HSSFRow row1 = sheet.CreateRow(lenyue);
sheet.AddMergedRegion(new CellRangeAddress(lenyue, lenyue + 1, 2, 2));
HSSFCell row1Cell0 = row1.CreateCell(2);
row1Cell0.SetCellValue("项目设备分类");
row1Cell0.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenyue, lenyue, 3, 4));
HSSFCell row1Cell9 = row1.CreateCell(3);
row1Cell9.SetCellValue("巡检");
row1Cell9.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenyue, lenyue, 5, 6));
HSSFCell row1Cell13 = row1.CreateCell(5);
row1Cell13.SetCellValue("故障修");
row1Cell13.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenyue, lenyue + 1, 7, 7));
HSSFCell row1Cell14 = row1.CreateCell(7);
row1Cell14.SetCellValue("设备质量分析");
row1Cell14.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenyue, lenyue + 1, 8, 8));
HSSFCell row1Cell15 = row1.CreateCell(8);
row1Cell15.SetCellValue("设备变更情况");
row1Cell15.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenyue, lenyue + 1, 9, 9));
HSSFCell row1Cell16 = row1.CreateCell(9);
row1Cell16.SetCellValue("备注");
row1Cell16.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(lenyue, lenyue1, 0, 0));
HSSFCell row1Ce2 = row1.CreateCell(0);
row1Ce2.SetCellValue("设备巡检");
row1Ce2.CellStyle = style3;
sheet.AddMergedRegion(new CellRangeAddress(lenyue, lenyue1, 1, 1));
HSSFCell row1Ce3 = row1.CreateCell(1);
if (ji == 13)
{
row1Ce3.SetCellValue("1-12月(全年)");
}
else
{
row1Ce3.SetCellValue("" + ji + "月");
}
row1Ce3.CellStyle = style3;
string[] row2columns = new string[4] { "计划台/次", "完成台/次", "发生故障件/次", "完成故障件/次" };
HSSFRow row2 = sheet.CreateRow(lenyue + 1);
for (int i = 0; i < row2columns.Length; i++)
{
sheet.AddMergedRegion(new CellRangeAddress(lenyue + 1, lenyue + 1, i + 3, i + 3));
HSSFCell row2Cell0 = row2.CreateCell(i + 3);
row2Cell0.SetCellValue(row2columns[i]);
row2Cell0.CellStyle = style1;
}
#endregion
#region 数据绑定
int rowLenYue = 0;
int jhSumYue = 0;
int wcSumYue = 0;
int fsgzSumYue = 0;
int wcgzSumYue = 0;
string startTime = "";
string endTime = "";
if (ji != 13)
{
if (ji < 10)
{
startTime = YueYear + "-0" + ji + "";
endTime = YueYear + "-0" + ji + "";
}
else
{
startTime = YueYear + "-" + ji + "";
endTime = YueYear + "-" + ji + "";
}
monthPlanList = ArtificialPlanDomain.GetBaoBiaodataBySearch(startTime, endTime);
monthDeviceIdList = ArtificialPlanDomain.GetDeviceiddataBySearch(startTime, endTime);
if (ji == 1)
{
rowLenYue = 6;
}
else
{
rowLenYue = 6 + lstYue1.Count() + (ji - 1) * 4;
}
}
else
{
monthPlanList = ArtificialPlanDomain.GetBaoBiaodataBySearch(YueYear + "-01", YueYear + "-12");
monthDeviceIdList = ArtificialPlanDomain.GetDeviceiddataBySearch(YueYear + "-01", YueYear + "-12");
rowLenYue = 6 + lstYue1.Count() + (ji - 1) * 4;
}
for (int i = 0; i < monthPlanList.Count(); i++)//月计划
{
HSSFRow row = sheet.CreateRow(i + rowLenYue);//创建行号
ArtificialDeviceYearOverhaulPlanEntity entity = monthPlanList[i];
List<ArtificialDeviceYearOverhaulPlanEntity> entityList = monthDeviceIdList.Where(p => p.Type == entity.Type).ToList();
bx_infoDomain bx = new bx_infoDomain();
string deviceId = "";
for (int a = 0; a < entityList.Count(); a++)
{
if (a == entityList.Count() - 1)
{
deviceId += "'" + entityList[a].DeviceId + "'";
}
else
{
deviceId += "'" + entityList[a].DeviceId + "'" + ",";
}
}
List<bx_infoEntity> ds = bx.getNumberByTimeNo(wordType, startTime, endTime, deviceId);
List<bx_infoEntity> dt = ds.Where(p => p.jindu == 4).ToList();
string[] array = new string[8];
array[0] = entity.Type;
array[1] = entity.sumCount;
array[2] = entity.wcCount;
array[3] = ds.Count().ToString();
array[4] = dt.Count().ToString();
array[5] = "";
array[6] = "";
array[7] = "";
for (int j = 0; j < array.Length; j++)
{
HSSFCell cell = row.CreateCell(j + 2);
cell.SetCellValue(array[j]);
cell.CellStyle = style1;
}
jhSumYue += Convert.ToInt32(entity.sumCount);
wcSumYue += Convert.ToInt32(entity.wcCount);
fsgzSumYue += ds.Count();
wcgzSumYue += dt.Count();
}
HSSFRow rowyue = sheet.CreateRow(lenyue1);//创建行号
string[] arrayyue = new string[8];
arrayyue[0] = "总计";
arrayyue[1] = jhSumYue.ToString();
arrayyue[2] = wcSumYue.ToString();
arrayyue[3] = fsgzSumYue.ToString();
arrayyue[4] = wcgzSumYue.ToString();
arrayyue[5] = "";
arrayyue[6] = "";
arrayyue[7] = "";
for (int j = 0; j < arrayyue.Length; j++)
{
HSSFCell cell = rowyue.CreateCell(j + 2);
cell.SetCellValue(arrayyue[j]);
cell.CellStyle = style1;
}
#endregion
}
#endregion
break;
#region 季计划
case "季计划":
seasonPlanList = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "全部");
deviceIdList = ArtificialSeasonPlanDomain.FindSeasonDeviceIdData(Convert.ToDateTime(Date_Year_Season.Value).Year, "全部");
int jidu1 = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "1").Count();
int jidu2 = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "2").Count();
int jidu3 = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "3").Count();
int jidu4 = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "4").Count();
int len = 0;
int len1 = 0;
HeadcolumnName = "设备巡检季计划年度报表";
sheet = workbook.CreateSheet("统计报表");
sheet.AddMergedRegion(new CellRangeAddress(0, 2, 0, 9));
HSSFRow row0 = sheet.CreateRow(0);
HSSFCell cell0 = row0.CreateCell(0);
cell0.SetCellValue(HeadcolumnName);
cell0.CellStyle.Alignment = CellHorizontalAlignment.CENTER;
cell0.CellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
cell0.CellStyle = style;
for (int ji = 1; ji <= 5; ji++)
{
#region 第一季度
if (ji == 1)
{
len = 3;
len1 = 3 + jidu1 + ji * 4 - 1;
}
#endregion
#region 第二季度
if (ji == 2)
{
len = 3 + jidu1 + (ji - 1) * 4;
len1 = 3 + jidu1 + jidu2 + ji * 4 - 1;
}
#endregion
#region 第三季度
if (ji == 3)
{
len = 3 + jidu1 + jidu2 + (ji - 1) * 4;
len1 = 3 + jidu1 + jidu2 + jidu3 + ji * 4 - 1;
}
#endregion
#region 第四季度
if (ji == 4)
{
len = 3 + jidu1 + jidu2 + jidu3 + (ji - 1) * 4;
len1 = 3 + jidu1 + jidu2 + jidu3 + jidu4 + ji * 4 - 1;
}
#endregion
#region 全年
if (ji == 5)
{
len = 3 + jidu1 + jidu2 + jidu3 + jidu4 + (ji - 1) * 4;
len1 = 3 + jidu1 + jidu2 + jidu3 + jidu4 + ji * 4 + seasonPlanList.Count() - 1;
}
#endregion
#region 样式
HSSFRow row1 = sheet.CreateRow(len);
sheet.AddMergedRegion(new CellRangeAddress(len, len + 1, 2, 2));
HSSFCell row1Cell0 = row1.CreateCell(2);
row1Cell0.SetCellValue("项目设备分类");
row1Cell0.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(len, len, 3, 4));
HSSFCell row1Cell9 = row1.CreateCell(3);
row1Cell9.SetCellValue("巡检");
row1Cell9.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(len, len, 5, 6));
HSSFCell row1Cell13 = row1.CreateCell(5);
row1Cell13.SetCellValue("故障修");
row1Cell13.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(len, len + 1, 7, 7));
HSSFCell row1Cell14 = row1.CreateCell(7);
row1Cell14.SetCellValue("设备质量分析");
row1Cell14.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(len, len + 1, 8, 8));
HSSFCell row1Cell15 = row1.CreateCell(8);
row1Cell15.SetCellValue("设备变更情况");
row1Cell15.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(len, len + 1, 9, 9));
HSSFCell row1Cell16 = row1.CreateCell(9);
row1Cell16.SetCellValue("备注");
row1Cell16.CellStyle = style1;
sheet.AddMergedRegion(new CellRangeAddress(len, len1, 0, 0));
HSSFCell row1Ce2 = row1.CreateCell(0);
row1Ce2.SetCellValue("设备巡检");
row1Ce2.CellStyle = style3;
sheet.AddMergedRegion(new CellRangeAddress(len, len1, 1, 1));
HSSFCell row1Ce3 = row1.CreateCell(1);
if (ji == 5)
{
row1Ce3.SetCellValue("1-4季度（全年）");
}
else
{
row1Ce3.SetCellValue("" + ji + "季度");
}
row1Ce3.CellStyle = style3;
string[] row2columns = new string[4] { "计划台/次", "完成台/次", "发生故障件/次", "完成故障件/次" };
HSSFRow row2 = sheet.CreateRow(len + 1);
for (int i = 0; i < row2columns.Length; i++)
{
sheet.AddMergedRegion(new CellRangeAddress(len + 1, len + 1, i + 3, i + 3));
HSSFCell row2Cell0 = row2.CreateCell(i + 3);
row2Cell0.SetCellValue(row2columns[i]);
row2Cell0.CellStyle = style1;
}
}
#endregion
#region 数据绑定
for (int ji = 1; ji <= 5; ji++)
{
#region 第一季度
if (ji == 1)
{
len = 3;
len1 = 3 + jidu1 + ji * 4;
}
#endregion
#region 第二季度
if (ji == 2)
{
len = 3 + jidu1 + (ji - 1) * 4;
len1 = 3 + jidu1 + jidu2 + ji * 4;
}
#endregion
#region 第三季度
if (ji == 3)
{
len = 3 + jidu1 + jidu2 + (ji - 1) * 4;
len1 = 3 + jidu1 + jidu2 + jidu3 + ji * 4;
}
#endregion
#region 第四季度
if (ji == 4)
{
len = 3 + jidu1 + jidu2 + jidu3 + (ji - 1) * 4;
len1 = 3 + jidu1 + jidu2 + jidu3 + jidu4 + ji * 4;
}
#endregion
#region 全年
if (ji == 5)
{
len = 3 + jidu1 + jidu2 + jidu3 + jidu4 + (ji - 1) * 4;
len1 = 3 + jidu1 + jidu2 + jidu3 + jidu4 + ji * 4 + seasonPlanList.Count();
}
#endregion
int rowLen = 0;
int jhSumji = 0;
int wcSumji = 0;
int fsgzSumji = 0;
int wcgzSumji = 0;
List<ArtificialSeasonOverhaulPlanEntity> lst = new List<ArtificialSeasonOverhaulPlanEntity>();
List<ArtificialSeasonOverhaulPlanEntity> dList = new List<ArtificialSeasonOverhaulPlanEntity>();
if (ji == 1)
{
lst = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "1").ToList();
dList = deviceIdList.Where(p => p.Quarterly == "1").ToList();
rowLen = 6;
}
if (ji == 2)
{
lst = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "2").ToList();
dList = deviceIdList.Where(p => p.Quarterly == "2").ToList();
rowLen = jidu1 + 4 + 6;
}
if (ji == 3)
{
lst = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "3").ToList();
dList = deviceIdList.Where(p => p.Quarterly == "3").ToList();
rowLen = jidu1 + 4 + 6 + jidu2 + 4;
}
if (ji == 4)
{
lst = ArtificialSeasonPlanDomain.FindSeasonPlanBaoBiaoData(Convert.ToDateTime(Date_Year_Season.Value).Year, "4").ToList();
dList = deviceIdList.Where(p => p.Quarterly == "4").ToList();
rowLen = jidu1 + 4 + 6 + jidu2 + 4 + jidu3 + 4;
}
if (ji == 5)
{
lst = seasonPlanList.ToList();
dList = deviceIdList.ToList();
rowLen = jidu1 + 4 + 6 + jidu2 + 4 + jidu3 + 4 + seasonPlanList.Count() + 2;
}
for (int i = 0; i < lst.Count(); i++)//季计划
{
HSSFRow row = sheet.CreateRow(i + rowLen);//创建行号
ArtificialSeasonOverhaulPlanEntity entity = lst[i];
List<ArtificialSeasonOverhaulPlanEntity> entityList = dList.Where(p => p.Type == entity.Type).ToList();
bx_infoDomain bx = new bx_infoDomain();
string startTime = "";
string endTime = "";
string deviceId = "";
if (ji == 1)
{
startTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "01-01";
endTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "03-31";
}
else if (ji == 2)
{
startTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "04-01";
endTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "06-30";
}
else if (ji == 3)
{
startTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "07-01";
endTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "09-30";
}
else if (ji == 4)
{
startTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "10-01";
endTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "12-31";
}
else
{
startTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "01-01";
endTime = Convert.ToDateTime(Date_Year_Season.Value).Year + "-" + "12-31";
}
for (int a = 0; a < entityList.Count(); a++)
{
if (a == entityList.Count() - 1)
{
deviceId += "'" + entityList[a].DeviceId + "'";
}
else
{
deviceId += "'" + entityList[a].DeviceId + "'" + ",";
}
}
List<bx_infoEntity> ds = bx.getNumberByTimeNo(wordType, startTime, endTime, deviceId);
List<bx_infoEntity> dt = ds.Where(p => p.jindu == 4).ToList();
string[] array = new string[8];
array[0] = entity.Type;
array[1] = entity.sumCount;
array[2] = entity.wcCount;
array[3] = ds.Count().ToString();
array[4] = dt.Count().ToString();
array[5] = "";
array[6] = "";
array[7] = "";
for (int j = 0; j < array.Length; j++)
{
HSSFCell cell = row.CreateCell(j + 2);
cell.SetCellValue(array[j]);
cell.CellStyle = style1;
}
jhSumji += Convert.ToInt32(entity.sumCount);
wcSumji += Convert.ToInt32(entity.wcCount);
fsgzSumji += ds.Count();
wcgzSumji += dt.Count();
}
HSSFRow row1 = sheet.CreateRow(len1 - 1);//创建行号
string[] array1 = new string[8];
array1[0] = "总计";
array1[1] = jhSumji.ToString();
array1[2] = wcSumji.ToString();
array1[3] = fsgzSumji.ToString();
array1[4] = wcgzSumji.ToString();
array1[5] = "";
array1[6] = "";
array1[7] = "";
for (int j = 0; j < array1.Length; j++)
{
HSSFCell cell = row1.CreateCell(j + 2);
cell.SetCellValue(array1[j]);
cell.CellStyle = style1;
}
}
#endregion
#endregion
break;
}
string strPath = "/Export/" + Guid.NewGuid() + ".xls";
string filePath = Server.MapPath(strPath);
//写入
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] bArr = ms.ToArray();
fs.Write(bArr, 0, bArr.Length);
fs.Flush();
}
FileInfo fileInfo = new FileInfo(filePath);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");//文件名
Response.AddHeader("content-length", fileInfo.Length.ToString());//文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.WriteFile(filePath);
}