//每日出票记录(不按部门分组)
private void DailyIssue(int Year, int Month, int DepCode)
{
int MaxDay = 30;
if (Month == 1 || Month == 3 || Month == 5 || Month == 7 || Month == 8 || Month == 10 || Month == 12)
{
MaxDay = 31;
}
if (Month == 2) //要考虑闰年,2月是29天
{
if ((Year % 400 == 0) || (Year % 100 != 0) && (Year % 4 == 0))
{
MaxDay = 29;
}
else
{
MaxDay = 28;
}
}
//read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "Invoice\\DailyIssue.xls", FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = hssfworkbook.GetSheet("Sheet1");
//row,cell都是从0开始计数
//第1行title,不是数据
int dayIssueCnt = 0;
DataTable dt;
if (DepCode == 0)
{
dt = Ticket.GetIssue(Year, Month);
}
else
{
dt = Ticket.GetIssue(Year, Month, DepCode);
}
for (int day = 1; day <= MaxDay; day++)
{
HSSFCell cell = sheet1.CreateRow(dayIssueCnt + 1).CreateCell(0);
HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();
//根据excel 单元格的自定义格式
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("d-mmm");
cell.CellStyle = cellStyle;
cell.SetCellValue(new DateTime(Year, Month, day));
DataRow[] drs = dt.Select("IssueDate='" + new DateTime(Year, Month, day).ToString() + "'");
for (int i = 0; i < drs.Length; i++)
{
DataRow dr = drs[i];
for (int j = 2; j < dt.Columns.Count; j++)
{
if (j == 4)
{
sheet1.CreateRow(dayIssueCnt + 2 + i).CreateCell(j - 2).SetCellValue(double.Parse(dr[j].ToString()));
}
else
{
sheet1.CreateRow(dayIssueCnt + 2 + i).CreateCell(j - 2).SetCellValue(dr[j].ToString());
}
}
}
dayIssueCnt += drs.Length + 4;
}
//Excel文件在被打开的时候自动将焦点定位在单元格
sheet1.GetRow(0).GetCell(0).SetAsActiveCell();
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
string FullFileName = AppDomain.CurrentDomain.BaseDirectory + "Upload\\Excel\\DailyIssue_" + Year.ToString() + Month.ToString("00") + ".xls";
file = new FileStream(FullFileName, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
DownloadFileAsAttachment(FullFileName);
}
