List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell
@Override public HSSFCell getCell(int cellnum)
From source file:com.lacreacion.remates.FrameMiembros.java
private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed try {// w ww.j a v a2 s .c o m JFileChooser fc = new JFileChooser(); int returnVal = fc.showOpenDialog(this); if (returnVal == JFileChooser.APPROVE_OPTION) { getDatabaseIP(); File file = fc.getSelectedFile(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { entityManager.getTransaction().commit(); entityManager.getTransaction().begin(); TblMiembros miembro = new TblMiembros(); miembro.setNombre(row.getCell(0).getStringCellValue()); miembro.setCtacte( Integer.valueOf(row.getCell(1).getStringCellValue().replaceAll("[^\\d.]", ""))); if (row.getCell(2) != null) { miembro.setDomicilio(row.getCell(2).getStringCellValue()); } if (row.getCell(3) != null) { miembro.setBox((int) row.getCell(3).getNumericCellValue()); } entityManager.persist(miembro); entityManager.flush(); java.util.Collection data = query.getResultList(); list.clear(); list.addAll(data); } } } } catch (Exception ex) { JOptionPane.showMessageDialog(null, Thread.currentThread().getStackTrace()[1].getMethodName() + " - " + ex.getMessage()); ex.printStackTrace(); } }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * Method main//from ww w. j a va 2 s. c o m * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out .println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s)."); for (int c = 0; c < row.getLastCellNum(); c++) { HSSFCell cell = row.getCell(c); String value; if (cell != null) { switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; case BLANK: value = "<BLANK>"; break; case BOOLEAN: value = "BOOLEAN value-" + cell.getBooleanCellValue(); break; case ERROR: value = "ERROR value=" + cell.getErrorCellValue(); break; default: value = "UNKNOWN value of type " + cell.getCellTypeEnum(); } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } else if (args.length == 3 && args[2].equalsIgnoreCase("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.lition.service.impl.OwnedServiceImpl.java
@Override public void importExcelData(File upload) { try {/* ww w . j ava2 s. c o m*/ InputStream in = new FileInputStream(upload); HSSFWorkbook wb = new HSSFWorkbook(in); HSSFSheet sheet = wb.getSheetAt(0); int rows = sheet.getLastRowNum(); List<OwnedVehicle> list = new ArrayList<OwnedVehicle>(); // i for (int i = 2; i < rows; i++) { HSSFRow row = sheet.getRow(i); int id = Integer.parseInt(getValue(row.getCell(0))); String vehicleId = getValue(row.getCell(1)); String depid = getValue(row.getCell(2)); String model = getValue(row.getCell(3)); String licenseCode = getValue(row.getCell(4)); OwnedVehicle ov = new OwnedVehicle(); ov.setVehicleId(vehicleId); ov.setId(id); ov.setDepid(depid); ov.setModel(model); ov.setLicenseCode(licenseCode); System.out.println(ov); list.add(ov); } dao.saveImportData(list); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * ? Excel//from www .jav a 2s .c om * * @param excel_name * @return * @throws Exception */ public static List<String[]> readExcel(String excel_name) throws Exception { // List<String[]> list = new ArrayList<String[]>(); 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(); // ?? String[] arrayString = new String[col]; for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString[i] = ""; } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString[i] = formater.format(d); } else { arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue() + ""; } } } else {// EXCEL? arrayString[i] = cell.getStringCellValue().trim(); } } list.add(arrayString); } } return list; }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * ? Excel/*from w ww . ja va 2s . c o m*/ * * @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//from w w w . ja v a 2s . co m * * @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 . jav 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); }//from w w w . ja v a 2s . co 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.mohammad.donkiello.CustomerManager.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);// www. j av a2 s . c o m HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
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 {/* w w w . j av a 2 s . com*/
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;
}
}