public static void WriteList(AbstractList list, string path, IXLSXWriteDataStrategy writeDataStrategy, IXLSXWriteHeaderStrategy headerStrategy, ListType listType)
{
CellStyleFactory.ResetStyles();
_sheet = SheetFactory.CreateSheet(path);
_list = list;
headerStrategy.FormatHeader(_list, _sheet, listType);
WriteColumnsNames();
if (listType == ListType.BoltsDelivery)
{
CellRangeAddress blankRegion =
new CellRangeAddress(_sheet.LastRowNum, _sheet.LastRowNum, _list.Columns.Count - 1, _list.Columns.Count + 1);
IRow columnsRow = _sheet.GetRow(_sheet.LastRowNum);
columnsRow.CreateCell(_list.Columns.Count);
columnsRow.CreateCell(_list.Columns.Count + 1);
columnsRow.GetCell(_list.Columns.Count + 1).CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(_sheet.Workbook);
_sheet.AddMergedRegion(blankRegion);
}
writeDataStrategy.WriteData(list, _sheet);
AutosizeAllColumns();
WriteWorkbook(path);
OpenWorkbook(path);
}
private static void WriteColumnsNames()
{
IRow row = SheetUtils.CreateRow(_sheet);
row.Height = 900;
int numColumns = _list.Columns.Count;
for (int i = 0; i < numColumns; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(_list.Columns.ElementAt(i).Name);
cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(_sheet.Workbook);
}
}
public void WriteData(AbstractList list, ISheet sheet)
{
ICollection <StringColumn> columns = list.Columns;
int numDataChunks = columns.First().Data.Count;
int numColumns = columns.Count;
_lastDataColumn = numColumns + 1;
for (int i = 0; i < numDataChunks; i++)
{
int rowCount = columns.First().Data.ElementAt(i).Entries.Count;
for (int r = 0; r < rowCount; r++)
{
IRow row = SheetUtils.CreateRow(sheet);
if (i == 0 && r == 0)
{
_firstDataRow = row.RowNum;
}
for (int c = 0; c < numColumns; c++)
{
ICell cell = row.CreateCell(c);
cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(sheet.Workbook);
string entry = columns.ElementAt(c).Data.ElementAt(i).Entries.ElementAt(r);
bool isNumber = double.TryParse(entry, NumberStyles.Any, CultureInfo.InvariantCulture,
out double result);
if (isNumber)
{
int decimalPts = columns.ElementAt(c).GetNumDecimalPlaces();
switch (decimalPts)
{
case -1:
cell.SetCellValue(entry);
break;
case 0:
cell.CellStyle =
CellStyleFactory.CreateCenterAlignmentStyle0DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
case 1:
cell.CellStyle =
CellStyleFactory.CreateCenterAlignmentStyle1DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
default:
cell.CellStyle =
CellStyleFactory.CreateCenterAlignmentStyle2DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
}
}
else
{
cell.SetCellValue(entry);
}
}
for (int notesIndex = 0; notesIndex < 2; notesIndex++)
{
ICell notesCell = row.CreateCell(numColumns + notesIndex);
// if ()
notesCell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(sheet.Workbook);
}
CellRangeAddress region =
new CellRangeAddress(sheet.LastRowNum, sheet.LastRowNum, numColumns - 1, numColumns + 1);
sheet.AddMergedRegion(region);
}
if (i == numDataChunks - 1)
{
continue;
}
IRow blankRow = SheetUtils.CreateRow(sheet);
for (int c = 0; c < numColumns + 2; c++)
{
ICell cell = blankRow.CreateCell(c);
cell.CellStyle = CellStyleFactory.CreateFinalSummaryStyle(sheet.Workbook);
}
CellRangeAddress blankRegion =
new CellRangeAddress(sheet.LastRowNum, sheet.LastRowNum, numColumns - 1, numColumns + 1);
sheet.AddMergedRegion(blankRegion);
}
_lastDataRow = sheet.LastRowNum;
BorderDrawer.drawBorders(sheet, _firstDataRow, _lastDataRow, _lastDataColumn);
}
public void WriteData(AbstractList list, ISheet sheet)
{
ICollection <StringColumn> columns = list.Columns;
int numDataChunks = columns.First().Data.Count;
int numColumns = columns.Count;
_lastDataColumn = numColumns - 1;
for (int chunkIndex = 0; chunkIndex < numDataChunks; chunkIndex++)
{
int numEntries = columns.First().Data.ElementAt(chunkIndex).Entries.Count;
for (int entryIndex = 0; entryIndex < numEntries; entryIndex++)
{
IRow row = SheetUtils.CreateRow(sheet);
if (chunkIndex == 0 && entryIndex == 0)
{
_firstDataRow = row.RowNum;
}
string assemblyEntry = columns.First().Data.ElementAt(chunkIndex).Entries.ElementAt(entryIndex);
bool isFirstRow = !string.IsNullOrEmpty(assemblyEntry);
for (int columnIndex = 0; columnIndex < numColumns; columnIndex++)
{
ICell cell = row.CreateCell(columnIndex);
cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(sheet.Workbook);
string entry = columns.ElementAt(columnIndex).Data.ElementAt(chunkIndex).Entries
.ElementAt(entryIndex);
bool isNumber = double.TryParse(entry, NumberStyles.Any, CultureInfo.InvariantCulture,
out double result);
if (isNumber)
{
int decimalPts = columns.ElementAt(columnIndex).GetNumDecimalPlaces();
switch (decimalPts)
{
case -1:
cell.SetCellValue(entry);
break;
case 0:
cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle0DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
case 1:
cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle1DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
default:
cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle2DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
}
}
else
{
cell.SetCellValue(entry.Trim());
}
if (isFirstRow)
{
if (isNumber)
{
int decimalPts = columns.ElementAt(columnIndex).GetNumDecimalPlaces();
switch (decimalPts)
{
case -1:
cell.SetCellValue(entry);
cell.CellStyle = CellStyleFactory.CreateSummaryStyle(sheet.Workbook);
break;
case 0:
cell.CellStyle = CellStyleFactory.CreateSummaryStyle0DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
case 1:
cell.CellStyle = CellStyleFactory.CreateSummaryStyle1DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
default:
cell.CellStyle = CellStyleFactory.CreateSummaryStyle2DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
}
}
else
{
cell.CellStyle = CellStyleFactory.CreateSummaryStyle(sheet.Workbook);
}
}
}
}
}
IRow summaryRow = SheetUtils.CreateRow(sheet);
int emptySummaryCount = 0;
string weightColumnName = TranslateUtils.Translate("Weight");
for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
{
ICell cell = summaryRow.CreateCell(columnIndex);
cell.CellStyle = CellStyleFactory.CreateFinalSummaryStyle(sheet.Workbook);
if (columns.ElementAt(columnIndex).Name.Contains(weightColumnName))
{
int decimalPts = columns.ElementAt(columnIndex).GetNumDecimalPlaces();
switch (decimalPts)
{
case -1:
cell.SetCellValue(((StructuralList)list).WeightSummary);
break;
case 0:
cell.CellStyle =
CellStyleFactory.CreateFinalSummaryStyle0DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(((StructuralList)list).WeightSummary, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
case 1:
cell.CellStyle =
CellStyleFactory.CreateFinalSummaryStyle1DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(((StructuralList)list).WeightSummary, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
default:
cell.CellStyle =
CellStyleFactory.CreateFinalSummaryStyle2DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(((StructuralList)list).WeightSummary, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
}
// cell.SetCellValue(SheetUtils.SeparateThousands(((StructuralList) list).WeightSummary, false));
}
else
{
cell.SetCellValue("Suma całkowita");
emptySummaryCount++;
}
}
CellRangeAddress region =
new CellRangeAddress(sheet.LastRowNum, sheet.LastRowNum, 0, emptySummaryCount - 1);
sheet.AddMergedRegion(region);
_lastDataRow = sheet.LastRowNum;
BorderDrawer.drawBorders(sheet, _firstDataRow, _lastDataRow, _lastDataColumn);
}
public void WriteData(AbstractList list, ISheet sheet)
{
ICollection <StringColumn> columns = list.Columns;
int numDataChunks = columns.First().Data.Count;
int numColumns = columns.Count;
_lastDataColumn = numColumns - 1;
for (int i = 0; i < numDataChunks - 1; i++)
{
int rowCount = columns.First().Data.ElementAt(i).Entries.Count;
for (int r = 0; r < rowCount; r++)
{
IRow row = SheetUtils.CreateRow(sheet);
if (i == 0 && r == 0)
{
_firstDataRow = row.RowNum;
}
for (int c = 0; c < numColumns; c++)
{
ICell cell = row.CreateCell(c);
cell.CellStyle = CellStyleFactory.CreateCenterAlignmentStyle(sheet.Workbook);
string entry = columns.ElementAt(c).Data.ElementAt(i).Entries.ElementAt(r);
bool isNumber = double.TryParse(entry, NumberStyles.Any, CultureInfo.InvariantCulture,
out double result);
if (isNumber)
{
int decimalPts = columns.ElementAt(c).GetNumDecimalPlaces();
switch (decimalPts)
{
case -1:
cell.SetCellValue(entry);
break;
case 0:
cell.CellStyle =
CellStyleFactory.CreateCenterAlignmentStyle0DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
case 1:
cell.CellStyle =
CellStyleFactory.CreateCenterAlignmentStyle1DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
default:
cell.CellStyle =
CellStyleFactory.CreateCenterAlignmentStyle2DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(entry, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
}
}
else
{
cell.SetCellValue(entry);
}
}
}
IRow summaryRow = SheetUtils.CreateRow(sheet);
int emptySummaryCount = 0;
bool isFinal = i == numDataChunks - 2;
ICellStyle summaryStyle = isFinal
? CellStyleFactory.CreateFinalSummaryStyle(sheet.Workbook)
: CellStyleFactory.CreateSummaryStyle(sheet.Workbook);
string sumLabel = isFinal ? "Suma całkowita" : "Suma";
for (int c = 0; c < numColumns; c++)
{
string summary = columns.ElementAt(c).Data.ElementAt(i).Summary;
ICell cell = summaryRow.CreateCell(c, CellType.Numeric);
cell.CellStyle = summaryStyle;
if (string.IsNullOrEmpty(summary) || summary.Contains("Tota") || summary.Contains("for"))
{
emptySummaryCount++;
cell.SetCellValue(sumLabel);
}
else
{
string columnName = columns.ElementAt(c).Name;
bool isNumber = double.TryParse(summary, NumberStyles.Any, CultureInfo.InvariantCulture,
out double result);
if (isNumber)
{
int decimalPts = columns.ElementAt(c).GetNumDecimalPlaces();
switch (decimalPts)
{
case -1:
cell.SetCellValue(summary);
break;
case 0:
cell.CellStyle = isFinal
? CellStyleFactory.CreateFinalSummaryStyle0DecimalPts(sheet.Workbook)
: CellStyleFactory.CreateSummaryStyle0DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(summary, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
case 1:
cell.CellStyle = isFinal
? CellStyleFactory.CreateFinalSummaryStyle1DecimalPts(sheet.Workbook)
: CellStyleFactory.CreateSummaryStyle1DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(summary, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
default:
cell.CellStyle = isFinal
? CellStyleFactory.CreateFinalSummaryStyle2DecimalPts(sheet.Workbook)
: CellStyleFactory.CreateSummaryStyle2DecimalPts(sheet.Workbook);
cell.SetCellValue(double.Parse(summary, NumberStyles.Any, CultureInfo.InvariantCulture));
break;
}
// cell.SetCellValue(SheetUtils.SeparateThousands(summary,
// !intColumns.Any(column => columnName.Contains(TranslateUtils.Translate(column)))));
}
else
{
emptySummaryCount++;
}
}
if (!string.IsNullOrEmpty(summary) && summary.Contains("for") && summary.Length == 3)
{
emptySummaryCount++;
}
}
CellRangeAddress region =
new CellRangeAddress(sheet.LastRowNum, sheet.LastRowNum, 0, emptySummaryCount - 1);
sheet.AddMergedRegion(region);
}
_lastDataRow = sheet.LastRowNum;
BorderDrawer.drawBorders(sheet, _firstDataRow, _lastDataRow, _lastDataColumn);
}
