Example usage for org.apache.poi.xssf.streaming SXSSFWorkbook getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.xssf.streaming SXSSFWorkbook getSheetAt.

Prototype

@Override
public SXSSFSheet getSheetAt(int index) 

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.softtek.mdm.web.institution.UserController.java

/**
  * //  w w w  .j a  va  2  s  .co m
  * 
  * @param request
  * @param response
  * @throws Exception
  */
@RequestMapping(value = "/exportuser", method = RequestMethod.GET)
@ResponseBody
public void exportuser(String groupid, HttpServletRequest request, HttpServletResponse response,
        HttpSession session) throws Exception {
    @SuppressWarnings("unchecked")
    List<StructureModel> list = (List<StructureModel>) session
            .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString());
    Integer id = Integer.parseInt(groupid);
    String name = structureService.queryNameById(id);
    request.setAttribute("name", name);
    List<Integer> idList = new ArrayList<Integer>();
    structureService.queryAllChildrenId(id, list, idList);
    idList.add(id);
    // ?ID?
    List<UserExportModel> userExportModel = userService.exportUsersById(idList);
    // ??
    for (int i = 0; i < idList.size(); i++) {
        StructureModel temp = structureService.getParents(idList.get(i));
        String belongStr = temp.getName();
        if (temp != null) {
            for (int j = 0; j < userExportModel.size(); j++) {
                if (userExportModel.get(j).getGroup_id().equals(idList.get(i))) {
                    // String belongStr = temp.getName();
                    while (temp.getParent() != null) {
                        belongStr = StringUtil.insert(belongStr, temp.getParent().getName() + "/");
                        temp = temp.getParent();
                    }
                    userExportModel.get(j).setGroup_name(belongStr);
                }
            }
        }
    }

    ExportData exportData = new ExportData();
    String headers[][] = {
            { messageSource.getMessage("web.institution.usercontroller.export.users.label1", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label2", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label3", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label4", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label5", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label6", null,
                    LocaleContextHolder.getLocale()), "String" },
            { messageSource.getMessage("web.institution.usercontroller.export.users.label7", null,
                    LocaleContextHolder.getLocale()), "String" } };
    SXSSFWorkbook workbook = exportData.getwb(headers, "sheet1");
    int currentRow = 1;
    Sheet sheet = workbook.getSheetAt(0);
    CellStyle cellStyle = workbook.createCellStyle();
    // cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    for (int i = 0; i < userExportModel.size(); i++) {
        Row row = sheet.createRow(currentRow);
        for (int j = 0; j < 7; j++) {
            Cell cell = row.createCell(j);
            if (j == 0) {
                cell.setCellValue(userExportModel.get(i).getGroup_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 1) {
                cell.setCellValue(userExportModel.get(i).getUser_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 2) {
                cell.setCellValue(userExportModel.get(i).getReal_name());
                cell.setCellStyle(cellStyle);
            }
            if (j == 3) {
                cell.setCellValue(userExportModel.get(i).getPhone());
                cell.setCellStyle(cellStyle);
            }
            if (j == 4) {
                cell.setCellValue(userExportModel.get(i).getEmail());
                cell.setCellStyle(cellStyle);
            }
            if (j == 5) {
                cell.setCellValue(userExportModel.get(i).getMark());
                cell.setCellStyle(cellStyle);
            }
            if (j == 6) {
                String sex = messageSource.getMessage("web.institution.usercontroller.sex.woman", null,
                        LocaleContextHolder.getLocale());

                if (userExportModel.get(i).getGender().equals("1")) {
                    sex = messageSource.getMessage("web.institution.usercontroller.sex.man", null,
                            LocaleContextHolder.getLocale());
                }

                cell.setCellValue(sex);
                cell.setCellStyle(cellStyle);
            }
        }
        currentRow++;
    }
    // XSSFWorkbook workbook = new XSSFWorkbook();
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/x-msdownload");
    OutputStream os = null;
    String fileName = messageSource.getMessage("web.institution.usercontroller.export.users.model", null,
            LocaleContextHolder.getLocale());
    response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
    os = response.getOutputStream();
    workbook.write(os);
    os.flush();
    os.close();
}

---

From source file:org.riflemansd.businessprofit.excel.ExcelExamplePOI.java

License:Open Source License

public static void main(String[] args) throws Throwable {
    SXSSFWorkbook wb = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows will be flushed to disk

    if (wb.getNumberOfSheets() == 0) {
        wb.createSheet("MySheet");
    }/*from  w w w  .  j a  va 2s.c o  m*/
    Sheet sh = wb.getSheetAt(0);
    Row row = sh.createRow(3);

    for (int i = 0; i < 10; i++) {
        Cell cell = row.createCell(i);
        //String address = new CellReference(cell).formatAsString();
        cell.setCellValue("? " + i);
        //row.setHeightInPoints(50);
        //sh.setColumnWidth(5, 1200); //4, 33 pixels
        wb.getSheetAt(0).autoSizeColumn(i);
    }

    FileOutputStream out = new FileOutputStream("test.xlsx");
    wb.write(out);
    out.close();

    // dispose of temporary files backing this workbook on disk
    wb.dispose();

    Desktop.getDesktop().open(new File("test.xlsx"));
}

---