List of usage examples for org.apache.poi.ss.usermodel Sheet getColumnWidth
int getColumnWidth(int columnIndex);
Character width is defined as the maximum digit width of the numbers From source file:org.phenotips.export.internal.SpreadsheetExporter.java License:Open Source License From source file:org.tiefaces.components.websheet.service.WebSheetLoader.java License:MIT License From source file:org.tiefaces.components.websheet.service.WebSheetLoader.java License:MIT License From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java License:MIT License From source file:ro.fortsoft.wicket.pivot.exporter.PivotXlsExporter.java License:Apache License From source file:uk.co.certait.htmlexporter.writer.excel.ExcelExporter.java License:Apache License From source file:uk.co.spudsoft.birt.emitters.excel.handlers.CellContentHandler.java License:Open Source License From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoColWidthsTest.java License:Open Source License From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoFilterTest.java License:Open Source License From source file:uk.co.spudsoft.birt.emitters.excel.tests.BasicReportTest.java License:Open Source License0, 1, 2, ... Usage
protected void write(DataSection section, Sheet sheet) {
DataCell[][] cells = section.getMatrix();
Styler styler = new Styler();
Row row;/*w w w. j ava2 s . co m*/
for (Integer y = 0; y <= section.getMaxY(); y++) {
row = sheet.createRow(y);
Integer maxLines = 0;
for (Integer x = 0; x <= section.getMaxX(); x++) {
DataCell dataCell = cells[x][y];
if (dataCell == null) {
continue;
}
Cell cell = row.createCell(x);
cell.setCellValue(dataCell.getValue());
styler.style(dataCell, cell, this.wBook);
if (dataCell.getNumberOfLines() != null) {
maxLines = maxLines < dataCell.getNumberOfLines() ? dataCell.getNumberOfLines() : maxLines;
}
}
if (maxLines > 1) {
Integer height = maxLines * 400;
row.setHeight(height.shortValue());
}
}
for (int col = 0; section.getMaxX() >= col; col++) {
sheet.autoSizeColumn(col);
if (sheet.getColumnWidth(col) > (DataToCellConverter.charactersPerLine * 210)) {
sheet.setColumnWidth(col, DataToCellConverter.charactersPerLine * 210);
}
}
/** Merging has to be done after autosizing because otherwise autosizing breaks */
for (Integer y = 0; y <= section.getMaxY(); y++) {
for (Integer x = 0; x <= section.getMaxX(); x++) {
DataCell dataCell = cells[x][y];
if (dataCell != null && dataCell.getMergeX() != null) {
sheet.addMergedRegion(new CellRangeAddress(y, y, x, x + dataCell.getMergeX()));
}
/*
* No longer will be merging cells on the Y axis, but keep this code for future reference. if
* (dataCell.getYBoundry() != null) { sheet.addMergedRegion(new CellRangeAddress(dataCell.y,
* dataCell.getYBoundry(), dataCell.x, dataCell.x)); }
*/
}
}
}
/**
* Load header row without configuration tab.
*
* @param rangeBuildRef//w w w. j ava 2 s . c om
* the range build ref
* @param rendered
* the rendered
* @return the list
*/
private List<HeaderCell> loadHeaderRowWithoutConfigurationTab(final RangeBuildRef rangeBuildRef,
final boolean rendered) {
int firstCol = rangeBuildRef.getLeft();
int lastCol = rangeBuildRef.getRight();
double totalWidth = (double) rangeBuildRef.getTotalWidth();
Sheet sheet1 = rangeBuildRef.getSheet();
List<HeaderCell> headercells = new ArrayList<>();
for (int i = firstCol; i <= lastCol; i++) {
if (!sheet1.isColumnHidden(i)) {
String style = getHeaderColumnStyle(parent.getWb(), null, sheet1.getColumnWidth(i), totalWidth);
headercells.add(new HeaderCell("1", "1", style, style, WebSheetUtility.getExcelColumnName(i),
rendered, true));
}
}
fillToMaxColumns(headercells);
return headercells;
}
/**
* Gets the column width style./*ww w.j a v a 2s . c o m*/
*
* @param sheet1
* the sheet 1
* @param cellRangeMap
* the cell range map
* @param cellindex
* the cellindex
* @param cindex
* the cindex
* @param totalWidth
* the total width
* @return the column width style
*/
private String getColumnWidthStyle(final Sheet sheet1, final Map<String, CellRangeAddress> cellRangeMap,
final String cellindex, final int cindex, final double totalWidth) {
CellRangeAddress caddress = cellRangeMap.get(cellindex);
double colWidth;
// check whether the cell has rowspan or colspan
if (caddress != null) {
colWidth = CellStyleUtility.calcTotalWidth(sheet1, caddress.getFirstColumn(), caddress.getLastColumn(),
0);
} else {
colWidth = sheet1.getColumnWidth(cindex);
}
return getWidthStyle(colWidth, totalWidth);
}
/**
* Calc total width.//from w w w.j av a2 s . c o m
*
* @param sheet1
* the sheet 1
* @param firstCol
* the first col
* @param lastCol
* the last col
* @param additionalWidth
* the additional width
* @return the int
*/
// e.g. lineNumberColumnWidth and addRowColumnWidth
public static int calcTotalWidth(final Sheet sheet1, final int firstCol, final int lastCol,
final int additionalWidth) {
int totalWidth = additionalWidth;
for (int i = firstCol; i <= lastCol; i++) {
totalWidth += sheet1.getColumnWidth(i);
}
return totalWidth;
}
private void autoSizeColumns(Sheet sheetData, int maxColNum) {
try {//from w ww . j a v a 2 s .c om
// Autosize columns
int width = 0;
for (int col = 0; col < maxColNum; col++) {
sheetData.autoSizeColumn(col);
int cwidth = sheetData.getColumnWidth(col);
cwidth += 500;
sheetData.setColumnWidth(col, cwidth);
width += cwidth;
}
// calculate zoom factor
int nominator = 45000 * 100 / width;
if (nominator < 100)
sheetData.setZoom(nominator, 100);
} catch (HeadlessException he) {
// No UI, no autosize :(
}
}
protected void formatSheet(Sheet sheet) {
int lastRowWithData = 0;
for (int i = sheet.getLastRowNum(); i >= 0; --i) {
if (sheet.getRow(i) != null && sheet.getRow(i).getPhysicalNumberOfCells() > 0) {
lastRowWithData = i;/* w w w. jav a2s .co m*/
break;
}
}
for (int i = 0; i < sheet.getRow(lastRowWithData).getPhysicalNumberOfCells(); ++i) {
sheet.autoSizeColumn(i);
}
for (int i = 0; i < sheet.getRow(sheet.getLastRowNum()).getPhysicalNumberOfCells(); ++i) {
sheet.setColumnWidth(i, (int) (sheet.getColumnWidth(i) * 1.2));
}
}
/**
* Calculate the width of a set of columns, in millimetres.
* @param startCol/*from w w w .ja v a 2 s. c om*/
* The first column to consider (inclusive).
* @param endCol
* The last column to consider (inclusive).
* @return
* The sum of the widths of all columns between startCol and endCol (inclusive) in millimetres.
*/
private double spanWidthMillimetres(Sheet sheet, int startCol, int endCol) {
int result = 0;
for (int columnIndex = startCol; columnIndex <= endCol; ++columnIndex) {
result += sheet.getColumnWidth(columnIndex);
}
return ClientAnchorConversions.widthUnits2Millimetres(result);
}
@Test
public void testRunReport() throws BirtException, IOException {
InputStream inputStream = runAndRenderReport("AutoColWidths.rptdesign", "xlsx");
assertNotNull(inputStream);/* w w w .j av a 2s . c om*/
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals(1, workbook.getNumberOfSheets());
assertEquals("AutoColWidths Test Report", workbook.getSheetAt(0).getSheetName());
Sheet sheet = workbook.getSheetAt(0);
assertEquals(23, this.firstNullRow(sheet));
assertEquals(6127, sheet.getColumnWidth(0));
assertEquals(2048, sheet.getColumnWidth(1));
assertEquals(4999, sheet.getColumnWidth(2));
assertEquals(3812, sheet.getColumnWidth(3));
assertEquals(3812, sheet.getColumnWidth(4));
assertEquals(2048, sheet.getColumnWidth(5));
assertTrue((sheet.getColumnWidth(6) > 3000) && (sheet.getColumnWidth(6) < 3200));
assertTrue((sheet.getColumnWidth(7) > 2100) && (sheet.getColumnWidth(7) < 2900));
assertEquals(2048, sheet.getColumnWidth(8));
DataFormatter formatter = new DataFormatter();
assertEquals("1", formatter.formatCellValue(sheet.getRow(2).getCell(1)));
assertEquals("2019-10-11 13:18:46", formatter.formatCellValue(sheet.getRow(2).getCell(2)));
assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(2).getCell(3)));
assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(2).getCell(4)));
assertEquals("false", formatter.formatCellValue(sheet.getRow(2).getCell(5)));
} finally {
inputStream.close();
}
}
@Test
public void autoFilterMultiTables() throws Exception {
debug = false;//from w w w . j a va 2 s .c o m
autoFilter = true;
InputStream inputStream = runAndRenderReport("NumberFormats.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals(1, workbook.getNumberOfSheets());
assertEquals("Number Formats Test Report", workbook.getSheetAt(0).getSheetName());
Sheet sheet = workbook.getSheetAt(0);
assertEquals(22, this.firstNullRow(sheet));
assertEquals(3035, sheet.getColumnWidth(0));
assertEquals(3913, sheet.getColumnWidth(1));
assertEquals(7021, sheet.getColumnWidth(2));
assertEquals(4205, sheet.getColumnWidth(3));
assertEquals(3474, sheet.getColumnWidth(4));
assertEquals(2852, sheet.getColumnWidth(5));
assertEquals(3510, sheet.getColumnWidth(6));
assertEquals(2889, sheet.getColumnWidth(7));
assertEquals(2048, sheet.getColumnWidth(8));
XSSFName name = workbook.getName(XSSFName.BUILTIN_FILTER_DB);
assertEquals(0, name.getSheetIndex());
assertEquals("'Number Formats Test Report'!$A$1:$H$3", name.getRefersToFormula());
assertNotNull(workbook.getSheetAt(0).getCTWorksheet().getAutoFilter());
} finally {
inputStream.close();
}
}
@Test
public void testRunReport() throws BirtException, IOException {
InputStream inputStream = runAndRenderReport("Simple.rptdesign", "xlsx");
assertNotNull(inputStream);/*from w w w.ja v a 2 s. c o m*/
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals(1, workbook.getNumberOfSheets());
assertEquals("Simple Test Report", workbook.getSheetAt(0).getSheetName());
Sheet sheet = workbook.getSheetAt(0);
assertNotNull(sheet.getRow(0));
assertNotNull(sheet.getRow(1));
assertNotNull(sheet.getRow(2));
assertNotNull(sheet.getRow(3));
assertNull(sheet.getRow(4));
assertEquals(1.0, sheet.getRow(1).getCell(0).getNumericCellValue(), 0.001);
assertEquals(2.0, sheet.getRow(1).getCell(1).getNumericCellValue(), 0.001);
assertEquals(3.0, sheet.getRow(1).getCell(2).getNumericCellValue(), 0.001);
assertEquals(2.0, sheet.getRow(2).getCell(0).getNumericCellValue(), 0.001);
assertEquals(4.0, sheet.getRow(2).getCell(1).getNumericCellValue(), 0.001);
assertEquals(6.0, sheet.getRow(2).getCell(2).getNumericCellValue(), 0.001);
assertEquals(3.0, sheet.getRow(3).getCell(0).getNumericCellValue(), 0.001);
assertEquals(6.0, sheet.getRow(3).getCell(1).getNumericCellValue(), 0.001);
assertEquals(9.0, sheet.getRow(3).getCell(2).getNumericCellValue(), 0.001);
assertEquals(3510, sheet.getColumnWidth(0));
assertEquals(3510, sheet.getColumnWidth(1));
assertEquals(3510, sheet.getColumnWidth(2));
} finally {
inputStream.close();
}
}