List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue
public String getStringCellValue()
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * ? Excel//from w ww .j a va 2 s . com * * @param excel_name * @return * @throws Exception */ public static List<List<Object>> readExcelByList(String excel_name) throws Exception { // List<List<Object>> list = new ArrayList<List<Object>>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name)); // ??i? getNumberOfSheets HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // ??,j getPhysicalNumberOfRows for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // ?? List<Object> arrayString = new ArrayList<Object>(); for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { arrayString.add(new Double(cell.getNumericCellValue()).toString()); } else {// EXCEL? arrayString.add(cell.getStringCellValue().trim()); } } list.add(arrayString); } } return list; }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * ? Excel//w ww.j av a2 s . c om * * @param inputstream * @return * @throws Exception */ public static List<List<Object>> readExcelByInputStream(InputStream inputstream) throws Exception { // List<List<Object>> list = new ArrayList<List<Object>>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream); // ??i? getNumberOfSheets HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // ??,j getPhysicalNumberOfRows // //System.out.println("excel "+hssfsheet.getPhysicalNumberOfRows()); for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // ?? List<Object> arrayString = new ArrayList<Object>(); for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { arrayString.add(new Double(cell.getNumericCellValue()).toString()); } else {// EXCEL? arrayString.add(cell.getStringCellValue().trim()); } } list.add(arrayString); } } return list; }
From source file:com.mebigfatguy.yank.YankTask.java
License:Apache License
private List<Artifact> readArtifactList() throws IOException { BufferedInputStream bis = null; List<Artifact> artifacts = new ArrayList<Artifact>(); try {/*from w ww .ja v a 2 s .c o m*/ bis = new BufferedInputStream(new FileInputStream(xlsFile)); POIFSFileSystem poifs = new POIFSFileSystem(bis); HSSFWorkbook workBook = new HSSFWorkbook(poifs); HSSFSheet sheet = workBook.getSheetAt(0); Map<ColumnType, Integer> columnHeaders = getColumnInfo(sheet); Integer typeColumn = columnHeaders.get(ColumnType.TYPE_COLUMN); Integer classifierColumn = columnHeaders.get(ColumnType.CLASSIFIER_COLUMN); String groupId = ""; String artifactId = ""; String type = JAR; String version = ""; String classifier = ""; for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); ++i) { HSSFRow row = sheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(columnHeaders.get(ColumnType.GROUP_COLUMN)); if (cell != null) { String gId = cell.getStringCellValue().trim(); if (!gId.isEmpty()) { groupId = gId; } } cell = row.getCell(columnHeaders.get(ColumnType.ARTIFACT_COLUMN)); if (cell != null) { String aId = cell.getStringCellValue().trim(); if (!aId.isEmpty()) { artifactId = aId; } } cell = row.getCell(columnHeaders.get(ColumnType.VERSION_COLUMN)); if (cell != null) { String v; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { v = String.valueOf(cell.getNumericCellValue()); } else { v = cell.getStringCellValue().trim(); } if (!v.isEmpty()) { version = v; } } cell = (typeColumn != null) ? row.getCell(typeColumn.intValue()) : null; if (cell != null) { type = cell.getStringCellValue().trim(); } cell = (classifierColumn != null) ? row.getCell(classifierColumn.intValue()) : null; if (cell != null) { classifier = cell.getStringCellValue().trim(); } if (groupId.isEmpty() || artifactId.isEmpty() || version.isEmpty()) { if (groupId.isEmpty() || version.isEmpty()) { getProject().log("Row " + row.getRowNum() + ": Invalid artifact specified: [groupId: " + groupId + ", artifactId: " + artifactId + ", classifier: " + classifier + ", version: " + version + "]"); } } else { artifacts.add(new Artifact(groupId, artifactId, type, classifier, version)); } } artifactId = ""; classifier = ""; type = JAR; } getProject().log(sheet.getLastRowNum() + " rows read from " + xlsFile, Project.MSG_VERBOSE); } finally { Closer.close(bis); } return artifacts; }
From source file:com.mebigfatguy.yank.YankTask.java
License:Apache License
private Map<ColumnType, Integer> getColumnInfo(HSSFSheet sheet) { int firstRow = sheet.getFirstRowNum(); HSSFRow row = sheet.getRow(firstRow); Map<ColumnType, Integer> columnHeaders = new EnumMap<ColumnType, Integer>(ColumnType.class); for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); ++i) { HSSFCell cell = row.getCell(i); if (cell != null) { String value = cell.getStringCellValue(); if (value != null) { value = value.trim().toLowerCase(); if (value.startsWith("group")) { columnHeaders.put(ColumnType.GROUP_COLUMN, i); } else if (value.startsWith("artifact")) { columnHeaders.put(ColumnType.ARTIFACT_COLUMN, i); } else if (value.startsWith("type")) { columnHeaders.put(ColumnType.TYPE_COLUMN, i); } else if (value.startsWith("version")) { columnHeaders.put(ColumnType.VERSION_COLUMN, i); } else if (value.startsWith("classifier") || value.startsWith("alternate")) { columnHeaders.put(ColumnType.CLASSIFIER_COLUMN, i); }// w w w. j a v a 2s.c o m if (columnHeaders.size() == 4) { return columnHeaders; } } } } if (columnHeaders.size() >= 3) return columnHeaders; throw new BuildException( "Input yank xls file (" + xlsFile + ") does not contains GroupId, ArtifactId, or Version columns"); }
From source file:com.ms.commons.file.excel.ExcelParser.java
License:Open Source License
@SuppressWarnings({ "deprecation", "unused" })
public String[] splitLine() throws Exception {
if (m_iCurrentRow == m_iNbRows)
return null;
HSSFRow row = m_sheet.getRow(m_iCurrentRow);
if (row == null) {
return null;
} else {/*from w w w.jav a 2 s . c om*/
int cellIndex = 0;
int noOfCells = row.getPhysicalNumberOfCells();
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
String[] values = new String[lastCellNum];
if (firstCellNum >= 0 && lastCellNum >= 0) {
for (short iCurrent = firstCellNum; iCurrent < lastCellNum; iCurrent++) {
HSSFCell cell = (HSSFCell) row.getCell(iCurrent);
if (cell == null) {
values[iCurrent] = StringUtils.EMPTY;
cellIndex++;
continue;
} else {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
double value = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
if (HSSFDateUtil.isValidExcelDate(value)) {
Date date = HSSFDateUtil.getJavaDate(value);
SimpleDateFormat dateFormat = new SimpleDateFormat(JAVA_TOSTRING);
values[iCurrent] = dateFormat.format(date);
} else {
throw new Exception("Invalid Date value found at row number " + row.getRowNum()
+ " and column number " + cell.getCellNum());
}
} else {
values[iCurrent] = value + StringUtils.EMPTY;
}
break;
case HSSFCell.CELL_TYPE_STRING:
values[iCurrent] = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
values[iCurrent] = null;
break;
default:
values[iCurrent] = null;
}
}
}
}
m_iCurrentRow++;
return values;
}
}
From source file:com.ms.commons.test.datareader.impl.ExcelReadUtil.java
License:Open Source License
/** * @param name column name of table in which this cell locates. *//*w ww. ja v a 2s . c o m*/ @SuppressWarnings("deprecation") protected static MemoryField readCellValue(String name, HSSFCell cell) { MemoryField field = null; if (cell == null) { field = new MemoryField(name, MemoryFieldType.Null); } else { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: case HSSFCell.CELL_TYPE_FORMULA: if (getCellValueForFormula(cell)) { field = new MemoryField(name, MemoryFieldType.Date, cell.getDateCellValue().toString()); } else { field = new MemoryField(name, MemoryFieldType.Number, cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: field = new MemoryField(name, MemoryFieldType.String, cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: field = new MemoryField(name, MemoryFieldType.Null); break; default: field = new MemoryField(name, MemoryFieldType.Unknow, cell.getStringCellValue()); break; } } return field; }
From source file:com.mycompany.corevaluecontest.InsertEmployee_Excecl.java
public void uploadExcel() { try {/*from ww w. j av a 2 s . com*/ conn = Database.getConnection(); // Use excel file insert to employee FileInputStream input = new FileInputStream("D:\\Excel\\Corevalue\\Employee.xls"); //System.out.println("+++++++++++++++++"); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell id, firstname, lastname, division, section, grp, position; int a = 0; int b = 2; int c = 3; int d = 5; int e = 6; int f = 7; int g = 8; int as = 0; String grpvalue = null; int x = sheet.getLastRowNum(); //System.out.println("+++++++2+++++"); for (int i = 1; i <= x; i++) { row = sheet.getRow(i); if (row.getCell(a).toString() != null) { as++; //System.out.println("%%%%%%%%" + as + "//////////" + x); } } System.out.println("++++++3++++++++"); for (int i = 1; i <= as; i++) { row = sheet.getRow(i); id = row.getCell(a); String str = id.getStringCellValue().toString(); String ans = str.substring(str.length() - 4, str.length()); firstname = row.getCell(b); String firstvalue = firstname.getStringCellValue().toString(); lastname = row.getCell(c); String lastvalue = lastname.getStringCellValue().toString(); division = row.getCell(d); String divisionvalue = division.getStringCellValue().toString(); section = row.getCell(e); String sectionvalue = section.getStringCellValue().toString(); if (row.getCell(f) != null) { grp = row.getCell(f); grpvalue = grp.getStringCellValue(); } else { grpvalue = ""; } position = row.getCell(g); String positionvalue = position.getStringCellValue(); String last2 = lastvalue.substring(0, 1) + "." + firstvalue; String sql = "INSERT INTO tblMaster_User (UserID)VALUES('" + ans + "')"; ps = (PreparedStatement) conn.prepareStatement(sql); ps.execute(); /* SQL_Str = "DELETE FROM PositionMaster"; conn = Database.getConnection(); ps = (PreparedStatement) conn.prepareStatement(SQL_Str); ps.execute(); String sql2 = "INSERT INTO PositionMaster (P_Name) VALUES ('" + positionvalue + "')"; ps = (PreparedStatement) conn.prepareStatement(sql2); ps.execute(); */ System.out.println("Import rows " + i); } //<p:commandButton value="Submitxx" update="@all" icon="ui-icon-check" action="#{InsertEmployee_Excecl.uploadExcel()}"/> conn.commit(); ps.close(); conn.close(); input.close(); System.out.println("Success import excel to mysql table"); } catch (SQLException ex) { System.out.println(ex); } catch (IOException ioe) { System.out.println(ioe); } }
From source file:com.netxforge.netxstudio.data.importer.HSSFMetricValuesImporter.java
License:Open Source License
protected String getCellValue(HSSFCell cell) { String value = null;// ww w . j a va 2s .co m // Get the value. int cellType = cell.getCellType(); if (cellType == HSSFCell.CELL_TYPE_NUMERIC) { double numericCellValue = cell.getNumericCellValue(); NumberFormat nf = NumberFormat.getInstance(); nf.setMaximumFractionDigits(0);// set as you need value = nf.format(numericCellValue); } else if (cellType == HSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } return value; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
/** * This is a helper method to retrieve the value of a cell regardles of its * type, which will be converted into a String. * //w w w .ja va 2s . co m * @param cell * @return */ private Object getCellValue(HSSFCell cell) { if (cell == null) { return null; } Object result = null; int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: result = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; break; case HSSFCell.CELL_TYPE_ERROR: result = "ERROR: " + cell.getErrorCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle cellStyle = cell.getCellStyle(); short dataFormat = cellStyle.getDataFormat(); // assumption is made that dataFormat = 14, // when cellType is HSSFCell.CELL_TYPE_NUMERIC // is equal to a DATE format. if (dataFormat == 164) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } break; case HSSFCell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; default: break; } if (result instanceof Double) { return String.valueOf(((Double) result).longValue()); } if (result instanceof Date) { return result; } return result.toString(); }
From source file:com.proem.exm.service.wholesaleGroupPurchase.customer.impl.CustomerInfoServiceImpl.java
@SuppressWarnings("static-access") private String getValue(HSSFCell hssfCell) { if (hssfCell != null) { if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { // //from www.j av a 2 s .co m return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { // return String.valueOf(hssfCell.getNumericCellValue()); } else { // return String.valueOf(hssfCell.getStringCellValue()); } } else { return null; } }