List of usage examples for org.apache.poi.ss.usermodel CellStyle setBorderLeft
void setBorderLeft(BorderStyle border);
From source file:servlet.exportScoreSheet.java
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle();/*from www .j a va 2 s .com*/ style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); 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()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:standarapp.algorithm.ReadFileVector.java
public String lectureRegistry(String nameFile, String nameOut, int[] col, double percent, int rowBegin) { String answer = ""; int quantityFound = 0; workbook = Lecture.lectureXLSX(nameFile); sheet = workbook.getSheetAt(0);//www . java2 s .co m for (Row row : sheet) { if (row.getRowNum() < rowBegin) { continue; } String[] cellsWI = new String[col.length + 1]; for (int i = 0; i < col.length; i++) { cellsWI[i] = ""; try { Cell cell = row.getCell(col[i]); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellsWI[i] = deleteTrash(cell.getStringCellValue()); } else { cellsWI[i] = cell.getDateCellValue().toString(); cellsWI[col.length] = String.valueOf(cell.getDateCellValue().getMonth()); } if (i == 2) { cellsWI[i] = cell.getStringCellValue(); } } catch (Exception e) { } } registry.add(cellsWI); } System.out.println("Total: " + registry.size()); /*for (int i = 0; i < registry.size(); i++) { System.out.println(i +" Municipio: " + registry.get(i)[0] + " | Localidad: " + registry.get(i)[1] + " | Especie: " + registry.get(i)[2] + " | Fecha: " + registry.get(i)[3]); }*/ int rowCount = 0; int columnCount = 0; CellStyle cs = workbook.createCellStyle(); //Font font = workbook.createFont(); cs.setAlignment(HorizontalAlignment.CENTER); cs.setVerticalAlignment(VerticalAlignment.CENTER); cs.setBorderRight(BorderStyle.THIN); cs.setBorderLeft(BorderStyle.THIN); cs.setBorderBottom(BorderStyle.THIN); cs.setBorderTop(BorderStyle.THIN); sheet = workbook.createSheet(); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 20); Row row = sheet.createRow(0); Cell encabezado = row.createCell(rowCount); encabezado.setCellValue("Base de datos coordenadas"); encabezado.setCellStyle(cs); CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8); sheet.addMergedRegion(region); row = sheet.createRow(++rowCount); Cell cell = row.createCell(columnCount); cell.setCellValue("Especie"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Municipio"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Codigo Municipio"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Vereda"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Codigo Vereda"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Mes"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Ao"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Latitud"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Longitud"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Fuente"); cell.setCellStyle(cs); for (int i = 0; i < registry.size(); i++) { try { String[] registro = registry.get(i); columnCount = -1; int cod_Mncp = 0; row = sheet.createRow(++rowCount); double levenstein = 0; double localidad_oficial = 0; double levensteinActual = 0; for (Integer codMunicipio : codigo_Municipio.keySet()) { if (registro[0].equals(codigo_Municipio.get(codMunicipio))) { cod_Mncp = codMunicipio; break; } try { double levenstein_local = FuzzySearch.ratio(registro[0], codigo_Municipio.get(codMunicipio)); if (levenstein_local >= levensteinActual) { cod_Mncp = codMunicipio; levensteinActual = levenstein_local; } if (levensteinActual == 100) { break; } } catch (Exception e) { } } for (Double cod_Loc : mncp_localidad.get(cod_Mncp).keySet()) { String loc = mncp_localidad.get(cod_Mncp).get(cod_Loc); if (registro[1].equals(loc)) { localidad_oficial = cod_Loc; levenstein = 101; } try { double levenstein_local = FuzzySearch.ratio(registro[1], loc); if (levenstein_local >= levenstein) { localidad_oficial = cod_Loc; levenstein = levenstein_local; } if (levenstein == 100) { break; } } catch (Exception e) { } } String mncp_oficial = codigo_Municipio.get(cod_Mncp); String loc_oficial = codigo_localidad.get(localidad_oficial); String especie = registro[2]; double locX = localidad_x.get(localidad_oficial); double locY = localidad_y.get(localidad_oficial); int year = 0; int month = 0; System.out.println(); try { year = Integer.parseInt(registro[3].split(" ")[5]); month = Integer.parseInt(registro[registro.length - 1]) + 1; } catch (Exception e) { year = Integer.parseInt(registro[3].substring(registro[3].length() - 4)); month = Integer .parseInt(registro[3].substring(registro[3].length() - 7, registro[3].length() - 5)); } quantityFound++; cell = row.createCell(++columnCount); cell.setCellValue(especie); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(mncp_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(cod_Mncp); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(loc_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(localidad_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(month); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(year); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(locY); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(locX); cell.setCellStyle(cs); } catch (Exception e) { continue; } } sheet.setColumnWidth(0, 5800); sheet.setColumnWidth(1, 5800); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 5800); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 6400); sheet.setColumnWidth(8, 6400); answer = "Se generaron " + quantityFound + " vector(es)"; try (FileOutputStream outputStream = new FileOutputStream(nameOut)) { workbook.write(outputStream); } catch (IOException ex) { quantityFound = 0; answer = "Cerrar el archivo de entrada "; } return answer; }
From source file:test.poi.MyExcelDemo.java
License:Apache License
/** * Create a library of cell styles/* w ww. j a v a 2 s .c o m*/ */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.BLACK.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); // style.setWrapText(true); 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()); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); 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()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:util.ExcelConverter.java
public static File createXlsx(String[] header, String[][] data, String path) { try {//w w w. j a va2 s . c o m XSSFWorkbook xwb = new XSSFWorkbook(); XSSFSheet sheet = xwb.createSheet(); CellStyle cellStyle = xwb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_LEFT); cellStyle.setAlignment(CellStyle.VERTICAL_TOP); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyle.setWrapText(false); Font bold = xwb.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); bold.setFontHeightInPoints((short) 10); CellStyle cellStyleHeader = xwb.createCellStyle(); cellStyleHeader.setAlignment(CellStyle.ALIGN_LEFT); cellStyleHeader.setAlignment(CellStyle.VERTICAL_TOP); cellStyleHeader.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderLeft(XSSFCellStyle.BORDER_THIN); cellStyleHeader.setFont(bold); cellStyleHeader.setWrapText(false); XSSFRow row; Cell cell; //header row = sheet.createRow(0); for (int i = 0; i < header.length; i++) { cell = row.createCell(i); cell.setCellStyle(cellStyleHeader); cell.setCellValue(header[i]); } int colCount = header.length; int no = 1; for (String[] obj : data) { row = sheet.createRow(no); for (int i = 0; i < colCount; i++) { cell = row.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(obj[i]); } no++; } for (int i = 0; i < header.length; i++) { sheet.autoSizeColumn(i); } File newFile = new File(path); try (FileOutputStream fileOut = new FileOutputStream(path)) { xwb.write(fileOut); } return newFile; } catch (IOException e) { return null; } }
From source file:util.ExcelConverter.java
public static File createXls(String[] header, String[][] data, String path) { try {/*from w ww .j ava 2s . c o m*/ HSSFWorkbook xwb = new HSSFWorkbook(); HSSFSheet sheet = xwb.createSheet(); CellStyle cellStyle = xwb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_LEFT); cellStyle.setAlignment(CellStyle.VERTICAL_TOP); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setWrapText(false); Font bold = xwb.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); bold.setFontHeightInPoints((short) 10); CellStyle cellStyleHeader = xwb.createCellStyle(); cellStyleHeader.setAlignment(CellStyle.ALIGN_LEFT); cellStyleHeader.setAlignment(CellStyle.VERTICAL_TOP); cellStyleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyleHeader.setFont(bold); cellStyleHeader.setWrapText(false); HSSFRow row; Cell cell; //header row = sheet.createRow(0); for (int i = 0; i < header.length; i++) { cell = row.createCell(i); cell.setCellStyle(cellStyleHeader); cell.setCellValue(header[i]); } int colCount = header.length; int no = 1; for (String[] obj : data) { row = sheet.createRow(no); for (int i = 0; i < colCount; i++) { cell = row.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(obj[i]); } no++; } for (int i = 0; i < header.length; i++) { sheet.autoSizeColumn(i); } File newFile = new File(path); try (FileOutputStream fileOut = new FileOutputStream(newFile)) { xwb.write(fileOut); } return newFile; } catch (IOException e) { return null; } }
From source file:utilities.DebtMgmtBatchInDJMS.java
private CellStyle createStandardStyle(XSSFWorkbook myWorkBook) { CellStyle style = myWorkBook.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.setWrapText(true);//from w ww . ja v a2 s .co m return style; }
From source file:Utilities.ExportToXLSX.java
private CellStyle createStandardStyle() { CellStyle 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.setWrapText(true);//w w w.j av a 2s. c o m return style; }
From source file:Utilities.GlobalVar.java
public static CellStyle createStandardStyle(XSSFWorkbook myWorkBook) { CellStyle style = myWorkBook.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.setWrapText(true);/*ww w . j av a2s . c o m*/ return style; }
From source file:Utility.TestReports.java
public void setHeaderCellStyle(org.apache.poi.hssf.usermodel.HSSFSheet sheet, org.apache.poi.ss.usermodel.Cell cell, org.apache.poi.hssf.usermodel.HSSFWorkbook wb) { CellStyle s = null; s = sheet.getWorkbook().createCellStyle(); cell.setCellStyle(s);/*from w w w. j a v a 2s . co m*/ HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); s.setFont(font); s.setAlignment(CellStyle.ALIGN_GENERAL); s.setBorderBottom(CellStyle.BORDER_THIN); s.setBorderLeft(CellStyle.BORDER_THIN); s.setBorderRight(CellStyle.BORDER_THIN); s.setBorderTop(CellStyle.BORDER_THIN); s.setVerticalAlignment(CellStyle.VERTICAL_CENTER); s.setAlignment(CellStyle.ALIGN_LEFT); s.setFont(font); }
From source file:Valuacion.valuacion.java
private void b_exel1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_b_exel1ActionPerformed // TODO add your handling code here: //h=new Herramientas(user, 0); //h.session(sessionPrograma); javax.swing.JFileChooser jF1 = new javax.swing.JFileChooser(); jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null;//www .ja v a 2 s .c om DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); formatoPorcentaje.setMinimumFractionDigits(2); if (jF1.showSaveDialog(null) == jF1.APPROVE_OPTION) { ruta = jF1.getSelectedFile().getAbsolutePath(); if (ruta != null) { File archivoXLS = new File(ruta + ".xls"); File plantilla = new File("imagenes/plantillaValuacion.xls"); Session session = HibernateUtil.getSessionFactory().openSession(); try { Path FROM = Paths.get("imagenes/plantillaValuacion.xls"); Path TO = Paths.get(ruta + ".xls"); //sobreescribir el fichero de destino, si existe, y copiar // los atributos, incluyendo los permisos rwx CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.COPY_ATTRIBUTES }; Files.copy(FROM, TO, options); FileInputStream miPlantilla = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla); Workbook libro = new HSSFWorkbook(fsFileSystem); //Sheet hoja = libro.getSheet("valuacion"); //Cargamos las cabeceras Configuracion con = (Configuracion) session.get(Configuracion.class, 1); ord = (Orden) session.get(Orden.class, Integer.parseInt(orden)); libro.getSheet("valuacion").getRow(0).getCell(10).setCellValue(con.getEmpresa()); libro.getSheet("valuacion").getRow(1).getCell(2).setCellValue(ord.getIdOrden()); libro.getSheet("valuacion").getRow(1).getCell(6).setCellValue(ord.getFecha().toString()); libro.getSheet("valuacion").getRow(1).getCell(10).setCellValue(ord.getTipo().getTipoNombre()); libro.getSheet("valuacion").getRow(2).getCell(2).setCellValue(ord.getClientes().getNombre()); libro.getSheet("valuacion").getRow(2).getCell(10).setCellValue(ord.getMarca().getMarcaNombre()); if (ord.getSiniestro() != null) libro.getSheet("valuacion").getRow(3).getCell(2).setCellValue(ord.getSiniestro()); if (ord.getFechaSiniestro() != null) libro.getSheet("valuacion").getRow(3).getCell(6) .setCellValue(ord.getFechaSiniestro().toString()); if (ord.getNoMotor() != null) libro.getSheet("valuacion").getRow(3).getCell(10).setCellValue(ord.getNoMotor()); libro.getSheet("valuacion").getRow(3).getCell(16).setCellValue(ord.getModelo()); if (ord.getPoliza() != null) libro.getSheet("valuacion").getRow(4).getCell(2).setCellValue(ord.getPoliza()); if (ord.getInciso() != null) libro.getSheet("valuacion").getRow(4).getCell(6).setCellValue(ord.getInciso()); if (ord.getNoSerie() != null) libro.getSheet("valuacion").getRow(4).getCell(10).setCellValue(ord.getNoSerie()); if (ord.getNoEconomico() != null) libro.getSheet("valuacion").getRow(4).getCell(16).setCellValue(ord.getNoEconomico()); CellStyle borde_d = libro.createCellStyle(); borde_d.setBorderBottom(CellStyle.BORDER_THIN); borde_d.setBorderTop(CellStyle.BORDER_THIN); borde_d.setBorderRight(CellStyle.BORDER_THIN); borde_d.setBorderLeft(CellStyle.BORDER_THIN); borde_d.setAlignment(CellStyle.ALIGN_RIGHT); CellStyle borde_i = libro.createCellStyle(); borde_i.setBorderBottom(CellStyle.BORDER_THIN); borde_i.setBorderTop(CellStyle.BORDER_THIN); borde_i.setBorderRight(CellStyle.BORDER_THIN); borde_i.setBorderLeft(CellStyle.BORDER_THIN); borde_i.setAlignment(CellStyle.ALIGN_LEFT); CellStyle borde_c = libro.createCellStyle(); borde_c.setBorderBottom(CellStyle.BORDER_THIN); borde_c.setBorderTop(CellStyle.BORDER_THIN); borde_c.setBorderRight(CellStyle.BORDER_THIN); borde_c.setBorderLeft(CellStyle.BORDER_THIN); borde_c.setAlignment(CellStyle.ALIGN_CENTER); double total = 0.0D; for (int ren = 0; ren < t_datos.getRowCount(); ren++) { double v = 0.0d; libro.getSheet("valuacion").createRow(ren + 8); libro.getSheet("valuacion").getRow(ren + 8).createCell(1) .setCellValue(t_datos.getValueAt(ren, 10).toString());//cant libro.getSheet("valuacion").getRow(ren + 8).getCell(1).setCellStyle(borde_d); libro.getSheet("valuacion").getRow(ren + 8).createCell(2) .setCellValue(t_datos.getValueAt(ren, 11).toString());//Med libro.getSheet("valuacion").getRow(ren + 8).getCell(2).setCellStyle(borde_c); libro.getSheet("valuacion").getRow(ren + 8).createCell(3) .setCellValue(t_datos.getValueAt(ren, 2).toString());//Grupo libro.getSheet("valuacion").getRow(ren + 8).getCell(3).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(ren + 8).createCell(4); libro.getSheet("valuacion").getRow(ren + 8).getCell(4).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(ren + 8).createCell(5); libro.getSheet("valuacion").getRow(ren + 8).getCell(5).setCellStyle(borde_i); libro.getSheet("valuacion").addMergedRegion(new CellRangeAddress(ren + 8, ren + 8, 3, 5)); libro.getSheet("valuacion").getRow(ren + 8).createCell(6) .setCellValue(t_datos.getValueAt(ren, 3).toString());//Descripcin libro.getSheet("valuacion").getRow(ren + 8).getCell(6).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(ren + 8).createCell(7); libro.getSheet("valuacion").getRow(ren + 8).getCell(7).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(ren + 8).createCell(8); libro.getSheet("valuacion").getRow(ren + 8).getCell(8).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(ren + 8).createCell(9); libro.getSheet("valuacion").getRow(ren + 8).getCell(9).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(ren + 8).createCell(10); libro.getSheet("valuacion").getRow(ren + 8).getCell(10).setCellStyle(borde_i); libro.getSheet("valuacion").addMergedRegion(new CellRangeAddress(ren + 8, ren + 8, 6, 10)); if (t_datos.getValueAt(ren, 4) != null) { v = Double.parseDouble(t_datos.getValueAt(ren, 4).toString()) * Double.parseDouble(t_datos.getValueAt(ren, 10).toString()); libro.getSheet("valuacion").getRow(ren + 8).createCell(11).setCellValue( new BigDecimal(v).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());//D/M } else libro.getSheet("valuacion").getRow(ren + 8).createCell(11).setCellValue(""); libro.getSheet("valuacion").getRow(ren + 8).getCell(11).setCellStyle(borde_d); if (t_datos.getValueAt(ren, 8) != null) { v = Double.parseDouble(t_datos.getValueAt(ren, 8).toString()) * Double.parseDouble(t_datos.getValueAt(ren, 10).toString()); libro.getSheet("valuacion").getRow(ren + 8).createCell(12).setCellValue( new BigDecimal(v).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());//Cam } else libro.getSheet("valuacion").getRow(ren + 8).createCell(12).setCellValue(""); libro.getSheet("valuacion").getRow(ren + 8).getCell(12).setCellStyle(borde_d); if (t_datos.getValueAt(ren, 5) != null) { v = Double.parseDouble(t_datos.getValueAt(ren, 5).toString()) * Double.parseDouble(t_datos.getValueAt(ren, 10).toString()); libro.getSheet("valuacion").getRow(ren + 8).createCell(13).setCellValue( new BigDecimal(v).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());//R. min } else libro.getSheet("valuacion").getRow(ren + 8).createCell(13).setCellValue(""); libro.getSheet("valuacion").getRow(ren + 8).getCell(13).setCellStyle(borde_d); if (t_datos.getValueAt(ren, 6) != null) { v = Double.parseDouble(t_datos.getValueAt(ren, 6).toString()) * Double.parseDouble(t_datos.getValueAt(ren, 10).toString()); libro.getSheet("valuacion").getRow(ren + 8).createCell(14).setCellValue( new BigDecimal(v).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());//R. med } else libro.getSheet("valuacion").getRow(ren + 8).createCell(14).setCellValue(""); libro.getSheet("valuacion").getRow(ren + 8).getCell(14).setCellStyle(borde_d); if (t_datos.getValueAt(ren, 7) != null) { v = Double.parseDouble(t_datos.getValueAt(ren, 7).toString()) * Double.parseDouble(t_datos.getValueAt(ren, 10).toString()); libro.getSheet("valuacion").getRow(ren + 8).createCell(15).setCellValue( new BigDecimal(v).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());//R. max } else libro.getSheet("valuacion").getRow(ren + 8).createCell(15).setCellValue(""); libro.getSheet("valuacion").getRow(ren + 8).getCell(15).setCellStyle(borde_d); if (t_datos.getValueAt(ren, 9) != null) { v = Double.parseDouble(t_datos.getValueAt(ren, 9).toString()) * Double.parseDouble(t_datos.getValueAt(ren, 10).toString()); libro.getSheet("valuacion").getRow(ren + 8).createCell(16).setCellValue( new BigDecimal(v).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());//Pin } else libro.getSheet("valuacion").getRow(ren + 8).createCell(16).setCellValue(""); libro.getSheet("valuacion").getRow(ren + 8).getCell(16).setCellStyle(borde_d); Double suma = Double.parseDouble(t_datos.getValueAt(ren, 22).toString()); suma *= ord.getCompania().getImporteHora(); total += suma; if (t_datos.getValueAt(ren, 22) != null) libro.getSheet("valuacion").getRow(ren + 8).createCell(17) .setCellValue(formatoPorcentaje.format(suma));//Costo M.O else libro.getSheet("valuacion").getRow(ren + 8).createCell(17).setCellValue(""); libro.getSheet("valuacion").getRow(ren + 8).getCell(17).setCellStyle(borde_d); } int renglon = t_datos.getRowCount() + 8; int celda = renglon; libro.getSheet("valuacion").createRow(renglon); libro.getSheet("valuacion").getRow(renglon).createCell(6);// libro.getSheet("valuacion").getRow(renglon).getCell(6).setCellValue( "Costo M.O:$" + formatoPorcentaje.format(ord.getCompania().getImporteHora()) + " Total de Horas:" + t_horas.getText()); libro.getSheet("valuacion").getRow(renglon).getCell(6).setCellStyle(borde_c); libro.getSheet("valuacion").getRow(renglon).createCell(7); libro.getSheet("valuacion").getRow(renglon).getCell(7).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(renglon).createCell(8); libro.getSheet("valuacion").getRow(renglon).getCell(8).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(renglon).createCell(9); libro.getSheet("valuacion").getRow(renglon).getCell(9).setCellStyle(borde_i); libro.getSheet("valuacion").getRow(renglon).createCell(10); libro.getSheet("valuacion").getRow(renglon).getCell(10).setCellStyle(borde_i); libro.getSheet("valuacion").addMergedRegion(new CellRangeAddress(renglon, renglon, 6, 10)); libro.getSheet("valuacion").getRow(renglon).createCell(11);// libro.getSheet("valuacion").getRow(renglon).getCell(11).setCellType(HSSFCell.CELL_TYPE_FORMULA); libro.getSheet("valuacion").getRow(renglon).getCell(11) .setCellFormula("SUM(L9:L" + celda + ")"); libro.getSheet("valuacion").getRow(renglon).getCell(11).setCellStyle(borde_d); libro.getSheet("valuacion").getRow(renglon).createCell(12);// libro.getSheet("valuacion").getRow(renglon).getCell(12).setCellType(HSSFCell.CELL_TYPE_FORMULA); libro.getSheet("valuacion").getRow(renglon).getCell(12) .setCellFormula("SUM(M9:M" + celda + ")"); libro.getSheet("valuacion").getRow(renglon).getCell(12).setCellStyle(borde_d); libro.getSheet("valuacion").getRow(renglon).createCell(13);// libro.getSheet("valuacion").getRow(renglon).getCell(13).setCellType(HSSFCell.CELL_TYPE_FORMULA); libro.getSheet("valuacion").getRow(renglon).getCell(13) .setCellFormula("SUM(N9:N" + celda + ")"); libro.getSheet("valuacion").getRow(renglon).getCell(13).setCellStyle(borde_d); libro.getSheet("valuacion").getRow(renglon).createCell(14);// libro.getSheet("valuacion").getRow(renglon).getCell(14).setCellType(HSSFCell.CELL_TYPE_FORMULA); libro.getSheet("valuacion").getRow(renglon).getCell(14) .setCellFormula("SUM(O9:O" + celda + ")"); libro.getSheet("valuacion").getRow(renglon).getCell(14).setCellStyle(borde_d); libro.getSheet("valuacion").getRow(renglon).createCell(15);// libro.getSheet("valuacion").getRow(renglon).getCell(15).setCellType(HSSFCell.CELL_TYPE_FORMULA); libro.getSheet("valuacion").getRow(renglon).getCell(15) .setCellFormula("SUM(P9:P" + celda + ")"); libro.getSheet("valuacion").getRow(renglon).getCell(15).setCellStyle(borde_d); libro.getSheet("valuacion").getRow(renglon).createCell(16);// libro.getSheet("valuacion").getRow(renglon).getCell(16).setCellType(HSSFCell.CELL_TYPE_FORMULA); libro.getSheet("valuacion").getRow(renglon).getCell(16) .setCellFormula("SUM(Q9:Q" + celda + ")"); libro.getSheet("valuacion").getRow(renglon).getCell(16).setCellStyle(borde_d); libro.getSheet("valuacion").getRow(renglon).createCell(17);// libro.getSheet("valuacion").getRow(renglon).getCell(17) .setCellValue(formatoPorcentaje.format(total)); libro.getSheet("valuacion").getRow(renglon).getCell(17).setCellStyle(borde_d); FileOutputStream archivo = new FileOutputStream(archivoXLS); libro.write(archivo); archivo.close(); //miPlantilla.close(); Desktop.getDesktop().open(archivoXLS); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto"); } if (session != null) if (session.isOpen()) { session.flush(); session.clear(); session.close(); } } } }