List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:com.liferay.dynamic.data.lists.exporter.test.DDLExporterTest.java
License:Open Source License
@Test public void testXLSExport() throws Exception { DDMForm ddmForm = DDMFormTestUtil.createDDMForm(_availableLocales, _defaultLocale); createDDMFormFields(ddmForm);//from www . j a v a2 s .com DDMFormValues ddmFormValues = DDMFormValuesTestUtil.createDDMFormValues(ddmForm, _availableLocales, _defaultLocale); createDDMFormFieldValues(ddmFormValues); DDLRecordSetTestHelper recordSetTestHelper = new DDLRecordSetTestHelper(_group); DDLRecordSet recordSet = recordSetTestHelper.addRecordSet(ddmForm); DDLRecordTestHelper recordTestHelper = new DDLRecordTestHelper(_group, recordSet); DDLRecord record = recordTestHelper.addRecord(ddmFormValues, WorkflowConstants.ACTION_PUBLISH); DDLRecordVersion recordVersion = record.getRecordVersion(); DDLExporter ddlExporter = _ddlExporterFactory.getDDLExporter("xls"); byte[] bytes = ddlExporter.export(recordSet.getRecordSetId()); try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes); HSSFWorkbook workbook = new HSSFWorkbook(byteArrayInputStream)) { Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); Cell cell = null; for (int i = 0; i < 13; i++) { cell = row.getCell(i); Assert.assertEquals("Field" + i, cell.getStringCellValue()); } row = sheet.getRow(1); cell = row.getCell(0); Assert.assertEquals("No", cell.getStringCellValue()); cell = row.getCell(1); Assert.assertEquals("1/1/70", cell.getStringCellValue()); cell = row.getCell(2); Assert.assertEquals("1", cell.getStringCellValue()); cell = row.getCell(3); Assert.assertEquals("file.txt", cell.getStringCellValue()); cell = row.getCell(4); Assert.assertEquals("Latitude: -8.035, Longitude: -34.918", cell.getStringCellValue()); cell = row.getCell(5); Assert.assertEquals("2", cell.getStringCellValue()); cell = row.getCell(6); Assert.assertEquals("Link to Page content", cell.getStringCellValue()); cell = row.getCell(7); Assert.assertEquals("3", cell.getStringCellValue()); cell = row.getCell(8); Assert.assertEquals("Option 1", cell.getStringCellValue()); cell = row.getCell(9); Assert.assertEquals("Option 1", cell.getStringCellValue()); cell = row.getCell(10); Assert.assertEquals("Text content", cell.getStringCellValue()); cell = row.getCell(11); Assert.assertEquals("Text Area content", cell.getStringCellValue()); cell = row.getCell(12); Assert.assertEquals("Text HTML content", cell.getStringCellValue()); cell = row.getCell(13); Assert.assertEquals("Approved", cell.getStringCellValue()); cell = row.getCell(14); Assert.assertEquals(formatDate(recordVersion.getStatusDate()), cell.getStringCellValue()); cell = row.getCell(15); Assert.assertEquals(recordVersion.getUserName(), cell.getStringCellValue()); } }
From source file:com.lulu.ofarm.test.ImportControllerTest.java
@Test public void getBeanFromExcel() throws FileNotFoundException, IOException { //1.Excel /*from w w w.j a v a 2s . c o m*/ // POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/FTP/test.xls")); //2.Excel Workbook wb = new HSSFWorkbook(new FileInputStream("F:/reposibility_new.xls")); //3.Excel Sheet sheet = wb.getSheetAt(0); // int trLength = sheet.getLastRowNum(); //4.Excel Row row = sheet.getRow(0); // int tdLength = row.getLastCellNum(); //5.Excel? Cell cell = row.getCell((short) 1); //6.?? //CellStyle cellStyle = cell.getCellStyle(); for (int i = 2; i < trLength; i++) { //Excel Row row1 = sheet.getRow(i); for (int j = 0; j < tdLength; j++) { Cell cell1 = row1.getCell(j); /** * ?ExcelCannot get a text value from a numeric cell * ?String? */ if (cell1 != null) { cell1.setCellType(Cell.CELL_TYPE_STRING); } System.out.println(cell1.getStringCellValue()); } //Excel? //? // OutfallPolluateResourceBean resource = new OutfallPolluateResourceBean(); // resource.setRivername(row1.getCell(1).getStringCellValue()); // resource.setArea(row1.getCell(2).getStringCellValue()); // resource.setLeftorrightbank(row1.getCell(3).getStringCellValue()); // resource.setOutfalltype(row1.getCell(4).getStringCellValue()); // resource.setOutfallcode(row1.getCell(5).getStringCellValue()); // resource.setSecondaryunit(row1.getCell(6).getStringCellValue()); // resource.setStreetname(row1.getCell(7).getStringCellValue()); // resource.setStreetmanager(row1.getCell(8).getStringCellValue()); // resource.setVillage(row1.getCell(9).getStringCellValue()); // resource.setVillagemanager(row1.getCell(10).getStringCellValue()); // resource.setPosition(row1.getCell(11).getStringCellValue()); // resource.setCoordinate(row1.getCell(12).getStringCellValue()); // resource.setOutfallsize(row1.getCell(13).getStringCellValue()); // resource.setOutfallshape(row1.getCell(14).getStringCellValue()); // resource.setPolldescription(row1.getCell(15).getStringCellValue()); // // resource.setRectificationmeasures(row1.getCell(16).getStringCellValue()); // resource.setDrainageTo(row1.getCell(17).getStringCellValue()); // resource.setTherectificationresponsibilityunit(row1.getCell(18).getStringCellValue()); // resource.setTimeofcompletion(row1.getCell(19).getStringCellValue()); // resource.setRemark(row1.getCell(20).getStringCellValue()); // System.err.println(resource); // service.save(resource); // PollutantSourceBean source = new PollutantSourceBean(); // source.setRivername(row1.getCell(1).getStringCellValue()); // source.setArea(row1.getCell(2).getStringCellValue()); // source.setPollsourcename(row1.getCell(3).getStringCellValue()); // source.setStreetname(row1.getCell(4).getStringCellValue()); // source.setStreetmanager(row1.getCell(5).getStringCellValue()); // source.setVillage(row1.getCell(6).getStringCellValue()); // source.setVillagemanager(row1.getCell(7).getStringCellValue()); // source.setPollsourcetype(row1.getCell(8).getStringCellValue()); // source.setOutfalltype(row1.getCell(9).getStringCellValue()); // source.setOutfallcode(row1.getCell(10).getStringCellValue()); // source.setPosition(row1.getCell(11).getStringCellValue()); // source.setCoordinate(row1.getCell(12).getStringCellValue()); // source.setPolldescription(row1.getCell(13).getStringCellValue()); // source.setDrainageto(row1.getCell(14).getStringCellValue()); // source.setPolldischarginglicense(row1.getCell(15).getStringCellValue()); // source.setDrainaglicense(row1.getCell(16).getStringCellValue()); // source.setHasmeasures(row1.getCell(17).getStringCellValue()); // source.setRectificationmeasures(row1.getCell(18).getStringCellValue()); // source.setTherectificationresponsibilityunit(row1.getCell(19).getStringCellValue()); // source.setTimeofcompletion(row1.getCell(20).getStringCellValue()); // source.setRemark(row1.getCell(21).getStringCellValue()); // System.out.println(source); // service.save(source); } }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * //from ww w . ja v a2 s . c om * excel * * @param inputstream : ? * @param pojoClass : (?) * @return */ public static Collection importExcelByIs(InputStream inputstream, Class pojoClass) { Collection dist = new ArrayList<Object>(); try { // Field filed[] = pojoClass.getDeclaredFields(); // Annotation??,map Map<String, Method> fieldSetMap = new HashMap<String, Method>(); Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>(); // ? for (int i = 0; i < filed.length; i++) { Field f = filed[i]; // ?Annotation Excel excel = f.getAnnotation(Excel.class); // Annotationd? if (excel != null) { // AnnotationSetter String fieldname = f.getName(); String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1); // method Method setMethod = pojoClass.getMethod(setMethodName, new Class[] { f.getType() }); // methodAnnotaion??key? // ??? ?? fieldSetMap.put(excel.exportName(), setMethod); if (excel.importConvert() == true) { // get/setXxxxConvert??? ?Entity? StringBuffer setConvertMethodName = new StringBuffer("convertSet"); setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); setConvertMethodName.append(fieldname.substring(1)); Method getConvertMethod = pojoClass.getMethod(setConvertMethodName.toString(), new Class[] { String.class }); fieldSetConvertMap.put(excel.exportName(), getConvertMethod); } } } // FileFileInputStream; // // HSSFWorkbook book = new HSSFWorkbook(inputstream); // // HSSFSheet sheet = book.getSheetAt(0); // // ? Iterator<Row> row = sheet.rowIterator(); // Row title = row.next(); // Iterator<Cell> cellTitle = title.cellIterator(); // map Map titlemap = new HashMap(); // int i = 0; // while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = cell.getStringCellValue(); titlemap.put(i, value); i = i + 1; } // ??DateFormat // SimpleDateFormat sf; while (row.hasNext()) { // Row rown = row.next(); // Iterator<Cell> cellbody = rown.cellIterator(); // Object tObject = pojoClass.newInstance(); int k = 0; // ?? while (cellbody.hasNext()) { Cell cell = cellbody.next(); // String titleString = (String) titlemap.get(k); // ?Annotation?set if (fieldSetMap.containsKey(titleString)) { Method setMethod = (Method) fieldSetMap.get(titleString); // setter? Type[] ts = setMethod.getGenericParameterTypes(); // ??? String xclass = ts[0].toString(); // ? if (Cell.CELL_TYPE_STRING == cell.getCellType() && fieldSetConvertMap.containsKey(titleString)) { // ???String? fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue()); } else { if (xclass.equals("class java.lang.String")) { // Cell??String? cell.setCellType(Cell.CELL_TYPE_STRING); setMethod.invoke(tObject, cell.getStringCellValue()); } else if (xclass.equals("class java.util.Date")) { // update-start--Author:Quainty Date:20130523 for??(?Excel?) Date cellDate = null; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { // ? cellDate = cell.getDateCellValue(); } else { // Cell.CELL_TYPE_STRING: ? yyyy-mm-dd hh:mm:ss ??(wait to do:?) cellDate = stringToDate(cell.getStringCellValue()); } setMethod.invoke(tObject, cellDate); //// -------------------------------------------------------------------------------------------- //String cellValue = cell.getStringCellValue(); //Date theDate = stringToDate(cellValue); //setMethod.invoke(tObject, theDate); //// -------------------------------------------------------------------------------------------- } else if (xclass.equals("class java.lang.Boolean")) { boolean valBool; if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) { valBool = cell.getBooleanCellValue(); } else {// Cell.CELL_TYPE_STRING valBool = cell.getStringCellValue().equalsIgnoreCase("true") || (!cell.getStringCellValue().equals("0")); } setMethod.invoke(tObject, valBool); } else if (xclass.equals("class java.lang.Integer")) { Integer valInt; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valInt = (new Double(cell.getNumericCellValue())).intValue(); } else {// Cell.CELL_TYPE_STRING valInt = new Integer(cell.getStringCellValue()); } setMethod.invoke(tObject, valInt); } else if (xclass.equals("class java.lang.Long")) { Long valLong; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valLong = (new Double(cell.getNumericCellValue())).longValue(); } else {// Cell.CELL_TYPE_STRING valLong = new Long(cell.getStringCellValue()); } setMethod.invoke(tObject, valLong); } else if (xclass.equals("class java.math.BigDecimal")) { BigDecimal valDecimal; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valDecimal = new BigDecimal(cell.getNumericCellValue()); } else {// Cell.CELL_TYPE_STRING valDecimal = new BigDecimal(cell.getStringCellValue()); } setMethod.invoke(tObject, valDecimal); } } } // k = k + 1; } dist.add(tObject); } } catch (Exception e) { e.printStackTrace(); return null; } return dist; }
From source file:com.lw.common.utils.ExcelUtil.java
private List<String> parseTitleRow(Row row) { List<String> rst = new ArrayList<>(); Cell cell; Iterator<Cell> iterator = row.iterator(); while (iterator.hasNext()) { cell = iterator.next();//from w w w . jav a2 s . c om cell.setCellType(Cell.CELL_TYPE_STRING); rst.add(cell.getStringCellValue()); } return rst; }
From source file:com.lw.common.utils.ExcelUtil.java
private List<String> parseDataRow(Row row, int size) { List<String> rst = new ArrayList<>(); Cell cell; for (int i = 0; i < size; i++) { cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL); if (cell == null) { rst.add(""); } else {/*from ww w. j av a2 s . c o m*/ cell.setCellType(Cell.CELL_TYPE_STRING); rst.add(cell.getStringCellValue().trim()); } } return rst; }
From source file:com.lw.common.utils.ExcelUtil.java
private Map<String, Integer> getTitleRowIndex(Row row) { Map<String, Integer> rowIndex = new HashMap<String, Integer>(); Cell cell; Iterator<Cell> iterator = row.iterator(); int index = 0; while (iterator.hasNext()) { cell = iterator.next();/* ww w . ja v a 2 s . c o m*/ cell.setCellType(Cell.CELL_TYPE_STRING); rowIndex.put(cell.getStringCellValue(), index); index++; } //???index return rowIndex; }
From source file:com.marcosanta.controllers.ComscoreController.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/*from w ww . j ava2 s .c om*/ CellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); for (Row row : sheet) { for (Cell cell : row) { cell.setCellValue(cell.getStringCellValue().toUpperCase()); cell.setCellStyle(style); } } }
From source file:com.maxl.java.aips2xml.Aips2Xml.java
License:Open Source License
static String getAnyValue(Cell part) { if (part != null) { switch (part.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return part.getBooleanCellValue() + ""; case Cell.CELL_TYPE_NUMERIC: return part.getNumericCellValue() + ""; case Cell.CELL_TYPE_STRING: return part.getStringCellValue() + ""; case Cell.CELL_TYPE_BLANK: return "BLANK"; case Cell.CELL_TYPE_ERROR: return "ERROR"; case Cell.CELL_TYPE_FORMULA: return "FORMEL"; }/*from ww w. j a va 2s .c o m*/ } return ""; }
From source file:com.medicaid.mmis.util.CodeMappingLoader.java
License:Apache License
private static void importSheet(EntityManager em, SequenceGeneratorBean sequence, Workbook workbook, String sheetName) {//w w w. ja v a 2 s . c om logger.info("Importing legacy mapping from worksheet: " + sheetName); Sheet sheet = workbook.getSheet(sheetName); Iterator<Row> rowIterator = sheet.rowIterator(); int total = 0; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (row.getRowNum() < 1) { continue; } Cell systemIdCell = row.getCell(0); Cell codeTypeCell = row.getCell(1); Cell internalCodeCell = row.getCell(2); Cell externalCodeCell = row.getCell(4); if (systemIdCell == null || StringUtils.isBlank(systemIdCell.getStringCellValue())) { continue; } if (codeTypeCell == null || StringUtils.isBlank(codeTypeCell.getStringCellValue())) { continue; } if (internalCodeCell == null || StringUtils.isBlank(internalCodeCell.getStringCellValue())) { continue; } if (externalCodeCell == null || StringUtils.isBlank(externalCodeCell.getStringCellValue())) { continue; } LegacySystemMapping mapping = new LegacySystemMapping(); mapping.setId(sequence.getNextValue("LEGACY_MAPPING")); mapping.setSystemName(systemIdCell.getStringCellValue()); mapping.setExternalCode(externalCodeCell.getStringCellValue()); mapping.setInternalCode(internalCodeCell.getStringCellValue()); mapping.setCodeType(codeTypeCell.getStringCellValue()); logger.debug("Inserting mapping: " + mapping); em.persist(mapping); total++; } logger.info("Total records imported from sheet: " + total); System.out.println("Total records imported from sheet[" + sheetName + "] : " + total); }
From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java
License:Open Source License
protected CellValue getCachedCellValue(Cell cell) { int valueType = cell.getCellType(); if (valueType == Cell.CELL_TYPE_FORMULA) { valueType = cell.getCachedFormulaResultType(); }/*from w w w . ja v a 2 s . co m*/ switch (valueType) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { return CellValue.TRUE; } else { return CellValue.FALSE; } case Cell.CELL_TYPE_NUMERIC: return new CellValue(cell.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return new CellValue(cell.getStringCellValue()); case Cell.CELL_TYPE_ERROR: return CellValue.getError(cell.getErrorCellValue()); default: String msg = String.format("Could not extract value from cell with cached value type %d", valueType); throw new RuntimeException(msg); } }