Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt.

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:com.vportal.portlet.vdoc.action.VDocManage.java

License:Open Source License

public void exportToExcel(ActionRequest request, ActionResponse response) throws IOException {
    int fromDay = ParamUtil.getInteger(request, "fromDay");
    int fromMonth = ParamUtil.getInteger(request, "fromMonth");
    int fromYear = ParamUtil.getInteger(request, "fromYear");
    String cmd = ParamUtil.getString(request, "cmd");
    int toDay = ParamUtil.getInteger(request, "toDay");
    int toMonth = ParamUtil.getInteger(request, "toMonth");
    int toYear = ParamUtil.getInteger(request, "toYear");

    long groupId = ParamUtil.getLong(request, "groupId");
    String byUser = ParamUtil.getString(request, "byUser");

    Date dateFrom = new GregorianCalendar(fromYear, fromMonth, fromDay).getTime();
    Date dateTo = new GregorianCalendar(toYear, toMonth, toDay).getTime();

    String language = ParamUtil.getString(request, "language");
    String parentId = ParamUtil.getString(request, "parentId");

    long statusId = ParamUtil.getLong(request, "statusId", 2);
    long typeId = ParamUtil.getLong(request, "byType");

    int begin = ParamUtil.getInteger(request, "begin");
    int end = ParamUtil.getInteger(request, "end");

    ThemeDisplay themeDisplay = (ThemeDisplay) request.getAttribute(WebKeys.THEME_DISPLAY);
    Layout layout = themeDisplay.getLayout();
    long companyId = layout.getCompanyId();
    FileInputStream fis = null;/*from w w  w . ja v  a  2 s.  com*/
    String porttalDir = PortalUtil.getPortalWebDir();
    if (cmd.equals("reportBydate")) {
        try {

            fis = new FileInputStream(porttalDir.substring(0, porttalDir.indexOf("ROOT"))
                    + "//VDoc-portlet//html//vdoc_manage//statistics//file//reportByDateTemplate.xls");

            POIFSFileSystem fs = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            // wb.createSheet();
            HSSFSheet sheet = wb.getSheetAt(0);

            setReportByDate(7, 0, sheet, wb, groupId, language, (int) statusId, dateFrom, dateTo);
            String strDir = "//VDoc-portlet//html//vdoc_manage//statistics//file//reportByDate.xls";
            getFile(PortalUtil.getHttpServletRequest(request), PortalUtil.getHttpServletResponse(response),
                    strDir, cmd);
        } catch (Exception ex) {
            ex.toString();
        } finally {
            fis.close();
        }
    } else {
        fis = new FileInputStream(porttalDir.substring(0, porttalDir.indexOf("ROOT"))
                + "//VDoc-portlet//html//vdoc_manage//statistics//file//reportByOrgTemplate.xls");

        POIFSFileSystem fs = new POIFSFileSystem(fis);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        // wb.createSheet();
        HSSFSheet sheet = wb.getSheetAt(0);

        try {
            setReportByOrg(7, 0, sheet, wb, groupId, language, (int) statusId);
            String strDir = "//VDoc-portlet//html//vdoc_manage//statistics//file//reportByOrg.xls";
            getFile(PortalUtil.getHttpServletRequest(request), PortalUtil.getHttpServletResponse(response),
                    strDir, cmd);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

From source file:com.wangzhu.poi.ExcelToHtmlConverter.java

License:Apache License

public void processWorkbook(HSSFWorkbook workbook) {
    final SummaryInformation summaryInformation = workbook.getSummaryInformation();
    if (summaryInformation != null) {
        this.processDocumentInformation(summaryInformation);
    }//w ww.  j a va  2 s  . co m

    if (this.isUseDivsToSpan()) {
        // prepare CSS classes for later usage
        this.cssClassContainerCell = this.htmlDocumentFacade.getOrCreateCssClass(this.cssClassPrefixCell,
                "padding:0;margin:0;align:left;vertical-align:top;");
        this.cssClassContainerDiv = this.htmlDocumentFacade.getOrCreateCssClass(this.cssClassPrefixDiv,
                "position:relative;");
    }

    for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
        HSSFSheet sheet = workbook.getSheetAt(s);
        this.processSheet(sheet);
    }

    this.htmlDocumentFacade.updateStylesheet();
}

From source file:com.web.mavenproject6.other.XLSParser.java

public static String parse(String name) {

    String result = "";
    InputStream in = null;/*from   w  w  w . j av  a  2  s .  com*/
    HSSFWorkbook wb = null;
    try {
        in = new FileInputStream(name);
        wb = new HSSFWorkbook(in);
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.println("!!!!sheet count:" + wb.getNumberOfSheets());
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        System.out.println("!!!!sheet[" + i + "]:" + wb.getSheetName(i));
    }
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        Iterator<Row> it = sheet.iterator();
        String buf = "";
        while (it.hasNext()) {
            Row row = it.next();

            //result += row.getRowNum() + ":";
            switch (row.getRowNum() + 1) {
            case 13:
                result += readCell(row);
                result += "<br>";
                break;
            case 14:
                result += readCell(row);
                result += "<br>";
                break;
            case 15:
                result += readCell(row);
                break;
            case 16:
                result += readCell(row);
                result += "<br>";
                break;
            case 18:
                result += readCell(row);
                break;
            case 19:
                result += readCell(row);
                break;
            case 20:
                result += readCell(row);
                break;
            case 21:
                result += readCell(row);
                break;
            case 22:
                result += readCell(row);
                result += "<br>";
                break;
            case 25:
                result += readCell(row);
                break;
            case 26:
                result += readCell(row);
                result += "<br>";
                break;
            default:
                result += readCell(row);
                result += "<br>";
                break;
            }

            //            if (row.getRowNum() + 1 != 34 || row.getRowNum() + 1 != 34) {
            //                result += readCell(row);
            //            } else {
            //                result += "[" + readCell(row, 0, 7) + "][" + readCell(row, 7, 10);
            //            }
        }
    }
    return result;
}

From source file:com.web.mavenproject6.other.XLSParser.java

public static String parseStatisticDoc(String name) {

    String result = "";
    InputStream in = null;//from  ww w  .  j  a v  a2s  .  c om
    HSSFWorkbook wb = null;
    try {
        in = new FileInputStream(name);
        wb = new HSSFWorkbook(in);
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.println("!!!!sheet count:" + wb.getNumberOfSheets());
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        System.out.println("!!!!sheet[" + i + "]:" + wb.getSheetName(i));
    }

    Sheet sheet = wb.getSheetAt(0);
    Iterator<Row> it = sheet.iterator();
    while (it.hasNext()) {
        Row row = it.next();
        Iterator<Cell> cells = row.iterator();

        //  row.getCell(0).getStringCellValue()
        switch (row.getRowNum()) {

        }
        result += "\n";
    }

    return result;
}

From source file:com.webbfontaine.valuewebb.report.AirCargoReporter.java

License:Open Source License

private byte[] getXLS() throws SQLException, IOException {

    try (Connection connection = getConnection();
            PreparedStatement ps = getPreparedStatement(connection);
            ResultSet rs = ps.executeQuery();
            InputStream is = getTemplateFileStream(getReportFilePath())) {

        rs.setFetchSize(1000);/*from w  w w  .  jav  a 2s . c om*/

        HSSFWorkbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        Map<String, CellStyle> cellStyleMap = getAllCellStyles(workbook);
        int rowsCreated = createCells(rs, cellStyleMap, sheet);

        if (rowsCreated == DEFAULT_STARTING_ROW_NUMBER) {
            LOGGER.debug("Report is empty");
            return null;
        }

        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }
}

From source file:com.webbfontaine.valuewebb.report.fcvrprocessing.FcvrTimeReportEngine.java

License:Open Source License

private static int createRows(HSSFWorkbook workbook, ResultSet resultSet) throws SQLException {
    Sheet sheet1 = workbook.getSheetAt(0);
    Sheet sheet2 = workbook.getSheetAt(1);
    int rowNumber = DEFAULT_STARTING_ROW_NUMBER;
    Map<CellType, CellStyle> cellStyleMap = retrieveNecessaryStyles(workbook);

    ProcessingTime processingTime = new ProcessingTime();
    while (resultSet.next()) {
        Row sheet1Row = sheet1.createRow(rowNumber);
        Row sheet2Row = sheet2.createRow(rowNumber);
        processingTime.calculate(resultSet.getLong("tt_id"));

        CellStyleUtils.createCell(sheet1Row, 'A', resultSet.getString("fcvr_num"),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'B', resultSet.getString("imp_nam"),
                cellStyleMap.get(CellType.TEXT_LEFT));
        CellStyleUtils.createCell(sheet1Row, 'C', resultSet.getBigDecimal("fob_assessed"),
                cellStyleMap.get(CellType.NUMBER_RIGHT));
        CellStyleUtils.createCell(sheet1Row, 'D', resultSet.getString("inv_cur"),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'E', resultSet.getDate("tt_dat"),
                cellStyleMap.get(CellType.DATE_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'F', resultSet.getDate("fcvr_dat"),
                cellStyleMap.get(CellType.DATE_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'G', resultSet.getInt("num_of_queries"),
                cellStyleMap.get(CellType.DIGIT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'H', resultSet.getInt("num_of_holidays"),
                cellStyleMap.get(CellType.DIGIT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'I', resultSet.getInt("num_of_weekends"),
                cellStyleMap.get(CellType.DIGIT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'J',
                Utils.convertProcessingTimeFromLongToString(processingTime.getProcessingTimeWithQuery()
                        - processingTime.getProcessingTimeWithoutQuery()),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'K',
                Utils.convertProcessingTimeFromLongToString(processingTime.getProcessingTimeWithQuery()),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet1Row, 'L', resultSet.getString("status"),
                cellStyleMap.get(CellType.TEXT_CENTER));

        CellStyleUtils.createCell(sheet2Row, 'A', resultSet.getString("fcvr_num"),
                cellStyleMap.get(CellType.TEXT_CENTER));
        CellStyleUtils.createCell(sheet2Row, 'B', resultSet.getString("containers"),
                cellStyleMap.get(CellType.TEXT_LEFT));

        rowNumber++;/*  www  . ja  v  a  2 s . c  om*/
    }

    return rowNumber;
}

From source file:com.webbfontaine.valuewebb.report.ScanSelectivityReporter.java

License:Open Source License

private byte[] processXls(InputStream is, ResultSet rs) throws IOException, SQLException {
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    Sheet sheet = workbook.getSheetAt(0);

    Map<String, CellStyle> cellStyleMap = getAllCellStyles(workbook);
    int rowsCreated = createCells(rs, cellStyleMap, sheet);

    if (rowsCreated == DEFAULT_STARTING_ROW_NUMBER) {
        LOGGER.debug("Report is empty");
        return null;
    }/*from  ww  w.ja va2 s.  c o  m*/

    try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
        workbook.write(outputStream);
        return outputStream.toByteArray();
    }
}

From source file:com.weibo.datasys.parser.office.extractor.ExcelParse.java

License:Open Source License

private FileData extractor(File filePath) {
    FileData fData = new FileData();
    fData.setName(filePath.getName());/*  w ww. j  a  v  a  2  s  . com*/
    StringBuffer sBuffer = new StringBuffer();
    HSSFWorkbook workbook = null;
    try {
        workbook = new HSSFWorkbook(new FileInputStream(filePath));
        for (int iSheets = 0; iSheets < workbook.getNumberOfSheets(); ++iSheets) {
            HSSFSheet sheet = workbook.getSheetAt(iSheets);
            for (int iRow = 0; iRow < sheet.getLastRowNum(); ++iRow) {
                HSSFRow row = sheet.getRow(iRow);
                for (int iCell = 0; iCell < row.getLastCellNum(); ++iCell) {
                    HSSFCell cell = row.getCell(iCell);
                    if (null != cell) {
                        if (0 == cell.getCellType()) {
                            sBuffer.append(String.valueOf(cell.getNumericCellValue()));
                            sBuffer.append(SEGMENT_CHAR);
                        } else if (1 == cell.getCellType()) {
                            sBuffer.append(cell.getStringCellValue().trim());
                            sBuffer.append(SEGMENT_CHAR);
                        }
                    }
                }
            }
        }
        fData.setContent(sBuffer.toString());
    } catch (Exception e) {
        LOG.error("", e);
    }
    return fData;
}

From source file:com.xx.platform.util.tools.ms.ExcelExtractor.java

License:Apache License

public String extractText(POIFSFileSystem poifs) throws Exception {
    StringBuffer resultText = new StringBuffer();
    HSSFWorkbook wb = new HSSFWorkbook(poifs, true);
    if (wb == null) {
        return "";
    }/* w w  w.j a v  a2  s.  c o m*/

    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    int sNum = 0;
    int rNum = 0;
    int cNum = 0;

    sNum = wb.getNumberOfSheets();

    for (int i = 0; i < sNum; i++) {
        if ((sheet = wb.getSheetAt(i)) == null) {
            continue;
        }
        rNum = sheet.getLastRowNum();

        for (int j = 0; j <= rNum; j++) {
            if ((row = sheet.getRow(j)) == null) {
                continue;
            }
            cNum = row.getLastCellNum();

            for (int k = 0; k < cNum; k++) {
                if ((cell = row.getCell((short) k)) != null) {
                    /*if(HSSFDateUtil.isCellDateFormatted(cell) == true) {
                        resultText += cell.getDateCellValue().toString() + " ";
                      } else
                     */
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        resultText.append(cell.getStringCellValue());
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        Double d = new Double(cell.getNumericCellValue());
                        resultText.append(d.toString());
                    }
                    /* else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
                         resultText += cell.getCellFormula() + " ";
                       }
                     */
                }
            }
        }
    }
    return resultText.toString();
}

From source file:com.xx.platform.util.tools.ms.ExcelExtrator.java

License:Apache License

public String extractText(POIFSFileSystem poifs) throws Exception {
    List<Map<String, String>> list = new ArrayList<Map<String, String>>();
    String text = null;//from   ww  w  .j  a  v  a2 s  . c o  m
    try {
        HSSFWorkbook wb = new HSSFWorkbook(poifs, true);
        if (wb == null) {
            return null;
        }

        HSSFSheet sheet;
        HSSFRow row;
        HSSFCell cell;
        int sNum = 0;
        int rNum = 0;
        int cNum = 0;

        sNum = wb.getNumberOfSheets();
        for (int i = 0; i < sNum; i++) {
            if ((sheet = wb.getSheetAt(i)) == null) {
                continue;
            }
            String[] key = null; //field
            boolean init = false; //key 
            rNum = sheet.getLastRowNum();
            for (int j = 0; j <= rNum; j++) {
                if ((row = sheet.getRow(j)) == null) {
                    continue;
                }
                Map<String, String> rowdata = new HashMap<String, String>();
                cNum = row.getLastCellNum();
                if (!init)
                    key = new String[cNum];
                String value = "";
                StringBuffer content = new StringBuffer();
                for (int k = 0; k < cNum; k++) {
                    if ((cell = row.getCell((short) k)) != null) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            value = cell.getStringCellValue();
                        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            Double d = new Double(cell.getNumericCellValue());
                            value = d.toString();
                        } else
                            value = "";
                        if (init) {
                            content.append(value);
                        } else {
                            key[k] = value;
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        text = "";
    }
    return text;

}