Ejemplo n.º 1
0
        /// <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);
        }
Ejemplo n.º 2
0
        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); }
        }