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