public void GenerateJson(System.Data.Common.DbConnection conn) { var output = new Dictionary <String, Object>(); SetDbParams(conn); foreach (var tab in def.tabs) { System.Data.IDataReader reader = conn.ExecuteReader(tab.query, QuerryArguments(tab.query)); var results = new List <Dictionary <string, object> >(); List <TableFields> rowConfig = GetDeclaration(reader, tab.fields); outDef.tabs.Add(new Tab { name = tab.name, title = tab.title, query = tab.query, fields = rowConfig }); while (reader.Read()) { results.Add(SerializeRow(rowConfig, reader)); } output.Add(tab.name, results); } // Create output file or print to console if (!consoleMode) { newFile = new FileInfo(Path.Combine(outputDir.FullName, Path.GetFileName(outFileName))); if (newFile.Exists) { newFile.Delete(); // ensures we create a new workbook newFile = new FileInfo(Path.Combine(outputDir.FullName, Path.GetFileName(outFileName))); } using (StreamWriter sw = newFile.CreateText()) { sw.Write(JsonConvert.SerializeObject(output, Formatting.Indented)); } // LOg or return out file if (!enabledOutput) { Console.Write(JsonConvert.SerializeObject(new { output_file = newFile.FullName }, Formatting.Indented)); } else { SimpleLog.WriteLog("Generated file: " + newFile.FullName); } } else { Console.Write(JsonConvert.SerializeObject(output, Formatting.Indented)); } }
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 "Date": 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); } }