public void TestAutoSizeColumn()
{
HSSFWorkbook wb = OpenSample("43902.xls");
String sheetName = "my sheet";
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
// Can't use literal numbers for column sizes, as
// will come out with different values on different
// machines based on the fonts available.
// So, we use ranges, which are pretty large, but
// thankfully don't overlap!
int minWithRow1And2 = 6400;
int maxWithRow1And2 = 7800;
int minWithRow1Only = 2750;
int maxWithRow1Only = 3300;
// autoSize the first column and check its size before the merged region (1,0,1,1) is set:
// it has to be based on the 2nd row width
sheet.AutoSizeColumn(0);
Assert.IsTrue(sheet.GetColumnWidth(0) >= minWithRow1And2, "Column autosized with only one row: wrong width");
Assert.IsTrue(sheet.GetColumnWidth(0) <= maxWithRow1And2, "Column autosized with only one row: wrong width");
//Create a region over the 2nd row and auto size the first column
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));
sheet.AutoSizeColumn(0);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb);
// Check that the autoSized column width has ignored the 2nd row
// because it is included in a merged region (Excel like behavior)
NPOI.SS.UserModel.Sheet sheet2 = wb2.GetSheet(sheetName);
Assert.IsTrue(sheet2.GetColumnWidth(0) >= minWithRow1Only);
Assert.IsTrue(sheet2.GetColumnWidth(0) <= maxWithRow1Only);
// Remove the 2nd row merged region and Check that the 2nd row value is used to the AutoSizeColumn width
sheet2.RemoveMergedRegion(1);
sheet2.AutoSizeColumn(0);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
NPOI.SS.UserModel.Sheet sheet3 = wb3.GetSheet(sheetName);
Assert.IsTrue(sheet3.GetColumnWidth(0) >= minWithRow1And2);
Assert.IsTrue(sheet3.GetColumnWidth(0) <= maxWithRow1And2);
}
public void TestColumnWidth()
{
//Check we can correctly read column widths from a reference workbook
HSSFWorkbook wb = OpenSample("colwidth.xls");
//reference values
int[] ref1 = { 365, 548, 731, 914, 1097, 1280, 1462, 1645, 1828, 2011, 2194, 2377, 2560, 2742, 2925, 3108, 3291, 3474, 3657 };
NPOI.SS.UserModel.Sheet sh = wb.GetSheetAt(0);
for (char i = 'A'; i <= 'S'; i++)
{
int idx = i - 'A';
int w = sh.GetColumnWidth(idx);
Assert.AreEqual(ref1[idx], w);
}
//the second sheet doesn't have overridden column widths
sh = wb.GetSheetAt(1);
int def_width = sh.DefaultColumnWidth;
for (char i = 'A'; i <= 'S'; i++)
{
int idx = i - 'A';
int w = sh.GetColumnWidth(idx);
//getDefaultColumnWidth returns width measured in characters
//getColumnWidth returns width measured in 1/256th units
Assert.AreEqual(def_width * 256, w);
}
//Test new workbook
wb = new HSSFWorkbook();
sh = wb.CreateSheet();
sh.DefaultColumnWidth = (10);
Assert.AreEqual(10, sh.DefaultColumnWidth);
Assert.AreEqual(256 * 10, sh.GetColumnWidth(0));
Assert.AreEqual(256 * 10, sh.GetColumnWidth(1));
Assert.AreEqual(256 * 10, sh.GetColumnWidth(2));
for (char i = 'D'; i <= 'F'; i++)
{
short w = (256 * 12);
sh.SetColumnWidth(i, w);
Assert.AreEqual(w, sh.GetColumnWidth(i));
}
//serialize and read again
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = wb.GetSheetAt(0);
Assert.AreEqual(10, sh.DefaultColumnWidth);
//columns A-C have default width
Assert.AreEqual(256 * 10, sh.GetColumnWidth(0));
Assert.AreEqual(256 * 10, sh.GetColumnWidth(1));
Assert.AreEqual(256 * 10, sh.GetColumnWidth(2));
//columns D-F have custom width
for (char i = 'D'; i <= 'F'; i++)
{
short w = (256 * 12);
Assert.AreEqual(w, sh.GetColumnWidth(i));
}
// Check for 16-bit signed/unsigned error:
sh.SetColumnWidth(0, 40000);
Assert.AreEqual(40000, sh.GetColumnWidth(0));
}