List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook(InputStream s) throws IOException
From source file:com.helger.poi.excel.ExcelReadUtils.java
License:Apache License
/** * Try to read an Excel {@link Workbook} from the passed * {@link IInputStreamProvider}. First XLS is tried, than XLSX, as XLS files * can be identified more easily./*from ww w . ja va2 s . c o m*/ * * @param aIIS * The input stream provider to read from. * @return <code>null</code> if the content of the InputStream could not be * interpreted as Excel file */ @Nullable public static Workbook readWorkbookFromInputStream(@Nonnull final IInputStreamProvider aIIS) { InputStream aIS = null; try { // Try to read as XLS aIS = aIIS.getInputStream(); if (aIS == null) { // Failed to open input stream -> no need to continue return null; } return new HSSFWorkbook(aIS); } catch (final IOException ex) { s_aLogger.error("Error trying to read XLS file from " + aIIS, ex); } catch (final OfficeXmlFileException ex) { // No XLS -> try XSLS StreamUtils.close(aIS); try { // Re-retrieve the input stream, to ensure we read from the beginning! aIS = aIIS.getInputStream(); return new XSSFWorkbook(aIS); } catch (final IOException ex2) { s_aLogger.error("Error trying to read XLSX file from " + aIIS, ex); } catch (final POIXMLException ex2) { // No XLSX either -> no valid Excel file } } finally { // Ensure the InputStream is closed. The data structures are in memory! StreamUtils.close(aIS); } return null; }
From source file:com.hl7.main.ImportDataFromXLStoXML.java
public void displayFromExcel(String xlsPath) { InputStream inputStream = null; try {/*from ww w . ja va 2s. c om*/ inputStream = new FileInputStream(xlsPath); } catch (FileNotFoundException e) { System.out.println("File not found in the specified path."); e.printStackTrace(); } POIFSFileSystem fileSystem = null; try { // Initializing the XML document DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document document = builder.newDocument(); Element rootElement = document.createElement("categories"); document.appendChild(rootElement); fileSystem = new POIFSFileSystem(inputStream); HSSFWorkbook workBook = new HSSFWorkbook(fileSystem); Integer count = workBook.getNumberOfSheets(); ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>(); for (int i = 0; i < count; i++) { HSSFSheet sheet = workBook.getSheetAt(i); Iterator<?> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator<?> cells = row.cellIterator(); ArrayList<String> rowData = new ArrayList<String>(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); String richTextString = cell.getStringCellValue(); System.out.println("String: " + richTextString); rowData.add(richTextString); } // end while data.add(rowData); } // end while } int numOfProduct = data.size(); for (int i = 0; i < numOfProduct; i++) { Element productElement = document.createElement("categoryName"); rootElement.appendChild(productElement); int index = 0; for (String s : data.get(i)) { String headerString = data.get(0).get(index); if (data.get(0).get(index).equals("image link")) { headerString = "image_link"; } if (data.get(0).get(index).equals("product type")) { headerString = "product_type"; } Element headerElement = document.createElement(headerString); productElement.appendChild(headerElement); headerElement.appendChild(document.createTextNode(s)); index++; } } TransformerFactory tFactory = TransformerFactory.newInstance(); Transformer transformer = tFactory.newTransformer(); // Add indentation to output transformer.setOutputProperty(OutputKeys.INDENT, "yes"); transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2"); DOMSource source = new DOMSource(document); StreamResult result = new StreamResult(new File("products.xml")); // StreamResult result = new StreamResult(System.out); transformer.transform(source, result); } catch (IOException e) { System.out.println("IOException " + e.getMessage()); } catch (ParserConfigurationException e) { System.out.println("ParserConfigurationException " + e.getMessage()); } catch (TransformerConfigurationException e) { System.out.println("TransformerConfigurationException " + e.getMessage()); } catch (TransformerException e) { System.out.println("TransformerException " + e.getMessage()); } }
From source file:com.hp.action.CustomerAction.java
public String addCustomerFromExcelFile() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize/*from w w w . j av a2 s . com*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } String saveName = (String) session.getAttribute("upload-name-file"); System.out.println("Get Attribute file name: " + saveName); if (saveName == null) return SUCCESS; int total = 0; int totalFail = 0; String reason = ""; //Import data try { String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/"); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows(); System.out.println("ROWs number" + rows); int cols = 0; // No of columns (max) int temp = 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) { temp = sheet.getRow(i).getPhysicalNumberOfCells(); if (temp > cols) cols = temp; } } for (int i = 1; i < rows; i++) { row = sheet.getRow(i); System.out.println("__ Rows: " + (i + 1)); if (row != null) { System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells()); //If the customer id null if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0 ) { continue; } //Init Customer Object Customer customer = new Customer(); // customer.setmStt((int)row.getCell(tmp++).getNumericCellValue()); //System.out.println(row.getCell(1).getStringCellValue()); if (row.getCell(1) != null && !row.getCell(1).getStringCellValue().equals("")) { Customer customer2 = customerDAO.loadCustomer(row.getCell(1).getStringCellValue()); if (customer2 != null) { reason += "Khch hng <b>" + row.getCell(1).getStringCellValue() + "</b> tn ti. "; } } else reason += "M Khch hng hng " + (i + 1) + " khng c trng. "; if (row.getCell(9) != null && !row.getCell(9).getStringCellValue().equals("")) { Staff staff = staffDAO.loadStaff(row.getCell(9).getStringCellValue()); if (staff == null) reason += "M Nhn vin <b>" + row.getCell(9).getStringCellValue() + "</b> khng tn ti. "; } else reason += "M Nhn vin hng " + (i + 1) + " khng c trng."; try { if (row.getCell(1) != null) customer.setMaDoiTuong(row.getCell(1).getStringCellValue()); if (row.getCell(2) != null) customer.setDoiTuong(row.getCell(2).getStringCellValue()); if (row.getCell(3) != null) customer.setTinhThanh(row.getCell(3).getStringCellValue()); if (row.getCell(4) != null) customer.setX(row.getCell(4).getStringCellValue()); if (row.getCell(5) != null) customer.setDiaChi(row.getCell(5).getStringCellValue()); if (row.getCell(6) != null) customer.setTuyenBanHangThu(row.getCell(6).getStringCellValue()); if (row.getCell(7) != null) customer.setDienThoai(row.getCell(7).getStringCellValue()); //System.out.println(row.getCell(7).getStringCellValue()); if (row.getCell(8) != null) customer.setFax(row.getCell(8).getStringCellValue()); if (row.getCell(9) != null) customer.setMaNhanVien(row.getCell(9).getStringCellValue()); if (row.getCell(10) != null) customer.setGhiChu(row.getCell(10).getStringCellValue()); if (row.getCell(11) != null) { if (row.getCell(11).getCellType() != HSSFCell.CELL_TYPE_STRING) customer.setCoordinateX(row.getCell(11).getNumericCellValue()); else customer.setCoordinateX(Double.parseDouble(row.getCell(12).getStringCellValue())); } if (row.getCell(12) != null) { if (row.getCell(12).getCellType() != HSSFCell.CELL_TYPE_STRING) customer.setCoordinateY(row.getCell(12).getNumericCellValue()); else customer.setCoordinateY(Double.parseDouble(row.getCell(12).getStringCellValue())); } //Add to database if (customerDAO.saveOrUpdate(customer)) { System.out.println("Add Object " + i); total++; customersTotal = total; } else { totalFail++; continue; } } catch (Exception e) { e.printStackTrace(); totalFail++; continue; } } } } catch (Exception ioe) { ioe.printStackTrace(); return SUCCESS; } reasonFail = reason; customersTotal = total; importFail = totalFail; return SUCCESS; }
From source file:com.hp.action.ProductsAction.java
public String addProductFromExcelFile() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize/* w w w . ja va 2s .co m*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } String saveName = (String) session.getAttribute("upload-name-file-product"); System.out.println("Get Attribute file name: " + saveName); if (saveName == null) return SUCCESS; DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); int total = 0; //Import data try { String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/"); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows(); System.out.println("ROWs number" + rows); int cols = 0; // No of columns (max) int temp = 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) { temp = sheet.getRow(i).getPhysicalNumberOfCells(); if (temp > cols) cols = temp; } } for (int i = 1; i < rows; i++) { row = sheet.getRow(i); System.out.println("__ Rows: " + (i + 1)); if (row != null) { System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells()); //If the product id null if (row.getCell(1) == null || (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING && row.getCell(1).getStringCellValue().compareTo("") == 0) || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING && row.getCell(1).getNumericCellValue() <= 0) ) { continue; } //Init Product Object Product product = new Product(); // try { if (row.getCell(1) != null) { if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING) product.setProductID( (new BigDecimal(row.getCell(1).getNumericCellValue())).toString()); else product.setProductID(row.getCell(1).getStringCellValue()); } if (row.getCell(2) != null) { if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING) product.setBarcode( (new BigDecimal(row.getCell(2).getNumericCellValue())).toString()); else product.setBarcode(row.getCell(2).getStringCellValue()); } if (row.getCell(3) != null) product.setProductName(row.getCell(3).getStringCellValue()); if (row.getCell(4) != null) product.setBrand(row.getCell(4).getStringCellValue()); if (row.getCell(5) != null) product.setOrigin(row.getCell(5).getStringCellValue()); if (row.getCell(6) != null) { if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING) product.setPackingSpecifications(row.getCell(6).getNumericCellValue() + ""); else product.setPackingSpecifications(row.getCell(6).getStringCellValue()); } if (row.getCell(7) != null) { product.setQuantification(row.getCell(7).getStringCellValue()); } if (row.getCell(8) != null) { if (row.getCell(8).getCellType() != HSSFCell.CELL_TYPE_STRING) product.setVatTax((float) row.getCell(8).getNumericCellValue()); else product.setVatTax(Float.parseFloat(row.getCell(8).getStringCellValue())); } if (row.getCell(9) != null) { if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING) product.setImportPrices((float) row.getCell(9).getNumericCellValue()); else product.setImportPrices(Float.parseFloat(row.getCell(9).getStringCellValue())); } if (row.getCell(10) != null) { if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING) product.setExportPrices((float) row.getCell(10).getNumericCellValue()); else product.setExportPrices(Float.parseFloat(row.getCell(10).getStringCellValue())); } if (row.getCell(11) != null) product.setProvider(row.getCell(11).getStringCellValue()); if (row.getCell(12) != null) product.setDescription(row.getCell(12).getStringCellValue()); if (row.getCell(13) != null) product.setProductImage(row.getCell(13).getStringCellValue()); //product.setMProvider("nhacungcap1"); //Add to database if (productDAO.saveOrUpdate(product)) { System.out.println("Add Object " + (i + 1)); total++; productsTotal = total; } else { int t = 0; continue; } } catch (Exception e) { e.printStackTrace(); continue; } } } } catch (Exception ioe) { ioe.printStackTrace(); return SUCCESS; } productsTotal = total; return SUCCESS; }
From source file:com.hp.action.ProviderAction.java
public String addProviderFromExcelFile() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize//from w w w . j av a 2 s.c o m if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } String saveName = (String) session.getAttribute("upload-name-file-provider"); System.out.println("Get Attribute file name: " + saveName); if (saveName == null) return SUCCESS; DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); int total = 0; //Import data try { String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/"); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows(); System.out.println("ROWs number" + rows); int cols = 0; // No of columns (max) int temp = 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) { temp = sheet.getRow(i).getPhysicalNumberOfCells(); if (temp > cols) cols = temp; } } for (int i = 1; i < rows; i++) { row = sheet.getRow(i); System.out.println("__ Rows: " + (i + 1)); if (row != null) { System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells()); //If the product id null if (row.getCell(1) == null || (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING && row.getCell(1).getStringCellValue().compareTo("") == 0) || (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING && row.getCell(1).getNumericCellValue() <= 0) ) { continue; } //Init Product Object Provider provider = new Provider(); // try { if (row.getCell(1) != null) { if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_STRING) provider.setId((new BigDecimal(row.getCell(1).getNumericCellValue())).toString()); else provider.setId(row.getCell(1).getStringCellValue()); } if (row.getCell(2) != null) { if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_STRING) provider.setName((new BigDecimal(row.getCell(2).getNumericCellValue())).toString()); else provider.setName(row.getCell(2).getStringCellValue()); } if (row.getCell(3) != null) provider.setAddress(row.getCell(3).getStringCellValue()); if (row.getCell(4) != null) provider.setPhoneNumber(row.getCell(4).getStringCellValue()); if (row.getCell(5) != null) provider.setFax(row.getCell(5).getStringCellValue()); if (row.getCell(6) != null) { if (row.getCell(6).getCellType() != HSSFCell.CELL_TYPE_STRING) provider.setNote(row.getCell(6).getNumericCellValue() + ""); else provider.setNote(row.getCell(6).getStringCellValue()); } //Add to database if (providerDAO.saveOrUpdate(provider)) { System.out.println("Add Object " + (i + 1)); total++; providersTotal = total; } else { int t = 0; continue; } } catch (Exception e) { e.printStackTrace(); continue; } } } } catch (Exception ioe) { ioe.printStackTrace(); return SUCCESS; } providersTotal = total; return SUCCESS; }
From source file:com.hp.action.StaffsAction.java
public String addStaffFromExcelFile() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize//from w w w . j a va 2 s . c om if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } String saveName = (String) session.getAttribute("upload-name-file-staff"); System.out.println("Get Attribute file name: " + saveName); if (saveName == null) return SUCCESS; DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); int total = 0; //Import data try { String fileInput = ServletActionContext.getServletContext().getRealPath("/db_inputs/" + saveName + "/"); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileInput)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows(); System.out.println("ROWs number" + rows); int cols = 0; // No of columns (max) int temp = 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) { temp = sheet.getRow(i).getPhysicalNumberOfCells(); if (temp > cols) cols = temp; } } for (int i = 1; i < rows; i++) { row = sheet.getRow(i); System.out.println("__ Rows: " + (i + 1)); if (row != null) { System.out.println("__ Row: " + (i + 1) + " ,Cell number: " + row.getPhysicalNumberOfCells()); //If the staff id null if (row.getCell(1) == null || row.getCell(1).getStringCellValue().compareTo("") == 0 ) { continue; } //Init Staff Object Staff staff = new Staff(); try { if (row.getCell(1) != null) staff.setId(row.getCell(1).getStringCellValue()); if (row.getCell(2) != null) staff.setPw(row.getCell(2).getStringCellValue()); if (row.getCell(3) != null) staff.setName(row.getCell(3).getStringCellValue()); if (row.getCell(4) != null) staff.setAdress(row.getCell(4).getStringCellValue()); if (row.getCell(5) != null) staff.setJob(row.getCell(5).getStringCellValue()); if (row.getCell(6) != null) staff.setPhone(row.getCell(6).getStringCellValue()); if (row.getCell(7) != null && row.getCell(7).getStringCellValue().compareTo("") != 0) { staff.setDate(df.parse(row.getCell(7).getStringCellValue())); } if (row.getCell(8) != null) staff.setManager(row.getCell(8).getStringCellValue()); if (row.getCell(9) != null) { if (row.getCell(9).getCellType() != HSSFCell.CELL_TYPE_STRING) staff.setStatus(row.getCell(9).getNumericCellValue() == 1 ? true : false); else staff.setStatus( row.getCell(9).getStringCellValue().compareTo("1") == 0 ? true : false); } if (row.getCell(10) != null) { if (row.getCell(10).getCellType() != HSSFCell.CELL_TYPE_STRING) staff.setPermission((int) row.getCell(10).getNumericCellValue()); else staff.setPermission(Integer.parseInt(row.getCell(10).getStringCellValue())); } //Add to database if (staffDAO.saveOrUpdate(staff)) { System.out.println("Add Object " + (i + 1)); total++; staffsTotal = total; } else continue; } catch (Exception e) { e.printStackTrace(); continue; } } } } catch (Exception ioe) { ioe.printStackTrace(); return SUCCESS; } staffsTotal = total; return SUCCESS; }
From source file:com.hp.excelhandle.GetData.java
public void readExcel() { try {// w w w. j a v a 2 s.com POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("database/customer.xls")); 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 (max) 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 = 0; r < rows; r++) { // row = sheet.getRow(r); // if(row != null) { // for(int c = 0; c < cols; c++) { // cell = row.getCell((short)c); // if(cell != null) { // // Your code here // } // } // } // } row = sheet.getRow(8); if (row != null) { cell = row.getCell(1); if (cell != null) System.out.println("Row: " + 9 + ", Data: " + cell.getStringCellValue()); } } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:com.hp.excelhandle.GetData.java
public ArrayList<Customer> loadCustomer() { ArrayList<Customer> listCustomer = new ArrayList(); try {/* www.ja v a 2s . co m*/ POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(mFileInput)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getLastRowNum() + 1; //getPhysicalNumberOfRows(); System.out.println("ROWs number" + rows); System.out.println("Cell value: " + sheet.getRow(rows - 1).getCell(0)); int cols = 0; // No of columns (max) int temp = 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) { temp = sheet.getRow(i).getPhysicalNumberOfCells(); if (temp > cols) cols = temp; } } for (int i = 8; i < rows; i++) { row = sheet.getRow(i); if (row != null) { //If the customer id null if (row.getCell(ConfigFile.MA_DOI_TUONG_COL) == null || row.getCell(ConfigFile.X_COORDINATES_COL) == null || row.getCell(ConfigFile.Y_COORDINATES_COL) == null) { continue; } //Init Customer Object Customer custumer = new Customer(); custumer.setCoordinateX(row.getCell(ConfigFile.X_COORDINATES_COL).getNumericCellValue()); custumer.setCoordinateY(row.getCell(ConfigFile.Y_COORDINATES_COL).getNumericCellValue()); int tmp = 0; custumer.setStt((int) row.getCell(tmp++).getNumericCellValue()); custumer.setTinhThanh(row.getCell(tmp++).getStringCellValue()); custumer.setTuyenBanHangThu(row.getCell(tmp++).getStringCellValue()); custumer.setMaNhanVien(row.getCell(tmp++).getStringCellValue()); custumer.setX(row.getCell(tmp++).getStringCellValue()); custumer.setMaDoiTuong(row.getCell(tmp++).getStringCellValue()); custumer.setDoiTuong(row.getCell(tmp++).getStringCellValue()); // custumer.setmNoDKy(row.getCell(tmp++).getNumericCellValue()); // // custumer.setmCoDKy(row.getCell(tmp++).getNumericCellValue()); // custumer.setmNoTKy(row.getCell(tmp++).getNumericCellValue()); // custumer.setmTienBan(row.getCell(tmp++).getNumericCellValue()); // // custumer.setmCoTKy(row.getCell(tmp++).getNumericCellValue()); // custumer.setmCKGG(row.getCell(tmp++).getNumericCellValue()); // custumer.setmNhapLai(row.getCell(tmp++).getNumericCellValue()); // // custumer.setmNoCKy(row.getCell(tmp++).getNumericCellValue()); // custumer.setmCoCKy(row.getCell(tmp++).getNumericCellValue()); // custumer.setmDoanhThu(row.getCell(tmp++).getNumericCellValue()); // // custumer.setmPhanTramNoChiaThu(row.getCell(tmp++).getNumericCellValue()); // custumer.setmNoToiDa(row.getCell(tmp++).getNumericCellValue()); // custumer.setmDaiDien(row.getCell(tmp++).getStringCellValue()); custumer.setDiaChi(row.getCell(tmp++).getStringCellValue()); custumer.setDienThoai(row.getCell(tmp++).getStringCellValue()); custumer.setFax(row.getCell(tmp++).getStringCellValue()); custumer.setGhiChu(row.getCell(tmp++).getStringCellValue()); listCustomer.add(custumer); System.out.println("Add Object " + i); } } } catch (Exception ioe) { ioe.printStackTrace(); return null; } return listCustomer; }
From source file:com.hp.idc.resm.util.ExcelUtil.java
License:Open Source License
/** * ,/*w w w .j a v a 2s . c o m*/ * * @param fileName * excel, getModelExcel * @return * @throws FileNotFoundException */ public Map<String, String> readModelExcel(File file, String modelId) { if (modelId == null) return null; Map<String, String> m = new HashMap<String, String>(); try { InputStream in = new FileInputStream(file); Workbook wb; try { wb = new HSSFWorkbook(in); } catch (IllegalArgumentException e) { wb = new XSSFWorkbook(in); } Sheet sheet = wb.getSheetAt(0); int total = sheet.getLastRowNum(); Row row0 = sheet.getRow(0); String[] head = new String[row0.getLastCellNum()]; for (int j = 0; j < row0.getLastCellNum(); j++) { String[] str = row0.getCell(j).getStringCellValue().split("/"); if (str.length == 2) { head[j] = str[1]; } else { head[j] = ""; } System.out.println(head[j]); } Row row = null; Cell cell = null; for (int i = 1; i < total; i++) { m.clear(); row = sheet.getRow(i); for (int j = 0; j < row.getLastCellNum(); j++) { cell = row.getCell(j); m.put(head[j], cell.getStringCellValue()); System.out.println(head[j] + "--" + cell.getStringCellValue()); } // ServiceManager.getResourceUpdateService().addResource(modelId, // m, 1); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { file.delete(); } return m; }
From source file:com.huateng.bo.impl.risk.T40201BOTarget.java
License:Open Source License
public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception { HSSFWorkbook workbook = null;//from w ww . j a v a 2 s. c o m HSSFSheet sheet = null; HSSFRow row = null; // ? String returnMsg = ""; // ?? int fileNameIndex = 0; // ?? String fileName = null; // ?? String saCardNo = null; // ?? // String saLimitAmt = null; // ? // String saAction = null; // String saBrhId = operator.getOprBrhId(); // ? String saOprId = operator.getOprId(); // String saInitTime = CommonFunction.getCurrentDateTime(); TblCtlCardInf tblCtlCardInf = null; FileInputStream fileInputStream = null; for (File file : fileList) { fileInputStream = new FileInputStream(file); workbook = new HSSFWorkbook(fileInputStream); sheet = workbook.getSheetAt(0); fileName = fileNameList.get(fileNameIndex); for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING) returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + "" + (i + 1) + "???<br>"; if (!"".equals(returnMsg)) return returnMsg; saCardNo = row.getCell(0).getStringCellValue(); // ?? if (saCardNo.getBytes().length > 19) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; // saLimitAmt = row.getCell(1).getStringCellValue(); // saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt); // ?? /*if(!CommonFunction.isAllDigit(saLimitAmt)) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "????<br>"; if(saLimitAmt.getBytes().length > 12) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/ // saAction = row.getCell(2).getStringCellValue(); // ? /*if(!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction))) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>";*/ // ??????? tblCtlCardInf = new TblCtlCardInf(); tblCtlCardInf.setId(saCardNo); // tblCtlCardInf.setSaLimitAmt(saLimitAmt); // tblCtlCardInf.setSaAction(saAction); tblCtlCardInf.setSaInitZoneNo(saBrhId); tblCtlCardInf.setSaInitOprId(saOprId); tblCtlCardInf.setSaInitTime(saInitTime); tblCtlCardInfDAO.saveOrUpdate(tblCtlCardInf); } fileInputStream.close(); fileNameIndex++; } return Constants.SUCCESS_CODE; }