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);
}