List of usage examples for org.apache.poi.ss.usermodel Workbook write
void write(OutputStream stream) throws IOException;
From source file:ket_noi_DB.ket_noi_kh.java
public void luuFile(Workbook workbook, String path, String sql, String tenCSDL) { try {//from w w w .j a va 2s .c o m data = statement.executeQuery(sql); } catch (SQLException ex) { Logger.getLogger(Khach_Hang.class.getName()).log(Level.SEVERE, null, ex); } Sheet sheet1 = workbook.createSheet(tenCSDL); try { metadata = data.getMetaData(); int numrow = 0; while (data.next()) { Row row = sheet1.createRow(numrow); sheet1.setColumnWidth(numrow, 5000); for (int i = 1; i <= metadata.getColumnCount(); i++) { row.createCell(i - 1).setCellValue(data.getString(i)); } numrow++; } } catch (SQLException ex) { Logger.getLogger(Khach_Hang.class.getName()).log(Level.SEVERE, null, ex); } try { FileOutputStream fout = new FileOutputStream(path); workbook.write(fout); fout.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:ket_noi_excel.ket_noi_excel_cd.java
public void saveFile(JTable tb, String path) { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("kho cd"); int numrow = 0; DefaultTableModel model = new DefaultTableModel(); model = (DefaultTableModel) tb.getModel(); Vector vt = model.getDataVector();// ly i tng vector ca model, cha ton b d liu for (int i = 0; i < tb.getRowCount(); i++) { Vector vtt = (Vector) vt.get(i);//ly dng i Row row = sheet.createRow(numrow); sheet.setColumnWidth(numrow, 5000); for (int j = 0; j < tb.getColumnCount() - 1; j++) {// tr i ct id cui cng ? phng khi import li ko c li out of range row.createCell(j).setCellValue(vtt.get(j).toString()); }//from www .j a v a 2 s . c o m numrow++; } try { FileOutputStream fout = new FileOutputStream(path); JOptionPane.showMessageDialog(null, "D liu c a ra file excel"); workbook.write(fout); fout.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ket_noi_excel.ket_noi_excel_dvd.java
public void saveFile(JTable tb, String path) { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("kho dvd"); int numrow = 0; DefaultTableModel model = new DefaultTableModel(); model = (DefaultTableModel) tb.getModel(); Vector vt = model.getDataVector();// ly i tng vector ca model, cha ton b d liu for (int i = 0; i < tb.getRowCount(); i++) { Vector vtt = (Vector) vt.get(i);//ly dng i Row row = sheet.createRow(numrow); sheet.setColumnWidth(numrow, 5000); for (int j = 0; j < tb.getColumnCount() - 1; j++) {// tr i ct id cui cng ? phng khi import li ko c li out of range row.createCell(j).setCellValue(vtt.get(j).toString()); }//from www. j a va 2 s.c o m numrow++; } try { FileOutputStream fout = new FileOutputStream(path); JOptionPane.showMessageDialog(null, "D liu c a ra file excel"); workbook.write(fout); fout.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:lldval.LLDVal.java
/** * @param args the command line arguments *//*from ww w. ja v a 2s. com*/ public static void main(String[] args) throws IOException { // reading data from a csv file System.out.println("Reading data from csv :"); ReadCsv readCsv = new ReadCsv(); readCsv.readCsv("./tunables.csv"); writeXLSXFile("./PCAT_AnalysisFile.xlsx", "Info"); Workbook wb = new XSSFWorkbook(new FileInputStream("./PCAT_AnalysisFile.xlsx")); XSSFSheet clusterSheet = (XSSFSheet) wb.createSheet("caaConfigurations"); XSSFSheet hwSheet = (XSSFSheet) wb.createSheet("HWConfigurations"); XSSFSheet vfcSheet = (XSSFSheet) wb.createSheet("VFC Mappings"); // reading data from a csv file and convert to java object System.out.println("Reading data from csv and convert to java object:"); // csvToTunables csvToJavaObject = new csvToTunables(); // csvToJavaObject.convertCsvToJava(); pcatFolders pcatFolder = new pcatFolders("./systems"); // System.out.println(Arrays.toString(pcatFolder.pcatFolderList)); String[] lparName = pcatFolder.pcatFolderList; System.out.println(Arrays.toString(lparName)); // caa CAA = new caa(); // readCaa readCaas = new readCaa("dx980"); for (int j = 0; j < lparName.length - 1; j++) { pcatLparFolders lparFolders = new pcatLparFolders("./systems", lparName[j]); System.out.print("LparName : " + String.valueOf(lparName[j]) + " : "); System.out.println(Arrays.toString(lparFolders.fileList)); for (int t = 0; t < lparFolders.fileList.length; t++) { System.out.println(lparFolders.fileList[t]); switch (lparFolders.fileList[t]) { // case "caa": // readCaa caa = new readCaa(lparName[j]); // caa.processingLineByLine(); // System.out.println("Cluster Configuration Populating"); //// System.out.println(caa.toString()); //// System.out.println(String.valueOf(CAA.CLUSTER_NAME+" : "+CAA.Cluster_shorthand_id_for_node+" : "+CAA.Mean_Deviation_in_network_rtt_to_node+" : "+CAA.Node_name+" : "+CAA.Number_of_clusters_node_is_a_member_in+" : "+CAA.Smoothed_rtt_to_node+" : "+CAA.State_of_node+" : "+CAA.UUID_for_node)); // int sheetLength = clusterSheet.getPhysicalNumberOfRows(); // if(sheetLength == 0){ // Row row = clusterSheet.createRow((short)sheetLength); // Cell cell = row.createCell(0); // cell.setCellValue("CLUSTER_NAME"); // Cell cell1 = row.createCell(1); // cell1.setCellValue("Node_name"); // Cell cell2 = row.createCell(2); // cell2.setCellValue("Number_of_clusters_node_is_a_member_in"); // Cell cell3 = row.createCell(3); // cell3.setCellValue("State_of_node"); // Cell cell4 = row.createCell(4); // cell4.setCellValue("UUID_for_node"); // Cell cell5 = row.createCell(5); // cell5.setCellValue("Cluster_shorthand_id_for_node"); // Cell cell6 = row.createCell(6); // cell6.setCellValue("Smoothed_rtt_to_node"); // Cell cell7 = row.createCell(7); // cell7.setCellValue("Mean_Deviation_in_network_rtt_to_node");}else // if (CAA.CLUSTER_NAME != null && !CAA.CLUSTER_NAME.isEmpty()){ // Row row = clusterSheet.createRow((short)sheetLength+1); // Cell cell = row.createCell(0); // cell.setCellValue(CAA.CLUSTER_NAME); // Cell cell1 = row.createCell(1); // cell1.setCellValue(CAA.Node_name); // Cell cell2 = row.createCell(2); // cell2.setCellValue(CAA.Number_of_clusters_node_is_a_member_in); // Cell cell3 = row.createCell(3); // cell3.setCellValue(CAA.State_of_node); // Cell cell4 = row.createCell(4); // cell4.setCellValue(CAA.UUID_for_node); // Cell cell5 = row.createCell(5); // cell5.setCellValue(CAA.Cluster_shorthand_id_for_node); // Cell cell6 = row.createCell(6); // cell6.setCellValue(CAA.Smoothed_rtt_to_node); // Cell cell7 = row.createCell(7); // cell7.setCellValue(CAA.Mean_Deviation_in_network_rtt_to_node); // } // try { // FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx"); // wb.write(out); // out.close(); // } catch (Exception e) { // e.printStackTrace(); // } // break; case "hds": System.out.println("HDS Storage Configuration Populating"); readHDL parser = new readHDL(lparName[j]); parser.processLineByLine(lparName[j]); // System.out.println(String.valueOf(lpar.getLparName()+" : "+lpar.getLparID()+" : "+lpar.getHostname()+" : "+lpar.getEntCapacity())); log("Done."); break; // case "hmc": // System.out.println("HMC Configuration Populating"); // break; case "hw": System.out.println("Hardware Configuration Populating"); readHW hw = new readHW(lparName[j]); hw.processLineByLine(); System.out.println(lpar.lparID + " : " + lpar.lparName + " : " + lpar.hostname + " : " + lpar.entCapacity + " : " + lpar.maxCPU + " : " + lpar.desiredCPU + " : " + lpar.minCPU + " : " + lpar.MaxVirtCPU + " : " + lpar.DesVirtCPU + " : " + lpar.minVirtCPU); int hwsheetLength = hwSheet.getPhysicalNumberOfRows(); if (hwsheetLength == 0) { Row row = hwSheet.createRow((short) hwsheetLength); Cell cell = row.createCell(0); cell.setCellValue("LparID"); Cell cell1 = row.createCell(1); cell1.setCellValue("lparName"); Cell cell2 = row.createCell(2); cell2.setCellValue("hostname"); Cell cell3 = row.createCell(3); cell3.setCellValue("maxMem"); Cell cell4 = row.createCell(4); cell4.setCellValue("desiredMem"); Cell cell5 = row.createCell(5); cell5.setCellValue("maxMem"); Cell cell6 = row.createCell(6); cell6.setCellValue("MemMode"); Cell cell7 = row.createCell(7); cell7.setCellValue("maxCPU"); Cell cell8 = row.createCell(8); cell8.setCellValue("desiredCPU"); Cell cell9 = row.createCell(9); cell9.setCellValue("minCPU"); Cell cell10 = row.createCell(10); cell10.setCellValue("MaxVirtCPU"); Cell cell11 = row.createCell(11); cell11.setCellValue("DesVirtCPU"); Cell cell12 = row.createCell(12); cell12.setCellValue("minVirtCPU"); Cell cell13 = row.createCell(13); cell13.setCellValue("entCapacity"); Cell cell14 = row.createCell(14); cell14.setCellValue("weight"); Cell cell15 = row.createCell(15); cell15.setCellValue("SMTType"); Cell cell16 = row.createCell(16); cell16.setCellValue("cpuMode"); } else if (lpar.lparID != null && !lpar.lparID.isEmpty()) { Row row = hwSheet.createRow((short) hwsheetLength + 1); Cell cell = row.createCell(0); cell.setCellValue(lpar.lparID); Cell cell1 = row.createCell(1); cell1.setCellValue(lpar.lparName); Cell cell2 = row.createCell(2); cell2.setCellValue(lpar.hostname); Cell cell3 = row.createCell(3); cell3.setCellValue(lpar.maxMem); Cell cell4 = row.createCell(4); cell4.setCellValue(lpar.desiredMem); Cell cell5 = row.createCell(5); cell5.setCellValue(lpar.maxMem); Cell cell6 = row.createCell(6); cell6.setCellValue(lpar.MemMode); Cell cell7 = row.createCell(7); cell7.setCellValue(lpar.maxCPU); Cell cell8 = row.createCell(8); cell8.setCellValue(lpar.desiredCPU); Cell cell9 = row.createCell(9); cell9.setCellValue(lpar.minCPU); Cell cell10 = row.createCell(10); cell10.setCellValue(lpar.MaxVirtCPU); Cell cell11 = row.createCell(11); cell11.setCellValue(lpar.DesVirtCPU); Cell cell12 = row.createCell(12); cell12.setCellValue(lpar.minVirtCPU); Cell cell13 = row.createCell(13); cell13.setCellValue(lpar.entCapacity); Cell cell14 = row.createCell(14); cell14.setCellValue(lpar.weight); Cell cell15 = row.createCell(15); cell15.setCellValue(lpar.SMTType); Cell cell16 = row.createCell(16); cell16.setCellValue(lpar.cpuMode); } try { FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx"); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } break; // case "network": // System.out.println("Network Configuration Populating"); // break; // case "os": // System.out.println("Operating System Configuration Populating"); // break; // case "perf": // System.out.println("Performance Configuration Populating"); // break; // case "secure": // System.out.println("Security Configuration Populating"); // break; // case "storage": // System.out.println("Storage Configuration Populating"); // break; // case "tsm": // System.out.println("TSM Configuration Populating"); // break; case "vio": vfcs.clear(); System.out.println("VIO Configuration Populating"); readVFCMap vfcmapd = new readVFCMap(lparName[j]); vfcmapd.processPatern(); vfcMap vfcmap = new vfcMap(); System.out.println(vfcs.size()); for (int vfcRows = 0; vfcRows <= vfcs.size() - 1; vfcRows++) { System.out.println(vfcs.get(vfcRows).vfcName + " | " + vfcs.get(vfcRows).physLoc + " | " + vfcs.get(vfcRows).clntId + " | " + vfcs.get(vfcRows).clntName + " | " + vfcs.get(vfcRows).status + " | " + vfcs.get(vfcRows).fc_name + " | " + vfcs.get(vfcRows).fc_loc_code + " | " + vfcs.get(vfcRows).flags + " | " + vfcs.get(vfcRows).VFC_client_name + " | " + vfcs.get(vfcRows).VFC_client_DRC); int vfcsheetLength = vfcSheet.getPhysicalNumberOfRows(); if (vfcsheetLength == 0) { Row row = vfcSheet.createRow((short) vfcsheetLength); int count = 0; Cell cell = row.createCell(0); cell.setCellValue("Lpar Name"); for (Field field : vfcmap.getClass().getDeclaredFields()) { count++; // System.out.println(field.getName()); Cell nextcell = row.createCell(count); nextcell.setCellValue(field.getName()); } // Row row1 = vfcSheet.createRow(1); // System.out.println("value row"+row1); // Cell valuecell = row1.createCell(0); // valuecell.setCellValue(lparName[j]); // Cell vfcNamecell = row1.createCell(vfcRows); // vfcNamecell.setCellValue(vfcs.get(vfcRows).vfcName); // Cell physLoccell = row1.createCell(vfcRows); // physLoccell.setCellValue(vfcs.get(vfcRows).physLoc); // Cell clntIdcell = row1.createCell(vfcRows); // clntIdcell.setCellValue(vfcs.get(vfcRows).clntId); // Cell clntNamecell = row1.createCell(vfcRows); // clntNamecell.setCellValue(vfcs.get(vfcRows).clntName); // Cell statuscell = row1.createCell(vfcRows); // statuscell.setCellValue(vfcs.get(vfcRows).status); // Cell fc_namecell = row1.createCell(vfcRows); // fc_namecell.setCellValue(vfcs.get(vfcRows).fc_name); // Cell fc_loc_codecell = row1.createCell(vfcRows); // fc_loc_codecell.setCellValue(vfcs.get(vfcRows).fc_loc_code); // Cell flagscell = row1.createCell(vfcRows); // flagscell.setCellValue(vfcs.get(vfcRows).flags); // Cell VFC_client_namecell = row1.createCell(vfcRows); // VFC_client_namecell.setCellValue(vfcs.get(vfcRows).VFC_client_name); // Cell VFC_client_DRCcell = row1.createCell(vfcRows); // VFC_client_DRCcell.setCellValue(vfcs.get(vfcRows).VFC_client_DRC); //vfcs.get(vfcRows).vfcName+" | "+vfcs.get(vfcRows).physLoc+" | "+vfcs.get(vfcRows).clntId+" | "+vfcs.get(vfcRows).clntName+" | "+vfcs.get(vfcRows).status+" | "+vfcs.get(vfcRows).fc_name+" | "+vfcs.get(vfcRows).fc_loc_code+" | "+vfcs.get(vfcRows).flags+" | "+vfcs.get(vfcRows).VFC_client_name+" | "+vfcs.get(vfcRows).VFC_client_DRC } else if (vfcs.get(vfcRows).vfcName != null && !vfcs.get(vfcRows).vfcName.isEmpty()) { Row valuerow = vfcSheet.createRow((short) vfcsheetLength); System.out.println("value row" + valuerow); Cell valuecell = valuerow.createCell(0); valuecell.setCellValue(lparName[j]); Cell vfcNamecell = valuerow.createCell(vfcRows); vfcNamecell.setCellValue(vfcs.get(vfcRows).vfcName); Cell physLoccell = valuerow.createCell(vfcRows); physLoccell.setCellValue(vfcs.get(vfcRows).physLoc); Cell clntIdcell = valuerow.createCell(vfcRows); clntIdcell.setCellValue(vfcs.get(vfcRows).clntId); Cell clntNamecell = valuerow.createCell(vfcRows); clntNamecell.setCellValue(vfcs.get(vfcRows).clntName); Cell statuscell = valuerow.createCell(vfcRows); statuscell.setCellValue(vfcs.get(vfcRows).status); Cell fc_namecell = valuerow.createCell(vfcRows); fc_namecell.setCellValue(vfcs.get(vfcRows).fc_name); Cell fc_loc_codecell = valuerow.createCell(vfcRows); fc_loc_codecell.setCellValue(vfcs.get(vfcRows).fc_loc_code); Cell flagscell = valuerow.createCell(vfcRows); flagscell.setCellValue(vfcs.get(vfcRows).flags); Cell VFC_client_namecell = valuerow.createCell(vfcRows); VFC_client_namecell.setCellValue(vfcs.get(vfcRows).VFC_client_name); Cell VFC_client_DRCcell = valuerow.createCell(vfcRows); VFC_client_DRCcell.setCellValue(vfcs.get(vfcRows).VFC_client_DRC); // valueCount++; // Cell value1cell = valuerow.createCell(valueCount); // value1cell.setCellValue(map.clntName);// i am stuck here to iterate around the values of each instance of the object. } } FileOutputStream out = new FileOutputStream("./PCAT_AnalysisFile.xlsx"); wb.write(out); out.close(); break; default: // System.out.println("Nothing to do"); break; } } ; } ; // getPropValue testRun = new getPropValue("./Systems/dx1010/hw/lparstat-vfcRows.txt"); }
From source file:logica.Excel.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//w ww .ja va 2 s.com * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { String reporte = request.getParameter("reporte"); System.out.println(reporte + "----------------------------------------------"); if (reporte.equals("repo1")) { String rutaArchivo = System.getProperty("user.home") + "/usuarios.xls"; Conexion conectar = new Conexion(); conectar.conectar(); ResultSet rs; rs = conectar.consulta("select * from usuario;"); System.out.println(rutaArchivo); File archivoXLS = new File(rutaArchivo); if (archivoXLS.exists()) archivoXLS.delete(); archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); try (FileOutputStream archivo = new FileOutputStream(archivoXLS)) { Sheet hoja = (Sheet) libro.createSheet("Mi hoja de trabajo"); int f = 1; Row fila2 = hoja.createRow(0); Cell celda2 = fila2.createCell(0); Cell celda3 = fila2.createCell(1); Cell celda4 = fila2.createCell(2); Cell celda5 = fila2.createCell(3); Cell celda6 = fila2.createCell(4); celda2.setCellValue("IdUsuario"); celda3.setCellValue("email"); celda4.setCellValue("estado"); celda5.setCellValue("fecha de registro"); celda6.setCellValue("contrasea"); while (rs.next()) { Row fila = hoja.createRow(f); for (int c = 0; c < 5; c++) { Cell celda = fila.createCell(c); if (c == 0) { celda.setCellValue(rs.getInt("IdUsuario")); } if (c == 1) { celda.setCellValue(rs.getString("email")); } if (c == 2) { celda.setCellValue(rs.getString("estado")); } if (c == 3) { celda.setCellValue(rs.getString("fecha_registro")); } if (c == 4) { celda.setCellValue(rs.getString("contra")); } } f++; } libro.write(archivo); } response.sendRedirect("Excel.jsp?r=Ve_a_ver_las_consultas_en_excel"); } if (reporte.equals("repo2")) { String rutaArchivo = System.getProperty("user.home") + "/peliseries.xls"; Conexion conectar = new Conexion(); conectar.conectar(); ResultSet rs; rs = conectar.consulta( "select idPeli_serie, valoracion, nombre, fecha_registro, estado, anio, descripcion from peli_serie;"); System.out.println(rutaArchivo); File archivoXLS = new File(rutaArchivo); if (archivoXLS.exists()) archivoXLS.delete(); archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); try (FileOutputStream archivo = new FileOutputStream(archivoXLS)) { Sheet hoja = (Sheet) libro.createSheet("Mi hoja de trabajo"); int f = 1; Row fila2 = hoja.createRow(0); Cell celda2 = fila2.createCell(0); Cell celda3 = fila2.createCell(1); Cell celda4 = fila2.createCell(2); Cell celda5 = fila2.createCell(3); Cell celda6 = fila2.createCell(4); Cell celda7 = fila2.createCell(5); Cell celda8 = fila2.createCell(6); celda2.setCellValue("IdPeli_serie"); celda3.setCellValue("valoracion"); celda4.setCellValue("nombre"); celda5.setCellValue("fecha de registro"); celda6.setCellValue("estado"); celda7.setCellValue("anio"); celda8.setCellValue("descripcion"); while (rs.next()) { Row fila = hoja.createRow(f); for (int c = 0; c < 7; c++) { Cell celda = fila.createCell(c); if (c == 0) { celda.setCellValue(rs.getInt("IdPeli_serie")); } if (c == 1) { celda.setCellValue(rs.getString("valoracion")); } if (c == 2) { celda.setCellValue(rs.getString("nombre")); } if (c == 3) { celda.setCellValue(rs.getString("fecha_registro")); } if (c == 4) { celda.setCellValue(rs.getString("estado")); } if (c == 5) { celda.setCellValue(rs.getString("anio")); } if (c == 6) { celda.setCellValue(rs.getString("descripcion")); } } f++; } libro.write(archivo); } response.sendRedirect("Excel.jsp?r=Ve_a_ver_las_consultas_en_excel"); } if (reporte.equals("repo3")) { String rutaArchivo = System.getProperty("user.home") + "/usuarios_y_planes.xls"; Conexion conectar = new Conexion(); conectar.conectar(); ResultSet rs; rs = conectar.consulta( "select idUsuario, email, usuario.fecha_registro, contra, plan.metodo_pago, plan.costo, plan.max_dispo from usuario,cliente,plan where idUsuario=Usuario_idUsuario and idPlan=Plan_idPlan;"); System.out.println(rutaArchivo); File archivoXLS = new File(rutaArchivo); if (archivoXLS.exists()) archivoXLS.delete(); archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); try (FileOutputStream archivo = new FileOutputStream(archivoXLS)) { Sheet hoja = (Sheet) libro.createSheet("Mi hoja de trabajo"); int f = 1; Row fila2 = hoja.createRow(0); Cell celda2 = fila2.createCell(0); Cell celda3 = fila2.createCell(1); Cell celda4 = fila2.createCell(2); Cell celda5 = fila2.createCell(3); Cell celda6 = fila2.createCell(4); Cell celda7 = fila2.createCell(5); Cell celda8 = fila2.createCell(6); celda2.setCellValue("IdUsuario"); celda3.setCellValue("email"); celda4.setCellValue("fecha de registro"); celda5.setCellValue("contrasea"); celda6.setCellValue("metodo de pago"); celda7.setCellValue("costo"); celda8.setCellValue("mximo # de dispositivos"); while (rs.next()) { Row fila = hoja.createRow(f); for (int c = 0; c < 7; c++) { Cell celda = fila.createCell(c); if (c == 0) { celda.setCellValue(rs.getInt("usuario.IdUsuario")); } if (c == 1) { celda.setCellValue(rs.getString("usuario.email")); } if (c == 2) { celda.setCellValue(rs.getString("usuario.fecha_registro")); } if (c == 3) { celda.setCellValue(rs.getString("usuario.contra")); } if (c == 4) { celda.setCellValue(rs.getString("plan.metodo_pago")); } if (c == 5) { celda.setCellValue(rs.getString("plan.costo")); } if (c == 6) { celda.setCellValue(rs.getString("plan.max_dispo")); } } f++; } libro.write(archivo); } response.sendRedirect("Excel.jsp?r=Ve_a_ver_las_consultas_en_excel"); } if (reporte.equals("repo4")) { response.sendRedirect("index.html?r= "); } }
From source file:LogicModel.excel_Manage.java
public static void createFinalExcel(String[] args, String fecha) throws Exception { /*La ruta donde se crear el archivo*/ /*Se crea el objeto de tipo File con la ruta del archivo*/ String rutaArchivo = System.getProperty("user.home") + "/ejemploExcelJava.xls"; File archivoXLS = new File(rutaArchivo); /*Si el archivo existe se elimina*/ if (archivoXLS.exists()) archivoXLS.delete();//ww w.jav a 2s. c o m /*Se crea el archivo*/ archivoXLS.createNewFile(); /*Se crea el libro de excel usando el objeto de tipo Workbook*/ Workbook libro = new HSSFWorkbook(); /*Se inicializa el flujo de datos con el archivo xls*/ FileOutputStream archivo = new FileOutputStream(archivoXLS); /*Utilizamos la clase Sheet para crear una nueva hoja de trabajo dentro del libro que creamos anteriormente*/ HashMap<String, Sheet> hojas = new HashMap<String, Sheet>(); for (int m = 0; m < 3; m++) { hojas.put("hoja" + m, libro.createSheet("Mi hoja de trabajo " + m)); } /*La clase Row nos permitir crear las filas*/ /*Cada fila tendr 10 celdas de datos*/ /*Creamos la celda a partir de la fila actual*/ /*Si la fila es la nmero 0, estableceremos los encabezados*/ for (int y = 0; y < 3; y++) { for (int f = 0; f < 10; f++) { Sheet hoja = hojas.get("hoja" + y); Row fila = hoja.createRow(f); for (int c = 0; c < 10; c++) { Cell celda = fila.createCell(c); if (f == 0) { switch (c) { case 0: celda.setCellValue("Fecha y Hora de recepcion"); break; case 1: celda.setCellValue("ID_Cliente"); break; case 2: celda.setCellValue("Asunto"); break; case 3: celda.setCellValue("Ticket_ID"); break; case 4: celda.setCellValue("Categoria"); break; case 5: celda.setCellValue("Empleado_ID"); break; case 6: celda.setCellValue("Fecha y hora de atencin"); break; case 7: celda.setCellValue("Tiempo en segundos"); break; case 8: celda.setCellValue("Comentario"); break; case 9: celda.setCellValue("Estado"); break; } } else { /*Aca se escriben los datos */ celda.setCellValue("YORLEY " + c + "," + f); } } } } /*Escribimos en el libro*/ libro.write(archivo); /*Cerramos el flujo de datos*/ archivo.close(); showExelData(readExcel(rutaArchivo)); /*Y abrimos el archivo con la clase Desktop*/ Desktop.getDesktop().open(archivoXLS); }
From source file:lp.XLSXhandler.java
public void create_xlsx_file(String absolute, String model, Object[] obj, double[][] results) { /*//ww w . ja va2 s . co m obj[0] = data; -- obj[1] = rows; obj[2] = columns; obj[3] = variables; -- obj[4] = dmu Names; -- */ double[][] data = (double[][]) obj[0]; // Rows number in file int rows_number = (Integer) obj[1]; // Columns in file (1-based) int col_number = (Integer) obj[2]; //int res = results.length; int results_number = results[0].length; int total_col = col_number + results_number;//9 String[] dmuNames = (String[]) obj[4]; String[] var = (String[]) obj[3]; String[] variables = new String[0]; /* Get the selected model/s and create the matrix "variables" for each model respectively. */ if (model.equals("multiplicative") || model.equals("composition")) { variables = new String[total_col]; System.arraycopy(var, 0, variables, 0, var.length); variables[col_number] = "e1"; variables[col_number + 1] = "e2"; variables[col_number + 2] = "Overall Efficiency"; } if (model.equals("additive")) { variables = new String[total_col]; System.arraycopy(var, 0, variables, 0, var.length); variables[col_number] = "Weight 1"; variables[col_number + 1] = "Weight 2"; variables[col_number + 2] = "Overall Efficiency"; variables[col_number + 3] = "Theta 1"; variables[col_number + 4] = "Theta 2"; } try { Workbook wb = new XSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream(absolute + "\\" + model + ".xlsx"); wb.write(fileOut); fileOut.close(); InputStream inp = new FileInputStream(absolute + "\\" + model + ".xlsx"); wb = WorkbookFactory.create(inp); Sheet sheet = wb.createSheet("new"); sheet = wb.getSheetAt(0); //int rows_number = results.length; //int col_number = results[0].length; Row row = sheet.createRow(0); row = sheet.getRow(0); if (row == null) { row = sheet.createRow(0); } Cell cell = row.getCell(0); if (cell == null) { //the first ROW: the names and variable names for each column for (int l = 0; l < variables.length; l++) { cell = row.createCell(l, cell.CELL_TYPE_STRING); cell.setCellValue(variables[l]); } for (int i = 0; i < rows_number; i++) { row = sheet.createRow(i + 1); int helper = 0; //the rest file... for (int k = 0; k < total_col + 1; k++) { if (k == 0) { //the first column (dmu names) cell = row.createCell(k, cell.CELL_TYPE_STRING); cell.setCellValue(dmuNames[i]); } if (k > 0 && k < col_number) { //the rest of the columns with data cell = row.createCell(k, cell.CELL_TYPE_NUMERIC); cell.setCellValue(data[i][k - 1]); } if (k >= col_number && helper < results_number) { //the columns with efficiency results cell = row.createCell(k, cell.CELL_TYPE_NUMERIC); cell.setCellValue(results[i][helper]); helper++; } } } } // Write the output to a file FileOutputStream fOut = new FileOutputStream(absolute + "\\" + model + ".xlsx"); wb.write(fOut); fOut.close(); inp.close(); } catch (FileNotFoundException e) { System.out.println("--EXCEPTION: " + e.getMessage()); } catch (IOException e) { System.out.println("--EXCEPTION: " + e.getMessage()); } catch (InvalidFormatException ex) { Logger.getLogger(XLSXhandler.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:main.KeywordList.java
public void writeToExcel(String fileName) { final int GROUP = 0; final int PACKAGENAME = 1; final int SUBPACKAGENAME = 2; final int POSITIONTYPE = 3; final int KEYWORD = 4; final int DATE = 5; final int PRIORITY = 6; try {//from ww w . j a v a 2 s . c om System.out.println("Writing XML: " + fileName); ForcastUi.consoleLog("Writing XML: " + fileName); FileInputStream fileIn = new FileInputStream(fileName); Workbook wb = WorkbookFactory.create(fileIn); Sheet sheet = wb.getSheetAt(0); keywordList.stream().filter(key -> key.isUsed()).forEach(keyword -> { //? ? or Don't int rowInt = 1; while (true) { Row row = sheet.getRow(rowInt); if (row == null) break; Cell cellKeyword = row.getCell(KEYWORD); String shortName = cellKeyword.getRichStringCellValue().getString(); if (shortName.equalsIgnoreCase(keyword.getKeyword())) {//If Match, Update Date Cell cellDate = row.getCell(DATE); cellDate.setCellType(Cell.CELL_TYPE_NUMERIC); cellDate.setCellValue(new Date()); //cellDate.setCellValue(ForcastUi.dateToString(new Date())); //cellDate.setCellType(Cell.CELL_TYPE_STRING); //cellDate.setCellValue(ForcastUi.dateToString(new Date())); //break; } rowInt++; } }); // Write the output to a file FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); fileIn.close(); } catch (FileNotFoundException e) { ErrorMessages.printErrorMsg(ErrorMessages.FILENOTFOUND, fileName); ForcastUi.consoleLog(e.getMessage()); e.printStackTrace(); } catch (IOException e) { ErrorMessages.printErrorMsg(ErrorMessages.FILECOR, fileName); ForcastUi.consoleLog(e.getMessage()); e.printStackTrace(); } catch (Exception ex) { ErrorMessages.printErrorMsg(ErrorMessages.FILECOR, fileName); ForcastUi.consoleLog(ex.getMessage()); Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:main.resources.FileExcel.java
public static void generaXlsx() throws IOException { //nombre del archivo de Excel String nombreArchivo = "quincena.xlsx"; String nombreHoja1 = "fecha";//nombre de la hoja1 Workbook libroTrabajo = new XSSFWorkbook(); Sheet hoja1 = libroTrabajo.createSheet(nombreHoja1); Row row = hoja1.createRow((short) 1); //row.setHeightInPoints(10); //alto de celda Cell cell = row.createCell((short) 1); Cell cell1 = row.createCell((short) 1); cell.setCellValue("Asistencia fecha xxxxxx"); CellStyle cellStyle = libroTrabajo.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //cellStyle.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex()); //cellStyle.setFillPattern(CellStyle.BIG_SPOTS); cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex()); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex()); hoja1.addMergedRegion(new CellRangeAddress(1, // first row (0-based) primera fila 1, //lasto row (0-based) ultima fila 1, //first column (0-based) numero de columna inicial 5 //last column (0-based) numero de columna final ));/*from ww w .j a v a2 s . co m*/ cell.setCellStyle(cellStyle); cell1.setCellStyle(cellStyle); //escribir este libro en un OutputStream. try (FileOutputStream fileOut = new FileOutputStream(nombreArchivo)) { //escribir este libro en un OutputStream. libroTrabajo.write(fileOut); fileOut.flush(); } }
From source file:main.resources.FileExcel.java
public void excelDia() throws FileNotFoundException, IOException { String nombreFile = "quincena.xlsx"; String nombreHoja = "dia x mes x ao x"; Workbook libro = new XSSFWorkbook(); Sheet hoja = libro.createSheet(nombreHoja); Font negrita = libro.createFont(); negrita.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle estilo = libro.createCellStyle(); estilo.setAlignment(CellStyle.ALIGN_CENTER); estilo.setFillForegroundColor(IndexedColors.GREEN.getIndex()); estilo.setFillPattern(CellStyle.SOLID_FOREGROUND); estilo.setBorderBottom(CellStyle.BORDER_THIN); estilo.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo.setBorderLeft(CellStyle.BORDER_THIN); estilo.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo.setBorderRight(CellStyle.BORDER_THIN); estilo.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo.setBorderTop(CellStyle.BORDER_THIN); estilo.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo.setFont(negrita);/* w ww . j a v a 2 s .c o m*/ CellStyle bordes = libro.createCellStyle(); bordes.setAlignment(CellStyle.ALIGN_LEFT); bordes.setBorderBottom(CellStyle.BORDER_THIN); bordes.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); bordes.setBorderLeft(CellStyle.BORDER_THIN); bordes.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex()); bordes.setBorderRight(CellStyle.BORDER_THIN); bordes.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex()); bordes.setBorderTop(CellStyle.BORDER_THIN); bordes.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex()); CellStyle estilo2 = libro.createCellStyle(); estilo2.setAlignment(CellStyle.ALIGN_CENTER); estilo2.setBorderBottom(CellStyle.BORDER_THIN); estilo2.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo2.setBorderLeft(CellStyle.BORDER_THIN); estilo2.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo2.setBorderRight(CellStyle.BORDER_THIN); estilo2.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo2.setBorderTop(CellStyle.BORDER_THIN); estilo2.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex()); estilo2.setAlignment(CellStyle.ALIGN_CENTER); estilo2.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); estilo2.setFillPattern(CellStyle.SOLID_FOREGROUND); estilo2.setFont(negrita); CellStyle borderBot = libro.createCellStyle(); borderBot.setBorderBottom(CellStyle.BORDER_THIN); borderBot.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex()); Row row = hoja.createRow(1); //Row row1 = hoja.createRow(2); //empleados faltas Appi app = new Appi(); Date Fecha = new Date(); DateFormat formato = new SimpleDateFormat("YYYY-MM-dd"); String fechaActual = formato.format(Fecha); ArrayList<Empleado> faltas = app.faltas(fechaActual);//obtengo listado de empleados String grupoBandera = ""; String maestro = ""; int pRow = 3; if (!faltas.isEmpty()) { Cell celda = row.createCell(3); Cell celda2 = row.createCell(4); Cell celda3 = row.createCell(5); Cell celda4 = row.createCell(6); Cell celda5 = row.createCell(7); combinarceldas(hoja, 1, 1, 3, 7); celda.setCellValue("Asistencia " + fechaActual); celda.setCellStyle(estilo); celda2.setCellStyle(estilo); celda3.setCellStyle(estilo); celda4.setCellStyle(estilo); celda5.setCellStyle(estilo); grupoBandera = faltas.get(0).getGrupo(); //encabezados Row row2 = hoja.createRow(pRow); Cell cell = row2.createCell(1); cell.setCellValue("Nficha"); cell.setCellStyle(estilo2); Cell cell1 = row2.createCell(2); cell1.setCellValue("1er Apellido"); cell1.setCellStyle(estilo2); Cell cell2 = row2.createCell(3); cell2.setCellValue("2do Apellido"); cell2.setCellStyle(estilo2); Cell cell3 = row2.createCell(4); cell3.setCellValue("1er Nombre"); cell3.setCellStyle(estilo2); Cell cell4 = row2.createCell(5); cell4.setCellValue("2do Nombre"); cell4.setCellStyle(estilo2); Cell cell5 = row2.createCell(6); cell5.setCellValue("Identificacion"); cell5.setCellStyle(estilo2); Cell cell6 = row2.createCell(7); cell6.setCellValue("Da"); cell6.setCellStyle(estilo2); Cell cell7 = row2.createCell(8); cell7.setCellValue("Cargo"); cell7.setCellStyle(estilo2); Cell cell8 = row2.createCell(9); cell8.setCellValue("Grupo"); cell8.setCellStyle(estilo2); } Empleado emp = null; for (int i = 0; i < faltas.size(); i++) { //datos emp = (Empleado) faltas.get(i); Grupo grupo = app.grupo(emp.getGrupo()); if (!grupoBandera.equals(emp.getGrupo())) { grupoBandera = emp.getGrupo(); pRow = pRow + 2; //frima maestro Row row4 = hoja.createRow(pRow); Cell celda9 = row4.createCell(1); combinarceldas(hoja, pRow, pRow, 1, 3); celda9.setCellValue("Maestro Grupo:"); celda9.setCellStyle(estilo); Cell celda10 = row4.createCell(2); celda10.setCellStyle(bordes); Cell celda11 = row4.createCell(3); celda11.setCellStyle(bordes); Cell celda12 = row4.createCell(4); celda12.setCellStyle(borderBot); Cell celda13 = row4.createCell(5); celda13.setCellStyle(borderBot); Cell celda14 = row4.createCell(6); celda14.setCellStyle(borderBot); Cell celda15 = row4.createCell(7); celda15.setCellStyle(borderBot); Cell celda16 = row4.createCell(8); celda16.setCellStyle(borderBot); pRow++; Row row6 = hoja.createRow(pRow); Cell celda64 = row6.createCell(4); combinarceldas(hoja, pRow, pRow, 4, 8); celda64.setCellValue(maestro); pRow = pRow + 2; //encabexzados Row row2 = hoja.createRow(pRow); Cell cell = row2.createCell(1); cell.setCellValue("Nficha"); cell.setCellStyle(estilo2); Cell cell1 = row2.createCell(2); cell1.setCellValue("1er Apellido"); cell1.setCellStyle(estilo2); Cell cell2 = row2.createCell(3); cell2.setCellValue("2do Apellido"); cell2.setCellStyle(estilo2); Cell cell3 = row2.createCell(4); cell3.setCellValue("1er Nombre"); cell3.setCellStyle(estilo2); Cell cell4 = row2.createCell(5); cell4.setCellValue("2do Nombre"); cell4.setCellStyle(estilo2); Cell cell5 = row2.createCell(6); cell5.setCellValue("Identificacion"); cell5.setCellStyle(estilo2); Cell cell6 = row2.createCell(7); cell6.setCellValue("Da"); cell6.setCellStyle(estilo2); Cell cell7 = row2.createCell(8); cell7.setCellValue("Cargo"); cell7.setCellStyle(estilo2); Cell cell8 = row2.createCell(9); cell8.setCellValue("Grupo"); cell8.setCellStyle(estilo2); } Row row5 = hoja.createRow(pRow + 1); Cell celda51 = row5.createCell(1); celda51.setCellStyle(bordes); celda51.setCellValue(emp.getnFicha()); Cell celda52 = row5.createCell(2); celda52.setCellStyle(bordes); celda52.setCellValue(emp.getpApellido()); Cell celda53 = row5.createCell(3); celda53.setCellStyle(bordes); celda53.setCellValue(emp.getsApellido()); Cell celda54 = row5.createCell(4); celda54.setCellStyle(bordes); celda54.setCellValue(emp.getpNombre()); Cell celda55 = row5.createCell(5); celda55.setCellStyle(bordes); celda55.setCellValue(emp.getsNombre()); Cell celda56 = row5.createCell(6); celda56.setCellStyle(bordes); celda56.setCellValue(emp.getCedula()); Cell celda57 = row5.createCell(7); celda57.setCellStyle(bordes); celda57.setCellValue(fechaActual); Cell celda58 = row5.createCell(8); celda58.setCellStyle(bordes); celda58.setCellValue(emp.getCargo()); Cell celda59 = row5.createCell(9); celda59.setCellStyle(bordes); celda59.setCellValue(grupo.getNombre()); pRow++; Empleado supervisor = app.empleado(grupo.getSupervisor()); if (supervisor != null) { maestro = supervisor.getpNombre() + " " + supervisor.getsNombre() + " " + supervisor.getpApellido() + " " + supervisor.getsApellido(); } else { maestro = String.valueOf(grupo.getSupervisor()); } // //String cedula = (String) e; //Empleado emp = app.empleado(cedula); System.out.println(emp.getCedula()); } if (emp != null) { pRow = pRow + 2; //frima maestro Row row4 = hoja.createRow(pRow); Cell celda9 = row4.createCell(1); combinarceldas(hoja, pRow, pRow, 1, 3); celda9.setCellValue("Maestro Grupo:"); celda9.setCellStyle(estilo); Cell celda10 = row4.createCell(2); celda10.setCellStyle(bordes); Cell celda11 = row4.createCell(3); celda11.setCellStyle(bordes); Cell celda12 = row4.createCell(4); celda12.setCellStyle(borderBot); Cell celda13 = row4.createCell(5); celda13.setCellStyle(borderBot); Cell celda14 = row4.createCell(6); celda14.setCellStyle(borderBot); Cell celda15 = row4.createCell(7); celda15.setCellStyle(borderBot); Cell celda16 = row4.createCell(8); celda16.setCellStyle(borderBot); pRow++; Row row6 = hoja.createRow(pRow); Cell celda64 = row6.createCell(4); combinarceldas(hoja, pRow, pRow, 4, 8); celda64.setCellValue(maestro); } //enmcabezados //Row row3 = hoja.createRow(1); // debe ejcutarse un loop de acuerdoa consaulta //datos //datos responsable firma //CellS //celda64.setCellStyle(); try (FileOutputStream fileOut = new FileOutputStream(nombreFile)) { //escribir este libro en un OutputStream. libro.write(fileOut); fileOut.flush(); } }