private Object getCellValue(Cell cell, FormulaEvaluator evaluator) throws ExcelManipulateException { if (cell == null) return null; // log.debug("Read Value for: " + ExcelUtil.getCellIndex(cell.getRowIndex(), // cell.getColumnIndex())); Object value = null; CellValue cellValue = evaluator.evaluate(cell); if (cellValue == null) { logger.debug("{}: null", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex())); return null; } switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: break; case Cell.CELL_TYPE_BOOLEAN: value = cellValue.getBooleanValue(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = DateUtil.getJavaDate(cellValue.getNumberValue()); } else value = cellValue.getNumberValue(); break; case Cell.CELL_TYPE_STRING: value = cellValue.getStringValue(); } logger.debug( "{}: {}", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()), value); return value; }
@Override public void close() { try { int nbS = wb.getNumberOfSheets(); for (int i = 0; i < nbS; i++) { Sheet s = wb.getSheetAt(i); if (s.getRow(0) != null) { // ceci arrive si on vide la mémoire tampon pour les grands fichiers. Dans ce // cas pas de possibilité de traiter la mise en page de gros fichiers for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) { Cell c = s.getRow(0).getCell(j); c.getSheet().autoSizeColumn(c.getColumnIndex()); } Cell firstCell = s.getRow(0).getCell(0); Cell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1); s.setAutoFilter( new CellRangeAddress( firstCell.getRowIndex(), lastCell.getRowIndex(), lastCell.getRowIndex(), lastCell.getColumnIndex())); } } wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
public void printWorksheetContent() throws Exception { FrontDesk desk = FrontDesk.getInstance(); // read policy & rule DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath)); @SuppressWarnings("unchecked") DynamicInspectionRequest<Workbook> req = (DynamicInspectionRequest<Workbook>) desk.requestNewDynamicInspection("fubar", new File(filepath)); FrontDesk.getInstance().scan(); FrontDesk.getInstance().register(policy); FrontDesk.getInstance().setPolicy(policy); DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0"); assertTrue("No rule with name \"greaterThan0\" was found", rule != null); // write TestInput in PoiWorkbook POIWriter writer = new POIWriter(); writer.insertTestInput(rule, req.getExternalSpreadsheet()); Workbook wb = req.getExternalSpreadsheet(); Sheet sheet1 = wb.getSheetAt(0); for (Row row : sheet1) { for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); System.out.print(row.getRowNum() + ", " + cell.getColumnIndex()); System.out.print(" - "); System.out.print(cellRef.formatAsString()); System.out.print(" - "); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.println(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: System.out.println(); } } } }
public List<String> getCellConstraints(Cell cell) { for (DataValidation _validation : getValidations()) for (CellRangeAddress _region : _validation.getRegions().getCellRangeAddresses()) if (_region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) { if (_validation.getValidationConstraint().getExplicitListValues() != null) return Arrays.asList(_validation.getValidationConstraint().getExplicitListValues()); else if (_validation.getValidationConstraint().getFormula1() != null) { String formula = _validation.getValidationConstraint().getFormula1().split("\"")[1]; String[] _names = formula.split("!"); String _sheetName = _names[0]; String _arrName = _names[1]; int sheetIndex = evalWorkbook.getSheetIndex(_sheetName); EvaluationName nm = evalWorkbook.getName(_arrName, sheetIndex); if (nm == null || !nm.isRange()) { throw new RuntimeException( "Specified name '" + _arrName + "' is not a range as expected."); } OperationEvaluationContext ec = new OperationEvaluationContext( new WorkbookEvaluator(evalWorkbook, null, null), evalWorkbook, defaultSheet, cell.getRowIndex(), cell.getColumnIndex(), null); Ptg[] ptgs = nm.getNameDefinition(); if (ptgs.length == 1 && ptgs[0] instanceof Area3DPtg) { ValueEval result = ec.getArea3DEval((Area3DPtg) ptgs[0]); if (result instanceof AreaEvalBase) { AreaEvalBase _area = (AreaEvalBase) result; ArrayList<String> resultStrings = new ArrayList<>(); for (int i = _area.getFirstRow(); i <= _area.getLastRow(); i++) { String value = getStringValue( new CellCoord(_area.getFirstSheetIndex(), _area.getFirstColumn(), i)); if (value != null && value.length() > 0) resultStrings.add(value); } return resultStrings; } return null; } } } return null; }
public List<Specialite> readBooksFromExcelFile(String excelFilePath) throws IOException { List<Specialite> listSpecialite = new ArrayList<>(); FileInputStream file = new FileInputStream(new File(excelFilePath)); // Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); // Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); Specialite sp = new Specialite(); Admin admin = new Admin(); while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); switch (columnIndex) { case 0: sp.setIntitule((String) getCellValue(nextCell)); break; } } listSpecialite.add(sp); } file.close(); return listSpecialite; }
private boolean isMerged(Cell cell) { if (cell == null) return false; for (CellRangeAddress range : mergedRegions) { if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) return true; } return false; }
public static void generateTrainingDataFromFile( String fileLocation) // Requires that the original file had the metadata and requires that this // file is formated the same in first sheet { testDataLL = (LinkedList<String[]>) dataLL.clone(); actualClassifications = (LinkedList<String>) classificationsLL.clone(); FileInputStream file; try { file = new FileInputStream(new File(fileLocation)); Workbook excelFile = new HSSFWorkbook(file); Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet for (Row row : sheet1) { String data[] = new String[row.getPhysicalNumberOfCells() - 1]; String classification = ""; int offset = 0; // Used so that we can declare an array of the size of the attributes without the // classification for (Cell cell : row) { int index = cell.getColumnIndex(); if (classificationLocation != index) { data[index - offset] = cell.toString(); } else { classification = cell.toString(); offset++; } } // Even though data and classifications are not really used add it onto the end so it is // still complete for in the event they end up being used in a later version dataLL.add(data); classificationsLL.add(classification); trainingDataLL.add(data); knownClassifications.add(classification); // Check to see if we have seen that classification yet int occurrences = 0; for (int i = 0; i < classificationTypes.size() && occurrences == 0; i++) { if (classificationTypes.get(i).compareTo(classification) == 0) { occurrences = 1; } } if (occurrences == 0) { classificationTypes.add(classification); } } excelFile.close(); } catch (FileNotFoundException e) { System.out.println("Error file not found"); System.exit(0); } catch (IOException e) { System.out.println("Unable to read file, disk drive may be failing"); e.printStackTrace(); System.exit(0); } }
/** * Extract a couple of data elements for easy reference, such as row and column * * @param sheetName * @param cell */ public SpreadsheetItem(String sheetName, Cell cell) { super(); this.sheetName = sheetName; this.spreadsheetCell = cell; column = spreadsheetCell.getColumnIndex(); row = spreadsheetCell.getRowIndex(); this.cellIdentifier = new CellIdentifier(this.sheetName, spreadsheetCell.getRowIndex(), column); }
private String[] rowToStringArray(Row row) { stringCache.clear(); for (Cell cell : CollectionUtils.asIterable(row.iterator())) { while (stringCache.size() < cell.getColumnIndex()) stringCache.add(""); // handle empty cells stringCache.add(formatter.formatCellValue(cell, evaluator)); } return stringCache.toArray(new String[stringCache.size()]); }
private void analyzeTable(XSSFRow row) { Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); // System.out.println(cell.getStringCellValue()); switch (readCells(cell)) { case "Name ": NAME_COLUMN = cell.getColumnIndex(); break; case "Value ": VALUE_COLUMN = cell.getColumnIndex(); break; case "Modification Status ": STATUS_COLUMN = cell.getColumnIndex(); break; } } }
/** * Load the Data from Excel spreadsheet.It uses Apache POI classes to load the data. * * @param excelFile the excel file input stream to load the data from * @return the loaded data. * @throws IOException if an exception occurs while loading the data */ private Map<String, List<Map<String, Object>>> loadFromSpreadsheet(final InputStream excelFile) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(excelFile); data = new HashMap<String, List<Map<String, Object>>>(); Sheet sheet = workbook.getSheetAt(0); LOG.debug("Sheet {} is being read", sheet); Map<String, List<Map<String, Object>>> finalData = new HashMap<String, List<Map<String, Object>>>(); Map<Integer, Object> tempData = new HashMap<Integer, Object>(); List<Map<String, Object>> dataValues = null; LinkedHashMap<String, Object> actualData = new LinkedHashMap<String, Object>(); for (Row row : sheet) { boolean keyRow = isKeyRow(row, workbook); actualData = initializeRowData(row, workbook, actualData); // Map<String, Object> actualData = new LinkedHashMap<String, Object>(); StringBuilder debugInfo = new StringBuilder("Row data being read is "); for (Cell cell : row) { Object cellData = objectFrom(workbook, cell); debugInfo.append(":" + cellData); if ((cell.getColumnIndex() == 0) && keyRow) { // Indicates that this is a new set of test data. dataValues = new ArrayList<Map<String, Object>>(); finalData.put(cellData.toString().trim(), dataValues); } else { if (keyRow) { tempData.put(cell.getColumnIndex(), cellData); } else { if (tempData.get(cell.getColumnIndex()) != null) { actualData.put(tempData.get(cell.getColumnIndex()).toString(), cellData); } } } } LOG.debug(debugInfo.toString()); if (!keyRow) { dataValues.add(actualData); } } return finalData; }
private static List<Product> readProductFile(String filePath) { List<Product> productList = new ArrayList<Product>(); try { FileInputStream file = new FileInputStream(FILE_PATH); // Use XSSF for xlsx format, for xls use HSSF Workbook workbook = new HSSFWorkbook(file); int numberOfSheets = workbook.getNumberOfSheets(); System.out.println("Number of sheets in the workbook : " + numberOfSheets); String productType = null; Sheet firstSheet = workbook.getSheetAt(0); for (Row myRow : firstSheet) { Product product = new Product(); Boolean skipProduct = Boolean.FALSE; for (Cell myCell : myRow) { switch (myCell.getColumnIndex()) { case 0: product.setProductName(myCell.getRichStringCellValue().getString().trim()); break; case 1: product.setSellingRate(myCell.getNumericCellValue()); break; case 2: product.setCostRate(myCell.getNumericCellValue()); break; case 3: if (myCell.getCellType() == Cell.CELL_TYPE_NUMERIC) product.setProductCode(String.valueOf((int) myCell.getNumericCellValue())); else product.setProductCode(myCell.getStringCellValue().trim()); break; case 4: if (myCell.getRichStringCellValue().getString() != null) { productType = myCell.getRichStringCellValue().getString().trim(); skipProduct = Boolean.TRUE; } else skipProduct = Boolean.FALSE; break; } } if (!skipProduct) { product.setProductType(productType); productList.add(product); } } file.close(); } catch (Exception ex) { ex.printStackTrace(); } return productList; }
private int findIndexCellByName(String name, Row row) { Iterator<Cell> iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); if (getCellValue(cell).trim().equalsIgnoreCase(name)) { return cell.getColumnIndex(); } } return -1; }
/** * Remove the Cell from this row. * * @param cell the cell to remove */ public void removeCell(Cell cell) { if (cell.getRow() != this) { throw new IllegalArgumentException("Specified cell does not belong to this row"); } XSSFCell xcell = (XSSFCell) cell; if (xcell.isPartOfArrayFormulaGroup()) { xcell.notifyArrayFormulaChanging(); } _cells.remove(cell.getColumnIndex()); }
/** * 判断excel标题与对象的标签标题是否一致 * * @param titleRow 开始行 * @param clz 对象 * @return 判断类型 */ private Map<Integer, String> getHeaderMap(Row titleRow, Class clz) { List<ExcelHeader> headers = getHeaderList(clz); // 取后台标题 Map<Integer, String> maps = new HashMap<Integer, String>(); for (Cell c : titleRow) { String title = c.getStringCellValue(); // 取excel的标题栏 for (ExcelHeader eh : headers) { if (eh.getTitle().equals(title.trim())) { // 相等则设定对应的字段顺序与方法名 maps.put(c.getColumnIndex(), eh.getMethodName().replace("get", "set")); break; } } } return maps; }
public static Object getCellValue(Cell cell) { Object value = new Object(); // Prevent a NullPointerException if (cell != null) { if (cell.getHyperlink() != null) { Workbook workbook = new XSSFWorkbook(); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Hyperlink link = cell.getHyperlink(); String address = link.getAddress(); if (logger.isTraceEnabled()) { logger.trace( "Found a Hyperlink to " + cell.getHyperlink().getAddress() + " in cell " + cell.getRowIndex() + "," + cell.getColumnIndex()); } cell = evaluator.evaluateInCell(cell); } // Depending on the cell type, the value is read using Apache POI methods switch (cell.getCellType()) { // String are easy to handle case Cell.CELL_TYPE_STRING: logger.trace("Found string " + cell.getStringCellValue()); value = cell.getStringCellValue(); break; // Since date formatted cells are also of the numeric type, this needs to be processed case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); DateFormat df = SimpleDateFormat.getDateInstance(); logger.trace("Found date " + df.format(date)); value = date; } else { logger.trace("Found general number " + cell.getNumericCellValue()); value = cell.getNumericCellValue(); } break; } } else { logger.trace("Found cell with NULL value"); } return value; }
private void fillRowWith(Row r, String[] data) { Cell c = null; for (String s : data) { if (c == null) c = r.createCell(0); else c = r.createCell(c.getColumnIndex() + 1); try { double d = Double.valueOf(s); c.setCellValue(d); } catch (NumberFormatException e) { c.setCellValue(s); } } }
@Test public void thatBuildCellReturnsCellWithCorrectTypeIndexAndValue() throws Exception { String value = "test value"; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("test sheet"); Row row = sheet.createRow(0); Cell cell = posreadyFieldsBuilder.buildStringCell(row, 0, value); assertEquals(0, cell.getColumnIndex()); assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); assertEquals(value, cell.getStringCellValue()); }
/** 通过cell得到A3 B6 */ public String getRowColmunNumByCell(Cell cell) { if (cell == null) return ""; String res = ""; String alpha = "abcdefghijklmnopqrstuvwxyz"; int row = cell.getRowIndex(); int col = cell.getColumnIndex() + 1; while (true) { int a = col % 26; if (a == 0) { a = 26; } res = alpha.charAt(a - 1) + res; col = (col - a) / 26; if (col == 0) break; } res += row; return res.toUpperCase(); }
public List<Object> readExcel(Workbook wb, Class clz, int readLine, int tailLine) { Sheet sheet = wb.getSheetAt(0); // 取第一张表 List<Object> objs = null; try { Row row = sheet.getRow(readLine); // 开始行,主题栏 objs = new ArrayList<Object>(); Map<Integer, String> maps = getHeaderMap(row, clz); // 设定对应的字段顺序与方法名 if (maps == null || maps.size() <= 0) throw new RuntimeException("要读取的Excel的格式不正确,检查是否设定了合适的行"); // 与order顺序不符 for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) { // 取数据 row = sheet.getRow(i); Object obj = clz.newInstance(); // 调用无参结构 for (Cell c : row) { int ci = c.getColumnIndex(); String mn = maps.get(ci).substring(3); // 消除get mn = mn.substring(0, 1).toLowerCase() + mn.substring(1); Map<String, Object> params = new HashMap<String, Object>(); if (!"enterDate".equals(mn)) c.setCellType(Cell.CELL_TYPE_STRING); // 设置单元格格式 else c.setCellType(Cell.CELL_TYPE_NUMERIC); if (this.getCellValue(c).trim().equals("是")) { BeanUtils.copyProperty(obj, mn, 1); } else if (this.getCellValue(c).trim().equals("否")) { BeanUtils.copyProperty(obj, mn, 0); } else BeanUtils.copyProperty(obj, mn, this.getCellValue(c)); } objs.add(obj); } } catch (InstantiationException e) { e.printStackTrace(); logger.error(e); } catch (IllegalAccessException e) { e.printStackTrace(); logger.error(e); } catch (InvocationTargetException e) { e.printStackTrace(); logger.error(e); } catch (NumberFormatException e) { e.printStackTrace(); logger.error(e); } return objs; }
@Override public List<Object> getRowValues(int rowIndex) { Row row = dataSheet.getRow(rowIndex); ArrayList<Object> values = new ArrayList<>(); if (row == null) return values; int nullCount = 0; int colCount = row.getLastCellNum(); for (int col = 0; col < colCount; col++) { Cell cell = row.getCell(col); // treat rows with merged cells as "empty" if (isMerged(cell)) { LogMgr.logDebug( "ExcelReader.getRowValues()", dataSheet.getSheetName() + ": column:" + cell.getColumnIndex() + ", row:" + cell.getRowIndex() + " is merged. Ignoring row!"); return Collections.emptyList(); } Object value = getCellValue(cell); if (value == null) { nullCount++; } values.add(value); } if (nullCount == values.size()) { // return an empty list if all columns are null values.clear(); } return values; }
/** * {@inheritDoc} * * @see * org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#getCellValue(org.apache.poi.ss.usermodel.Cell, * org.eclipse.emf.ecore.EStructuralFeature) */ @Override public Object getCellValue(Cell cell, EStructuralFeature eStructuralFeature) throws EMFFormsConverterException { String string; try { string = cell.getStringCellValue(); } catch (final IllegalStateException e) { throw new EMFFormsConverterException( String.format( "Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$ cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()), e); } if (string == null || string.length() == 0) { return Collections.emptyList(); } final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature); final EDataType eDataType = eAttribute.getEAttributeType(); if (isDecimalNumber(eDataType.getInstanceClass())) { string = string.replace( DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator(), '.'); } final List<Object> result = new ArrayList<Object>(); final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance(); for (final String element : string.split(SEPARATOR)) { try { result.add(eFactory.createFromString(eDataType, element)); } // BEGIN SUPRESS CATCH EXCEPTION catch (final RuntimeException ex) { // END SUPRESS CATCH EXCEPTION throw new EMFFormsConverterException( MessageFormat.format( "The cell value {0} could not converted to a model value.", string)); // $NON-NLS-1$ } } return result; }
@Override public List<Entity> parse(File file) throws Exception { List<Entity> retVal = new ArrayList<Entity>(); Workbook wb = null; FileInputStream fileIn = new FileInputStream(file); try { wb = WorkbookFactory.create(fileIn); Map<String, Integer> headers = new HashMap<String, Integer>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); RowDataAccessor dataAccessor = new RowDataAccessor(); for (Row row : sheet) { if (row.getRowNum() == sheet.getFirstRowNum()) { for (Cell cell : row) { headers.put(cell.getStringCellValue(), cell.getColumnIndex()); } dataAccessor.setHeaders(headers); } else { dataAccessor.setDataRow(row); Entity entity = Parser.EntityFiller.fillEntity(dataAccessor, dbHelper); if (entity != null) { retVal.add(entity); } } } } } catch (Exception ex) { throw ex; } finally { fileIn.close(); } return retVal; }
private LinkedHashMap<String, Object> initializeRowData( Row row, HSSFWorkbook workbook, LinkedHashMap<String, Object> actualData) { LinkedHashMap<String, Object> result = new LinkedHashMap<String, Object>(); if (isKeyRow(row, workbook)) { // reset the actualData actualData = new LinkedHashMap<String, Object>(); for (Cell cell : row) { if (cell.getColumnIndex() != 0) { Object cellData = objectFrom(workbook, cell); if (cellData != null) actualData.put(cellData.toString(), null); } } } else { // not a key row, so reinitialize each key to null for (String key : actualData.keySet()) { result.put(key, null); } return result; } return actualData; }
// Setup column data here private void setValue(Cell cell, ConfirmationRecord data) { switch (cell.getColumnIndex()) { case 0: cell.setCellValue(cell.getRowIndex() - 4); break; case 1: cell.setCellValue(DateUtil.convDateToString("yyyy", data.getApplicationDate())); break; case 2: cell.setCellValue(DateUtil.convDateToString("M", data.getApplicationDate())); break; case 3: cell.setCellValue(DateUtil.convDateToString("d", data.getApplicationDate())); break; case 4: cell.setCellValue(DateUtil.convDateToString("yyyy", data.getSettleDate())); break; case 5: cell.setCellValue(DateUtil.convDateToString("M", data.getSettleDate())); break; case 6: cell.setCellValue(DateUtil.convDateToString("d", data.getSettleDate())); break; case 7: cell.setCellValue(data.getPolicyNo()); break; case 8: cell.setCellValue(data.getPlanCode()); break; case 9: cell.setCellValue(data.getInsuredName()); break; case 10: cell.setCellValue(data.getModalPremium().doubleValue()); break; case 11: cell.setCellValue(data.getApe().doubleValue()); break; case 12: cell.setCellValue(data.getSumInsured().doubleValue()); break; case 13: cell.setCellValue(data.getMode()); break; case 14: cell.setCellValue(data.getStatus()); break; case 15: cell.setCellValue(data.getUnitCode()); break; case 16: cell.setCellValue(data.getBankCard()); break; case 17: cell.setCellValue(data.getCreditCardNo()); break; case 18: cell.setCellValue(data.getExpiredDate()); break; case 19: cell.setCellValue( data.getBirthDate() != null ? DateUtil.convDateToString("yyyy", data.getBirthDate()) : null); break; case 20: cell.setCellValue( data.getBirthDate() != null ? DateUtil.convDateToString("M", data.getBirthDate()) : null); break; case 21: cell.setCellValue( data.getBirthDate() != null ? DateUtil.convDateToString("d", data.getBirthDate()) : null); break; case 22: cell.setCellValue(data.getCitizenId()); break; case 23: cell.setCellValue(data.getAddress1()); break; case 24: cell.setCellValue(data.getAddress2()); break; case 25: cell.setCellValue(data.getAddress3()); break; case 26: cell.setCellValue(data.getProvince()); break; case 27: cell.setCellValue(data.getPostCode()); break; case 28: cell.setCellValue(data.getTel1()); break; case 29: cell.setCellValue(data.getTel2()); break; case 30: cell.setCellValue(data.getTel3()); break; case 31: cell.setCellValue(data.getMobile1()); break; case 32: cell.setCellValue(data.getMobile2()); break; case 33: cell.setCellValue(data.getTsrName()); break; case 34: cell.setCellValue(data.getAction() != null ? data.getAction().getParamValue() : null); break; case 35: cell.setCellValue(data.getRemark()); break; default: break; } }
public String process2xml() { String result = ""; // String sourcefilenametag = config.getString("conversion.tags.sourcefilename"); String sourcefilename = file.getAbsolutePath(); try { sourcefilename = file.getCanonicalPath(); } catch (IOException ioe) { log.warn("Could not get cannonical path for file!", ioe); } String templatedir = config.getString("conversion.template.path"); // result += "<" + sourcefilenametag + "><![CDATA[" + file.toString() + "]]></" + // sourcefilenametag + ">"; try { Workbook input = WorkbookFactory.create(file); log.debug("Processing " + file.toString() + " as an Excel file."); FormulaEvaluator evaluator = input.getCreationHelper().createFormulaEvaluator(); String templateName = ""; STGroup g = new STRawGroupDir(templatedir, '$', '$'); // Go through each sheet for (int sheetno = 0; sheetno < input.getNumberOfSheets(); sheetno++) { Sheet sheet = input.getSheetAt(sheetno); log.debug("Processing sheet #" + sheetno + ": " + sheet.getSheetName()); LandmarkMatchList lml = new LandmarkMatchList(landmarks.size()); for (Row row : sheet) { // Go through each cell for (Cell cell : row) { String cellvalue = lml.getCellValue(cell, evaluator); if (!cellvalue.equals("")) { log.trace( "Cell value is: " + cellvalue + " [Row,Col]=[" + cell.getRowIndex() + "," + cell.getColumnIndex() + "]"); log.trace("Matching landmarks: " + landmarks.getLandmarksFor(cellvalue)); // Does Cell contents match a landmark? lml.addMatches(landmarks.getLandmarksFor(cellvalue), cell); } else { log.trace( "Cell value is blank. [Row,Col]=[" + cell.getRowIndex() + "," + cell.getColumnIndex() + "]"); } } } templateName = lml.getTemplateName(landmarks); if (!templateName.equals("")) { ST st = g.getInstanceOf(templateName); if (st != null) { // Set landmark name to value of cell given direction and distance Hashtable templateValues = lml.getCellTemplateValues( templateName, sheet, landmarks, evaluator, sourcefilename, sheetno); Enumeration templateValuesKeys = templateValues.keys(); while (templateValuesKeys.hasMoreElements()) { String key = (String) templateValuesKeys.nextElement(); st.add(key, (String) templateValues.get(key)); } ArrayList<String> sectionNames = lml.getSectionNamesForTemplate(templateName, landmarks); for (String sectionName : sectionNames) { ArrayList<Hashtable> sectionrows = lml.getSectionRows(templateName, sheet, landmarks, evaluator, sectionName); st.add(sectionName, sectionrows); } result += st.render(); } else { log.error( "Unable to load template " + templateName + ".st! Cannot render data to template while processing " + file.toString() + " sheet number " + sheetno); } } } } catch (IOException ioe) { log.error("Unable to open " + file.toString() + " as an Excel file.", ioe); } catch (InvalidFormatException ife) { log.error("Unable to open " + file.toString() + ". Format not recognized as Excel. ", ife); } catch (IllegalArgumentException iae) { log.error("Unable to open " + file.toString() + " as an Excel file.", iae); } catch (Exception e) { log.error("Unable to open " + file.toString() + " as an Excel file.", e); } // If cannot render, make sure don't reprocess unnecessarily if (result.equals("")) { result = "<Notemplatedata></Notemplatedata>"; } return result; }
private void readBOM(File fileItem) throws Exception { FileInputStream file = new FileInputStream(fileItem); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Connection con = Connect.getconnection(); PreparedStatement ps1 = con.prepareStatement("TRUNCATE TABLE bom"); ps1.executeUpdate(); Row nextRow = rowIterator.next(); while (rowIterator.hasNext()) { nextRow = rowIterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); PreparedStatement ps = con.prepareStatement( "INSERT INTO `star`.`bom` (`FGNo`, `Parent`, `Child`, `Qty`, `Decs`, `Unit`, `Mat_grp`,`Mat_grp_desc`,`Mat_sub_grp`,`Mat_sub_grp_desc`,`Stock`) VALUES(?,?,?,?,?,?,?,?,?,?,?)"); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == 0) { // FG ps.setString(1, cell.getStringCellValue()); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 1) { // Parent ps.setString(2, cell.getStringCellValue()); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 2) { // Child ps.setString(3, cell.getStringCellValue()); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 4) { // Decsription ps.setString(5, cell.getStringCellValue()); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 5) { // Unit ps.setString(6, cell.getStringCellValue()); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 6) { // Mat_grp ps.setString(7, cell.getStringCellValue()); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 7) { // Mat_grp_desc ps.setString(8, cell.getStringCellValue()); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 8) { // Mat_sub_grp ps.setString(9, cell.getStringCellValue()); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 9) { // Mat_sub_grp_desc ps.setString(10, cell.getStringCellValue()); // System.out.println(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_NUMERIC: if (cell.getColumnIndex() == 3) { // Quantity ps.setDouble(4, cell.getNumericCellValue()); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 10) { // Stock ps.setInt(11, (int) cell.getNumericCellValue()); // System.out.println(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_BLANK: if (cell.getColumnIndex() == 0) { // FG ps.setString(1, " "); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 1) { // Parent ps.setString(2, " "); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 2) { // Child ps.setString(3, " "); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 3) { // Quantity ps.setDouble(4, 0); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 4) { // Decsription ps.setString(5, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 5) { // Unit ps.setString(6, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 6) { // Mat_grp ps.setString(7, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 7) { // Mat_grp_desc ps.setString(8, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 8) { // Mat_sub_grp ps.setString(9, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 9) { // Mat_sub_grp_desc ps.setString(10, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 10) { // Stock ps.setInt(11, 0); // System.out.println(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_ERROR: if (cell.getColumnIndex() == 0) { // FG ps.setString(1, " "); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 1) { // Parent ps.setString(2, " "); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 2) { // Child ps.setString(3, " "); // System.out.println(cell.getStringCellValue()); break; } else if (cell.getColumnIndex() == 3) { // Quantity ps.setDouble(4, 0); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 4) { // Decsription ps.setString(5, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 5) { // Unit ps.setString(6, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 6) { // Mat_grp ps.setString(7, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 7) { // Mat_grp_desc ps.setString(8, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 8) { // Mat_sub_grp ps.setString(9, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 9) { // Mat_sub_grp_desc ps.setString(10, " "); // System.out.println(cell.getNumericCellValue()); break; } else if (cell.getColumnIndex() == 10) { // Stock ps.setInt(11, 0); // System.out.println(cell.getNumericCellValue()); break; } } // out.print("  "); } ps.executeUpdate(); // out.println("<br>"); } workbook.close(); file.close(); con.close(); }
/** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub String filepath = null; String id = null, name = null, email = null, mobile = null, doj = null, password = null, man1 = null, man2 = null, check = "success"; int level = 0, layer = 0; /* filepath=request.getParameter("file"); */ String message = ""; // out.print("<html><body><h3>"+filepath+"</h3></body></html>"); boolean isMultiPart = ServletFileUpload.isMultipartContent(request); HttpSession session = request.getSession(); if (isMultiPart) { ServletFileUpload upload = new ServletFileUpload(); try { FileItemIterator itr = upload.getItemIterator(request); while (itr.hasNext()) { FileItemStream item = itr.next(); if (!item.isFormField()) { String path = getServletContext().getInitParameter("file-upload"); request.setAttribute("path", path); if (FileUpload.processFile(path, item)) { filepath = "C:\\Users\\shagayaraj\\Documents\\Tina\\webtechlab\\Ideation\\WebContent" + "\\" + item.getName(); String[] ext = filepath.split("\\."); String ext1 = ext[0]; String ext2 = ext[1]; if (ext2.equals("xlsx")) { int rc = 0; FileInputStream fis = new FileInputStream(new File(filepath)); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet spreadsheet = wb.getSheetAt(0); // System.out.println(list); Iterator<Row> rowIterator = spreadsheet.iterator(); while (rowIterator.hasNext()) { check = "success"; row = (XSSFRow) rowIterator.next(); rc++; Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); // out.print("<h3>"+cell.getCellType()+"</h3><br>"); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (cell.getColumnIndex() == 4) { DataFormatter df = new DataFormatter(); if ((df.formatCellValue(cell)) .matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$")) doj = df.formatCellValue(cell); else { message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>"; check = "error"; } // out.print( "<h3>"+cvd+"</h3><br>"); } else if (cell.getColumnIndex() == 7) { cell.setCellType(Cell.CELL_TYPE_STRING); if ((cell.getStringCellValue()).matches("[0-9]{10}")) mobile = cell.getStringCellValue(); else { message += "<p>Invalid Mobile Number format at row:" + rc + " and Column:8</p>"; check = "error"; } } else if (cell.getColumnIndex() == 5) { cell.setCellType(Cell.CELL_TYPE_STRING); if ((cell.getStringCellValue()).matches("[0-9]+")) level = Integer.parseInt(cell.getStringCellValue()); else { message += "<p>Invalid Level at row:" + rc + " and Column:6</p>"; check = "error"; } } else if (cell.getColumnIndex() == 6) { cell.setCellType(Cell.CELL_TYPE_STRING); if ((cell.getStringCellValue()).matches("[0-9]+")) layer = Integer.parseInt(cell.getStringCellValue()); else { message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>"; check = "error"; } } break; case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == 0) { id = cell.getStringCellValue(); String test; if (id.isEmpty()) test = "invalid"; else { userdao userd = new userdao(); userbean user = userd.getUserbyId(id); if (user == null) { test = "valid"; } else test = "invalid"; } if (test.equals("invalid")) { message += "<p>User Id is either null or already exists at row:" + rc + " and Column:1</p>"; check = "error"; } } if (cell.getColumnIndex() == 8) { man1 = cell.getStringCellValue(); String test; if (man1.isEmpty()) test = "valid"; else { userdao userd = new userdao(); userbean user = userd.getUserbyId(man1); if (user == null) { test = "invalid"; } else test = "valid"; } if (test.equals("invalid")) { message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>"; check = "error"; } } if (cell.getColumnIndex() == 9) { man2 = cell.getStringCellValue(); String test; if (man2.isEmpty()) test = "valid"; else { userdao userd = new userdao(); userbean user = userd.getUserbyId(man2); if (user == null) { test = "invalid"; } else test = "valid"; } if (test.equals("invalid")) { message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>"; check = "error"; } } if (cell.getColumnIndex() == 1) { if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+")) name = cell.getStringCellValue(); else { message += "<p>Invalid name format at row:" + rc + " and Column:2</p>"; check = "error"; } } if (cell.getColumnIndex() == 2) { if ((cell.getStringCellValue()) .matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$")) email = cell.getStringCellValue(); else { message += "<p>Invalid email format at row:" + rc + " and Column:3</p>"; check = "error"; } } if (cell.getColumnIndex() == 3) password = cell.getStringCellValue(); break; } } if (check.equals("success")) { java.sql.Date date_joined = java.sql.Date.valueOf(doj); userdao userd = new userdao(); userd.setUser( id, name, email, password, date_joined, level, layer, 0, mobile, man1, man2, false, true); user_roledao user_roled = new user_roledao(); user_roled.setUserRolebyUId(id, "3"); } } fis.close(); } // if closed else if (ext2.equals("xls")) { int rc = 0; File file = new File(filepath); InputStream input = new BufferedInputStream(new FileInputStream(file)); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); rc++; check = "success"; Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { if (cell.getColumnIndex() == 4) { DataFormatter df = new DataFormatter(); if ((df.formatCellValue(cell)) .matches("^([0-9]{4})-([0-9]{2})-([0-9]{2})$")) doj = df.formatCellValue(cell); else { message += "<p>Invalid Date format at row:" + rc + " and Column:5</p>"; check = "error"; } // out.print( "<h3>"+cvd+"</h3><br>"); } else if (cell.getColumnIndex() == 7) { cell.setCellType(Cell.CELL_TYPE_STRING); if ((cell.getStringCellValue()).matches("[0-9]{10}")) mobile = cell.getStringCellValue(); else { message += "<p>Invalid Mobile Number format at row:" + rc + " and Column:8</p>"; check = "error"; } } else if (cell.getColumnIndex() == 5) { cell.setCellType(Cell.CELL_TYPE_STRING); if ((cell.getStringCellValue()).matches("[0-9]+")) level = Integer.parseInt(cell.getStringCellValue()); else { message += "<p>Invalid Level at row:" + rc + " and Column:6</p>"; check = "error"; } } else if (cell.getColumnIndex() == 6) { cell.setCellType(Cell.CELL_TYPE_STRING); if ((cell.getStringCellValue()).matches("[0-9]+")) layer = Integer.parseInt(cell.getStringCellValue()); else { message += "<p>Invalid Layer at row:" + rc + " and Column:7</p>"; check = "error"; } } } else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) { if (cell.getColumnIndex() == 0) { id = cell.getStringCellValue(); String test; if (id.isEmpty()) test = "invalid"; else { userdao userd = new userdao(); userbean user = userd.getUserbyId(id); if (user == null) { test = "valid"; } else test = "invalid"; } if (test.equals("invalid")) { message += "<p>User Id is either null or already exists at row:" + rc + " and Column:1</p>"; check = "error"; } } if (cell.getColumnIndex() == 8) { man1 = cell.getStringCellValue(); String test; if (man1.isEmpty()) test = "valid"; else { userdao userd = new userdao(); userbean user = userd.getUserbyId(man1); if (user == null) { test = "invalid"; } else test = "valid"; } if (test.equals("invalid")) { message += "<p>Invalid Manager Id at row:" + rc + " and Column:9</p>"; check = "error"; } } if (cell.getColumnIndex() == 9) { man2 = cell.getStringCellValue(); String test; if (man2.isEmpty()) test = "valid"; else { userdao userd = new userdao(); userbean user = userd.getUserbyId(man2); if (user == null) { test = "invalid"; } else test = "valid"; } if (test.equals("invalid")) { message += "<p>Invalid Manager Id at row:" + rc + " and Column:10</p>"; check = "error"; } } if (cell.getColumnIndex() == 1) { if ((cell.getStringCellValue()).matches("^[a-zA-Z\\s]+")) name = cell.getStringCellValue(); else { message += "<p>Invalid name format at row:" + rc + " and Column:2</p>"; check = "error"; } } if (cell.getColumnIndex() == 2) { if ((cell.getStringCellValue()) .matches("^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$")) email = cell.getStringCellValue(); else { message += "<p>Invalid email format at row:" + rc + " and Column:3</p>"; check = "error"; } } if (cell.getColumnIndex() == 3) password = cell.getStringCellValue(); } } if (check.equals("success")) { java.sql.Date date_joined = java.sql.Date.valueOf(doj); userdao userd = new userdao(); userd.setUser( id, name, email, password, date_joined, level, layer, 0, mobile, man1, man2, false, true); user_roledao user_roled = new user_roledao(); user_roled.setUserRolebyUId(id, "3"); } } } else { message += "Unsupported File format!Choose files of format .xls or .xlsx only!"; } if (message == null) { message = "<p>Users added successfully</p>"; } } else { message += "File upload unsuccessful"; } } } request.setAttribute("errormsg", message); RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp"); rd.forward(request, response); } catch (FileNotFoundException e) { message += "The given file cannot be found!"; request.setAttribute("errormsg", message); RequestDispatcher rd = request.getRequestDispatcher("exceltodbmessage.jsp"); rd.forward(request, response); } catch (Exception e) { e.printStackTrace(); } } }
private void readExcel() throws IOException, URISyntaxException { URL inputStream = this.getClass().getResource("ReleaseNotes-002.xlsx"); File file = new File(inputStream.toURI()); FileInputStream fIP = new FileInputStream(file); XSSFRow row; // Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet spreadsheet = workbook.getSheetAt(i); // System.out.println(spreadsheet.getSheetName()); if (!spreadsheet.getSheetName().equals("EAR SAI")) { continue; } StringBuilder sb = new StringBuilder(); Iterator<Row> rowIterator = spreadsheet.iterator(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); System.out.println(row.getRowNum() + " " + row.getRowStyle()); if (row.getRowStyle() != null) { System.out.println(row.getRowNum() + " " + row.getRowStyle().getBorderBottom()); } Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator(); if (row.getRowNum() < 4) { if (row.getRowNum() == 3) analyzeTable(row); continue; } while (cellIterator.hasNext()) { org.apache.poi.ss.usermodel.Cell cell = cellIterator.next(); if (cell.getColumnIndex() == NAME_COLUMN) { if (!(sb.lastIndexOf("\n") == sb.length() - 1)) sb.append("\n"); } if (cell.getColumnIndex() < VALUE_COLUMN) { sb.append(readCells(cell)); continue; } if (cell.getColumnIndex() == VALUE_COLUMN) { sb.append(" -> " + readCells(cell)); if (!cellIterator.hasNext()) { sb.append("\n"); } } if (cell.getColumnIndex() == STATUS_COLUMN) { sb.append(" => " + readCells(cell)); if (!cellIterator.hasNext()) { sb.append("\n"); } } // if (!cellIterator.hasNext()) { // sb.append("\n"); // } } // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // sb.append(readCells(cell)); // if (cellIterator.hasNext()) { // // if (cell.getColumnIndex() == 5 && cell.getCellType() != 3) { // sb.append("\t->\t"); // sb.append(readCells(cell)); // if (!cellIterator.hasNext()) { // sb.append("\t=>\tstate\n"); // continue; // } // cell = cellIterator.next(); // if (cell.getCellType() != 3) { // sb.append("\t=>\t"); // sb.append(readCells(cell)); // sb.append("\n"); // } else { // sb.append("\t=>\tstate\n"); // } // continue; // } // sb.append(readCells(cell)); // } else { // sb.append("\\"); // } // } } // System.out.println(); System.out.println(sb); } if (file.isFile() && file.exists()) { System.out.println("openworkbook.xlsx file open successfully."); } else { System.out.println("Error to open openworkbook.xlsx file."); } }
/** * Compute width of a single cell * * @param cell the cell whose width is to be calculated * @param defaultCharWidth the width of a single character * @param formatter formatter used to prepare the text to be measured * @param useMergedCells whether to use merged cells * @return the width in pixels */ public static double getCellWidth( Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) { Sheet sheet = cell.getSheet(); Workbook wb = sheet.getWorkbook(); Row row = cell.getRow(); int column = cell.getColumnIndex(); int colspan = 1; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress region = sheet.getMergedRegion(i); if (containsCell(region, row.getRowNum(), column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and move on to the next. return -1; } cell = row.getCell(region.getFirstColumn()); colspan = 1 + region.getLastColumn() - region.getFirstColumn(); } } CellStyle style = cell.getCellStyle(); int cellType = cell.getCellType(); // for formula cells we compute the cell width for the cached formula result if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType(); Font font = wb.getFontAt(style.getFontIndex()); AttributedString str; TextLayout layout; double width = -1; if (cellType == Cell.CELL_TYPE_STRING) { RichTextString rt = cell.getRichStringCellValue(); String[] lines = rt.getString().split("\\n"); for (int i = 0; i < lines.length; i++) { String txt = lines[i] + defaultChar; str = new AttributedString(txt); copyAttributes(font, str, 0, txt.length()); if (rt.numFormattingRuns() > 0) { // TODO: support rich text fragments } layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.getRotation() != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ AffineTransform trans = new AffineTransform(); trans.concatenate( AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0)); trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple)); width = Math.max( width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } else { width = Math.max( width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } } } else { String sval = null; if (cellType == Cell.CELL_TYPE_NUMERIC) { // Try to get it formatted to look the same as excel try { sval = formatter.formatCellValue(cell, dummyEvaluator); } catch (Exception e) { sval = String.valueOf(cell.getNumericCellValue()); } } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase(); } if (sval != null) { String txt = sval + defaultChar; str = new AttributedString(txt); copyAttributes(font, str, 0, txt.length()); layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.getRotation() != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ AffineTransform trans = new AffineTransform(); trans.concatenate( AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0)); trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple)); width = Math.max( width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } else { width = Math.max( width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention()); } } } return width; }