//每日出票记录(不按部门分组) 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); }