Exemple #1
0
 public override void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
 {
     DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
     ExcelHelper.Workbook(fileName, b => {
         var ws = (MSExcel._Worksheet)b.Worksheets["rep" + settings.ReportCode.ToString()];
         FormatExcelFile(ws, reportData.Tables["Results"], settings.ReportCaption, CountDownRows);
     });
     ProfileHelper.End();
 }
Exemple #2
0
        public void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
        {
            var optimizationSettings = (OptimizationEfficiencySettings)settings;

            _reportCode    = optimizationSettings.ReportCode;
            _reportCaption = optimizationSettings.ReportCaption;
            _beginDate     = optimizationSettings.BeginDate;
            _endDate       = optimizationSettings.EndDate;
            _clientId      = optimizationSettings.ClientId;
            _concurents    = optimizationSettings.Concurents;
            _supplierName  = optimizationSettings.SupplierName;

            DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
            FormatExcel(reportData, fileName);
        }
Exemple #3
0
        public virtual void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
        {
            DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
            ProfileHelper.Next("FormatExcel");
            var file     = fileName;
            var result   = reportData.Tables["Results"];
            var reportId = settings.ReportCode;
            var caption  = settings.ReportCaption;

            ExcelHelper.Workbook(file, b => {
                var ws = (_Worksheet)b.Worksheets["rep" + reportId.ToString()];
                FormatExcelFile(ws, result, caption, 0);
            });
            ProfileHelper.End();
        }
        public override void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings reportSettings)
        {
            var settings = (CostDynamicSettings)reportSettings;
            var results  = reportData.Tables["Results"];

            results.Columns.Remove("Id");
            DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);

            ExcelHelper.Workbook(fileName, b => {
                var sheet  = ExcelHelper.GetSheet(b, settings.ReportCode);
                sheet.Name = reportSettings.ListName;
                sheet.Activate();
                var row = 0;
                foreach (var filter in settings.Filters)
                {
                    ExcelHelper.Header(sheet, row, 10, filter);
                    row++;
                }

                ((Range)sheet.Cells[row + 1, 1]).Select();
                var sheetRow = ((Range)sheet.Application.Selection).EntireRow;
                sheetRow.Insert(XlInsertShiftDirection.xlShiftDown, Type.Missing);

                ((Range)sheet.Cells[row + 1, 2]).Value2 = settings.Date.ToShortDateString();
                for (var i = 0; i < settings.Dates.Count; i++)
                {
                    ExcelHelper.Merge(sheet, row, (i + 1) * 2, 2, settings.Dates[i].Label);
                }

                row++;
                MakeHeder(row, sheet, 45);

                row++;
                ExcelHelper.FormatHeader(sheet, row, results);
                MakeHeder(row, sheet);

                var tableBegin      = settings.Filters.Count + 1;
                var tableHeaderSize = 2;
                var count           = results.Rows.Count;
                var columnCount     = results.Columns.Count;
                var tableEnd        = tableBegin + count + 1;

                var range            = sheet.get_Range(sheet.Cells[tableBegin, 1], sheet.Cells[tableEnd, columnCount]);
                range.Borders.Weight = XlBorderWeight.xlThin;

                sheet.get_Range(sheet.Cells[tableBegin + tableHeaderSize, 2], sheet.Cells[tableEnd, columnCount]).NumberFormat = "0.00%";
            });
        }
Exemple #5
0
        public override void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
        {
            var result = reportData.Tables["Results"];
            var ppz    = result.NewRow();

            ppz[0] = "Отчет сформирован: " + DateTime.Now;
            result.Rows.InsertAt(ppz, 0);

            var reportParameters = new List <object>();

            foreach (var reportParam in ReportParams)
            {
                var typeReportParam = reportParam.Value.GetType();
                if (typeReportParam.IsGenericType)
                {
                    if (!ParamNOVisualisation.Contains(reportParam.Key))
                    {
                        var itemList = (List <ulong>)reportParam.Value;

                        var namesList = (AssiciateReportParams[reportParam.Key](itemList, connection));
                        namesList.Sort();
                        var itemString = string.Join(" ,", namesList.ToArray());
                        if (itemString.Length > 2048)
                        {
                            itemString = itemString.Substring(0, 2047);
                        }
                        reportParameters.Add(GetDescription(reportParam.Key) + ": " + itemString);
                    }
                }
                if (typeReportParam == typeof(bool))
                {
                    if (!ParamNOVisualisation.Contains(reportParam.Key))
                    {
                        var yesNo = (bool)reportParam.Value ? ": Да" : ": Нет";
                        reportParameters.Add(GetDescription(reportParam.Key) + yesNo);
                    }
                }
                if (typeReportParam == typeof(Int32))
                {
                    if (!ParamNOVisualisation.Contains(reportParam.Key))
                    {
                        var value = Convert.ToUInt32(reportParam.Value);
                        if (AssiciateReportParams.ContainsKey(reportParam.Key))
                        {
                            var tempList = new List <ulong> {
                                value
                            };
                            var namesList = (AssiciateReportParams[reportParam.Key](tempList, connection));
                            if (namesList.Count > 0)
                            {
                                reportParameters.Add(GetDescription(reportParam.Key) + ": " + namesList[0]);
                            }
                        }
                        else
                        {
                            reportParameters.Add(GetDescription(reportParam.Key) + ": " + value);
                        }
                    }
                }
            }
            var countDownRows = reportParameters.Count + 5;
            var position      = 1;

            foreach (var reportParameter in reportParameters)
            {
                ppz    = result.NewRow();
                ppz[0] = reportParameter;
                result.Rows.InsertAt(ppz, position);
                position++;
            }
            var delemitRow = result.NewRow();

            result.Rows.InsertAt(delemitRow, position);
            delemitRow = result.NewRow();
            result.Rows.InsertAt(delemitRow, position++);

            DataTableToExcel(result, fileName, settings.ReportCode);

            ExcelHelper.Workbook(fileName, b => {
                var ws  = (MSExcel._Worksheet)b.Worksheets["rep" + settings.ReportCode.ToString()];
                ws.Name = _reportCaption.Substring(0, (_reportCaption.Length < MaxListName) ? _reportCaption.Length : MaxListName);
                ws.Activate();

                if (countDownRows > 0)
                {
                    for (int j = 1; j < countDownRows - 1; j++)
                    {
                        for (int i = 0; i < countDownRows - 3; i++)
                        {
                            ws.Cells[1 + i, j] = ws.Cells[2 + i, j];
                        }
                        ws.Cells[countDownRows - 2, j] = "";
                        ws.get_Range("A" + j.ToString(), "Z" + j.ToString()).Merge();
                    }
                }
                if (countDownRows == 0)
                {
                    countDownRows = 2;
                }
                for (int i = 4; i < 20; i++)
                {
                    ws.Cells[1, i] = "";
                }
                for (int i = 0; i < result.Columns.Count; i++)
                {
                    ws.Cells[countDownRows - 1, i + 1] = "";
                    ws.Cells[countDownRows - 1, i + 1] = result.Columns[i].Caption;
                    if (countDownRows != 2)
                    {
                        ws.Cells[1, 4] = "";
                    }

                    if (result.Columns[i].ExtendedProperties.ContainsKey("Width"))
                    {
                        ((MSExcel.Range)ws.Columns[i + 1, Type.Missing]).ColumnWidth = ((int?)result.Columns[i].ExtendedProperties["Width"]).Value;
                    }
                    else
                    {
                        ((MSExcel.Range)ws.Columns[i + 1, Type.Missing]).AutoFit();
                    }
                    if (result.Columns[i].ExtendedProperties.ContainsKey("Color"))
                    {
                        ws.get_Range(ws.Cells[countDownRows, i + 1], ws.Cells[result.Rows.Count + 1, i + 1]).Interior.Color = System.Drawing.ColorTranslator.ToOle((System.Drawing.Color)result.Columns[i].ExtendedProperties["Color"]);
                    }
                }

                //рисуем границы на всю таблицу
                ws.get_Range(ws.Cells[countDownRows - 1, 1], ws.Cells[result.Rows.Count + 1, result.Columns.Count]).Borders.Weight = MSExcel.XlBorderWeight.xlThin;

                //Устанавливаем шрифт листа
                ws.Rows.Font.Size = 8;
                ws.Rows.Font.Name = "Arial Narrow";
                ws.Activate();

                //Устанавливаем АвтоФильтр на все колонки
                ws.Range[ws.Cells[countDownRows - 1, 1], ws.Cells[result.Rows.Count + 1, result.Columns.Count]].Select();
                ((MSExcel.Range)ws.Application.Selection).AutoFilter(1, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
            });
            ProfileHelper.End();
        }
 public void WriteReportToFile(DataSet reportData, string fileName, BaseReportSettings settings)
 {
     DataTableToExcel(reportData.Tables["Results"], fileName, settings.ReportCode);
     FormatExcel(reportData, fileName, settings as PharmacyMixedSettings);
 }
Exemple #7
0
        public void WriteReportToFile(DataSet data, string file, BaseReportSettings settings)
        {
            var workbook = new Workbook();

            foreach (var row in data.Tables["prices"].Rows.Cast <DataRow>())
            {
                var offers = data.Tables[row["PriceCode"].ToString()];
                if (offers == null)
                {
                    continue;
                }

                var name = row["ShortName"] + " " + row["PriceName"];
                if (name.Length > 26)
                {
                    name = name.Substring(0, 26);
                }

                var header = new CellStyle {
                    Warp = true,
                    Font = new Font("Arial", 11)
                    {
                        Bold = true
                    },
                    HorizontalAlignment = HorizontalAlignment.Center
                };

                var body = new CellStyle {
                    Borders = Borders.Box(BorderStyle.Thin)
                };

                var sheet = new Worksheet(name);

                sheet.Cells[0, 0] = new Cell("Код")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[0] = 11 * 255;
                sheet.Cells[0, 1]          = new Cell("Код изготовителя")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[1] = 11 * 255;
                sheet.Cells[0, 2]          = new Cell("Наименование")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[2] = 30 * 255;
                sheet.Cells[0, 3]          = new Cell("Изготовитель")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[3] = 25 * 255;
                sheet.Cells[0, 4]          = new Cell("Цена")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[4] = (ushort)(15.5 * 255);
                sheet.Cells[0, 5]          = new Cell("Остаток")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[5] = 17 * 255;
                sheet.Cells[0, 6]          = new Cell("Срок годности")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[6] = 10 * 255;
                sheet.Cells[0, 7]          = new Cell("Примечание")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[7] = 20 * 255;
                sheet.Cells[0, 8]          = new Cell("ЖНВЛС")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[0] = 11 * 255;
                sheet.Cells[0, 9]          = new Cell("Лек. ассортимент")
                {
                    Style = header
                };
                sheet.Cells.ColumnWidth[0] = 15 * 255;

                var i = 1;
                foreach (var offer in offers.Rows.Cast <DataRow>())
                {
                    sheet.Cells[i, 0] = new Cell(offer["Code"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 1] = new Cell(offer["CodeCr"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 2] = new Cell(offer["Product"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 3] = new Cell(offer["Producer"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 4] = new Cell(offer["Cost"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 5] = new Cell(offer["Quantity"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 6] = new Cell(offer["Period"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 7] = new Cell(offer["Note"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 8] = new Cell(offer["VitallyImportant"])
                    {
                        Style = body
                    };
                    sheet.Cells[i, 9] = new Cell(offer["Pharmacie"])
                    {
                        Style = body
                    };
                    i++;
                }


                workbook.Worksheets.Add(sheet);
            }
            workbook.Save(file);
        }