List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellValue
@Override public void setCellValue(boolean value)
From source file:br.com.jinsync.controller.ExportExcelFile.java
License:Open Source License
@Override protected Object doInBackground() throws Exception { int valuePgsBar = 0; int qtdTotLin = 0; final String dir = System.getProperty("user.dir") + "\\file"; File arqProp = new File(dir); if (!arqProp.exists()) { arqProp.mkdirs();//from ww w. j ava2s . com } String nameFile = name; int lin = 0; int col = 0; int pos = 0; int posEnd = 0; pos = nameFile.lastIndexOf("("); if (pos > 0) { posEnd = nameFile.lastIndexOf(")"); nameFile = dir + "\\" + nameFile.substring(pos + 1, posEnd) + "_file" + ".xlsx"; } else { pos = nameFile.lastIndexOf("\\"); if (pos > 0) { posEnd = nameFile.lastIndexOf("."); if (posEnd > 0) { nameFile = dir + "\\" + nameFile.substring(pos + 1, posEnd) + "_file" + ".xlsx"; } else { nameFile = dir + nameFile.substring(pos) + "_file" + ".xlsx"; } } } FileOutputStream out; try { out = new FileOutputStream(nameFile); TableModel model = tableName.getModel(); XSSFWorkbook wb = new XSSFWorkbook(); // Criando area de trabalho // para o excel XSSFSheet s = wb.createSheet(); // criando uma nova sheet XSSFFont f = wb.createFont(); XSSFFont f2 = wb.createFont(); XSSFCellStyle cs = wb.createCellStyle(); XSSFCellStyle cs2 = wb.createCellStyle(); XSSFCellStyle cs3 = wb.createCellStyle(); XSSFCellStyle cs4 = wb.createCellStyle(); f.setFontHeightInPoints((short) 8); f2.setFontHeightInPoints((short) 8); f.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setBoldweight(Font.BOLDWEIGHT_NORMAL); f.setFontName("Courier New"); f2.setFontName("Courier New"); XSSFRow r = null; // Criando uma referencia para Linha XSSFCell c = null; // Referencia para Celula cs.setFont(f); cs2.setFont(f2); cs3.setFont(f2); cs4.setFont(f2); cs2.setAlignment(CellStyle.ALIGN_LEFT); cs3.setAlignment(CellStyle.ALIGN_RIGHT); cs4.setAlignment(CellStyle.ALIGN_RIGHT); r = s.createRow(lin); for (int i = 1; i < model.getColumnCount(); i++) { c = r.createCell(col); c.setCellStyle(cs); c.setCellValue(model.getColumnName(i)); col = col + 1; } col = 0; qtdTotLin = model.getRowCount() - 1; if (qtdTotLin == 0) { qtdTotLin = 1; } for (int i = 0; i < model.getRowCount(); i++) { valuePgsBar = (i * 100) / qtdTotLin; lin = lin + 1; r = s.createRow(lin); col = 0; for (int j = 1; j < model.getColumnCount(); j++) { String valor = model.getValueAt(i, j).toString(); if (valor == null) { valor = ""; } c = r.createCell(col); c.setCellStyle(cs2); valor = valor.trim(); if (valor.matches("-?\\d+(\\.\\d+)?")) { c.setCellValue(Double.parseDouble(valor)); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellStyle(cs4); } else { c.setCellValue(valor); } col = col + 1; setProgress(valuePgsBar); } } for (int i = 0; i <= model.getColumnCount(); i++) { s.autoSizeColumn(i); } wb.write(out); wb.close(); out.close(); Desktop desktop = Desktop.getDesktop(); desktop.open(new File(nameFile)); } catch (FileNotFoundException e) { // TODO Auto-generated catch block JOptionPane.showMessageDialog(null, e.getLocalizedMessage()); e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block JOptionPane.showMessageDialog(null, e.getLocalizedMessage()); e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } return null; }
From source file:br.com.jinsync.controller.ExportExcelString.java
License:Open Source License
@Override protected Object doInBackground() throws Exception { final String dir = System.getProperty("user.dir") + "\\string"; File arqProp = new File(dir); if (!arqProp.exists()) { arqProp.mkdirs();/* ww w .j a v a 2 s .co m*/ } String nameFile = this.nameFile; int lin = 0; int col = 0; int pos = 0; int posFim = 0; int valDec = 0; pos = nameFile.lastIndexOf("("); if (pos > 0) { posFim = nameFile.lastIndexOf(")"); nameFile = dir + "\\" + nameFile.substring(pos + 1, posFim) + "_string" + ".xlsx"; } else { pos = nameFile.lastIndexOf("\\"); if (pos > 0) { posFim = nameFile.lastIndexOf("."); if (posFim > 0) { nameFile = dir + "\\" + nameFile.substring(pos + 1, posFim) + "_string" + ".xlsx"; } else { nameFile = dir + nameFile.substring(pos) + "_string" + ".xlsx"; } } } FileOutputStream out; try { out = new FileOutputStream(nameFile); TableModel model = tableName.getModel(); XSSFWorkbook wb = new XSSFWorkbook(); // Criando area de trabalho // para o excel XSSFSheet s = wb.createSheet(); // criando uma nova sheet XSSFFont f = wb.createFont(); XSSFFont f2 = wb.createFont(); XSSFCellStyle cs = wb.createCellStyle(); XSSFCellStyle cs2 = wb.createCellStyle(); XSSFCellStyle cs3 = wb.createCellStyle(); XSSFCellStyle cs4 = wb.createCellStyle(); f.setFontHeightInPoints((short) 8); f2.setFontHeightInPoints((short) 8); f.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setBoldweight(Font.BOLDWEIGHT_NORMAL); f.setFontName("Courier New"); f2.setFontName("Courier New"); XSSFRow r = null; // Criando uma referencia para Linha XSSFCell c = null; // Referencia para Celula cs.setFont(f); cs2.setFont(f2); cs3.setFont(f2); cs4.setFont(f); cs2.setAlignment(CellStyle.ALIGN_LEFT); cs3.setAlignment(CellStyle.ALIGN_RIGHT); cs4.setAlignment(CellStyle.ALIGN_RIGHT); r = s.createRow(lin); r = s.createRow(0); // Criando a primeira linha na LINHA zero, que // seria o nmero 1 c = r.createCell(0); // Criando a celula na posicao ZERO, que seria // A, com referencia na linha zero acima = // A1 c.setCellStyle(cs); c.setCellValue(Language.stringFieldName); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(Language.stringType); c = r.createCell(2); c.setCellStyle(cs); c.setCellValue(Language.stringLength); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(Language.stringDecimal); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(Language.stringTotal); c = r.createCell(5); c.setCellStyle(cs4); c.setCellValue(Language.stringContent); col = 0; lin = lin + 1; for (int i = 0; i < model.getColumnCount(); i++) { r = s.createRow(lin); c = r.createCell(col); c.setCellStyle(cs2); c.setCellValue(model.getColumnName(i)); col = col + 1; c = r.createCell(col); c.setCellStyle(cs2); c.setCellValue(tipoConteudo.get(i)); col = col + 1; c = r.createCell(col); c.setCellStyle(cs2); if (tamanhoConteudo.get(i).matches("-?\\d+(\\.\\d+)?")) { c.setCellValue(Double.parseDouble(tamanhoConteudo.get(i))); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellStyle(cs3); } else { c.setCellValue(tamanhoConteudo.get(i)); } col = col + 1; c = r.createCell(col); c.setCellStyle(cs2); if (decimalConteudo.get(i).matches("-?\\d+(\\.\\d+)?")) { c.setCellValue(Integer.parseInt(decimalConteudo.get(i))); valDec = Integer.parseInt(decimalConteudo.get(i)); c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellStyle(cs3); } else { c.setCellValue(decimalConteudo.get(i)); valDec = 0; } col = col + 1; c = r.createCell(col); c.setCellStyle(cs2); c.setCellValue(totalConteudo.get(i)); col = col + 1; c = r.createCell(col); c.setCellStyle(cs2); String valor = model.getValueAt(0, i).toString().trim(); if (valor == null) { valor = ""; } if (valor.matches("-?\\d+(\\.\\d+)?")) { if (Double.parseDouble(valor) > 0 && valDec > 0) { double val2 = Double.parseDouble(valor) / (Math.pow(10, valDec)); NumberFormat format = NumberFormat.getInstance(); format.setMinimumFractionDigits(2); format.setMaximumFractionDigits(valDec); c.setCellValue(format.format(val2).toString()); } else { c.setCellValue(Double.parseDouble(valor)); } // c.setCellType(Cell.CELL_TYPE_NUMERIC); c.setCellStyle(cs3); } else { c.setCellValue(valor); } lin = lin + 1; col = 0; } for (int i = 0; i <= model.getColumnCount(); i++) { s.autoSizeColumn(i); } wb.write(out); wb.close(); out.close(); Desktop desktop = Desktop.getDesktop(); desktop.open(new File(nameFile)); } catch (FileNotFoundException e) { // TODO Auto-generated catch block JOptionPane.showMessageDialog(null, e.getLocalizedMessage()); //e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block JOptionPane.showMessageDialog(null, e.getLocalizedMessage()); //e.printStackTrace(); } return null; }
From source file:br.uff.ic.kraken.extractdata.excel.ConflictingChunkData.java
public static void main(String[] args) { String bdName = "automaticAnalysisUpdated"; String outputPath = "/Users/gleiph/Dropbox/doutorado/publication/TSE Manual + Automatic/TSE 2/report1.0.xlsx"; //Excel stuff XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Data"); int rowNumber = 0; XSSFRow row = sheet.createRow(rowNumber); XSSFCell cell = row.createCell(PROJECT_ID); cell.setCellValue("Project ID"); row = sheet.createRow(rowNumber++);//from w w w.j a v a2 s .c o m cell = row.createCell(PROJECT_NAME); cell.setCellValue("Project name"); cell = row.createCell(REVISION_SHA); cell.setCellValue("Revision SHA"); cell = row.createCell(FILE_NAME); cell.setCellValue("File name"); cell = row.createCell(CONFLICTING_CHUNK_IDENTIFIER); cell.setCellValue("Conflicting chunk identifier"); cell = row.createCell(OUTMOST_KIND_CONFLICT); cell.setCellValue("Kind of conflict"); cell = row.createCell(AMOUNT_LANGUAGE_CONSTRUCTS); cell.setCellValue("#Language constructs"); cell = row.createCell(DEVELOPER_DECISION); cell.setCellValue("Developer decision"); cell = row.createCell(LOC_VERSION_1); cell.setCellValue("#LOC Version 1"); cell = row.createCell(LOC_VERSION_2); cell.setCellValue("#LOC Version 2"); cell = row.createCell(DEVELOPERS); cell.setCellValue("Developers"); try (Connection connection = (new JDBCConnection()).getConnection(bdName)) { ProjectJDBCDAO projectDAO = new ProjectJDBCDAO(connection); RevisionJDBCDAO revisionDAO = new RevisionJDBCDAO(connection); ConflictingFileJDBCDAO conflictingFileDAO = new ConflictingFileJDBCDAO(connection); ConflictingChunkJDBCDAO conflictingChunkDAO = new ConflictingChunkJDBCDAO(connection); List<Project> projects = projectDAO.selectAnalyzedMainProjects(); for (Project project : projects) { System.out.println(project.getName()); List<Revision> revisions = revisionDAO.selectByProjectId(project.getId()); for (Revision revision : revisions) { List<ConflictingFile> conflictingFiles = conflictingFileDAO .selectByRevisionId(revision.getId()); for (ConflictingFile conflictingFile : conflictingFiles) { if (!conflictingFile.getName().toLowerCase().endsWith(".java")) { continue; } List<ConflictingChunk> conflictingChunks = conflictingChunkDAO .selectByConflictingFileId(conflictingFile.getId()); for (ConflictingChunk conflictingChunk : conflictingChunks) { row = sheet.createRow(rowNumber++); cell = row.createCell(PROJECT_ID); cell.setCellValue(project.getId()); // System.out.print(project.getId() + ", "); cell = row.createCell(PROJECT_NAME); cell.setCellValue(project.getName()); // System.out.print(project.getName() + ", "); cell = row.createCell(REVISION_SHA); cell.setCellValue(revision.getSha()); // System.out.print(revision.getSha() + ", "); cell = row.createCell(FILE_NAME); cell.setCellValue(conflictingFile.getName()); // System.out.print(conflictingFile.getName() + ", "); cell = row.createCell(CONFLICTING_CHUNK_IDENTIFIER); cell.setCellValue(conflictingChunk.getId()); // System.out.print(conflictingChunk.getIdentifier() + ", "); String generalKindConflictOutmost = conflictingChunk.getGeneralKindConflictOutmost(); cell = row.createCell(OUTMOST_KIND_CONFLICT); String newKindConflict = replaceAttributeByVariable(generalKindConflictOutmost); cell.setCellValue(newKindConflict); // System.out.print(generalKindConflictOutmost + ", "); String[] languageConstructs = generalKindConflictOutmost.split(", "); cell = row.createCell(AMOUNT_LANGUAGE_CONSTRUCTS); cell.setCellValue(languageConstructs.length); // System.out.print(languageConstructs.length + ", "); String developerDecision = conflictingChunk.getDeveloperDecision().toString(); cell = row.createCell(DEVELOPER_DECISION); cell.setCellValue(developerDecision); // System.out.print(developerDecision + ", "); int locVersion1 = conflictingChunk.getSeparatorLine() - conflictingChunk.getBeginLine() - 1; int locVersion2 = conflictingChunk.getEndLine() - conflictingChunk.getSeparatorLine() - 1; cell = row.createCell(LOC_VERSION_1); cell.setCellValue(locVersion1); cell = row.createCell(LOC_VERSION_2); cell.setCellValue(locVersion2); // System.out.println(locVersion1 + ", " + locVersion2); cell = row.createCell(DEVELOPERS); cell.setCellValue(project.getDevelopers()); if (rowNumber % 10 == 0) { FileOutputStream out; out = new FileOutputStream(outputPath); wb.write(out); out.close(); } } } } } } catch (SQLException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } try { FileOutputStream out; out = new FileOutputStream(outputPath); wb.write(out); out.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } }
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();// www . j ava2s .c o 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:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java
License:Apache License
private int addPhase(XSSFSheet sheet, int currentRow, Phase phase, LocalizationEngine localizationEngine, XSSFCellStyle ergebnisStyle, XSSFCellStyle modulStyle, XSSFCellStyle defaultStyle, SzenarioItem szenarioTree) {//from w w w . jav a2 s .co m XSSFRow row = sheet.createRow(currentRow++); XSSFCell phaseCell = row.createCell(MAIN_COL); phaseCell.setCellStyle(defaultStyle); String phaseName = localizationEngine.localize(phase.getPresentationName()); phaseCell.setCellValue(isNotBlank(phaseName) ? phaseName.toUpperCase() : phaseName); List<Aufgabe> aufgabenInPhase = phase.getAufgaben(); List<Modul> module = phase.getModule(); for (Modul modul : module) { currentRow = addModul(sheet, currentRow, localizationEngine, ergebnisStyle, modulStyle, defaultStyle, phase, modul, aufgabenInPhase, szenarioTree); } return currentRow; }
From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java
License:Apache License
private int addModul(XSSFSheet sheet, int currentRow, LocalizationEngine localizationEngine, XSSFCellStyle ergebnisStyle, XSSFCellStyle modulStyle, XSSFCellStyle defaultStyle, Phase phase, Modul modul, List<Aufgabe> aufgabenInPhase, SzenarioItem szenarioTree) { XSSFRow modulRow = sheet.createRow(currentRow++); String modulName = localizationEngine.localize(modul.getPresentationName()); XSSFCell modulCell = modulRow.createCell(MAIN_COL); modulCell.setCellStyle(modulStyle);//from ww w. ja v a 2 s .c o m modulCell.setCellValue( modulIndent.getStringValue() + (isNotBlank(modulName) ? modulName.toUpperCase() : modulName)); for (Aufgabe aufgabe : modul.getAufgaben()) { if (aufgabenInPhase.contains(aufgabe)) { currentRow = addAufgabe(sheet, currentRow, phase, modul, aufgabe, localizationEngine, ergebnisStyle, defaultStyle, szenarioTree); } } return currentRow; }
From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java
License:Apache License
private int addAufgabe(XSSFSheet sheet, int currentRow, Phase phase, Modul modul, Aufgabe aufgabe, LocalizationEngine localizationEngine, XSSFCellStyle ergebnisStyle, XSSFCellStyle defaultStyle, SzenarioItem szenarioTree) {/*from ww w .ja v a 2 s. c o m*/ XSSFRow row = sheet.createRow(currentRow++); XSSFCell aufgabeCell = row.createCell(MAIN_COL); aufgabeCell.setCellStyle(defaultStyle); aufgabeCell.setCellValue( aufgabeIndent.getStringValue() + localizationEngine.localize(aufgabe.getPresentationName())); Rolle verantwortlicheRolle = aufgabe.getVerantwortlicheRolle(); if (verantwortlicheRolle != null) { XSSFCell rollCell = row.createCell(VERANTWORTLICH_COL); rollCell.setCellStyle(defaultStyle); rollCell.setCellValue(localizationEngine.localize(verantwortlicheRolle.getPresentationName())); } List<Ergebnis> ergebnisse = aufgabe.getErgebnisse(); for (Ergebnis ergebnis : ergebnisse) { if (szenarioTree == null || modul.isCustom() || szenarioItemUtil.isErgebnisSelected(szenarioTree, phase, modul, aufgabe, ergebnis)) { XSSFRow ergebnisRow = sheet.createRow(currentRow++); XSSFCell ergebnisCell = ergebnisRow.createCell(MAIN_COL); ergebnisCell.setCellStyle(ergebnisStyle); ergebnisCell.setCellValue(ergebnisIndent.getStringValue() + localizationEngine.localize(ergebnis.getPresentationName())); XSSFCell verantwortlichCell = ergebnisRow.createCell(VERANTWORTLICH_COL); verantwortlichCell.setCellStyle(defaultStyle); verantwortlichCell.setCellValue(getVerantwortlichFuerErgebnis(localizationEngine, ergebnis)); } } return currentRow; }
From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java
License:Apache License
private void addHeaderCell(XSSFRow headerRow, XSSFCellStyle cellStyle, int i, String string) { XSSFCell cell = headerRow.createCell(i); cell.setCellStyle(cellStyle);/*w ww. j a va2 s . c o m*/ cell.setCellValue(string); }
From source file:cn.comgroup.tzmedia.server.report.CustomerOrderReport.java
public CustomerOrderReportResult runOrderReport(String deployPath, List<CustomerOrder> orders) throws ParseException, FileNotFoundException, IOException { String reportTemplate = deployPath + File.separator + "template" + File.separator + "CustomerOrderReport.xlsx"; final XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(reportTemplate)); DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); XSSFSheet sheet = workbook.getSheetAt(0); int startRow = 1; for (CustomerOrder co : orders) { XSSFRow row = sheet.getRow(startRow); if (row == null) { row = sheet.createRow(startRow); }//w w w. j a v a2 s.co m XSSFCell cellOrderNumber = row.getCell(0); if (cellOrderNumber == null) { cellOrderNumber = row.createCell(0); } XSSFCell cellOrderDate = row.getCell(1); if (cellOrderDate == null) { cellOrderDate = row.createCell(1); } XSSFCell cellOrderType = row.getCell(2); if (cellOrderType == null) { cellOrderType = row.createCell(2); } XSSFCell cellOrderStatus = row.getCell(3); if (cellOrderStatus == null) { cellOrderStatus = row.createCell(3); } XSSFCell cellETN = row.getCell(4); if (cellETN == null) { cellETN = row.createCell(4); } XSSFCell cellShop = row.getCell(5); if (cellShop == null) { cellShop = row.createCell(5); } XSSFCell cellUserId = row.getCell(6); if (cellUserId == null) { cellUserId = row.createCell(6); } XSSFCell cellUserName = row.getCell(7); if (cellUserName == null) { cellUserName = row.createCell(7); } XSSFCell cellPaymentTerm = row.getCell(8); if (cellPaymentTerm == null) { cellPaymentTerm = row.createCell(8); } XSSFCell cellOrderAmount = row.getCell(9); if (cellOrderAmount == null) { cellOrderAmount = row.createCell(9); } XSSFCell cellCouponAmount = row.getCell(10); if (cellCouponAmount == null) { cellCouponAmount = row.createCell(10); } cellOrderNumber.setCellValue(co.getOrderNumber()); if (co.getOrderDate() != null) { cellOrderDate.setCellValue(dateFormat.format(co.getOrderDate().getTime())); } else { cellOrderDate.setCellValue(dateFormat.format(co.getOrderTime().getTime())); } cellOrderType.setCellValue(co.getOrderType().toString()); cellOrderStatus.setCellValue(co.getOrderStatus().toString()); if (co.getExternalTransactionNumber() != null) { cellETN.setCellValue(co.getExternalTransactionNumber()); } cellShop.setCellValue(co.getShopName()); cellUserId.setCellValue(co.getUserId()); cellUserName.setCellValue(co.getUserName()); cellPaymentTerm.setCellValue(co.getPaymentTerm().toString()); cellOrderAmount.setCellValue(co.getOrderAmount()); cellCouponAmount.setCellValue(co.getCouponAmount()); startRow++; System.out.println(startRow + " orders in the report"); } DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd-HHMMSS"); String reportName = "CustomerOrderReport" + dateTimeFormat.format(new Date()) + ".xlsx"; String reportPath = deployPath + File.separator + reportName; FileOutputStream fos = new FileOutputStream(reportPath); try (BufferedOutputStream bout = new BufferedOutputStream(fos)) { workbook.write(bout); bout.flush(); } return new CustomerOrderReportResult(reportName); }
From source file:com.adobe.acs.commons.data.SpreadsheetTest.java
License:Apache License
@BeforeClass public static void setUp() throws IOException { testWorkbook = new XSSFWorkbook(); XSSFSheet sheet = testWorkbook.createSheet("sheet 1"); createRow(sheet, header);//from ww w . j a va 2s . c o m createRow(sheet, "/test/a1", "A-1"); createRow(sheet, "/test/a2", "A-2", "val"); createRow(sheet, "/test/a1/a1a", "A-1-A", "val"); createRow(sheet, "/test/a3/a3a", "A-3-A", "val"); XSSFRow valuesRow = createRow(sheet, "/some/types", "Types", "...", "12345", "one,two,three", "four;five;six", "12.345", "One Value", null, "Another Value"); XSSFCell dateCell = valuesRow.createCell(10); dateCell.setCellValue(testDate); CellStyle dateStyle = testWorkbook.createCellStyle(); dateStyle.setDataFormat(testWorkbook.createDataFormat().getFormat("YYYY-mm-dd")); dateCell.setCellStyle(dateStyle); testWorkbook.write(workbookData); workbookData.close(); InputStream dataTypesFile = SpreadsheetTest.class .getResourceAsStream("/com/adobe/acs/commons/data/spreadsheet-data-types.xlsx"); dataTypesSheet = new Spreadsheet(false, dataTypesFile); }