Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow.

Prototype

@Override
public XSSFRow getRow(int rownum)

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:de.tuttas.servlets.DokuServlet.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from  w w w  .jav a2 s .c om*/
 *
 * @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>&nbsp;</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);
        for (int x = 0; x < cols; x++) {
            XSSFCell c = r.getCell(x);/* ww  w  .  j  a v  a 2  s  . c o m*/
            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:edu.jhu.cvrg.timeseriesstore.opentsdb.store.ExcelStorer.java

License:Apache License

@Override
public ArrayList<IncomingDataPoint> extractTimePoints(InputStream inputStream, String[] channels, int samples,
        long epochTime) {

    ArrayList<IncomingDataPoint> dataPoints = new ArrayList<IncomingDataPoint>();
    XSSFWorkbook subjectWorkbook = getWorkbook(inputStream);
    HashMap<String, String> tags = new HashMap<String, String>();

    for (int i = 0; i < subjectWorkbook.getNumberOfSheets(); i++) {
        XSSFSheet sheetIn = subjectWorkbook.getSheetAt(i);
        for (int r = 1; r <= sheetIn.getLastRowNum(); r++) {
            long currentTime = epochTime;
            XSSFRow row = sheetIn.getRow(r);
            String channel = getChannelName(i, channels);
            dataPoints.add(new IncomingDataPoint("ecg.uv." + channel, currentTime,
                    String.valueOf(row.getCell(1).getNumericCellValue()), tags));
            tags.put("format", "excel");
            currentTime++;//from   www .ja  va 2s. co  m
        }
    }
    return dataPoints;
}

From source file:edu.vt.vbi.patric.common.ExcelHelper.java

License:Apache License

/**
 * This method automatically sets the column widths How: Measures the character length of the text in header cell of a column. Max column length
 * is either the title length or the title length *4
 *///from ww  w  .  ja  v  a  2  s.  c  o  m
public void setColWidths() {
    if (wb == null) {
        int margin = 4;
        XSSFSheet sheet = xwb.getSheetAt(0);

        XSSFRow row = sheet.getRow(0);
        for (int i = 0; i < row.getLastCellNum(); i++) {
            sheet.setColumnWidth(i, (decideXColumnWidth(sheet, i) + margin) * 256);
        }
    } else {
        int margin = 4;
        Sheet sheet = wb.getSheetAt(0);

        Row row = sheet.getRow(0);
        for (int i = 0; i < row.getLastCellNum(); i++) {
            sheet.setColumnWidth(i, (decideColumnWidth(sheet, i) + margin) * 256);
        }
    }
}

From source file:edu.vt.vbi.patric.common.ExcelHelper.java

License:Apache License

/**
 * Returns the width the Column should be (XSSF version)
 * @param sheet - sheet of workbook/*  w w w  .jav a 2  s  .co m*/
 * @param col - the column to work with
 * @return length (in characters) of that column
 */
private int decideXColumnWidth(XSSFSheet sheet, int col) {
    int titleLength = sheet.getRow(0).getCell(col).getStringCellValue().length();
    int longestString = titleLength;

    for (int i = 0; i < sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i);
        XSSFCell cell = row.getCell(col);
        int temp = cell.getStringCellValue().length();
        if (temp > titleLength * 2) {
            longestString = temp;
        }
    }

    if (longestString > titleLength * 4) {
        longestString = titleLength * 4;
    }

    return longestString;
}

From source file:egovframework.rte.fdl.excel.util.AbstractPOIExcelView.java

License:Apache License

/**
* Convenient method to obtain the cell in the given sheet, row and column.
* 
* <p>Creates the row and the cell if they still doesn't already exist.
* Thus, the column can be passed as an int, the method making the needed downcasts.</p>
* 
* @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0)
* @param row thr row number//  w  w w. j  a v  a 2s.com
* @param col the column number
* @return the XSSFCell
*/
protected XSSFCell getCell(XSSFSheet sheet, int row, int col) {
    XSSFRow sheetRow = sheet.getRow(row);
    if (sheetRow == null) {
        sheetRow = sheet.createRow(row);
    }
    XSSFCell cell = sheetRow.getCell((short) col);
    if (cell == null) {
        cell = sheetRow.createCell((short) col);
    }
    return cell;
}

From source file:eremeykin.pete.loader.xlsxdao.XlsxModelDao.java

private String getTableContent(String table) throws DaoException {
    FileInputStream excelFile = null;
    try {/* ww  w  . j  a v a  2  s. co m*/
        excelFile = new FileInputStream(source);
        XSSFWorkbook wb = new XSSFWorkbook(excelFile);
        XSSFSheet sheet = wb.getSheet(table);
        return sheet.getRow(1).getCell(0).getStringCellValue();
    } catch (FileNotFoundException ex) {
        throw new DaoException("Can't find excel file " + source, ex);
    } catch (IOException ex) {
        throw new DaoException("Can't open excel file " + source, ex);
    } finally {
        try {
            if (excelFile != null) {
                excelFile.close();
            }
        } catch (IOException ex) {
            throw new DaoException("Can't close excel file " + source, ex);
        }
    }
}

From source file:eremeykin.pete.loader.xlsxdao.XlsxModelDao.java

private String getTableTillEndContent(String table) throws DaoException {
    FileInputStream excelFile = null;
    try {//from w w w . j  a  va 2s.co  m
        excelFile = new FileInputStream(source);
        XSSFWorkbook wb = new XSSFWorkbook(excelFile);
        XSSFSheet sheet = wb.getSheet(table);
        Integer numRow = sheet.getPhysicalNumberOfRows();
        StringBuffer sb = new StringBuffer();
        for (int i = 1; i < numRow; i++) {
            String s = sheet.getRow(i).getCell(0).getStringCellValue();
            sb.append(sheet.getRow(i).getCell(0).getStringCellValue());
            if (!s.endsWith("\n")) {
                sb.append("\n");
            }
        }
        System.out.println(sb.toString());
        return sb.toString();
        //            return sheet.getRow(1).getCell(0).getStringCellValue();
    } catch (FileNotFoundException ex) {
        throw new DaoException("Can't find excel file " + source, ex);
    } catch (IOException ex) {
        throw new DaoException("Can't open excel file " + source, ex);
    } finally {
        try {
            if (excelFile != null) {
                excelFile.close();
            }
        } catch (IOException ex) {
            throw new DaoException("Can't close excel file " + source, ex);
        }
    }
}

From source file:eremeykin.pete.loader.xlsxdao.XlsxResultSet.java

public XlsxResultSet(XSSFSheet sheet) {
    this.sheet = sheet;
    currRow = sheet.getRow(0);
    for (Cell cell : currRow) {
        colNameMap.put(cell.getStringCellValue(), cell.getColumnIndex());
    }/*from w  w w.j av  a 2 s. c o  m*/
}

From source file:es.SSII2.manager.ExcelManagerAccount.java

public void actualizarCuentas(ArrayList<String> cuentas, ArrayList<String> cuentasCorrectas,
        ArrayList<String> arrayIban, ArrayList<String> pos) throws IOException, ParseException {

    int row, col;
    String originalCuenta, nuevaCuenta, iban, posicion, entidad, oficina, dc, numCuenta;
    String[] a;/*w w  w .  j av a2s  . com*/

    FileInputStream file;
    file = new FileInputStream(new File(excel));

    FileOutputStream outFile;

    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow rowIban;
    XSSFCell cellIban;
    Cell cellCuenta;

    //sacar las cuentas
    for (int i = 0; i < cuentas.size(); i++) {

        originalCuenta = cuentas.get(i);//se coje la cuenta original
        nuevaCuenta = cuentasCorrectas.get(i);//se coje la cuenta actualizada   
        iban = arrayIban.get(i);// cojer el iban

        posicion = pos.get(i);//se coje la posicion
        a = posicion.split("-");//split del string "1-3" de las posiciones que estan el el arraylist
        row = Integer.parseInt(a[0]);//fila "1"
        col = Integer.parseInt(a[1]);//columna "3"

        //insertar el iban
        rowIban = sheet.getRow(row);//coje la fila
        cellIban = rowIban.createCell(1 + col);//crea la celda
        cellIban.setCellValue(iban);

        //2096 0056 16 3231500000
        entidad = nuevaCuenta.substring(0, 4);
        oficina = nuevaCuenta.substring(4, 8);
        dc = nuevaCuenta.substring(8, 10);
        numCuenta = nuevaCuenta.substring(10);

        //actualizar la cuenta si esta mal el cc
        if (!originalCuenta.equals(nuevaCuenta)) {

            cellCuenta = sheet.getRow(row).getCell(col); //obtiene la fila y columna
            DecimalFormat df = new DecimalFormat("#");
            Number cuenta = df.parse(nuevaCuenta);
            cellCuenta.setCellValue(cuenta.doubleValue());

            System.out.println(
                    "Cuenta actualizada: " + iban + "-" + entidad + "-" + oficina + "-" + dc + "-" + numCuenta);

        } else {

            System.out.println(
                    "Cuenta correcta:    " + iban + "-" + entidad + "-" + oficina + "-" + dc + "-" + numCuenta);

        }

    } //for

    outFile = new FileOutputStream(new File(excel));
    //escribe en el excel
    workbook.write(outFile);
    outFile.close();

    file.close();//cierra el archivo  

}