List of usage examples for org.apache.poi.ss.usermodel Sheet setColumnWidth
void setColumnWidth(int columnIndex, int width);
The maximum column width for an individual cell is 255 characters.
From source file:archivocsv.models.Reporte.java
public static ArrayList<Reporte> generarReporteExcel(String anio, String mes) throws IOException { ArrayList<Reporte> archivoExcel = new ArrayList<>(); try {//from ww w .j ava2 s . c o m FileChooser elegirArchivo = new FileChooser(); FileChooser.ExtensionFilter filtroExt = new FileChooser.ExtensionFilter("Archivos Excel (*.xls)", "*.xls"); elegirArchivo.getExtensionFilters().add(filtroExt); File archivo = elegirArchivo.showSaveDialog(archivoCsv.getVentanaPrincipal()); archivo.createNewFile(); Workbook libro = new HSSFWorkbook(); FileOutputStream enviar = new FileOutputStream(archivo); Sheet hoja = libro.createSheet("Reportes"); Sheet hoja2 = libro.createSheet("Entradas Tarde"); Row fila = hoja.createRow(0); Row fila2 = hoja2.createRow(0); for (int c = 0; c < 5; c++) { Cell celda = fila.createCell(c); if (c == 0) { celda.setCellValue("ID"); } if (c == 1) { celda.setCellValue("Nombre"); } if (c == 2) { celda.setCellValue("Entrada"); } if (c == 3) { celda.setCellValue("Salida"); } if (c == 4) { celda.setCellValue("Horas Trabajadas"); } } for (int c2 = 0; c2 < 3; c2++) { Cell celda2 = fila2.createCell(c2); if (c2 == 0) { celda2.setCellValue("ID"); } if (c2 == 1) { celda2.setCellValue("Nombre"); } if (c2 == 2) { celda2.setCellValue("Entrada"); } } Connection con = Helper.getConnection(); String entrada = "SELECT * FROM t_marcacion WHERE marcacion LIKE '%/" + mes + "/" + anio + "%'" + " AND marcacion LIKE '%a%'"; ResultSet rs = con.createStatement().executeQuery(entrada); ResultSet rs3 = con.createStatement().executeQuery(entrada); int contar = 0; int contar2 = 1; while (rs.next()) { contar += 1; Row filaDatos = hoja.createRow(contar); Cell celdaID = filaDatos.createCell(0); Cell celdaNombre = filaDatos.createCell(1); Cell celdaEntrada = filaDatos.createCell(2); Cell celdaSalida = filaDatos.createCell(3); Cell celdaHoras = filaDatos.createCell(4); Reporte reporte = new Reporte(); reporte.setEntrada(rs.getString("marcacion")); celdaEntrada.setCellValue(reporte.getEntrada()); reporte.setId(rs.getString("id")); celdaID.setCellValue(reporte.getId()); reporte.setNombre(rs.getString("nombre")); celdaNombre.setCellValue(reporte.getNombre()); //Query que verifica si existe el registro de salida String coincidir = "SELECT * FROM t_marcacion WHERE id=" + rs.getString("id") + " AND marcacion LIKE '%" + rs.getString("marcacion").substring(0, 10) + "%'" + " AND marcacion LIKE '%p%'"; ResultSet rs2 = con.createStatement().executeQuery(coincidir); if (rs2.next()) { reporte.setSalida(rs2.getString("marcacion")); celdaSalida.setCellValue(reporte.getSalida()); String horaInicial = rs.getString("marcacion").substring(11, 15) + " AM"; String horaFinal = rs2.getString("marcacion").substring(11, 15) + " PM"; if (horaFinal.substring(0, 2).equals("12")) { horaFinal = rs2.getString("marcacion").substring(11, 15) + " AM"; } DateFormat sdf = new SimpleDateFormat("KK:mm a"); Date date = sdf.parse(horaInicial); Date date2 = sdf.parse(horaFinal); double hrsInicialMs = date.getTime(); double hrsFinalMs = date2.getTime(); double diferencia = hrsFinalMs - hrsInicialMs; double resta = (diferencia / (1000 * 60 * 60)); int primerNumero = (int) resta; double segundoNumero = resta - primerNumero; int convertirNumero = (int) (segundoNumero * 60); int restarAlumerzo = primerNumero - 1; if (convertirNumero == 0) { reporte.setHorasTrabajadas(restarAlumerzo + ":00"); celdaHoras.setCellValue(reporte.getHorasTrabajadas()); } else { if (convertirNumero > 0 & convertirNumero < 10) { reporte.setHorasTrabajadas(restarAlumerzo + ":0" + convertirNumero); celdaHoras.setCellValue(reporte.getHorasTrabajadas()); } else { reporte.setHorasTrabajadas(restarAlumerzo + ":" + convertirNumero); celdaHoras.setCellValue(reporte.getHorasTrabajadas()); } } } else { reporte.setSalida("Sin registro"); celdaSalida.setCellValue(reporte.getSalida()); reporte.setHorasTrabajadas("Sin registro"); celdaHoras.setCellValue(reporte.getHorasTrabajadas()); } } while (rs3.next()) { Row filaDatos2 = hoja2.createRow(contar2); Cell celdaId = filaDatos2.createCell(0); Cell celdaNombre = filaDatos2.createCell(1); Cell celdaMarcacion = filaDatos2.createCell(2); Reporte reporte = new Reporte(); DateFormat sdf = new SimpleDateFormat("KK:mm a"); String horaInicio = "08:00 AM"; String horaFin = "12:00 PM"; String horaEntrada = rs3.getString("marcacion"); if (horaEntrada.substring(11, 12).equals("0")) { horaEntrada = rs3.getString("marcacion").substring(11, 16) + " AM"; } else { if (horaEntrada.substring(11, 13).equals("10") || horaEntrada.substring(11, 13).equals("11")) { horaEntrada = rs3.getString("marcacion").substring(11, 16) + " AM"; } else { horaEntrada = rs3.getString("marcacion").substring(11, 15) + " AM"; } } Date hrInicio = sdf.parse(horaInicio); Date hrFin = sdf.parse(horaFin); Date hrEntrada = sdf.parse(horaEntrada); Calendar calInicio = new GregorianCalendar(); Calendar calFin = new GregorianCalendar(); Calendar calEntrada = new GregorianCalendar(); calInicio.setTime(hrInicio); calFin.setTime(hrFin); calEntrada.setTime(hrEntrada); if (calEntrada.after(calInicio) & calEntrada.before(calFin)) { contar2 += 1; reporte.setId(rs3.getString("id")); celdaId.setCellValue(reporte.getId()); reporte.setNombre(rs3.getString("nombre")); celdaNombre.setCellValue(reporte.getNombre()); reporte.setEntrada(rs3.getString("marcacion")); celdaMarcacion.setCellValue(reporte.getEntrada()); archivoExcel.add(reporte); } } hoja.setColumnWidth(0, 850); hoja.setColumnWidth(1, 3000); hoja.setColumnWidth(2, 6000); hoja.setColumnWidth(3, 6000); hoja.setColumnWidth(4, 4000); hoja2.setColumnWidth(0, 850); hoja2.setColumnWidth(1, 3000); hoja2.setColumnWidth(2, 6000); libro.write(enviar); enviar.close(); Desktop.getDesktop().open(archivo); } catch (Exception e) { } return archivoExcel; }
From source file:b01.officeLink.excel.ExcelRefiller.java
License:Apache License
public void copyColumnWidths() { Sheet srcSheet = getSourceSheet();/* w ww. j a va2 s . c o m*/ Sheet tarSheet = getTargetSheet(); for (int i = 1; i < 40; i++) { int w = srcSheet.getColumnWidth(i); tarSheet.setColumnWidth((short) (i - 1), w); } }
From source file:br.com.algoritmo.compilacao.CompilaXlsx.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from ww w. j av a 2 s . c o m Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>(); data.put(0, new Object[] { 0, "Luiz Carlos Miyadaira Ribeiro Junior", "Base", "0468265522433921", "SOFTWARE", null, null, null }); data.put(1, new Object[] { 1, "Sergio Antnio Andrade de Freitas", "Destino 1", "0395549254894676", "SOFTWARE", null, null, null }); data.put(2, new Object[] { 2, "Andre Luiz Aquere de Cerqueira e Souza", "Destino 2", "8424412648258970", "CIVIL", null, null, null }); data.put(3, new Object[] { 3, "Edson Mintsu Hung Destino", "Destino 3", "6753551743147880", "ELETRNICA", null, null, null }); data.put(4, new Object[] { 4, "Edgard Costa Oliveira", "Destino 4", "1196380808351110", "SOFTWARE", null, null, null }); data.put(5, new Object[] { 5, "Edson Alves da Costa Jnior", "Destino 5", "2105379147123450", "SOFTWARE", null, null, null }); data.put(6, new Object[] { 6, "Andr Barros de Sales", "Destino 6", "7610669796869660", "SOFTWARE", null, null, null }); data.put(7, new Object[] { 7, "Giovanni Almeida dos Santos", "Destino 7", "0580891429319047", "SOFTWARE", null, null, null }); data.put(8, new Object[] { 8, "Cristiane Soares Ramos", "Destino 8", "9950213660160160", "SOFTWARE", null, null, null }); data.put(9, new Object[] { 9, "Fabricio Ataides Braz", "Destino 9", "1700216932505000", "SOFTWARE", null, null, null }); data.put(10, new Object[] { 10, "Alexandre Srgio de Arajo Bezerra", "Destino 10", "0255998976169051", "MEDICINA", null, null, null }); data.put(11, new Object[] { 11, "Eduardo Stockler Tognetti", "Destino 11", "2443108673822680", "ELTRICA", null, null, null }); data.put(12, new Object[] { 12, "Jan Mendona Correa", "Destino 12", "7844006017790570", "CINCIA DA COMPUTAO", null, null, null }); data.put(13, new Object[] { 13, "Rejane Maria da Costa Figueiredo", "Destino 13", "2187680174312042", "SOFTWARE", null, null, null }); data.put(14, new Object[] { 14, "Augusto Csar de Mendona Brasil", "Destino 14", "0571960641751286", "ENERGIA", null, null, null }); data.put(15, new Object[] { 15, "Fbio Macdo Mendes", "Destino 15", "8075435338067780", "F?SICA", null, null, null }); if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet aba1 = wb.createSheet("Percentual de similaridade 1"); PrintSetup printSetup = aba1.getPrintSetup(); printSetup.setLandscape(true); aba1.setFitToPage(true); aba1.setHorizontallyCenter(true); Sheet aba2 = wb.createSheet("Percentual de similaridade 2"); PrintSetup printSetup2 = aba2.getPrintSetup(); printSetup2.setLandscape(true); aba1.setFitToPage(true); aba1.setHorizontallyCenter(true); //title row Row titleRow = aba1.createRow(0); titleRow.setHeightInPoints(15); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue( "Resultado da aplicao do algoritmo de clculo do percentual de similaridade entre os indivduos"); titleCell.setCellStyle(styles.get("title")); aba1.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); //header row Row headerRow = aba1.createRow(1); headerRow.setHeightInPoints(15); Cell headerCell; for (int i = 1; i <= titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i - 1]); headerCell.setCellStyle(styles.get("header")); } Row headerBase = aba1.createRow(2); headerBase.setHeightInPoints(15); Cell headerCellBase; for (int i = 1; i <= base.length; i++) { headerCellBase = headerBase.createCell(i); headerCellBase.setCellValue(base[i - 1]); headerCellBase.setCellStyle(styles.get("header1")); } Row headerDestino = aba1.createRow(4); headerDestino.setHeightInPoints(15); Cell headerCellDestino; for (int i = 1; i <= destino.length; i++) { headerCellDestino = headerDestino.createCell(i); headerCellDestino.setCellValue(destino[i - 1]); headerCellDestino.setCellStyle(styles.get("header1")); } /*int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if(j == 9){ //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" +rownum+ ":I" + rownum; cell.setCellFormula("SUM("+ref+")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11){ cell.setCellFormula("J" +rownum+ "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } rownum = 3; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles1.length; j++) { Cell cell = row.createCell(j); if(j == 9){ //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" +rownum+ ":I" + rownum; cell.setCellFormula("SUM("+ref+")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11){ cell.setCellFormula("J" +rownum+ "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } */ //set sample data //Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = aba1.createRow(3 + rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } if (row.getRowNum() == 3) { rownum++; } } //finally set column widths, the width is measured in units of 1/256th of a character width aba1.setColumnWidth(0, 2 * 256); //2 characters wide aba1.setColumnWidth(1, 26 * 256); //26 characters wide aba1.setColumnWidth(2, 20 * 256); //20 characters wide aba1.setColumnWidth(3, 18 * 256); //18 characters wide aba1.setColumnWidth(4, 20 * 256); //20 characters wide for (int i = 5; i < 9; i++) { aba1.setColumnWidth(i, 15 * 256); //6 characters wide } // Write the output to a file String file = "Sada/Percentual de similaridade.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:br.com.tecsinapse.dataio.util.WorkbookUtil.java
License:LGPL
public Workbook toWorkBook(Workbook wb, Table table) { List<List<TableCell>> matrix = table.getCells(); List<List<TableCell>> matrixFull = table.toTableCellMatrix(); replaceColorsPallete(table.getColorsReplaceMap(), wb); String sheetName = table.getTitle(); Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName); int titleRows = 0; int r = titleRows; int c = 0;/*from w ww. j av a2s.c o m*/ int maxColumns = -1; Map<Integer, Integer> defaultColumnWidth = new HashMap<>(); ExporterFormatter tableExporterFormatter = table.getExporterFormatter(); for (List<TableCell> row : matrix) { Row sheetRow = sheet.createRow(r); for (TableCell tableCell : row) { while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) { c++; if (c >= matrixFull.get(r - titleRows).size()) { c = 0; r++; } } Cell cell = sheetRow.createCell(c); if (c > maxColumns) { maxColumns = c; } if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) { int rowStart = r; int rowEnd = rowStart + (tableCell.getRowspan() - 1); int colStart = c; int colEnd = colStart + (tableCell.getColspan() - 1); CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd); sheet.addMergedRegion(cellRange); RegionUtil.setBorderTop(BorderStyle.THIN, cellRange, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, cellRange, sheet); RegionUtil.setBorderBottom(BorderStyle.THIN, cellRange, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, cellRange, sheet); } else if (!table.isAutoSizeColumnSheet()) { Integer maxColumnWidth = defaultColumnWidth.get(c); if (maxColumnWidth == null) { defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth()); } else { int defaultWidth = tableCell.getDefaultColumnWidth(); if (defaultWidth > maxColumnWidth) { defaultColumnWidth.put(c, defaultWidth); } } } String format = setConvertedValue(cell, tableCell, tableExporterFormatter); setCellStyle(cell, tableCell, wb, format); c++; } r++; c = 0; } if (table.isAutoSizeColumnSheet()) { for (int i = 0; i <= maxColumns; ++i) { if (sheet instanceof SXSSFSheet) { ((SXSSFSheet) sheet).trackColumnForAutoSizing(i); } sheet.autoSizeColumn(i, true); } } else { for (int i = 0; i <= maxColumns; ++i) { if (defaultColumnWidth.get(i) == null) { if (sheet instanceof SXSSFSheet) { ((SXSSFSheet) sheet).trackColumnForAutoSizing(i); } sheet.autoSizeColumn(i, true); } else { int width = table.getMinOrMaxOrActualCellWidth(defaultColumnWidth.get(i)); sheet.setColumnWidth(i, width); } } } return wb; }
From source file:br.com.tecsinapse.exporter.util.WorkbookUtil.java
License:LGPL
public Workbook toWorkBook(Workbook wb, Table table) { List<List<TableCell>> matrix = table.getCells(); List<List<TableCell>> matrixFull = table.toTableCellMatrix(); String sheetName = table.getTitle(); Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName); int titleRows = 0; int r = titleRows; int c = 0;//from w w w. j a va 2 s . co m int maxColumns = -1; Map<Integer, Integer> defaultColumnWidth = new HashMap<>(); ExporterFormatter tableExporterFormatter = table.getExporterFormatter(); for (List<TableCell> row : matrix) { Row sheetRow = sheet.createRow(r); for (TableCell tableCell : row) { while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) { c++; if (c >= matrixFull.get(r - titleRows).size()) { c = 0; r++; } } Cell cell = sheetRow.createCell(c); if (c > maxColumns) { maxColumns = c; } if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) { int rowStart = r; int rowEnd = rowStart + (tableCell.getRowspan() - 1); int colStart = c; int colEnd = colStart + (tableCell.getColspan() - 1); CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd); sheet.addMergedRegion(cellRange); RegionUtil.setBorderTop(1, cellRange, sheet, wb); RegionUtil.setBorderRight(1, cellRange, sheet, wb); RegionUtil.setBorderBottom(1, cellRange, sheet, wb); RegionUtil.setBorderLeft(1, cellRange, sheet, wb); } else if (!table.isAutoSizeColumnSheet()) { Integer maxColumnWidth = defaultColumnWidth.get(c); if (maxColumnWidth == null) { defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth()); } else { int defaultWidth = tableCell.getDefaultColumnWidth(); if (defaultWidth > maxColumnWidth) { defaultColumnWidth.put(c, defaultWidth); } } } String format = setConvertedValue(cell, tableCell, tableExporterFormatter); setCellStyle(cell, tableCell, wb, format); c++; } r++; c = 0; } if (table.isAutoSizeColumnSheet()) { for (int i = 0; i <= maxColumns; ++i) { if (sheet instanceof SXSSFSheet) { ((SXSSFSheet) sheet).trackColumnForAutoSizing(i); } else { sheet.autoSizeColumn(i, true); } } } else { for (int i = 0; i <= maxColumns; ++i) { if (defaultColumnWidth.get(i) == null) { if (sheet instanceof SXSSFSheet) { ((SXSSFSheet) sheet).trackColumnForAutoSizing(i); } else { sheet.autoSizeColumn(i, true); } } else { sheet.setColumnWidth(i, defaultColumnWidth.get(i)); } } } return wb; }
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();/* ww w . ja v a2 s . c om*/ 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:cn.afterturn.easypoi.excel.export.base.BaseExportService.java
License:Apache License
public void setCellWith(List<ExcelExportEntity> excelParams, Sheet sheet) { int index = 0; for (int i = 0; i < excelParams.size(); i++) { if (excelParams.get(i).getList() != null) { List<ExcelExportEntity> list = excelParams.get(i).getList(); for (int j = 0; j < list.size(); j++) { sheet.setColumnWidth(index, (int) (256 * list.get(j).getWidth())); index++;/*from ww w . j a v a 2s .co m*/ } } else { sheet.setColumnWidth(index, (int) (256 * excelParams.get(i).getWidth())); index++; } } }
From source file:cn.afterturn.easypoi.excel.html.css.impl.WidthCssConverImpl.java
License:Apache License
@Override public void convertToExcel(Cell cell, CellStyle cellStyle, CellStyleEntity style) { if (StringUtils.isNoneBlank(style.getWidth())) { int width = (int) Math.round(PoiCssUtils.getInt(style.getWidth()) * 2048 / 8.43F); Sheet sheet = cell.getSheet(); int colIndex = cell.getColumnIndex(); if (width > sheet.getColumnWidth(colIndex)) { if (width > 255 * 256) { width = 255 * 256;//from w w w . j a v a2 s .c o m } sheet.setColumnWidth(colIndex, width); } } }
From source file:cn.afterturn.easypoi.util.PoiSheetUtil.java
License:Apache License
/** * Given a sheet, this method deletes a column from a sheet and moves * all the columns to the right of it to the left one cell. * // w ww .jav a2 s . co m * Note, this method will not update any formula references. * * @param sheet * @param columnToDelete */ public static void deleteColumn(Sheet sheet, int columnToDelete) { int maxColumn = 0; for (int r = 0; r < sheet.getLastRowNum() + 1; r++) { Row row = sheet.getRow(r); // if no row exists here; then nothing to do; next! if (row == null) { continue; } // if the row doesn't have this many columns then we are good; next! int lastColumn = row.getLastCellNum(); if (lastColumn > maxColumn) { maxColumn = lastColumn; } if (lastColumn < columnToDelete) { continue; } for (int x = columnToDelete + 1; x < lastColumn + 1; x++) { Cell oldCell = row.getCell(x - 1); if (oldCell != null) { row.removeCell(oldCell); } Cell nextCell = row.getCell(x); if (nextCell != null) { Cell newCell = row.createCell(x - 1, nextCell.getCellType()); cloneCell(newCell, nextCell); } } } // Adjust the column widths for (int c = columnToDelete; c < maxColumn; c++) { sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1)); } }
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
@SuppressWarnings("rawtypes") public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException { Class[] types = getTypes(table); Workbook wb = new XSSFWorkbook(); Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;/*w w w.ja v a2 s. co m*/ DataFormat df = wb.createDataFormat(); Font font = wb.createFont(); font.setFontName("Serif"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 15); style = wb.createCellStyle(); style.setFont(font); styles.put("title", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 10); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("th", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-border", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-double", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-right", style); font = wb.createFont(); font.setFontName("Serif"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-bold", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setDataFormat(df.getFormat("d.mm.yyyy h:MM")); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-date", style); Sheet sheet = wb.createSheet(); sheet.setFitToPage(true); Cell cell; int maxRows = 0; for (int r = 0; r < table.length; r++) { Row row = sheet.createRow(r); if (r == 0) { row.setRowStyle(styles.get("th")); } int rows = 1; for (int c = 0; c < table[r].length; c++) { cell = row.createCell(c); String s = table[r][c]; if (s == null) continue; rows = Math.max(rows, s.split("\n").length); try { if (exportMode == ExportMode.HEADERS_TOP && r == 0) { cell.setCellStyle(styles.get("th")); cell.setCellValue(s); } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) { if (r == 0 && c == 0) { cell.setCellStyle(styles.get("td")); } else { cell.setCellStyle(styles.get("th")); } cell.setCellValue(s); } else if (types[c] == Double.class) { cell.setCellStyle(styles.get("td-double")); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(s)); } else if (types[c] == String.class) { cell.setCellStyle( styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td")); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(s); } else { cell.setCellStyle(styles.get("td-right")); cell.setCellValue(s); } } catch (Exception e) { cell.setCellStyle(styles.get("td")); cell.setCellValue(s); } } maxRows = Math.max(maxRows, rows); row.setHeightInPoints(rows * 16f); } // Add footer notes if (footerData.size() > 0) { Row row = sheet.createRow(table.length); row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints())); cell = row.createCell(0); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based) row.getRowNum(), //last row (0-based) 0, //first column (0-based) table[0].length - 1 //last column (0-based) )); //for ( String data : footerData ) { style = wb.createCellStyle(); style.setWrapText(true); cell.setCellStyle(style); cell.setCellValue(MiscUtils.flatten(footerData, "\n")); //} } footerData.clear(); autoSizeColumns(sheet); if (table.length > 0) { for (int c = 0; c < table[0].length; c++) { if (sheet.getColumnWidth(c) > 10000) sheet.setColumnWidth(c, 3000); } } if (exportMode == ExportMode.HEADERS_TOPLEFT) { for (int r = 1; r < table.length; r++) { sheet.getRow(r).setHeightInPoints(maxRows * 16f); } } File reportFile = IOUtils.createTempFile("export_", ".xlsx"); FileOutputStream out = new FileOutputStream(reportFile); wb.write(out); wb.close(); out.close(); Desktop.getDesktop().open(reportFile); }