List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:de.tum.in.socket.server.ReadExcel.java
License:Apache License
/** * Reads the excel// w w w . j av a2 s . c o m */ public static List<RealtimeData> read() throws IOException { System.out.println("Reading dummy data from Excel....."); final List<RealtimeData> data = Lists.newArrayList(); XSSFWorkbook workbook = null; try { final File file = new File("testdata.xlsx"); final FileInputStream fileStream = new FileInputStream(file); // Get the workbook instance for XLS file workbook = new XSSFWorkbook(fileStream); // Get first sheet from the workbook final XSSFSheet sheet = workbook.getSheetAt(0); // load data from excel file final RealtimeData bluetoothData = new RealtimeData(); loadDataToList(sheet, data, bluetoothData); } catch (final Exception e) { e.printStackTrace(); } finally { workbook.close(); } System.out.println("Reading dummy data from Excel.....Done"); return data; }
From source file:de.tuttas.servlets.DokuServlet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// www. jav a 2 s . c o m * * @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 { String auth = request.getParameter("auth_token"); String service = request.getParameter("service_key"); Log.d("auth_token=" + auth); if (request.getParameter("cmd") == null || request.getParameter("idklasse") == null || request.getParameter("from") == null) { Log.d("Info zeigen"); response.setContentType("text/html;charset=UTF-8"); try (PrintWriter out = response.getWriter()) { /* TODO output your page here. You may use following sample code. */ out.println("<!DOCTYPE html>"); out.println("<html>"); out.println("<head>"); out.println("<title>Doku Servlet Usage</title>"); out.println("</head>"); out.println("<body>"); out.println("<h1>Doku Servlet @ " + request.getContextPath() + "</h1>"); out.println( "<a href='?cmd=Verlauf&idklasse=3608&from=2015-09-08&debug=" + Config.getInstance().debug + "' target='_pdf'>Usage: ?cmd=verlauf&idklasse=3608&from=2015-09-08</a>"); out.println("</body>"); out.println("</html>"); } } else { if (Config.getInstance().debug || service != null && auth != null && Authenticator.getInstance().isAuthTokenValid(auth)) { Log.d("ID Klasse = " + request.getParameter("idklasse")); Klasse kl = em.find(Klasse.class, Integer.parseInt(request.getParameter("idklasse"))); String cmd = request.getParameter("cmd"); String type = request.getParameter("type"); String sidSchuljahr = request.getParameter("idSchuljahr"); int idSchuljahr = -1; if (sidSchuljahr != null) { try { idSchuljahr = Integer.parseInt(sidSchuljahr); } catch (NumberFormatException nux) { } } String filter1 = request.getParameter("dokufilter1"); String filter2 = request.getParameter("dokufilter2"); int anwFilter1 = 0; int anwFilter2 = 0; if (request.getParameter("anwfilter1") != null) { anwFilter1 = Integer.parseInt(request.getParameter("anwfilter1")); } if (request.getParameter("anwfilter2") != null) { anwFilter2 = Integer.parseInt(request.getParameter("anwfilter2")); } Authenticator a = Authenticator.getInstance(); String me = a.getUser(auth); Log.d("Verlauf Filter1=" + filter1 + " Verlauf Filter2=" + filter2 + " me=" + me); Log.d("Anwesenheitsfilter 1 = " + anwFilter1 + " Filter2=" + anwFilter2); DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date parsedFrom = null; try { parsedFrom = (Date) dateFormat.parse(request.getParameter("from")); } catch (ParseException ex) { Logger.getLogger(DokuServlet.class.getName()).log(Level.SEVERE, null, ex); } Date parsedTo = null; if (request.getParameter("to") == null) { parsedTo = new java.sql.Date(System.currentTimeMillis()); } else { try { parsedTo = (Date) dateFormat.parse(request.getParameter("to")); } catch (ParseException ex) { Logger.getLogger(DokuServlet.class.getName()).log(Level.SEVERE, null, ex); } } Log.d("setze To auf " + new java.sql.Date(parsedTo.getTime())); Log.d("type=" + type + " cmd=" + cmd + " Klasse=" + kl.getKNAME()); if (type.compareTo("csv") == 0) { MyTableDataModel myModel = null; if (cmd.compareTo("Betriebe") == 0) { response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8"); response.setCharacterEncoding("UTF-8"); String fileName = cmd + "_" + kl.getKNAME() + "_" + new java.sql.Date(parsedTo.getTime()).toString() + ".xlsx"; fileName = URLEncoder.encode(fileName, "UTF-8"); String contentDisposition = "attachment; filename=\"" + fileName + "\"; filename*=UTF-8''" + fileName; response.addHeader("Content-Disposition", contentDisposition); myModel = getModelBetriebsliste(kl); XSSFWorkbook wb = ExcelUtil.readExcel( Config.getInstance().TEMPLATE_FILE_PATH + cmd + ".xlsx", new String[] { "Betriebe" }, myModel.getRows(), myModel.getCols()); wb = myModel.toExcel(wb, 0); wb.write(response.getOutputStream()); } else if (cmd.compareTo("Notenliste") == 0) { response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8"); response.setCharacterEncoding("UTF-8"); String fileName = cmd + "_" + kl.getKNAME() + "_" + new java.sql.Date(parsedTo.getTime()).toString() + ".xlsx"; fileName = URLEncoder.encode(fileName, "UTF-8"); String contentDisposition = "attachment; filename=\"" + fileName + "\"; filename*=UTF-8''" + fileName; response.addHeader("Content-Disposition", contentDisposition); myModel = getModelNotenliste(kl, idSchuljahr); XSSFWorkbook wb = ExcelUtil.readExcel( Config.getInstance().TEMPLATE_FILE_PATH + cmd + ".xlsx", new String[] { "Notenliste" }, myModel.getRows(), myModel.getCols()); wb = myModel.toExcel(wb, 0); wb.write(response.getOutputStream()); } else if (cmd.compareTo("Fehlzeiten") == 0) { response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8"); response.setCharacterEncoding("UTF-8"); String fileName = cmd + "_" + kl.getKNAME() + "_" + new java.sql.Date(parsedFrom.getTime()).toString() + "-" + new java.sql.Date(parsedTo.getTime()).toString() + ".xlsx"; fileName = URLEncoder.encode(fileName, "UTF-8"); String contentDisposition = "attachment; filename=\"" + fileName + "\"; filename*=UTF-8''" + fileName; response.addHeader("Content-Disposition", contentDisposition); myModel = getModelFehlzeiten(kl, parsedFrom, parsedTo); XSSFWorkbook wb = ExcelUtil.readExcel( Config.getInstance().TEMPLATE_FILE_PATH + cmd + ".xlsx", new String[] { "Fehlzeiten" }, myModel.getRows(), myModel.getCols()); wb = myModel.toExcel(wb, 0); wb.write(response.getOutputStream()); } else if (cmd.compareTo("Anwesenheit") == 0) { response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8"); response.setCharacterEncoding("UTF-8"); String fileName = cmd + "_" + kl.getKNAME() + "_" + new java.sql.Date(parsedFrom.getTime()).toString() + "-" + new java.sql.Date(parsedTo.getTime()).toString() + ".xlsx"; fileName = URLEncoder.encode(fileName, "UTF-8"); String contentDisposition = "attachment; filename=\"" + fileName + "\"; filename*=UTF-8''" + fileName; response.addHeader("Content-Disposition", contentDisposition); myModel = getModelAnwesenheit(kl, parsedFrom, parsedTo, anwFilter1, anwFilter2); XSSFWorkbook wb = ExcelUtil.readExcel( Config.getInstance().TEMPLATE_FILE_PATH + cmd + ".xlsx", new String[] { "Anwesenheit" }, myModel.getRows(), myModel.getCols()); wb = myModel.toExcel(wb, 0); wb.write(response.getOutputStream()); } else if (cmd.compareTo("Verlauf") == 0) { response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8"); response.setCharacterEncoding("UTF-8"); String fileName = cmd + "_" + kl.getKNAME() + "_" + new java.sql.Date(parsedFrom.getTime()).toString() + "-" + new java.sql.Date(parsedTo.getTime()).toString() + ".xlsx"; fileName = URLEncoder.encode(fileName, "UTF-8"); String contentDisposition = "attachment; filename=\"" + fileName + "\"; filename*=UTF-8''" + fileName; response.addHeader("Content-Disposition", contentDisposition); myModel = getModelVerlauf(kl, parsedFrom, parsedTo, filter1, filter2, me); XSSFWorkbook wb = ExcelUtil.readExcel( Config.getInstance().TEMPLATE_FILE_PATH + cmd + ".xlsx", new String[] { "Unterrichtsverlauf" }, myModel.getRows(), myModel.getCols()); wb = myModel.toExcel(wb, 0); wb.write(response.getOutputStream()); } else if (cmd.compareTo("UmfrageAuswertung") == 0) { Umfrage u = em.find(Umfrage.class, anwFilter1); Umfrage u2 = em.find(Umfrage.class, anwFilter2); response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8"); response.setCharacterEncoding("UTF-8"); String fileName = "Auswertung_" + u.getNAME() + "_vom_" + new java.sql.Date(parsedTo.getTime()).toString() + ".xlsx"; fileName = URLEncoder.encode(fileName, "UTF-8"); String contentDisposition = "attachment; filename=\"" + fileName + "\"; filename*=UTF-8''" + fileName; response.addHeader("Content-Disposition", contentDisposition); List<UmfrageResult> res1 = UmfrageUtil.getUmfrageResult(em, auth, anwFilter1, filter1); List<UmfrageResult> res2 = UmfrageUtil.getUmfrageResult(em, auth, anwFilter2, filter2); res2 = UmfrageUtil.getComparableResultList(res1, res2); myModel = getModelUmfrageAuswertung(res1); XSSFWorkbook wb = ExcelUtil.readExcel( Config.getInstance().TEMPLATE_FILE_PATH + u.getNAME() + ".xlsx", new String[] { "Hauptgruppe", "Vergleichsgruppe", "Info" }, myModel.getRows(), myModel.getCols()); wb = myModel.toExcel(wb, 0); myModel = getModelUmfrageAuswertung(res2); wb = myModel.toExcel(wb, 1); XSSFSheet s = wb.getSheetAt(2); XSSFRow r = s.getRow(0); r.getCell(0).setCellValue(u.getNAME()); r.getCell(1).setCellValue(filter1); r = s.getRow(1); r.getCell(0).setCellValue(u2.getNAME()); r.getCell(1).setCellValue(filter2); wb.write(response.getOutputStream()); } else { PrintWriter out = response.getWriter(); response.setContentType("application/json; charset=UTF-8"); String r = "{\"error\":true,\"msg\":\"Kann fr " + cmd + " kein Datenmodell erzeugen!\"}"; out.print(r); } } else { response.setContentType("application/pdf"); //Get the output stream for writing PDF object OutputStream out = response.getOutputStream(); try { String kopf = ""; kopf += ("<table border='1' align='center' width='100%'>"); kopf += ("<tr>"); kopf += ("<td rowspan=\"3\" width='150px'></td>"); kopf += ("<td align='center'><h2>Multi Media Berufsbildende Schulen Hannover</h2></td>"); if (cmd.compareTo("Verlauf") == 0) { kopf += ("<td colspan=\"2\" align='center'><b>Digitales Klassenbuch Unterrichtsverlauf</b></td>"); } else if (cmd.compareTo("Anwesenheit") == 0) { kopf += ("<td colspan=\"2\" align='center'><b>Digitales Klassenbuch Anwesenheit</b></td>"); } else if (cmd.compareTo("Fehlzeiten") == 0) { kopf += ("<td colspan=\"2\" align='center'><b>Digitales Klassenbuch Fehlzeiten</b></td>"); } else if (cmd.compareTo("Stundenplan") == 0) { kopf += ("<td colspan=\"2\" align='center'><b>Digitales Klassenbuch Stundenplan</b></td>"); } else if (cmd.compareTo("Vertretungsplan") == 0) { kopf += ("<td colspan=\"2\" align='center'><b>Digitales Klassenbuch Vertretungsplan</b></td>"); } else if (cmd.compareTo("Notenliste") == 0) { kopf += ("<td colspan=\"2\" align='center'><b>Digitales Klassenbuch Notenliste</b></td>"); } else if (cmd.compareTo("Betriebe") == 0) { kopf += ("<td colspan=\"2\" align='center'><b>Digitales Klassenbuch Betriebsliste</b></td>"); } else if (cmd.compareTo("UmfrageAuswertung") == 0) { kopf += ("<td colspan=\"2\" align='center'><b>Digitales Klassenbuch Auswertung Umfrage</b></td>"); } kopf += ("</tr>"); kopf += ("<tr>"); if (cmd.compareTo("UmfrageAuswertung") == 0) { Umfrage u1 = em.find(Umfrage.class, anwFilter1); Umfrage u2 = em.find(Umfrage.class, anwFilter2); kopf += ("<td align='center' rowspan=\"2\" style=\"padding:5px;font-size: 11\">Hauptgruppe: (" + u1.getNAME() + "/" + filter1 + ")<br></br> Vergleichsgruppe: (" + u2.getNAME() + "/" + filter2 + ")</td>"); } else { kopf += ("<td align='center' rowspan=\"2\"><h3>Klasse/ Kurs: " + kl.getKNAME() + "</h3></td>"); } kopf += ("<td style=\"font-size: 11;\">Verantwortlicher: " + kl.getID_LEHRER() + "</td>"); kopf += ("<td style=\"font-size: 11;\">geprft</td>"); kopf += ("</tr>"); kopf += ("<tr>"); DateFormat df = new SimpleDateFormat("dd.MM.yyyy"); Calendar c = df.getCalendar(); c.setTimeInMillis(System.currentTimeMillis()); String dat = c.get(Calendar.DAY_OF_MONTH) + "." + (c.get(Calendar.MONTH) + 1) + "." + c.get(Calendar.YEAR); kopf += ("<td style=\"font-size: 11;\">Ausdruck am: " + dat + "</td>"); kopf += ("<td style=\"font-size: 11;\">Datum</td>"); kopf += ("</tr>"); kopf += ("</table>"); kopf += ("<p> </p>"); Document document; if (cmd.compareTo("Verlauf") == 0) { response.addHeader("Content-Disposition", "attachment; filename=Verlauf_" + kl.getKNAME() + "_" + new java.sql.Date(parsedFrom.getTime()).toString() + "-" + new java.sql.Date(parsedTo.getTime()).toString() + ".pdf"); document = createVerlauf(kl, kopf, parsedFrom, parsedTo, out, filter1, filter2, me); } else if (cmd.compareTo("Portfolio") == 0) { response.addHeader("Content-Disposition", "attachment; filename=Portfolio_" + kl.getKNAME() + ".pdf"); document = createPortfolio(kl, out); } else if (cmd.compareTo("Anwesenheit") == 0) { response.addHeader("Content-Disposition", "attachment; filename=Anwesenheit_" + kl.getKNAME() + "_" + new java.sql.Date(parsedFrom.getTime()).toString() + "-" + new java.sql.Date(parsedTo.getTime()).toString() + ".pdf"); document = createAnwesenheit(kl, kopf, parsedFrom, parsedTo, out, anwFilter1, anwFilter2); } else if (cmd.compareTo("Fehlzeiten") == 0) { response.addHeader("Content-Disposition", "attachment; filename=Fehlzeiten_" + kl.getKNAME() + "_" + new java.sql.Date(parsedFrom.getTime()).toString() + "-" + new java.sql.Date(parsedTo.getTime()).toString() + ".pdf"); MyTableDataModel myModel = getModelFehlzeiten(kl, parsedFrom, parsedTo); document = createFehlzeiten(kl, kopf, parsedFrom, parsedTo, out); } else if (cmd.compareTo("Vertretungsliste") == 0) { response.addHeader("Content-Disposition", "attachment; filename=Vertretungsliste_" + new java.sql.Date(parsedFrom.getTime()).toString() + "-" + new java.sql.Date(parsedTo.getTime()).toString() + ".pdf"); document = createVertretungsliste(parsedFrom, parsedTo, out); } else if (cmd.compareTo("Notenliste") == 0) { response.addHeader("Content-Disposition", "attachment; filename=Notenliste_" + kl.getKNAME() + "_" + new java.sql.Date(parsedTo.getTime()).toString() + ".pdf"); MyTableDataModel myModel = getModelNotenliste(kl, idSchuljahr); Schuljahr schuljahr = em.find(Schuljahr.class, idSchuljahr); document = createNotenliste(myModel, kopf, out, schuljahr.getNAME()); } else if (cmd.compareTo("Betriebe") == 0) { response.addHeader("Content-Disposition", "attachment; filename=Betriebsliste_" + kl.getKNAME() + "_" + new java.sql.Date(parsedTo.getTime()).toString() + ".pdf"); MyTableDataModel myModel = getModelBetriebsliste(kl); document = createBetriebsListe(myModel, kopf, out); } else if (cmd.compareTo("UmfrageAuswertung") == 0) { response.addHeader("Content-Disposition", "attachment; filename=UmfrageAuswertung_" + new java.sql.Date(parsedTo.getTime()).toString() + ".pdf"); List<UmfrageResult> res1 = UmfrageUtil.getUmfrageResult(em, auth, anwFilter1, filter1); List<UmfrageResult> res2 = UmfrageUtil.getUmfrageResult(em, auth, anwFilter2, filter2); res2 = UmfrageUtil.getComparableResultList(res1, res2); if (res1 != null && res2 != null) { Log.d("erzeuge pdf Dokument"); document = createUmfrageauswertung(res1, res2, anwFilter1, anwFilter2, filter1, filter2, kopf, out); } } } catch (DocumentException exc) { Log.d("Document Exception " + exc.getMessage()); exc.printStackTrace(); throw new IOException(exc.getMessage()); } catch (ParseException ex) { Log.d("Parse Exception " + ex.getMessage()); Logger.getLogger(DokuServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { out.close(); } } } else { response.setContentType("text/html;charset=UTF-8"); try (PrintWriter out = response.getWriter()) { out.println("<!DOCTYPE html>"); out.println("<html>"); out.println("<head>"); out.println("<title>Doku Servlet Usage</title>"); out.println("</head>"); out.println("<body>"); out.println("<h1>You are not authorized</h1>"); out.println("</body>"); out.println("</html>"); } } } }
From source file:de.tuttas.servlets.MyTableDataModel.java
public XSSFWorkbook toExcel(XSSFWorkbook wb, int sheetNumer) { XSSFSheet sh = wb.getSheetAt(sheetNumer); for (int y = 0; y < rows; y++) { XSSFRow r = sh.getRow(y);//from ww w .j av a 2 s . co m for (int x = 0; x < cols; x++) { XSSFCell c = r.getCell(x); String d = data[y][x]; Log.d("Write to Cell " + d); if (d != null) { try { double value = Double.parseDouble(d); c.setCellValue(value); } catch (NumberFormatException nux) { c.setCellValue(d); } } } } return wb; }
From source file:dias.Load.java
public Matrix load(Matrix matrice, String filename) throws FileNotFoundException, IOException { FileInputStream fis = null;//from w w w . ja v a 2 s. com try { String fileName = DIAS.excelFilePath + "\\" + filepath + "\\" + filename + ".xlsx"; fis = new FileInputStream(fileName); XSSFWorkbook calismaKitap = new XSSFWorkbook(fis); XSSFSheet sheet = calismaKitap.getSheetAt(0); Iterator rows = sheet.rowIterator(); int ih = 0; int jh = 0; while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); ih++; while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); jh++; // matrice=createnewMatrix(ih,jh, matrice); matrice.set(ih - 1, jh - 1, cell.getNumericCellValue()); } jh = 0; } ih = 0; jh = 0; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return matrice; }
From source file:dias.Load.java
public double[][][] load3D(double[][][] matrice3d, String filename, int kj) throws FileNotFoundException, IOException { Matrix matrice = new Matrix(matrice3d.length, matrice3d[0].length); FileInputStream fis = null;/* w w w .jav a 2 s . c om*/ for (int t = 0; t < kj; t++) { try { String fileName = "D:\\Phd\\Research\\Kamuran`s Code\\" + filepath + "\\" + filename + t + ".xlsx"; fis = new FileInputStream(fileName); XSSFWorkbook calismaKitap = new XSSFWorkbook(fis); XSSFSheet sheet = calismaKitap.getSheetAt(0); Iterator rows = sheet.rowIterator(); int ih = 0; int jh = 0; while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); ih++; while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); jh++; // matrice=createnewMatrix(ih,jh, matrice); matrice.set(ih - 1, jh - 1, cell.getNumericCellValue()); } jh = 0; } ih = 0; jh = 0; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } matrice3D = new double[matrice.getRowDimension()][matrice.getColumnDimension()][kj]; for (int i = 0; i < matrice.getRowDimension(); i++) for (int j = 0; j < matrice.getColumnDimension(); j++) matrice3D[i][j][t] = matrice.get(i, j); } return matrice3D; }
From source file:dias.Load.java
public double loaddouble(String filename) throws FileNotFoundException, IOException { /// double[][] matrice=new double[150][150]; double matrice = 0; FileInputStream fis = null;/*from w w w .jav a 2s .c o m*/ try { String fileName = "D:\\Phd\\Research\\Kamuran`s Code\\" + filepath + "\\" + filename + ".xlsx"; fis = new FileInputStream(fileName); XSSFWorkbook calismaKitap = new XSSFWorkbook(fis); XSSFSheet sheet = calismaKitap.getSheetAt(0); Iterator rows = sheet.rowIterator(); int ih = 0; int jh = 0; while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); ih++; while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); jh++; matrice = cell.getNumericCellValue(); } jh = 0; } ih = 0; jh = 0; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return matrice; }
From source file:dias.Load.java
public String[] loadString(String filename, int kj) throws FileNotFoundException, IOException { /// double[][] matrice=new double[150][150]; String[] matrice = new String[kj]; FileInputStream fis = null;/*from w w w. j av a 2 s . c o m*/ try { String fileName = "D:\\Phd\\Research\\Kamuran`s Code\\" + filepath + "\\" + filename + ".xlsx"; fis = new FileInputStream(fileName); XSSFWorkbook calismaKitap = new XSSFWorkbook(fis); XSSFSheet sheet = calismaKitap.getSheetAt(0); Iterator rows = sheet.rowIterator(); int ih = 0; int jh = 0; while (rows.hasNext()) { XSSFRow row = (XSSFRow) rows.next(); Iterator cells = row.cellIterator(); ih++; while (cells.hasNext()) { XSSFCell cell = (XSSFCell) cells.next(); jh++; matrice[ih - 1] = cell.getStringCellValue(); } jh = 0; } ih = 0; jh = 0; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return matrice; }
From source file:ec.mil.he1.mbeans.JSFManagedBeanFileUpload.java
public String convertjava() { grabar = "0"; Connection connection = null; PreparedStatement preparedStatement = null; try {// www . j ava2s .co m connection = he1_pool.getConnection(); String sql = "Insert into CODIGO_MIFIN(CEDULA, CODIGO, MES , MES_NUMERO, ANIO , DESCRIPCION, ARCHIVO) Values " + " (?, ?, ?, ? , ?, ? , ? )"; int columna = 0; /*PrepareStatement*/ preparedStatement = connection.prepareStatement(sql); //variables donde cargar los datos por cada celda String cc = ""; String codigo = ""; String mes = ""; String mes_numero = ""; String anio = ""; String descripcion = ""; file = (FileInputStream) inputstream; // Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); sheet = workbook.getSheetAt(0); // Iterate through each rows from first sheet Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.rowIterator(); //aca se barre todas las filas while (rowIterator.hasNext()) { org.apache.poi.ss.usermodel.Row row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); //aca se tiene las columnas por ello encero columna = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: cell.getStringCellValue(); break; } if (columna == 0) { cc = cell.getStringCellValue(); } else if (columna == 1) { codigo = cell.getStringCellValue(); } else if (columna == 2) { mes = cell.getStringCellValue(); } else if (columna == 3) { mes_numero = cell.getStringCellValue(); } else if (columna == 4) { anio = cell.getStringCellValue(); } else if (columna == 5) { descripcion = cell.getStringCellValue(); } columna++; } preparedStatement.setString(1, cc); preparedStatement.setString(2, codigo); preparedStatement.setString(3, mes); preparedStatement.setString(4, mes_numero); preparedStatement.setString(5, anio); preparedStatement.setString(6, descripcion); preparedStatement.setString(7, nombre_archivo); preparedStatement.addBatch(); cc = ""; codigo = ""; mes = ""; mes_numero = ""; anio = ""; descripcion = ""; System.out.println(""); } file.close(); int[] affectedRecords = preparedStatement.executeBatch(); addMessage("Se ha cargado la informacin en el sistema"); } catch (IOException ex) { Logger.getLogger(JSFManagedBeanFileUpload.class.getName()).log(Level.SEVERE, null, ex); } catch (SQLException ex) { Logger.getLogger(JSFManagedBeanFileUpload.class.getName()).log(Level.SEVERE, null, ex); } finally { if (preparedStatement != null) { try { preparedStatement.close(); preparedStatement = null; } catch (SQLException ex) { Logger.getLogger(JSFManagedBeanFileUpload.class.getName()).log(Level.SEVERE, null, ex); } } if (connection != null) { try { connection.close(); connection = null; } catch (SQLException ex) { Logger.getLogger(JSFManagedBeanFileUpload.class.getName()).log(Level.SEVERE, null, ex); } } } return null; }
From source file:ec.sirec.web.impuestos.GestionAlcabalasControlador.java
public void postProcessXLS(Object document) { XSSFWorkbook wb = (XSSFWorkbook) document; XSSFSheet sheet = wb.getSheetAt(0); //Creo variable hoja ()contiene los atributos para la hoja de calculo List<String> encabezadoColumna = new ArrayList<String>(); for (Row row : sheet) { //Recorre los valores de la fila 1 (encabezado) pero en dataTable=0 if (row.getRowNum() == 0) { for (Cell cell : row) { encabezadoColumna.add(cell.getStringCellValue() + " "); }/*from w w w . j a v a 2 s. co m*/ } else { break; } } //----inicio crea estilo XSSFCellStyle style = wb.createCellStyle(); //Se crea el estilo XSSFFont font = wb.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); byte[] rgb = new byte[3]; rgb[0] = (byte) 076; rgb[1] = (byte) 145; rgb[2] = (byte) 065; XSSFColor myColor = new XSSFColor(rgb); style.setFillForegroundColor(myColor); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); XSSFRow row0 = sheet.createRow((short) 0); //Creo una fila en la posicion 0 //----fin crea estilo for (int i = 0; i <= encabezadoColumna.size() - 1; i++) { createCell(row0, i, encabezadoColumna.get(i), style); //agrego celdas en la posicion indicada con los valores de los encabezados } //Ajusta el ancho de las columnas for (int i = 0; i < 20; i++) { sheet.autoSizeColumn((short) i); } }
From source file:ec.sirec.web.impuestos.GestionImpuestoPredialControlador.java
public void postProcessXLS(Object document) throws IOException { XSSFWorkbook wb = (XSSFWorkbook) document; XSSFSheet hoja = wb.getSheetAt(0); CellStyle style = wb.createCellStyle(); style.setFillPattern(CellStyle.NO_FILL); org.apache.poi.ss.usermodel.Font font = wb.createFont(); font.setFontName("Times Roman"); font.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.BLACK.getIndex()); style.setFont(font);/* w w w . j a v a 2 s . com*/ /** * ** Configuracin del estilo de la celda header de la tabla. ***** */ CellStyle styleHeaderTable = wb.createCellStyle(); styleHeaderTable.setFillPattern(CellStyle.NO_FILL); org.apache.poi.ss.usermodel.Font fontHeaderTable = wb.createFont(); fontHeaderTable.setFontName("Times Roman"); fontHeaderTable.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); fontHeaderTable.setColor(IndexedColors.BLACK.getIndex()); styleHeaderTable.setFont(fontHeaderTable); Sheet sheet = wb.getSheetAt(0); sheet.autoSizeColumn((short) 0); //ajusta el ancho de la primera columna sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); for (int i = 0; i < 20; i++) { hoja.autoSizeColumn((short) i); } }