List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getRichStringCellValue
public HSSFRichTextString getRichStringCellValue()
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-5] : ?? ? ? ? /*w w w.j a v a 2s.c om*/ */ @Test public void testGetCellContents() throws Exception { try { log.debug("testGetCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testGetCellContents.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } HSSFWorkbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? HSSFWorkbook wb = excelService.loadWorkbook(sb.toString()); log.debug("testGetCellContents after loadWorkbook...."); HSSFSheet sheet = wb.createSheet("cell test sheet"); HSSFCellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setWrapText(true); for (int i = 0; i < 100; i++) { HSSFRow row = sheet.createRow(i); for (int j = 0; j < 5; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString()); HSSFSheet sheetT = wbT.getSheet("cell test sheet"); for (int i = 0; i < 100; i++) { HSSFRow row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { HSSFCell cell1 = row1.getCell(j); log.debug("row " + i + ", cell " + j + " : " + cell1.getRichStringCellValue()); assertEquals("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString()); } } } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testGetCellContents end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? // w ww.j av a 2 s.c o m */ @Test public void testModifyCellAttribute() throws Exception { try { log.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } HSSFWorkbook wbTmp = new HSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? HSSFWorkbook wb = excelService.loadWorkbook(sb.toString()); log.debug("testModifyCellAttribute after loadWorkbook...."); HSSFSheet sheet = wb.createSheet("cell test sheet2"); // sheet.setColumnWidth((short) 3, (short) 200); // column Width HSSFCellStyle cs = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setFontHeight((short) 16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { HSSFRow row = sheet.createRow(i); // row.setHeight((short)300); // row? height for (int j = 0; j < 5; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString()); HSSFSheet sheetT = wbT.getSheet("cell test sheet2"); log.debug("getNumCellStyles : " + wbT.getNumCellStyles()); HSSFCellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); HSSFFont fontT = cs1.getFont(wbT); log.debug("font getFontHeight : " + fontT.getFontHeight()); log.debug("font getBoldweight : " + fontT.getBoldweight()); log.debug("font getFontName : " + fontT.getFontName()); log.debug("getAlignment : " + cs1.getAlignment()); log.debug("getWrapText : " + cs1.getWrapText()); for (int i = 0; i < 100; i++) { HSSFRow row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { HSSFCell cell1 = row1.getCell(j); log.debug("row " + i + ", cell " + j + " : " + cell1.getRichStringCellValue()); assertEquals(16, fontT.getFontHeight()); assertEquals(3, fontT.getBoldweight()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testModifyCellAttribute end...."); } }
From source file:endrov.customData.ImportTable.java
License:BSD License
/** * Import Excel file//from w w w .j a va 2s. c o m */ public void importExcel(String filename) throws Exception { rows.clear(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); //Take first sheet HSSFSheet sheet = wb.getSheetAt(0); for (int rowi = 0; sheet.getRow(rowi) != null; rowi++) { HSSFRow row = sheet.getRow(rowi); List<String> a = new LinkedList<String>(); for (int coli = 0; row.getCell((short) coli) != null; coli++) { HSSFCell c = row.getCell((short) coli); if (c.getCellType() == HSSFCell.CELL_TYPE_STRING) a.add(c.getRichStringCellValue().getString()); else if (c.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) a.add("" + c.getNumericCellValue()); } rows.add(a); } }
From source file:endrov.util.io.EvSpreadsheetImporter.java
License:BSD License
/** * Import Excel file/*from w w w .j a v a2 s. c o m*/ */ public void importExcel(String filename) throws Exception { rows.clear(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); //Take first sheet HSSFSheet sheet = wb.getSheetAt(0); int lastCapacity = 0; for (int rowi = 0; sheet.getRow(rowi) != null; rowi++) { HSSFRow row = sheet.getRow(rowi); ArrayList<String> a = new ArrayList<String>(lastCapacity); for (int coli = 0; row.getCell((short) coli) != null; coli++) { HSSFCell c = row.getCell((short) coli); if (c.getCellType() == HSSFCell.CELL_TYPE_STRING) a.add(c.getRichStringCellValue().getString()); else if (c.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) a.add("" + c.getNumericCellValue()); } rows.add(a); lastCapacity = a.size(); } }
From source file:gatebass.utils.exel.POIExcelReader.java
/** * 41 This method is used to display the Excel content to command line. 42 * * * @param xlsPath//from w w w .j a va 2s .co m */ @SuppressWarnings("unchecked") public void displayFromExcel(String xlsPath) { // end_row = 2242; InputStream inputStream = null; try { inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; int dd = 0; try { fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator rows = sheet.rowIterator(); List<Individuals> individualses = new ArrayList<>(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // if (row.getRowNum() >= end_row) { // break; // } if (row.getRowNum() <= start_row) { continue; } dd = row.getRowNum(); // if (row.getRowNum() == 0 // || row.getRowNum() < 195 || row.getRowNum() > 250 // ) { // continue; // } Individuals individuals = null; // display row number in the console. // System.out.println("Row No.: " + row.getRowNum()); // once get a row its time to iterate through cells. Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // System.out.println("Cell No.: " + cell.getCellNum()); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellNum()) { case 0: individuals = new Individuals(); // individuals = new Individuals(Integer.parseInt(cell.getRichStringCellValue().getString())); break; case 1: try { individuals.setCard_id(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setCard_id(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; // case 2: // if (!cell.getRichStringCellValue().getString().isEmpty()) { // } // break; case 3: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setFirst_name(cell.getRichStringCellValue().getString()); } break; case 4: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setLast_name(cell.getRichStringCellValue().getString()); } break; case 6: try { individuals.setNational_id(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setNational_id(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; case 10: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setFirst_name_ENG(cell.getRichStringCellValue().getString()); } break; case 16: try { individuals.setPostal_code(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setPostal_code(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; case 17: try { individuals.setId_number(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setId_number(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; case 18: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setBirth_day(databaseHelper.historyDao.getFirst("date", cell.getRichStringCellValue().getString().substring(2))); } break; case 19: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setFather_first_name(cell.getRichStringCellValue().getString()); } break; case 20: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setBirth_state(cell.getRichStringCellValue().getString()); } break; case 21: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setIssued(cell.getRichStringCellValue().getString()); } break; case 22: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setStreet_address(cell.getRichStringCellValue().getString()); } break; case 23: String ss = cell.getRichStringCellValue().getString(); individuals.setVeteran_status(BEDONE_KART); if (!ss.isEmpty()) { if (ss.contains("?")) { individuals.setVeteran_status(MOAF); } else if (ss.contains("")) { individuals.setVeteran_status(PAYAN_KHEDMAT); } } break; case 25: try { individuals.setMobile(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { individuals.setMobile(cell.getRichStringCellValue().getString()); } catch (Exception e) { } break; case 26: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setAcademic_degree(cell.getRichStringCellValue().getString()); } break; case 27: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setField_of_study(cell.getRichStringCellValue().getString()); } break; case 28: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setReligion(cell.getRichStringCellValue().getString()); } break; case 34: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setHave_soe_pishine(true); } break; case 35: if (!cell.getRichStringCellValue().getString().isEmpty()) { individuals.setComments(cell.getRichStringCellValue().getString()); } break; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { // cell type numeric. // System.out.println("Numeric value: " + cell.getNumericCellValue()); break; } case HSSFCell.CELL_TYPE_STRING: { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue(); // System.out.println("String value: " + richTextString.getString()); break; } default: { // types other than String and Numeric. // System.out.println("Type not supported."); break; } } } String split = FileSystems.getDefault().getSeparator(); individuals.setFilesPatch( "data" + split + "1394" + split + dd / 50 + split + individuals.getNational_id() + split); File imageFile = new File( "d://test//Images-Personal-Gatepass//" + individuals.getCard_id() + ".jpg"); if (imageFile.exists()) { individuals.setPicture_address(individuals.getNational_id() + "-pic"); copyImageFile(imageFile.getAbsolutePath(), server + individuals.getFilesPatch(), individuals.getPicture_address()); individuals.setPicture_address( individuals.getPicture_address() + getFileExtension(imageFile.getAbsolutePath())); } individualses.add(individuals); // databaseHelper.individualsDao.createOrUpdate(individuals, dd); } databaseHelper.individualsDao.insertList(individualses); } catch (Exception e) { e.printStackTrace(); } }
From source file:gatebass.utils.exel.POIExcelReader.java
@SuppressWarnings("unchecked") public void displayFromExcel2(String xlsPath) { InputStream inputStream = null; try {// www . j av a2 s.co m inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; int dd = 0; try { fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); HSSFSheet sheet = workBook.getSheetAt(0); Iterator rows = sheet.rowIterator(); List<Cars> carses = new ArrayList<>(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); // if (row.getRowNum() >= end_row) { // break; // } if (row.getRowNum() <= start_row) { continue; } dd = row.getRowNum(); // if (row.getRowNum() == 0 // || row.getRowNum() < 195 || row.getRowNum() > 250 // ) { // continue; // } Cars cars = null; // display row number in the console. // System.out.println("Row No.: " + row.getRowNum()); // once get a row its time to iterate through cells. Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); // System.out.println("Cell No.: " + cell.getCellNum()); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellNum()) { case 0: cars = new Cars(); // individuals = new Individuals(Integer.parseInt(cell.getRichStringCellValue().getString())); break; case 1: try { cars.setCard_id(((long) cell.getNumericCellValue()) + ""); } catch (Exception e) { } try { cars.setCard_id(cell.getRichStringCellValue().getString()); } catch (Exception e) { } cars.setShasi_number(cars.getCard_id()); break; // case 2: // if (!cell.getRichStringCellValue().getString().isEmpty()) { // } // break; case 3: if (!cell.getRichStringCellValue().getString().isEmpty()) { cars.setCar_name(cell.getRichStringCellValue().getString()); } break; // case 4: // if (!cell.getRichStringCellValue().getString().isEmpty()) { // cars.setLast_name(cell.getRichStringCellValue().getString()); // } // break; // case 5: // if (!cell.getRichStringCellValue().getString().isEmpty()) { // cars.set(cell.getRichStringCellValue().getString()); // } // break; case 13: if (!cell.getRichStringCellValue().getString().isEmpty()) { cars.setComments(cell.getRichStringCellValue().getString()); } break; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { // cell type numeric. // System.out.println("Numeric value: " + cell.getNumericCellValue()); break; } case HSSFCell.CELL_TYPE_STRING: { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue(); // System.out.println("String value: " + richTextString.getString()); break; } default: { // types other than String and Numeric. // System.out.println("Type not supported."); break; } } } String split = FileSystems.getDefault().getSeparator(); cars.setFilesPatch( "data" + split + "1394" + split + dd / 50 + split + cars.getShasi_number() + "_c" + split); carses.add(cars); // databaseHelper.individualsDao.createOrUpdate(individuals, dd); } databaseHelper.carDao.insertList(carses); } catch (Exception e) { e.printStackTrace(); } }
From source file:gda.hrpd.data.ExcelWorkbook.java
License:Open Source License
/** * gets value from the specified cell and return it as String. * /* w w w . j a v a 2 s . c o m*/ * @param cell * @return value from cell as a String */ public String getCellValue(HSSFCell cell) { // If the cell is null return an empty string if (cell == null) { return ""; } String value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { value = cell.getRichStringCellValue().toString(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { value = String.valueOf(cell.getNumericCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { value = " "; } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { value = String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) { value = String.valueOf(cell.getErrorCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { value = cell.getCellFormula(); } return value; }
From source file:jp.dbcls.bp3d.kaorif.visiblebody.nerves.VisibleBodyNerves.java
License:Open Source License
/** * ???(manuallyMapped.txt)?/* w ww . java2 s.c om*/ * @throws Exception */ public void readManuallyMapped() throws Exception { POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.MANUALLY_MAPPED)); HSSFWorkbook wb = new HSSFWorkbook(filein); HSSFSheet sheet = wb.getSheet("manuallyMapped"); for (int i = 1; i < sheet.getLastRowNum() + 1; i++) { HSSFRow row = sheet.getRow(i); HSSFCell cell = null; String original = ""; cell = row.getCell(0); if (cell != null) { original = cell.getRichStringCellValue().toString().trim(); } String av = ""; cell = row.getCell(1); if (cell != null) { av = cell.getRichStringCellValue().toString().trim(); } String renamed = ""; cell = row.getCell(2); if (cell != null) { renamed = cell.getRichStringCellValue().toString().trim(); } String remark = ""; for (int j = 3; j < row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell != null) { remark += cell.getRichStringCellValue().toString().trim() + "\t"; } } VisibleBodyManuallyMapped mm = new VisibleBodyManuallyMapped(); mm.setOriginal(original); mm.setAv(av); mm.setRenamed(renamed); mm.setRemark(remark); this.manuallyMapped.add(mm); } }
From source file:jp.dbcls.bp3d.kaorif.visiblebody.vessels.VisibleBodyVessels.java
License:Open Source License
/** * this.ORDERED_BY_KAORIF???//from w w w . ja v a 2 s . c o m * * @throws Exception */ public void readOrderedByKaorif() throws Exception { POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.ORDERED_BY_KAORIF)); HSSFWorkbook wb = new HSSFWorkbook(filein); HSSFSheet sheet = wb.getSheet("CirculatorySystem"); for (int i = 1; i < sheet.getLastRowNum() + 1; i++) { HSSFRow row = sheet.getRow(i); boolean isRequired = row.getCell(0).getBooleanCellValue(); String en = ""; HSSFCell cell = row.getCell(1); if (cell != null) { en = cell.getRichStringCellValue().toString().trim(); } String av = ""; cell = row.getCell(2); if (cell != null) { av = cell.getRichStringCellValue().toString().trim(); } VisibleBodyEntry vbe = getEntry(en, av); if (vbe != null) { vbe.setOrderedByKaorif(isRequired); } else { System.out.println("readOrderedByKaorif not found=" + en + " " + av); } } }
From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadExcelJob.java
License:Open Source License
private void loadExcel(final String file) { final File fil = new File(file); if (fil.exists()) { canRead = true;/*from w ww .j a v a 2s .c om*/ if (grid != null) { try { InputStream inp = new FileInputStream(file); try { wb = new HSSFWorkbook(inp); } catch (Exception e) { MsgDialog.message("Wrong format!\nOnly Excel 97-2007 is supported!"); canRead = false; } } catch (IOException ex) { ex.printStackTrace(); } if (canRead) { for (s = 0; s < wb.getNumberOfSheets(); s++) { Display display = PlatformUI.getWorkbench().getDisplay(); display.syncExec(new Runnable() { public void run() { String name = fil.getName(); grid = new Spread().spread(SampleView.getTabFolder(), 0, 0, name); SampleView.setGrid(grid); HSSFSheet sheet = wb.getSheetAt(s); int colCount = grid.getColumnCount(); int rowCount = grid.getItemCount(); int exelRow = endOfRow(sheet); int exelColumn = endOfColumn(sheet); // System.out.println(exelRow + " " + exelColumn // + "---" + sheet.getPhysicalNumberOfRows() + // " " + // sheet.getRow(0).getPhysicalNumberOfCells()); if (colCount < exelColumn) { int diff = exelColumn - colCount; for (int i = 0; i < diff; i++) { GridColumn column = new GridColumn(grid, SWT.NONE); column.setText("C " + (i + 1 + colCount)); column.setWidth(50); } } if (rowCount < exelRow) { int diff = exelRow - rowCount; for (int i = 0; i < diff; i++) { new GridItem(grid, SWT.NONE).setHeight(16); } } // Iterate over each row in the sheet int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < exelRow; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { for (int u = 0; u < exelColumn; u++) { grid.getItem(i).setText(u, " "); } } else { for (int u = 0; u < exelColumn; u++) { HSSFCell cell = row.getCell(u); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String val = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val); break; case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString st = cell.getRichStringCellValue(); String val2 = st.getString(); grid.getItem(i).setText(u, val2); break; case HSSFCell.CELL_TYPE_FORMULA: try { String val3 = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val3); } catch (Exception e) { String s2 = cell.getCellFormula(); grid.getItem(i).setText(u, s2); } break; case HSSFCell.CELL_TYPE_BLANK: grid.getItem(i).setText(u, " "); break; case HSSFCell.CELL_TYPE_BOOLEAN: boolean s4 = cell.getBooleanCellValue(); if (s4) { grid.getItem(i).setText(u, "TRUE"); } else { grid.getItem(i).setText(u, "FALSE"); } break; default: break; } } else { grid.getItem(i).setText(u, " "); } } } } } }); } wb = null; } } } else { MsgDialog.message("File not found!"); } }