List of usage examples for org.apache.poi.ss.usermodel CellStyle getDataFormat
short getDataFormat();
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void setupStyle(Cell cell, int row) { CellStyle style = cell.getCellStyle(); // cell has its own style and not the default style if (reuseExistingStyles) { // we have to reuse the existing style if (reuseExistingStylesAlternating) { // we have to reuse the style from the even/odd row if (isFirstRow(row)) { // we are in the first row, memorize the style if (style.getIndex() > 0) { // only if the cell does not use the default style oddRowColumnStyleMap.put(cell.getColumnIndex(), style); }//w w w . j av a2s . c o m } else if (isSecondRow(row)) { // we are in the first row, memorize the style if (style.getIndex() > 0) { // only if the cell does not use the default style evenRowColumnStyleMap.put(cell.getColumnIndex(), style); } } else if (isEvenDataRow(row)) { // reference to the previously memorized style for even rows CellStyle s = evenRowColumnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } else { // reference to the previously memorized style for even rows CellStyle s = oddRowColumnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } } else { // we take the style from the last row if (isFirstRow(row)) { // memorize the style for reuse in all other rows if (style.getIndex() > 0) { // only if the cell does not use the default style columnStyleMap.put(cell.getColumnIndex(), style); } } else { // set the style from the previous row CellStyle s = columnStyleMap.get(cell.getColumnIndex()); if (s != null) { style = s; cell.setCellStyle(style); } } } } else { Short formatIndex = cellFormatMap.get(cell.getColumnIndex()); if (formatIndex != null) { if ((style.getIndex() == 0) || (style.getDataFormat() != formatIndex)) { // this is the default style or the current format differs from the given format // we need our own style for this style = columnStyleMap.get(cell.getColumnIndex()); if (style == null) { style = workbook.createCellStyle(); style.setDataFormat(formatIndex.shortValue()); columnStyleMap.put(cell.getColumnIndex(), style); } cell.setCellStyle(style); } } } }
From source file:gov.nih.nci.evs.app.neopl.XLStoXLSX.java
License:Open Source License
/** * @param args/*from w w w . j av a 2 s. c om*/ * @throws InvalidFormatException * @throws IOException */ public static void run(String inputfile, String outputfile) throws IOException { InputStream in = new BufferedInputStream(new FileInputStream(inputfile)); try { Workbook wbIn = new HSSFWorkbook(in); File outFn = new File(outputfile); if (outFn.exists()) { outFn.delete(); } Workbook wbOut = new XSSFWorkbook(); int sheetCnt = wbIn.getNumberOfSheets(); for (int i = 0; i < sheetCnt; i++) { Sheet sIn = wbIn.getSheetAt(0); Sheet sOut = wbOut.createSheet(sIn.getSheetName()); Iterator<Row> rowIt = sIn.rowIterator(); while (rowIt.hasNext()) { Row rowIn = rowIt.next(); Row rowOut = sOut.createRow(rowIn.getRowNum()); Iterator<Cell> cellIt = rowIn.cellIterator(); while (cellIt.hasNext()) { Cell cellIn = cellIt.next(); Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType()); switch (cellIn.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: cellOut.setCellValue(cellIn.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: cellOut.setCellValue(cellIn.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellOut.setCellFormula(cellIn.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: cellOut.setCellValue(cellIn.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cellOut.setCellValue(cellIn.getStringCellValue()); break; } { CellStyle styleIn = cellIn.getCellStyle(); CellStyle styleOut = cellOut.getCellStyle(); styleOut.setDataFormat(styleIn.getDataFormat()); } cellOut.setCellComment(cellIn.getCellComment()); // HSSFCellStyle cannot be cast to XSSFCellStyle // cellOut.setCellStyle(cellIn.getCellStyle()); } } } OutputStream out = new BufferedOutputStream(new FileOutputStream(outFn)); try { wbOut.write(out); } finally { out.close(); } } finally { in.close(); } }
From source file:midas.sheeco.type.adapter.SpreadsheetBooleanAdapterTest.java
License:Apache License
@Test(expected = InvalidCellValueException.class) public void testInvalidNumeric() { SpreadsheetBooleanAdapter adapter = new SpreadsheetBooleanAdapter(); CellStyle style = mock(CellStyle.class); when(style.getDataFormat()).thenReturn((short) 0x0e); Cell cell = mock(Cell.class); when(cell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC); when(cell.getDateCellValue()).thenReturn(new Date(111111)); when(cell.getCellStyle()).thenReturn(style); adapter.fromSpreadsheet(cell);//from w w w . j a v a2 s.com }
From source file:midas.sheeco.type.adapter.SpreadsheetDateAdapterTest.java
License:Apache License
@Test public void testNumericTypeDate() { Date expected = new Date(111111); CellStyle style = mock(CellStyle.class); when(style.getDataFormat()).thenReturn((short) 0x0e); Cell cell = mock(Cell.class); when(cell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC); when(cell.getDateCellValue()).thenReturn(expected); when(cell.getCellStyle()).thenReturn(style); Date value = adapter.fromSpreadsheet(cell); Assert.assertEquals(expected, value); }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * DateUtil?Localize??(,,?)?????????// w ww . java 2 s. c om * ?""???????? * DateUtil???????? * Bug 47071???? * * @param cell */ public static boolean isCellDateFormatted(Cell cell) { if (cell == null) { return false; } boolean bDate = false; double d = cell.getNumericCellValue(); if (DateUtil.isValidExcelDate(d)) { CellStyle style = cell.getCellStyle(); if (style == null) { return false; } int i = style.getDataFormat(); String fs = style.getDataFormatString(); if (fs != null) { // And '"any"' into '' while (fs.contains("\"")) { int beginIdx = fs.indexOf("\""); if (beginIdx == -1) { break; } int endIdx = fs.indexOf("\"", beginIdx + 1); if (endIdx == -1) { break; } fs = fs.replaceFirst(Pattern.quote(fs.substring(beginIdx, endIdx + 1)), ""); } } bDate = DateUtil.isADateFormat(i, fs); } return bDate; }
From source file:org.dbflute.helper.io.xls.DfTableXlsReader.java
License:Apache License
protected boolean isCellBase64Formatted(Cell cell) { final CellStyle cs = cell.getCellStyle(); final short dfNum = cs.getDataFormat(); return DfDataSetConstants.BASE64_FORMAT.equals(_dataFormat.getFormat(dfNum)); }
From source file:org.dbflute.helper.io.xls.DfTableXlsReader.java
License:Apache License
protected boolean isCellDateFormatted(Cell cell) { final CellStyle cs = cell.getCellStyle(); final short dfNum = cs.getDataFormat(); final String format = _dataFormat.getFormat(dfNum); if (format == null || format.length() == 0) { return false; }//from w w w . j a v a 2s . co m if (format.indexOf('/') > 0 || format.indexOf('y') > 0 || format.indexOf('m') > 0 || format.indexOf('d') > 0) { return true; } return false; }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_StyleFormatTest.java
License:Apache License
/** * Test applying Format and Style from cell (from a template) when writing fields * * @param fileType/* w w w . j a v a 2 s.c o m*/ * @throws Exception */ private void testStyleFormat(String fileType) throws Exception { setupStepMock(fileType); createStepMeta(fileType); createStepData(fileType); step.init(stepMeta, stepData); // We do not run transformation or executing the whole step // instead we just execute ExcelWriterStepData.writeNextLine() to write to Excel workbook object // Values are written in A2:D2 and A3:D3 rows List<Object[]> rows = createRowData(); for (int i = 0; i < rows.size(); i++) { step.writeNextLine(rows.get(i)); } // Custom styles are loaded from G1 cell Row xlsRow = stepData.sheet.getRow(0); Cell baseCell = xlsRow.getCell(6); CellStyle baseCellStyle = baseCell.getCellStyle(); DataFormat format = stepData.wb.createDataFormat(); // Check style of the exported values in A3:D3 xlsRow = stepData.sheet.getRow(2); for (int i = 0; i < stepData.inputRowMeta.size(); i++) { Cell cell = xlsRow.getCell(i); CellStyle cellStyle = cell.getCellStyle(); if (i > 0) { assertEquals(cellStyle.getBorderRight(), baseCellStyle.getBorderRight()); assertEquals(cellStyle.getFillPattern(), baseCellStyle.getFillPattern()); } else { // cell A2/A3 has no custom style assertFalse(cellStyle.getBorderRight() == baseCellStyle.getBorderRight()); assertFalse(cellStyle.getFillPattern() == baseCellStyle.getFillPattern()); } if (i != 1) { assertEquals(format.getFormat(cellStyle.getDataFormat()), "0.00000"); } else { // cell B2/B3 use different format from the custom style assertEquals(format.getFormat(cellStyle.getDataFormat()), "##0,000.0"); } } }
From source file:org.pentaho.reporting.ui.datasources.table.ImportFromFileTask.java
License:Open Source License
private void importFromFile(final File file, final boolean firstRowIsHeader) { final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length())); try {/*w w w .j a va2s . com*/ final InputStream fin = new FileInputStream(file); try { IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout); } finally { fin.close(); } if (Thread.currentThread().isInterrupted()) { return; } final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray())); int sheetIndex = 0; if (workbook.getNumberOfSheets() > 1) { final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent); if (selectorDialog.performSelection()) { sheetIndex = selectorDialog.getSelectedIndex(); } else { return; } } final TypedTableModel tableModel = new TypedTableModel(); final Sheet sheet = workbook.getSheetAt(sheetIndex); final Iterator rowIterator = sheet.rowIterator(); if (firstRowIsHeader) { if (rowIterator.hasNext()) { final Row headerRow = (Row) rowIterator.next(); final short cellCount = headerRow.getLastCellNum(); for (short colIdx = 0; colIdx < cellCount; colIdx++) { final Cell cell = headerRow.getCell(colIdx); if (cell != null) { while (colIdx > tableModel.getColumnCount()) { tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(tableModel.getColumnCount())), Object.class); } final RichTextString string = cell.getRichStringCellValue(); if (string != null) { tableModel.addColumn(string.getString(), Object.class); } else { tableModel.addColumn( Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)), Object.class); } } } } } Object[] rowData = null; while (rowIterator.hasNext()) { final Row row = (Row) rowIterator.next(); final short cellCount = row.getLastCellNum(); if (cellCount == -1) { continue; } if (rowData == null || rowData.length != cellCount) { rowData = new Object[cellCount]; } for (short colIdx = 0; colIdx < cellCount; colIdx++) { final Cell cell = row.getCell(colIdx); final Object value; if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { final RichTextString string = cell.getRichStringCellValue(); if (string != null) { value = string.getString(); } else { value = null; } } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { final CellStyle hssfCellStyle = cell.getCellStyle(); final short dataFormat = hssfCellStyle.getDataFormat(); final String dataFormatString = hssfCellStyle.getDataFormatString(); if (isDateFormat(dataFormat, dataFormatString)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { value = cell.getBooleanCellValue(); } else { value = cell.getStringCellValue(); } } else { value = null; } if (value != null && "".equals(value) == false) { while (colIdx >= tableModel.getColumnCount()) { tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column", String.valueOf(tableModel.getColumnCount())), Object.class); } } rowData[colIdx] = value; } if (Thread.currentThread().isInterrupted()) { return; } tableModel.addRow(rowData); } final int colCount = tableModel.getColumnCount(); final int rowCount = tableModel.getRowCount(); for (int col = 0; col < colCount; col++) { Class type = null; for (int row = 0; row < rowCount; row += 1) { final Object value = tableModel.getValueAt(row, col); if (value == null) { continue; } if (type == null) { type = value.getClass(); } else if (type != Object.class) { if (type.isInstance(value) == false) { type = Object.class; } } } if (Thread.currentThread().isInterrupted()) { return; } if (type != null) { tableModel.setColumnType(col, type); } } parent.importComplete(tableModel); } catch (Exception e) { parent.importFailed(e); logger.error("Failed to import spreadsheet", e); // NON-NLS } }
From source file:org.tiefaces.components.websheet.utility.CellStyleUtility.java
License:MIT License
/** * Gets the input type from cell type./*from w w w.j a v a2 s. co m*/ * * @param cell * the cell * @return the input type from cell type */ @SuppressWarnings("deprecation") private static String getInputTypeFromCellType(final Cell cell) { String inputType = TieConstants.CELL_INPUT_TYPE_TEXT; if (cell.getCellTypeEnum() == CellType.NUMERIC) { inputType = TieConstants.CELL_INPUT_TYPE_DOUBLE; } CellStyle style = cell.getCellStyle(); if (style != null) { int formatIndex = style.getDataFormat(); String formatString = style.getDataFormatString(); if (DateUtil.isADateFormat(formatIndex, formatString)) { inputType = TieConstants.CELL_INPUT_TYPE_DATE; } else { if (isAPercentageCell(formatString)) { inputType = TieConstants.CELL_INPUT_TYPE_PERCENTAGE; } } } return inputType; }