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:Logic.Xls.java
public void genXLS(ResultSet rs, String Rpt_name, String path) { try {/*ww w . j ava2 s . c o m*/ //RS METE DATA ResultSetMetaData rsmd = rs.getMetaData(); int col_count = rsmd.getColumnCount(); ArrayList<String> col_name = new ArrayList<>(); for (int i = 1; i <= col_count; i++) { col_name.add(rsmd.getColumnLabel(i)); } //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet(Rpt_name); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue(Rpt_name); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count - 1)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); for (int i = 0; i < col_count; i++) { cell = row.createCell(i); cell.setCellValue(col_name.get(i)); cell.setCellStyle(xstyle); } //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); for (int j = 1; j <= col_count; j++) { cell = row.createCell(j - 1); cell.setCellValue(rs.getString(j)); } i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + Rpt_name + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(path)); workbook.write(out); Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, ""); } catch (Exception ex) { Logger.getLogger(Xls.class.getName()).log(Level.SEVERE, "Exception : " + ex); } }
From source file:Logica.LLeerExcel.java
public static void LLeerExcel(File fileName) { try {// w ww .j av a2s. c o m InputStream inp = new FileInputStream(fileName); XSSFWorkbook wb = new XSSFWorkbook(inp); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(13); Cell cell = row.getCell(13); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("x"); FileOutputStream fileOut = new FileOutputStream( "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx"); wb.write(fileOut); fileOut.close(); } catch (Exception e) { System.out.println(e); } }
From source file:Logica.LLeerExcel.java
public static void LLeerExcel1(File fileName) { try {// w w w . j a v a 2 s .c o m InputStream inp = new FileInputStream(fileName); XSSFWorkbook wb = new XSSFWorkbook(inp); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(2); Cell cell = row.getCell(13); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("x"); FileOutputStream fileOut = new FileOutputStream( "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx"); wb.write(fileOut); fileOut.close(); } catch (Exception e) { System.out.println(e); } }
From source file:Logica.LLeerExcel1.java
public LLeerExcel1(File fileName) { try {/*from w ww.j a va 2 s . c om*/ InputStream inp = new FileInputStream(fileName); XSSFWorkbook wb = new XSSFWorkbook(inp); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(12); Cell cell = row.getCell(14); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a"); FileOutputStream fileOut = new FileOutputStream( "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx"); wb.write(fileOut); fileOut.close(); } catch (Exception e) { System.out.println(e); } }
From source file:Logica.L_Exel.java
public String SobreExportar(File archivo, JTable jTable1, String drct) { String a = "revisr"; try {/*from ww w. j a va 2 s .c o m*/ 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 w w w.ja v a2s .c om 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 {/* www .ja v a2s .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 {/* w w w. j a v a 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:log_compressor.event_checker.java
public static void event_checker() throws IOException, InterruptedException { HashMap<String, List<String>> hm = 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); }/*from w ww .j a va 2s .c o m*/ 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> event = 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("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); hm.put(servername, event1); } write_xlsx(hm, server_list); for (String server : server_list) { System.out.println(" "); System.out.println(server); System.out.println(hm.get(server).toString().substring(1, hm.get(server).toString().length() - 1)); System.out.println("==============================="); } }
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); }/*from w ww. java 2s. c om*/ 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("==============================="); } }