List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write
@SuppressWarnings("resource") public final void write(OutputStream stream) throws IOException
From source file:DSC.PackerReport.java
private static void createSpreadsheets() { orderList.sort(new Comparator<Order>() { @Override//from w ww .ja v a 2 s.c o m public int compare(Order o1, Order o2) { int result; if (o1.getFamilySize() < o2.getFamilySize()) { result = -1; } else if (o1.getFamilySize() == o2.getFamilySize()) { result = 0; } else { result = 1; } return result; } }); XSSFWorkbook workbook = new XSSFWorkbook(); for (Route route : routeList) { XSSFSheet sheet = workbook.createSheet("PackerReports Route - " + route.getID()); Map<String, String[]> data = new TreeMap<>(); data.put("1", new String[] { "Doorstep Chef Packer Sheet", "", "", route.getDrivers().get(0).getDriver().getDriverName().split(" ")[0] + " - " + route.getDrivers().get(0).getDriver().getContactNumber(), "", " Week: " + DriverReport.returnWeekInt() + " Route: " + route.getID() }); data.put("2", new String[] { "", "", "", "", "", "" }); data.put("3", new String[] { "Customer", "FamSize", "MealType", "Qty", "Allergies", "Exclutions" }); int[] totals = new int[11]; int counter = 4; for (Order order : orderList) { if (order.getRoute().equals(route.getID())) { Client client = order.getClient(); String customer = client.getName() + " " + client.getSurname(); String famSize = order.getFamilySize() + ""; for (Meal meal : order.getMeals()) { data.put(counter + "", new String[] { customer, famSize, meal.getMealType(), meal.getQuantity() + "", meal.getAllergies(), meal.getExclusions() }); customer = ""; famSize = ""; counter++; if (meal.getMealType().equals("Standard")) { totals[1] += meal.getQuantity(); } else if (meal.getMealType().equals("Low Carb")) { totals[2] += meal.getQuantity(); } else if (meal.getMealType().equals("Kiddies")) { totals[3] += meal.getQuantity(); } switch (meal.getQuantity()) { case 1: totals[4]++; break; case 2: totals[5]++; break; case 3: totals[6]++; break; case 4: totals[7]++; break; case 5: totals[8]++; break; case 6: totals[9]++; break; default: if (meal.getQuantity() > 6) { totals[10]++; } } } totals[0]++; } } Set<String> keySet = data.keySet(); int totalSize = 22000; int longestCustomer = 0; for (int key = 1; key < keySet.size() + 1; key++) { Row row = sheet.createRow(key - 1); String[] arr = data.get(key + ""); for (int i = 0; i < arr.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(arr[i]); XSSFCellStyle borderStyle = workbook.createCellStyle(); if (i == 0 && !(key + "").equals("1") && longestCustomer < ((String) arr[i]).length()) { longestCustomer = ((String) arr[i]).length(); } if (!((key + "").equals("1") || (key + "").equals("2"))) { borderStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); borderStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); if ((key + "").equals("3")) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); borderStyle.setAlignment(HorizontalAlignment.CENTER); borderStyle.setFillPattern(XSSFCellStyle.LESS_DOTS); borderStyle.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); XSSFFont font = workbook.createFont(); font.setColor(IndexedColors.WHITE.getIndex()); font.setBold(true); borderStyle.setFont(font); } else { if (i != 0) { borderStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); } if (i != 5) { borderStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); } if (i == 5 || i == 4) { borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY); } if ((Integer.parseInt((key + ""))) != keySet.size()) { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); } else { borderStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); } borderStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); } if (i == 3 || i == 1) { borderStyle.setAlignment(HorizontalAlignment.CENTER); } } else { if (key != 3 && (i == 4 || i == 5)) { borderStyle.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY); borderStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY); } if (i == 3) { borderStyle.setAlignment(HorizontalAlignment.CENTER); } else if (i == 5) { borderStyle.setAlignment(HorizontalAlignment.RIGHT); } XSSFFont font = workbook.createFont(); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 13); font.setBold(true); borderStyle.setFont(font); } cell.setCellStyle(borderStyle); } } //<editor-fold defaultstate="collapsed" desc="Add Totals"> Row row = sheet.createRow(keySet.size()); Cell cell1 = row.createCell(0); cell1.setCellValue("Clients: " + totals[0]); XSSFCellStyle cellStyle1 = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); font.setBold(true); cellStyle1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); cellStyle1.setFont(font); cell1.setCellStyle(cellStyle1); Cell cell2 = row.createCell(1); cell2.setCellValue("Standard: " + totals[1]); XSSFCellStyle cellStyle2 = workbook.createCellStyle(); font.setBold(true); cellStyle2.setFont(font); cell2.setCellStyle(cellStyle2); Cell cell3 = row.createCell(4); cell3.setCellValue("Low Carb: " + totals[2]); XSSFCellStyle cellStyle3 = workbook.createCellStyle(); font.setBold(true); cellStyle3.setFont(font); cell3.setCellStyle(cellStyle3); Cell cell4 = row.createCell(5); cell4.setCellValue("Kiddies: " + totals[3]); XSSFCellStyle cellStyle4 = workbook.createCellStyle(); font.setBold(true); cellStyle4.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle4.setFont(font); cell4.setCellStyle(cellStyle4); row = sheet.createRow(keySet.size() + 1); Cell holder = row.createCell(0); XSSFCellStyle border1 = workbook.createCellStyle(); border1.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); holder.setCellStyle(border1); cell2 = row.createCell(1); cell2.setCellValue("Single: " + totals[4]); XSSFCellStyle cellStyle6 = workbook.createCellStyle(); font.setBold(true); cellStyle6.setFont(font); cell2.setCellStyle(cellStyle6); cell3 = row.createCell(4); cell3.setCellValue("Couple: " + totals[5]); XSSFCellStyle cellStyle7 = workbook.createCellStyle(); font.setBold(true); cellStyle7.setFont(font); cell3.setCellStyle(cellStyle7); cell4 = row.createCell(5); cell4.setCellValue("Small(3): " + totals[6]); XSSFCellStyle cellStyle8 = workbook.createCellStyle(); font.setBold(true); cellStyle8.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle8.setFont(font); cell4.setCellStyle(cellStyle8); row = sheet.createRow(keySet.size() + 2); Cell holder2 = row.createCell(0); XSSFCellStyle border2 = workbook.createCellStyle(); border2.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM); border2.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder2.setCellStyle(border2); cell2 = row.createCell(1); cell2.setCellValue("Medium(4): " + totals[7]); XSSFCellStyle cellStyle9 = workbook.createCellStyle(); font.setBold(true); cellStyle9.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle9.setFont(font); cell2.setCellStyle(cellStyle9); Cell holder3 = row.createCell(2); XSSFCellStyle border3 = workbook.createCellStyle(); border3.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder3.setCellStyle(border3); Cell holder4 = row.createCell(3); XSSFCellStyle border4 = workbook.createCellStyle(); border4.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); holder4.setCellStyle(border4); cell3 = row.createCell(4); cell3.setCellValue("Large(5): " + totals[8]); XSSFCellStyle cellStyle10 = workbook.createCellStyle(); font.setBold(true); cellStyle10.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle10.setFont(font); cell3.setCellStyle(cellStyle10); cell4 = row.createCell(5); cell4.setCellValue("XLarge(6): " + totals[9]); XSSFCellStyle cellStyle11 = workbook.createCellStyle(); font.setBold(true); cellStyle11.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); cellStyle11.setBorderRight(XSSFCellStyle.BORDER_MEDIUM); cellStyle11.setFont(font); cell4.setCellStyle(cellStyle11); //</editor-fold> sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 4)); sheet.addMergedRegion(new CellRangeAddress(keySet.size(), keySet.size(), 1, 3)); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 1, keySet.size() + 1, 1, 3)); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 2, keySet.size() + 2, 1, 3)); sheet.setColumnWidth(0, (longestCustomer + 1) * 240); sheet.setColumnWidth(1, 8 * 240); sheet.setColumnWidth(2, 10 * 240); sheet.setColumnWidth(3, 4 * 240); int usedSize = 0; for (int i = 0; i <= 3; i++) { usedSize += sheet.getColumnWidth(i); } sheet.setColumnWidth(4, (totalSize - usedSize) / 2); sheet.setColumnWidth(5, (totalSize - usedSize) / 2); Row rowDate = sheet.createRow(keySet.size() + 4); Cell cell = rowDate.createCell(0); SimpleDateFormat sf = new SimpleDateFormat("EEE MMM yyyy HH:mm:ss"); cell.setCellValue(sf.format(Calendar.getInstance().getTime())); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT); cell.setCellStyle(cellStyle); sheet.addMergedRegion(new CellRangeAddress(keySet.size() + 4, keySet.size() + 4, 0, 5)); } try { workbook.write(excelOut); excelOut.close(); System.out.println("Done - Packer"); if (DSC_Main.generateAllReports) { DSC_Main.reportsDone++; if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) { DSC_Main.reportsDone(); } } else { packerLoadingObj.setVisible(false); packerLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "PackerReport Succesfully Generated", "Success", JOptionPane.INFORMATION_MESSAGE); } } catch (IOException io) { packerLoadingObj.setVisible(false); packerLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "An error occured\nCould not create PackerReport", "Error", JOptionPane.ERROR_MESSAGE); System.err.println("Error - Could not create new PackerReport: "); io.printStackTrace(); } }
From source file:DSC.QuantityReport.java
private static void creatSheet(String mealType, XSSFWorkbook workbook) throws IOException { FileOutputStream excelOut = null; try {/*w w w .j av a 2 s . co m*/ Path path = Paths.get( "Reports\\Week " + DriverReport.returnWeekInt() + " (" + DriverReport.returnWeekString() + ")"); Files.createDirectories(path); File file = path.resolve("QuantityReport Week - " + returnWeekInt() + ".xlsx").toFile(); if (!file.exists()) { file.createNewFile(); } excelOut = new FileOutputStream(file); workbook.write(excelOut); excelOut.close(); System.out.println("Done - Quantity"); if (DSC_Main.generateAllReports) { DSC_Main.reportsDone++; if (DSC_Main.reportsDone == DSC_Main.TOTAL_REPORTS) { DSC_Main.reportsDone(); } } else { quantityLoadingObj.setVisible(false); quantityLoadingObj.dispose(); JOptionPane.showMessageDialog(null, "QuantityReport Succesfully Generated", "Success", JOptionPane.INFORMATION_MESSAGE); } } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "File is Currently being Used. Please Close the File."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-2] ? : ? ? ? ? ?// www . j a v a 2 s. co m */ @Test public void testModifyCellContents() throws Exception { try { String content = "Use \n with word wrap on to create a new line"; short rownum = 2; int cellnum = 2; LOGGER.debug("testModifyCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx"); if (!EgovFileUtil.isExistsFile(sb.toString())) { Workbook wbT = new XSSFWorkbook(); wbT.createSheet(); // ? ? excelService.createWorkbook(wbT, sb.toString()); } // ? XSSFWorkbook wb = null; wb = excelService.loadWorkbook(sb.toString(), wb); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.getSheetAt(0); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); //Word Wrap MUST be turned on cs.setWrapText(true); Row row = sheet.createRow(rownum); row.setHeight((short) 0x349); Cell cellx = row.createCell(cellnum); cellx.setCellType(XSSFCell.CELL_TYPE_STRING); cellx.setCellValue(new XSSFRichTextString(content)); cellx.setCellStyle(cs); sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20))); //excelService.writeWorkbook(wb); FileOutputStream outx = new FileOutputStream(sb.toString()); wb.write(outx); outx.close(); // ? Workbook wb1 = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheet1 = wb1.getSheetAt(0); Row row1 = sheet1.getRow(rownum); Cell cell1 = row1.getCell(cellnum); // ? ? ? LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue()); LOGGER.debug("cont ###{}###", content); assertNotSame("TEST", cell1.getRichStringCellValue().toString()); assertEquals(content, cell1.getRichStringCellValue().toString()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellContents end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? /*from www .ja va 2s . co m*/ */ @Test public void testModifyCellAttribute() throws Exception { try { LOGGER.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? XSSFWorkbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); LOGGER.debug("testModifyCellAttribute after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet2"); sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontHeight(16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); row.setHeight((short) 300); // row? height for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? XSSFWorkbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("cell test sheet2"); LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles()); XSSFCellStyle cs1 = (XSSFCellStyle) wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); XSSFFont fontT = cs1.getFont(); LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight()); LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight()); LOGGER.debug("font getFontName : {}", fontT.getFontName()); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); LOGGER.debug("getWrapText : {}", cs1.getWrapText()); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue()); assertEquals(320, fontT.getFontHeight()); assertEquals(400, fontT.getBoldweight()); LOGGER.debug("fontT.getBoldweight()? ? 400? ?"); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.util.AbstractPOIExcelView.java
License:Apache License
/** * Renders the Excel view, given the specified model. */// w w w .j a v a 2s .c om @Override protected final void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); LOGGER.debug("Created Excel Workbook from scratch"); setContentType(CONTENT_TYPE_XLSX); buildExcelDocument(model, workbook, request, response); // Set the filename String sFilename = ""; if (model.get("filename") != null) { sFilename = (String) model.get("filename"); } else if (request.getAttribute("filename") != null) { sFilename = (String) request.getAttribute("filename"); } else { sFilename = getClass().getSimpleName(); } // Set the content type. response.setContentType(getContentType()); response.setHeader("Content-Disposition", "attachment; filename=\"" + sFilename + ".xlsx\""); // Flush byte array to servlet output stream. ServletOutputStream out = response.getOutputStream(); out.flush(); workbook.write(out); out.flush(); // Don't close the stream - we didn't open it, so let the container handle it. // http://stackoverflow.com/questions/1829784/should-i-close-the-servlet-outputstream }
From source file:Ekon.PanelVypisFirem.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Zamestnanci"); Map<String, Object[]> data = new TreeMap<>(); data.put("1", new Object[] { "Nazev Firmy", "Mesto", "Ulice", "Kontakt", "Email", "ICO", "DICO", "Kraj", "PSC" }); int i = 2;//from ww w . j av a 2 s .co m try { for (Iterator it = vytvorFirmy.dejIterator(); it.hasNext();) { Firma f = (Firma) it.next(); data.put(String.valueOf(i++), new Object[] { f.getNazevFirmy(), f.getMesto(), f.getUlice(), f.getKontakt(), f.getEmail(), f.getIco(), f.getDico(), f.getKraj(), f.getPsc() }); } Set<String> keySet = data.keySet(); int rowNum = 0; for (String key : keySet) { Row row = sheet.createRow(rowNum++); Object[] objArr = data.get(key); int cellNum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellNum++); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Integer) { cell.setCellValue((Integer) obj); } } } JFileChooser fch = new JFileChooser(); FileNameExtensionFilter filter = new FileNameExtensionFilter("objects xls xlsx", "xls", "xlsx"); fch.setFileFilter(filter); int returnVal = fch.showSaveDialog(this); File nazevSouboru = fch.getSelectedFile(); FileOutputStream out = new FileOutputStream(nazevSouboru.getAbsolutePath()); workbook.write(out); out.close(); JOptionPane.showMessageDialog(this, "Sobour XLS/XLSX vytvoren", "Informace", 1); } catch (Exception e) { JOptionPane.showMessageDialog(this, "Chyba !", "Error", 1); } }
From source file:Ekon.PanelVypisZamestnancu.java
private void btnUlozXLSActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnUlozXLSActionPerformed XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Zamestnanci"); Map<String, Object[]> data = new TreeMap<>(); data.put("1", new Object[] { "Jmeno", "Prijmeni", "Mesto", "Ulice", "PSC", "Kraj", "Titul", "Datum narozeni", "Email", "Telefonni cislo", "Rodne cislo", "Narodnost", "Pozice" }); int i = 2;/*ww w.j a v a 2 s . c om*/ for (Iterator it = aktualniFirma.dejIteratorZamestnancu(); it.hasNext();) { Zamestnanec z = (Zamestnanec) it.next(); data.put(String.valueOf(i++), new Object[] { z.getJmeno(), z.getPrijmeni(), z.getMesto(), z.getUlice(), z.getPsc(), z.getKraj(), z.getTitul(), z.getDatumNarozeni(), z.getEmail(), z.getTelefoniCislo(), z.getRodneCislo(), z.getNarodnost(), z.getPozice() }); } Set<String> keySet = data.keySet(); int rowNum = 0; for (String key : keySet) { Row row = sheet.createRow(rowNum++); Object[] objArr = data.get(key); int cellNum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellNum++); if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Integer) { cell.setCellValue((Integer) obj); } } } JFileChooser fch = new JFileChooser(); FileNameExtensionFilter filter = new FileNameExtensionFilter("objects xls xlsx", "xls", "xlsx"); fch.setFileFilter(filter); int returnVal = fch.showSaveDialog(this); try { File nazevSouboru = fch.getSelectedFile(); FileOutputStream out = new FileOutputStream(nazevSouboru.getAbsolutePath()); workbook.write(out); out.close(); JOptionPane.showMessageDialog(this, "Sobour XLS/XLSX vytvoren", "Informace", 1); } catch (Exception e) { } }
From source file:es.SSII2.manager.ExcelManagerAccount.java
public void actualizarCuentas(ArrayList<String> cuentas, ArrayList<String> cuentasCorrectas, ArrayList<String> arrayIban, ArrayList<String> pos) throws IOException, ParseException { int row, col; String originalCuenta, nuevaCuenta, iban, posicion, entidad, oficina, dc, numCuenta; String[] a;/* w w w. j a v a 2 s . c o m*/ FileInputStream file; file = new FileInputStream(new File(excel)); FileOutputStream outFile; XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow rowIban; XSSFCell cellIban; Cell cellCuenta; //sacar las cuentas for (int i = 0; i < cuentas.size(); i++) { originalCuenta = cuentas.get(i);//se coje la cuenta original nuevaCuenta = cuentasCorrectas.get(i);//se coje la cuenta actualizada iban = arrayIban.get(i);// cojer el iban posicion = pos.get(i);//se coje la posicion a = posicion.split("-");//split del string "1-3" de las posiciones que estan el el arraylist row = Integer.parseInt(a[0]);//fila "1" col = Integer.parseInt(a[1]);//columna "3" //insertar el iban rowIban = sheet.getRow(row);//coje la fila cellIban = rowIban.createCell(1 + col);//crea la celda cellIban.setCellValue(iban); //2096 0056 16 3231500000 entidad = nuevaCuenta.substring(0, 4); oficina = nuevaCuenta.substring(4, 8); dc = nuevaCuenta.substring(8, 10); numCuenta = nuevaCuenta.substring(10); //actualizar la cuenta si esta mal el cc if (!originalCuenta.equals(nuevaCuenta)) { cellCuenta = sheet.getRow(row).getCell(col); //obtiene la fila y columna DecimalFormat df = new DecimalFormat("#"); Number cuenta = df.parse(nuevaCuenta); cellCuenta.setCellValue(cuenta.doubleValue()); System.out.println( "Cuenta actualizada: " + iban + "-" + entidad + "-" + oficina + "-" + dc + "-" + numCuenta); } else { System.out.println( "Cuenta correcta: " + iban + "-" + entidad + "-" + oficina + "-" + dc + "-" + numCuenta); } } //for outFile = new FileOutputStream(new File(excel)); //escribe en el excel workbook.write(outFile); outFile.close(); file.close();//cierra el archivo }
From source file:es.SSII2.manager.ExcelManagerMail.java
public void actualizarDnis() throws IOException { int row;/*from ww w. j a va2 s .com*/ int col = 15; FileInputStream file; file = new FileInputStream(new File(excel)); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); for (int i = 0; i < arrayWorkers.size(); i++) { row = i; XSSFRow rowEmail = sheet.getRow(row + 1); //coje la fila XSSFCell cellEmail = rowEmail.createCell(col); //crea la celda cellEmail.setCellValue(arrayWorkers.get(i).getEmail()); //escribe en el excel try (FileOutputStream outFile = new FileOutputStream(new File(excel))) { workbook.write(outFile); } } //for }
From source file:es.tena.foundation.util.POIUtil.java
public static void generateXLS(String tabla, String filename, Connection conn, String encoding) throws SQLException { String query = ""; try {/*from www. j a va2 s . c o m*/ query = "SELECT * FROM (" + tabla + ")"; PreparedStatement stmt = conn.prepareStatement(query); ResultSet rset = stmt.executeQuery(); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(filename); String sheetRef = sheet.getPackagePart().getPartName().getName(); String template = "c:\\temp\\template_" + filename + ".xlsx"; FileOutputStream os = new FileOutputStream(template); wb.write(os); os.close(); File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), encoding); generate(fw, rset, encoding); rset.close(); stmt.close(); fw.close(); FileOutputStream out = new FileOutputStream( "c:\\temp\\" + filename + sdf.format(calendario.getTime()) + ".xlsx"); FileUtil.substitute(new File(template), tmp, sheetRef.substring(1), out); out.close(); Logger.getLogger(POIUtil.class.getName()).log(Level.INFO, "Creado con exito {0}", filename); } catch (Exception ex) { ex.printStackTrace(); Logger.getLogger(POIUtil.class.getName()).log(Level.SEVERE, null, query + "\n" + ex); System.out.println(query); } finally { conn.close(); } }