List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet rowIterator
@Override
public Iterator<Row> rowIterator()
From source file:RefDiviedMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {//www .j a v a 2 s .c om db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefDiviedMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:RefSouceOnlyMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {/* w ww .j a va2 s. c o m*/ db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefSouceOnlyMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:ambit.test.io.POItest.java
License:Open Source License
public void test1() { try {/*from ww w .j a v a 2s . c om*/ HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("data/misc/Debnath_smiles.xls")); HSSFSheet sheet = workbook.getSheetAt(0); //HSSFSheet sheet = workbook.getSheet("Sheet1"); Iterator i = sheet.rowIterator(); while (i.hasNext()) { Object o = i.next(); assertTrue(o instanceof HSSFRow); Iterator j = ((HSSFRow) o).cellIterator(); while (j.hasNext()) { Object cell = j.next(); assertTrue(cell instanceof HSSFCell); System.out.println(cell); } } //HSSFCell cell = row.getCell((short)0); } catch (IOException x) { x.printStackTrace(); fail(); } }
From source file:axiom.util.TextExtractor.java
License:Open Source License
public static String msExcelExtractor(InputStream is) throws Exception { POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); StringBuffer sb = new StringBuffer(); final int numSheets = wb.getNumberOfSheets(); for (int k = 0; k < numSheets; k++) { HSSFSheet sheet = wb.getSheetAt(k); Iterator rIt = sheet.rowIterator(); while (rIt.hasNext()) { HSSFRow row = (HSSFRow) rIt.next(); Iterator cIt = row.cellIterator(); while (cIt.hasNext()) { HSSFCell cell = (HSSFCell) cIt.next(); sb.append(cell.toString()).append(" "); }//from w w w .j av a 2 s . c om } } return sb.toString(); }
From source file:br.eti.rogerioaguilar.minhasclasses.util.excel.leitor.LeitorExcel.java
License:Open Source License
/** * Realiza o processamento de leitura seguindo o fluxo: * //from w w w . j av a 2 s . c o m * 1 - O leitor abre a planilha <br> * 2 - o leitor comea a ler as linhas e colunas da planilha <br> * 3 - Para cada linha encontrada: <br> * 3.1 - Caso a linha esteja na lista de linhas que devem ser processadas passada ao construtor: <br> * 3.1.1 - O leitor monta uma lista de vos contendos os dados das colunas para a linha atual que <br> * estejam dentro do padro de leitura passado ao construtor <br> * 3.1.2 - O leitor chama o mtodo LinhaListener.lendoLinha passando o mapa com os dados da linha <br> * e aguarda o final do processamento. <br> * 3.1.2.1 - Caso o mtodo do listener retorne true, o leitor continua o processamento da <br> * da planilha. Caso contrrio, o processamento da planilha interrompido <br> * Se o processamento da planilha continuar (de acordo com o parmetro de retorno <br> * do listener para a linha descrito anteriormente), o leitor chama o listener para <br> * a coluna para cada coluna da linha atual. O comportamento deste listener o mesmo <br> * do listener para a linha, ou seja, se o listener retornar false o processamento da <br> * planilha interrompido. * * @throws ParseException * @throws PlanilhaNaoEncontradaException caso o ndice da planilha no seja encontrado no arquivo * @throws FileNotFoundException caso o arquivo passado como parmetro no exista * @throws ListenerException caso ocorra algum erro na chamada de algum dos listeners * @throws IOException caso ocorra algum problema de io * */ public void processarLeituraPlanilha() throws ParseException, PlanilhaNaoEncontradaException, FileNotFoundException, IOException, ListenerException { try { log.debug("Inicializando o processamento da leitura do arquivo..."); log.debug("Dados para o processamento --> " + this); POIFSFileSystem fs = null; if (this.streamArquivo != null) { fs = new POIFSFileSystem(streamArquivo); } else if (this.caminhoArquivoExcel != null) { fs = new POIFSFileSystem(new FileInputStream(this.caminhoArquivoExcel)); } else { throw new IllegalArgumentException( "No foi definido um stream para o arquivo nem um caminho para o arquivo!"); } log.debug("Processando a string de entrada --> " + this.strPadraoLeitura); Map mapaLinhasAProcessar = LeitorExcelReader .getMapaEntradas(new ByteArrayInputStream(this.strPadraoLeitura.getBytes())); log.debug("A string de entrada --> " + this.strPadraoLeitura + " foi processada com sucesso."); log.debug("Mapa retornado --> " + mapaLinhasAProcessar); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet planilha = wb.getSheetAt(this.indicePlanilha - 1); if (planilha == null) { log.error("Planilha no encontrada no ndice -->" + this.indicePlanilha); throw new PlanilhaNaoEncontradaException( "Planilha no encontrada no ndice -->" + this.indicePlanilha); } else { log.debug("A string de entrada --> " + this.strPadraoLeitura + " foi processada com sucesso."); boolean processarTodasAsLinhas = (mapaLinhasAProcessar.get("*") != null); boolean processarTodasAsColunas = false; boolean continuarProcessamentoLinha = true; Map propriedadesListenerLinha = new HashMap(); Map propriedadesListenerColuna = new HashMap(); List listaColunas = null; List listaVosColunas = new LinkedList(); if (processarTodasAsLinhas) { log.debug("Processando todas as linhas..."); } Iterator itLinhas = planilha.rowIterator(); while (itLinhas.hasNext() && continuarProcessamentoLinha) { HSSFRow linha = (HSSFRow) itLinhas.next(); propriedadesListenerLinha.clear(); listaVosColunas.clear(); propriedadesListenerLinha.put(LinhaListener.CHAVE_LINHA_OBJETO_ORIGINAL_POI, linha); int intLinhaAtual = linha.getRowNum() + 1; log.debug("Processando linha --> " + intLinhaAtual); if (!processarTodasAsLinhas) { listaColunas = getListaColunasLinha("" + intLinhaAtual, mapaLinhasAProcessar); } else { listaColunas = getListaColunasLinha("*", mapaLinhasAProcessar); } boolean processarLinhaAtual = processarTodasAsLinhas || (listaColunas != null); if (processarLinhaAtual) { Iterator itColunas = linha.cellIterator(); processarTodasAsColunas = (listaColunas != null) && (listaColunas.size() > 0) && ("" + listaColunas.get(0)).equals("*"); while (itColunas.hasNext()) { HSSFCell celula = (HSSFCell) itColunas.next(); int intCelulaAtual = celula.getCellNum() + 1; boolean processarColunaAtual = processarTodasAsColunas || ((listaColunas != null) && (listaColunas.size() > 0) && listaColunas.indexOf(new Long(intCelulaAtual)) != -1); LinhaColunaListenerVo linhaColunaListenerVo = new LinhaColunaListenerVo(); linhaColunaListenerVo.setLinha(intLinhaAtual); linhaColunaListenerVo.setColuna(intCelulaAtual); linhaColunaListenerVo.setCelulaAtual(celula); if (processarColunaAtual) { if (celula != null) { log.debug("Coluna --> " + intCelulaAtual + " para a linha --> " + intLinhaAtual + " deve ser processada..."); switch (celula.getCellType()) { case HSSFCell.CELL_TYPE_STRING: linhaColunaListenerVo.setValorStr(celula.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: linhaColunaListenerVo .setValorNumerico(new Double(celula.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_FORMULA: linhaColunaListenerVo.setCelulaFormula(true); linhaColunaListenerVo.setValorStrFormula(celula.getCellFormula()); break; case HSSFCell.CELL_TYPE_ERROR: linhaColunaListenerVo.setCelulaContemErro(true); linhaColunaListenerVo.setErro(new Byte(celula.getErrorCellValue())); break; case HSSFCell.CELL_TYPE_BOOLEAN: linhaColunaListenerVo .setValorBoolean(new Boolean(celula.getBooleanCellValue())); break; case HSSFCell.CELL_TYPE_BLANK: linhaColunaListenerVo.setCelulaBranca(true); linhaColunaListenerVo.setValorStr(""); break; } } else { log.warn("Clula nula!"); linhaColunaListenerVo.setCelulaNula(true); } listaVosColunas.add(linhaColunaListenerVo); } else { log.debug("Coluna --> " + intCelulaAtual + " para a linha --> " + intLinhaAtual + " no deve ser processada..."); } } if (this.linhaListener != null) { log.debug("Chamando o listener para a linha --> " + intLinhaAtual); Collections.sort(listaVosColunas, new Comparator() { public int compare(Object arg0, Object arg1) { int colunaUm = ((LinhaColunaListenerVo) arg0).getColuna(); int colunaDois = ((LinhaColunaListenerVo) arg1).getColuna(); if (colunaUm < colunaDois) { return -1; } else if (colunaUm > colunaDois) { return 1; } return 0; } }); propriedadesListenerLinha.put(LinhaListener.LISTA_VOS_LINHA, listaVosColunas); continuarProcessamentoLinha = this.linhaListener.lendoLinha(intLinhaAtual, propriedadesListenerLinha); if (!continuarProcessamentoLinha) { log.debug( "Listener retornou boolean false indicando que o processamento deve ser interrompido!"); } } else { log.debug("Listener no configurado para a linha --> " + intLinhaAtual); } if (this.colunaListener != null) { Iterator itColunasVoListener = listaVosColunas.iterator(); boolean continuarProcessamentoColunasnaLinha = true; while (itColunasVoListener.hasNext() && continuarProcessamentoColunasnaLinha) { propriedadesListenerColuna.clear(); LinhaColunaListenerVo voAtual = (LinhaColunaListenerVo) itColunasVoListener.next(); propriedadesListenerColuna.put(ColunaListener.CHAVE_VO_COLUNA, voAtual); propriedadesListenerColuna.put(ColunaListener.CHAVE_COLUNA_OBJETO_ORIGINAL_POI, voAtual.getCelulaAtual()); log.debug("Chamando o listener para a coluna --> " + voAtual.getColuna() + " na linha " + voAtual.getLinha()); continuarProcessamentoColunasnaLinha = this.colunaListener.lendoColuna( voAtual.getLinha(), voAtual.getColuna(), propriedadesListenerColuna); if (!continuarProcessamentoColunasnaLinha) { log.debug( "Listener de coluna retornou boolean false indicando que o processamento das colunas na linha " + voAtual.getLinha() + " deve ser interrompido!"); } } } else { log.debug("Listener no configurado para processamento das colunas"); } } else { log.debug("Linha --> " + intLinhaAtual + " no ser processada!"); } } } log.debug("Processamento da planilha realizado com sucesso!"); } catch (ParseException e) { e.printStackTrace(); log.error("Erro ao processar a string de entrada ", e); throw e; } catch (FileNotFoundException e) { e.printStackTrace(); log.error("Arquivo " + this.caminhoArquivoExcel + " no encontrado", e); throw e; } catch (IOException e) { e.printStackTrace(); log.error("Erro ao abrir o arquivo " + this.caminhoArquivoExcel, e); throw e; } catch (ListenerException e) { e.printStackTrace(); log.error("Erro ao processar o listener ", e); throw e; } }
From source file:com.afrisoftech.lib.ExportData.java
public static Vector read(String fileName) { Vector cellVectorHolder = new Vector(); try {//ww w .ja v a2s .c o m FileInputStream myInput = new FileInputStream(fileName); POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); HSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator rowIter = mySheet.rowIterator(); while (rowIter.hasNext()) { HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); Vector cellStoreVector = new Vector(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); //Object obj=myCell.getStringCellValue(); System.out.print(myCell.getCellType() + " -"); if (myCell.getCellType() == 0) { cellStoreVector.addElement(myCell.getNumericCellValue()); } else if (myCell.getCellType() == 1) { cellStoreVector.addElement(myCell.getStringCellValue()); } //cellStoreVector.addElement(myCell.getStringCellValue()); } System.out.println(); cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { e.printStackTrace(); } return cellVectorHolder; }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importEmploye(HttpServletRequest req, HttpServletResponse res) throws Exception { System.out.println("hiiii heree......."); MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);/*from ww w . j av a 2 s . c om*/ fout.flush(); fout.close(); try { String DepartmentID = "", LocationID = "", EmpName = "", Email1 = "", Email2 = "", address = ""; String city = "", zip = "", phone1 = "", locCoun = "", locstate = "", loccity = "", locContact = "", locEmail = ""; String phone2 = "", divisions = "", email2 = "", username = "", reporting = "", photo = ""; int isLicense = 0; int isactive = 1; int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; String[] data = null; List<String> dataStatus = new ArrayList<String>(); int val = 0; //Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { EmpName = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { EmpName = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); val++; } try { DepartmentID = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { DepartmentID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); val++; } try { LocationID = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { LocationID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Location -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); val++; } try { locCoun = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { locCoun = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Country - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country- " + e.getMessage()); val++; } try { locstate = hsrow.getCell((short) 4).getStringCellValue(); } catch (Exception e) { locstate = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location State - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State- " + e.getMessage()); val++; } try { loccity = hsrow.getCell((short) 5).getStringCellValue(); } catch (Exception e) { loccity = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location City -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City- " + e.getMessage()); val++; } try { locContact = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { locContact = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Contact Name -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name- " + e.getMessage()); val++; } try { locEmail = hsrow.getCell((short) 7).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and validated the supplied // address which is this case is an email address. InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { locEmail = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Email -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); val++; } try { divisions = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { divisions = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); val++; } try { address = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { address = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); val++; } try { city = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); val++; } try { zip = String.valueOf(hsrow.getCell((short) 11).getNumericCellValue()); zip = String.valueOf(zip).split("\\.")[0]; } catch (Exception e) { zip = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone1 = String.valueOf(hsrow.getCell((short) 12).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 12)); phone1 = df2; phone1 = String.valueOf(phone1).split("\\.")[0]; } catch (Exception e) { phone1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone2 = String.valueOf(hsrow.getCell((short) 13).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 13)); phone2 = df2; System.out.println("check phone1" + phone2); phone2 = String.valueOf(phone2).split("\\.")[0]; } catch (Exception e) { phone2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 - " + e.getMessage()); val++; } try { boolean isValid = false; Email1 = hsrow.getCell((short) 14).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); val++; } try { boolean isValid = false; Email2 = hsrow.getCell((short) 15).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); val++; } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + hsrow.getRowNum() + "Not Inserted Row : " + EmpName); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } //Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { EmpName = c.toString(); } catch (Exception e) { EmpName = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": EmpName - " + e.getMessage()); } } if (c.getColumnIndex() == 1) { try { DepartmentID = c.toString(); } catch (Exception e) { DepartmentID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Department - " + e.getMessage()); } } if (c.getColumnIndex() == 2) { try { LocationID = c.toString(); } catch (Exception e) { LocationID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Location -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { locCoun = c.toString(); } catch (Exception e) { locCoun = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Country - " + e.getMessage()); } } if (c.getColumnIndex() == 4) { try { locstate = c.toString(); } catch (Exception e) { locstate = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location State - " + e.getMessage()); } } if (c.getColumnIndex() == 5) { try { loccity = c.toString(); } catch (Exception e) { loccity = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location City -" + e.getMessage()); } } if (c.getColumnIndex() == 6) { try { locContact = c.toString(); } catch (Exception e) { locContact = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Contact Name -" + e.getMessage()); } } if (c.getColumnIndex() == 7) { try { locEmail = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": locEmail - " + e.getMessage()); } } catch (Exception e) { locEmail = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Email -" + e.getMessage()); } } if (c.getColumnIndex() == 8) { try { divisions = c.toString(); } catch (Exception e) { divisions = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Divisions - " + e.getMessage()); } } if (c.getColumnIndex() == 9) { try { address = c.toString(); } catch (Exception e) { address = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": address - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { city = c.toString(); } catch (Exception e) { city = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": city - " + e.getMessage()); } } if (c.getColumnIndex() == 11) { try { zip = c.toString(); } catch (Exception e) { zip = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": zip - " + e.getMessage()); } } if (c.getColumnIndex() == 12) { try { DataFormatter formatter = new DataFormatter(); phone1 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone1 = df2; } catch (Exception e) { phone1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone1 - " + e.getMessage()); } } if (c.getColumnIndex() == 13) { try { DataFormatter formatter = new DataFormatter(); phone2 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone2 = df2; } catch (Exception e) { phone2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone2 -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { Email1 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { Email2 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row EmpName : " + EmpName); notcount = notcount + 1; } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { dataStatus.add(notcount + " Rows are not inserted"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("organization/employees", map); }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importAssets(HttpServletRequest req, HttpServletResponse res) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);/*from w w w. jav a2s . c om*/ fout.flush(); fout.close(); try { String Assets = "", category = "", location = "", application = "", business = ""; String initial = "", quantity = "", country = "", state = "", city = "", contact = "", email = ""; List<String> dataStatus = new ArrayList<String>(); int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; // Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); System.out.println("check this...hsrow." + hsrow.getRowNum()); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { Assets = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": AssetName - " + e.getMessage()); } } try { category = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Category - " + e.getMessage()); } } try { application = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Application - " + e.getMessage()); } } try { business = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } try { initial = String.valueOf(hsrow.getCell((short) 4).getNumericCellValue()); initial = String.valueOf(initial).split("\\.")[0]; } catch (Exception e) { initial = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": InitialValue - " + e.getMessage()); } } try { quantity = String.valueOf(hsrow.getCell((short) 5).getNumericCellValue()); quantity = String.valueOf(quantity).split("\\.")[0]; } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Quantity - " + e.getMessage()); } } try { location = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); } } try { country = hsrow.getCell((short) 7).getStringCellValue(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country - " + e.getMessage()); } } try { state = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State - " + e.getMessage()); } } try { city = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City - " + e.getMessage()); } } try { contact = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } try { email = hsrow.getCell((short) 11).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an email // address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { email = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email - " + e.getMessage()); } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println("Error Row : " + hsrow.getRowNum() + "Not Inserted Row Asset Name : " + Assets); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } // Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { Assets = c.toString(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": AssetName - " + e.getMessage()); } } } if (c.getColumnIndex() == 1) { try { category = c.toString(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Category - " + e.getMessage()); } } } if (c.getColumnIndex() == 2) { try { application = c.toString(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Application - " + e.getMessage()); } } } if (c.getColumnIndex() == 3) { try { business = c.toString(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } } if (c.getColumnIndex() == 4) { try { initial = c.toString(); } catch (Exception e) { initial = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": InitialValue - " + e.getMessage()); } } } if (c.getColumnIndex() == 5) { try { quantity = c.toString(); } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Quantity - " + e.getMessage()); } } } if (c.getColumnIndex() == 6) { try { location = c.toString(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location - " + e.getMessage()); } } } if (c.getColumnIndex() == 7) { try { country = c.toString(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Country - " + e.getMessage()); } } } if (c.getColumnIndex() == 8) { try { state = c.toString(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location State - " + e.getMessage()); } } } if (c.getColumnIndex() == 9) { try { city = c.toString(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location City - " + e.getMessage()); } } } if (c.getColumnIndex() == 10) { try { contact = c.toString(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } } if (c.getColumnIndex() == 11) { try { email = c.toString(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an // email address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } catch (Exception e) { email = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row Asset Name : " + Assets); } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("assests/assets", map); }
From source file:com.bayareasoftware.chartengine.ds.ExcelDataSource.java
License:Apache License
/** * work around bug with named cells/*from w w w . j a v a 2s . c o m*/ * */ private static void rewriteFormulas(final HSSFWorkbook workbook) { //if (true) return; // build up a cache of names // this is just an easy way of fetching the HSSFName based on the string // representation of the name final Map<String, HSSFName> nameCache = new HashMap<String, HSSFName>(workbook.getNumberOfNames()); for (int i = 0; i < workbook.getNumberOfNames(); i++) { final HSSFName name = workbook.getNameAt(i); nameCache.put(name.getNameName(), name); } // remove all the sheet names from the name references, having the sheet // names around messes up the formulas for (int i = 0; i < workbook.getNumberOfSheets(); i++) { nameCache.remove(workbook.getSheetName(i)); } //p("Names: " + nameCache.keySet()); // loop over all the cells and rewrite the formula ones for (int sheetCount = 0; sheetCount < workbook.getNumberOfSheets(); sheetCount++) { final HSSFSheet sheet = workbook.getSheetAt(sheetCount); for (final Iterator rowIterator = sheet.rowIterator(); rowIterator.hasNext();) { final HSSFRow row = (HSSFRow) rowIterator.next(); for (final Iterator cellIterator = row.cellIterator(); cellIterator.hasNext();) { final HSSFCell cell = (HSSFCell) cellIterator.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { String formula = cell.getCellFormula(); for (final String name : nameCache.keySet()) { final Pattern pattern = Pattern.compile("(\\W|^)" + name + "(\\W|$)", Pattern.CASE_INSENSITIVE); final HSSFName hssfName = nameCache.get(name); formula = pattern.matcher(formula) .replaceAll("$1" + hssfName.getReference().replace("$", "\\$") + "$2"); } //p("Resetting Cell (" + cell.toString() // + ") Formula:" + formula); cell.setCellFormula(formula); } // end if } // end for } // end for } // end for }
From source file:com.br.uepb.bsc7.www.UI.ManipulaXLS.java
public /*static*/ void leXLS(String filename) throws IOException { System.out.println("Mtodo leXLS chamado!"); FileInputStream fileInputStream = new FileInputStream(filename); try {// ww w . j av a 2 s . co m //Obtem acesso pasta de trabalho wb = new HSSFWorkbook(fileInputStream); //Obtem acesso planilha Plan1 HSSFSheet s = wb.getSheet("Plan1"); Iterator<Row> rowIterator = s.rowIterator(); while (rowIterator.hasNext()) { System.out.println("Nova linha!"); //Obtem acesso a cada linha de Plan1 Row linha = rowIterator.next(); Iterator<Cell> cellIterator = linha.cellIterator(); while (cellIterator.hasNext()) { //Obtem acesso a cada clula de cada linha de Plan1 Cell celula = cellIterator.next(); //System.out.println(celula.getStringCellValue()); //Adiciona o valor de cada clula ao ArrayList que ser passado a DAO dados.add(celula.getStringCellValue()); // } //Chamada ao mtodo do BD que recebe o ArrayList (Deve estar em DAO) new TesteInsereBD().insereLinha(dados, dados.size()); //Limpa o ArrayLista para preench-lo novamente dados.clear(); } //Corrigir este catch com algo mais eficiente } catch (IOException ex) { System.out.println("Teste"); } }