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