public static void GetAllMonthlyDataStatistic(IWorkbook workbook, string companyName, List <string> portfolio, List <string> logShortDate, List <string> staffs, DataSet data, DataSet total, SheetStylePattern styles) { //构建sheet表 ISheet sheet = workbook.CreateSheet(companyName); //构建日期行,合并相应单元格 var firstRow = sheet.CreateRow(0); SetCellValueWithStyle(firstRow.CreateCell(0), "", styles.markStyle); //统一列样式 sheet.SetDefaultColumnStyle(0, styles.markStyle); int portfolioCount = portfolio.Count; for (int i = 0; i < logShortDate.Count; i++) { SetCellValueWithStyle(firstRow.CreateCell(1 + i * portfolioCount), logShortDate[i], i % 2 == 1 ? styles.alterOddStyle : styles.alterEvenStyle); //统一列样式 for (int s = 1 + i * portfolioCount; s < 1 + (i + 1) * portfolioCount; s++) { sheet.SetDefaultColumnStyle(s, i % 2 == 1 ? styles.alterOddStyle : styles.alterEvenStyle); } sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1 + i * portfolioCount, (i + 1) * portfolioCount)); } SetCellValueWithStyle(firstRow.CreateCell(1 + logShortDate.Count * portfolioCount), "Total", styles.totalStyle); //统一列样式 for (int s = 1 + logShortDate.Count * portfolioCount; s < 1 + (logShortDate.Count + 1) * portfolioCount; s++) { sheet.SetDefaultColumnStyle(s, styles.totalStyle); } sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1 + logShortDate.Count * portfolioCount, (logShortDate.Count + 1) * portfolioCount)); SetCellValueWithStyle(firstRow.CreateCell(1 + (logShortDate.Count + 1) * portfolioCount), "工时比", styles.totalPercentStyle); for (int s = 1 + (logShortDate.Count + 1) * portfolioCount; s < 1 + (logShortDate.Count + 2) * portfolioCount; s++) { sheet.SetDefaultColumnStyle(s, styles.totalPercentStyle); } sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1 + (logShortDate.Count + 1) * portfolioCount, ((logShortDate.Count + 1) + 1) * portfolioCount)); //构建标题行,包括部门,职位,项目 List <string> departmentColName = new List <string>(); List <string> positionColName = new List <string>(); List <string> projectColName = new List <string>(); foreach (string item in portfolio) { string[] sperate = item.Split("*".ToArray()); departmentColName.Add(sperate[0]); projectColName.Add(sperate[1]); positionColName.Add(sperate[2]); } for (int j = 0; j < colTitle.Length; j++) { var row = sheet.CreateRow(j + 1); SetCellValueWithStyle(row.CreateCell(0), colTitle[j], j == 2 ? styles.markBorderBoldStyle : styles.markBoldStyle); var colNames = j == 0 ? departmentColName : j == 1 ? projectColName : positionColName; for (int i = 0; i < logShortDate.Count; i++) { var style = j == 2 ? (i % 2 == 1 ? styles.alterBorderOddStyle : styles.alterBorderEvenStyle) : (i % 2 == 1 ? styles.alterOddStyle : styles.alterEvenStyle); int start = colNames.Count * i + 1; SetCellSeriesData(row, start, colNames, style); } SetCellSeriesData(row, colNames.Count * logShortDate.Count + 1, colNames, j == 2 ? styles.totalBorderStyle : styles.totalStyle); SetCellSeriesData(row, colNames.Count * (logShortDate.Count + 1) + 1, colNames, j == 2 ? styles.totalBorderPercentStyle : styles.totalPercentStyle); } //构建统计记录行,包括所有员工名和工时 for (int y = 0; y < staffs.Count; y++) { var row = sheet.CreateRow(y + 4); var style = y == staffs.Count - 1 ? styles.markBorderStyle : styles.markStyle; SetCellValueWithStyle(row.CreateCell(0), staffs[y], style); } SetCellValueWithStyle(sheet.CreateRow(staffs.Count + 5).CreateCell(0), "", styles.markBorderStyle); //数据列 for (int i = 0; i < logShortDate.Count; i++) { DataTable dt = data.Tables[logShortDate[i]]; int start = dt.Columns.Count * i + 1; SetCellSeriesData(sheet, start, dt, i % 2 == 1 ? styles.alterOddStyle : styles.alterEvenStyle, i % 2 == 1 ? styles.alterBorderOddStyle : styles.alterBorderEvenStyle); } //总计和工时比 int totalStart = data.Tables[0].Columns.Count * logShortDate.Count + 1; SetCellSeriesData(sheet, totalStart, total.Tables[0], styles.totalStyle, styles.totalBorderStyle); int ratioStart = totalStart + data.Tables[0].Columns.Count; SetCellSeriesData(sheet, ratioStart, total.Tables[1], styles.totalPercentStyle, styles.totalBorderPercentStyle); //冻结某些列和某些行 sheet.CreateFreezePane(1, 4, 1, 4); //sheet.SetDefaultColumnStyle() //return new DataTable(); }
public static XSSFWorkbook BuildExcelData(List <WorklogTime> records, List <DateTime> logDates) { SheetStylePattern styles = new SheetStylePattern(); XSSFWorkbook workbook = styles.book; foreach (DateTime logDate in logDates) { string sheetName = string.Format("{0}.{1}", logDate.Month, logDate.Day); //构造表头部分 ISheet sheet = workbook.CreateSheet(sheetName); BuildRowData(sheet.CreateRow(0), colName.Keys, styles.titleStyle); //构造表内容记录 var list = records.Where(r => r.logDate == logDate).ToList(); double totalTime = list.Sum(r => r.worklog); for (int i = 0; i < list.Count(); i++) { BuildRowData(sheet.CreateRow(i + 1), new List <string>() { list[i].userName, list[i].rank, list[i].department, list[i].position, list[i].project, list[i].worklog.ToString(), }, styles.textStyle); } //构造表尾时间总和 BuildRowData(sheet.CreateRow(list.Count + 1), new string[] { "", "", "", "", "", totalTime.ToString() }, styles.textStyle); sheet.SetColumnWidth(0, 10 * 256); } List <WorklogTime> lianheRecords = new List <WorklogTime>(); List <WorklogTime> heyiRecords = new List <WorklogTime>(); var heyiNames = GetHeyiStaff(); foreach (var record in records) { if (heyiNames.Contains(record.userName)) { heyiRecords.Add(record); } else { lianheRecords.Add(record); } } List <string> logShortDate = new List <string>(); foreach (DateTime logDate in logDates) { logShortDate.Add(string.Format("{0}.{1}", logDate.Month, logDate.Day)); } //联和统计表数据 var lianheStaffs = lianheRecords.AsEnumerable().Select(r => r.userName).Distinct().ToList(); var lianhePortfolio = lianheRecords.AsEnumerable().OrderBy(r => r.portfolio).Select(r => r.portfolio).Distinct().ToList(); DataSet lianheData = GetMonthlyTimeslog(lianheStaffs, lianhePortfolio, lianheRecords, logDates); DataSet lianheTotal = GetMonthlyTotalTimeslog(lianheStaffs, lianhePortfolio, lianheRecords); GetAllMonthlyDataStatistic(workbook, "联和", lianhePortfolio, logShortDate, lianheStaffs, lianheData, lianheTotal, styles); //和逸统计表数据 var heyiStaffs = heyiRecords.AsEnumerable().Select(r => r.userName).Distinct().ToList(); var heyiPortfolio = heyiRecords.AsEnumerable().OrderBy(r => r.portfolio).Select(r => r.portfolio).Distinct().ToList(); DataSet heyiData = GetMonthlyTimeslog(heyiStaffs, heyiPortfolio, heyiRecords, logDates); DataSet heyiTotal = GetMonthlyTotalTimeslog(heyiStaffs, heyiPortfolio, heyiRecords); GetAllMonthlyDataStatistic(workbook, "和逸", heyiPortfolio, logShortDate, heyiStaffs, heyiData, heyiTotal, styles); return(workbook); }
public static XSSFWorkbook BuildExcelData(List <WorkReportRecord> records, List <int> reportDate, int year, int month) { SheetStylePattern styles = new SheetStylePattern(); XSSFWorkbook workbook = styles.book; string sheetName = string.Format("{0}.{1}", year, month); ISheet sheet = workbook.CreateSheet(sheetName); int rowNum = 0; //表格备注 SetCellValueWithStyle(sheet.CreateRow(rowNum).CreateCell(0), "统计截止时间:", styles.textStyle); SetCellValueWithStyle(sheet.GetRow(rowNum).CreateCell(1), sheetName, styles.textStyle); rowNum++; SetCellValueWithStyle(sheet.CreateRow(rowNum).CreateCell(0), "备注:“×” 表示没有按时发出邮件;空白表示已按时发出邮件", styles.textStyle); rowNum++; //表格时间段 var timeRow = sheet.CreateRow(rowNum); for (int i = 0; i < reportDate.Count; i++) { SetCellValueWithStyle(timeRow.CreateCell(i * 3 + 4), string.Format("{0}.{1}", month, reportDate[i]), styles.textStyle); } rowNum++; var titleRow = sheet.CreateRow(rowNum); SetCellValueWithStyle(titleRow.CreateCell(0), "中文名", styles.textStyle); SetCellValueWithStyle(titleRow.CreateCell(1), "英文名", styles.textStyle); SetCellValueWithStyle(titleRow.CreateCell(2), "人员性质", styles.textStyle); SetCellValueWithStyle(titleRow.CreateCell(3), "未按时发", styles.textStyle); for (int i = 0; i < reportDate.Count; i++) { SetCellValueWithStyle(titleRow.CreateCell(i * 3 + 4), "早", styles.textStyle); SetCellValueWithStyle(titleRow.CreateCell(i * 3 + 5), "中", styles.textStyle); SetCellValueWithStyle(titleRow.CreateCell(i * 3 + 6), "晚", styles.textStyle); } rowNum++; //员工数据段 var allUsers = UserService.GetAllUsers(); var listAll = UserService.GetAllMailUsers(allUsers); //var listTwo = UserService.GetTwoMailUsers(allUsers); //var listSummary = UserService.GetSummaryMailUsers(allUsers); var needMailUsers = listAll.ToList(); var reportUsers = records.Select(r => r.userName).Distinct().ToList(); foreach (User user in needMailUsers) { var row = sheet.CreateRow(rowNum); string userName = user.LetterName; var report = records.Where(r => r.userName.ToLower() == userName); //if (userName.Equals(specialUser)) //{ // report = report.Union(records.Where(r => r.userName.ToLower() == "cnabs")); //} SetCellValueWithStyle(row.CreateCell(0), user.ChineseName, styles.textStyle); SetCellValueWithStyle(row.CreateCell(1), userName, styles.textStyle); SetCellValueWithStyle(row.CreateCell(2), user.rankLevel, styles.textStyle); SetCellValueWithStyle(row.CreateCell(3), report.Where(r => !r.signIn).Count(), styles.textStyle); for (int j = 0; j < reportDate.Count; j++) { int day = reportDate[j]; SetCellValueWithStyle(row.CreateCell(j * 3 + 4), (report.Count() == 0 || report.Any(r => r.recordDate.Day == day && r.type.Equals("morning") && !r.signIn)) ? "×" : "", styles.textStyle); SetCellValueWithStyle(row.CreateCell(j * 3 + 5), (report.Count() == 0 || report.Any(r => r.recordDate.Day == day && r.type.Equals("noon") && !r.signIn)) ? "×" : "", styles.textStyle); SetCellValueWithStyle(row.CreateCell(j * 3 + 6), (report.Count() == 0 || report.Any(r => r.recordDate.Day == day && r.type.Equals("summary") && !r.signIn)) ? "×" : "", styles.textStyle); } rowNum++; } //for (int i = 0; i < count; i++) //{ //var row = sheet.CreateRow(rowNum); //string userName = reportUsers[i]; //var user = allUsers.Where(r => r.LetterName == userName.ToLower()).FirstOrDefault(); //var report = records.Where(r => r.userName == userName).ToList(); //SetCellValueWithStyle(row.CreateCell(0), user.ChineseName, styles.textStyle); //SetCellValueWithStyle(row.CreateCell(1), userName, styles.textStyle); //SetCellValueWithStyle(row.CreateCell(2), user.rankLevel, styles.textStyle); //(row.CreateCell(3), report.Where(r=>!r.signIn).Count(), styles.textStyle); //for (int j = 0; j < reportDate.Count; j++) //{ // int day = reportDate[j]; // SetCellValueWithStyle(row.CreateCell(j * 3 + 4), report.Any(r => r.recordDate.Day == day && r.type.Equals("morning") && !r.signIn) ? "×" : "", styles.textStyle); // SetCellValueWithStyle(row.CreateCell(j * 3 + 5), report.Any(r => r.recordDate.Day == day && r.type.Equals("noon") && !r.signIn) ? "×" : "", styles.textStyle); // SetCellValueWithStyle(row.CreateCell(j * 3 + 6), report.Any(r => r.recordDate.Day == day && r.type.Equals("summary") && !r.signIn) ? "×" : "", styles.textStyle); //} //rowNum++; //} sheet.DefaultColumnWidth = 10; return(workbook); }