List of usage examples for org.apache.poi.ss.usermodel Sheet getColumnStyle
public CellStyle getColumnStyle(int column);
From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java
License:Open Source License
public void reloadActiveSheetColumnRowStyles() { final Workbook workbook = spreadsheet.getWorkbook(); if (spreadsheet.getState().rowIndexToStyleIndex == null) { spreadsheet.getState().rowIndexToStyleIndex = new HashMap<Integer, Integer>( workbook.getNumCellStyles()); } else {/*from ww w . j a v a 2s . co m*/ spreadsheet.getState().rowIndexToStyleIndex.clear(); } if (spreadsheet.getState().columnIndexToStyleIndex == null) { spreadsheet.getState().columnIndexToStyleIndex = new HashMap<Integer, Integer>( workbook.getNumCellStyles()); } else { spreadsheet.getState().columnIndexToStyleIndex.clear(); } if (spreadsheet.getState().lockedColumnIndexes == null) { spreadsheet.getState().lockedColumnIndexes = new HashSet<Integer>(); } else { spreadsheet.getState().lockedColumnIndexes.clear(); } if (spreadsheet.getState().lockedRowIndexes == null) { spreadsheet.getState().lockedRowIndexes = new HashSet<Integer>(); } else { spreadsheet.getState().lockedRowIndexes.clear(); } Sheet activeSheet = spreadsheet.getActiveSheet(); for (int i = 0; i < spreadsheet.getRows(); i++) { Row row = activeSheet.getRow(i); if (row != null && row.getRowStyle() != null) { int styleIndex = row.getRowStyle().getIndex(); spreadsheet.getState().rowIndexToStyleIndex.put(i + 1, styleIndex); if (row.getRowStyle().getLocked()) { spreadsheet.getState().lockedRowIndexes.add(i + 1); } } else { if (spreadsheet.isActiveSheetProtected()) { spreadsheet.getState().lockedRowIndexes.add(i + 1); } } } for (int i = 0; i < spreadsheet.getColumns(); i++) { if (activeSheet.getColumnStyle(i) != null) { int styleIndex = activeSheet.getColumnStyle(i).getIndex(); spreadsheet.getState().columnIndexToStyleIndex.put(i + 1, styleIndex); if (activeSheet.getColumnStyle(i).getLocked()) { spreadsheet.getState().lockedColumnIndexes.add(i + 1); } } } }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * /*ww w . j a v a 2s . c om*/ * * @param expected * @param actual * @param isActCopyOfExp ??????true * @throws ReportsCheckException */ public static void checkSheet(Sheet expected, Sheet actual, boolean isActCopyOfExp) throws ReportsCheckException { List<CheckMessage> errors = new ArrayList<CheckMessage>(); Workbook expectedWorkbook = expected.getWorkbook(); Workbook actualWorkbook = actual.getWorkbook(); if (log.isDebugEnabled()) { log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)) + "] check start!"); } // ---------------------- // ???? // ---------------------- // ?? String eSheetName = expectedWorkbook.getSheetName(expectedWorkbook.getSheetIndex(expected)); String aSheetName = actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)); if (!isActCopyOfExp) { if (!eSheetName.equals(aSheetName)) { errors.add(new CheckMessage("??", eSheetName, aSheetName)); } } // ? String ePrintSetupString = getPrintSetupString(expected.getPrintSetup()); String aPrintSetupString = getPrintSetupString(actual.getPrintSetup()); if (!ePrintSetupString.equals(aPrintSetupString)) { errors.add(new CheckMessage("?", ePrintSetupString, aPrintSetupString)); } // ? String eHeaderString = getHeaderString(expected.getHeader()); String aHeaderString = getHeaderString(actual.getHeader()); if (!eHeaderString.equals(aHeaderString)) { errors.add(new CheckMessage("", eHeaderString, aHeaderString)); } String eFooterString = getFooterString(expected.getFooter()); String aFooterString = getFooterString(actual.getFooter()); if (!eFooterString.equals(aFooterString)) { errors.add(new CheckMessage("", eFooterString, aFooterString)); } // String eBreaksString = getBreaksString(expected); String aBreaksString = getBreaksString(actual); log.debug(eBreaksString + "/" + aBreaksString); if (!eBreaksString.equals(aBreaksString)) { errors.add(new CheckMessage("", eBreaksString, aBreaksString)); } // ? String expectedPrintArea = expectedWorkbook.getPrintArea(expectedWorkbook.getSheetIndex(expected)); String actualPrintArea = actualWorkbook.getPrintArea(actualWorkbook.getSheetIndex(actual)); if (expectedPrintArea != null || actualPrintArea != null) { // ????????Null????????????? // if ( expectedPrintArea == null || actualPrintArea == null || !equalPrintArea( expectedPrintArea, actualPrintArea, isActCopyOfExp)) { // errors.add( new CheckMessage( "?", expectedPrintArea, actualPrintArea)); // } if (!isActCopyOfExp) { if (expectedPrintArea == null || actualPrintArea == null || !expectedPrintArea.equals(actualPrintArea)) { errors.add(new CheckMessage("?", expectedPrintArea, actualPrintArea)); } } } // (?) String ePaneInformationString = getPaneInformationString(expected.getPaneInformation()); String aPaneInformationString = getPaneInformationString(actual.getPaneInformation()); if (!ePaneInformationString.equals(aPaneInformationString)) { errors.add(new CheckMessage("(?)", expectedPrintArea, actualPrintArea)); } // ?????? // ????? // ????? // // if (expected.isDisplayGridlines() ^ actual.isDisplayGridlines()) { errors.add(new CheckMessage("", String.valueOf(expected.isDisplayGridlines()), String.valueOf(actual.isDisplayGridlines()))); } // ? if (expected.isDisplayRowColHeadings() ^ actual.isDisplayRowColHeadings()) { errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayRowColHeadings()), String.valueOf(actual.isDisplayRowColHeadings()))); } // ? if (expected.isDisplayFormulas() ^ actual.isDisplayFormulas()) { errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayFormulas()), String.valueOf(actual.isDisplayFormulas()))); } // ?? if (expected.getNumMergedRegions() != actual.getNumMergedRegions()) { errors.add(new CheckMessage("??", String.valueOf(expected.getNumMergedRegions()), String.valueOf(actual.getNumMergedRegions()))); } for (int i = 0; i < actual.getNumMergedRegions(); i++) { CellRangeAddress actualAddress = null; if (expected instanceof HSSFSheet) { actualAddress = ((HSSFSheet) actual).getMergedRegion(i); } else if (expected instanceof XSSFSheet) { actualAddress = ((XSSFSheet) actual).getMergedRegion(i); } StringBuffer expectedAdressBuffer = new StringBuffer(); boolean equalAddress = false; for (int j = 0; j < expected.getNumMergedRegions(); j++) { CellRangeAddress expectedAddress = null; if (expected instanceof HSSFSheet) { expectedAddress = ((HSSFSheet) expected).getMergedRegion(j); } else if (expected instanceof XSSFSheet) { expectedAddress = ((XSSFSheet) expected).getMergedRegion(j); } if (expectedAddress.toString().equals(actualAddress.toString())) { equalAddress = true; break; } CellReference crA = new CellReference(expectedAddress.getFirstRow(), expectedAddress.getFirstColumn()); CellReference crB = new CellReference(expectedAddress.getLastRow(), expectedAddress.getLastColumn()); expectedAdressBuffer.append(" [" + crA.formatAsString() + ":" + crB.formatAsString() + "]"); } if (!equalAddress) { errors.add(new CheckMessage("??", expectedAdressBuffer.toString(), actualAddress.toString())); } } int maxColumnNum = -1; if (expected instanceof HSSFSheet) { maxColumnNum = HSSF_MAX_COLUMN_NUMBER; } else if (expected instanceof XSSFSheet) { maxColumnNum = XSSF_MAX_COLUMN_NUMBER; } for (int i = 0; i < maxColumnNum; i++) { try { // checkCellStyle(expected.getWorkbook(), expected.getColumnStyle(i), actual.getWorkbook(), actual.getColumnStyle(i)); } catch (ReportsCheckException e) { CheckMessage checkMessage = e.getCheckMessages().iterator().next(); checkMessage.setMessage("[" + i + "]" + checkMessage.getMessage()); errors.add(checkMessage); } // if (expected.getColumnWidth(i) != actual.getColumnWidth(i)) { errors.add(new CheckMessage("[" + i + "]", String.valueOf(expected.getColumnWidth(i)), String.valueOf(actual.getColumnWidth(i)))); } } // ??? if (expected.getLastRowNum() != actual.getLastRowNum()) { // ?????? if (expected.getLastRowNum() < actual.getLastRowNum()) { int lastRowIndex = -1; if (expected instanceof HSSFSheet) { lastRowIndex = 0; } Iterator<Row> rowIterator = actual.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // ????? Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellTypeEnum() != CellType.BLANK) { lastRowIndex = row.getRowNum(); break; } } } if (expected.getLastRowNum() != lastRowIndex) { errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()), String.valueOf(lastRowIndex))); } } else { errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()), String.valueOf(actual.getLastRowNum()))); } } if (errors.isEmpty()) { for (int i = 0; i <= expected.getLastRowNum(); i++) { try { checkRow(expected.getRow(i), actual.getRow(i)); } catch (ReportsCheckException e) { errors.addAll(e.getCheckMessages()); } } } if (!errors.isEmpty()) { if (log.isErrorEnabled()) { for (CheckMessage message : errors) { log.error("?[" + message.getMessage() + "]"); log.error(":" + message.getExpected()); log.error(":" + message.getActual()); } } throw new ReportsCheckException(errors); } if (log.isDebugEnabled()) { log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)) + "] check end."); } }
From source file:org.cerberus.service.export.ExportServiceFactory.java
License:Open Source License
private int createRow(String test, HashMap<String, List<TestCaseExecution>> executionsPerTestCase, Sheet sheet, int currentIndex, List<String> mapCountries) { int lastRow = currentIndex + executionsPerTestCase.size(); int current = currentIndex; TreeMap<String, List<TestCaseExecution>> sortedKeys = new TreeMap<String, List<TestCaseExecution>>( executionsPerTestCase);// w w w. j a va 2 s . c om CellStyle wrapStyle = sheet.getColumnStyle(0); //Create new style wrapStyle.setWrapText(true); //Set wordwrap for (String testCaseKey : sortedKeys.keySet()) { List<String> browserEnvironment = new LinkedList<String>(); String application; String description; Row r = sheet.createRow(current); List<TestCaseExecution> executionList = executionsPerTestCase.get(testCaseKey); Cell testCell = r.createCell(0); testCell.setCellValue(test); testCell.setCellStyle(wrapStyle); r.createCell(1).setCellValue(testCaseKey); //gets the first object to retrieve the application - at least exists one test case execution if (executionList.isEmpty()) { application = "N/D"; description = "N/D"; } else { application = executionList.get(0).getApplication(); description = executionList.get(0).getTestCaseObj().getBehaviorOrValueExpected(); } //Sets the application and description r.createCell(2).setCellValue(application); r.createCell(3).setCellValue(description); int rowStartedTestCaseInfo = current; for (TestCaseExecution exec : executionList) { if (browserEnvironment.isEmpty()) { browserEnvironment.add(exec.getEnvironment() + "_" + exec.getBrowser()); r.createCell(4).setCellValue(exec.getEnvironment()); r.createCell(5).setCellValue(exec.getBrowser()); } else { int index = browserEnvironment.indexOf(exec.getEnvironment() + "_" + exec.getBrowser()); //Does not exist any information about browser and environment if (browserEnvironment.indexOf(exec.getEnvironment() + "_" + exec.getBrowser()) == -1) { //need to add another row with the same characteristics r = sheet.createRow(++current); r.createCell(0).setCellValue(test); r.createCell(1).setCellValue(testCaseKey); r.createCell(2).setCellValue(application); r.createCell(3).setCellValue(description); r.createCell(4).setCellValue(exec.getEnvironment()); r.createCell(5).setCellValue(exec.getBrowser()); browserEnvironment.add(exec.getEnvironment() + "_" + exec.getBrowser()); } else { //there is information about the browser and environment Row rowExisting = sheet.getRow(rowStartedTestCaseInfo + index); r = rowExisting; } } //TODO:FN tirar daqui estes valores int indexOfCountry = mapCountries.indexOf(exec.getCountry()) + 6; Cell executionResult = r.createCell(indexOfCountry); executionResult.setCellValue(exec.getControlStatus()); //Create hyperling CreationHelper createHelper = sheet.getWorkbook().getCreationHelper(); CellStyle hlinkstyle = sheet.getWorkbook().createCellStyle(); Font hlinkfont = sheet.getWorkbook().createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); hlinkstyle.setFont(hlinkfont); Hyperlink link = (Hyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress("http://www.tutorialspoint.com/"); executionResult.setHyperlink((Hyperlink) link); executionResult.setCellStyle(hlinkstyle); } current++; } /*r.createCell(1).setCellValue(""); r.createCell(2).setCellValue(""); r.createCell(3).setCellValue(""); r.createCell(4).setCellValue(""); r.createCell(5).setCellValue(""); */ // for(TestCaseWithExecution exec : execution){ // // //r.createCell(2).setCellValue(exec.getDescription()); // //r.createCell(3).setCellValue(exec.getApplication()); // //r.createCell(4).setCellValue(exec.getEnvironment()); // //r.createCell(5).setCellValue(exec.getBrowser()); // int indexOfCountry = mapCountries.indexOf(exec.getCountry()) + 6; // r.createCell(indexOfCountry).setCellValue(exec.getControlStatus()); // //current++; // } //puts the test name in the first column /*r = sheet.getRow(currentIndex); r.getCell(0).setCellValue(test); */ /*CellRangeAddress range = new CellRangeAddress(currentIndex, lastRow, 0, 0); sheet.addMergedRegion(range);*/ return lastRow; }
From source file:org.netxilia.impexp.impl.ExcelExportService.java
License:Open Source License
@Override public void exportSheetTo(INetxiliaSystem workbookProcessor, SheetFullName sheetName, OutputStream out, IProcessingConsole console)/* ww w .ja va 2s .co m*/ throws ExportException, NetxiliaResourceException, NetxiliaBusinessException { Workbook poiWorkbook = new HSSFWorkbook(); Sheet poiSheet = poiWorkbook.createSheet(sheetName.getSheetName()); ISheet nxSheet = null; try { nxSheet = workbookProcessor.getWorkbook(sheetName.getWorkbookId()).getSheet(sheetName.getSheetName()); SheetData nxSheetData = nxSheet.receiveSheet().getNonBlocking(); for (AreaReference area : nxSheetData.getSpans()) { poiSheet.addMergedRegion(new CellRangeAddress(area.getFirstRowIndex(), area.getLastRowIndex(), area.getFirstColumnIndex(), area.getLastColumnIndex())); } // cells Matrix<CellData> nxCells = nxSheet.receiveCells(AreaReference.ALL).getNonBlocking(); int rowIndex = 0; for (List<CellData> nxRow : nxCells.getRows()) { Row poiRow = poiSheet.createRow(rowIndex); for (CellData nxCell : nxRow) { if (nxCell != null) { Cell poiCell = poiRow.createCell(nxCell.getReference().getColumnIndex()); try { copyCellValue(nxCell, poiCell); } catch (Exception ex) { if (console != null) { console.println("Error " + nxCell.getReference() + ":" + ex); } } } } rowIndex++; } // columns List<ColumnData> nxColumns = nxSheet.receiveColumns(Range.ALL).getNonBlocking(); for (int c = 0; c < nxColumns.size(); ++c) { ColumnData col = nxColumns.get(c); if (col.getWidth() > 0) { poiSheet.setColumnWidth(c, PoiUtils.pixel2WidthUnits(col.getWidth())); } PoiUtils.netxiliaStyle2Poi(col.getStyles(), poiSheet.getWorkbook(), poiSheet.getColumnStyle(c)); } } catch (StorageException e) { throw new ExportException(e); } // close the workbook try { poiWorkbook.write(out); } catch (IOException e) { throw new ExportException(e); } }
From source file:org.netxilia.impexp.impl.ExcelImportService.java
License:Open Source License
@Override public List<SheetFullName> importSheets(INetxiliaSystem workbookProcessor, WorkbookId workbookName, InputStream is, IProcessingConsole console) throws ImportException { List<SheetFullName> sheetNames = new ArrayList<SheetFullName>(); try {//w w w . ja v a 2 s. c om log.info("Starting import:" + workbookName); Workbook poiWorkbook = new HSSFWorkbook(is); IWorkbook nxWorkbook = workbookProcessor.getWorkbook(workbookName); log.info("Read POI"); NetxiliaStyleResolver styleResolver = new NetxiliaStyleResolver( styleService.getStyleDefinitions(workbookName)); HSSFPalette palette = ((HSSFWorkbook) poiWorkbook).getCustomPalette(); for (int s = 0; s < poiWorkbook.getNumberOfSheets(); ++s) { Sheet poiSheet = poiWorkbook.getSheetAt(s); SheetFullName sheetName = new SheetFullName(workbookName, getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName())); ISheet nxSheet = null; BlockCellCommandBuilder cellCommandBuilder = new BlockCellCommandBuilder(); try { List<CellReference> refreshCells = new ArrayList<CellReference>(); for (Row poiRow : poiSheet) { if (poiRow.getRowNum() % 100 == 0) { log.info("importing row #" + poiRow.getRowNum()); } for (Cell poiCell : poiRow) { if (nxSheet == null) { // lazy creation while (true) { try { nxSheet = nxWorkbook.addNewSheet(sheetName.getSheetName(), SheetType.normal); nxSheet.setRefreshEnabled(false); break; } catch (AlreadyExistsException e) { // may happen is simultaneous imports take place sheetName = new SheetFullName(workbookName, getNextFreeSheetName(nxWorkbook, poiSheet.getSheetName())); } } } CellReference ref = new CellReference(sheetName.getSheetName(), poiRow.getRowNum(), poiCell.getColumnIndex()); try { ICellCommand cmd = copyCell(poiCell, ref, palette, styleResolver); if (cmd != null) { cellCommandBuilder.command(cmd); } if (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA) { refreshCells.add(ref); } } catch (Exception e) { if (console != null) { console.println("Could import cell " + ref + ":" + poiCell + ":" + e); } log.error("Could import cell " + ref + ":" + poiCell + ":" + e, e); } } if (poiRow.getRowNum() % 100 == 0 && !cellCommandBuilder.isEmpty()) { nxSheet.sendCommandNoUndo(cellCommandBuilder.build()); cellCommandBuilder = new BlockCellCommandBuilder(); } } if (nxSheet == null) { // empty sheet continue; } if (!cellCommandBuilder.isEmpty()) { nxSheet.sendCommandNoUndo(cellCommandBuilder.build()); } // add the columns after as is not very clear how to get the number of cols in poi for (int c = 0; c < nxSheet.getDimensions().getNonBlocking().getColumnCount(); ++c) { int width = 50; try { width = PoiUtils.widthUnits2Pixel(poiSheet.getColumnWidth(c)); nxSheet.sendCommand(ColumnCommands.width(Range.range(c), width)); } catch (NullPointerException ex) { // ignore it // NPE in at org.apache.poi.hssf.model.Sheet.getColumnWidth(Sheet.java:998) // defaultColumnWidth can be null !? } CellStyle poiStyle = poiSheet.getColumnStyle(c); if (poiStyle == null) { continue; } Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle, poiSheet.getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver); if (styles != null) { nxSheet.sendCommand(ColumnCommands.styles(Range.range(c), styles)); } } // merge List<AreaReference> spans = new ArrayList<AreaReference>(poiSheet.getNumMergedRegions()); for (int i = 0; i < poiSheet.getNumMergedRegions(); ++i) { CellRangeAddress poiSpan = poiSheet.getMergedRegion(i); spans.add(new AreaReference(sheetName.getSheetName(), poiSpan.getFirstRow(), poiSpan.getFirstColumn(), poiSpan.getLastRow(), poiSpan.getLastColumn())); } nxSheet.sendCommand(SheetCommands.spans(spans)); // refresh all the cells now nxSheet.setRefreshEnabled(true); nxSheet.sendCommandNoUndo(moreCellCommands.refresh(refreshCells, false)); } finally { if (nxSheet != null) { sheetNames.add(sheetName); } } } } catch (IOException e) { throw new ImportException(null, "Cannot open workbook:" + e, e); } catch (StorageException e) { throw new ImportException(null, "Error storing sheet:" + e, e); } catch (NotFoundException e) { throw new ImportException(null, "Cannot find workbook:" + e, e); } catch (NetxiliaResourceException e) { throw new ImportException(null, e.getMessage(), e); } catch (NetxiliaBusinessException e) { throw new ImportException(null, e.getMessage(), e); } return sheetNames; }