public void TestCleanColumns()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_Cols cols1 = worksheet.AddNewCols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Cols cols2 = worksheet.AddNewCols();
CT_Col col4 = cols2.AddNewCol();
col4.min = (13);
col4.max = (16384);
// Test cleaning cols
Assert.AreEqual(2, worksheet.sizeOfColsArray());
int count = countColumns(worksheet);
Assert.AreEqual(16375, count);
// Clean columns and Test a clean worksheet
ColumnHelper helper = new ColumnHelper(worksheet);
Assert.AreEqual(1, worksheet.sizeOfColsArray());
count = countColumns(worksheet);
Assert.AreEqual(16375, count);
// Remember - POI column 0 == OOXML column 1
Assert.AreEqual(88.0, helper.GetColumn(0, false).width, 0.0);
Assert.IsTrue(helper.GetColumn(0, false).hidden);
Assert.AreEqual(0.0, helper.GetColumn(1, false).width, 0.0);
Assert.IsFalse(helper.GetColumn(1, false).hidden);
}
public void TestColumn()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_Cols cols1 = worksheet.AddNewCols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Cols cols2 = worksheet.AddNewCols();
CT_Col col4 = cols2.AddNewCol();
col4.min = (3);
col4.max = (6);
// Remember - POI column 0 == OOXML column 1
ColumnHelper helper = new ColumnHelper(worksheet);
Assert.IsNotNull(helper.GetColumn(0, false));
Assert.IsNotNull(helper.GetColumn(1, false));
Assert.AreEqual(88.0, helper.GetColumn(0, false).width, 0.0);
Assert.AreEqual(0.0, helper.GetColumn(1, false).width, 0.0);
Assert.IsTrue(helper.GetColumn(0, false).hidden);
Assert.IsFalse(helper.GetColumn(1, false).hidden);
Assert.IsNull(helper.GetColumn(99, false));
Assert.IsNotNull(helper.GetColumn(5, false));
}
public void TestAddCleanColIntoCols()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols1 = new CT_Cols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Col col3 = cols1.AddNewCol();
col3.min = (13);
col3.max = (16750);
Assert.AreEqual(3, cols1.sizeOfColArray());
CT_Col col4 = cols1.AddNewCol();
col4.min = (8);
col4.max = (9);
Assert.AreEqual(4, cols1.sizeOfColArray());
CT_Col col5 = new CT_Col();
col5.min = (4);
col5.max = (5);
helper.AddCleanColIntoCols(cols1, col5);
Assert.AreEqual(5, cols1.sizeOfColArray());
CT_Col col6 = new CT_Col();
col6.min = (8);
col6.max = (11);
col6.hidden = (true);
helper.AddCleanColIntoCols(cols1, col6);
Assert.AreEqual(6, cols1.sizeOfColArray());
CT_Col col7 = new CT_Col();
col7.min = (6);
col7.max = (8);
col7.width = (17.0);
helper.AddCleanColIntoCols(cols1, col7);
Assert.AreEqual(8, cols1.sizeOfColArray());
CT_Col col8 = new CT_Col();
col8.min = (20);
col8.max = (30);
helper.AddCleanColIntoCols(cols1, col8);
Assert.AreEqual(10, cols1.sizeOfColArray());
CT_Col col9 = new CT_Col();
col9.min = (25);
col9.max = (27);
helper.AddCleanColIntoCols(cols1, col9);
// TODO - assert something interesting
Assert.AreEqual(12, cols1.col.Count);
Assert.AreEqual(1u, cols1.GetColArray(0).min);
Assert.AreEqual(16750u, cols1.GetColArray(11).max);
}
public void TestSortColumns()
{
//CT_Worksheet worksheet = new CT_Worksheet();
//ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols1 = new CT_Cols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Col col3 = cols1.AddNewCol();
col3.min = (13);
col3.max = (16750);
Assert.AreEqual(3, cols1.sizeOfColArray());
CT_Col col4 = cols1.AddNewCol();
col4.min = (8);
col4.max = (11);
Assert.AreEqual(4, cols1.sizeOfColArray());
CT_Col col5 = cols1.AddNewCol();
col5.min = (4);
col5.max = (5);
Assert.AreEqual(5, cols1.sizeOfColArray());
CT_Col col6 = cols1.AddNewCol();
col6.min = (8);
col6.max = (9);
col6.hidden = (true);
CT_Col col7 = cols1.AddNewCol();
col7.min = (6);
col7.max = (8);
col7.width = (17.0);
CT_Col col8 = cols1.AddNewCol();
col8.min = (25);
col8.max = (27);
CT_Col col9 = cols1.AddNewCol();
col9.min = (20);
col9.max = (30);
Assert.AreEqual(9, cols1.sizeOfColArray());
Assert.AreEqual(20u, cols1.GetColArray(8).min);
Assert.AreEqual(30u, cols1.GetColArray(8).max);
ColumnHelper.SortColumns(cols1);
Assert.AreEqual(9, cols1.sizeOfColArray());
Assert.AreEqual(25u, cols1.GetColArray(8).min);
Assert.AreEqual(27u, cols1.GetColArray(8).max);
}
public void TestAddCleanColIntoCols()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols1 = new CT_Cols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Col col3 = cols1.AddNewCol();
col3.min = (13);
col3.max = (16750);
Assert.AreEqual(3, cols1.sizeOfColArray());
CT_Col col4 = cols1.AddNewCol();
col4.min = (8);
col4.max = (9);
Assert.AreEqual(4, cols1.sizeOfColArray());
// No overlap
helper.addCleanColIntoCols(cols1, createCol(4, 5));
Assert.AreEqual(5, cols1.sizeOfColArray());
// Overlaps with 8 - 9 (overlap and after replacements required)
CT_Col col6 = createCol(8, 11);
col6.hidden = (true);
helper.AddCleanColIntoCols(cols1, col6);
Assert.AreEqual(6, cols1.sizeOfColArray());
// Overlaps with 8 - 9 (before and overlap replacements required)
CT_Col col7 = createCol(6, 8);
col7.width = (17.0);
helper.AddCleanColIntoCols(cols1, col7);
Assert.AreEqual(8, cols1.sizeOfColArray());
// Overlaps with 13 - 16750 (before, overlap and after replacements required)
helper.addCleanColIntoCols(cols1, createCol(20, 30));
Assert.AreEqual(10, cols1.sizeOfColArray());
// Overlaps with 20 - 30 (before, overlap and after replacements required)
helper.addCleanColIntoCols(cols1, createCol(25, 27));
// TODO - assert something interesting
Assert.AreEqual(12, cols1.col.Count);
Assert.AreEqual(1u, cols1.GetColArray(0).min);
Assert.AreEqual(16750u, cols1.GetColArray(11).max);
}
/**
* Creates and adds a hidden column and then a best fit column with the given min/max pairs.
* Suitable for testing handling of overlap.
*/
private CT_Cols createHiddenAndBestFitColsWithHelper(int hiddenMin, int hiddenMax, int bestFitMin, int bestFitMax)
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols = worksheet.GetColsArray(0);
CT_Col hidden = createCol(hiddenMin, hiddenMax);
hidden.hidden = (true);
helper.addCleanColIntoCols(cols, hidden);
CT_Col bestFit = createCol(bestFitMin, bestFitMax);
bestFit.bestFit = (true);
helper.addCleanColIntoCols(cols, bestFit);
return(cols);
}
public void TestSetColumnAttributes()
{
CT_Col col = new CT_Col();
col.width = (12);
col.hidden = (true);
CT_Col newCol = new CT_Col();
Assert.AreEqual(0.0, newCol.width, 0.0);
Assert.IsFalse(newCol.hidden);
ColumnHelper helper = new ColumnHelper(new CT_Worksheet());
helper.SetColumnAttributes(col, newCol);
Assert.AreEqual(12.0, newCol.width, 0.0);
Assert.IsTrue(newCol.hidden);
}
public void TestCloneCol()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols = new CT_Cols();
CT_Col col = new CT_Col();
col.min = (2);
col.max = (8);
col.hidden = (true);
col.width = (13.4);
CT_Col newCol = helper.CloneCol(cols, col);
Assert.AreEqual(2u, newCol.min);
Assert.AreEqual(8u, newCol.max);
Assert.IsTrue(newCol.hidden);
Assert.AreEqual(13.4, newCol.width, 0.0);
}
public void TestCleanColumns()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_Cols cols1 = worksheet.AddNewCols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Cols cols2 = worksheet.AddNewCols();
CT_Col col4 = cols2.AddNewCol();
col4.min = (13);
col4.max = (16384);
// Test cleaning cols
Assert.AreEqual(2, worksheet.sizeOfColsArray());
int count = countColumns(worksheet);
Assert.AreEqual(16375, count);
// Clean columns and Test a clean worksheet
ColumnHelper helper = new ColumnHelper(worksheet);
Assert.AreEqual(1, worksheet.sizeOfColsArray());
count = countColumns(worksheet);
Assert.AreEqual(16375, count);
// Remember - POI column 0 == OOXML column 1
Assert.AreEqual(88.0, helper.GetColumn(0, false).width, 0.0);
Assert.IsTrue(helper.GetColumn(0, false).hidden);
Assert.AreEqual(0.0, helper.GetColumn(1, false).width, 0.0);
Assert.IsFalse(helper.GetColumn(1, false).hidden);
}
public void TestGetOrCreateColumn()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet 1");
ColumnHelper columnHelper = sheet.GetColumnHelper();
// Check POI 0 based, OOXML 1 based
CT_Col col = columnHelper.GetOrCreateColumn1Based(3, false);
Assert.IsNotNull(col);
Assert.IsNull(columnHelper.GetColumn(1, false));
Assert.IsNotNull(columnHelper.GetColumn(2, false));
Assert.IsNotNull(columnHelper.GetColumn1Based(3, false));
Assert.IsNull(columnHelper.GetColumn(3, false));
CT_Col col2 = columnHelper.GetOrCreateColumn1Based(30, false);
Assert.IsNotNull(col2);
Assert.IsNull(columnHelper.GetColumn(28, false));
Assert.IsNotNull(columnHelper.GetColumn(29, false));
Assert.IsNotNull(columnHelper.GetColumn1Based(30, false));
Assert.IsNull(columnHelper.GetColumn(30, false));
}
public void TestGetSetColDefaultStyle()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
ColumnHelper columnHelper = sheet.GetColumnHelper();
// POI column 3, OOXML column 4
CT_Col col = columnHelper.GetOrCreateColumn1Based(4, false);
Assert.IsNotNull(col);
Assert.IsNotNull(columnHelper.GetColumn(3, false));
columnHelper.SetColDefaultStyle(3, 2);
Assert.AreEqual(2, columnHelper.GetColDefaultStyle(3));
Assert.AreEqual(-1, columnHelper.GetColDefaultStyle(4));
StylesTable stylesTable = workbook.GetStylesSource();
CT_Xf cellXf = new CT_Xf();
cellXf.fontId = (0);
cellXf.fillId = (0);
cellXf.borderId = (0);
cellXf.numFmtId = (0);
cellXf.xfId = (0);
stylesTable.PutCellXf(cellXf);
CT_Col col_2 = ctWorksheet.GetColsArray(0).AddNewCol();
col_2.min = (10);
col_2.max = (12);
col_2.style = (1);
col_2.styleSpecified = true;
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.SetColDefaultStyle(11, cellStyle);
Assert.AreEqual(0u, col_2.style);
Assert.AreEqual(1, columnHelper.GetColDefaultStyle(10));
}
/**
* Initialize worksheet data when creating a new sheet.
*/
internal override void OnDocumentCreate()
{
worksheet = NewSheet();
InitRows(worksheet);
columnHelper = new ColumnHelper(worksheet);
hyperlinks = new List<XSSFHyperlink>();
}
public CT_Cols AddCleanColIntoCols(CT_Cols cols, CT_Col col)
{
bool flag = false;
for (int index = 0; index < cols.sizeOfColArray(); ++index)
{
CT_Col colArray = cols.GetColArray(index);
long[] range1 = new long[2] {
(long)colArray.min, (long)colArray.max
};
long[] range2 = new long[2] {
(long)col.min, (long)col.max
};
long[] overlappingRange = NumericRanges.GetOverlappingRange(range1, range2);
int overlappingType = NumericRanges.GetOverlappingType(range1, range2);
if (overlappingType == 0)
{
colArray.max = (uint)((ulong)overlappingRange[0] - 1UL);
this.insertCol(cols, overlappingRange[0], overlappingRange[1], new CT_Col[2]
{
colArray,
col
});
int num = index + 1;
this.insertCol(cols, overlappingRange[1] + 1L, (long)col.max, new CT_Col[1]
{
col
});
index = num + 1;
}
else if (overlappingType == 1)
{
colArray.min = (uint)((ulong)overlappingRange[1] + 1UL);
this.insertCol(cols, overlappingRange[0], overlappingRange[1], new CT_Col[2]
{
colArray,
col
});
int num = index + 1;
this.insertCol(cols, (long)col.min, overlappingRange[0] - 1L, new CT_Col[1]
{
col
});
index = num + 1;
}
else if (overlappingType == 3)
{
this.SetColumnAttributes(col, colArray);
if ((int)col.min != (int)colArray.min)
{
this.insertCol(cols, (long)col.min, (long)(colArray.min - 1U), new CT_Col[1]
{
col
});
++index;
}
if ((int)col.max != (int)colArray.max)
{
this.insertCol(cols, (long)(colArray.max + 1U), (long)col.max, new CT_Col[1]
{
col
});
++index;
}
}
else if (overlappingType == 2)
{
if ((int)col.min != (int)colArray.min)
{
this.insertCol(cols, (long)colArray.min, (long)(col.min - 1U), new CT_Col[1]
{
colArray
});
++index;
}
if ((int)col.max != (int)colArray.max)
{
this.insertCol(cols, (long)(col.max + 1U), (long)colArray.max, new CT_Col[1]
{
colArray
});
++index;
}
colArray.min = (uint)overlappingRange[0];
colArray.max = (uint)overlappingRange[1];
this.SetColumnAttributes(col, colArray);
}
if (overlappingType != -1)
{
flag = true;
}
}
if (!flag)
{
this.CloneCol(cols, col);
}
ColumnHelper.SortColumns(cols);
return(cols);
}
public void TestAddCleanColIntoCols()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols1 = new CT_Cols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Col col3 = cols1.AddNewCol();
col3.min = (13);
col3.max = (16750);
Assert.AreEqual(3, cols1.sizeOfColArray());
CT_Col col4 = cols1.AddNewCol();
col4.min = (8);
col4.max = (9);
Assert.AreEqual(4, cols1.sizeOfColArray());
CT_Col col5 = new CT_Col();
col5.min = (4);
col5.max = (5);
helper.AddCleanColIntoCols(cols1, col5);
Assert.AreEqual(5, cols1.sizeOfColArray());
CT_Col col6 = new CT_Col();
col6.min = (8);
col6.max = (11);
col6.hidden = (true);
helper.AddCleanColIntoCols(cols1, col6);
Assert.AreEqual(6, cols1.sizeOfColArray());
CT_Col col7 = new CT_Col();
col7.min = (6);
col7.max = (8);
col7.width = (17.0);
helper.AddCleanColIntoCols(cols1, col7);
Assert.AreEqual(8, cols1.sizeOfColArray());
CT_Col col8 = new CT_Col();
col8.min = (20);
col8.max = (30);
helper.AddCleanColIntoCols(cols1, col8);
Assert.AreEqual(10, cols1.sizeOfColArray());
CT_Col col9 = new CT_Col();
col9.min = (25);
col9.max = (27);
helper.AddCleanColIntoCols(cols1, col9);
// TODO - assert something interesting
Assert.AreEqual(12, cols1.col.Count);
Assert.AreEqual(1u, cols1.GetColArray(0).min);
Assert.AreEqual(16750u, cols1.GetColArray(11).max);
}
internal virtual void Read(Stream is1)
{
//try
//{
worksheet = WorksheetDocument.Parse(is1).GetWorksheet();
//}
//catch (XmlException e)
//{
// throw new POIXMLException(e);
//}
InitRows(worksheet);
columnHelper = new ColumnHelper(worksheet);
// Look for bits we're interested in
foreach (POIXMLDocumentPart p in GetRelations())
{
if (p is CommentsTable)
{
sheetComments = (CommentsTable)p;
break;
}
if (p is XSSFTable)
{
tables[p.GetPackageRelationship().Id] = (XSSFTable)p;
}
}
// Process external hyperlinks for the sheet, if there are any
InitHyperlinks();
}
public void TestCloneCol()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols = new CT_Cols();
CT_Col col = new CT_Col();
col.min = (2);
col.max = (8);
col.hidden = (true);
col.width = (13.4);
CT_Col newCol = helper.CloneCol(cols, col);
Assert.AreEqual(2u, newCol.min);
Assert.AreEqual(8u, newCol.max);
Assert.IsTrue(newCol.hidden);
Assert.AreEqual(13.4, newCol.width, 0.0);
}
public void TestSetColumnAttributes()
{
CT_Col col = new CT_Col();
col.width = (12);
col.hidden = (true);
CT_Col newCol = new CT_Col();
Assert.AreEqual(0.0, newCol.width, 0.0);
Assert.IsFalse(newCol.hidden);
ColumnHelper helper = new ColumnHelper(new CT_Worksheet());
helper.SetColumnAttributes(col, newCol);
Assert.AreEqual(12.0, newCol.width, 0.0);
Assert.IsTrue(newCol.hidden);
}
public void TestColumn()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_Cols cols1 = worksheet.AddNewCols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Cols cols2 = worksheet.AddNewCols();
CT_Col col4 = cols2.AddNewCol();
col4.min = (3);
col4.max = (6);
// Remember - POI column 0 == OOXML column 1
ColumnHelper helper = new ColumnHelper(worksheet);
Assert.IsNotNull(helper.GetColumn(0, false));
Assert.IsNotNull(helper.GetColumn(1, false));
Assert.AreEqual(88.0, helper.GetColumn(0, false).width, 0.0);
Assert.AreEqual(0.0, helper.GetColumn(1, false).width, 0.0);
Assert.IsTrue(helper.GetColumn(0, false).hidden);
Assert.IsFalse(helper.GetColumn(1, false).hidden);
Assert.IsNull(helper.GetColumn(99, false));
Assert.IsNotNull(helper.GetColumn(5, false));
}