List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet
Sheet getSheet(String name);
From source file:sqlitemanager.Excel2Dataset.java
public static void export2Excel(List<DataTable> dts, String outPath) { if (dts == null || dts.isEmpty()) { System.err.println("No DataTable was found!"); return;/* www . jav a 2s .c om*/ } try { // Create a work book reference Workbook excel = null; if (outPath.endsWith(".xls")) { excel = new HSSFWorkbook(); } else if (outPath.endsWith(".xlsx")) { excel = new XSSFWorkbook(); } else { System.err.println("No XLS or XLSX file found!"); return; } DataTable dt; for (int i = 0; i < dts.size(); i++) { dt = dts.get(i); excel.createSheet(dt.getName()); Sheet sheet = excel.getSheet(dt.getName()); sheet.createRow(0); Row r; Cell c; int fieldCt = dt.getFieldCount(); fieldType[] types = dt.getFieldTypes(); for (int j = 0; j < dt.getRecordCount() + 1; j++) { sheet.createRow(j); for (int k = 0; k < fieldCt; k++) { r = sheet.getRow(j); r.createCell(k); } } for (int j = 0; j < fieldCt; j++) { r = sheet.getRow(0); r.createCell(j); r.getCell(j).setCellValue(dt.getFieldNames()[j]); } for (int j = 0; j < fieldCt; j++) { switch (types[j]) { case Integer: for (int k = 1; k < dt.getRecordCount() + 1; k++) { sheet.getRow(k).getCell(j).setCellValue((int) dt.getField(j).get(k - 1)); } break; case Double: for (int k = 1; k < dt.getRecordCount() + 1; k++) { sheet.getRow(k).getCell(j).setCellValue((double) dt.getField(j).get(k - 1)); } break; case String: for (int k = 1; k < dt.getRecordCount() + 1; k++) { sheet.getRow(k).getCell(j).setCellValue((String) dt.getField(j).get(k - 1)); } break; } } } excel.write(new FileOutputStream(outPath)); } catch (Exception ex) { System.err.println(String.format("Exporting to %s ERROR!", outPath)); Logger.getLogger(Excel2Dataset.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:step.datapool.excel.ExcelDataPoolImpl.java
License:Open Source License
@Override public void init() { super.init(); String bookName = configuration.getFile().get(); String sheetName = configuration.getWorksheet().get(); logger.debug("book: " + bookName + " sheet: " + sheetName); ExcelFileLookup excelFileLookup = new ExcelFileLookup(context); File workBookFile = excelFileLookup.lookup(bookName); forWrite = configuration.getForWrite().get(); workbookSet = new WorkbookSet(workBookFile, ExcelFunctions.getMaxExcelSize(), forWrite, true); Workbook workbook = workbookSet.getMainWorkbook(); if (sheetName == null || sheetName.isEmpty()) { if (workbook.getNumberOfSheets() > 0) { sheet = workbook.getSheetAt(0); } else {//from ww w . j a v a 2 s. c o m if (forWrite) { sheet = workbook.createSheet(); } else { throw new ValidationException("The workbook " + workBookFile.getName() + " contains no sheet"); } } } else { sheet = workbook.getSheet(sheetName); if (sheet == null) { if (forWrite) { sheet = workbook.createSheet(sheetName); } else { throw new ValidationException( "The sheet " + sheetName + " doesn't exist in the workbook " + workBookFile.getName()); } } } resetCursor(); }
From source file:test.XExcel.java
public static void main(String[] args) throws FileNotFoundException, IOException { Properties bundle = new Properties(); bundle.load(new FileInputStream(new File("configuration.properties"))); Integer counter = Integer.parseInt(bundle.getProperty("loop.counter")); String titles[] = { "Firstname", "Lastname", "Country", "Language" }; String data[][] = { { "noman ali", "abbasi", "PK", "EN" }, { "ahsan", "shaikh", "PK", "EN" }, { "abdul jalil", "ahmed", "PK", "EN" }, { "umair", "khan", "PK", "EN" }, { "abdul rahim", "khan", "PK", "EN" } }; int rowCount = 0; try {//ww w . j a va 2 s.c o m File xlsxFile = new File("C:/workbook.xlsx"); Workbook wb = null; Sheet sheet = null; Row row = null; if (xlsxFile.exists()) { FileInputStream fileInputStream = new FileInputStream(xlsxFile); wb = new XSSFWorkbook(fileInputStream); sheet = wb.getSheet("Test"); rowCount = sheet.getPhysicalNumberOfRows(); System.err.println("Writingxisting file ...."); for (int i = 0; i < counter; i++) { row = sheet.createRow(rowCount++); int rndNumber = new Random().nextInt(3); System.out.println(rndNumber); for (int c = 0; c < titles.length; c++) { Cell cell = row.createCell(c); cell.setCellValue(data[rndNumber][c]); } } System.err.println(xlsxFile.delete()); } else { System.err.println("Creatingl file ...."); wb = new XSSFWorkbook(); sheet = wb.createSheet("Test"); row = sheet.createRow(rowCount++); CellStyle cellStyle = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font); for (int i = 0; i < titles.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(cellStyle); } for (int i = 0; i < counter; i++) { row = sheet.createRow(rowCount++); int rndNumber = new Random().nextInt(3); for (int c = 0; c < titles.length; c++) { Cell cell = row.createCell(c); cell.setCellValue(data[rndNumber][c]); } } } FileOutputStream fileOut; try { fileOut = new FileOutputStream("C:/workbook.xlsx", true); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } System.out.println("Last " + sheet.getLastRowNum() + ", " + sheet.getPhysicalNumberOfRows()); } catch (java.lang.IllegalArgumentException illegalArgumentException) { System.err.println(illegalArgumentException.getMessage()); } }
From source file:Teste.importarExcel.java
/** * @param args the command line arguments *///from w ww . ja v a2s.co m public static void main(String[] args) { // TODO code application logic here try { //get arquivo File file = new File("C:\\Users\\Alessandro\\Desktop\\TCC2\\IMPORTAR\\Alunos.xls"); //get extensao do arquivo String name = file.toString(); int pos = name.lastIndexOf('.'); String ext = name.substring(pos + 1); FileInputStream fileIn = new FileInputStream(file); Workbook obj = null; if (ext.equals("xlsx")) { try { //Metodo aceita o path do arquivo obj = new XSSFWorkbook(fileIn); } catch (IOException ex) { throw new RuntimeException(ex); } } else if (ext.equals("xls")) { try { //Metodo nao aceita string do path do arquivo obj = new HSSFWorkbook(fileIn); } catch (IOException ex) { throw new RuntimeException(ex); } } else { throw new IllegalArgumentException("Received file does not have a standard excel extension."); } int o = 0; Sheet worksheet = obj.getSheet("Plan1"); Row row; Cell cell; for (int i = 0; i <= worksheet.getLastRowNum(); i++) { row = worksheet.getRow(i); String linha = ""; for (int j = 0; j < 2; j++) { cell = row.getCell(j); if (cell.getCellType() == 1) { linha += " | " + cell.getStringCellValue(); } else { double aux = 0; int aux2 = 0; aux = cell.getNumericCellValue(); aux2 = (int) aux; linha += " | " + aux2; } } System.out.println(linha); } } catch (FileNotFoundException ex) { System.out.println("Arquivo no encontrado"); } }
From source file:tools.xor.service.AggregateManager.java
License:Apache License
@Override /**/*from w w w .j a va 2 s . c o m*/ * For now we handle only one aggregate entity in the document. * Later on we can update it handle multiple entities. * * Ideally, we would want each entity to be in a separate document, * so we can process it efficiently using streaming. */ public Object importAggregate(InputStream is, Settings settings) throws IOException { validateImportExport(); try { Workbook wb = WorkbookFactory.create(is); Sheet entitySheet = wb.getSheet(Constants.XOR.EXCEL_ENTITY_SHEET); if (entitySheet == null) { throw new RuntimeException("The entity sheet is missing"); } // Get the entity class name Map<String, Integer> colMap = getHeaderMap(entitySheet); if (!colMap.containsKey(Constants.XOR.TYPE)) { // TODO: Fallback to entity class in settings if provided throw new RuntimeException("XOR.type column is missing"); } Row entityRow = entitySheet.getRow(1); if (entityRow == null) { throw new RuntimeException("Entity row is missing"); } Cell typeCell = entityRow.getCell(colMap.get(Constants.XOR.TYPE)); String entityClassName = typeCell.getStringCellValue(); try { settings.setEntityClass(Class.forName(entityClassName)); } catch (ClassNotFoundException e) { throw new RuntimeException("Class " + entityClassName + " is not found"); } /****************************************************** * Algorithm * * 1. Create all objects with the XOR.id * 2. Create the collections * 3. Associate the collections to their owners * 4. Then finally call JSONTransformer.unpack to link the objects by XOR.id * ********************************************************/ // 1. Create all objects with the XOR.id Map<String, String> collectionSheets = new HashMap<String, String>(); Map<String, String> entitySheets = new HashMap<String, String>(); entitySheets.put(Constants.XOR.EXCEL_ENTITY_SHEET, entityClassName); Map<String, JSONObject> idMap = parseEntities(wb, entitySheets, collectionSheets); // 2. Create the collections // The key in the collection property map is of the form <owner_xor_id>:<property> Map<String, JSONArray> collectionPropertyMap = parseCollections(wb, collectionSheets, idMap); // 3. Associate the collections to their owners // Replace all objectref prefix keys with the actual objects // Replace all collection properties with the array objects link(wb, idMap, collectionPropertyMap); // Find the root Cell idCell = entityRow.getCell(colMap.get(Constants.XOR.ID)); String rootId = idCell.getStringCellValue(); JSONObject root = idMap.get(rootId); // Finally persist the root object // call the update persistence method Class entityClass; try { entityClass = Class.forName(root.getString(Constants.XOR.TYPE)); } catch (ClassNotFoundException | JSONException e) { throw new RuntimeException( "Unable to construct root entity. Either the class is not found or the class name is missing"); } return update(root, entityClass); } catch (EncryptedDocumentException e) { throw new RuntimeException("Document is encrypted, provide a decrypted inputstream"); } catch (InvalidFormatException e) { throw new RuntimeException("The provided inputstream is not valid. " + e.getMessage()); } }
From source file:tools.xor.service.AggregateManager.java
License:Apache License
/** * Parse the given Excel workbook and group the entity and collection sheets separately. * //from www . j a v a 2 s . c o m * @param wb given workbook * @param entitySheets Sheets containing entity data * @param collectionSheets Sheets capturing collection relationships. Will be used in subsequent processing * @return a map of JSON entities keyed by their XOR id */ private Map<String, JSONObject> parseEntities(Workbook wb, Map<String, String> entitySheets, Map<String, String> collectionSheets) { // First find all the entity sheets Sheet sheetMap = wb.getSheet(Constants.XOR.EXCEL_INDEX_SHEET); // SheetName is in first column // Entity type and property is in second column for (int i = 0; i <= sheetMap.getLastRowNum(); i++) { Row row = sheetMap.getRow(i); String entityInfo = row.getCell(1).getStringCellValue(); if (getProperty(entityInfo).isMany()) { collectionSheets.put(row.getCell(0).getStringCellValue(), entityInfo); } else { entitySheets.put(row.getCell(0).getStringCellValue(), entityInfo); } } Map<String, JSONObject> idMap = new HashMap<String, JSONObject>(); for (Map.Entry<String, String> entry : entitySheets.entrySet()) { processEntitySheet(wb, entry.getKey(), idMap); } return idMap; }
From source file:tools.xor.service.AggregateManager.java
License:Apache License
private void processEntitySheet(Workbook wb, String sheetName, Map<String, JSONObject> idMap) { // Ensure we have the XOR.id column in the entity sheet Sheet entitySheet = wb.getSheet(sheetName); Map<String, Integer> colMap = getHeaderMap(entitySheet); if (!colMap.containsKey(Constants.XOR.ID)) { throw new RuntimeException("XOR.id column is missing"); }//from w w w. ja v a2 s.co m // process each entity for (int i = 1; i <= entitySheet.getLastRowNum(); i++) { JSONObject entityJSON = getJSON(colMap, entitySheet.getRow(i)); idMap.put(entityJSON.getString(Constants.XOR.ID), entityJSON); } }
From source file:tools.xor.service.AggregateManager.java
License:Apache License
private void processCollectionSheet(Workbook wb, String sheetName, String entityInfo, Map<String, JSONArray> collectionPropertyMap, Map<String, JSONObject> idMap) { // Ensure we have the XOR.id column in the entity sheet Sheet collectionSheet = wb.getSheet(sheetName); Map<String, Integer> colMap = getHeaderMap(collectionSheet); // A collection can have value objects, so XOR.ID is not mandatory // But a collection entry should have a collection owner if (!colMap.containsKey(Constants.XOR.OWNER_ID)) { throw new RuntimeException("XOR.owner.id column is missing"); }/* w w w .ja v a2 s .c om*/ // process each collection entry for (int i = 1; i <= collectionSheet.getLastRowNum(); i++) { JSONObject collectionEntryJSON = getJSON(colMap, collectionSheet.getRow(i)); String key = getCollectionKey(collectionEntryJSON.getString(Constants.XOR.OWNER_ID), entityInfo); addCollectionEntry(collectionPropertyMap, key, collectionEntryJSON); // If the collection element is an entity add it to the idMap also if (collectionEntryJSON.has(Constants.XOR.ID)) { try { idMap.put(collectionEntryJSON.getString(Constants.XOR.ID), collectionEntryJSON); } catch (Exception e) { String longStr = new Long(collectionEntryJSON.getLong(Constants.XOR.ID)).toString(); idMap.put(longStr, collectionEntryJSON); } } } }
From source file:tools.xor.service.AggregateManager.java
License:Apache License
private void createBOSheet(Workbook wb, String sheetName, String entityInfo, List<BusinessObject> boList, BusinessObject owner) {// w w w.ja v a2 s . c o m int colNo = 0; int rowNo = 1; SXSSFSheet sh = (SXSSFSheet) wb.getSheet(sheetName); if (sh == null) { sh = (SXSSFSheet) wb.createSheet(sheetName); } else { rowNo = sh.getLastRowNum() + 1; } Map<String, Integer> propertyColIndex = new HashMap<String, Integer>(); for (BusinessObject bo : boList) { if (bo.getContainmentProperty() != null && bo.getContainmentProperty().isMany()) { createBOSheet(wb, sheetName, entityInfo, bo.getList(), (BusinessObject) bo.getContainer()); continue; } List<String> propertyPaths = new ArrayList<String>(); // Based on polymorphism, the actual instance can be a different subtype // so we need to get a fresh property list and calculate the column indexes // as new properties might be present and would need to be mapped to additional columns if (owner == null && bo.getContainer() != null) { owner = (BusinessObject) bo.getContainer(); } if (owner != null) { propertyPaths.add(Constants.XOR.OWNER_ID); } propertyPaths.add(Constants.XOR.ID); propertyPaths.add(Constants.XOR.TYPE); for (Property property : bo.getType().getProperties()) { if (property.isMany()) { propertyPaths.add(ExcelJsonCreationStrategy.getCollectionTypeKey(property)); // Collections are handled separately continue; } // Skip open content until we come with a default serialized form for empty object // Currently it fails validation since empty string does not equal JSONObject if (property.isOpenContent()) { continue; } // Handle embedded objects and expand them if necessary propertyPaths.addAll(property.expand()); } for (String propertyPath : propertyPaths) { if (!propertyColIndex.containsKey(propertyPath)) { propertyColIndex.put(propertyPath, colNo++); } } // TODO: add columns only if the value is not null Row row = sh.createRow(rowNo++); for (String propertyPath : propertyPaths) { Cell cell = row.createCell(propertyColIndex.get(propertyPath)); Object value = null; if (Constants.XOR.OWNER_ID.equals(propertyPath)) { value = owner.getOpenProperty(Constants.XOR.ID); } else if (Constants.XOR.ID.equals(propertyPath) || propertyPath.startsWith(Constants.XOR.TYPE + Constants.XOR.SEP)) { value = bo.getOpenProperty(propertyPath); } else if (Constants.XOR.TYPE.equals(propertyPath)) { value = bo.getInstanceClassName(); } else if (propertyPath.startsWith(Constants.XOR.OBJECTREF)) { String path = propertyPath.substring(Constants.XOR.OBJECTREF.length()); value = bo.getExistingDataObject(Settings.convertToBOPath(path)); if (value != null && value instanceof BusinessObject) { value = ((BusinessObject) value).getOpenProperty(Constants.XOR.ID); } else if (value != null) { throw new RuntimeException("ObjectRef needs to refer to an Entity: " + value.toString()); } } else { value = bo.get(Settings.convertToBOPath(propertyPath)); } if (value != null) { cell.setCellValue(value.toString()); } } } writeColumnNames(sh, propertyColIndex); }
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;//from w w w .j ava 2s . co m 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(); } } } }