List of usage examples for org.apache.poi.ss.usermodel Sheet createRow
Row createRow(int rownum);
From source file:br.ufpa.psi.comportamente.labgame.relatorios.RelatorioJogadasExperimento.java
License:Open Source License
public InputStream relatorioOntogenese(Long idExp) throws FileNotFoundException, IOException { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Relatrio Ontognese"); JogadaDAO jogadaDAO = new JogadaDAO(); jogadaDAO.beginTransaction();// w w w .jav a 2 s . co m List<Jogada> jogadasAux = jogadaDAO.encontrarPorExperimento(idExp); jogadaDAO.stopOperation(false); ExperimentoDAO expDAO = new ExperimentoDAO(); expDAO.beginTransaction(); Experimento experimento = expDAO.find(Experimento.class, idExp); expDAO.stopOperation(false); JogadorDAO jogDAO = new JogadorDAO(); jogDAO.beginTransaction(); List<Jogador> jogadoresTotais = jogDAO.encontraPorExperimento(experimento); jogDAO.stopOperation(false); //CRIA O CABEALHO "ONTOGNESE int quantidadeColunas = 6; int quantidadeJogadoresPorCiclo = experimento.getTamanhoFilaJogadores(); //Vai pegar o valor de acordo com o experimento. int larguraColuna = 4600; int colunaAtual; int colunaFinalOntogenese = (quantidadeColunas * quantidadeJogadoresPorCiclo) + 1; // --- DEFINE AS PROPRIEDADES DAS CLULAS sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(0, 2, 1, 1)); sheet.setColumnWidth(0, 1500); sheet.setColumnWidth(1, 2000); sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, colunaFinalOntogenese)); sheet.addMergedRegion(new CellRangeAddress(1, 2, colunaFinalOntogenese + 1, colunaFinalOntogenese + 1)); sheet.setColumnWidth(colunaFinalOntogenese + 1, 3000); sheet.addMergedRegion(new CellRangeAddress(1, 3, colunaFinalOntogenese + 2, colunaFinalOntogenese + 2)); sheet.setColumnWidth(colunaFinalOntogenese + 2, 4000); // --- FIM //for (int i = 0; i < quantidadeJogadoresPorCiclo; i++) //if(i == 1){ sheet.setColumnWidth(2, larguraColuna); // Campo com o nome do participante fica nessa coluna. colunaAtual = 2 + quantidadeColunas; //} else { sheet.setColumnWidth((colunaAtual), larguraColuna); colunaAtual += quantidadeColunas; sheet.setColumnWidth(colunaAtual, larguraColuna); //} // ---DEFINE AS CORES DE CADA FONTE XSSFFont fonteBranca = (XSSFFont) wb.createFont(); fonteBranca.setColor(new XSSFColor(Color.WHITE)); XSSFFont fonteNegra = (XSSFFont) wb.createFont(); fonteNegra.setColor(new XSSFColor(Color.BLACK)); XSSFFont fonteVermelha = (XSSFFont) wb.createFont(); fonteVermelha.setColor(new XSSFColor(Color.RED)); // --- FIM // --- DEFINE ESTILOS CLULAS //ESTILO COLUNA LINHA XSSFCellStyle estiloColunaLinha = (XSSFCellStyle) wb.createCellStyle(); estiloColunaLinha.setVerticalAlignment(VerticalAlignment.CENTER); estiloColunaLinha.setAlignment(CellStyle.ALIGN_CENTER); estiloColunaLinha.setFillForegroundColor(new XSSFColor(Color.LIGHT_GRAY)); estiloColunaLinha.setFillPattern(CellStyle.SOLID_FOREGROUND); estiloColunaLinha.getFont().setBold(true); estiloColunaLinha.setBorderBottom(BorderStyle.MEDIUM); estiloColunaLinha.setBorderLeft(BorderStyle.MEDIUM); estiloColunaLinha.setBorderRight(BorderStyle.MEDIUM); estiloColunaLinha.setBorderTop(BorderStyle.MEDIUM); XSSFCellStyle estiloColunaColuna = (XSSFCellStyle) wb.createCellStyle(); estiloColunaColuna.setVerticalAlignment(VerticalAlignment.CENTER); estiloColunaColuna.setAlignment(CellStyle.ALIGN_CENTER); estiloColunaColuna.getFont().setBold(true); //ESTILO CABEALHO XSSFCellStyle estiloCabecalhoColunaAB = (XSSFCellStyle) wb.createCellStyle(); estiloCabecalhoColunaAB.setVerticalAlignment(VerticalAlignment.CENTER); estiloCabecalhoColunaAB.setAlignment(CellStyle.ALIGN_CENTER); estiloCabecalhoColunaAB.setFillForegroundColor(new XSSFColor(Color.LIGHT_GRAY)); estiloCabecalhoColunaAB.setFillPattern(CellStyle.SOLID_FOREGROUND); estiloCabecalhoColunaAB.getFont().setBold(true); XSSFCellStyle estiloCabecalhoColunaP = (XSSFCellStyle) wb.createCellStyle(); estiloCabecalhoColunaP.setVerticalAlignment(VerticalAlignment.CENTER); estiloCabecalhoColunaP.setAlignment(CellStyle.ALIGN_CENTER); estiloCabecalhoColunaP.setFont(fonteNegra); estiloCabecalhoColunaP.getFont().setBold(true); //ESTILO MUDANA DE CICLO XSSFCellStyle estiloMudancaCiclo = (XSSFCellStyle) wb.createCellStyle(); estiloMudancaCiclo.setVerticalAlignment(VerticalAlignment.CENTER); estiloMudancaCiclo.setAlignment(CellStyle.ALIGN_CENTER); estiloMudancaCiclo.setFillForegroundColor(new XSSFColor(Color.RED)); estiloMudancaCiclo.setFillPattern(CellStyle.SOLID_FOREGROUND); estiloMudancaCiclo.setFont(fonteBranca); //ESTILO CICLO SEM MUDANA XSSFCellStyle estiloCicloSemMudanca = (XSSFCellStyle) wb.createCellStyle(); estiloCicloSemMudanca.setVerticalAlignment(VerticalAlignment.CENTER); estiloCicloSemMudanca.setAlignment(CellStyle.ALIGN_CENTER); estiloCicloSemMudanca.setFont(fonteNegra); //ESTILO NOME PARTICIPANTE XSSFCellStyle estiloNomeP = (XSSFCellStyle) wb.createCellStyle(); estiloNomeP.setVerticalAlignment(VerticalAlignment.CENTER); estiloNomeP.setAlignment(CellStyle.ALIGN_CENTER); estiloNomeP.setBorderBottom(BorderStyle.DOTTED); estiloNomeP.setBorderTop(BorderStyle.DOTTED); estiloNomeP.setFillForegroundColor(new XSSFColor(Color.BLACK)); estiloNomeP.setFillPattern(CellStyle.SOLID_FOREGROUND); estiloNomeP.setFont(fonteBranca); estiloNomeP.getFont().setBold(true); //ESTILO ESTABILIDADE XSSFCellStyle estiloEstabilidade = (XSSFCellStyle) wb.createCellStyle(); estiloEstabilidade.setVerticalAlignment(VerticalAlignment.CENTER); estiloEstabilidade.setAlignment(CellStyle.ALIGN_CENTER); estiloEstabilidade.setFont(fonteVermelha); estiloEstabilidade.getFont().setBold(true); // --- FIM int cr = 0; // --- CRIA PRIMEIRA COLUNA (CABEALHO) XSSFRow row1 = (XSSFRow) sheet.createRow((short) cr++); //CRIA CLULA 1 XSSFCell c1 = row1.createCell(0); c1.setCellValue("FASE"); c1.setCellStyle(estiloCabecalhoColunaAB); //CRIA CLULA 2 XSSFCell c2 = row1.createCell(1); c2.setCellValue("CICLO"); c2.setCellStyle(estiloCabecalhoColunaAB); //CRIA CLULA 3 (ONTOGNESE) XSSFCell cOnto = row1.createCell(2); cOnto.setCellValue("ONTOGNESE"); cOnto.setCellStyle(estiloCicloSemMudanca); //CRIA CLULA 'CC' XSSFRow row2 = (XSSFRow) sheet.createRow(1); XSSFCell cCC = row2.createCell(colunaFinalOntogenese + 1); cCC.setCellValue("CC"); cCC.setCellStyle(estiloCicloSemMudanca); //CRIA CLULA 'ESTABILIDADE' XSSFCell cEstab = row2.createCell(colunaFinalOntogenese + 2); cEstab.setCellValue("ESTABILIDADE"); cEstab.setCellStyle(estiloEstabilidade); // --- FIM int contadorCelulasCabecalho = 2; int contadorAcertosCultural = 0; XSSFRow row3 = (XSSFRow) sheet.createRow((short) 2); //GERA O CABEALHO DAS JOGADAS for (int i = 0; i < experimento.getTamanhoFilaJogadores(); i++) { //CRIA CLULA 3 NA LINHA 3 XSSFCell c3 = row3.createCell(contadorCelulasCabecalho++); c3.setCellValue("P"); c3.setCellStyle(estiloCabecalhoColunaP); //CRIA CLULA 4 NA LINHA 3 XSSFCell c4 = row3.createCell(contadorCelulasCabecalho++); c4.setCellValue("Linha"); c4.setCellStyle(estiloCabecalhoColunaP); //CRIA CLULA 5 NA LINHA 3 XSSFCell c5 = row3.createCell(contadorCelulasCabecalho++); c5.setCellValue("Cor"); c5.setCellStyle(estiloCabecalhoColunaP); //CRIA CLULA 6 NA LINHA 3 XSSFCell c6 = row3.createCell(contadorCelulasCabecalho++); c6.setCellValue("Col"); c6.setCellStyle(estiloCabecalhoColunaP); //CRIA CLULA 7 NA LINHA 3 XSSFCell c7 = row3.createCell(contadorCelulasCabecalho++); c7.setCellValue("CI"); c7.setCellStyle(estiloCabecalhoColunaP); //CRIA CLULA 8 NA LINHA 3 XSSFCell c8 = row3.createCell(contadorCelulasCabecalho++); c8.setCellValue("CI Cum"); c8.setCellStyle(estiloCabecalhoColunaP); } //VARI?VEIS INICIAIS DA ELABORAO DO RELATRIO int quantidadeCiclosTotais = (jogadasAux.size() / experimento.getTamanhoFilaJogadores()); int contRowJogadas = 4; int contadorCiclo = 1; //INICIA LISTA DOS JOGADORES POR ORDEM List<Jogador> jogadoresAtuais = new ArrayList<>(); int contOrdem = 1; for (int i = 0; i < quantidadeJogadoresPorCiclo; i++) { for (Jogador jgdr : jogadoresTotais) { if (jgdr.getOrdem() == contOrdem) { jogadoresAtuais.add(jgdr); contOrdem++; break; } } } //FAZ A REMOO DO(S) ELEMENTO(S) DA LISTA PRA POUPAR RECURSO EM BUSCA FUTURA jogadoresTotais.removeAll(jogadoresAtuais); //CRIA INSTNCIA CONTROLE DE PONTUAO CULTURAL int pontCulturalCiclo; //FOR (JOGADOR : JOGADORES POR CICLO) //CRIA LISTA DE CADA JOGADOR AT FIM DO CICLO for (int i = 0; i < quantidadeCiclosTotais; i++) { //PEGA JOGADAS DO CICLO List<Jogada> jogadasCiclo = new ArrayList<>(); for (Jogada jogada : jogadasAux) { if (jogada.getRodada() == i + 1) { jogadasCiclo.add(jogada); if (jogadasCiclo.size() == quantidadeJogadoresPorCiclo) { break; } } } //FAZ A REMOO DO(S) ELEMENTO(S) DA LISTA PRA POUPAR RECURSO EM BUSCA FUTURA jogadasAux.removeAll(jogadasCiclo); //VERIFICA SE A ORDEM MUDOU int contJogadoresIguais = 0; List<Jogada> jogadasARemover = new ArrayList<>(); for (Jogador jogador : jogadoresAtuais) { for (Jogada jogada : jogadasCiclo) { if (jogada.getJogador().compareTo(jogador) == 0) { jogador.setUltimaJogada(jogada); jogador.incrementaPontuacaoRelatorio(); jogadasARemover.add(jogada); contJogadoresIguais++; } } } jogadasCiclo.removeAll(jogadasARemover); boolean mudouGeracaoCiclo = false; if (contJogadoresIguais == quantidadeJogadoresPorCiclo) { //CONTINUA COM OS MESMOS JOGADORES } else { mudouGeracaoCiclo = true; Jogador jogadorARemover = new Jogador(); jogadoresAtuais.remove(0); for (Jogador jgdr : jogadoresTotais) { if (jgdr.getOrdem() == contOrdem) { //PEGA A PRIMEIRA POSIO PQ ESSA TEM QUE SER A NICA COM ELEMENTO jgdr.setUltimaJogada(jogadasCiclo.get(0)); jgdr.incrementaPontuacaoRelatorio(); jogadoresAtuais.add(jgdr); contOrdem++; jogadorARemover = jgdr; break; } } jogadoresTotais.remove(jogadorARemover); } // --- ENCERRA ETAPAS DE VERIFICAO, INICIA A POPULAO DE NOVA LINHA DO XLSX E //VERIFICA SE EXISTE PONTUAO CULTURAL. int contCellJogadas = 1; if (jogadoresAtuais.get(0).getUltimaJogada().getPontuacaoCultural() != 0) { pontCulturalCiclo = jogadoresAtuais.get(0).getUltimaJogada().getPontuacaoCultural(); contadorAcertosCultural++; } else { pontCulturalCiclo = 0; } XSSFRow row = (XSSFRow) sheet.createRow((short) contRowJogadas); XSSFCell cell = row.createCell(contCellJogadas++); //CICLO if (contRowJogadas == 4 || mudouGeracaoCiclo == true) { //QUANDO HOUVER MUDANA DA GERAO cell.setCellValue(contadorCiclo++); cell.setCellStyle(estiloMudancaCiclo); } else { cell.setCellValue(contadorCiclo++); cell.setCellStyle(estiloCicloSemMudanca); } for (int j = 0; j < quantidadeJogadoresPorCiclo; j++) { //P XSSFCell cell1 = row.createCell(contCellJogadas++); cell1.setCellValue(jogadoresAtuais.get(j).getNome()); cell1.setCellStyle(estiloNomeP); //Linha XSSFCell cell2 = row.createCell(contCellJogadas++); cell2.setCellValue(jogadoresAtuais.get(j).getUltimaJogada().getLinhaSelecionada()); cell2.setCellStyle(estiloColunaLinha); //Cor XSSFCell cell3 = row.createCell(contCellJogadas++); cell3.setCellValue(jogadoresAtuais.get(j).getUltimaJogada().getCorSelecionada()); cell3.setCellStyle(EstiloCelula.retornaEstilo( jogadoresAtuais.get(j).getUltimaJogada().getCorSelecionada(), wb, fonteBranca, fonteNegra)); //Col XSSFCell cell4 = row.createCell(contCellJogadas++); cell4.setCellValue(jogadoresAtuais.get(j).getUltimaJogada().getColunaSelecionada()); cell4.setCellStyle(estiloColunaColuna); //CI XSSFCell cell5 = row.createCell(contCellJogadas++); cell5.setCellValue(jogadoresAtuais.get(j).getUltimaJogada().getPontuacaoIndividual()); cell5.setCellStyle(estiloColunaColuna); //CI Cum XSSFCell cell6 = row.createCell(contCellJogadas++); cell6.setCellValue(jogadoresAtuais.get(j).getPontuacaoExibidaRelatorio()); cell6.setCellStyle(estiloColunaColuna); } //CC XSSFCell cell7 = row.createCell(contCellJogadas++); cell7.setCellValue(pontCulturalCiclo); cell7.setCellStyle(estiloCabecalhoColunaAB); // ESTABILIDADE XSSFCell cell8 = row.createCell(contCellJogadas); cell8.setCellValue((contadorAcertosCultural * 100) / (i + 1) + "%"); cell8.setCellStyle(estiloColunaColuna); contRowJogadas++; } //ESCREVE O ARQUIVO byte[] bytes; try (ByteArrayOutputStream out = new ByteArrayOutputStream()) { wb.write(out); bytes = out.toByteArray(); } return new ByteArrayInputStream(bytes); }
From source file:business.SongExcelParser.java
private void songsToWorkbook(Workbook wb, ArrayList<SongContainer> songContainerList) { Row row;/* w w w . j av a 2s . c o m*/ int rowNumber = 1; Sheet sheet = wb.createSheet(); row = setRowHeader(sheet); for (SongContainer container : songContainerList) { for (Song song : container.getSongs()) { row = sheet.createRow(rowNumber); setRowInfo(row, song, container); rowNumber++; } } for (int x = 0; x < sheet.getRow(0).getPhysicalNumberOfCells(); x++) { sheet.autoSizeColumn(x); } }
From source file:business.SongExcelParser.java
private Row setRowHeader(Sheet sheet) { Row row = sheet.createRow(0); Cell cell = row.createCell(0);/* w ww . j a v a2 s. c om*/ cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("?lbum"); cell = row.createCell(1); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("Nombre de la cancin"); cell = row.createCell(2); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("Cantante"); cell = row.createCell(3); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("Duracin"); cell = row.createCell(4); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("Gnero musical"); cell = row.createCell(5); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("Path o ubicacin"); return row; }
From source file:campmanager.CampUI.java
public void exportToExcel() { JFrame parentFrame = new JFrame(); File fileToSave = null;//from www . j a va2s . c om ; JFileChooser fileChooser = new JFileChooser(); fileChooser.setDialogTitle("Export to Excel"); int userSelection = fileChooser.showSaveDialog(parentFrame); if (userSelection == JFileChooser.APPROVE_OPTION) { fileToSave = fileChooser.getSelectedFile(); // System.out.println("Save as file: " + fileToSave.getAbsolutePath()); } if (fileToSave != null) { String fileName = fileToSave.getAbsolutePath(); if (!fileName.endsWith(".xls")) fileName += ".xls"; try { DefaultTableModel dtm = (DefaultTableModel) jTable_records.getModel(); Workbook wb = new HSSFWorkbook(); CreationHelper createhelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); Row row = null; Cell cell = null; row = sheet.createRow(0); HSSFFont font = (HSSFFont) wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle style = (HSSFCellStyle) wb.createCellStyle(); style.setFont(font); for (int t = 0; t < dtm.getColumnCount() - 1; t++) { cell = row.createCell(t); cell.setCellStyle(style); cell.setCellValue(dtm.getColumnName(t)); } HSSFCellStyle style_gray = (HSSFCellStyle) wb.createCellStyle(); style_gray.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); // style_gray.setFillPattern(CellStyle.ALT_BARS); for (int i = 1; i <= dtm.getRowCount(); i++) { row = sheet.createRow(i); for (int j = 0; j < dtm.getColumnCount() - 1; j++) { cell = row.createCell(j); if (i % 2 == 0) cell.setCellStyle(style_gray); cell.setCellValue(dtm.getValueAt(i - 1, j).toString()); } } FileOutputStream out = new FileOutputStream(fileName); wb.write(out); out.close(); } catch (FileNotFoundException ex) { ex.printStackTrace(); } catch (IOException ex) { ex.printStackTrace(); } } }
From source file:cartel.DynamicDegreeCalculation.java
License:Open Source License
public void compute(Graph graph) throws FileNotFoundException, IOException { Map<String, Integer> weightedDegreeForOneCountryfForOneYear; Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("weighted degree"); Row row;// w ww . j ava 2 s . c o m Cell cell; Map<String, Integer> countryIndices = new TreeMap(); int index = 0; for (String country : CartelDynamic.europeanCountries) { index++; countryIndices.put(country, index); } //COLUMNS HEADER row = sheet.createRow((short) 0); index = 1; for (int i = 1948; i < 2009; i++) { cell = row.createCell(index); index++; cell.setCellValue(String.valueOf(i)); } //CREATING EMPTY CELLS FOR EACH ROW for (String country : countryIndices.keySet()) { row = sheet.createRow((countryIndices.get(country))); index = 0; for (int i = 1948; i <= 2009; i++) { row.createCell(index); index++; } } //FILLING FIRST COLUMN WITH COUNTRIES for (String country : countryIndices.keySet()) { row = sheet.getRow(countryIndices.get(country)); row.getCell(0).setCellValue(country); } int indexYear = 1; for (int i = 1948; i < 2009; i++) { weightedDegreeForOneCountryfForOneYear = new TreeMap(); for (Node node : graph.getNodes()) { String nodeLabel = node.getLabel(); int sumDegrees = 0; for (Edge edge : graph.getAllEdges()) { if (!edge.getSource().getLabel().equals(nodeLabel) & !edge.getTarget().getLabel().equals(nodeLabel)) { continue; } if (edge.getSource().getLabel().equals(edge.getTarget().getLabel())) { continue; } AttributeValueList attributeValueList = edge.getAttributeValues(); for (AttributeValue attributeValue : attributeValueList) { if (!attributeValue.getAttribute().getTitle().equals("freq")) { continue; } if (((Integer) attributeValue.getStartValue()) != i) { continue; } sumDegrees = sumDegrees + Integer.parseInt(attributeValue.getValue()); } } sumDegrees = sumDegrees / 2; row = sheet.getRow(countryIndices.get(nodeLabel)); cell = row.getCell(indexYear); cell.setCellValue(String.valueOf(sumDegrees)); } indexYear++; } String pathFile = "D:/workbook weighted degree.xlsx"; FileOutputStream fileOut = new FileOutputStream(pathFile); wb.write(fileOut); fileOut.close(); }
From source file:cfdi.clases.db.DerbyUtilities.java
License:Open Source License
/** * Exporta los registros de de CFDI datos generales o su detalle, con el filtro que se * haya utilizado en la interface grfica * /*from w ww.ja v a 2 s. c om*/ * @param query es el query filtradn para la tabla de CFDI y CFDI_DETALLE * @param nombre nombre del archivo * @param path directorio donde se va a crear el archivo de excel * @return the boolean */ public boolean exportarExcel(String query, String nombre, String path) { Connection connection = null; Statement st = null; ResultSet rs = null; boolean respuesta = false; BoneCP connectionPool = null; try { Class.forName(propiedades.getProperty("DB_DRIVER")); // setup the connection pool BoneCPConfig config = new BoneCPConfig(); config.setJdbcUrl(propiedades.getProperty("DB_SERVER")); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb config.setUsername(propiedades.getProperty("DB_USER")); config.setPassword(propiedades.getProperty("DB_PASSWORD")); config.setMinConnectionsPerPartition(5); config.setMaxConnectionsPerPartition(10); config.setPartitionCount(1); connectionPool = new BoneCP(config); // setup the connection pool FileOutputStream fileOut = new FileOutputStream(path + nombre + ".xlsx"); connection = connectionPool.getConnection(); // fetch a connection if (connection != null) { st = connection.createStatement(); rs = st.executeQuery(query); ResultSetMetaData metaData = rs.getMetaData(); int count = metaData.getColumnCount(); SXSSFWorkbook workbook = new SXSSFWorkbook(10000); Sheet sheet = workbook.createSheet(nombre); int rownum = 0; Row row = sheet.createRow(rownum++); CellStyle stylec = workbook.createCellStyle(); stylec.setBorderBottom(CellStyle.BORDER_THIN); stylec.setBottomBorderColor(IndexedColors.BLACK.getIndex()); Font fontc = workbook.createFont(); fontc.setBoldweight(Font.BOLDWEIGHT_BOLD); stylec.setFont(fontc); for (int i = 1; i <= count; i++) { row.createCell(i).setCellValue(metaData.getColumnName(i)); row.getCell(i).setCellStyle(stylec); } while (rs.next()) { Row rowh = sheet.createRow(rownum++); for (int i = 1; i <= count; i++) { if (metaData.getColumnTypeName(i).equalsIgnoreCase("INT") || metaData.getColumnTypeName(i).equalsIgnoreCase("INT UNSIGNED")) rowh.createCell(i).setCellValue(rs.getInt(i)); else if (metaData.getColumnTypeName(i).equalsIgnoreCase("DOUBLE")) rowh.createCell(i).setCellValue(rs.getDouble(i)); else rowh.createCell(i).setCellValue(rs.getString(i)); } } /*if(rownum<5000){ for (int i = 1; i <= count; i++) sheet.autoSizeColumn(i); }*/ try { workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { System.out.println("Error: export 1"); } catch (IOException e) { System.out.println("Error: export 2"); } respuesta = true; connectionPool.shutdown(); } } catch (SQLException e) { System.out.println("Error: insertDatos 3"); logger.log(Level.SEVERE, null, e); } catch (ClassNotFoundException ex) { logger.log(Level.SEVERE, null, ex); } catch (Exception ex) { System.out.println("Error: insertDatos 5"); logger.log(Level.SEVERE, null, ex); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { System.out.println("Error: insertDatos 4"); logger.log(Level.SEVERE, null, e); } } } return respuesta; }
From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java
/*** * Generate an Excel file based on a list of results. * Use the name of the fields described in the GenericResultRow to label the columns. * @param language Language of the label in the Excel file * @param sheetTitle Title of the sheet in the Excel file * @param tableTitle Title of the table in the Excel file * @param rows Arrays of rows to include in the listing * @param excelFilePath Path of the outputed file * @throws FileNotFoundException//from ww w . j av a2 s . co m * @throws IOException */ public static void exportToExcel(Translation.TRANSLATION_LANGUAGE language, String sheetTitle, String tableTitle, GenericResultRow[] rows, String excelFilePath) throws FileNotFoundException, IOException { //Workbook wb = new HSSFWorkbook(); //xls Workbook wb = new SXSSFWorkbook(); //xlsx //create new sheet Sheet sheet1 = wb.createSheet(sheetTitle); Row row; Cell cell; String translation; int rowIndex = 0; //add title row = sheet1.createRow((short) rowIndex); cell = row.createCell(0); cell.setCellValue(tableTitle); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontHeightInPoints((short) 24); //font.setFontName("Courier New"); style.setFont(font); cell.setCellStyle(style); //add rows for (int i = 0; i < rows.length; i++) { //if first line, write col names if (i == 0) { row = sheet1.createRow((short) rowIndex + 2); for (int j = 0; j < rows[i].getColNames().length; j++) { cell = row.createCell(j); //look for translation translation = Translation.getForKey(language, rows[i].getColNames()[j]); if (translation == null) { translation = rows[i].getColNames()[j]; //if doesn't found a translation for the column take name of col } cell.setCellValue(translation); } } row = sheet1.createRow((short) (rowIndex + i + 3)); for (int j = 0; j < rows[i].getColNames().length; j++) { cell = row.createCell(j); cell.setCellValue(rows[i].getValueAt(j).toString()); } } //write to the file FileOutputStream fileOut = new FileOutputStream(excelFilePath); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java
/*** * Prototype function. Not yet functional. * Allow to generate an age pyramid graphic in Excel by using an existing Excel Template. * @param language Language of the label in the Excel file * @param rows Arrays of rows to include in the listing * @param excelFilePath Path of the outputed file * @throws FileNotFoundException/*from w w w . java 2 s .c om*/ * @throws IOException * @throws InvalidFormatException */ public static void exportToAgePyramid(Translation.TRANSLATION_LANGUAGE language, GenericResultRow[] rows, String excelFilePath) throws FileNotFoundException, IOException, InvalidFormatException { //open template URL url = Translation.class.getClassLoader() .getResource("ch/bfh/lca/_15h/library/export/template/alter-pyramide-v2.xlsx"); //Workbook wb = WorkbookFactory.create(new File(url.getPath())); XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(url.getPath()))); Sheet sheet = wb.getSheetAt(0); //http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html //https://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook Row row; Cell cell; for (int i = 0; i < 20; i++) { row = sheet.getRow(i + 1); if (row == null) { row = sheet.createRow(i + 1); } for (int j = 0; j < 3; j++) { cell = row.getCell(j); if (cell == null) { cell = row.createCell(j); } switch (j) { case 0: cell.setCellValue(i); break; case 1: cell.setCellValue(i * j * -1); break; case 2: cell.setCellValue(i * j); break; } } } //redefine data range //http://thinktibits.blogspot.ch/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html XSSFSheet sheet1 = wb.getSheetAt(0); XSSFTable table = sheet1.getTables().get(0); CTTable cttable = table.getCTTable(); AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(20, 2)); /* Set Range to the Table */ cttable.setRef(my_data_range.formatAsString()); // cttable.setDisplayName("DATEN"); /* this is the display name of the table */ //cttable.setName("test"); /* This maps to "displayName" attribute in <table>, OOXML */ //cttable.setId(1L); //id attribute against table as long value /* //redefine data range Name rangeCell = wb.getName("DATEN"); //Set new range for named range //String reference = sheetName + "!$C$" + (deface + 1) + ":$C$" + (rowNum + deface); String reference = sheet.getSheetName() + "!$A$2:$C$20"; //Assigns range value to named range rangeCell.setRefersToFormula(reference); */ //write to the file FileOutputStream fileOut = new FileOutputStream(excelFilePath); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:ch.dbs.actions.reports.HoldingsReport.java
License:Open Source License
private void getXLSLine(final Workbook wb, final Sheet s, final Bestand b, final short rownumber) { final Row row = s.createRow(rownumber); if (b.getId() != null) { row.createCell((short) 0).setCellValue(b.getId().toString()); } else {//from ww w . j a v a2 s. c om row.createCell((short) 0).setCellValue(""); } if (b.getHolding().getId() != null) { row.createCell((short) 1).setCellValue(b.getHolding().getId().toString()); } else { row.createCell((short) 1).setCellValue(""); } if (b.getStandort().getId() != null) { row.createCell((short) 2).setCellValue(b.getStandort().getId().toString()); } else { row.createCell((short) 2).setCellValue(""); } if (b.getStandort().getInhalt() != null) { row.createCell((short) 3).setCellValue(b.getStandort().getInhalt()); } else { row.createCell((short) 3).setCellValue(""); } if (b.getShelfmark() != null) { row.createCell((short) 4).setCellValue(removeSpecialCharacters(b.getShelfmark())); } else { row.createCell((short) 4).setCellValue(""); } if (b.getHolding().getTitel() != null) { row.createCell((short) 5).setCellValue(removeSpecialCharacters(b.getHolding().getTitel())); } else { row.createCell((short) 5).setCellValue(""); } if (b.getHolding().getCoden() != null) { row.createCell((short) 6).setCellValue(removeSpecialCharacters(b.getHolding().getCoden())); } else { row.createCell((short) 6).setCellValue(""); } if (b.getHolding().getVerlag() != null) { row.createCell((short) 7).setCellValue(removeSpecialCharacters(b.getHolding().getVerlag())); } else { row.createCell((short) 7).setCellValue(""); } if (b.getHolding().getOrt() != null) { row.createCell((short) 8).setCellValue(removeSpecialCharacters(b.getHolding().getOrt())); } else { row.createCell((short) 8).setCellValue(""); } if (b.getHolding().getIssn() != null) { row.createCell((short) 9).setCellValue(removeSpecialCharacters(b.getHolding().getIssn())); } else { row.createCell((short) 9).setCellValue(""); } if (b.getHolding().getZdbid() != null) { row.createCell((short) 10).setCellValue(removeSpecialCharacters(b.getHolding().getZdbid())); } else { row.createCell((short) 10).setCellValue(""); } if (b.getStartyear() != null) { row.createCell((short) 11).setCellValue(removeSpecialCharacters(b.getStartyear())); } else { row.createCell((short) 11).setCellValue(""); } if (b.getStartvolume() != null) { row.createCell((short) 12).setCellValue(removeSpecialCharacters(b.getStartvolume())); } else { row.createCell((short) 12).setCellValue(""); } if (b.getStartissue() != null) { row.createCell((short) 13).setCellValue(removeSpecialCharacters(b.getStartissue())); } else { row.createCell((short) 13).setCellValue(""); } if (b.getEndyear() != null) { row.createCell((short) 14).setCellValue(removeSpecialCharacters(b.getEndyear())); } else { row.createCell((short) 14).setCellValue(""); } if (b.getEndvolume() != null) { row.createCell((short) 15).setCellValue(removeSpecialCharacters(b.getEndvolume())); } else { row.createCell((short) 15).setCellValue(""); } if (b.getEndissue() != null) { row.createCell((short) 16).setCellValue(removeSpecialCharacters(b.getEndissue())); } else { row.createCell((short) 16).setCellValue(""); } row.createCell((short) 17).setCellValue(String.valueOf(b.getSuppl())); if (b.getBemerkungen() != null) { row.createCell((short) 18).setCellValue(removeSpecialCharacters(b.getBemerkungen())); } else { row.createCell((short) 18).setCellValue(""); } row.createCell((short) 19).setCellValue(String.valueOf(b.isEissue())); row.createCell((short) 20).setCellValue(String.valueOf(b.isInternal())); }
From source file:ch.dbs.actions.reports.HoldingsReport.java
License:Open Source License
private void initXLS(final Workbook wb, final Sheet s) { final Row rowhead = s.createRow((short) 0); rowhead.createCell((short) 0).setCellValue("Stock ID"); rowhead.createCell((short) 1).setCellValue("Holding ID"); rowhead.createCell((short) 2).setCellValue("Location ID"); rowhead.createCell((short) 3).setCellValue("Location Name"); rowhead.createCell((short) 4).setCellValue("Shelfmark"); rowhead.createCell((short) 5).setCellValue("Title"); rowhead.createCell((short) 6).setCellValue("Coden"); rowhead.createCell((short) 7).setCellValue("Publisher"); rowhead.createCell((short) 8).setCellValue("Place"); rowhead.createCell((short) 9).setCellValue("ISSN"); rowhead.createCell((short) 10).setCellValue("ZDB-ID"); rowhead.createCell((short) 11).setCellValue("Startyear"); rowhead.createCell((short) 12).setCellValue("Startvolume"); rowhead.createCell((short) 13).setCellValue("Startissue"); rowhead.createCell((short) 14).setCellValue("Endyear"); rowhead.createCell((short) 15).setCellValue("Endvolume"); rowhead.createCell((short) 16).setCellValue("Endissue"); rowhead.createCell((short) 17).setCellValue("Suppl"); rowhead.createCell((short) 18).setCellValue("remarks"); rowhead.createCell((short) 19).setCellValue("eissue"); rowhead.createCell((short) 20).setCellValue("internal"); }