Exemple #1
0
        public void GenerateXlsxReport(System.Data.Common.DbConnection conn)
        {
            SetDbParams(conn);
            using (ExcelPackage package = (templateFile != null ? new ExcelPackage(newFile, templateFile) : new ExcelPackage(newFile)))
            {
                foreach (var tab in def.tabs)
                {
                    var ws     = GetWs(package, tab);
                    var riadok = 1;
                    var stlpec = 1;
                    var start  = 1;


                    // set start  position for Label - that would be Global or Local
                    if (templateFile != null)
                    {
                        ExcelNamedRange label = null;
                        try
                        {
                            label = ws.Names["Label"];
                        }
                        catch
                        {
                            //Console.WriteLine("{0} Exception caught.", e);
                            try
                            {
                                label = package.Workbook.Names["Label"];
                            }

                            catch
                            {
                                SimpleLog.WriteLog("Label field not found in this workbook/template");
                            }
                        }

                        if (label != null)
                        {
                            riadok = label.Start.Row;
                            stlpec = label.Start.Column;
                        }
                    }

                    var nadpis = "";
                    if (!(tab.title == null || tab.title == ""))
                    {
                        if (tab.title.Trim().ToUpper().Substring(0, 6) == "SELECT")
                        {
                            nadpis = conn.QuerySingle <string>(tab.title, QuerryArguments(tab.title));
                        }
                        else
                        {
                            nadpis = tab.title;
                        }
                    }

                    // Main Select
                    // https://github.com/ericmend/oracleClientCore-2.0/blob/master/test/dotNetCore.Data.OracleClient.test/OracleClientCore.cs
                    System.Data.IDataReader reader    = conn.ExecuteReader(tab.query, QuerryArguments(tab.query));
                    List <TableFields>      rowConfig = getDeclaration(reader, tab.fields);
                    outDef.tabs.Add(new Tab {
                        name = tab.name, title = tab.title, query = tab.query, fields = rowConfig
                    });

                    int r = 0;
                    int activeColCount = 0;
                    while (reader.Read())
                    {
                        r++;
                        //Initial section for sheet
                        if (r == 1)
                        {
                            if (nadpis != null && nadpis != "")
                            {
                                ws.Cells[riadok, stlpec].Value = nadpis;

                                if (templateFile == null)
                                {
                                    using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, stlpec - 1 + rowConfig.Where(o => o.order != 0).Count()])
                                    {
                                        rr.Merge = true;
                                        //rr.Style.Font.SetFromFont(new Font("Britannic Bold", 12, FontStyle.Italic));
                                        rr.Style.Font.Size = 12;
                                        rr.Style.Font.Bold = true;
                                        rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63));
                                        rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
                                        rr.Style.Fill.PatternType    = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                        //r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
                                        rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));
                                    }
                                }
                                riadok++;
                            }

                            // set Data position
                            if (templateFile != null)
                            {
                                ExcelNamedRange label = null;
                                try
                                {
                                    label = ws.Names["Data"];
                                }
                                catch
                                {
                                    try
                                    {
                                        label = package.Workbook.Names["Data"];
                                    }
                                    catch
                                    {
                                        SimpleLog.WriteLog("Data field not found in this workbook/template");
                                    }
                                }

                                if (label != null)
                                {
                                    riadok = label.Start.Row - 1; // Header je nad riadkom  (above row)
                                    stlpec = label.Start.Column;
                                }
                            }

                            //Add the headers
                            for (int i = 0; i < rowConfig.Count; i++)
                            {
                                if (rowConfig[i].order != 0)
                                {
                                    activeColCount++;
                                    if (templateFile == null || tab.printHeader)
                                    {
                                        ws.Cells[riadok, activeColCount + stlpec - 1].Value = rowConfig[i].title;
                                    }
                                    ws.Names.Add(rowConfig[i].name, ws.Cells[riadok, activeColCount + stlpec - 1]);
                                }
                            }

                            //Ok now format the values;
                            //ws.Cells[1, 1, 1, tab.fields.Count].Style.Font.Bold = true; //Font should be bold
                            //ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                            //ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.BackgroundColor.SetColor(Color.Aqua);

                            if (templateFile == null || tab.printHeader)
                            {
                                using (var range = ws.Cells[riadok, stlpec, riadok, activeColCount + stlpec - 1])
                                {
                                    range.Style.Font.Color.SetColor(Color.White);
                                    range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                    //range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
                                    range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
                                    range.Style.Font.Bold = true;
                                    range.Style.WrapText  = true;
                                    //Only need to grab the first cell of the merged range
                                    //ws.Cells[$"A{row}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                    //ws.Cells[$"A{row}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                }
                            }
                            riadok++;
                            // set line for data
                            start = riadok;
                        }

                        //data section, FETCH RECORDS
                        for (int i = 0; i < rowConfig.Count(); i++)
                        {
                            var colId = rowConfig[i].colId;
                            if (rowConfig[i].order != 0 && !reader.IsDBNull(colId))
                            {
                                var a = reader.GetValue(colId);
                                switch (rowConfig[i].type)
                                {
                                case "String":
                                    var pom = reader.GetString(colId);
                                    ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, pom.Substring(0, pom.Length /*- correctStringChars */));
                                    break;

                                case "Integer":
                                    ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetInt32(colId));
                                    break;

                                case "DateTime":
                                    ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId));
                                    break;

                                case "Decimal":
                                    ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDecimal(colId));
                                    break;

                                case "Byte[]":
                                    ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetByte(colId));
                                    break;

                                default:
                                    ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetValue(colId).ToString());
                                    break;
                                }
                                ;
                            }
                        }
                        riadok++;
                    }

                    // no rows
                    if (r == 0)
                    {
                        if (nadpis != null)
                        {
                            ws.Cells[riadok, stlpec].Value = nadpis;
                            using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, 8 + stlpec])
                            {
                                rr.Merge           = true;
                                rr.Style.Font.Size = 12;
                                rr.Style.Font.Bold = true;
                                rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63));
                                rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
                                rr.Style.Fill.PatternType    = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242));
                            }
                            riadok++;
                        }
                        ws.Cells[riadok, stlpec].Value = "No rows in querry result";
                        SimpleLog.WriteLog("No rows in Query from  " + tab.name);
                    }
                    else
                    {
                        foreach (var row in rowConfig.Where(o => o.format != null && o.format != "auto"))
                        {
                            ws.Cells[start, row.order + stlpec - 1, riadok, row.order + stlpec - 1].Style.Numberformat.Format = row.format;
                        }
                        //ws.Cells[1, 1, Rows, 1].Style.Numberformat.Format = "#,##0";
                        //ws.Cells[1, 3, Rows, 3].Style.Numberformat.Format = "YYYY-MM-DD";
                        //ws.Cells[1, 4, Rows, 5].Style.Numberformat.Format = "#,##0.00";

                        // add comp fields
                        // worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress);

                        // Autofit
                        if (templateFile == null)
                        {
                            ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFitColumns();
                            foreach (var row in rowConfig.Where(o => o.order != 0))
                            {
                                if (ws.Column(row.order).Width < row.minsize)
                                {
                                    ws.Column(row.order).Width = row.minsize;
                                }
                            }
                        }

                        //Create an autofilter(global settings) for the range
                        if (def.autofilter)
                        {
                            ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFilter = true;
                        }
                    }

                    if (enabledTimestamp && def.timestamp)
                    {
                        ws.Cells[riadok + 2, stlpec].Value = "Created :  " + DateTime.Now.ToString("dd.MM.yyyy H:mm:ss");
                    }
                }

                package.Workbook.Calculate();

                // Set document properties
                package.Workbook.Properties.Comments = "Created with EpSqlGen Copyright © 2018 Miroslav Dubovsky";
                package.Workbook.Properties.Created  = DateTime.Now;
                package.Workbook.Properties.Title    = outFileName;
                var pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Author");
                if (pomProp != null)
                {
                    package.Workbook.Properties.Author = pomProp;
                }
                pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Company");
                if (pomProp != null)
                {
                    package.Workbook.Properties.Company = pomProp;
                }
                if (def.version != null)
                {
                    package.Workbook.Properties.Subject = "Template " + defFileName + ", version:" + def.version;
                }
                else
                {
                    package.Workbook.Properties.Subject = "Template " + defFileName;
                }

                package.Save();
            }
            if (!enabledConsoleLog)
            {
                Console.Write(JsonConvert.SerializeObject(new { output_file = newFile.FullName }, Formatting.Indented));
            }
            else
            {
                SimpleLog.WriteLog("Generated file: " + newFile.FullName);
            }
        }