List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.dexter.fuelcard.mbean.UserMBean.java
public void cancelCards() { FacesContext curContext = FacesContextImpl.getCurrentInstance(); if (getUploadItem() != null) { try {//from w ww . j a va2 s . c o m HSSFWorkbook workbook = new HSSFWorkbook(getUploadItem().getInputstream()); int sheetCount = workbook.getNumberOfSheets(); if (sheetCount >= 1) { String password = getRandomDigitPassword(); HSSFSheet sheet = workbook.getSheetAt(0); // first sheet should be the main sheet sheet.protectSheet(password); ByteArrayOutputStream byout = new ByteArrayOutputStream(); workbook.write(byout); byout.close(); CardRequest cr = new CardRequest(); cr.setRequestRefNum(getActiveUser().getPartner().getCode() + "-" + password); cr.setAdditionalComment(getRequestComment()); cr.setCrt_dt(new Date()); cr.setExcelFile(byout.toByteArray()); cr.setPartner(getActiveUser().getPartner()); cr.setRequest_dt(new Date()); cr.setRequestedBy(getActiveUser()); cr.setRequestType("CANCEL-CARDS"); cr.setStatus("PENDING"); GeneralDAO gDAO = new GeneralDAO(); gDAO.startTransaction(); boolean ret = gDAO.save(cr); if (ret) { gDAO.commit(); setRequestComment(null); setMyPendingCardCancelRequests(null); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Success", "Request submitted successfully!")); // Send email to user that submitted that their request is been treated String body = "<html><body><p>Dear " + getActiveUser().getFullname() + ",</p><p>Your request to decommission cards has been recieved and will be treated as soon as possible. You will be notified of every progress.</p><p>Regards<br/>Fuel Card Platform</p></body></html>"; Emailer.sendEmail("fuelcard@sattrakservices.com", new String[] { getActiveUser().getEmail() }, "Card Decommission Request - " + cr.getRequestRefNum() + " Received", body); // Send email to sattrak that request is submitted byte[] doc = cr.getExcelFile(); String body2 = "<html><body><p>Hello,</p><p>A request to decommission cards has been submitted. The document is attached.</p><p>Regards<br/>Fuel Card Platform</p></body></html>"; if (getSattrakPartner() != null && getSattrakPartner().getContactEmails() != null && getSattrakPartner().getContactEmails().trim().length() > 0) { String[] to = getSattrakPartner().getContactEmails().split(","); Emailer.sendEmail("fuelcard@sattrakservices.com", to, "Card Decommission Request - " + cr.getRequestRefNum() + " Submitted", body2, doc, cr.getRequestRefNum() + ".xls", "application/vnd.ms-excel"); } } else { gDAO.rollback(); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", gDAO.getMessage())); } gDAO.destroy(); } else { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Excel document is not valid!")); } } catch (Exception ex) { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Severe", "Please upload an excel document! Error: " + ex.getMessage())); } } else { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Please upload an excel document!")); } }
From source file:com.dexter.fuelcard.mbean.UserMBean.java
public void orderCards() { FacesContext curContext = FacesContextImpl.getCurrentInstance(); if (getUploadItem() != null) { try {//from w w w. j a va2 s.c o m HSSFWorkbook workbook = new HSSFWorkbook(getUploadItem().getInputstream()); int sheetCount = workbook.getNumberOfSheets(); if (sheetCount >= 2) { String password = getRandomDigitPassword(); HSSFSheet sheet = workbook.getSheetAt(0); // first sheet should be the main sheet sheet.protectSheet(password); ByteArrayOutputStream byout = new ByteArrayOutputStream(); workbook.write(byout); byout.close(); CardRequest cr = new CardRequest(); cr.setRequestRefNum(getActiveUser().getPartner().getCode() + "-" + password); cr.setAdditionalComment(getRequestComment()); cr.setCrt_dt(new Date()); cr.setExcelFile(byout.toByteArray()); cr.setPartner(getActiveUser().getPartner()); cr.setRequest_dt(new Date()); cr.setRequestedBy(getActiveUser()); cr.setRequestType("ORDER-CARDS"); cr.setStatus("PENDING"); GeneralDAO gDAO = new GeneralDAO(); gDAO.startTransaction(); boolean ret = gDAO.save(cr); if (ret) { gDAO.commit(); setRequestComment(null); setMyPendingCardOrderRequests(null); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Success", "Request submitted successfully!")); // Send email to user that submitted that their request is been treated String body = "<html><body><p>Dear " + getActiveUser().getFullname() + ",</p><p>Your request to order cards has been recieved and will be treated as soon as possible. You will be notified of every progress.</p><p>Regards<br/>Fuel Card Platform</p></body></html>"; Emailer.sendEmail("fuelcard@sattrakservices.com", new String[] { getActiveUser().getEmail() }, "Card Order Request - " + cr.getRequestRefNum() + " Received", body); // Send email to sattrak that request is submitted byte[] doc = cr.getExcelFile(); String body2 = "<html><body><p>Hello,</p><p>A request to order cards has been submitted. The document is attached.</p><p>Regards<br/>Fuel Card Platform</p></body></html>"; if (getSattrakPartner() != null && getSattrakPartner().getContactEmails() != null && getSattrakPartner().getContactEmails().trim().length() > 0) { String[] to = getSattrakPartner().getContactEmails().split(","); Emailer.sendEmail("fuelcard@sattrakservices.com", to, "Card Order Request - " + cr.getRequestRefNum() + " Submitted", body2, doc, cr.getRequestRefNum() + ".xls", "application/vnd.ms-excel"); } } else { gDAO.rollback(); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", gDAO.getMessage())); } gDAO.destroy(); } else { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Excel document is not valid!")); } } catch (Exception ex) { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Severe", "Please upload an excel document! Error: " + ex.getMessage())); } } else { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Please upload an excel document!")); } }
From source file:com.dexter.fuelcard.mbean.UserMBean.java
@SuppressWarnings("unchecked") public void buckLoadVehicles() { AuditTrail audit = new AuditTrail(); audit.setPartner(getActiveUser().getPartner()); audit.setAuditTime(new java.util.Date()); audit.setActionPerformed("Batch loading vehicles..."); audit.setEntity("CAR"); audit.setUsername(getActiveUser().getUsername()); Vector<Car> loadedCars = new Vector<Car>(); try {//from ww w .ja va 2 s . c o m //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(getVehiclesFile().getInputstream()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { //Get current sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(i); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; // reading the contents // column 1 - zonControlId, 2 - regNumber, 3 - fuelType, 4 - username, 5 - cardPan, 6 - region, 7 - dept, 8 - make, 9 - type, 10 - model, 11 - year, 12 - kmpl, 13 - vehicleCapacity, 14 - calibratedCapacity while (rowIterator.hasNext()) { Row row = rowIterator.next(); String zonControlId = "", regNumber = "", fuelType = "", username = ""; String cardPan = "", region = "", dept = "", make = "", type = ""; String model = "", year = "", kmpl = "", vehicleCapacity = "", calibratedCapacity = ""; if (pos > 1) // skip the first row, should be headers { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: zonControlId = val; break; case 1: regNumber = val; break; case 2: fuelType = val; break; case 3: username = val; break; case 4: cardPan = val; break; case 5: region = val; break; case 6: dept = val; break; case 7: make = val; break; case 8: type = val; break; case 9: model = val; break; case 10: year = val; break; case 11: kmpl = val; break; case 12: vehicleCapacity = val; break; case 13: calibratedCapacity = val; break; } } try { Car e = new Car(); e.setCalibratedCapacity( (calibratedCapacity != null) ? Double.parseDouble(calibratedCapacity) : 0); e.setCardPan(cardPan); e.setFuelType(fuelType); e.setPartner(getActiveUser().getPartner()); e.setRegNumber(regNumber); try { e.setVehicleCapacity( (vehicleCapacity != null) ? Double.parseDouble(vehicleCapacity) : 0); } catch (Exception ex) { } try { e.setZonControlId((zonControlId != null) ? Integer.parseInt(zonControlId) : 0); } catch (Exception ex) { } if (username != null) { User u = new UserDAO().getUserByUsername(username, getActiveUser().getPartner().getCode()); if (u != null) { e.setAssigned(true); e.setAssignedUser(u); } } if (region != null) { RegionDAO regDAO = new RegionDAO(); Region reg = regDAO.getRegionByName(region); if (reg == null) { reg = new Region(); reg.setName(region); reg.setPartner(getActiveUser().getPartner()); regDAO.save(reg); } e.setRegion(reg); } if (dept != null) { DepartmentDAO dDAO = new DepartmentDAO(); Department d = dDAO.getDepartmentByName(dept); if (d == null) { d = new Department(); d.setName(dept); d.setPartner(getActiveUser().getPartner()); dDAO.save(d); } e.setDepartment(d); } if (make != null && type != null && model != null) { VehicleMake m = null; VehicleType t = null; VehicleModel md = null; GeneralDAO gDAO = new GeneralDAO(); Hashtable<String, Object> params = new Hashtable<String, Object>(); params.put("name", make); params.put("partner", getActiveUser().getPartner()); Object retObj = gDAO.search("VehicleMake", params); if (retObj != null) { Vector<VehicleMake> retList = (Vector<VehicleMake>) retObj; for (VehicleMake ret : retList) m = ret; } if (m == null) { m = new VehicleMake(); m.setName(make); m.setPartner(getActiveUser().getPartner()); new PlainDAO().save(m); } params = new Hashtable<String, Object>(); params.put("name", type); params.put("partner", getActiveUser().getPartner()); retObj = gDAO.search("VehicleType", params); if (retObj != null) { Vector<VehicleType> retList = (Vector<VehicleType>) retObj; for (VehicleType ret : retList) t = ret; } if (t == null) { t = new VehicleType(); t.setName(type); t.setPartner(getActiveUser().getPartner()); new PlainDAO().save(t); } params = new Hashtable<String, Object>(); params.put("name", model); params.put("make", m); params.put("type", t); params.put("year", year); params.put("partner", getActiveUser().getPartner()); retObj = gDAO.search("VehicleModel", params); if (retObj != null) { Vector<VehicleModel> retList = (Vector<VehicleModel>) retObj; for (VehicleModel ret : retList) md = ret; } if (md == null) { md = new VehicleModel(); md.setName(model); try { md.setKmpl((kmpl != null && kmpl.trim().length() > 0) ? Double.parseDouble(kmpl) : 0); } catch (Exception ex) { } md.setMake(m); md.setType(t); md.setYear(year); md.setPartner(getActiveUser().getPartner()); new PlainDAO().save(md); } e.setModel(md); } loadedCars.add(e); } catch (Exception ig) { } } else pos += 1; } } setAllusers(null); } catch (Exception ex) { ex.printStackTrace(); } audit.setActionPerformed(audit.getActionPerformed() + " Loaded: " + loadedCars.size()); int success = 0, failed = 0; CarDAO cDAO = new CarDAO(); for (Car e : loadedCars) { if (cDAO.createCar(e)) { success += 1; if (e.getCardPan() != null) { Card card = new Card(); card.setCardPan(getVehicle().getCardPan()); card.setCrt_dt(new Date()); new PlainDAO().save(card); } } else failed += 1; } audit.setActionPerformed(audit.getActionPerformed() + " Success: " + success + ", Failed: " + failed); FacesContext curContext = FacesContextImpl.getCurrentInstance(); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Done", "Loaded: " + loadedCars.size() + ", Success: " + success + ", Failed: " + failed)); new AuditDAO().save(audit); setVehicles(null); }
From source file:com.dexter.fuelcard.mbean.UserMBean.java
public void buckLoadUsers() { AuditTrail audit = new AuditTrail(); audit.setPartner(getActiveUser().getPartner()); audit.setAuditTime(new java.util.Date()); audit.setActionPerformed("Batch loading users..."); audit.setEntity("USER"); audit.setUsername(getActiveUser().getUsername()); Vector<User> loadedUsers = new Vector<User>(); try {/* w w w . j a va2 s. c om*/ /*InputStream inputstream = getUploadItem().getInputstream(); ByteArrayOutputStream fos = new ByteArrayOutputStream(); int read = 0; byte[] bytes = new byte[1024]; while ((read = inputstream.read(bytes)) != -1) { fos.write(bytes, 0, read); } fos.close(); ByteArrayInputStream byteIn = new ByteArrayInputStream(fos.toByteArray());*/ //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(getUploadItem().getInputstream()); RoleDAO rDAO = new RoleDAO(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { //Get current sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(i); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; // reading the contents // column 1 - username, 2 - password, 3 - full name, 4 - role id, 5 - email, 6 - mobile number, 7 - Vehicle Reg Number while (rowIterator.hasNext()) { Row row = rowIterator.next(); String username = "", password = "", fullname = "", email = "", mobile = "", regNumber = ""; long role_id = 0L; if (pos > 1) // skip the first row, should be headers { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: username = val; break; case 1: password = val; break; case 2: fullname = val; break; case 3: try { role_id = Long.parseLong(val); } catch (Exception ex) { ex.printStackTrace(); } break; case 4: email = val; break; case 5: mobile = val; break; case 6: regNumber = val; break; } } try { User e = new User(); e.setUsername(username); e.setPassword(password); e.setFullname(fullname); e.setEmail(email); e.setMobileNumber(mobile); e.setActive(true); e.setRegNumber(regNumber); Role role = rDAO.getRoleById(role_id); if (role != null) { e.setRole(role); loadedUsers.add(e); } } catch (Exception ig) { } } else pos += 1; } } setAllusers(null); } catch (Exception ex) { ex.printStackTrace(); } audit.setActionPerformed(audit.getActionPerformed() + " Loaded: " + loadedUsers.size()); int success = 0, failed = 0; UserDAO uDAO = new UserDAO(); for (User e : loadedUsers) { String ret = uDAO.createUser(e); if (ret.indexOf("Success") >= 0) { success += 1; if (e.getRegNumber() != null) { CarDAO cDAO = new CarDAO(); Car c = cDAO.getCarByRegNumber(e.getRegNumber()); if (c != null) { c.setAssigned(true); c.setAssignedUser(e); cDAO.updateCar(c); } } } else failed += 1; } audit.setActionPerformed(audit.getActionPerformed() + " Success: " + success + ", Failed: " + failed); FacesContext curContext = FacesContextImpl.getCurrentInstance(); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Done", "Loaded: " + loadedUsers.size() + ", Success: " + success + ", Failed: " + failed)); new AuditDAO().save(audit); setAllusers(null); }
From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java
License:Apache License
public void convertEDD(EntityFactory ef, RuleSet rs, String excelFileName) throws Exception { InputStream input = new FileInputStream(new File(excelFileName)); // If the EDD is an XML file, We assume no conversion is necessary. if (excelFileName.endsWith(".xml")) { ef.loadedd(rs.newSession(), excelFileName, input); // Transfer bytes from in to out return;/* ww w. j a v a2 s . c om*/ } else if (!(excelFileName.endsWith(".xls"))) throw new Exception("EDD Excel File name is invalid"); // If the EDD is an Excel file, we need to convert the thing. HSSFWorkbook wb = new HSSFWorkbook(input); HSSFSheet sheet = wb.getSheetAt(0); // Open the EDD.xml output file String tmpEDDfilename = rs.getWorkingdirectory() + tmpEDD; XMLPrinter xout = new XMLPrinter(new FileOutputStream(tmpEDDfilename)); // Write out a header in the EDD xml file. xout.opentag("edd_header"); xout.printdata("edd_create_stamp", new SimpleDateFormat("EEE, d MMM yyyy HH:mm:ss Z").format(new Date())); xout.printdata("Excel_File_Name", excelFileName); xout.closetag(); xout.opentag("edd"); // Get the indexes of the columns we need to write out the XML for this EDD. int rows = sheet.getLastRowNum(); int entityIndex = findvalue("entity", sheet, 0); int attributeIndex = findvalue("attribute", sheet, 0); int typeIndex = findvalue("type", sheet, 0); int subtypeIndex = findvalue("subtype", sheet, 0); int defaultIndex = findvalue("defaultvalue", sheet, 0); int inputIndex = findvalue("input", sheet, 0); int accessIndex = findvalue("access", sheet, 0); int commentIndex = findvalue("comment", sheet, 0); // optional int sourceIndex = findvalue("source", sheet, 0); // optional // Some columns we just have to have. Make sure we have them here. if (entityIndex < 0 || attributeIndex < 0 || typeIndex < 0 || defaultIndex < 0 || accessIndex < 0 || inputIndex < 0) { String err = " Couldn't find the following column header(s): " + (entityIndex < 0 ? " entity" : "") + (attributeIndex < 0 ? " attribute" : "") + (typeIndex < 0 ? " type" : "") + (defaultIndex < 0 ? " default value" : "") + (accessIndex < 0 ? " access" : "") + (inputIndex < 0 ? " input" : ""); throw new Exception("This EDD may not be valid, as we didn't find the proper column headers\n" + err); } // Go through each row, writing out each entry to the XML. for (int row = 1; row <= rows; row++) { String entityname = getCellValue(sheet, row, entityIndex); // Skip all the rows that have no Entity if (entityname.length() > 0) { String src = sourceIndex >= 0 ? getCellValue(sheet, row, sourceIndex) : ""; String comment = commentIndex >= 0 ? getCellValue(sheet, row, commentIndex) : ""; xout.opentag("entry"); xout.opentag("entity", "entityname", entityname, "attribute", getCellValue(sheet, row, attributeIndex), "type", getCellValue(sheet, row, typeIndex), "subtype", getCellValue(sheet, row, subtypeIndex), "default", getCellValue(sheet, row, defaultIndex), "access", getCellValue(sheet, row, accessIndex), "input", getCellValue(sheet, row, inputIndex), "comment", getCellValue(sheet, row, commentIndex)); xout.closetag(); if (comment.length() > 0) xout.printdata("comment", getCellValue(sheet, row, commentIndex)); if (src.length() > 0) xout.printdata("source", getCellValue(sheet, row, sourceIndex)); xout.closetag(); } } xout.closetag(); xout.close(); convertEDD(ef, rs, tmpEDDfilename); }
From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java
License:Apache License
/** * Reads the decision table out of an Excel spreadsheet and generates the * approriate XML. /* w w w. j a v a 2 s. c o m*/ * @param file * @param sb * @return true if at least one decision table was found in this file * @throws Exception */ public boolean convertDecisionTable(StringBuffer data, File file, XMLPrinter out, int depth) throws Exception { if (!(file.getName().endsWith(".xls"))) return false; InputStream input = new FileInputStream(file.getAbsolutePath()); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); boolean tablefound = false; CountsAreDirty = false; for (int i = 0; i < wb.getNumberOfSheets(); i++) { tablefound |= convertOneSheet(data, file.getName(), wb.getSheetAt(i), out, depth); } if (CountsAreDirty == true) { System.out.println( "Line Numbers on Contexts, Initial Actions, Conditions, and/or Actions are incorrect.\r\n" + "A Corrected version has been written to the decision table directory"); OutputStream output = new FileOutputStream(file.getAbsolutePath() + ".fixedCounts"); wb.write(output); } else { (new File(file.getAbsolutePath() + ".fixedCounts")).delete(); } return tablefound; }
From source file:com.duroty.lucene.parser.MSExcelParser.java
License:Open Source License
/** * DOCUMENT ME!/* ww w . ja va2 s . co m*/ * * @return DOCUMENT ME! * * @throws ParserException DOCUMENT ME! */ private String getContents() throws ParserException { String contents = ""; try { POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workbook = new HSSFWorkbook(fs); StringBuffer buffer = new StringBuffer(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String num = Double.toString(cell.getNumericCellValue()).trim(); if (num.length() > 0) { buffer.append(num + " "); } break; case HSSFCell.CELL_TYPE_STRING: String text = cell.getStringCellValue().trim(); if (text.length() > 0) { buffer.append(text + " "); } break; } } buffer.append("\n"); /*if (sleep > 0) { try { Thread.sleep(sleep); } catch (Exception ex) { } }*/ } } contents = buffer.toString(); } catch (Exception ex) { throw new ParserException(ex); } return contents; }
From source file:com.eastsoft.ui.MainUI.java
License:Open Source License
void savePrintInfoToExcel(recordInfo recordinfor, int printFormat) { if (printFormat != 0) { HSSFWorkbook wb = null; File printRecord = new File("?.xls"); if (!printRecord.exists()) { wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("sheet1"); HSSFRow row = sheet1.createRow(0); row.createCell((short) 0).setCellValue("? "); row.createCell((short) 1).setCellValue(" "); row.createCell((short) 2).setCellValue("?? "); row.createCell((short) 3).setCellValue("?? "); row.createCell((short) 4).setCellValue("Aid "); row.createCell((short) 5).setCellValue("? "); HSSFRow row1 = sheet1.createRow(sheet1.getLastRowNum() + 1); row1.createCell((short) 0).setCellValue(recordinfor.getDate()); row1.createCell((short) 1).setCellValue(recordinfor.getProductSerialNO()); row1.createCell((short) 2).setCellValue(recordinfor.getDevice()); row1.createCell((short) 3).setCellValue(recordinfor.getProduct()); row1.createCell((short) 4).setCellValue(recordinfor.getAid()); row1.createCell((short) 5).setCellValue(recordinfor.getPasswd()); } else {// w w w .j a va 2s.com FileInputStream fs = null; try { fs = new FileInputStream(printRecord); } catch (FileNotFoundException e2) { // TODO Auto-generated catch block e2.printStackTrace(); } POIFSFileSystem ps = null; try { ps = new POIFSFileSystem(fs); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { wb = new HSSFWorkbook(ps); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } HSSFSheet sheet = wb.getSheetAt(0); // ?cell,Rows 0(Create a row and put some cells // in // it. Rows are 0 based.) HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1); row.createCell((short) 0).setCellValue(recordinfor.getDate()); row.createCell((short) 1).setCellValue(recordinfor.getProductSerialNO()); row.createCell((short) 2).setCellValue(recordinfor.getDevice()); row.createCell((short) 3).setCellValue(recordinfor.getProduct()); row.createCell((short) 4).setCellValue(recordinfor.getAid()); row.createCell((short) 5).setCellValue(recordinfor.getPasswd()); } FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(printRecord); } catch (FileNotFoundException e) { // TODO Auto-generated catch block appendTextareaText(jTextArea_status, "\n?.xls ??"); JOptionPane.showMessageDialog(getParent(), "?.xls ??", "?.xls", JOptionPane.WARNING_MESSAGE); e.printStackTrace(); } try { wb.write(fileOut); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { fileOut.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelImportStudyServiceImpl.java
License:Open Source License
private void importDataToWorkbook(HSSFWorkbook xlsBook, Workbook workbook) { if (workbook.getObservations() != null) { HSSFSheet observationSheet = xlsBook.getSheetAt(1); int xlsRowIndex = 1; //row 0 is the header row for (MeasurementRow wRow : workbook.getObservations()) { HSSFRow xlsRow = observationSheet.getRow(xlsRowIndex); for (MeasurementData wData : wRow.getDataList()) { String label = wData.getLabel(); int xlsColIndex = findColumn(observationSheet, label); Cell cell = xlsRow.getCell(xlsColIndex); String xlsValue = ""; if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Double doubleVal = Double.valueOf(cell.getNumericCellValue()); Integer intVal = Integer.valueOf(doubleVal.intValue()); if (Double.parseDouble(intVal.toString()) == doubleVal.doubleValue()) { xlsValue = intVal.toString(); } else { xlsValue = doubleVal.toString(); }// w ww. j a va 2s . c om } else xlsValue = cell.getStringCellValue(); } wData.setValue(xlsValue); } xlsRowIndex++; } } }
From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelImportStudyServiceImpl.java
License:Open Source License
private void validate(HSSFWorkbook xlsBook, Workbook workbook) throws WorkbookParserException { HSSFSheet descriptionSheet = xlsBook.getSheetAt(0); HSSFSheet observationSheet = xlsBook.getSheetAt(1); validateNumberOfSheets(xlsBook);/* w w w . j ava 2s . c om*/ validateDescriptionSheetFirstCell(descriptionSheet); validateSections(descriptionSheet); validateRequiredObservationColumns(observationSheet, workbook); validateNumberOfRows(observationSheet, workbook); validateRowIdentifiers(observationSheet, workbook); validateObservationColumns(getAllVariates(descriptionSheet), workbook); }