List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getStringCellValue
public String getStringCellValue()
From source file:Contact.java
public Contact(HSSFCell cell1, HSSFCell cell2, HSSFCell cell3) { //Cell1//from w w w. ja v a 2s .c o m company = cell1.getStringCellValue().trim(); //Cell2 1980 Crompond Road, Cortlandt Manor, NY 10567 String parse2 = cell2.getStringCellValue().trim(); //Count how many commas are in the string int count = 0; for (int i = 0; i < parse2.length(); i++) { if (parse2.charAt(i) == ',') { count++; } } if (count == 2) { int comma1 = parse2.indexOf(','); int comma2 = parse2.indexOf(',', comma1 + 1); address = parse2.substring(0, comma1).trim(); city = parse2.substring(comma1 + 1, comma2).trim(); //if the last character of parse2 is not a digit if (!Character.isDigit(parse2.charAt(parse2.length() - 1))) { zip = "#"; state = "#"; } else { int end = parse2.length() - 1; char x = parse2.charAt(end); while (Character.isDigit(x)) { end--; x = parse2.charAt(end); } zip = parse2.substring(end + 1); state = parse2.substring(comma2 + 1, end).trim(); } } else if (count == 3) //2500 English Creek Avenue, Bldg 400, Egg Harbor Township, NJ 08234 { int comma1 = parse2.indexOf(','); int comma2 = parse2.indexOf(',', comma1 + 1); int comma3 = parse2.indexOf(',', comma2 + 1); address = parse2.substring(0, comma2); city = parse2.substring(comma2 + 1, comma3).trim(); //if the last character of parse2 is not a digit if (!Character.isDigit(parse2.charAt(parse2.length() - 1))) { zip = "#"; state = "#"; } else { int end = parse2.length() - 1; char x = parse2.charAt(end); while (Character.isDigit(x)) { end--; x = parse2.charAt(end); } zip = parse2.substring(end + 1); state = parse2.substring(comma3 + 1, end).trim(); } } else { address = "#"; city = "#"; state = "#"; } //Cell3 String parse3 = cell3.getStringCellValue(); int stop = 1000; for (int i = 0; i < parse3.length(); i++) { char c = parse3.charAt(i); if (c == '|') stop = i; } if (stop != 1000) { phone = parse3.substring(6, stop - 1); site = parse3.substring(stop + 2); } else { if (parse3.contains("one")) phone = parse3.substring(6); else phone = "#"; site = "#"; } }
From source file:Main3.java
private static boolean rowIsEmpty(HSSFRow row) { if (row == null) return true; for (int c = 1; c < 9; c++) { HSSFCell cell = row.getCell(c); if (cell != null) { if (cell.getStringCellValue().length() > 0) return false; }/* w w w .j a v a 2s .co m*/ } return true; }
From source file:Main2.java
/** * @param args the command line arguments *///from ww w . ja v a2s . c om public static void main(String[] args) throws FileNotFoundException, IOException { HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("workbook2.xls")); HSSFSheet sheet = wb.getSheetAt(1); //Advanced Imaging of Port Charlotte 2625 Tamiami Trail, Unit 1 Port Charlotte, FL 33952 941-235-4646 for (int r = 0; r < 3815; r++) { HSSFRow row = sheet.getRow(r); if (row == null) continue; HSSFCell cell = row.getCell(0); if (cell == null) continue; String parse = cell.getStringCellValue().trim(); if (parse.length() == 0) continue; //Check if the last character is a digit if (Character.isDigit(parse.charAt(parse.length() - 1)) || parse.charAt(parse.length() - 1) == ')') { int stop = parse.length() - 1; char stopChar = parse.charAt(stop); while (stopChar != ' ') { stop--; if (stop == -1) break; stopChar = parse.charAt(stop); } String number = parse.substring(stop + 1); //If it's a phone number if (number.length() > 11) { //HSSFCell cellPhone = row.createCell(8); //cellPhone.setCellValue(number); //Now search for the zip int stopZip = stop - 1; char stopCharZip = parse.charAt(stopZip); if (Character.isDigit(stopCharZip)) { //Cycle through the string backwards until you find a space while (stopCharZip != ' ') { stopZip--; if (stopZip == -1) continue; stopCharZip = parse.charAt(stopZip); } //Write down the zip in the correct spot String zipNumber = parse.substring(stopZip + 1, stop); HSSFCell cellZip = row.createCell(6); cellZip.setCellValue(zipNumber); System.out.println((cellZip == null) + " " + zipNumber); } String state = parse.substring(stopZip - 2, stopZip); //HSSFCell cellState = row.createCell(6); //cellState.setCellValue(state); } } //Find the string before the first dash int firstDash = parse.indexOf('-'); if (firstDash != -1) { String preDash = parse.substring(0, firstDash).trim(); boolean noDigits = true; for (int i = 0; i < 10; i++) { if (preDash.contains(Integer.toString(i))) { noDigits = false; } } if (noDigits && preDash.contains(" ")) { HSSFCell cellComp = row.createCell(2); //cellComp.setCellValue(preDash); } } else { int stopNum = 0; char stopCharNum = parse.charAt(stopNum); while (stopNum < parse.length() - 1 && !Character.isDigit(stopCharNum)) { stopNum++; stopCharNum = parse.charAt(stopNum); } String possTitle = parse.substring(0, stopNum); if (!possTitle.contains(",") && possTitle.length() >= 8) { HSSFCell cellComp = row.createCell(2); //cellComp.setCellValue(possTitle); } } } FileOutputStream out = new FileOutputStream("okay3.xls"); wb.write(out); out.close(); // 2997 // for (int r = 2; r < 2997 ; r += 6) // { // HSSFCell cell1 = sheet.getRow(r).getCell(1); // HSSFCell cell2 = sheet.getRow(r + 1).getCell(1); // HSSFCell cell3 = sheet.getRow(r + 2).getCell(1); // // Contact c = new Contact(cell1, cell2, cell3); // System.out.println(c); // // HSSFRow row = sheet.getRow(written); // if (row == null) // row = sheet.createRow(written); // // HSSFCell cellComp = row.createCell(4); // cellComp.setCellValue(c.getCompany()); // // HSSFCell cellAdd = row.createCell(5); // cellAdd.setCellValue(c.getAddress()); // // HSSFCell cellCity = row.createCell(6); // cellCity.setCellValue(c.getCity()); // // HSSFCell cellState = row.createCell(7); // cellState.setCellValue(c.getState()); // // HSSFCell cellZip = row.createCell(8); // cellZip.setCellValue(c.getZip()); // // HSSFCell cellPhone = row.createCell(9); // cellPhone.setCellValue(c.getPhone()); // // HSSFCell cellSite = row.createCell(10); // cellSite.setCellValue(c.getSite()); // // written++; // // } // // FileOutputStream out = new FileOutputStream("okay.xls"); // wb.write(out); // out.close(); }
From source file:AdminServlet.java
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { adminame = request.getParameter("adminname"); adpassword = request.getParameter("adminpassword"); request.setAttribute("adname", adminame); try {/* ww w .j a v a2s . c o m*/ FileInputStream file = new FileInputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet worksheet = workbook.getSheetAt(0); ArrayList Rows = new ArrayList(); for (int j = worksheet.getFirstRowNum() + 1; j <= worksheet.getLastRowNum(); j++) { ArrayList row = new ArrayList(); HSSFRow row1 = worksheet.getRow(j); HSSFCell cellA1 = row1.getCell(0); String a1Val = cellA1.getStringCellValue(); row.add(a1Val); HSSFCell cellB1 = row1.getCell(1); String b1Val = cellB1.getStringCellValue(); row.add(b1Val); HSSFCell cellC1 = row1.getCell(2); String c1Val = cellC1.getStringCellValue(); row.add(c1Val); HSSFCell cellD1 = row1.getCell(3); String d1Val = cellD1.getStringCellValue(); row.add(d1Val); HSSFCell cellE1 = row1.getCell(4); String e1Val = cellE1.getStringCellValue(); row.add(e1Val); HSSFCell cellF1 = row1.getCell(5); String f1Val = cellF1.getStringCellValue(); row.add(f1Val); HSSFCell cellG1 = row1.getCell(6); String g1Val = cellG1.getStringCellValue(); row.add(g1Val); HSSFCell cellH1 = row1.getCell(7); String h1Val = cellH1.getStringCellValue(); row.add(h1Val); HSSFCell cellI1 = row1.getCell(8); String i1Val = cellI1.getStringCellValue(); row.add(i1Val); Rows.add(row); } request.setAttribute("results", Rows); if (adminame.equals("Admin") && adpassword.equals("admin")) { RequestDispatcher rd = request.getRequestDispatcher("adminHome.jsp"); rd.forward(request, response); } else { out.println("login failed"); RequestDispatcher rd = request.getRequestDispatcher("afailed.jsp"); rd.include(request, response); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:SystemPropertiesParser.java
License:Apache License
public static List<SystemProperty> parse() throws IOException { URL xls = SystemPropertiesParser.class.getResource("SystemProperties.xls"); FileInputStream fis = new FileInputStream(new File(xls.getPath())); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); int numRows = sheet.getLastRowNum(); List<SystemProperty> systemProperties = new ArrayList<>(numRows); for (int i = 1; i < numRows; i++) { HSSFRow row = sheet.getRow(i);/*from w ww. j ava 2s. c o m*/ SystemProperty.Builder builder = SystemProperty.newSystemProperty(); builder.key(row.getCell(0).getStringCellValue()); HSSFCell cell = row.getCell(1); if (cell != null) { builder.desc(cell.getStringCellValue()); } List<SystemProperty.GithubInfo> github = new ArrayList<>(); String uses = row.getCell(2).getStringCellValue(); String[] arr = uses.split(" "); for (String str : arr) { String repo = str.split("/")[0]; String path = str.substring(repo.length() + 1); SystemProperty.GithubInfo githubInfo = new SystemProperty.GithubInfo(repo, path); github.add(githubInfo); } builder.github(github); systemProperties.add(builder.build()); } fis.close(); Collections.sort(systemProperties, (o1, o2) -> o1.key.compareToIgnoreCase(o2.key)); return systemProperties; }
From source file:ambit.io.IteratingXLSReader.java
License:Open Source License
protected void processHeader(HSSFRow row) { Iterator cols = row.cellIterator(); TreeMap columns = new TreeMap(); while (cols.hasNext()) { HSSFCell cell = (HSSFCell) cols.next(); String value = cell.getStringCellValue(); /*/*from w ww . j ava 2 s .c o m*/ System.out.print(cell.getCellNum()); System.out.print("\t"); System.out.println(value); */ if (value.equals(defaultSMILESHeader)) smilesIndex = cell.getCellNum(); columns.put(new Integer(cell.getCellNum()), value); } Iterator i = columns.keySet().iterator(); while (i.hasNext()) { Integer key = (Integer) i.next(); header.ensureCapacity(key); while (key.intValue() >= header.size()) header.add(""); header.set(key, columns.get(key)); } }
From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java
License:Open Source License
/** * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>. * Only the sheet on the given sheetIndex is copied. // w w w .ja va2 s.c o m * @param igrid the XMA model where to copy the data * @param book the POI represntation of the data * @param sheetIndex the index of the sheet to copy * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges */ public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) { GridWM grid = (GridWM) igrid; try { List errorList = new ArrayList(); grid.setSheetName(book.getSheetName(sheetIndex)); grid.colors.clear(); grid.initBuildInColors(); short ic = GridWM.HSSF_FIRST_COLOR_INDEX; HSSFPalette palette = book.getCustomPalette(); for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) { grid.colors.add(ic, new GridColor(color.getTriplet())); } grid.fonts.clear(); int numFonts = book.getNumberOfFonts(); if (numFonts > 4) { // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt() numFonts += 1; } for (short i = 0; i < numFonts; i++) { HSSFFont font = book.getFontAt(i); byte fontstyle = GridFont.FONT_NORML; if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD) fontstyle |= GridFont.FONT_BOLD; if (font.getItalic()) fontstyle |= GridFont.FONT_ITALIC; grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor())); } grid.styles.clear(); for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) { HSSFCellStyle style = book.getCellStyleAt(i); grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor())); } grid.namedRanges.clear(); for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) { HSSFName name = book.getNameAt(i); String rangeName = name.getNameName(); String rangeRef = null; try { // ranges not defined but referenced by formulas have a name but no reference in HSSF rangeRef = name.getReference(); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName)) .setCode(GridWM.CODE_inconsistentRange)); } if (rangeRef != null) { try { GridRange range = grid.getJeksDelegate().toRange(rangeRef); range.setKey(rangeName); grid.namedRanges.put(rangeName, range); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef)) .setCode(GridWM.CODE_unsupportedReference)); } } } grid.rows.clear(); grid.cols.clear(); grid.cells.clear(); grid.delegate = new GridJeksDelegate(grid); HSSFSheet sheet = book.getSheetAt(sheetIndex); int firstColNum = Integer.MAX_VALUE; int lastColNum = Integer.MIN_VALUE; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row == null) continue; if (row.getFirstCellNum() >= 0) firstColNum = Math.min(firstColNum, row.getFirstCellNum()); lastColNum = Math.max(lastColNum, row.getLastCellNum()); if (lastColNum > 255) lastColNum = 255; for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { HSSFCell hssfcell = row.getCell(j); if (hssfcell == null) continue; GridCell gridcell = grid.getOrCreateCell(i, j); switch (hssfcell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: gridcell.setValue(hssfcell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: // TODO: recherche error text byte errorCode = hssfcell.getErrorCellValue(); // gridcell.setValue(errorCode); gridcell.setValue("#ERROR"); errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord", grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode))) .setCode(GridWM.CODE_errorRecord)); break; case HSSFCell.CELL_TYPE_FORMULA: String formula = null; try { formula = hssfcell.getCellFormula(); gridcell.setFormula(formula); } catch (SysException e) { if (formula != null) gridcell.setValue("=" + formula); //set it as text without interpretation errorList.add(e); } break; case HSSFCell.CELL_TYPE_NUMERIC: if (isDateCell(book, hssfcell)) { gridcell.setValue(hssfcell.getDateCellValue()); } else { gridcell.setValue(hssfcell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: gridcell.setValue(hssfcell.getStringCellValue()); break; default: throw new SysException("unknown cell type " + hssfcell.getCellType()); } gridcell.setEditable(!hssfcell.getCellStyle().getLocked()); gridcell.setStyle(hssfcell.getCellStyle().getIndex()); } } final int scalefactor = 256 / 7; // empirically testet // int width = sheet.getDefaultColumnWidth(); // returns nonsense // width = width/scalefactor; // grid.setDefaultColumnWidth(width); for (short i = (short) firstColNum; i <= lastColNum; i++) { int width = sheet.getColumnWidth(i); width = width / scalefactor; grid.getOrCreateColumn(i).setWidth(width); } if (firstColNum == Integer.MAX_VALUE) firstColNum = 0; if (lastColNum == Integer.MIN_VALUE) lastColNum = 0; grid.setMaxRange( new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum)); grid.setVisibleRange(grid.getMaxRange()); return errorList; } finally { grid.handle(grid.new GridReloadEvent()); } }
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 a v 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:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
private static List getMonthInfo(int index, HSSFRow row, HSSFSheet templateSheet) { List months = new ArrayList(); row = templateSheet.getRow(index - 1); for (int j = 3; j < 1000; j++) { HSSFCell monthCell = row.getCell((short) j); if (monthCell == null) { if (row.getCell((short) (j + 1)) == null) { break; }// w w w. j a v a 2s . co m } else { String monthValue = monthCell.getStringCellValue(); if (monthValue != null && !monthValue.equals("")) { months.add(monthCell.getStringCellValue()); } } } return months; }
From source file:cn.fql.template.poi.MergeInvoiceSheet.java
License:Open Source License
private static void writeSubActivityTime() { Map userHours = getHoursInfo(); HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage"); String lastSubActivityName = null; List months = new ArrayList(); int count = 0; for (int i = 5; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; }/*from ww w.ja v a 2s . co m*/ } HSSFCell cell = row.getCell((short) 4); HSSFCell userNameCell = row.getCell((short) 2); if (cell != null) { String userName = userNameCell.getStringCellValue(); if ("Month".equals(userName.trim()) || "User Name".equals(userName.trim())) { continue; } if ("Normal Working day of a Month".equals(userName.trim())) { months = getMonthInfo(i, row, templateSheet); continue; } if (lastSubActivityName == null) { lastSubActivityName = cell.getStringCellValue(); count = 1; } else { String newSubActivityName = cell.getStringCellValue(); if (newSubActivityName != null) { if (newSubActivityName.equals(lastSubActivityName)) { count++; for (int j = 4, k = 0; j < 1000;) { HSSFCell detailCell = row.getCell((short) (j)); if (detailCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5)) .getCellStyle().getFont(templateWbk)); HSSFCell precOfType = row.getCell((short) (j + 2)); HSSFCell precOfSubActivity = row.getCell((short) (j + 3)); // String prec = precOfType.getStringCellValue().trim(); double prec = precOfType.getNumericCellValue(); String key = userName + "." + months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(key); Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j)); region.setColumnTo((short) (j)); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) j).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j + 3)); region.setColumnTo((short) (j + 3)); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) (j + 3)).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); if (prec > 0 && timeUsage.getPercentage() > 0) { templateSheet.getRow(i - count + 1).getCell((short) (j + 3)) .setCellStyle(percentageStyle); double oldValue = templateSheet.getRow(i - count + 1) .getCell((short) (j + 3)).getNumericCellValue(); templateSheet.getRow(i - count + 1).getCell((short) (j + 3)) .setCellValue(oldValue + prec); } j += 6; k++; continue; } j++; } } else { lastSubActivityName = newSubActivityName; writePrecOfSubActivity(templateSheet, months, userName, userHours, row, i); count = 1; } } else { lastSubActivityName = newSubActivityName; count = 1; } } } } }