private void FillExcelMonthly(HSSFSheet sheet1, DataTable dt, int startRow)
{
//row,cell都是从0开始计数
//第1行title,不是数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
//最后一列bookingDate 不显示
for (int j = 0; j < dt.Columns.Count - 1; j++)
{
HSSFCell cell = sheet1.Items((startRow + i + 1), j);
//HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();
if (j == 2 || j == 3 || j == 5)
{
if (!string.IsNullOrEmpty(dr[j].ToString()))
cell.SetCellValue(double.Parse(dr[j].ToString()));
}
else
cell.SetCellValue(dr[j].ToString());
//cell.CellStyle = cellStyle;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="sheet1"></param>
/// <param name="dt"></param>
/// <param name="dtPrevYear"></param>
/// <param name="CurrentMonth"></param>
/// <param name="DepId"></param>
private void FillExcelPersonProfit(HSSFSheet sheet1, DataTable dt, DataTable dtPrevYear,
int CurrentMonth, string DepId,int Cnt, int PrevCnt)
{
switch (CurrentMonth)
{
case 1:
sheet1.Items(0, 1).SetCellValue("Dec");
sheet1.Items(0, 2).SetCellValue("Jan");
sheet1.Items(0, 3).SetCellValue("Total(Dec+Jan)");
break;
case 2:
sheet1.Items(0, 1).SetCellValue("Jan");
sheet1.Items(0, 2).SetCellValue("Feb");
sheet1.Items(0, 3).SetCellValue("Total(Jan+Feb)");
break;
case 3:
sheet1.Items(0, 1).SetCellValue("Feb");
sheet1.Items(0, 2).SetCellValue("Mar");
sheet1.Items(0, 3).SetCellValue("Total(Feb+Mar)");
break;
case 4:
sheet1.Items(0, 1).SetCellValue("Mar");
sheet1.Items(0, 2).SetCellValue("Apr");
sheet1.Items(0, 3).SetCellValue("Total(Mar+Apr)");
break;
case 5:
sheet1.Items(0, 1).SetCellValue("Apr");
sheet1.Items(0, 2).SetCellValue("May");
sheet1.Items(0, 3).SetCellValue("Total(Apr+May)");
break;
case 6:
sheet1.Items(0, 1).SetCellValue("May");
sheet1.Items(0, 2).SetCellValue("Jun");
sheet1.Items(0, 3).SetCellValue("Total(May+Jun)");
break;
case 7:
sheet1.Items(0, 1).SetCellValue("Jun");
sheet1.Items(0, 2).SetCellValue("Jul");
sheet1.Items(0, 3).SetCellValue("Total(Jun+Jul)");
break;
case 8:
sheet1.Items(0, 1).SetCellValue("Jul");
sheet1.Items(0, 2).SetCellValue("Aug");
sheet1.Items(0, 3).SetCellValue("Total(Jul+Aug)");
break;
case 9:
sheet1.Items(0, 1).SetCellValue("Aug");
sheet1.Items(0, 2).SetCellValue("Sep");
sheet1.Items(0, 3).SetCellValue("Total(Aug+Sep)");
break;
case 10:
sheet1.Items(0, 1).SetCellValue("Sep");
sheet1.Items(0, 2).SetCellValue("Oct");
sheet1.Items(0, 3).SetCellValue("Total(Sep+Oct)");
break;
case 11:
sheet1.Items(0, 1).SetCellValue("Oct");
sheet1.Items(0, 2).SetCellValue("Nov");
sheet1.Items(0, 3).SetCellValue("Total(Oct+Nov)");
break;
case 12:
sheet1.Items(0, 1).SetCellValue("Nov");
sheet1.Items(0, 2).SetCellValue("Dec");
sheet1.Items(0, 3).SetCellValue("Total(Nov+Dec)");
break;
default:
break;
}
//是否按部门过滤数据
DataRow[] DRS =new DataRow[dt.Rows.Count];
DataRow[] DRSPrevYear = new DataRow[dtPrevYear.Rows.Count];
if (DepId != "")
{
DRS = dt.Select("DepId=" + DepId);
//只有1月份的时候，才有去年的数值
if(dtPrevYear.Rows.Count>0)
DRSPrevYear = dtPrevYear.Select("DepId=" + DepId);
}
else
{
dt.Rows.CopyTo(DRS, 0);
dtPrevYear.Rows.CopyTo(DRSPrevYear, 0);
}
//row,cell都是从0开始计数
//第1行title,不是数据
for (int i = 0; i < DRS.Length; i++) //每个员工
{
DataRow dr = DRS[i];
HSSFCell cell = sheet1.Items((i + 1), 0);
cell.SetCellValue(dr[1].ToString()); //员工名字
//dr[5~16]的值是该员工1-12月的profit 1=>5,3=>7,5=>9,7=>11,9=>13,11=>15
double monthProfit1, monthProfit2;
if (CurrentMonth == 1)
{
monthProfit1 = double.Parse(DRSPrevYear[i][12+4].ToString());
monthProfit2 = double.Parse(dr[CurrentMonth + 4].ToString());
}
else
{
monthProfit1 = double.Parse(dr[CurrentMonth + 3].ToString());
monthProfit2 = double.Parse(dr[CurrentMonth + 4].ToString());
}
sheet1.Items((i + 1), 1).SetCellValue(monthProfit1);
sheet1.Items((i + 1), 2).SetCellValue(monthProfit2);
}
//模板员工行数现在是23行,假如超过就要再加
for (int i = DRS.Length+1; i < 23; i++)
{
sheet1.GetRow(i).Hide();
}
//28B,28C是出票数量
sheet1.Items(27, 1).SetCellValue(PrevCnt);
sheet1.Items(27, 2).SetCellValue(Cnt);
}
private void FillExcelAccount(HSSFSheet sheet1, DataTable dt, int startRow)
{
//row,cell都是从0开始计数
//第1行title,不是数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
HSSFCell cell = sheet1.Items((startRow + i + 1), j);
if (j == 4)
cell.SetCellValue(double.Parse(dr[j].ToString()));
else if (j == 1)
{
DateTime dtBooking;
if (DateTime.TryParse(dr[j].ToString(), out dtBooking))
cell.SetCellValue(dtBooking);
else
cell.SetCellValue(dr[j].ToString());
}
else
cell.SetCellValue(dr[j].ToString());
}
}
}
