/// <summary> /// 产生新的单据 /// </summary> /// <param name="sheetSubmitDto"></param> private Sheet GenerateSheet(SheetSubmitDto sheetSubmitDto) { var sheet = new Sheet(); sheet.SheetName = sheetSubmitDto.SheetHeader.GetDataOrEmpty("sheetName"); if (string.IsNullOrEmpty(sheet.SheetName)) { throw new UserFriendlyException(L("单据标志sheetName不能为空")); } sheet.SheetSN = sheetSubmitDto.SheetHeader.GetDataOrEmpty("sheetSN"); if (string.IsNullOrEmpty(sheet.SheetSN)) { throw new UserFriendlyException(L("单据编号不能为空")); } sheet.SheetDate = sheetSubmitDto.SheetHeader.GetDataOrDefault <DateTime?>("sheetDate") ?? DateTime.Now; sheet.BusinessType = sheetSubmitDto.SheetHeader.GetDataOrEmpty("businessType"); sheet.HandlerId = sheetSubmitDto.SheetHeader.GetDataOrDefault <long?>("handlerId"); sheet.ProjectId = sheetSubmitDto.SheetHeader.GetDataOrDefault <int?>("projectId"); sheet.UnitId = sheetSubmitDto.SheetHeader.GetDataOrDefault <int?>("unitId"); //将表头数据保存至属性中 foreach (var key in sheetSubmitDto.SheetHeader.Keys) { sheet.SetPropertyValue(key, sheetSubmitDto.SheetHeader[key]); } //将表格内容保存 foreach (var rowValue in sheetSubmitDto.SheetValues) { var sheetContent = new SheetContent(); foreach (var key in rowValue.Keys) { sheetContent.SetPropertyValue(key, rowValue[key]); } sheet.SheetContents.Add(sheetContent); } return(sheet); }
private static bool Export(List <DisplayResults> allDisplayResults, bool splitPackageIntoSheets, out string errMsg, out MemoryStream excelStream) { errMsg = string.Empty; excelStream = null; try { if (allDisplayResults == null || allDisplayResults.Count == 0) { errMsg = "Nothing to export."; return(false); } HardDefinitions.ExportDescriptionMode descriptionMode = allDisplayResults.First().info.exportDescriptionMode; List <SheetContent> sheetsContent = new List <SheetContent>(); if (!splitPackageIntoSheets) { foreach (DisplayResults displayResults in allDisplayResults) { if (displayResults == null || displayResults.displayPages == null || displayResults.displayPages.Count == 0) { continue; } List <ExportPage> expPages = GetPackagePages(displayResults); SheetContent sheetContent = new SheetContent { Caption = displayResults.info.title, SubCaption = displayResults.info.subtitle, SheetName = displayResults.info.button, Pages = expPages }; sheetsContent.Add(sheetContent); } } else // this option is only called with a single package (see ExportDisplayPages above) { DisplayResults displayResults = allDisplayResults[0]; if (displayResults == null || displayResults.displayPages == null || displayResults.displayPages.Count == 0) { errMsg = "Nothing to export."; return(false); } ; List <ExportPage> expPages = GetPackagePages(displayResults); foreach (ExportPage expPage in expPages) { SheetContent sheetContent = new SheetContent { SheetName = expPage.PageName, Caption = displayResults.info.title, SubCaption = displayResults.info.subtitle, Pages = new List <ExportPage>() { expPage } }; sheetsContent.Add(sheetContent); } } if (sheetsContent.Count == 0) { errMsg = "Nothing to export."; return(false); } ; ExcelPackage excel = new ExcelPackage(); foreach (SheetContent sheetContent in sheetsContent) { string sheetName = VerifyUniqueSheetName(excel.Workbook.Worksheets, sheetContent.SheetName); var workSheet = excel.Workbook.Worksheets.Add(sheetName); int chartCount = 0; int rPos = 1; workSheet.Cells[rPos, 1].Style.Font.Bold = true; workSheet.Cells[rPos, 1].Style.Font.Size = 16; workSheet.Cells[rPos, 1].Style.Font.Color.SetColor(Color.DarkBlue); workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(sheetContent.Caption)); if (!string.IsNullOrEmpty(sheetContent.SubCaption)) { workSheet.Cells[rPos, 1].Style.Font.Color.SetColor(Color.DarkBlue); workSheet.Cells[rPos, 1].Style.Font.Bold = true; workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(sheetContent.SubCaption)); } ++rPos; foreach (ExportPage exportPage in sheetContent.Pages) { if (!string.IsNullOrEmpty(exportPage.Caption)) { workSheet.Cells[rPos, 1].Style.Font.Bold = true; workSheet.Cells[rPos, 1].Style.Font.Size = 14; workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(exportPage.Caption)); ++rPos; } foreach (ExportTable exportTable in exportPage.Tables) { if (!string.IsNullOrEmpty(exportTable.Caption)) { workSheet.Cells[rPos, 1].Style.Font.Bold = true; workSheet.Cells[rPos, 1].Style.Font.Size = 12; workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(exportTable.Caption)); } if (!string.IsNullOrEmpty(exportTable.SubCaption)) { workSheet.Cells[rPos, 1].Style.Font.Bold = true; workSheet.Cells[rPos++, 1].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(exportTable.SubCaption)); } for (int ch = 0; ch < exportTable.ColHeaders.Count; ++ch) { workSheet.Cells[rPos, ch + 2].Style.Font.Italic = true; workSheet.Cells[rPos, ch + 2].Value = ReplaceBr(EM_Helpers.StripHTMLSpecialCharacters(exportTable.ColHeaders[ch])); } for (int rh = 1; rh <= exportTable.RowHeaders.Count; ++rh) { workSheet.Cells[rPos + rh, 1].Style.Font.Italic = true; workSheet.Cells[rPos + rh, 1].Value = exportTable.RowHeaders[rh - 1]; } ExcelRangeBase pastedTable = workSheet.Cells[++rPos, 2].LoadFromDataTable(exportTable.Content, false); if (pastedTable != null) // may be null if for example Content does not have any rows (just column-headers) { for (int c = pastedTable.Start.Column; c <= pastedTable.End.Column; c++) { List <string> nf = exportTable.NumberFormats[c - pastedTable.Start.Column]; List <string> sv = exportTable.StringValues[c - pastedTable.Start.Column]; for (int r = pastedTable.Start.Row; r <= pastedTable.End.Row; r++) { if (sv[r - pastedTable.Start.Row] == null) { workSheet.Cells[r, c].Style.Numberformat.Format = nf[r - pastedTable.Start.Row]; } else { workSheet.Cells[r, c].Value = sv[r - pastedTable.Start.Row]; } } } if (exportTable.Graph != null && exportTable.Graph.allSeries.Count > 0) { List <eChartType> chartTypes = GetAllChartTypes(exportTable.Graph); ExcelChart baseChart = (ExcelChart)workSheet.Drawings.AddChart("myChart" + (++chartCount), chartTypes[0]); Dictionary <eChartType, ExcelChart> allAreas = new Dictionary <eChartType, ExcelChart> { { chartTypes[0], baseChart } }; for (int i = 1; i < chartTypes.Count; i++) { allAreas.Add(chartTypes[i], baseChart.PlotArea.ChartTypes.Add(chartTypes[i])); } baseChart.SetSize(900, 500); baseChart.SetPosition(pastedTable.Start.Row, 0, pastedTable.End.Column, 50); baseChart.Title.Text = exportTable.Graph.title; baseChart.Axis[0].LabelPosition = eTickLabelPosition.Low; if (exportTable.Graph.seriesInRows) { int labelPos = pastedTable.Start.Row - 1; if (!string.IsNullOrEmpty(exportTable.Graph.axisX.valuesFrom)) { for (int r = pastedTable.Start.Row; r <= pastedTable.End.Row; r++) { if (workSheet.Cells[r, pastedTable.Start.Column - 1].Value.ToString() == exportTable.Graph.axisX.valuesFrom) { labelPos = r; } } } ExcelRange axisLabels = workSheet.Cells[labelPos, pastedTable.Start.Column, labelPos, pastedTable.End.Column]; for (int r = pastedTable.Start.Row; r <= pastedTable.End.Row; r++) { string seriesName = workSheet.Cells[r, pastedTable.Start.Column - 1].Value.ToString(); DisplayResults.DisplayPage.DisplayTable.DisplayGraph.Series s = exportTable.Graph.allSeries.Where(x => x.name == seriesName).First(); if (s.visible) { ExcelChartSerie serie = allAreas[GetChartType(s)].Series.Add(workSheet.Cells[r, pastedTable.Start.Column, r, pastedTable.End.Column], axisLabels); SetSerieDetails(s, serie); } } } else { int labelPos = pastedTable.Start.Column - 1; if (!string.IsNullOrEmpty(exportTable.Graph.axisX.valuesFrom)) { for (int c = pastedTable.Start.Column; c <= pastedTable.End.Column; c++) { if (workSheet.Cells[pastedTable.Start.Row - 1, c].Value.ToString() == exportTable.Graph.axisX.valuesFrom) { labelPos = c; } } } ExcelRange axisLabels = workSheet.Cells[pastedTable.Start.Row, labelPos, pastedTable.End.Row, labelPos]; for (int c = pastedTable.Start.Column; c <= pastedTable.End.Column; c++) { string seriesName = workSheet.Cells[pastedTable.Start.Row - 1, c].Value.ToString(); DisplayResults.DisplayPage.DisplayTable.DisplayGraph.Series s = exportTable.Graph.allSeries.Where(x => x.name == seriesName).First(); if (s.visible) { ExcelChartSerie serie = allAreas[GetChartType(s)].Series.Add(workSheet.Cells[pastedTable.Start.Row, c, pastedTable.End.Row, c], axisLabels); SetSerieDetails(s, serie); } } } } rPos += exportTable.RowHeaders.Count; if (descriptionMode == HardDefinitions.ExportDescriptionMode.InSheets) { InsertDescription(workSheet, ref rPos, exportTable.description); } rPos++; // Keep a space between tables } } if (descriptionMode == HardDefinitions.ExportDescriptionMode.InSheets) { InsertDescription(workSheet, ref rPos, exportPage.description); } rPos++; // Keep an extra space between pages } } AddDescriptionPage(excel, allDisplayResults[0], descriptionMode); // todo: not yet clear whether the file-selection should be in the presenter or the library excelStream = new MemoryStream(); excel.SaveAs(excelStream); excel.Dispose(); return(true); } catch (Exception exception) { errMsg = exception.Message; return(false); } }