List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell
@Override public HSSFCell getCell(int cellnum)
From source file:be.vds.jtbdive.client.view.core.dive.profile.DiveProfileExcelParser.java
License:Open Source License
public DiveProfile read(File file) throws IOException { DiveProfile dp = new DiveProfile(); Map<Double, Double> depthEntries = new HashMap<Double, Double>(); Set<Double> ascentWarnings = new HashSet<Double>(); Set<Double> decoWarnings = new HashSet<Double>(); Set<Double> remainingBottomTimeWarnings = new HashSet<Double>(); Set<Double> decoEntries = new HashSet<Double>(); HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file)); HSSFSheet sheet = wb.getSheetAt(0);// w ww . j a v a2s. c o m int rowMax = sheet.getLastRowNum(); for (int i = 1; i <= rowMax; i++) { HSSFRow row = sheet.getRow(i); double second = row.getCell(0).getNumericCellValue(); depthEntries.put(second, row.getCell(1).getNumericCellValue()); boolean b = row.getCell(2).getBooleanCellValue(); if (b) ascentWarnings.add(second); b = row.getCell(3).getBooleanCellValue(); if (b) remainingBottomTimeWarnings.add(second); b = row.getCell(4).getBooleanCellValue(); if (b) decoWarnings.add(second); b = row.getCell(5).getBooleanCellValue(); if (b) decoEntries.add(second); } dp.setDepthEntries(depthEntries); if (ascentWarnings.size() > 0) dp.setAscentWarnings(ascentWarnings); if (decoWarnings.size() > 0) dp.setDecoCeilingWarnings(decoWarnings); if (remainingBottomTimeWarnings.size() > 0) dp.setRemainingBottomTimeWarnings(remainingBottomTimeWarnings); if (decoEntries.size() > 0) dp.setDecoEntries(decoEntries); return dp; }
From source file:Beans.GeminusCompen.java
public void postProcessXLS(Object document) throws IOException { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from ww w . j a v a 2 s. c o m HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.WHITE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:blueprint.sdk.experimental.util.XlsReader.java
License:Open Source License
/** * returns effective cells from sheetIdx starting from rowIdx, cellIdx * * @param sheetIdx index of target sheet * @param rowIdx index of target row//from ww w . j a va 2s .c o m * @return cells on target row * @throws IOException cell type error */ public List<String> getCellsAt(final int sheetIdx, final int rowIdx) throws IOException { List<String> result = new ArrayList<>(); HSSFRow row = wbook.getSheetAt(sheetIdx).getRow(rowIdx); int lastCell = row.getLastCellNum(); cell_loop: for (int i = 0; i < cells[sheetIdx].length && i <= lastCell; i++) { HSSFCell cell = row.getCell(i); if (cell == null) { result.add(""); } else { try { switch (cells[sheetIdx][i]) { case TYPE_SKIP: break; case TYPE_TEXT_SKIP: if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { String val = cell.getRichStringCellValue().getString(); result.add(val == null ? "" : val); } else { break cell_loop; } break; case TYPE_TEXT: String textVal = cell.getRichStringCellValue().getString(); result.add(textVal == null ? "" : textVal); break; case TYPE_TIME: String timeVal = getTimeValue(cell); result.add(timeVal == null ? "" : timeVal); break; case TYPE_DATE: String dateVal = getDateValue(cell); result.add(dateVal == null ? "" : dateVal); break; case TYPE_NUM_TEXT: String numTextVal = getTextValue(cell); result.add(numTextVal == null ? "" : numTextVal); break; default: throw new IOException(createTypeErrMsg(cell)); } } catch (IllegalStateException e) { e.printStackTrace(); throw new IOException(createTypeErrMsg(cell)); } } } return result; }
From source file:br.com.nfsconsultoria.azcontrole.bean.VendaBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from w ww .ja v a 2 s .c om HSSFRow header = sheet.getRow(0); HSSFFont font = wb.createFont(); font.setBold(true); font.setColor(HSSFColor.WHITE.index); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setWrapText(true); cellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); cellStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:br.com.primetestes.TableBean.java
License:Apache License
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from w w w.j a v a 2 s. co m HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); //TODO fucking fix that HSSCell class pls } }
From source file:br.edu.tglima.model.result.PlanilhaXLS.java
License:Open Source License
/** * Mtodo responsvel por gerar a planilha XLS. * // www.jav a 2 s . co m * * @param arquivo Referente ao local e nome do arquivo. * @return Retorno do tipo Boolean, indicando se o arquivo foi gerado * com sucesso, ou no. */ public boolean gerarPlanilha(File arquivo) { try { /*Verificamos se j existe um arquivo com esse "nome". Caso ele exista, ele ser removido e o novo arquivo ser gerado.*/ if (workbook.getNumberOfSheets() > 0) { workbook.removeSheetAt(0); } HSSFSheet sheet = workbook.createSheet("Valores Exportados"); /* Criando as linhas. --------------------------------------------- */ HSSFRow header1 = sheet.createRow((short) 0); HSSFRow linha02 = sheet.createRow((short) 1); HSSFRow linha03 = sheet.createRow((short) 2); HSSFRow linha04 = sheet.createRow((short) 3); HSSFRow linha05 = sheet.createRow((short) 4); HSSFRow header2 = sheet.createRow((short) 7); HSSFRow linha09 = sheet.createRow((short) 8); HSSFRow linha10 = sheet.createRow((short) 9); HSSFRow linha11 = sheet.createRow((short) 10); HSSFRow linha12 = sheet.createRow((short) 11); HSSFRow linha13 = sheet.createRow((short) 12); HSSFRow linha14 = sheet.createRow((short) 13); HSSFRow linha15 = sheet.createRow((short) 14); HSSFRow linha17 = sheet.createRow((short) 16); HSSFRow header3 = sheet.createRow((short) 19); HSSFRow linha21 = sheet.createRow((short) 20); HSSFRow linha22 = sheet.createRow((short) 21); HSSFRow linha23 = sheet.createRow((short) 22); HSSFRow linha25 = sheet.createRow((short) 24); // ------------------------------------------------------------------------ // /* Mesclando as clulas. ------------------------------------------ */ sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(19, 19, 0, 2)); // ------------------------------------------------------------------------ // /* Definindo a largura das colunas. ------------------------------- */ sheet.setColumnWidth(0, 12000); sheet.setColumnWidth(1, 5200); sheet.setColumnWidth(2, 6500); // ------------------------------------------------------------------------ // /* Definindo os estilos das clulas. ------------------------------ */ CellStyle headerStyle = workbook.createCellStyle(); Font headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 20); headerStyle.setFont(headerFont); headerStyle.setAlignment(HorizontalAlignment.CENTER); CellStyle cellCentered = workbook.createCellStyle(); cellCentered.setAlignment(HorizontalAlignment.CENTER); CellStyle cellFontBold = workbook.createCellStyle(); Font fontBold = workbook.createFont(); fontBold.setBold(true); cellFontBold.setFont(fontBold); CellStyle cellResulted = workbook.createCellStyle(); Font resultFont = workbook.createFont(); resultFont.setBold(true); cellResulted.setFont(resultFont); cellResulted.setAlignment(HorizontalAlignment.CENTER); Cell cell; // ------------------------------------------------------------------------ // /* Criando as Colunas da Tabela ----------------------------------- */ //Colunas da linha 1 header1.createCell(0).setCellValue("Dados Fornecidos"); //Colunas da linha 2 linha02.createCell(0).setCellValue("Data de Entrada"); linha02.createCell(2).setCellValue(this.rst.getDataEntrada()); //Colunas da Linha 3 linha03.createCell(0).setCellValue("Data de Sada"); linha03.createCell(2).setCellValue(this.rst.getDataSaida()); //Colunas da Linha 4 linha04.createCell(0).setCellValue("Salrio Informado"); linha04.createCell(2).setCellValue(this.rst.getSalario()); //Colunas da Linha 5 linha05.createCell(0).setCellValue("Motivo da Sada"); linha05.createCell(2).setCellValue(this.rst.getMotivoRes()); //Colunas da linha 8 header2.createCell(0).setCellValue("Resciso"); //Colunas da linha 9 linha09.createCell(0).setCellValue("Item"); linha09.createCell(1).setCellValue("Referncia"); linha09.createCell(2).setCellValue("Valor"); //Colunas da Linha 10 linha10.createCell(0).setCellValue("Saldo Salrio"); linha10.createCell(1).setCellValue(this.rst.getTotDiasTrabUltMes()); linha10.createCell(2).setCellValue(this.rst.getUltSalario()); //Colunas da Linha 11 linha11.createCell(0).setCellValue("13 Proporcional"); linha11.createCell(1).setCellValue(this.rst.getTotMesesTrabUltAno()); linha11.createCell(2).setCellValue(this.rst.getVlrDecimo()); //Colunas da Linha 12 linha12.createCell(0).setCellValue("Frias Proporcional"); linha12.createCell(1).setCellValue(this.rst.getTotMesesAqFerias()); linha12.createCell(2).setCellValue(this.rst.getVlrFerias()); //Colunas da Linha 13 linha13.createCell(0).setCellValue("1/3 Frias Proporcional"); linha13.createCell(1).setCellValue("-"); linha13.createCell(2).setCellValue(this.rst.getVlrTercoFerias()); //Colunas da Linha 14 linha14.createCell(0).setCellValue("Frias Vencidas"); linha14.createCell(1).setCellValue(this.rst.getTotFeriasVenc()); linha14.createCell(2).setCellValue(this.rst.getVlrFeriasVenc()); //Colunas da linha 15 linha15.createCell(0).setCellValue("Aviso Prvio"); linha15.createCell(1).setCellValue(this.rst.getTotDiasAviso()); linha15.createCell(2).setCellValue(this.rst.getVlrAvisoP()); //Colunas da linha 17 linha17.createCell(0).setCellValue("Valor Total"); linha17.createCell(1).setCellValue("-"); linha17.createCell(2).setCellValue(this.rst.getVlrTotVenc()); //Colunas da Linha 20 header3.createCell(0).setCellValue("FGTS"); //Colunas da Linha 21 linha21.createCell(0).setCellValue("Valores do FGTS estaro disponveis para saque?"); linha21.createCell(2).setCellValue(this.rst.getReceberFgts()); //Colunas da Linha 22 linha22.createCell(0).setCellValue("Saldo FGTS"); linha22.createCell(2).setCellValue(this.rst.getSaldoFgts()); //Colunas da Linha 23 linha23.createCell(0).setCellValue("Multa de 40%"); linha23.createCell(2).setCellValue(this.rst.getVlrMulta()); //Colunas da Linha 25 linha25.createCell(0).setCellValue("Valor total"); linha25.createCell(2).setCellValue(this.rst.getVlrTotFgts()); // ------------------------------------------------------------------------ // /* Aplicando os estilos nas clulas ------------------------------- */ cell = header1.getCell(0); cell.setCellStyle(headerStyle); header1.setRowStyle(headerStyle); header1.setHeightInPoints(30); cell = header2.getCell(0); cell.setCellStyle(headerStyle); header2.setRowStyle(headerStyle); header2.setHeightInPoints(30); cell = header3.getCell(0); cell.setCellStyle(headerStyle); header3.setRowStyle(headerStyle); header3.setHeightInPoints(30); linha02.getCell(2).setCellStyle(cellCentered); linha03.getCell(2).setCellStyle(cellCentered); linha04.getCell(2).setCellStyle(cellCentered); linha05.getCell(2).setCellStyle(cellCentered); linha09.getCell(1).setCellStyle(cellCentered); linha09.getCell(2).setCellStyle(cellCentered); linha10.getCell(1).setCellStyle(cellCentered); linha10.getCell(2).setCellStyle(cellCentered); linha11.getCell(1).setCellStyle(cellCentered); linha11.getCell(2).setCellStyle(cellCentered); linha12.getCell(1).setCellStyle(cellCentered); linha12.getCell(2).setCellStyle(cellCentered); linha13.getCell(1).setCellStyle(cellCentered); linha13.getCell(2).setCellStyle(cellCentered); linha14.getCell(1).setCellStyle(cellCentered); linha14.getCell(2).setCellStyle(cellCentered); linha15.getCell(1).setCellStyle(cellCentered); linha15.getCell(2).setCellStyle(cellCentered); linha17.getCell(0).setCellStyle(cellFontBold); linha17.getCell(1).setCellStyle(cellCentered); linha17.getCell(2).setCellStyle(cellResulted); linha21.getCell(2).setCellStyle(cellCentered); linha22.getCell(2).setCellStyle(cellCentered); linha23.getCell(2).setCellStyle(cellCentered); linha25.getCell(0).setCellStyle(cellFontBold); linha25.getCell(2).setCellStyle(cellResulted); // ------------------------------------------------------------------------ // /* Escrever, salvar e fechar o arquivo ---------------------------- */ workbook.write(arquivo); // Escrevendo no arquivo. workbook.close(); // Salvando e fechando o arquivo. return true; } catch (Exception e) { System.err.println("No foi possvel gerar seu arquivo!" + " \n" + e.getLocalizedMessage()); return false; } }
From source file:br.luck.managerbean.PrincipalBean.java
/*** * Exporta para arquivo xls//from w w w .j a v a2 s .c o m * @param document */ public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:br.rio.puc.inf.les.controller.ApartamentoBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);//from w w w .jav a 2s .c o m HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:br.solutio.licita.controlador.ControladorPregao.java
public void editandoXlsParaExportar(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet planilha = wb.getSheetAt(0); //Move as celulas selecionadas para baixo de acordo com o valor informado planilha.shiftRows(planilha.getFirstRowNum(), planilha.getLastRowNum(), 5); HSSFRow linha0 = planilha.createRow(0); linha0.createCell(0).setCellValue("Instituio Licitadora:"); planilha.addMergedRegion(new CellRangeAddress(0, 0, 0, 1)); linha0.createCell(2)//from w w w . ja v a 2 s.c o m .setCellValue(" " + getEntidade().getInstituicaoLicitadora().getPessoaJuridica().getNomeFantasia()); planilha.addMergedRegion(new CellRangeAddress(0, 0, 2, 6)); HSSFRow linha1 = planilha.createRow(1); linha1.createCell(0).setCellValue("Numero do Pregao:"); planilha.addMergedRegion(new CellRangeAddress(1, 1, 0, 1)); linha1.createCell(2).setCellValue(" " + getEntidade().getNumeroPregao()); planilha.addMergedRegion(new CellRangeAddress(1, 1, 2, 6)); HSSFRow linha2 = planilha.createRow(2); linha2.createCell(0).setCellValue("Numero do Processo:"); planilha.addMergedRegion(new CellRangeAddress(2, 2, 0, 1)); linha2.createCell(2).setCellValue(" " + getEntidade().getNumeroProcesso()); planilha.addMergedRegion(new CellRangeAddress(2, 2, 2, 6)); HSSFRow linha3 = planilha.createRow(3); linha3.createCell(0).setCellValue("Empresa Licitante:"); planilha.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); linha3.createCell(2).setCellValue("Preencha com o nome de sua Empresa"); planilha.addMergedRegion(new CellRangeAddress(3, 3, 2, 6)); HSSFRow linha4 = planilha.createRow(4); //Nova coluna para a empresas adicionarem seus valores HSSFRow linha5 = planilha.getRow(5); HSSFCell celula5 = linha5.createCell(5); celula5.setCellValue("Valor do Licitante"); //for para ajustar automaticamente o tamnho das colunas for (int i = 0; i < 6; i++) { planilha.autoSizeColumn(i); } //Cor da linha de titulos da tabela HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < linha5.getPhysicalNumberOfCells(); i++) { HSSFCell cell = linha5.getCell(i); cell.setCellStyle(cellStyle); } CellStyle unlockedCellStyle = wb.createCellStyle(); unlockedCellStyle.setLocked(false); HSSFCell celula2 = linha3.getCell(2); celula2.setCellStyle(unlockedCellStyle); }
From source file:br.solutio.licita.servico.ServicoSessao.java
@Override public boolean filtraPlanilha(UploadedFile uploadArquivo) { HSSFWorkbook wb = null;/*from ww w .ja v a 2s.c om*/ try { wb = new HSSFWorkbook(uploadArquivo.getInputstream()); } catch (IOException ex) { Logger.getLogger(ControladorSessao.class.getName()).log(Level.SEVERE, "Error InputStream ArquivoProposta", ex); } HSSFSheet planilha = wb.getSheetAt(0); Logger.getLogger(ControladorSessao.class.getName()).log(Level.INFO, " TAMANHO DA PLANILHA: {0}", planilha.getLastRowNum()); //Indica o numero da coluna onde inicia os valores que sero retirado int indInicioValores = 6; // +1, devido a necessidade de contar mais uma coluna propostas = new Double[(planilha.getLastRowNum() + 1) - indInicioValores][2]; for (int i = 6; i <= planilha.getLastRowNum(); i++) { HSSFRow linha = planilha.getRow(i); String conversao = linha.getCell(0).getStringCellValue(); Double convertido = Double.parseDouble(conversao); propostas[i - indInicioValores][0] = convertido; propostas[i - indInicioValores][1] = linha.getCell(5).getNumericCellValue(); numTable.add(linha.getCell(5).getNumericCellValue()); Logger.getLogger(ControladorSessao.class.getName()).log(Level.INFO, numTable.toString()); } return !numTable.isEmpty(); }