List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook(InputStream s) throws IOException
From source file:br.eti.rogerioaguilar.minhasclasses.util.excel.leitor.LeitorExcel.java
License:Open Source License
/** * Realiza o processamento de leitura seguindo o fluxo: * //w w w.j ava 2s . 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:br.solutio.licita.servico.ServicoSessao.java
@Override public boolean filtraPlanilha(UploadedFile uploadArquivo) { HSSFWorkbook wb = null;//from w ww .j a va 2 s. co m 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(); }
From source file:br.ufmt.periscope.importer.impl.ESPACENETPatentImporter.java
private void xlsManipulator() { try {/*from w w w. j av a 2s . c o m*/ wb = new HSSFWorkbook(content); sheet = wb.getSheetAt(0); rowIterator = sheet.iterator(); //Pulando primeiras linhas rowIterator.next(); //Logotipo rowIterator.next();// Numero de resultados encontrados na busca rowIterator.next(); // Titulo da pesquisa rowIterator.next(); // Quantidade de Publicaes exibidas rowIterator.next(); // Nome das Colunas (Titulo, Publicao, Autor ...) } catch (IOException ex) { Logger.getLogger(ESPACENETPatentImporter.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:br.unesp.rc.desafio.utils.Spreadsheet.java
public static ArrayList<String> ReadXlsSpreadsheet(File spreadsheet) { /*/*from ww w. j a v a2 s . co m*/ Constructing File */ ArrayList values = new ArrayList<String>(); FileInputStream inputStr = null; try { inputStr = new FileInputStream(spreadsheet); } catch (FileNotFoundException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Workbook currentSpreadsheetFile = null; try { HSSFRow row; currentSpreadsheetFile = new HSSFWorkbook(inputStr); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Sheet sheet = currentSpreadsheetFile.getSheetAt(0); Iterator<Row> rowItr = sheet.rowIterator(); while (rowItr.hasNext()) { row = (HSSFRow) rowItr.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = ""; switch (cell.getCellTypeEnum()) { default: // cellValue = cell.getCellFormula(); cellValue = Double.toString(cell.getNumericCellValue()); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); break; case NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); case BLANK: break; case STRING: break; } if (!cell.getStringCellValue().isEmpty()) { values.add(cell.getStringCellValue()); values.add(","); // System.out.println("HOLD IT"); } else { values.add("0"); values.add(","); // System.out.println("OBJECTION!!"); } //System.out.print(cell.getStringCellValue() + " \t\t " ); } //System.out.println(); values.add(";"); } try { inputStr.close(); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } //System.out.println(values.get(0)); return values; }
From source file:browsermator.com.MyTable.java
MyTable(String csvFile) {
DataFile = csvFile;//w w w. ja va 2s .co m
DataTable = new JTable();
myEntries = new ArrayList<>();
File filecheck = new File(csvFile);
if (filecheck.isAbsolute()) {
String[] left_right_side_of_dot = csvFile.split("\\.");
String file_extension = left_right_side_of_dot[left_right_side_of_dot.length - 1];
switch (file_extension) {
case "xls":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception e) {
System.out.println("Error occurred while reading XLS file: " + e.toString());
}
break;
case "xlsx":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception ex) {
System.out.print("Exception during XLSX import: " + ex.toString());
}
break;
case "csv":
try {
CSVFileReader = new CSVReader(new FileReader(DataFile), ',', '"', '\0');
myEntries = CSVFileReader.readAll();
} catch (Exception e) {
}
}
columnnames = (String[]) myEntries.get(0);
DefaultTableModel tableModel = new DefaultTableModel(columnnames, myEntries.size() - 1);
rowcount = tableModel.getRowCount();
this.number_of_records = rowcount;
for (int x = 0; x < rowcount + 1; x++) {
int columnnumber = 0;
if (x > 0) {
for (String thiscellvalue : (String[]) myEntries.get(x)) {
tableModel.setValueAt(thiscellvalue, x - 1, columnnumber);
columnnumber++;
}
}
}
DataTable = new JTable(tableModel);
int number_of_rows = DataTable.getRowCount();
if (number_of_rows < 20) {
DataTable.setPreferredScrollableViewportSize(
new Dimension(1200, number_of_rows * DataTable.getRowHeight()));
}
} else {
columnnames[0] = "Stored URL List:" + csvFile;
DefaultTableModel tableModel = new DefaultTableModel(columnnames, 0);
DataTable = new JTable(tableModel);
DataTable.getColumnModel().getColumn(0).setPreferredWidth(200);
DataTable.setPreferredScrollableViewportSize(new Dimension(20, 0));
}
}
From source file:bs.compra.proceso.ActualizarPreciosCostoBean.java
public void procesarArchivoExcel() { try {//from w w w . j a v a2s. c o m FileInputStream file = new FileInputStream(new File(pathArchivoExcel)); String[] split = pathArchivoExcel.split("\\."); String extension = split[split.length - 1].toLowerCase(); if (extension.equals("xls")) { HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); leerDatosExcel(rowIterator); } else if (extension.equals("xls")) { XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); leerDatosExcel(rowIterator); } else { JsfUtil.addErrorMessage( "Formato de archivo incorrecto. El archivo debe tener extensin xls o xlsx"); } } catch (IOException ex) { Logger.getLogger(ActualizarPreciosCostoBean.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ca.uwo.csd.cs2212.team02.DailyTips.java
/** * Draw UI/*from ww w . j a va2 s.co m*/ */ private void initUI() { this.setSize(450, 350); BufferedImage myImage = null; try { myImage = ImageIO.read(new File("src/main/resources/images/TipCloud.png")); } catch (IOException ex) { Logger.getLogger(DailyTips.class.getName()).log(Level.SEVERE, null, ex); } this.setContentPane(new ImagePanel(getScaledImage(myImage, this.getWidth(), this.getHeight()))); this.getContentPane().setLayout(new GridBagLayout()); c = new GridBagConstraints(); c.insets = new Insets(0, 0, 5, 5); JButton prev = new JButton("Prev"); prev.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { decreaseIndex(); } }); prev.setSize(new Dimension(50, 50)); JButton next = new JButton("Next"); next.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { increaseIndex(); } }); JButton exit = new JButton("Close"); exit.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { dispose(); } }); JLabel title = new JLabel("Tip of the Day"); title.setFont(new Font("Lucida Grande", Font.BOLD, 14)); c.anchor = GridBagConstraints.FIRST_LINE_START; c.gridx = 0; c.gridy = 0; this.add(prev, c); c.anchor = GridBagConstraints.PAGE_START; c.gridx = 1; this.add(next, c); c.gridx = 2; this.add(title, c); c.gridx = 3; c.anchor = GridBagConstraints.FIRST_LINE_END; this.add(exit, c); FileInputStream file = null; try { file = new FileInputStream(new File("src/main/resources/HealthTips.xls")); } catch (FileNotFoundException ex) { Logger.getLogger(DailyTips.class.getName()).log(Level.SEVERE, null, ex); } try { //Get the workbook instance for XLS file this.workbook = new HSSFWorkbook(file); } catch (IOException ex) { Logger.getLogger(DailyTips.class.getName()).log(Level.SEVERE, null, ex); } //Get first sheet from the workbook this.sheet = workbook.getSheetAt(0); this.NumTips = sheet.getPhysicalNumberOfRows(); Random NumberGenerator = new Random(); this.tipIndex = NumberGenerator.nextInt(this.NumTips); }
From source file:cdc.impl.datasource.office.ExcelDataSource.java
License:LGPL
private static DataColumnDefinition[] readDataModel(String sourceName, Map params) throws IOException, RJException { BufferedInputStream is = null; try {//from ww w . ja v a 2 s . com is = new BufferedInputStream(new FileInputStream((String) params.get(PARAM_FILE))); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is)); String[] sheets; if (params.get(PARAM_SHEET) != null) { sheets = new String[] { (String) params.get(PARAM_SHEET) }; } else { sheets = new String[wb.getNumberOfSheets()]; for (int i = 0; i < sheets.length; i++) { sheets[i] = wb.getSheetName(i); } } if (sheets.length == 0) { throw new RJException("Excel file " + params.get(PARAM_FILE) + " does not provide any sheets."); } List cols = new ArrayList(); HSSFSheet sheet = wb.getSheet(sheets[0]); if (sheet == null) { //System.out.println("Thorwing: " + "Sheet " + sheets[0] + " is not provided by file " + params.get(PARAM_FILE)); throw new RJException( "Sheet '" + sheets[0] + "' is not provided by file " + params.get(PARAM_FILE)); } HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); //first row should provide data model HSSFRow row = sheet.getRow(0); evaluator.setCurrentRow(row); for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) { HSSFCell cell = row.getCell(i); cols.add(new ExcelDataColumnDefinition(decodeValue(cell, evaluator), DataColumnDefinition.TYPE_STRING, sourceName, i)); } for (int i = 1; i < sheets.length; i++) { sheet = wb.getSheet(sheets[i]); if (sheet == null) { throw new RJException("Sheet '" + params.get(PARAM_SHEET) + "' is not provided by file " + params.get(PARAM_FILE)); } evaluator = new HSSFFormulaEvaluator(sheet, wb); //first row should provide data model row = sheet.getRow(0); evaluator.setCurrentRow(row); List localCols = new ArrayList(); for (i = 0; i < row.getPhysicalNumberOfCells(); i++) { HSSFCell cell = row.getCell(i); DataColumnDefinition col = new ExcelDataColumnDefinition(decodeValue(cell, evaluator), DataColumnDefinition.TYPE_STRING, sourceName, i); localCols.add(col); } List toRemove = new ArrayList(); for (Iterator iterator = cols.iterator(); iterator.hasNext();) { DataColumnDefinition object = (DataColumnDefinition) iterator.next(); if (!localCols.contains(object)) { toRemove.add(object); } } cols.removeAll(toRemove); } return (DataColumnDefinition[]) cols.toArray(new DataColumnDefinition[] {}); } finally { if (is != null) { is.close(); } } }
From source file:cdc.impl.datasource.office.ExcelDataSource.java
License:LGPL
private void openFile() throws IOException, RJException { stream = new BufferedInputStream(new FileInputStream(getProperty(PARAM_FILE))); workbook = new HSSFWorkbook(new POIFSFileSystem(stream)); iterator = new SheetsIterator(workbook, getProperty(PARAM_SHEET) == null ? null : new String[] { getProperty(PARAM_SHEET) }); filesOpen = true;//www .j a va 2 s.c o m closed = false; }
From source file:ch.astina.hesperid.util.jasper.JasperExcelStreamResponse.java
License:Apache License
@Override public void exportReportToStream(JasperPrint jasperPrint, OutputStream outputStream) throws Exception { JRXlsExporter exporter = new JRXlsExporter(); ByteArrayOutputStream baos = new ByteArrayOutputStream(); exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint); exporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, baos); exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, this.removeEmptySpaceBetweenRows); exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, this.removeEmptySpaceBetweenColumns); exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, this.printWhitePageBackground); exporter.setParameter(JRXlsExporterParameter.IS_FONT_SIZE_FIX_ENABLED, this.fixFontSize); exporter.setParameter(JRXlsExporterParameter.IS_IGNORE_GRAPHICS, this.ignoreGraphics); exporter.exportReport();//from w ww . j av a2 s . c o m HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(baos.toByteArray())); workbook.getSheetAt(0).setAutobreaks(true); workbook.getSheetAt(0).getPrintSetup().setFitHeight((short) jasperPrint.getPages().size()); workbook.getSheetAt(0).getPrintSetup().setFitWidth((short) 1); SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy"); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); for (Integer x = 0; x < workbook.getSheetAt(0).getPhysicalNumberOfRows(); x++) { HSSFRow row = workbook.getSheetAt(0).getRow(x); Iterator<Cell> ci = row.cellIterator(); Cell c = null; Date d = null; while (ci.hasNext()) { c = ci.next(); try { d = sdf.parse(c.getStringCellValue().trim()); c.setCellValue(d); c.setCellStyle(cellStyle); } catch (Exception e) { } } } workbook.write(outputStream); }