Esempio n. 1
0
        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();
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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);
        }