private int WriteReportToPage(ISheet summaryPage, DataTable table, int startingRow, Boolean autoSize = true)
{
if (table == null)
{
return(startingRow);
}
var iRunningColumn = 0;
var cellStyle = summaryPage.Workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = IndexedColors.Grey50Percent.Index;
var failCellStyle = summaryPage.Workbook.CreateCellStyle();
failCellStyle.FillPattern = FillPattern.SolidForeground;
failCellStyle.FillForegroundColor = IndexedColors.Red.Index;
IRow excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
ICell excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
excelCell.SetCellValue(table.TableName);
startingRow++;
excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
{
continue;
}
var runCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
iRunningColumn++;
runCell.SetCellValue(tCol.Caption);
runCell.CellStyle = cellStyle;
}
startingRow++;
var writer = new ExcelCellVisualValue(summaryPage.Workbook);
foreach (DataRow row in table.Rows)
{
excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
startingRow++;
iRunningColumn = -1;
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
{
continue;
}
iRunningColumn++;
if (row[tCol] == DBNull.Value)
{
continue;
}
excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
// ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
if (row[tCol] is IVisualValue)
{
writer.SetCell(excelCell, (IVisualValue)row[tCol]);
}
else
{
switch (tCol.DataType.Name)
{
case "String":
excelCell.SetCellValue((string)row[tCol]);
break;
case "Int32":
excelCell.SetCellValue(Convert.ToInt32(row[tCol]));
break;
default:
excelCell.SetCellValue((string)row[tCol]);
break;
}
}
}
}
if (!autoSize)
{
return(startingRow + 1);
}
// sets all used columns to autosize
for (int irun = 0; irun < iRunningColumn; irun++)
{
summaryPage.AutoSizeColumn(irun);
}
return(startingRow + 1);
}
private int WriteReportToPage(ISheet summaryPage, DataTable table, int startingRow, Boolean autoSize = true)
{
if (table == null)
return startingRow;
var iRunningColumn = 0;
var cellStyle = summaryPage.Workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = IndexedColors.Grey50Percent.Index;
var failCellStyle = summaryPage.Workbook.CreateCellStyle();
failCellStyle.FillPattern = FillPattern.SolidForeground;
failCellStyle.FillForegroundColor = IndexedColors.Red.Index;
IRow excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
ICell excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
excelCell.SetCellValue(table.TableName);
startingRow++;
excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
continue;
var runCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
iRunningColumn++;
runCell.SetCellValue(tCol.Caption);
runCell.CellStyle = cellStyle;
}
startingRow++;
var writer = new ExcelCellVisualValue(summaryPage.Workbook);
foreach (DataRow row in table.Rows)
{
excelRow = summaryPage.GetRow(startingRow) ?? summaryPage.CreateRow(startingRow);
startingRow++;
iRunningColumn = -1;
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
continue;
iRunningColumn++;
if (row[tCol] == DBNull.Value)
continue;
excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
// ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
if (row[tCol] is IVisualValue)
{
writer.SetCell(excelCell, (IVisualValue) row[tCol]);
}
else
{
switch (tCol.DataType.Name)
{
case "String":
excelCell.SetCellValue((string) row[tCol]);
break;
case "Int32":
excelCell.SetCellValue(Convert.ToInt32(row[tCol]));
break;
default:
excelCell.SetCellValue((string) row[tCol]);
break;
}
}
}
}
if (!autoSize)
return startingRow + 1;
// sets all used columns to autosize
for (int irun = 0; irun < iRunningColumn; irun++)
{
summaryPage.AutoSizeColumn(irun);
}
return startingRow + 1;
}
private static bool CreateDetailSheet(ISheet detailSheet, TwoLevelRequirementPointer <Zone, Space> requirementPointer)
{
try
{
var excelRow = detailSheet.GetRow(0) ?? detailSheet.CreateRow(0);
var excelCell = excelRow.GetCell(0) ?? excelRow.CreateCell(0);
SetHeader(excelCell);
excelCell.SetCellValue("Zone and spaces report");
var rep = new TwoLevelDetailedGridReport <Zone, Space>(requirementPointer);
rep.PrepareReport();
var iRunningRow = 2;
var iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(@"Name:"); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(requirementPointer.Name); // writes cell and moves index forward
iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(@"External system:"); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(requirementPointer.ExternalSystem); // writes cell and moves index forward
iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(@"External id:"); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(requirementPointer.ExternalId); // writes cell and moves index forward
iRunningRow++; // one empty row
iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(@"Matching categories:"); // writes cell and moves index forward
foreach (var cat in rep.RequirementCategories)
{
iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(cat.Classification); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(cat.Code); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(cat.Description); // writes cell and moves index forward
}
iRunningRow++; // one empty row
iRunningColumn = 0;
var cellStyle = detailSheet.Workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = IndexedColors.Grey50Percent.Index;
var table = rep.AttributesGrid;
excelRow = detailSheet.GetRow(iRunningRow) ?? detailSheet.CreateRow(iRunningRow);
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
{
continue;
}
excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
iRunningColumn++;
excelCell.SetCellValue(tCol.Caption);
excelCell.CellStyle = cellStyle;
}
iRunningRow++;
var writer = new ExcelCellVisualValue(detailSheet.Workbook);
foreach (DataRow row in table.Rows)
{
excelRow = detailSheet.GetRow(iRunningRow) ?? detailSheet.CreateRow(iRunningRow);
iRunningRow++;
iRunningColumn = -1;
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
{
continue;
}
iRunningColumn++;
if (row[tCol] == DBNull.Value)
{
continue;
}
excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
// ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
if (row[tCol] is IVisualValue)
{
writer.SetCell(excelCell, (IVisualValue)row[tCol]);
}
else
{
switch (tCol.DataType.Name)
{
case "String":
excelCell.SetCellValue((string)row[tCol]);
break;
case "Int32":
excelCell.SetCellValue(Convert.ToInt32(row[tCol]));
break;
default:
excelCell.SetCellValue((string)row[tCol]);
break;
}
}
}
}
//// sets all used columns to autosize
//for (var irun = 0; irun < iRunningColumn; irun++)
//{
// detailSheet.AutoSizeColumn(irun);
//}
return(true);
}
catch (Exception e)
{
//log the error
Logger.Error("Failed to create detail Sheet", e);
return(false);
}
}
private static bool CreateDetailSheet(ISheet detailSheet, TwoLevelRequirementPointer<Zone, Space> requirementPointer)
{
try
{
var excelRow = detailSheet.GetRow(0) ?? detailSheet.CreateRow(0);
var excelCell = excelRow.GetCell(0) ?? excelRow.CreateCell(0);
SetHeader(excelCell);
excelCell.SetCellValue("Zone and spaces report");
var rep = new TwoLevelDetailedGridReport<Zone, Space>(requirementPointer);
rep.PrepareReport();
var iRunningRow = 2;
var iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(@"Name:"); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(requirementPointer.Name); // writes cell and moves index forward
iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(@"External system:"); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(requirementPointer.ExternalSystem); // writes cell and moves index forward
iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(@"External id:"); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(requirementPointer.ExternalId); // writes cell and moves index forward
iRunningRow++; // one empty row
iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(@"Matching categories:"); // writes cell and moves index forward
foreach (var cat in rep.RequirementCategories)
{
iRunningColumn = 0;
excelRow = detailSheet.GetRow(iRunningRow++) ?? detailSheet.CreateRow(iRunningRow - 1); // prepares a row and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(cat.Classification); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(cat.Code); // writes cell and moves index forward
(excelRow.GetCell(iRunningColumn++) ?? excelRow.CreateCell(iRunningColumn - 1)).SetCellValue(cat.Description); // writes cell and moves index forward
}
iRunningRow++; // one empty row
iRunningColumn = 0;
var cellStyle = detailSheet.Workbook.CreateCellStyle();
cellStyle.BorderBottom = BorderStyle.Thick;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = IndexedColors.Grey50Percent.Index;
var table = rep.AttributesGrid;
excelRow = detailSheet.GetRow(iRunningRow) ?? detailSheet.CreateRow(iRunningRow);
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
continue;
excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
iRunningColumn++;
excelCell.SetCellValue(tCol.Caption);
excelCell.CellStyle = cellStyle;
}
iRunningRow++;
var writer = new ExcelCellVisualValue(detailSheet.Workbook);
foreach (DataRow row in table.Rows)
{
excelRow = detailSheet.GetRow(iRunningRow) ?? detailSheet.CreateRow(iRunningRow);
iRunningRow++;
iRunningColumn = -1;
foreach (DataColumn tCol in table.Columns)
{
if (tCol.AutoIncrement)
continue;
iRunningColumn++;
if (row[tCol] == DBNull.Value)
continue;
excelCell = excelRow.GetCell(iRunningColumn) ?? excelRow.CreateCell(iRunningColumn);
// ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
if (row[tCol] is IVisualValue)
{
writer.SetCell(excelCell, (IVisualValue)row[tCol]);
}
else
{
switch (tCol.DataType.Name)
{
case "String":
excelCell.SetCellValue((string)row[tCol]);
break;
case "Int32":
excelCell.SetCellValue(Convert.ToInt32(row[tCol]));
break;
default:
excelCell.SetCellValue((string)row[tCol]);
break;
}
}
}
}
//// sets all used columns to autosize
//for (var irun = 0; irun < iRunningColumn; irun++)
//{
// detailSheet.AutoSizeColumn(irun);
//}
return true;
}
catch (Exception e)
{
//log the error
Logger.Error("Failed to create detail Sheet", e);
return false;
}
}
