public void exportTable(UIResult result) { var adjustRecord = base.LocalID.DeSerializeAdjustRecord <ResultAdjustmentModel>(result.TaskID); if (adjustRecord != null) { if (!result.IsUploaded) { this.ShowDialog("提示信息", "本地结果发生改变请先上传结果!", DialogSettingType.OnlyOkButton, DialogType.Warning); return; } } // (1:班级 2:教师 4 年级) int exportType = 1; ExportTypeWindow window = new ExportTypeWindow(false); window.Closed += (s, arg) => { if (window.DialogResult.Value) { exportType = window.Type; #region 导出 var cp = CommonDataManager.GetCPCase(base.LocalID); var local = CommonDataManager.GetLocalCase(base.LocalID); ResultModel resultModel = base.LocalID.DeSerializeLocalResult <ResultModel>(result.TaskID); if (resultModel == null) { var value = WebAPI.Instance.GetAdminResult(result.TaskID); if (value.Item1) { resultModel = value.Item2; } else { this.ShowDialog("提示信息", "获取行政班结果失败", CustomControl.Enums.DialogSettingType.OnlyOkButton, CustomControl.Enums.DialogType.Warning); return; } } string typeName = string.Empty; if (exportType == 1) { typeName = "班级课表"; } else if (exportType == 2) { typeName = "教师课表"; } else if (exportType == 4) { typeName = "年级课表"; } System.Windows.Forms.SaveFileDialog saveDialog = new System.Windows.Forms.SaveFileDialog(); saveDialog.Filter = "Microsoft Excel files(*.xls)|*.xls;*.xlsx"; saveDialog.FileName = $"{local.Name}-{result.TaskID}-({typeName})"; var confirm = saveDialog.ShowDialog(); if (confirm == System.Windows.Forms.DialogResult.OK) { if (exportType == 1) { Dictionary <string, DataTable> values = new Dictionary <string, DataTable>(); foreach (var rc in resultModel.ResultClasses) { // 创建基础结构 var dt = this.CreateTableFrame(); // 创建sheet var firstClass = cp.Classes.FirstOrDefault(c => rc.ClassID.Equals(c.ID)); values.Add(firstClass.Name, dt); // 常规 var normals = rc.ResultDetails.Where(rd => rd.ResultType == XYKernel.OS.Common.Enums.ClassHourResultType.Normal)?.ToList(); normals?.ForEach(n => { var classHourInfo = cp.GetClassHours(new int[] { n.ClassHourId })?.FirstOrDefault(); SetCellData(dt, classHourInfo.Course + "\n" + classHourInfo.TeacherString, n.DayPeriod); }); // 单双周 var mulitplys = rc.ResultDetails.Where(rd => rd.ResultType != XYKernel.OS.Common.Enums.ClassHourResultType.Normal)?.ToList(); var groups = mulitplys?.GroupBy(m => $"{m.DayPeriod.Day}{m.DayPeriod.Period}"); if (groups != null) { foreach (var g in groups) { var first = g.FirstOrDefault(); var courseName = g.Select(gi => { return(cp.Courses.FirstOrDefault(c => c.ID.Equals(gi.CourseID))?.Name); })?.Parse("|"); var teacherName = g.Select(gi => { return(cp.GetTeachersByIds(gi.Teachers.ToList()).Select(a => a.Name).ToArray().Parse(",")); })?.Parse("|"); SetCellData(dt, courseName + "\n" + teacherName, first.DayPeriod); } } } #region 导出 List <int> enableIndex = new List <int>(); var abIndex = cp.Positions.FirstOrDefault(p => p.Position == XYKernel.OS.Common.Enums.Position.AB); if (abIndex != null) { enableIndex.Add(abIndex.DayPeriod.Period); } var noonIndex = cp.Positions.FirstOrDefault(p => p.Position == XYKernel.OS.Common.Enums.Position.Noon); if (noonIndex != null) { enableIndex.Add(noonIndex.DayPeriod.Period); } var pbIndex = cp.Positions.FirstOrDefault(p => p.Position == XYKernel.OS.Common.Enums.Position.PB); if (pbIndex != null) { enableIndex.Add(pbIndex.DayPeriod.Period); } var table = NPOIClass.DataTableToExcel(values, saveDialog.FileName, enableIndex); if (table.Item1) { this.ShowDialog("提示信息", "导出成功!", CustomControl.Enums.DialogSettingType.NoButton, CustomControl.Enums.DialogType.None); FileHelper.OpenFilePath(saveDialog.FileName); } else { this.ShowDialog("提示信息", table.Item2, CustomControl.Enums.DialogSettingType.OnlyOkButton, CustomControl.Enums.DialogType.Warning); } #endregion } else if (exportType == 4) { GradeExcelModel excelModel = new GradeExcelModel(); // 检查是否有周六周日 List <string> weeks = new List <string>() { "星期一", "星期二", "星期三", "星期四", "星期五", }; // 是否有周末 var hasWeekend = resultModel.ResultClasses.Any(rc => rc.ResultDetails.Any(rd => (rd.DayPeriod.Day == DayOfWeek.Saturday || rd.DayPeriod.Day == DayOfWeek.Sunday))); if (hasWeekend) { weeks.Add("星期六"); weeks.Add("星期日"); } // 节次 List <string> periods = new List <string>(); cp.Positions.GroupBy(p => p.DayPeriod.PeriodName)?.ToList()?.ForEach(p => { periods.Add(p.Key); }); // 设置节次 excelModel.SetPeriods(periods); // 设置星期 excelModel.SetWeeks(weeks); foreach (var rc in resultModel.ResultClasses) { var classInfo = resultModel.Classes.FirstOrDefault(c => rc.ClassID.Equals(c.ID)); // 添加结果 List <string> values = new List <string>(); excelModel.AddClassesDictionary(classInfo.Name, values); var mondays = rc.ResultDetails.Where(rd => rd.DayPeriod.Day == DayOfWeek.Monday)?.ToList(); var tuesdays = rc.ResultDetails.Where(rd => rd.DayPeriod.Day == DayOfWeek.Tuesday)?.ToList(); var wednesdays = rc.ResultDetails.Where(rd => rd.DayPeriod.Day == DayOfWeek.Wednesday)?.ToList(); var thursdays = rc.ResultDetails.Where(rd => rd.DayPeriod.Day == DayOfWeek.Thursday)?.ToList(); var fridays = rc.ResultDetails.Where(rd => rd.DayPeriod.Day == DayOfWeek.Friday)?.ToList(); var saturdays = rc.ResultDetails.Where(rd => rd.DayPeriod.Day == DayOfWeek.Saturday)?.ToList(); var sundays = rc.ResultDetails.Where(rd => rd.DayPeriod.Day == DayOfWeek.Sunday)?.ToList(); values.AddRange(FillGradeExportValues(mondays, periods)); values.AddRange(FillGradeExportValues(tuesdays, periods)); values.AddRange(FillGradeExportValues(wednesdays, periods)); values.AddRange(FillGradeExportValues(thursdays, periods)); values.AddRange(FillGradeExportValues(fridays, periods)); if (saturdays?.Count > 0) { values.AddRange(FillGradeExportValues(saturdays, periods)); } if (sundays?.Count > 0) { values.AddRange(FillGradeExportValues(sundays, periods)); } } var table = GradeExcel.Export(excelModel, saveDialog.FileName); if (table.Item1) { this.ShowDialog("提示信息", "导出成功!", CustomControl.Enums.DialogSettingType.NoButton, CustomControl.Enums.DialogType.None); FileHelper.OpenFilePath(saveDialog.FileName); } else { this.ShowDialog("提示信息", table.Item2, CustomControl.Enums.DialogSettingType.OnlyOkButton, CustomControl.Enums.DialogType.Warning); } } else if (exportType == 2) { Dictionary <string, DataTable> values = new Dictionary <string, DataTable>(); // 获取所有教师 var classHourIDs = from c in resultModel.ResultClasses from rd in c.ResultDetails select rd.ClassHourId; var classHours = cp.GetClassHours(classHourIDs?.ToArray()); List <UITeacher> teachers = new List <UITeacher>(); classHours?.ForEach(ch => { ch.Teachers.ForEach(t => { var teacher = teachers.FirstOrDefault(tt => tt.ID.Equals(t.ID)); if (teacher == null) { teachers.Add(new UITeacher() { ID = t.ID, Name = t.Name, ClassHourIDs = new List <int> { ch.ID } }); } else { teacher.ClassHourIDs.Add(ch.ID); } }); }); if (teachers.Count == 0) { this.ShowDialog("提示信息", "没有教师信息无法导出教师课表!", DialogSettingType.OnlyOkButton, DialogType.Warning); return; } teachers.ForEach(t => { // 创建基础结构 var dt = this.CreateTableFrame(); values.Add(t.Name, dt); var resultDetails = (from rc in resultModel.ResultClasses from rd in rc.ResultDetails select new { rc.ClassID, rd.ClassHourId, rd.CourseID, rd.DayPeriod, rd.ResultType }); var filters = (from ch in t.ClassHourIDs from rc in resultDetails where ch == rc.ClassHourId select rc)?.ToList(); var groups = filters?.GroupBy(g => $"{g.DayPeriod.Day}{g.DayPeriod.Period}"); if (groups != null) { foreach (var g in groups) { var dayPeriod = g.FirstOrDefault().DayPeriod; var ids = g.Select(gs => { return(gs.ClassHourId); })?.ToArray(); var setValue = cp.GetClassHours(ids)?.Select(ss => $"{ss.Course}-{ss.Class}")?.Parse("|"); SetCellData(dt, setValue, dayPeriod); } } }); #region 导出 List <int> enableIndex = new List <int>(); var abIndex = cp.Positions.FirstOrDefault(p => p.Position == XYKernel.OS.Common.Enums.Position.AB); if (abIndex != null) { enableIndex.Add(abIndex.DayPeriod.Period); } var noonIndex = cp.Positions.FirstOrDefault(p => p.Position == XYKernel.OS.Common.Enums.Position.Noon); if (noonIndex != null) { enableIndex.Add(noonIndex.DayPeriod.Period); } var pbIndex = cp.Positions.FirstOrDefault(p => p.Position == XYKernel.OS.Common.Enums.Position.PB); if (pbIndex != null) { enableIndex.Add(pbIndex.DayPeriod.Period); } var table = NPOIClass.DataTableToExcel(values, saveDialog.FileName, enableIndex); if (table.Item1) { this.ShowDialog("提示信息", "导出成功!", CustomControl.Enums.DialogSettingType.NoButton, CustomControl.Enums.DialogType.None); FileHelper.OpenFilePath(saveDialog.FileName); } else { this.ShowDialog("提示信息", table.Item2, CustomControl.Enums.DialogSettingType.OnlyOkButton, CustomControl.Enums.DialogType.Warning); } #endregion } } #endregion } }; window.ShowDialog(); }
public static Tuple <bool, string> Export(GradeExcelModel grade, string file) { #region 创建Workbook IWorkbook workbook = new HSSFWorkbook(); #endregion // 创建列样式 var columnCellStyle = CreateColumnCellStye(workbook); var headerStyle = CreateHeaderCellStye(workbook); #region 创建ISheet ISheet sheet = workbook.CreateSheet(grade.Header); sheet.SetColumnWidth(0, 200 * 25); var weekday = grade.Weeks.Count; var period = grade.Periods.Count; var columnCount = (weekday * period) + 1; // 设置标题 var header = sheet.CreateRow(0); header.Height = 60 * 15; // 设置创建时间 var timeRow = sheet.CreateRow(1); timeRow.Height = 30 * 15; for (int i = 0; i < columnCount; i++) { var headerCell = header.CreateCell(i); var timeCell = timeRow.CreateCell(i); if (i == 0) { headerCell.SetCellValue(grade.Header); timeCell.SetCellValue(grade.CreateDateString); } timeCell.CellStyle = columnCellStyle; headerCell.CellStyle = headerStyle; } CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, columnCount - 1); sheet.AddMergedRegion(region1); CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, columnCount - 1); sheet.AddMergedRegion(region2); // 创建星期 var week = sheet.CreateRow(2); var periodRow = sheet.CreateRow(3); for (int i = 0; i < columnCount; i++) { var weekCell = week.CreateCell(i); var periodCell = periodRow.CreateCell(i); if (i == 0) { weekCell.SetCellValue("星期"); periodCell.SetCellValue("节次"); } weekCell.CellStyle = columnCellStyle; periodCell.CellStyle = columnCellStyle; } // 临时节次 var tempPeriodList = new List <string>(); // 星期 for (int i = 1; i <= weekday; i++) { // 添加节次 grade.Periods.ForEach(p => { tempPeriodList.Add(p); }); if (i == 1) { week.GetCell(i).SetCellValue(grade.Weeks[i - 1]); sheet.AddMergedRegion(new CellRangeAddress(2, 2, i, period)); } else { var start = ((i - 1) * period) + 1; week.GetCell(start).SetCellValue(grade.Weeks[i - 1]); sheet.AddMergedRegion(new CellRangeAddress(2, 2, start, (period * i))); } } // 节次 for (int i = 0; i < tempPeriodList.Count; i++) { // 从第二列开始获取 var periodCell = periodRow.GetCell(i + 1); periodCell.SetCellValue(tempPeriodList[i]); } // 填充内容 if (grade.ClassesDictionary != null) { // 行起始位置 int rowStart = 4; // 班级字典 if (grade.ClassesDictionary != null) { foreach (var cd in grade.ClassesDictionary) { // 创建新数据 var newRow = sheet.CreateRow(rowStart); var values = cd.Value; // 创建单元格 var columnIndex = 0; // 创建班级单元格 var classCell = newRow.CreateCell(columnIndex); classCell.CellStyle = columnCellStyle; classCell.SetCellValue(cd.Key); for (int i = 0; i < values.Count; i++) { columnIndex += 1; var newCell = newRow.CreateCell(columnIndex); newCell.CellStyle = columnCellStyle; newCell.SetCellValue(values[i]); } // 累加计算 rowStart += 1; } } foreach (var cd in grade.ClassesDictionary) { var className = cd.Key; } } #endregion using (var fs = File.OpenWrite(file)) { workbook.Write(fs); } return(Tuple.Create <bool, string>(true, "")); }