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