public List <FileInfo> PrepareDatabaseWorkbooks(ConfigurationEntity config)
{
var xls = new List <FileInfo>();
foreach (var database in m_databaseResults)
{
// Create a new file per database
var workbook = PrepareExcel(database.Value, config);
if (workbook != null)
{
xls.Add(workbook);
}
}
return(xls);
}
public FileWriter(ConfigurationEntity config)
{
m_config = config;
}
public FileInfo PrepareExcel(List <ResultEntity> results, ConfigurationEntity config)
{
var database = results.First().Database;
// Skip things like ReportServer
if (database.Equals("ReportServerTempDB", StringComparison.InvariantCultureIgnoreCase) ||
(config.UseDatabaseWhitelist &&
!config.DatabaseWhitelist.Any(s => database.Equals(s, StringComparison.InvariantCultureIgnoreCase))))
{
return(null);
}
var path = Tools.IsWindows() ? config.ExportFilePathWin : config.ExportFilePathMac;
var filename = new FileInfo($"{path}{Path.DirectorySeparatorChar}{FilePrefix}_{RemoveSpecialCharacters(database).Trim()}.xlsx");
var workbook = new XLWorkbook();
var ctr = 0;
foreach (var dr in results)
{
Console.WriteLine($"Writing worksheet {ctr++}...");
var worksheetName = GetMaxLengthSheetName(dr.Name);
if (workbook.Worksheets.TryGetWorksheet(worksheetName, out IXLWorksheet _))
{
var s = ctr.ToString().Trim();
worksheetName = worksheetName + s;
if (worksheetName.Length > 31)
{
worksheetName = worksheetName.Substring(0, 31 - s.Length) + s;
}
}
var worksheet = workbook.Worksheets.Add(worksheetName);
if (dr.Results != null && dr.Results.Rows.Count > 0)
{
for (var c = 0; c < dr.Results.Columns.Count; c++)
{
worksheet.Cell(1, c + 1).Value = dr.Results.Columns[c].ToString().Trim();
}
for (var r = 0; r < dr.Results.Rows.Count; r++)
{
for (var c = 0; c < dr.Results.Columns.Count; c++)
{
var cellValue = dr.Results.Rows[r][c].ToString().Trim();
if (cellValue.Length > 32767)
{
cellValue = cellValue.Substring(0, 32767);
}
worksheet.Cell(r + 2, c + 1).Value = cellValue;
}
}
worksheet.Range(1, 1, dr.Results.Rows.Count + 1, dr.Results.Columns.Count).CreateTable();
var rngHeaders = worksheet.Range(1, 1, 1, dr.Results.Columns.Count); // The address is relative to rngTable (NOT the worksheet)
rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rngHeaders.Style.Font.Bold = true;
rngHeaders.Style.Fill.BackgroundColor = XLColor.Black;
rngHeaders.Style.Font.FontColor = XLColor.White;
worksheet.Columns(1, dr.Results.Columns.Count).AdjustToContents();
foreach (var column in worksheet.Columns(1, dr.Results.Columns.Count))
{
if (column.Width > 254)
{
column.Width = 254;
}
}
}
else
{
worksheet.Cell("A1").Value = dr.Messages;
}
}
Console.WriteLine("Saving output...");
workbook.SaveAs(filename.FullName);
return(filename);
}