public void Test_WindowSizeEqualsOne_flushedRowHasMergedCell() { workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory sheet = workbook.CreateSheet() as SXSSFSheet; sheet.TrackAllColumnsForAutoSizing(); ICell a1 = CreateRowWithCellValues(sheet, 0, LONG_CELL_VALUE); assumeRequiredFontsAreInstalled(workbook, a1); sheet.AddMergedRegion(CellRangeAddress.ValueOf("A1:B1")); CreateRowWithCellValues(sheet, 1, SHORT_CELL_VALUE, SHORT_CELL_VALUE); /** * A B * 1 LONGMERGED * 2 SHORT SHORT */ sheet.AutoSizeColumn(0, useMergedCells); sheet.AutoSizeColumn(1, useMergedCells); if (useMergedCells) { // Excel and LibreOffice behavior: ignore merged cells for auto-sizing. // POI behavior: evenly distribute the column width among the merged columns. // each column must be auto-sized in order for the column widths // to add up to the best fit width. int colspan = 2; int expectedWidth = (10000 + 1000) / colspan; //average of 1_000 and 10_000 int minExpectedWidth = expectedWidth / 2; int maxExpectedWidth = expectedWidth * 3 / 2; assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), minExpectedWidth, maxExpectedWidth); //short } else { assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH); //long } assertColumnWidthStrictlyWithinRange(sheet.GetColumnWidth(1), 0, COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG); //short }
public void updateColumnWidths_and_getBestFitColumnWidth() { tracker.TrackAllColumns(); IRow row1 = sheet.CreateRow(0); IRow row2 = sheet.CreateRow(1); // A1, B1, D1 foreach (int column in columns) { row1.CreateCell(column).SetCellValue(LONG_MESSAGE); row2.CreateCell(column + 1).SetCellValue(SHORT_MESSAGE); } tracker.UpdateColumnWidths(row1); tracker.UpdateColumnWidths(row2); sheet.AddMergedRegion(CellRangeAddress.ValueOf("D1:E1")); assumeRequiredFontsAreInstalled(workbook, row1.GetCell(columns.GetEnumerator().Current)); // Excel 2013 and LibreOffice 4.2.8.2 both treat columns with merged regions as blank /** A B C D E * 1 LONG LONG LONGMERGE * 2 SHORT SHORT SHORT */ // measured in Excel 2013. Sizes may vary. int longMsgWidth = (int)(57.43 * 256); int shortMsgWidth = (int)(4.86 * 256); CheckColumnWidth(longMsgWidth, 0, true); CheckColumnWidth(longMsgWidth, 0, false); CheckColumnWidth(longMsgWidth, 1, true); CheckColumnWidth(longMsgWidth, 1, false); CheckColumnWidth(shortMsgWidth, 2, true); CheckColumnWidth(shortMsgWidth, 2, false); CheckColumnWidth(-1, 3, true); CheckColumnWidth(longMsgWidth, 3, false); CheckColumnWidth(shortMsgWidth, 4, true); //but is it really? shouldn't autosizing column E use "" from E1 and SHORT from E2? CheckColumnWidth(shortMsgWidth, 4, false); }
void SetCell(IWorkbook workbook, SXSSFSheet sheet, int r, int tor, int c, int toc, dynamic value, short fontSize = 11, bool isBorder = false, BorderStyle borderStyle = BorderStyle.Medium, bool isCenter = false, short?rowHeight = null, short?color = null) { var rang = new CellRangeAddress(r, tor, c, toc); sheet.AddMergedRegion(rang); var row = sheet._rows[r]; if (rowHeight.HasValue) { row.Height = rowHeight.Value; } var cell = row.Cells[c]; if (value != null) { cell.SetCellValue(value); } //设置边框 if (isBorder) { // if (r == tor && c == toc) { //var brow = sheet._rows[r]; //var sigleCell = brow.Cells[c]; //var sigleStyle = workbook.CreateCellStyle(); //sigleStyle.BorderLeft = borderStyle; //sigleCell.CellStyle = sigleStyle; } // else { Parallel.For(r, tor + 1, firstR => { var brow = sheet._rows[firstR]; var leftCell = brow.Cells[c]; var leftStyle = workbook.CreateCellStyle(); leftStyle.BorderLeft = borderStyle; leftCell.CellStyle = leftStyle; var rightCell = brow.Cells[toc]; var rightStyle = workbook.CreateCellStyle(); rightStyle.BorderRight = borderStyle; rightCell.CellStyle = rightStyle; }); //for (var firstR = r; firstR <= tor; firstR++) //{ // var brow = sheet._rows[firstR]; // var leftCell = brow.Cells[c]; // var leftStyle = workbook.CreateCellStyle(); // leftStyle.BorderLeft = borderStyle; // leftCell.CellStyle = leftStyle; // var rightCell = brow.Cells[toc]; // var rightStyle = workbook.CreateCellStyle(); // rightStyle.BorderRight = borderStyle; // rightCell.CellStyle = rightStyle; //} Parallel.For(c, toc + 1, firstC => { var topRow = sheet._rows[r]; var topCell = topRow.Cells[firstC]; var topStyle = workbook.CreateCellStyle(); topStyle.BorderTop = borderStyle; if (firstC == c) { topStyle.BorderLeft = borderStyle; } if (firstC == toc) { topStyle.BorderRight = borderStyle; } topCell.CellStyle = topStyle; var bottomRow = sheet._rows[tor]; var bottomCell = bottomRow.Cells[firstC]; var bottomStyle = workbook.CreateCellStyle(); bottomStyle.BorderBottom = borderStyle; if (firstC == c) { bottomStyle.BorderLeft = borderStyle; } if (firstC == toc) { bottomStyle.BorderRight = borderStyle; } bottomCell.CellStyle = bottomStyle; }); // for (var firstC = c; firstC <= toc; firstC++) //{ // var topRow = sheet._rows[r]; // var topCell = topRow.Cells[firstC]; // var topStyle = workbook.CreateCellStyle(); // topStyle.BorderTop = borderStyle; // if (firstC == c) // { // topStyle.BorderLeft = borderStyle; // } // if (firstC == toc) // { // topStyle.BorderRight = borderStyle; // } // topCell.CellStyle = topStyle; // var bottomRow = sheet._rows[tor]; // var bottomCell = bottomRow.Cells[firstC]; // var bottomStyle = workbook.CreateCellStyle(); // bottomStyle.BorderBottom = borderStyle; // if (firstC == c) // { // bottomStyle.BorderLeft = borderStyle; // } // if (firstC == toc) // { // bottomStyle.BorderRight = borderStyle; // } // bottomCell.CellStyle = bottomStyle; //} } } var style = isBorder ? cell.CellStyle : workbook.CreateCellStyle(); //设置颜色 if (color.HasValue) { style.FillForegroundColor = color.Value; style.FillPattern = FillPattern.SolidForeground; } //设置字体 var font = workbook.CreateFont(); font.FontHeightInPoints = fontSize; style.SetFont(font); //设置内容居中 if (isCenter) { style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Center; } cell.CellStyle = style; }
void SetCell(IWorkbook workbook, SXSSFSheet sheet, int r, int tor, int c, int toc, dynamic value, short fontSize = 11, bool isBorder = false, BorderStyle borderStyle = BorderStyle.Medium, bool isCenter = false, short?rowHeight = null, short?color = null) { var rang = new CellRangeAddress(r, tor, c, toc); sheet.AddMergedRegion(rang); var fff = sheet.GetType().GetField("_rows").GetValue(sheet); var row = (fff as SortedDictionary <int, SXSSFRow>).ContainsKey(r) ? sheet.GetRow(r) : sheet.CreateRow(r);// sheet.GetRow(r) == null ? sheet.CreateRow(r) : sheet.GetRow(r); if (rowHeight.HasValue) { row.Height = rowHeight.Value; } var cell = row.CreateCell(c); if (value != null) { cell.SetCellValue(value); } var style = workbook.CreateCellStyle(); //设置颜色 if (color.HasValue) { style.FillForegroundColor = color.Value; style.FillPattern = FillPattern.SolidForeground; } //设置字体 var font = workbook.CreateFont(); font.FontHeightInPoints = fontSize; style.SetFont(font); //设置边框 //if (isBorder) //{ // style.BorderLeft = borderStyle; // style.BorderRight = borderStyle; // style.BorderTop = borderStyle; // style.BorderBottom = borderStyle; // for (int i = rang.FirstRow; i <= rang.LastRow; i++) // { // var borderRow = CellUtil.GetRow(i, sheet); // for (int j = rang.FirstColumn; j <= rang.LastColumn; j++) // { // var singleCell = CellUtil.GetCell(borderRow, (short)j); // singleCell.CellStyle = style; // } // } //} //else //{ // cell.CellStyle = style; //} cell.CellStyle = style; //设置内容居中 if (isCenter) { style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Center; } }