List of usage examples for org.apache.poi.xssf.usermodel XSSFFormulaEvaluator evaluateAllFormulaCells
public static void evaluateAllFormulaCells(XSSFWorkbook wb)
From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java
public void saveChangesAndClose(String fileName) { try {/*w w w . j a v a 2s . c om*/ // First refresh all calculated formulas and cells XSSFFormulaEvaluator.evaluateAllFormulaCells(this.book); // Set the date to today save and close Date myDate = new Date(); XSSFCell myCell = book.getSheetAt(1).getRow(3).getCell(5); // code that assigns a cell from an HSSFSheet to 'myCell' would go here... myCell.setCellValue(myDate); fsIP.close(); //Close the InputStream FileOutputStream output_file = new FileOutputStream(fileName); //Open FileOutputStream to write updates book.write(output_file); //write changes output_file.close(); //close the stream } catch (Exception e) { e.printStackTrace(); } }
From source file:com.svi.main.logic.ExtractAndPrint.java
private void writeLogFile(List<Nodes> nodesHolder) { DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HHmm"); Date date = new Date(); Sheet mainSheet;//from ww w .j av a 2 s .c o m Sheet dataSheet; Sheet elementSheet; Row dataSheetRow; Row elementSheetRow; InputStream fis; XSSFWorkbook workbook; File outputPath = new File(logPath + "\\Logs"); File outputFile = new File(logPath + "\\Logs\\BPO KPI Report " + dateFormat.format(date) + ".xlsx"); // File name int dataSheetRowCount = 1; int elementSheetRowCount = 1; int totalElementException = 0; try { if (!Files.exists(outputPath.toPath())) { Files.createDirectories(outputPath.toPath()); } fis = ExtractAndPrint.class.getResourceAsStream("bpo_template.xlsx"); workbook = new XSSFWorkbook(fis); //Style for exception sheet XSSFCellStyle style = workbook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); //Get data Sheet mainSheet = workbook.getSheetAt(0); writeProjectDetails(mainSheet); dataSheet = workbook.getSheetAt(4); dataSheetRow = dataSheet.createRow(0); elementSheet = workbook.getSheetAt(1); elementSheetRow = elementSheet.createRow(0); //Write excel headers writeDataSheetHeaders(dataSheetRow); writeElementSheetHeaders(elementSheetRow); //Set progress bar values progress = new AtomicInteger(0); total = new AtomicInteger(nodesHolder.size()); mf.setJprogressValues(total, progress); // Sort the nodes per Node ID Collections.sort(nodesHolder, new Comparator<Nodes>() { public int compare(Nodes o1, Nodes o2) { return o1.getNodeId().compareTo(o2.getNodeId()); } }); //Write Data Sheet for (Nodes node : nodesHolder) { mf.loader(); dataSheetRow = dataSheet.createRow(dataSheetRowCount++); writeDataSheet(node, dataSheetRow); } for (Nodes node : nodesHolder) { for (Elements e : node) { if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING")) && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) { totalElementException++; } } } progress = new AtomicInteger(0); total = new AtomicInteger(totalElementException); mf.setJprogressValues(total, progress); //Write exception sheet for (Nodes node : nodesHolder) { for (Elements e : node) { if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING")) && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) { elementSheetRow = elementSheet.createRow(elementSheetRowCount++); writeElementSheet(e, elementSheetRow); mf.elementLoader(); } } } XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) workbook); try (FileOutputStream outputStream = new FileOutputStream(outputFile)) { workbook.write(outputStream);//Write the Excel File outputStream.close(); } workbook.close(); fis.close(); mf.generateMessage(); } catch (Exception ex) { Logger.getLogger(ExtractAndPrint.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Logica.L_Exel.java
public String SobreExportar(File archivo, JTable jTable1, String drct) { String a = "revisr"; try {//from ww w . ja v a 2 s . c om FileInputStream entrada = new FileInputStream(new File(drct)); XSSFWorkbook xlsx = new XSSFWorkbook(entrada); XSSFSheet hoja = xlsx.getSheetAt(0); Row fila = null; Cell celda = null; try { fila = hoja.getRow(4); celda = fila.createCell(3); celda.setCellValue(1); } catch (Exception e) { JOptionPane.showMessageDialog(null, e); } XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx); entrada.close(); FileOutputStream sld = new FileOutputStream(new File(drct)); xlsx.write(sld); sld.close(); } catch (Exception e) { JOptionPane.showMessageDialog(null, e); } return a; }
From source file:Logica.L_POIExcel.java
public void Exportar(JTable Tbl_OT_Eventos_MostrandoExportar) throws Exception { JFileChooser selector = new JFileChooser(); selector.setFileFilter(new FileNameExtensionFilter("Excel 2010 or Superior", "xlsx")); //selector.setFileFilter(new FileNameExtensionFilter("Excel with Macrs", "xlsm")); int numfila = Tbl_OT_Eventos_MostrandoExportar.getRowCount(); int numcolumn = Tbl_OT_Eventos_MostrandoExportar.getColumnCount(); String directorio = ""; String extension = ""; boolean seleccion = false; int resultado = selector.showOpenDialog(null); switch (resultado) { case JFileChooser.APPROVE_OPTION: directorio = selector.getSelectedFile().getPath(); int i = directorio.lastIndexOf('.'); if (i >= 0) { extension = directorio.substring(i + 1); }//from www.jav a 2s . co m seleccion = true; JOptionPane.showMessageDialog(null, "Seleccionaste" + directorio); break; case JFileChooser.CANCEL_OPTION: seleccion = false; JOptionPane.showMessageDialog(null, "No seleccionaste un archivo"); break; case JFileChooser.ERROR_OPTION: seleccion = false; JOptionPane.showMessageDialog(null, "Ocurreo un Error"); break; default: break; } if (extension.equals("xlsx")) { FileInputStream entrada = new FileInputStream(new File(directorio)); XSSFWorkbook xlsx = new XSSFWorkbook(entrada); XSSFSheet hoja = xlsx.getSheetAt(0); Row fila = null; Cell celda = null; try { //EN CASO CELDAS TENGAN ALGUN VALOR //PODEMOS MANEJAR DICHO VALOR O SOBREESCRIBIR SOBRE EL COMO EN EL SIGUIENTE CASO //fila = hoja.getRow(0); //celda = fila.getCell(0); //celda.setCellValue(1); //fila = hoja.getRow(7); //celda = fila.getCell(6); //celda.setCellValue(9); for (int i = -1; i < numfila; i++) { //esta linea posiciona donde se empezara a escribir en este caso en la fila 4 //recuerda que tanto fila como columna inician su conteo con 0 fila = hoja.getRow(i + 14); for (int j = 0; j < numcolumn; j++) { celda = fila.getCell(j); if (i == -1) { celda.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getColumnName(j))); } else { celda.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getValueAt(i, j))); } } } } catch (NullPointerException NPE) { //EN CSAO LAS CELDAS ESTE VACIAS //ESCRIBIMOS UN VALOR SOBRE ELLAS for (int i = -1; i < numfila; i++) { Row filaV = hoja.createRow(i + 14); for (int j = 0; j < numcolumn; j++) { Cell celdaV = filaV.createCell(j); if (i == -1) { celdaV.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getColumnName(j))); } else { celdaV.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getValueAt(i, j))); } } } } //EVALUA LAS FORMULAS DEL WORKBK XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx); entrada.close(); //ABRIENDO ARCHIVO PARA ESCRITURA FileOutputStream salida = new FileOutputStream(new File(directorio)); //ESCRIBIENDO DATOS, EN ESTE CASO DE LA TABLA xlsx.write(salida); salida.close(); } }
From source file:Logica.L_POIExcel.java
public void Exportar_To_Mcr() throws Exception { JFileChooser selector = new JFileChooser(); selector.setFileFilter(new FileNameExtensionFilter("Excel 2010 or Superior", "xlsx")); //selector.setFileFilter(new FileNameExtensionFilter("Excel with Macrs", "xlsm")); String sSql = ""; String UPS_Monitoreados = ""; sSql = "SELECT Count(EstadoMonitoreo) FROM upsinformacion_prb WHERE EstadoMonitoreo Like '%MON%'"; try {/*from w ww. j a v a 2 s . c o m*/ Statement st = cn.createStatement(); ResultSet rs = st.executeQuery(sSql); rs.next(); UPS_Monitoreados = rs.getString("Count(EstadoMonitoreo)"); } catch (Exception e) { } String directorio = ""; String extension = ""; boolean seleccion = false; int resultado = selector.showOpenDialog(null); switch (resultado) { case JFileChooser.APPROVE_OPTION: directorio = selector.getSelectedFile().getPath(); int i = directorio.lastIndexOf('.'); if (i >= 0) { extension = directorio.substring(i + 1); } seleccion = true; JOptionPane.showMessageDialog(null, "Seleccionaste" + directorio); break; case JFileChooser.CANCEL_OPTION: seleccion = false; JOptionPane.showMessageDialog(null, "No seleccionaste un archivo"); break; case JFileChooser.ERROR_OPTION: seleccion = false; JOptionPane.showMessageDialog(null, "Ocurreo un Error"); break; default: break; } if (extension.equals("xlsx")) { //Leer el archivo de Excel XLSX FileInputStream entrada = new FileInputStream(new File(directorio)); //Acceso al libro de trabajo XSSFWorkbook xlsx = new XSSFWorkbook(entrada); //Acceso a la hoja de trabajo XSSFSheet hoja = xlsx.getSheetAt(0); //Declaracion de fila y celda Row fila = null; Cell celda = null; try { //Asignando a valores a celdas con valores fila = hoja.getRow(2); celda = fila.getCell(8); celda.setCellValue(Integer.parseInt(UPS_Monitoreados)); } catch (NullPointerException NPE) { //En caso de que las celdas esten vacias hay que crearlas fila = hoja.createRow(0); celda = fila.createCell(0); celda.setCellValue(2); celda = fila.createCell(1); celda.setCellValue(6); fila = hoja.createRow(1); celda = fila.createCell(0); celda.setCellValue(9); celda = fila.createCell(1); celda.setCellValue(3); } //Evaluando formulas de todo el libro de excel XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx); //Cerrando la entrada archivo entrada.close(); //Abriendo archivo para escritura FileOutputStream salida = new FileOutputStream(new File(directorio)); //write changes xlsx.write(salida); //close the stream salida.close(); } }
From source file:Logica.L_POIExcel.java
public void Servic_Realizados() throws Exception { JFileChooser selector = new JFileChooser(); selector.setFileFilter(new FileNameExtensionFilter("Excel 2010 or Superior", "xlsx")); //selector.setFileFilter(new FileNameExtensionFilter("Excel with Macrs", "xlsm")); String sSql = ""; String sSql1 = ""; String sSql2 = ""; String sSql3 = ""; String sSql4 = ""; String sSql5 = ""; String sSql6 = ""; String sSql7 = ""; String sSql8 = ""; String UPS_Monitoreados = ""; String BYF = ""; sSql = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and Proveedor_Item like '%SISE%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')"; sSql1 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and Proveedor_Item like '%BYF%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')"; sSql2 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and Proveedor_Item like '%GE%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')"; sSql3 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and Proveedor_Item like '%EM%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')"; sSql4 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and Proveedor_Item like '%GATM%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')"; sSql5 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and Proveedor_Item like '%SOD%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')"; sSql6 = "SELECT count(Nro_OT) FROM otinformacion_prb WHERE FechaReporte like '%/08/2016%' and Proveedor_Item like '%TEC%' and (Estado_Servic like '%CC%' or Estado_Servic like '%CS%')"; try {//from ww w. ja va 2 s. c om Statement st = cn.createStatement(); ResultSet rs = st.executeQuery(sSql); rs.next(); UPS_Monitoreados = rs.getString("Count(Nro_OT)"); ResultSet rs1 = st.executeQuery(sSql1); rs1.next(); BYF = rs1.getString("Count(Nro_OT)"); } catch (Exception e) { } String directorio = ""; String extension = ""; boolean seleccion = false; int resultado = selector.showOpenDialog(null); switch (resultado) { case JFileChooser.APPROVE_OPTION: directorio = selector.getSelectedFile().getPath(); int i = directorio.lastIndexOf('.'); if (i >= 0) { extension = directorio.substring(i + 1); } seleccion = true; JOptionPane.showMessageDialog(null, "Seleccionaste" + directorio); break; case JFileChooser.CANCEL_OPTION: seleccion = false; JOptionPane.showMessageDialog(null, "No seleccionaste un archivo"); break; case JFileChooser.ERROR_OPTION: seleccion = false; JOptionPane.showMessageDialog(null, "Ocurreo un Error"); break; default: break; } if (extension.equals("xlsx")) { //Leer el archivo de Excel XLSX FileInputStream entrada = new FileInputStream(new File(directorio)); //Acceso al libro de trabajo XSSFWorkbook xlsx = new XSSFWorkbook(entrada); //Acceso a la hoja de trabajo XSSFSheet hoja = xlsx.getSheetAt(0); //Declaracion de fila y celda Row fila = null; Cell celda = null; try { //Asignando a valores a celdas con valores fila = hoja.getRow(1); celda = fila.getCell(8); celda.setCellValue(Integer.parseInt(UPS_Monitoreados)); fila = hoja.getRow(2); celda = fila.getCell(8); celda.setCellValue(Integer.parseInt(BYF)); } catch (NullPointerException NPE) { //En caso de que las celdas esten vacias hay que crearlas fila = hoja.createRow(0); celda = fila.createCell(0); celda.setCellValue(2); celda = fila.createCell(1); celda.setCellValue(6); fila = hoja.createRow(1); celda = fila.createCell(0); celda.setCellValue(9); celda = fila.createCell(1); celda.setCellValue(3); } //Evaluando formulas de todo el libro de excel XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx); //Cerrando la entrada archivo entrada.close(); //Abriendo archivo para escritura FileOutputStream salida = new FileOutputStream(new File(directorio)); //write changes xlsx.write(salida); //close the stream salida.close(); } }
From source file:Model.Spreadsheet.java
public void clockIn() throws FileNotFoundException, IOException { int row = getRowOfCurrentDate(); fsIP.close();/*from w w w. ja va 2s .co m*/ FileOutputStream output_file = new FileOutputStream( new File("C:/Users/brian.marshall/Documents/SamplePayroll.xlsx")); this.worksheet.getRow(row).getCell(1).setCellValue(currentDate.getTime()); XSSFFormulaEvaluator.evaluateAllFormulaCells(wb); this.wb.write(output_file); output_file.close(); }
From source file:Model.Spreadsheet.java
public void clockOut() throws IOException { int row = getRowOfCurrentDate(); fsIP.close();//w ww. j av a2 s . c o m FileOutputStream output_file = new FileOutputStream( new File("C:/Users/brian.marshall/Documents/SamplePayroll.xlsx")); this.worksheet.getRow(row).getCell(2).setCellValue(currentDate.getTime()); XSSFFormulaEvaluator.evaluateAllFormulaCells(wb); this.wb.write(output_file); output_file.close(); }
From source file:utilities.DocumentXLSManager.java
License:Open Source License
public void create(Connection sd, String remoteUser, ArrayList<KeyValue> data, OutputStream outputStream, String basePath, int oId) throws Exception { FileInputStream templateIS = null; String templateName = "ewarn_report_template.xlsx"; File templateFile = GeneralUtilityMethods.getDocumentTemplate(basePath, "ewarn_report_template.xlsx", oId); try {/*from w ww. j a va 2 s . co m*/ lm.writeLog(sd, 0, remoteUser, "error", "Failed to open template: " + templateName); templateIS = new FileInputStream(templateFile); } catch (Exception e) { throw e; } finally { templateIS.close(); } wb = new XSSFWorkbook(templateIS); templateIS.close(); Sheet sheet = wb.getSheetAt(0); for (KeyValue kv : data) { int namedCellIdx = wb.getNameIndex(kv.k); Name aNamedCell = wb.getNameAt(namedCellIdx); AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula(), null); CellReference[] crefs = aref.getAllReferencedCells(); for (int i = 0; i < crefs.length; i++) { Sheet s = wb.getSheet(crefs[i].getSheetName()); Row r = sheet.getRow(crefs[i].getRow()); Cell cell = r.getCell(crefs[i].getCol()); // extract the cell contents based on cell type etc. cell.setCellValue(new Double(kv.v)); } } XSSFFormulaEvaluator.evaluateAllFormulaCells(wb); wb.write(outputStream); wb.write(outputStream); outputStream.close(); }
From source file:workbench.db.importer.ExcelReader.java
License:Apache License
@Override public void load() throws IOException { if (dataFile != null) { // do not load the file twice. return;//from w ww . j a v a 2 s. c o m } InputStream in = null; try { in = new FileInputStream(inputFile); if (useXLSX) { dataFile = new XSSFWorkbook(in); } else { dataFile = new HSSFWorkbook(in); } } finally { FileUtil.closeQuietely(in); } initActiveSheet(); // TODO: find references to external files and update those as well // see: https://poi.apache.org/spreadsheet/eval.html try { if (useXLSX) { XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) dataFile); } else { HSSFFormulaEvaluator.evaluateAllFormulaCells((HSSFWorkbook) dataFile); } } catch (Exception ex) { LogMgr.logError("ExcelReader.load()", "Could not refresh formulas!", ex); } }