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 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 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));
}
@SuppressWarnings("deprecation")
public void cleanColumns() {
this.newCols = CTCols.Factory.newInstance();
CTCols aggregateCols = CTCols.Factory.newInstance();
CTCols[] colsArray = worksheet.getColsArray();
assert (colsArray != null);
for (CTCols cols : colsArray) {
for (CTCol col : cols.getColArray()) {
cloneCol(aggregateCols, col);
}
}
sortColumns(aggregateCols);
CTCol[] colArray = aggregateCols.getColArray();
sweepCleanColumns(newCols, colArray, null);
int i = colsArray.length;
for (int y = i - 1; y >= 0; y--) {
worksheet.removeCols(y);
}
worksheet.addNewCols();
worksheet.setColsArray(0, newCols);
}
private static int countColumns(CTWorksheet worksheet) {
int count;
count = 0;
for (int i = 0; i < worksheet.sizeOfColsArray(); i++) {
for (int y = 0; y < worksheet.getColsArray(i).sizeOfColArray(); y++) {
for (long k = worksheet.getColsArray(i).getColArray(y).getMin();
k <= worksheet.getColsArray(i).getColArray(y).getMax();
k++) {
count++;
}
}
}
return count;
}
/**
* 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;
}
/** 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;
}
@SuppressWarnings(
"deprecation") // YK: getXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5
// support
public void cleanColumns() {
this.newCols = CTCols.Factory.newInstance();
CTCols[] colsArray = worksheet.getColsArray();
int i = 0;
for (i = 0; i < colsArray.length; i++) {
CTCols cols = colsArray[i];
CTCol[] colArray = cols.getColArray();
for (int y = 0; y < colArray.length; y++) {
CTCol col = colArray[y];
newCols = addCleanColIntoCols(newCols, col);
}
}
for (int y = i - 1; y >= 0; y--) {
worksheet.removeCols(y);
}
worksheet.addNewCols();
worksheet.setColsArray(0, newCols);
}
/**
* 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;
}
private void setWorksheetOutlineLevelRow() {
CTWorksheet ct = _sh.getCTWorksheet();
CTSheetFormatPr pr = ct.isSetSheetFormatPr() ? ct.getSheetFormatPr() : ct.addNewSheetFormatPr();
if (outlineLevelRow > 0) pr.setOutlineLevelRow((short) outlineLevelRow);
}
/**
* Adds a copy of HSSFConditionalFormatting object to the sheet
*
* <p>This method could be used to copy HSSFConditionalFormatting object from one sheet to
* another. For example:
*
* <pre>
* HSSFConditionalFormatting cf = sheet.getConditionalFormattingAt(index);
* newSheet.addConditionalFormatting(cf);
* </pre>
*
* @param cf HSSFConditionalFormatting object
* @return index of the new Conditional Formatting object
*/
public int addConditionalFormatting(ConditionalFormatting cf) {
XSSFConditionalFormatting xcf = (XSSFConditionalFormatting) cf;
CTWorksheet sh = _sheet.getCTWorksheet();
sh.addNewConditionalFormatting().set(xcf.getCTConditionalFormatting().copy());
return sh.sizeOfConditionalFormattingArray() - 1;
}