/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.xssf.usermodel;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.TempFile;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Test;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;
public final class TestXSSFTable {
@Test
public void bug56274() throws IOException {
// read sample file
XSSFWorkbook wb1 = XSSFTestDataSamples.openSampleWorkbook("56274.xlsx");
// read the original sheet header order
XSSFRow row = wb1.getSheetAt(0).getRow(0);
List<String> headers = new ArrayList<String>();
for (Cell cell : row) {
headers.add(cell.getStringCellValue());
}
// save the worksheet as-is using SXSSF
File outputFile = TempFile.createTempFile("poi-56274", ".xlsx");
SXSSFWorkbook outputWorkbook = new SXSSFWorkbook(wb1);
FileOutputStream fos = new FileOutputStream(outputFile);
outputWorkbook.write(fos);
fos.close();
outputWorkbook.close();
// re-read the saved file and make sure headers in the xml are in the original order
FileInputStream fis = new FileInputStream(outputFile);
XSSFWorkbook wb2 = new XSSFWorkbook(fis);
fis.close();
CTTable ctTable = wb2.getSheetAt(0).getTables().get(0).getCTTable();
CTTableColumn[] ctTableColumnArray = ctTable.getTableColumns().getTableColumnArray();
assertEquals("number of headers in xml table should match number of header cells in worksheet",
headers.size(), ctTableColumnArray.length);
for (int i = 0; i < headers.size(); i++) {
assertEquals("header name in xml table should match number of header cells in worksheet",
headers.get(i), ctTableColumnArray[i].getName());
}
assertTrue(outputFile.delete());
wb2.close();
wb1.close();
}
@Test
public void testCTTableStyleInfo() throws IOException {
XSSFWorkbook outputWorkbook = new XSSFWorkbook();
XSSFSheet sheet = outputWorkbook.createSheet();
//Create
XSSFTable outputTable = sheet.createTable();
outputTable.setDisplayName("Test");
CTTable outputCTTable = outputTable.getCTTable();
//Style configurations
CTTableStyleInfo outputStyleInfo = outputCTTable.addNewTableStyleInfo();
outputStyleInfo.setName("TableStyleLight1");
outputStyleInfo.setShowColumnStripes(false);
outputStyleInfo.setShowRowStripes(true);
XSSFWorkbook inputWorkbook = XSSFTestDataSamples.writeOutAndReadBack(outputWorkbook);
List<XSSFTable> tables = inputWorkbook.getSheetAt(0).getTables();
assertEquals("Tables number", 1, tables.size());
XSSFTable inputTable = tables.get(0);
assertEquals("Table display name", outputTable.getDisplayName(), inputTable.getDisplayName());
CTTableStyleInfo inputStyleInfo = inputTable.getCTTable().getTableStyleInfo();
assertEquals("Style name", outputStyleInfo.getName(), inputStyleInfo.getName());
assertEquals("Show column stripes",
outputStyleInfo.getShowColumnStripes(), inputStyleInfo.getShowColumnStripes());
assertEquals("Show row stripes",
outputStyleInfo.getShowRowStripes(), inputStyleInfo.getShowRowStripes());
inputWorkbook.close();
outputWorkbook.close();
}
@Test
public void findColumnIndex() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertNotNull(table);
assertEquals("column header has special escaped characters",
0, table.findColumnIndex("calc='#*'#"));
assertEquals(1, table.findColumnIndex("Name"));
assertEquals(2, table.findColumnIndex("Number"));
assertEquals("case insensitive", 2, table.findColumnIndex("NuMbEr"));
// findColumnIndex should return -1 if no column header name matches
assertEquals(-1, table.findColumnIndex(null));
assertEquals(-1, table.findColumnIndex(""));
assertEquals(-1, table.findColumnIndex("one"));
wb.close();
}
@Test
public void findColumnIndexIsRelativeToTableNotSheet() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("DataTableCities.xlsx");
XSSFTable table = wb.getTable("SmallCity");
// Make sure that XSSFTable.findColumnIndex returns the column index relative to the first
// column in the table, not the column number in the sheet
assertEquals(0, table.findColumnIndex("City")); // column I in worksheet but 0th column in table
assertEquals(1, table.findColumnIndex("Latitude"));
assertEquals(2, table.findColumnIndex("Longitude"));
assertEquals(3, table.findColumnIndex("Population"));
wb.close();
}
@Test
public void getSheetName() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals("Table", table.getSheetName());
wb.close();
}
@Test
public void isHasTotalsRow() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertFalse(table.isHasTotalsRow());
wb.close();
}
@Test
public void getStartColIndex() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals(0, table.getStartColIndex());
wb.close();
}
@Test
public void getEndColIndex() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals(2, table.getEndColIndex());
wb.close();
}
@Test
public void getStartRowIndex() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals(0, table.getStartRowIndex());
wb.close();
}
@Test
public void getEndRowIndex() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals(6, table.getEndRowIndex());
wb.close();
}
@Test
public void getStartCellReference() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals(new CellReference("A1"), table.getStartCellReference());
wb.close();
}
@Test
public void getEndCellReference() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals(new CellReference("C7"), table.getEndCellReference());
wb.close();
}
@Test
public void getNumberOfMappedColumns() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals(3, table.getNumberOfMappedColumns());
wb.close();
}
@Test
public void getAndSetDisplayName() throws IOException {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
XSSFTable table = wb.getTable("\\_Prime.1");
assertEquals("\\_Prime.1", table.getDisplayName());
table.setDisplayName(null);
assertNull(table.getDisplayName());
assertEquals("\\_Prime.1", table.getName()); // name and display name are different
table.setDisplayName("Display name");
assertEquals("Display name", table.getDisplayName());
assertEquals("\\_Prime.1", table.getName()); // name and display name are different
wb.close();
}
@Test
public void getCellReferences() {
// make sure that cached start and end cell references
// can be synchronized with the underlying CTTable
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = wb.createSheet();
XSSFTable table = sh.createTable();
CTTable ctTable = table.getCTTable();
ctTable.setRef("B2:E8");
assertEquals(new CellReference("B2"), table.getStartCellReference());
assertEquals(new CellReference("E8"), table.getEndCellReference());
// At this point start and end cell reference are cached
// and may not follow changes to the underlying CTTable
ctTable.setRef("C1:M3");
assertEquals(new CellReference("B2"), table.getStartCellReference());
assertEquals(new CellReference("E8"), table.getEndCellReference());
// Force a synchronization between CTTable and XSSFTable
// start and end cell references
table.updateReferences();
assertEquals(new CellReference("C1"), table.getStartCellReference());
assertEquals(new CellReference("M3"), table.getEndCellReference());
}
@Test
public void getRowCount() {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = wb.createSheet();
XSSFTable table = sh.createTable();
CTTable ctTable = table.getCTTable();
assertEquals(0, table.getRowCount());
ctTable.setRef("B2:B2");
// update cell references to clear the cache
table.updateReferences();
assertEquals(1, table.getRowCount());
ctTable.setRef("B2:B12");
// update cell references to clear the cache
table.updateReferences();
assertEquals(11, table.getRowCount());
}
}