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