public void testCleanColumns() {
CTWorksheet worksheet = CTWorksheet.Factory.newInstance();
CTCols cols1 = worksheet.addNewCols();
CTCol col1 = cols1.addNewCol();
col1.setMin(1);
col1.setMax(1);
col1.setWidth(88);
col1.setHidden(true);
CTCol col2 = cols1.addNewCol();
col2.setMin(2);
col2.setMax(3);
CTCols cols2 = worksheet.addNewCols();
CTCol col4 = cols2.addNewCol();
col4.setMin(13);
col4.setMax(16384);
// Test cleaning cols
assertEquals(2, worksheet.sizeOfColsArray());
int count = countColumns(worksheet);
assertEquals(16375, count);
// Clean columns and test a clean worksheet
ColumnHelper helper = new ColumnHelper(worksheet);
assertEquals(1, worksheet.sizeOfColsArray());
count = countColumns(worksheet);
assertEquals(16375, count);
// Remember - POI column 0 == OOXML column 1
assertEquals(88.0, helper.getColumn(0, false).getWidth(), 0.0);
assertTrue(helper.getColumn(0, false).getHidden());
assertEquals(0.0, helper.getColumn(1, false).getWidth(), 0.0);
assertFalse(helper.getColumn(1, false).getHidden());
}
public void testGetColumn() {
CTWorksheet worksheet = CTWorksheet.Factory.newInstance();
CTCols cols1 = worksheet.addNewCols();
CTCol col1 = cols1.addNewCol();
col1.setMin(1);
col1.setMax(1);
col1.setWidth(88);
col1.setHidden(true);
CTCol col2 = cols1.addNewCol();
col2.setMin(2);
col2.setMax(3);
CTCols cols2 = worksheet.addNewCols();
CTCol col4 = cols2.addNewCol();
col4.setMin(3);
col4.setMax(6);
// Remember - POI column 0 == OOXML column 1
ColumnHelper helper = new ColumnHelper(worksheet);
assertNotNull(helper.getColumn(0, false));
assertNotNull(helper.getColumn(1, false));
assertEquals(88.0, helper.getColumn(0, false).getWidth(), 0.0);
assertEquals(0.0, helper.getColumn(1, false).getWidth(), 0.0);
assertTrue(helper.getColumn(0, false).getHidden());
assertFalse(helper.getColumn(1, false).getHidden());
assertNull(helper.getColumn(99, false));
assertNotNull(helper.getColumn(5, false));
}
public void testAddCleanColIntoCols() {
CTWorksheet worksheet = CTWorksheet.Factory.newInstance();
ColumnHelper helper = new ColumnHelper(worksheet);
CTCols cols1 = CTCols.Factory.newInstance();
CTCol col1 = cols1.addNewCol();
col1.setMin(1);
col1.setMax(1);
col1.setWidth(88);
col1.setHidden(true);
CTCol col2 = cols1.addNewCol();
col2.setMin(2);
col2.setMax(3);
CTCol col3 = cols1.addNewCol();
col3.setMin(13);
col3.setMax(16750);
assertEquals(3, cols1.sizeOfColArray());
CTCol col4 = cols1.addNewCol();
col4.setMin(8);
col4.setMax(9);
assertEquals(4, cols1.sizeOfColArray());
CTCol col5 = CTCol.Factory.newInstance();
col5.setMin(4);
col5.setMax(5);
helper.addCleanColIntoCols(cols1, col5);
assertEquals(5, cols1.sizeOfColArray());
CTCol col6 = CTCol.Factory.newInstance();
col6.setMin(8);
col6.setMax(11);
col6.setHidden(true);
helper.addCleanColIntoCols(cols1, col6);
assertEquals(6, cols1.sizeOfColArray());
CTCol col7 = CTCol.Factory.newInstance();
col7.setMin(6);
col7.setMax(8);
col7.setWidth(17.0);
helper.addCleanColIntoCols(cols1, col7);
assertEquals(8, cols1.sizeOfColArray());
CTCol col8 = CTCol.Factory.newInstance();
col8.setMin(20);
col8.setMax(30);
helper.addCleanColIntoCols(cols1, col8);
assertEquals(10, cols1.sizeOfColArray());
CTCol col9 = CTCol.Factory.newInstance();
col9.setMin(25);
col9.setMax(27);
helper.addCleanColIntoCols(cols1, col9);
// TODO - assert something interesting
assertEquals(12, cols1.sizeOfColArray());
assertEquals(1, cols1.getColArray(0).getMin());
assertEquals(16750, cols1.getColArray(11).getMax());
}
public void testGetSetColDefaultStyle() {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
CTWorksheet ctWorksheet = sheet.getCTWorksheet();
ColumnHelper columnHelper = sheet.getColumnHelper();
// POI column 3, OOXML column 4
CTCol col = columnHelper.getOrCreateColumn1Based(4, false);
assertNotNull(col);
assertNotNull(columnHelper.getColumn(3, false));
columnHelper.setColDefaultStyle(3, 2);
assertEquals(2, columnHelper.getColDefaultStyle(3));
assertEquals(-1, columnHelper.getColDefaultStyle(4));
StylesTable stylesTable = workbook.getStylesSource();
CTXf cellXf = CTXf.Factory.newInstance();
cellXf.setFontId(0);
cellXf.setFillId(0);
cellXf.setBorderId(0);
cellXf.setNumFmtId(0);
cellXf.setXfId(0);
stylesTable.putCellXf(cellXf);
CTCol col_2 = ctWorksheet.getColsArray(0).addNewCol();
col_2.setMin(10);
col_2.setMax(12);
col_2.setStyle(1);
assertEquals(1, columnHelper.getColDefaultStyle(11));
XSSFCellStyle cellStyle = new XSSFCellStyle(0, 0, stylesTable, null);
columnHelper.setColDefaultStyle(11, cellStyle);
assertEquals(0, col_2.getStyle());
assertEquals(1, columnHelper.getColDefaultStyle(10));
}
/**
* Returns the Column at the given 1 based index. POI default is 0 based, but the file stores as 1
* based.
*/
public CTCol getColumn1Based(long index1, boolean splitColumns) {
CTCols cols = worksheet.getColsArray(0);
// Fetching the array is quicker than working on the new style
// list, assuming we need to read many of them (which we often do),
// and assuming we're not making many changes (which we're not)
@SuppressWarnings("deprecation")
CTCol[] colArray = cols.getColArray();
for (CTCol col : colArray) {
long colMin = col.getMin();
long colMax = col.getMax();
if (colMin <= index1 && colMax >= index1) {
if (splitColumns) {
if (colMin < index1) {
insertCol(cols, colMin, (index1 - 1), new CTCol[] {col});
}
if (colMax > index1) {
insertCol(cols, (index1 + 1), colMax, new CTCol[] {col});
}
col.setMin(index1);
col.setMax(index1);
}
return col;
}
}
return null;
}
public CTCol cloneCol(CTCols cols, CTCol col) {
CTCol newCol = cols.addNewCol();
newCol.setMin(col.getMin());
newCol.setMax(col.getMax());
setColumnAttributes(col, newCol);
return newCol;
}
/** Return the CTCol at the given (0 based) column index, creating it if required. */
protected CTCol getOrCreateColumn1Based(long index1, boolean splitColumns) {
CTCol col = getColumn1Based(index1, splitColumns);
if (col == null) {
col = worksheet.getColsArray(0).addNewCol();
col.setMin(index1);
col.setMax(index1);
}
return col;
}
public void testSortColumns() {
CTWorksheet worksheet = CTWorksheet.Factory.newInstance();
ColumnHelper helper = new ColumnHelper(worksheet);
CTCols cols1 = CTCols.Factory.newInstance();
CTCol col1 = cols1.addNewCol();
col1.setMin(1);
col1.setMax(1);
col1.setWidth(88);
col1.setHidden(true);
CTCol col2 = cols1.addNewCol();
col2.setMin(2);
col2.setMax(3);
CTCol col3 = cols1.addNewCol();
col3.setMin(13);
col3.setMax(16750);
assertEquals(3, cols1.sizeOfColArray());
CTCol col4 = cols1.addNewCol();
col4.setMin(8);
col4.setMax(11);
assertEquals(4, cols1.sizeOfColArray());
CTCol col5 = cols1.addNewCol();
col5.setMin(4);
col5.setMax(5);
assertEquals(5, cols1.sizeOfColArray());
CTCol col6 = cols1.addNewCol();
col6.setMin(8);
col6.setMax(9);
col6.setHidden(true);
CTCol col7 = cols1.addNewCol();
col7.setMin(6);
col7.setMax(8);
col7.setWidth(17.0);
CTCol col8 = cols1.addNewCol();
col8.setMin(25);
col8.setMax(27);
CTCol col9 = cols1.addNewCol();
col9.setMin(20);
col9.setMax(30);
assertEquals(9, cols1.sizeOfColArray());
assertEquals(20, cols1.getColArray(8).getMin());
assertEquals(30, cols1.getColArray(8).getMax());
ColumnHelper.sortColumns(cols1);
assertEquals(9, cols1.sizeOfColArray());
assertEquals(25, cols1.getColArray(8).getMin());
assertEquals(27, cols1.getColArray(8).getMax());
}
/*
* Insert a new CTCol at position 0 into cols, setting min=min, max=max and
* copying all the colsWithAttributes array cols attributes into newCol
*/
private CTCol insertCol(CTCols cols, long min, long max, CTCol[] colsWithAttributes) {
if (!columnExists(cols, min, max)) {
CTCol newCol = cols.insertNewCol(0);
newCol.setMin(min);
newCol.setMax(max);
for (CTCol col : colsWithAttributes) {
setColumnAttributes(col, newCol);
}
return newCol;
}
return null;
}
public void testCloneCol() {
CTWorksheet worksheet = CTWorksheet.Factory.newInstance();
ColumnHelper helper = new ColumnHelper(worksheet);
CTCols cols = CTCols.Factory.newInstance();
CTCol col = CTCol.Factory.newInstance();
col.setMin(2);
col.setMax(8);
col.setHidden(true);
col.setWidth(13.4);
CTCol newCol = helper.cloneCol(cols, col);
assertEquals(2, newCol.getMin());
assertEquals(8, newCol.getMax());
assertTrue(newCol.getHidden());
assertEquals(13.4, newCol.getWidth(), 0.0);
}
private CTCol insertCol(
CTCols cols,
long min,
long max,
CTCol[] colsWithAttributes,
boolean ignoreExistsCheck,
CTCol overrideColumn) {
if (ignoreExistsCheck || !columnExists(cols, min, max)) {
CTCol newCol = cols.insertNewCol(0);
newCol.setMin(min);
newCol.setMax(max);
for (CTCol col : colsWithAttributes) {
setColumnAttributes(col, newCol);
}
if (overrideColumn != null) setColumnAttributes(overrideColumn, newCol);
return newCol;
}
return null;
}
/**
* Returns the Column at the given 1 based index. POI default is 0 based, but the file stores as 1
* based.
*/
public CTCol getColumn1Based(long index1, boolean splitColumns) {
CTCols colsArray = worksheet.getColsArray(0);
for (int i = 0; i < colsArray.sizeOfColArray(); i++) {
CTCol colArray = colsArray.getColArray(i);
if (colArray.getMin() <= index1 && colArray.getMax() >= index1) {
if (splitColumns) {
if (colArray.getMin() < index1) {
insertCol(colsArray, colArray.getMin(), (index1 - 1), new CTCol[] {colArray});
}
if (colArray.getMax() > index1) {
insertCol(colsArray, (index1 + 1), colArray.getMax(), new CTCol[] {colArray});
}
colArray.setMin(index1);
colArray.setMax(index1);
}
return colArray;
}
}
return null;
}
public CTCols addCleanColIntoCols(CTCols cols, CTCol col) {
boolean colOverlaps = false;
for (int i = 0; i < cols.sizeOfColArray(); i++) {
CTCol ithCol = cols.getColArray(i);
long[] range1 = {ithCol.getMin(), ithCol.getMax()};
long[] range2 = {col.getMin(), col.getMax()};
long[] overlappingRange = NumericRanges.getOverlappingRange(range1, range2);
int overlappingType = NumericRanges.getOverlappingType(range1, range2);
// different behavior required for each of the 4 different
// overlapping types
if (overlappingType == NumericRanges.OVERLAPS_1_MINOR) {
ithCol.setMax(overlappingRange[0] - 1);
CTCol rangeCol =
insertCol(cols, overlappingRange[0], overlappingRange[1], new CTCol[] {ithCol, col});
i++;
CTCol newCol = insertCol(cols, (overlappingRange[1] + 1), col.getMax(), new CTCol[] {col});
i++;
} else if (overlappingType == NumericRanges.OVERLAPS_2_MINOR) {
ithCol.setMin(overlappingRange[1] + 1);
CTCol rangeCol =
insertCol(cols, overlappingRange[0], overlappingRange[1], new CTCol[] {ithCol, col});
i++;
CTCol newCol = insertCol(cols, col.getMin(), (overlappingRange[0] - 1), new CTCol[] {col});
i++;
} else if (overlappingType == NumericRanges.OVERLAPS_2_WRAPS) {
setColumnAttributes(col, ithCol);
if (col.getMin() != ithCol.getMin()) {
CTCol newColBefore =
insertCol(cols, col.getMin(), (ithCol.getMin() - 1), new CTCol[] {col});
i++;
}
if (col.getMax() != ithCol.getMax()) {
CTCol newColAfter =
insertCol(cols, (ithCol.getMax() + 1), col.getMax(), new CTCol[] {col});
i++;
}
} else if (overlappingType == NumericRanges.OVERLAPS_1_WRAPS) {
if (col.getMin() != ithCol.getMin()) {
CTCol newColBefore =
insertCol(cols, ithCol.getMin(), (col.getMin() - 1), new CTCol[] {ithCol});
i++;
}
if (col.getMax() != ithCol.getMax()) {
CTCol newColAfter =
insertCol(cols, (col.getMax() + 1), ithCol.getMax(), new CTCol[] {ithCol});
i++;
}
ithCol.setMin(overlappingRange[0]);
ithCol.setMax(overlappingRange[1]);
setColumnAttributes(col, ithCol);
}
if (overlappingType != NumericRanges.NO_OVERLAPS) {
colOverlaps = true;
}
}
if (!colOverlaps) {
CTCol newCol = cloneCol(cols, col);
}
sortColumns(cols);
return cols;
}