List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:hjow.libreference.proxy.PHWorkbook.java
License:Apache License
public PHWorkbook(String sheetName, FileInputStream stream) throws IOException { // TODO : ?? HSSFWorkbook workbook = null; try {//from ww w. jav a2 s.c o m PSheet newSheet = new PSheet(); workbook = new HSSFWorkbook(stream); Sheet sheet = workbook.getSheetAt(0); for (Row r : sheet) { HgRow newRow = new PRow(); for (Cell c : r) { PCell newCell = new PCell(); if (((Cell) c).getCellType() == Column.TYPE_BLANK) { newCell.setCellValue(""); newCell.setType(Column.TYPE_BLANK); } else if (((Cell) c).getCellType() == Column.TYPE_BOOLEAN) { newCell.setCellValue(((Cell) c).getBooleanCellValue()); newCell.setType(Column.TYPE_BOOLEAN); } else if (((Cell) c).getCellType() == Column.TYPE_DATE) { newCell.setCellValue(((Cell) c).getDateCellValue()); newCell.setType(Column.TYPE_DATE); } else if (((Cell) c).getCellType() == Column.TYPE_ERROR) { newCell.setCellValue(""); newCell.setType(Column.TYPE_ERROR); } else if (((Cell) c).getCellType() == Column.TYPE_FLOAT) { newCell.setCellValue(((Cell) c).getNumericCellValue()); newCell.setType(Column.TYPE_FLOAT); } else if (((Cell) c).getCellType() == Column.TYPE_NUMERIC) { newCell.setCellValue(((Cell) c).getNumericCellValue()); newCell.setType(Column.TYPE_NUMERIC); } else if (((Cell) c).getCellType() == Column.TYPE_FORMULA) { newCell.setCellValue(((Cell) c).getCellFormula()); newCell.setType(Column.TYPE_FORMULA); } else if (((Cell) c).getCellType() == Column.TYPE_INTEGER) { newCell.setCellValue((int) ((Cell) c).getNumericCellValue()); newCell.setType(Column.TYPE_INTEGER); } else { newCell.setCellValue(((Cell) c).getStringCellValue()); newCell.setType(Column.TYPE_STRING); } newRow.add(newCell); } newSheet.add(newRow); } newSheet.setName(sheetName); sheets.add(newSheet); } catch (IOException e) { throw e; } finally { try { workbook.close(); } catch (Exception e) { } } }
From source file:hr.restart.sisfun.frmReportxList.java
License:Apache License
void fillDataProc(File orig, HSSFWorkbook wb) { DataSet logo = dM.getDataModule().getLogotipovi(); DataSet orgs = dM.getDataModule().getOrgstruktura(); String corg = jpc.getCorg();/*from w w w . j a v a2 s . com*/ while (!ld.raLocate(logo, "CORG", corg)) { if (!ld.raLocate(orgs, "CORG", corg)) { JOptionPane.showMessageDialog(this.getWindow(), "Greka u organizacijskim jedinicama!", "Greka", JOptionPane.ERROR_MESSAGE); return; } if (orgs.getString("PRIPADNOST").equals(corg)) { JOptionPane.showMessageDialog(this.getWindow(), "Nije definiran logotip za knjigovodstvo!", "Greka", JOptionPane.ERROR_MESSAGE); return; } corg = orgs.getString("PRIPADNOST"); } raProcess.checkClosing(); StorageDataSet gk = Gkstavke.getDataModule().getScopedSet("BROJKONTA ID IP"); raProcess.fillScratchDataSet(gk, "SELECT brojkonta,id,ip FROM gkstavke WHERE " + jpc.getCondition().and(Condition.between("DATUMKNJ", fld, "DATFROM", "DATTO"))); StorageDataSet ogk = Gkstavke.getDataModule().getScopedSet("BROJKONTA ID IP"); Timestamp old = Util.getUtil().addYears(fld.getTimestamp("DATFROM"), -1); raProcess.fillScratchDataSet(ogk, "SELECT brojkonta,id,ip FROM gkstavke WHERE " + jpc.getCondition().and(Condition.between("DATUMKNJ", Util.getUtil().getFirstDayOfYear(old), Util.getUtil().getLastDayOfYear(old)))); gk.enableDataSetEvents(false); gk.setSort(new SortDescriptor(new String[] { "BROJKONTA" })); ogk.enableDataSetEvents(false); ogk.setSort(new SortDescriptor(new String[] { "BROJKONTA" })); HSSFDataFormat df = wb.createDataFormat(); HSSFSheet sh = wb.getSheetAt(0); if (sh == null) throw new RuntimeException("Greka u plahti!"); DataSet rep = Repxdata.getDataModule().getTempSet(Condition.equal("CREP", reps)); rep.open(); raProcess.checkClosing(); for (rep.first(); rep.inBounds(); rep.next()) { HSSFRow hr = sh.getRow((short) (rep.getInt("RED") - 1)); HSSFCell cell = hr.getCell((short) (rep.getInt("KOL") - 1)); if ("S".equals(rep.getString("TIP"))) { fillString(cell, logo, rep.getString("DATA")); cell.getCellStyle().setDataFormat(df.getFormat("text")); } else if ("2".equals(rep.getString("TIP"))) { fillNum(cell, gk, ogk, rep.getString("DATA")); cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00")); } else if ("D".equals(rep.getString("TIP"))) { fillDate(cell, rep.getString("DATA")); cell.getCellStyle().setDataFormat(df.getFormat("dd.mm.yyyy")); } raProcess.checkClosing(); } String oname = orig.getAbsolutePath(); oname = oname.substring(0, oname.length() - 4); FileOutputStream out = null; try { out = new FileOutputStream(oname + "-RA.xls"); wb.write(out); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (out != null) try { out.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
From source file:ia_app.PastPerformancesPanel.java
public PastPerformancesPanel() throws FileNotFoundException, IOException { initComponents();/*w w w.j a v a 2s . co m*/ FileInputStream fis = new FileInputStream(new File("testing.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator(); this.text = ""; for (Row row : sheet) { for (Cell cell : row) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.text = (text + cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: this.text = (text + cell.getStringCellValue() + "\t"); break; } } this.text = (text + "\n"); } this.jTextArea1.setText(text); }
From source file:ia_app.StatsPanel.java
/** * Creates new form StatsPanel/*from ww w .j a v a 2s . co m*/ */ public StatsPanel() throws FileNotFoundException, IOException { initComponents(); FileInputStream fis = new FileInputStream(new File("games.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator(); Row row1 = sheet.getRow(1); for (Cell cell : row1) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.makes += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextFieldMakes.setText(makes + ""); Row row2 = sheet.getRow(2); for (Cell cell : row2) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.attempts += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextFieldAttempts.setText(attempts + ""); this.jTextFieldFGPct.setText("58.3%"); Row row3 = sheet.getRow(3); for (Cell cell : row3) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.threeMakes += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } Row row4 = sheet.getRow(4); for (Cell cell : row4) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.threeAttempts += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextField3Pct.setText("80%"); /*for(Row column : sheet){ for(Cell cell : column){ switch(forEval.evaluateInCell(cell).getCellType()){ case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: break; } } } */ }
From source file:Import.Utils.XSSFConvert.java
public static XSSFWorkbook convertWorkbookHSSFToXSSF(HSSFWorkbook source) { XSSFWorkbook retVal = new XSSFWorkbook(); for (int i = 0; i < source.getNumberOfSheets(); i++) { XSSFSheet xssfSheet = retVal.createSheet(); HSSFSheet hssfsheet = source.getSheetAt(i); copySheets(hssfsheet, xssfSheet); }// w w w .j av a2 s . com return retVal; }
From source file:Importers.ExcelImporter.java
License:Apache License
@Override public DefaultMutableTreeNode readFile(File file) { System.out.println("==ExcelImporter=readFile: " + file.getAbsolutePath()); DefaultMutableTreeNode root = new DefaultMutableTreeNode("vulns"); try {// w ww.ja v a 2 s .c om 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) { // Create a new vuln Vulnerability vuln = new Vulnerability(); vuln.setTitle("NEW"); vuln.setIs_custom_risk(true); vuln.setRisk_category("None"); for (int c = 0; c < cols; c++) { cell = row.getCell(c); if (cell != null) { // Your code here String value = cell.getStringCellValue(); switch (c) { case 1:// title vuln.setTitle(value); break; case 2: // Risk CellStyle style = cell.getCellStyle(); short colorIdx = style.getFillForegroundColor(); HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); HSSFColor color = palette.getColor(colorIdx); String cc = color.getHexString(); System.out.println(cc); if (cc.equalsIgnoreCase("8080:8080:0")) { vuln.setRisk_category("Critical"); } else if (cc.equalsIgnoreCase("FFFF:0:0")) { vuln.setRisk_category("High"); } else if (cc.equalsIgnoreCase("FFFF:6666:0")) { vuln.setRisk_category("Medium"); } else if (cc.equalsIgnoreCase("F2F2:ECEC:0")) { vuln.setRisk_category("Low"); } else if (cc.equalsIgnoreCase("0:0:FFFF")) { vuln.setRisk_category("Info"); } break; case 3:// cvss string System.out.println(value); if (value.equalsIgnoreCase("No CVSS Vector")) { vuln.setIs_custom_risk(true); } else { vuln.setIs_custom_risk(false); vuln.setCvss_vector_string("CVSS2#" + value); } break; case 4://Description vuln.setDescription(value); break; case 5://Recommendation vuln.setRecommendation(value); break; case 6://Affected Hosts try { String[] lines = value.split("\n"); for (String line : lines) { String[] bits = line.split(" "); Host host = new Host(); host.setIp_address(bits[0]); String portprotocol = bits[2]; host.setPortnumber(portprotocol.split("/")[0]); host.setProtocol(portprotocol.split("/")[1]); vuln.addAffectedHost(host); } } catch (Exception ex) { ; } break; } } } System.out.println(vuln); root.add(new DefaultMutableTreeNode(vuln)); } } } catch (Exception ex) { ex.printStackTrace(); } return root; }
From source file:in.igsa.upload.FileUploadAction.java
License:Apache License
public String upload() throws Exception { try {//from w w w . j a va 2 s .c om Map<String, Object> session = ActionContext.getContext().getSession(); List<String> regionNames = new ArrayList<String>(); List<String> modelNames = new ArrayList<String>(); List<String> unitNames = new ArrayList<String>(); List<String> variableNames = new ArrayList<String>(); String emailError = ""; regionNames = service.getRegionNames(); modelNames = service.getModelNames(); unitNames = service.getUnitNames(); variableNames = service.getVariableNames(); String dateTime; Date date = new Date(); dateTime = DateTime.getDateTime1(date); String filePath = servletRequest.getSession().getServletContext().getRealPath("/") + "/files/"; File theFile; parent = new ArrayList<FileUploadVo>(); rowHeader = new FileUploadVo(); System.out.println(" contentType : " + contentType); boolean row_header = true; if ("application/vnd.ms-excel".equalsIgnoreCase(contentType)) { try { theFile = new File(filePath, DateTime.getFileForUpload(date) + ".xls"); if (theFile.exists()) { System.out.println(" file esist "); } else { System.out.println(" null file"); } fileNameForUpload = DateTime.getFileForUpload(date) + ".xls"; FileUtils.copyFile(upload, theFile); FileInputStream file = new FileInputStream(theFile); //new File("\\files\\howtodoinjava_demo.xls")); //Create Workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first/desired sheet from the workbook (.xls) HSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { HSSFRow row = (HSSFRow) rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); child = new FileUploadVo(); yearVal = new ArrayList<String>(); year = new ArrayList<String>(); int i = 1; if (row_header) { while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i > 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: mapHeader.put(i + "", cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: mapHeader.put(i + "", cell.getStringCellValue()); break; } } i++; } row_header = false; } else { while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i == 1) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setModel(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setModel(cell.getStringCellValue()); break; } } else if (i == 2) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setScenario(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setScenario(cell.getStringCellValue()); break; } } else if (i == 3) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setRegion(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setRegion(cell.getStringCellValue()); break; } } else if (i == 4) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setVariable(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setVariable(cell.getStringCellValue()); break; } } else if (i == 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setUnit(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setUnit(cell.getStringCellValue()); break; } } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: yearVal.add(cell.getNumericCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; case Cell.CELL_TYPE_STRING: yearVal.add(cell.getStringCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; } } //parent.add(child); //child.setValue(value); i++; } child.setVal(yearVal); child.setYear(year); child.setDateTime(dateTime); child.setUploadedBy(session.get("user_id").toString()); child.setFilePath("files/" + fileNameForUpload); parent.add(child); } } file.close(); } catch (Exception e) { e.printStackTrace(); } service.deleteModelSceRegion(child); for (FileUploadVo aa : parent) { boolean flag = true; if (!modelNames.contains(aa.getModel())) { emailError = emailError + "ERROR : Model name <i>" + aa.getModel() + "</i> not in list of valid Model name. <br>"; flag = false; } if (!regionNames.contains(aa.getRegion())) { emailError = emailError + "ERROR : Region name <i>" + aa.getRegion() + "</i> not in list of valid Region name. <br>"; flag = false; } if (!variableNames.contains(aa.getVariable())) { emailError = emailError + "ERROR : Variable name <i>" + aa.getVariable() + "</i> not in list of valid Variable name. <br>"; flag = false; } if (!unitNames.contains(aa.getUnit())) { emailError = emailError + "ERROR : Unit name <i>" + aa.getUnit() + "</i> not in list of valid Unit name. <br>"; flag = false; } if (flag) service.insertFileUpload(aa); else emailError = emailError + "********************************************<br>"; } } else if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".equals(contentType)) { try { theFile = new File(filePath, DateTime.getFileForUpload(date) + ".xlsx"); fileNameForUpload = DateTime.getFileForUpload(date) + ".xlsx"; FileUtils.copyFile(upload, theFile); FileInputStream file = new FileInputStream(theFile); //new File("\\files\\howtodoinjava_demo.xls")); //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); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { XSSFRow row = (XSSFRow) rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); child = new FileUploadVo(); yearVal = new ArrayList<String>(); year = new ArrayList<String>(); int i = 1; if (row_header) { while (cellIterator.hasNext()) { XSSFCell cell = (XSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i > 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: mapHeader.put(i + "", cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: mapHeader.put(i + "", cell.getStringCellValue()); break; } } i++; } row_header = false; } else { while (cellIterator.hasNext()) { XSSFCell cell = (XSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i == 1) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setModel(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setModel(cell.getStringCellValue()); break; } } else if (i == 2) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setScenario(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setScenario(cell.getStringCellValue()); break; } } else if (i == 3) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setRegion(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setRegion(cell.getStringCellValue()); break; } } else if (i == 4) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setVariable(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setVariable(cell.getStringCellValue()); break; } } else if (i == 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setUnit(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setUnit(cell.getStringCellValue()); break; } } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: yearVal.add(cell.getNumericCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; case Cell.CELL_TYPE_STRING: yearVal.add(cell.getStringCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; } } //parent.add(child); //child.setValue(value); i++; } child.setVal(yearVal); child.setYear(year); child.setDateTime(dateTime); child.setUploadedBy(session.get("user_id").toString()); child.setFilePath("files/" + fileNameForUpload); parent.add(child); System.out.println("\n"); } } file.close(); } catch (Exception e) { e.printStackTrace(); } service.deleteModelSceRegion(child); for (FileUploadVo aa : parent) { boolean flag = true; if (!modelNames.contains(aa.getModel())) { emailError = emailError + "ERROR : Model name " + aa.getModel() + " not in list of valid Model name. <br>"; flag = false; } if (!regionNames.contains(aa.getRegion())) { emailError = emailError + "ERROR : Region name " + aa.getRegion() + " not in list of valid Region name. <br>"; flag = false; } if (!variableNames.contains(aa.getVariable())) { emailError = emailError + "ERROR : Variable name " + aa.getVariable() + " not in list of valid Variable name. <br>"; flag = false; } if (!unitNames.contains(aa.getUnit())) { emailError = emailError + "ERROR : Unit name " + aa.getUnit() + " not in list of valid Unit name. <br>"; flag = false; } if (flag) service.insertFileUpload(aa); else emailError = emailError + "********************************************<br>"; } } else { addActionError(" File Format Should be xls or xlsx "); scenarioReport = service.getScenarioReport(); return INPUT; } String subject = "Error : undefined parameters"; String content = "Dear " + session.get("user_id").toString() + " <br> <br> here's a brief report about your scenarios data upload to the SSP database." + "Please do open and carefully check the attached log file to find out whether the import was successful.<br><br>Regards,<br>SSP database admin Summary <br><br><br>"; if (emailError != "") SendEmail.send("4igsalabs@gmail.com", session.get("user_email").toString(), subject, content + emailError); } catch (Exception e) { addActionError(e.getCause().getLocalizedMessage()); scenarioReport = service.getScenarioReport(); linkAction(scenarioReport); return INPUT; } scenarioReport = service.getScenarioReport(); linkAction(scenarioReport); return SUCCESS; }
From source file:info.jtrac.domain.ExcelFile.java
License:Apache License
public ExcelFile(InputStream is) { POIFSFileSystem fs = null;//from w ww.j a v a2 s. c o m HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); } catch (Exception e) { throw new RuntimeException(e); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; int row = 0; int col = 0; columns = new ArrayList<Column>(); //========================== HEADER ==================================== // column headings are important, this routine assumes that the first // row is a header row and that reaching an empty cell means end of data r = sheet.getRow(row); while (true) { c = r.getCell((short) col); if (c == null) { break; } String value = c.getStringCellValue(); if (value == null || value.trim().length() == 0) { break; } Column column = new Column(value.trim()); columns.add(column); col++; } //============================ DATA ==================================== rows = new ArrayList<List<Cell>>(); while (true) { row++; r = sheet.getRow(row); if (r == null) { break; } List<Cell> rowData = new ArrayList<>(columns.size()); boolean isEmptyRow = true; for (col = 0; col < columns.size(); col++) { c = r.getCell((short) col); Object value = null; switch (c.getCellType()) { case (HSSFCell.CELL_TYPE_STRING): value = c.getStringCellValue(); break; case (HSSFCell.CELL_TYPE_NUMERIC): // value = c.getDateCellValue(); value = c.getNumericCellValue(); break; case (HSSFCell.CELL_TYPE_BLANK): break; default: // do nothing } if (value != null && value.toString().length() > 0) { isEmptyRow = false; rowData.add(new Cell(value)); } else { rowData.add(new Cell(null)); } } if (isEmptyRow) { break; } rows.add(rowData); } }
From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java
License:Apache License
private static int getSheetIndex(final HSSFWorkbook workbook, final HSSFSheet sheet) { for (int i = workbook.getNumberOfSheets() - 1; i >= 0; i--) { if (sheet.equals(workbook.getSheetAt(i))) { return i; }/*from w w w . ja v a2 s. c om*/ } throw new InvalidParameterException("HSSFSheet non trouv dans le HSSFWorkbook."); }
From source file:is.idega.idegaweb.egov.fsk.business.FSKBusinessBean.java
License:Open Source License
@Override public Map importExcelFile(UploadFile file, Object coursePK, int column) { Map map = new HashMap(); try {//from w ww. jav a 2 s .c o m Course course = getCourse(coursePK); Group group = course.getGroup(); FileInputStream input = new FileInputStream(file.getRealPath()); HSSFWorkbook wb = new HSSFWorkbook(input); HSSFSheet sheet = wb.getSheetAt(0); NumberFormat format = NumberFormat.getNumberInstance(); format.setGroupingUsed(false); format.setMinimumIntegerDigits(10); Collection imported = new ArrayList(); Collection alreadyImported = new ArrayList(); Collection outsideCommune = new ArrayList(); Collection outsideAgeRange = new ArrayList(); Collection invalidPersonalID = new ArrayList(); Collection noUserFound = new ArrayList(); for (int a = sheet.getFirstRowNum(); a <= sheet.getLastRowNum(); a++) { HSSFRow row = sheet.getRow(a); HSSFCell cell = row.getCell((short) (column - 1)); if (cell == null) { continue; } String personalID = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { personalID = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { personalID = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); } else { personalID = cell.getStringCellValue(); } try { personalID = format.format(format.parse(personalID.replaceAll("-", ""))); } catch (ParseException e1) { e1.printStackTrace(); continue; } if (SocialSecurityNumber.isValidSocialSecurityNumber(personalID, getDefaultLocale())) { try { User user = getUserBusiness().getUser(personalID); if (!group.hasRelationTo(((Integer) user.getPrimaryKey()).intValue())) { IWTimestamp dateOfBirth = new IWTimestamp(user.getDateOfBirth()); dateOfBirth.setMonth(1); dateOfBirth.setDay(1); Age age = new Age(dateOfBirth.getDate()); if (age.getYears(course.getStartDate()) < 6 || age.getYears(course.getStartDate()) > 18) { outsideAgeRange.add(user); continue; } if (!getUserBusiness().isCitizenOfDefaultCommune(user)) { outsideCommune.add(user); continue; } group.addGroup(user); imported.add(user); } else { alreadyImported.add(user); } } catch (FinderException e) { noUserFound.add(personalID); } } else { invalidPersonalID.add(personalID); } } map.put(FSKConstants.REGISTRATION_CODE_REGISTERED, imported); map.put(FSKConstants.REGISTRATION_CODE_ALREADY_REGISTERED, alreadyImported); map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_COMMUNE, outsideCommune); map.put(FSKConstants.REGISTRATION_CODE_OUTSIDE_AGE_RANGE, outsideAgeRange); map.put(FSKConstants.REGISTRATION_CODE_INVALID_PERSONAL_ID, invalidPersonalID); map.put(FSKConstants.REGISTRATION_CODE_NO_USER_FOUND, noUserFound); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return map; }