List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:log_compressor.space_checker.java
public static void space_checker() throws IOException, InterruptedException { HashMap<String, ArrayList<String>> hm = new HashMap<String, ArrayList<String>>(); HashMap<String, List<String>> hm1 = new HashMap<String, List<String>>(); List<String> file = import_file("D:\\log\\input.txt"); ArrayList<Integer> server_position = new ArrayList<Integer>(); ArrayList<String> server_list = new ArrayList<String>(); File myFile = new File("D:\\log\\log_output.xlsx"); if (!myFile.exists()) { FileOutputStream fos = new FileOutputStream(myFile); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("sheet1"); workbook.write(fos);/* w w w . j a v a 2s. com*/ } int k = 0; for (String s : file) { if (s.startsWith("Server Name")) { int i = s.lastIndexOf(":"); server_position.add(k); } k++; } for (int j = 0; j < server_position.size(); j++) { ArrayList<String> disk = new ArrayList<String>(); String servername = file.get(server_position.get(j)); server_list.add(servername); if (j != server_position.size() - 1) { for (int l = server_position.get(j); l < server_position.get(j + 1); l++) { if (file.get(l).startsWith("Drive")) { int drive_position = file.get(l).lastIndexOf(":"); String driveId = file.get(l).substring(drive_position - 1, drive_position); String drive_free_space = file.get(l).substring(drive_position + 1); String result = driveId + ":" + drive_free_space; disk.add(result); } else if (file.get(l).startsWith("Cannot retrieve DISK information from server")) { String result = "need manual check"; disk.add(result); } } } else { for (int l = server_position.get(server_position.size() - 1); l < file.size(); l++) { if (file.get(l).startsWith("Drive")) { int drive_position = file.get(l).lastIndexOf(":"); String driveId = file.get(l).substring(drive_position - 1, drive_position); String drive_free_space = file.get(l).substring(drive_position + 1); String result = driveId + ":" + drive_free_space; disk.add(result); } else if (file.get(l).startsWith("Cannot retrieve DISK information from server")) { String result = "need manual check"; disk.add(result); } } } /* Set<String> s = new LinkedHashSet<>(event); List<String> event1 = new ArrayList<String>(); event1.addAll(s); */ hm.put(servername, disk); } /* for (int j = 0; j < server_position.size(); j++) { ArrayList<String> event = new ArrayList<String>(); String servername = file.get(server_position.get(j)); if (j != server_position.size() - 1) { for (int l = server_position.get(j); l < server_position.get(j + 1); l++) { if (file.get(l).startsWith("Event")) { int eventPosition = file.get(l).lastIndexOf(":"); String eventId = file.get(l).substring(eventPosition + 1); event.add(eventId); } else if (file.get(l).startsWith("No critical")) { String eventId = "Normal"; event.add(eventId); } else if (file.get(l).startsWith("Cannot retrieve EVENT information")) { String eventId = "Need maual check"; event.add(eventId); } } } else { for (int l = server_position.get(server_position.size() - 1); l < file.size(); l++) { if (file.get(l).startsWith("Event")) { int eventPosition = file.get(l).lastIndexOf(":"); String eventId = file.get(l).substring(eventPosition + 1); event.add(eventId); } else if (file.get(l).startsWith("No critical")) { String eventId = "Normal"; event.add(eventId); } else if (file.get(l).startsWith("Cannot retrieve EVENT information")) { String eventId = "Need maual check"; event.add(eventId); } } } Set<String> s = new LinkedHashSet<>(event); List<String> event1 = new ArrayList<String>(); event1.addAll(s); hm1.put(servername, event1); } */ write_disk_space(hm, server_list); for (String server : server_list) { System.out.println(" "); System.out.println(server); //System.out.println(hm.get(server).toString()); System.out.println(hm1.get(server).toString()); System.out.println("==============================="); } }
From source file:lospolloshermanos.SalesTablePan.java
public void PrintSales() { if (items != null && categories != null) { XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet itemssheet = workbook.createSheet("Item-wise"); XSSFSheet categorysheet = workbook.createSheet("Cateogry-wise"); XSSFRow row;//from ww w .j a va 2s . c o m XSSFFont font = workbook.createFont(); font.setBold(true); XSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); Cell cell; row = itemssheet.createRow(0); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("Meal Name"); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("Quantity"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("Sub Total"); font.setBold(false); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); for (int i = 2; i <= no_of_items + 1; i++) { try { row = itemssheet.createRow(i); String QtyTot = items.getString("QtyTot"); String SubTot = items.getString("SubTot"); cell = row.createCell(0); cell.setCellValue(items.getString("MName")); cell = row.createCell(1); if (QtyTot != null) cell.setCellValue(QtyTot); else cell.setCellValue("0"); cell = row.createCell(2); if (SubTot != null) cell.setCellValue(SubTot); else cell.setCellValue("0.00"); items.next(); } catch (SQLException ex) { Logger.getLogger(SalesTablePan.class.getName()).log(Level.SEVERE, null, ex); } } row = itemssheet.createRow(no_of_items + 3); font.setBold(true); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("Grand Total"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(GrandTot + ""); row = categorysheet.createRow(0); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("Category Name"); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("No of items sold"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("Sub Total"); font.setBold(false); for (int i = 2; i <= no_of_cats + 1; i++) { try { row = categorysheet.createRow(i); cell = row.createCell(0); cell.setCellValue(categories.getString("CName")); cell = row.createCell(1); String QtyTot = categories.getString("QtyTot"); String SubTot = categories.getString("SubTot"); if (QtyTot != null) cell.setCellValue(QtyTot); else cell.setCellValue("0"); cell = row.createCell(2); if (SubTot != null) cell.setCellValue(SubTot); else cell.setCellValue("0.00"); categories.next(); } catch (SQLException ex) { Logger.getLogger(SalesTablePan.class.getName()).log(Level.SEVERE, null, ex); } } row = categorysheet.createRow(no_of_cats + 3); font.setBold(true); cell = row.createCell(0); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("Grand Total"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(GrandTot + ""); font.setBold(false); itemssheet.autoSizeColumn(0); itemssheet.autoSizeColumn(1); itemssheet.autoSizeColumn(2); categorysheet.autoSizeColumn(0); categorysheet.autoSizeColumn(1); categorysheet.autoSizeColumn(2); try { items.first(); categories.first(); FileOutputStream out = new FileOutputStream( new File("C:/Program Files/RMS/Sales_from_" + Date1 + "_to_" + Date2 + ".xlsx")); workbook.write(out); out.close(); } catch (Exception e) { } } }
From source file:Main.Database.java
/** * @param dbTable - the database table which contains job results from a * particular job board./*from www . java 2s . c o m*/ * @param excelSheetIndex - the sheet index on the Excel File which will * house the results from the database. * * This method appends results from the specific database table onto the * index of the excel sheet. */ /* public void appendDBTableToExcel(String dbTable, int excelSheetIndex) { try { connect = connectDatabase(); String sqlQuery = "Select *from " + dbTable; // create the java statement selectStmt = connect.prepareStatement(sqlQuery); resultSet = selectStmt.executeQuery(); //call upon the excel file. excelFile = new File("C:\\Users\\jason\\Desktop\\Job Scraper\\JobScraper\\src\\main\\java\\Main\\links.xlsx"); excelInputStream = new FileInputStream(excelFile); Workbook workbook = create(excelInputStream); CreationHelper createHelper = workbook.getCreationHelper(); Sheet sheet = workbook.getSheetAt(excelSheetIndex); excelOutputStream = new FileOutputStream(excelFile); //set link style to blue CellStyle hlinkstyle = workbook.createCellStyle(); Font hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); hlinkstyle.setFont(hlinkfont); // iterate through the java database,grabbing the details of the job. System.out.println("I can see contents of database"); while (resultSet.next()) { this.title = resultSet.getString("title"); this.link = resultSet.getString("link"); this.date = resultSet.getString("date"); System.out.println(this.title + " / " + this.link + " / " + this.date ); //append database Information onto Excel Row row = sheet.createRow(rowNumber); Cell titleCell = row.createCell(0); titleCell.setCellValue(title); Cell linkCell = row.createCell(1); linkCell.setCellValue(link); Cell dateCell = row.createCell(2); dateCell.setCellValue(date); //Make the link a clickable blue url. XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); hyperLink.setAddress(link); linkCell.setHyperlink(hyperLink); linkCell.setCellStyle(hlinkstyle); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i+=2) { sheet.autoSizeColumn(i); } selectStmt.close(); excelOutputStream = new FileOutputStream(excelFile); workbook.write(excelOutputStream); excelOutputStream.close(); System.out.println("Printed out " + dbTable); } catch (SQLException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(IndeedScraper.class.getName()).log(Level.SEVERE, null, ex); } } */ public void appendDBTableToExcel(String dbTable) { rowNumber = 0; try { connect = connectDatabase(); Statement statement = connect.createStatement(); ResultSet resultSet = statement.executeQuery("Select * from " + dbTable); File file = new File("exceldatabase.xlsx"); if (file.exists()) { closeDBSession(); callExistingExcel(dbTable); } else { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(dbTable); while (resultSet.next()) { XSSFRow row = spreadsheet.createRow(rowNumber); XSSFCell titleCell = row.createCell(0); titleCell.setCellValue(resultSet.getString("title")); XSSFCell linkCell = row.createCell(1); linkCell.setCellValue(resultSet.getString("link")); XSSFCell dateCell = row.createCell(2); dateCell.setCellValue(resultSet.getString("date")); System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link") + resultSet.getString("date")); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i += 2) { spreadsheet.autoSizeColumn(i); } FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx")); workbook.write(out); out.close(); System.out.println("exceldatabase.xlsx written successfully"); } } catch (SQLException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Main.Database.java
private void callExistingExcel(String dbTable) throws FileNotFoundException, IOException, SQLException { rowNumber = 0;/*from w ww .ja v a 2s. co m*/ connect = connectDatabase(); Statement statement = connect.createStatement(); ResultSet resultSet = statement.executeQuery("Select * from " + dbTable); File file = new File("exceldatabase.xlsx"); FileInputStream fIP = new FileInputStream(file); //Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); try { if (file.isFile() && file.exists()) { System.out.println("openworkbook.xlsx file open successfully."); XSSFSheet spreadsheet = workbook.createSheet(dbTable); while (resultSet.next()) { XSSFRow row = spreadsheet.createRow(rowNumber); XSSFCell titleCell = row.createCell(0); titleCell.setCellValue(resultSet.getString("title")); XSSFCell linkCell = row.createCell(1); linkCell.setCellValue(resultSet.getString("link")); XSSFCell dateCell = row.createCell(2); dateCell.setCellValue(resultSet.getString("date")); System.out.println(resultSet.getString("title") + " / " + resultSet.getString("link") + resultSet.getString("date")); rowNumber++; } //autosizes the columns for clarity for (int i = 0; i < 3; i += 2) { spreadsheet.autoSizeColumn(i); } FileOutputStream out = new FileOutputStream("exceldatabase.xlsx"); workbook.write(out); out.close(); System.out.println("Successfully written"); } else { System.out.println("Error to open openworkbook.xlsx file."); } } catch (IllegalArgumentException ex) { } }
From source file:mvjce.internal_sheet.java
public static void internal_details(XSSFWorkbook workbook) { XSSFSheet spreadsheet = workbook.createSheet("test_excel_internal"); XSSFRow row = spreadsheet.createRow((short) 0); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("MVJ College of Bangalore- 560067"); //MEARGING CELLS spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19)); XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true);/* w w w . ja v a 2 s . c om*/ XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); cell.setCellStyle(style); Excel_operations.set_subcode(); row = spreadsheet.createRow((short) 1); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Department of " + dept_name); spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19)); cell.setCellStyle(style); row = spreadsheet.createRow(3); row.setHeight((short) 600); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Semester: \n" + sem_string + sec); cell.setCellStyle(style); row = spreadsheet.createRow(4); String[] text = new String[3]; text[0] = "SI.No"; text[1] = "USN"; text[2] = "STUDENT NAME"; for (int i = 0; i < 3; i++) { cell = (XSSFCell) row.createCell((short) i); cell.setCellValue(text[i]); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(4, 5, i, i)); } XSSFRow row2 = spreadsheet.createRow((short) 5); for (int i = 0, j = 3; j <= 23; j += 4) { cell = row.createCell((short) j); cell.setCellValue(sub[i]); i++; cell.setCellStyle(style); cell = row2.createCell(j); cell.setCellValue("T1"); cell.setCellStyle(style); cell = row2.createCell(j + 1); cell.setCellValue("T2"); cell.setCellStyle(style); cell = row2.createCell(j + 2); cell.setCellValue("T3"); cell.setCellStyle(style); cell = row2.createCell(j + 3); cell.setCellValue("Avg"); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(4, 4, j, j + 3)); Excel_operations.insert_internals(workbook, spreadsheet); } }
From source file:mvjce.Writesheet.java
public static void writesheet() { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(sem_string + sec); XSSFRow row = spreadsheet.createRow((short) 0); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("MVJ College of Bangalore- 560067"); //MEARGING CELLS spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19)); XSSFFont font = workbook.createFont(); font.setFontName("Arial"); font.setBold(true);//from ww w. j av a 2 s . c om XSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(font); cell.setCellStyle(style); Excel_operations.set_subcode(); for (int i = 0; i < 8; i++) { internal_sheet.sub[i] = sub[i]; } internal_sheet.dept_name = dept_name; internal_sheet.sec = sec; internal_sheet.sem_string = sem_string; internal_sheet.internal_details(workbook); row = spreadsheet.createRow((short) 1); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Department of " + dept_name); spreadsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 19)); cell.setCellStyle(style); row = spreadsheet.createRow(4); row.setHeight((short) 600); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Semester: \n" + sem_string + sec); cell.setCellStyle(style); row = spreadsheet.createRow(5); String[] text = new String[3]; text[0] = "SI.No"; text[1] = "USN"; text[2] = "STUDENT\nNAME"; for (int i = 0; i < 3; i++) { cell = (XSSFCell) row.createCell((short) i); cell.setCellValue(text[i]); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 7, i, i)); } int j = 0; XSSFRow row1 = spreadsheet.createRow((short) 6); row1.setHeight((short) 1000); XSSFRow row2 = spreadsheet.createRow((short) 7); row2.setHeight((short) 1000); for (int i = 3; i < 18; i++) { cell = row1.createCell((short) i); cell.setCellValue("Total no. of classes"); cell.setCellStyle(style); cell = row2.createCell((short) i); cell.setCellValue("No.of Classes attended"); cell.setCellStyle(style); cell = row2.createCell((short) i + 1); cell.setCellValue("%"); cell.setCellStyle(style); cell = row.createCell((short) i); cell.setCellValue(sub[j]); j++; cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 5, i, i + 1)); i++; } cell = row1.createCell((short) 19); cell.setCellValue("%"); cell.setCellStyle(style); cell = row.createCell((short) 19); cell.setCellValue("AVG"); cell.setCellStyle(style); spreadsheet.addMergedRegion(new CellRangeAddress(5, 5, 19, 19)); Excel_operations.fill_exceldata(workbook, spreadsheet); try { FileOutputStream out = new FileOutputStream(new File("test_excel.xlsx")); workbook.write(out); out.close(); } catch (Exception e) { Database.print_error("Excel_output_stream"); } System.out.println("typesofcells.xlsx written successfully"); }
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@SuppressWarnings("unchecked") @Override/*w w w . java 2 s .c om*/ public void arreglaFechas(OutputStream out) { log.debug("Arreglando fechas"); Date inicio = new Date(); XSSFWorkbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy")); XSSFSheet fechas = wb.createSheet("FECHAS-ANTERIORES"); int fechasRow = 0; XSSFSheet fechas2 = wb.createSheet("FECHAS-POSTERIORES"); int fechas2Row = 0; Transaction tx = null; try { tx = currentSession().beginTransaction(); Query update = currentSession() .createQuery("update Activo set fechaCompra = :fechaCompra where id = :id"); Query query = currentSession().createQuery( "select new Activo(a.id, a.descripcion, a.fechaCompra, a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo) from Activo a where a.fechaCompra < :fechaCompra order by a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo"); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); query.setDate("fechaCompra", sdf.parse("01/01/1970")); List<Activo> activos = query.list(); int cont = 0; for (Activo activo : activos) { Calendar cal1 = Calendar.getInstance(); cal1.setTime(activo.getFechaCompra()); if (cal1.get(Calendar.YEAR) < 10) { log.debug("Pasando al ao 2000 {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, 2000); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas.createRow(fechasRow++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } else if (cal1.get(Calendar.YEAR) < 100) { log.debug("Pasando al ao 1900 {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, 1900); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas.createRow(fechasRow++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } else if (cal1.get(Calendar.YEAR) >= 1900 && cal1.get(Calendar.YEAR) <= 1912) { log.debug("Pasando al ao 2000 {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, 100); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas.createRow(fechasRow++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } cont++; } currentSession().flush(); query = currentSession().createQuery( "select new Activo(a.id, a.descripcion, a.fechaCompra, a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo) from Activo a where a.fechaCompra > :fechaCompra order by a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo"); query.setDate("fechaCompra", new Date()); activos = query.list(); for (Activo activo : activos) { Calendar cal1 = Calendar.getInstance(); cal1.setTime(activo.getFechaCompra()); if (cal1.get(Calendar.YEAR) < 2020) { log.debug("Quitandole 10 anios {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, -10); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas2.createRow(fechas2Row++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } else if (cal1.get(Calendar.YEAR) >= 2020) { log.debug("Pasando al ao 1900 {} - {}", activo.getDescripcion(), activo.getFechaCompra()); cal1.add(Calendar.YEAR, -100); update.setDate("fechaCompra", cal1.getTime()); update.setLong("id", activo.getId()); update.executeUpdate(); XSSFRow renglon = fechas2.createRow(fechas2Row++); renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta()); renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta()); renglon.createCell(2).setCellValue(activo.getCodigo()); renglon.createCell(3).setCellValue(activo.getDescripcion()); renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra())); Cell cell = renglon.createCell(5); cell.setCellValue(cal1.getTime()); cell.setCellStyle(cellStyle); } cont++; } currentSession().flush(); tx.commit(); log.debug("Termino actualizando {} de {} en {}", new Object[] { cont, activos.size(), ((new Date().getTime() - inicio.getTime()) / 1000) }); wb.write(out); } catch (ParseException | HibernateException | IOException e) { log.error("No se pudieron arreglar las fechas de los activos", e); tx.rollback(); throw new RuntimeException("No se pudieron arreglar las fechas de los actios", e); } }
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@Override @SuppressWarnings("unchecked") public void sube(byte[] datos, Usuario usuario, OutputStream out, Integer codigoInicial) { Date inicio = new Date(); int idx = 5;//w w w . j a v a 2s . co m int i = 0; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat sdf2 = new SimpleDateFormat("dd/MM/yy"); SimpleDateFormat sdf3 = new SimpleDateFormat("dd-MM-yy"); MathContext mc = new MathContext(16, RoundingMode.HALF_UP); NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(0); nf.setMinimumIntegerDigits(5); Transaction tx = null; try { String ejercicioId = "001-2013"; Map<String, CentroCosto> centrosDeCosto = new HashMap<>(); Map<String, TipoActivo> tipos = new HashMap<>(); Query tipoActivoQuery = currentSession() .createQuery("select ta from TipoActivo ta " + "where ta.empresa.id = :empresaId " + "and ta.cuenta.id.ejercicio.id.idEjercicio = :ejercicioId " + "and ta.cuenta.id.ejercicio.id.organizacion.id = :organizacionId"); log.debug("empresaId: {}", usuario.getEmpresa().getId()); log.debug("ejercicioId: {}", ejercicioId); log.debug("organizacionId: {}", usuario.getEmpresa().getOrganizacion().getId()); tipoActivoQuery.setLong("empresaId", usuario.getEmpresa().getId()); tipoActivoQuery.setString("ejercicioId", ejercicioId); tipoActivoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); List<TipoActivo> listaTipos = tipoActivoQuery.list(); for (TipoActivo tipoActivo : listaTipos) { tipos.put(tipoActivo.getCuenta().getId().getIdCtaMayor(), tipoActivo); } log.debug("TIPOS: {}", tipos); Query proveedorQuery = currentSession().createQuery( "select p from Proveedor p where p.empresa.id = :empresaId and p.nombre = :nombreEmpresa"); proveedorQuery.setLong("empresaId", usuario.getEmpresa().getId()); proveedorQuery.setString("nombreEmpresa", usuario.getEmpresa().getNombre()); Proveedor proveedor = (Proveedor) proveedorQuery.uniqueResult(); Query codigoDuplicadoQuery = currentSession() .createQuery("select a from Activo a where a.empresa.id = :empresaId and a.codigo = :codigo"); XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(datos)); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ccostoFantasma = wb.createSheet("CCOSTO-FANTASMAS"); int ccostoFantasmaRow = 0; XSSFSheet sinCCosto = wb.createSheet("SIN-CCOSTO"); int sinCCostoRow = 0; XSSFSheet codigoAsignado = wb.createSheet("CODIGO-ASIGNADO"); int codigoAsignadoRow = 0; XSSFSheet fechaInvalida = wb.createSheet("FECHA-INVALIDA"); int fechaInvalidaRow = 0; XSSFSheet sinCosto = wb.createSheet("SIN-COSTO"); int sinCostoRow = 0; //tx = currentSession().beginTransaction(); for (idx = 5; idx <= 5; idx++) { XSSFSheet sheet = workbook.getSheetAt(idx); int rows = sheet.getPhysicalNumberOfRows(); for (i = 2; i < rows; i++) { log.debug("Leyendo pagina {} renglon {}", idx, i); XSSFRow row = sheet.getRow(i); if (row.getCell(0) == null) { break; } String nombreGrupo = row.getCell(0).getStringCellValue().trim(); switch (row.getCell(0).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: nombreGrupo = row.getCell(0).toString().trim(); break; case XSSFCell.CELL_TYPE_STRING: nombreGrupo = row.getCell(0).getStringCellValue().trim(); break; } TipoActivo tipoActivo = tipos.get(nombreGrupo); if (tipoActivo != null) { String cuentaCCosto = row.getCell(2).toString().trim(); if (StringUtils.isNotBlank(cuentaCCosto)) { CentroCosto centroCosto = centrosDeCosto.get(cuentaCCosto); if (centroCosto == null) { Query ccostoQuery = currentSession().createQuery("select cc from CentroCosto cc " + "where cc.id.ejercicio.id.idEjercicio = :ejercicioId " + "and cc.id.ejercicio.id.organizacion.id = :organizacionId " + "and cc.id.idCosto like :idCosto"); ccostoQuery.setString("ejercicioId", ejercicioId); ccostoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); ccostoQuery.setString("idCosto", "1.01." + cuentaCCosto); ccostoQuery.setMaxResults(1); List<CentroCosto> listaCCosto = ccostoQuery.list(); if (listaCCosto != null && listaCCosto.size() > 0) { centroCosto = listaCCosto.get(0); } if (centroCosto == null) { XSSFRow renglon = ccostoFantasma.createRow(ccostoFantasmaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } centrosDeCosto.put(cuentaCCosto, centroCosto); } String poliza = null; switch (row.getCell(4).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: poliza = row.getCell(4).toString(); poliza = StringUtils.removeEnd(poliza, ".0"); log.debug("POLIZA-N: {}", poliza); break; case XSSFCell.CELL_TYPE_STRING: poliza = row.getCell(4).getStringCellValue().trim(); log.debug("POLIZA-S: {}", poliza); break; } Boolean seguro = false; if (row.getCell(5) != null && StringUtils.isNotBlank(row.getCell(5).toString())) { seguro = true; } Boolean garantia = false; if (row.getCell(6) != null && StringUtils.isNotBlank(row.getCell(6).toString())) { garantia = true; } Date fechaCompra = null; if (row.getCell(7) != null) { log.debug("VALIDANDO FECHA"); XSSFCell cell = row.getCell(7); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: log.debug("ES NUMERIC"); if (DateUtil.isCellDateFormatted(cell)) { log.debug("ES FECHA"); fechaCompra = cell.getDateCellValue(); } else if (DateUtil.isCellInternalDateFormatted(cell)) { log.debug("ES FECHA INTERNAL"); fechaCompra = cell.getDateCellValue(); } else { BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); bd = stripTrailingZeros(bd); log.debug("CONVIRTIENDO DOUBLE {} - {}", DateUtil.isValidExcelDate(bd.doubleValue()), bd); fechaCompra = HSSFDateUtil.getJavaDate(bd.longValue(), true); log.debug("Cal: {}", fechaCompra); } break; case Cell.CELL_TYPE_FORMULA: log.debug("ES FORMULA"); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { fechaCompra = DateUtil.getJavaDate(cellValue.getNumberValue(), true); } } } } if (row.getCell(7) != null && fechaCompra == null) { String fechaCompraString; if (row.getCell(7).getCellType() == Cell.CELL_TYPE_STRING) { fechaCompraString = row.getCell(7).getStringCellValue(); } else { fechaCompraString = row.getCell(7).toString().trim(); } try { fechaCompra = sdf.parse(fechaCompraString); } catch (ParseException e) { try { fechaCompra = sdf2.parse(fechaCompraString); } catch (ParseException e2) { try { fechaCompra = sdf3.parse(fechaCompraString); } catch (ParseException e3) { // no se pudo convertir } } } } if (fechaCompra == null) { XSSFRow renglon = fechaInvalida.createRow(fechaInvalidaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } String codigo = null; switch (row.getCell(8).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: codigo = row.getCell(8).toString(); break; case XSSFCell.CELL_TYPE_STRING: codigo = row.getCell(8).getStringCellValue().trim(); break; } if (StringUtils.isBlank(codigo)) { codigo = "SIN CODIGO" + nf.format(codigoInicial); XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue("SIN CODIGO" + codigoInicial); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigoInicial++; } else { // busca codigo duplicado if (codigo.contains(".")) { codigo = codigo.substring(0, codigo.lastIndexOf(".")); log.debug("CODIGO: {}", codigo); } codigoDuplicadoQuery.setLong("empresaId", usuario.getEmpresa().getId()); codigoDuplicadoQuery.setString("codigo", codigo); Activo activo = (Activo) codigoDuplicadoQuery.uniqueResult(); if (activo != null) { XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9) .setCellValue(codigo + "-" + "SIN CODIGO" + nf.format(codigoInicial)); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigo = "SIN CODIGO" + nf.format(codigoInicial); codigoInicial++; } } String descripcion = null; if (row.getCell(9) != null) { switch (row.getCell(9).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: descripcion = row.getCell(9).toString(); descripcion = StringUtils.removeEnd(descripcion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: descripcion = row.getCell(9).getStringCellValue().trim(); break; default: descripcion = row.getCell(9).toString().trim(); } } String marca = null; if (row.getCell(10) != null) { switch (row.getCell(10).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: marca = row.getCell(10).toString(); marca = StringUtils.removeEnd(marca, ".0"); break; case XSSFCell.CELL_TYPE_STRING: marca = row.getCell(10).getStringCellValue().trim(); break; default: marca = row.getCell(10).toString().trim(); } } String modelo = null; if (row.getCell(11) != null) { switch (row.getCell(11).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: modelo = row.getCell(11).toString(); modelo = StringUtils.removeEnd(modelo, ".0"); break; case XSSFCell.CELL_TYPE_STRING: modelo = row.getCell(11).getStringCellValue().trim(); break; default: modelo = row.getCell(11).toString().trim(); } } String serie = null; if (row.getCell(12) != null) { switch (row.getCell(12).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: serie = row.getCell(12).toString(); serie = StringUtils.removeEnd(serie, ".0"); break; case XSSFCell.CELL_TYPE_STRING: serie = row.getCell(12).getStringCellValue().trim(); break; default: serie = row.getCell(12).toString().trim(); } } String responsable = null; if (row.getCell(13) != null) { switch (row.getCell(13).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: responsable = row.getCell(13).toString(); responsable = StringUtils.removeEnd(responsable, ".0"); break; case XSSFCell.CELL_TYPE_STRING: responsable = row.getCell(13).getStringCellValue().trim(); break; default: responsable = row.getCell(13).toString().trim(); } } String ubicacion = null; if (row.getCell(14) != null) { switch (row.getCell(14).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: ubicacion = row.getCell(14).toString(); ubicacion = StringUtils.removeEnd(ubicacion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: ubicacion = row.getCell(14).getStringCellValue().trim(); break; default: ubicacion = row.getCell(14).toString().trim(); } } BigDecimal costo = null; switch (row.getCell(15).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: costo = new BigDecimal(row.getCell(15).getNumericCellValue(), mc); log.debug("COSTO-N: {} - {}", costo, row.getCell(15).getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: costo = new BigDecimal(row.getCell(15).toString(), mc); log.debug("COSTO-S: {} - {}", costo, row.getCell(15).toString()); break; case XSSFCell.CELL_TYPE_FORMULA: costo = new BigDecimal( evaluator.evaluateInCell(row.getCell(15)).getNumericCellValue(), mc); log.debug("COSTO-F: {}", costo); } if (costo == null) { XSSFRow renglon = sinCosto.createRow(sinCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } Activo activo = new Activo(fechaCompra, seguro, garantia, poliza, codigo, descripcion, marca, modelo, serie, responsable, ubicacion, costo, tipoActivo, centroCosto, proveedor, usuario.getEmpresa()); this.crea(activo, usuario); } else { XSSFRow renglon = sinCCosto.createRow(sinCCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } } else { throw new RuntimeException( "(" + idx + ":" + i + ") No se encontro el tipo de activo " + nombreGrupo); } } } //tx.commit(); log.debug("################################################"); log.debug("################################################"); log.debug("TERMINO EN {} MINS", (new Date().getTime() - inicio.getTime()) / (1000 * 60)); log.debug("################################################"); log.debug("################################################"); wb.write(out); } catch (IOException | RuntimeException e) { //if (tx != null && tx.isActive()) { //tx.rollback(); //} log.error("Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); throw new RuntimeException( "Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); } }
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@Override @Transactional(readOnly = true)/*from ww w . ja v a 2 s . c om*/ public void hojaCalculoDepreciacion(Map<String, Object> params) { try { log.debug("Creando excel con depreciacion"); XSSFWorkbook wb = new XSSFWorkbook(); int rows = 0; if (params.containsKey("centrosDeCosto")) { XSSFSheet sheet = wb.createSheet("CentrosDeCosto"); TreeSet<String> cuentas = null; Collection<Map<String, Object>> centrosDeCosto = (Collection<Map<String, Object>>) params .get("centrosDeCosto"); for (Map<String, Object> centroCosto : centrosDeCosto) { Map<String, Object> totales = (Map<String, Object>) centroCosto.get("totales"); if (rows == 0) { XSSFRow row = sheet.createRow(rows++); int cols = 0; row.createCell(cols++).setCellValue("Cuenta"); row.createCell(cols++).setCellValue("Nombre"); cuentas = new TreeSet<>(totales.keySet()); for (String cuenta : cuentas) { row.createCell(cols++).setCellValue(cuenta); } } int cols = 0; XSSFRow row = sheet.createRow(rows++); row.createCell(cols++).setCellValue((String) centroCosto.get("cuenta")); row.createCell(cols++).setCellValue((String) centroCosto.get("nombre")); for (String cuenta : cuentas) { row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) totales.get(cuenta)).doubleValue()); } } int cols = 0; XSSFRow row = sheet.createRow(rows++); row.createCell(cols++); row.createCell(cols++); List<Map<String, Object>> tiposDeActivo = (List<Map<String, Object>>) params.get("tiposDeActivo"); for (Map<String, Object> tipoDeActivo : tiposDeActivo) { row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) tipoDeActivo.get("total")).doubleValue()); } } else { XSSFSheet sheet = wb.createSheet("Grupos"); XSSFRow row = sheet.createRow(rows++); int cols = 0; row.createCell(cols++).setCellValue("CUENTA"); row.createCell(cols++).setCellValue("NOMBRE"); row.createCell(cols++).setCellValue("ACUMULADA"); row.createCell(cols++).setCellValue("MENSUAL"); List<Map<String, Object>> tiposDeActivo = (List<Map<String, Object>>) params.get("tiposDeActivo"); for (Map<String, Object> tipoDeActivo : tiposDeActivo) { cols = 0; row = sheet.createRow(rows++); row.createCell(cols++).setCellValue(tipoDeActivo.get("cuenta").toString()); row.createCell(cols++).setCellValue(tipoDeActivo.get("nombre").toString()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) tipoDeActivo.get("ACUMULADA")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) tipoDeActivo.get("MENSUAL")).doubleValue()); } cols = 0; row = sheet.createRow(rows++); row.createCell(cols++); row.createCell(cols++); Map<String, BigDecimal> totales = (Map<String, BigDecimal>) params.get("totales"); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue((totales.get("ACUMULADA")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC).setCellValue((totales.get("MENSUAL")).doubleValue()); } OutputStream out = (OutputStream) params.get("out"); wb.write(out); } catch (IOException ex) { log.error("No se pudo crear la hoja de calculo", ex); } }
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@Override @Transactional(readOnly = true)// www.j a v a 2 s. c om public void hojaCalculoConcentradoDepreciacion(Map<String, Object> params) { try { log.debug("Creando excel con concentrado depreciacion"); XSSFWorkbook wb = new XSSFWorkbook(); int rows = 0; if (params.containsKey("centrosDeCosto")) { XSSFSheet sheet = wb.createSheet("CentrosDeCosto"); List<Map<String, Object>> tiposDeActivo = (List<Map<String, Object>>) params.get("tiposDeActivo"); Collection<Map<String, Object>> centrosDeCosto = (Collection<Map<String, Object>>) params .get("centrosDeCosto"); for (Map<String, Object> centroCosto : centrosDeCosto) { Map<String, Object> totales = (Map<String, Object>) centroCosto.get("totales"); if (rows == 0) { XSSFRow row = sheet.createRow(rows++); int cols = 0; row.createCell(cols++); row.createCell(cols++); for (Map<String, Object> tipoActivo : tiposDeActivo) { log.debug("Creando encabezado de {}", tipoActivo.get("cuenta")); row.createCell(cols++).setCellValue((String) tipoActivo.get("cuenta")); row.createCell(cols++).setCellValue((String) tipoActivo.get("nombre")); row.createCell(cols++); row.createCell(cols++); row.createCell(cols++); row.createCell(cols++); } row = sheet.createRow(rows++); cols = 0; row.createCell(cols++).setCellValue("Cuenta"); row.createCell(cols++).setCellValue("Nombre"); for (Map<String, Object> tipoActivo : tiposDeActivo) { log.debug("Creando columnas para {}", tipoActivo.get("cuenta")); row.createCell(cols++).setCellValue("Costo"); row.createCell(cols++).setCellValue("Depreciacin Ao"); row.createCell(cols++).setCellValue("Depreciacion Anual"); row.createCell(cols++).setCellValue("Depreciacin Mensual"); row.createCell(cols++).setCellValue("Depreciacin Acumulada"); row.createCell(cols++).setCellValue("Valor Neto"); } } int cols = 0; XSSFRow row = sheet.createRow(rows++); row.createCell(cols++).setCellValue((String) centroCosto.get("cuenta")); row.createCell(cols++).setCellValue((String) centroCosto.get("nombre")); for (Map<String, Object> tipoActivo : tiposDeActivo) { Map<String, BigDecimal> valores = (Map<String, BigDecimal>) totales .get((String) tipoActivo.get("cuenta")); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue((valores.get("costo")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC).setCellValue(0d); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue((valores.get("anual")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue((valores.get("mensual")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue((valores.get("acumulada")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue((valores.get("valorNeto")).doubleValue()); } } int cols = 0; XSSFRow row = sheet.createRow(rows++); row.createCell(cols++); row.createCell(cols++).setCellValue("TOTAL"); for (Map<String, Object> tipoDeActivo : tiposDeActivo) { row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) tipoDeActivo.get("costo")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC).setCellValue(0d); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) tipoDeActivo.get("anual")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) tipoDeActivo.get("mensual")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) tipoDeActivo.get("acumulada")).doubleValue()); row.createCell(cols++, Cell.CELL_TYPE_NUMERIC) .setCellValue(((BigDecimal) tipoDeActivo.get("valorNeto")).doubleValue()); } } OutputStream out = (OutputStream) params.get("out"); wb.write(out); } catch (IOException ex) { log.error("No se pudo crear la hoja de calculo", ex); } }