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

Introduction

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

Prototype

@Override
public HSSFSheet createSheet(String sheetname) 

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:de.jwic.ecolib.tableviewer.export.ExcelExportControl.java

License:Apache License

private HSSFWorkbook createWorkBook() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet");
    HSSFRow row = sheet.createRow(0);/*from w  w w .  ja v  a  2 s  .  c  om*/

    // Style for title cells
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.BLUE.index);

    HSSFCellStyle styleTitle = wb.createCellStyle();
    styleTitle.setFont(font);

    // Style for data date cells
    font = wb.createFont();
    HSSFCellStyle styleDate = wb.createCellStyle();
    styleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    short col = 0;
    TableModel model = tableViewer.getModel();
    Iterator<TableColumn> it = model.getColumnIterator();

    // create title in the sheet
    while (it.hasNext()) {
        TableColumn column = it.next();
        if (!isColumnVisible(column)) {
            continue;
        }
        sheet.setColumnWidth(col, (short) (column.getWidth() * 40));
        HSSFCell cell = row.createCell(col++);
        cell.setCellValue(column.getTitle());
        cell.setCellStyle(styleTitle);
    }

    // add the datas from the table viewer
    IContentProvider<?> contentProvider = model.getContentProvider();
    Iterator<?> iter = contentProvider.getContentIterator(new Range());

    try {
        renderRows(iter, 0, model, sheet, styleDate);
    } catch (Throwable t) {
        log.error("Error rendering rows", t);
    }

    return wb;
}

---

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLS.java

License:Open Source License

/**
 * //from  ww  w. j  a va2 s.  c  om
 * @throws FileNotFoundException
 * @throws IOException
 */

public void write() throws FileNotFoundException, IOException {
    FileOutputStream out = new FileOutputStream(new File(filename));
    HSSFWorkbook wb;

    wb = new HSSFWorkbook();

    Map<String, HSSFCellStyle> styles = createStyles(wb);
    HSSFSheet sheet = wb.createSheet(sheetName);

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    HSSFPrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    HSSFRow headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);

    int[][] width = new int[titles.length][titles.length];

    for (int i = 0; i < titles.length; i++) {
        HSSFCell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));

        width[i][0] = titles[i].length();
    }

    HSSFRow row;
    HSSFCell cell;
    int rownum = 1;

    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            if (data[i][j] == null)
                data[i][j] = "";

            cell.setCellValue(data[i][j].toString());

            if (data[i][j].toString().length() > width[j][0])
                width[j][0] = data[i][j].toString().length();
        }
    }

    for (int i = 0; i < titles.length; i++) {
        int widthShort = (256 * (width[i][0] + 3));

        sheet.setColumnWidth(i, widthShort);
    }

    int position = (titles.length / 2) - 1;

    row = sheet.createRow(rownum + 3);
    cell = row.createCell(position);

    if (footName == null) {
        SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm");
        cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis()))
                + " - www.maklerpoint.de");
    } else {
        cell.setCellValue(footName);
    }

    sheet.setZoom(3, 4);

    wb.write(out);
    out.close();
}

---

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportKalenderExcel.java

License:Open Source License

/**
 * //ww w  .j a  v  a  2s  .  c  om
 * @throws FileNotFoundException
 * @throws IOException
 */

public void write() throws FileNotFoundException, IOException {
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    HSSFWorkbook wb = new HSSFWorkbook();
    Map<String, HSSFCellStyle> styles = createStyles(wb);

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        HSSFSheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        HSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short) 1);
        printSetup.setFitWidth((short) 1);

        //the header row: centered text in 48pt font
        HSSFRow headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        HSSFCell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        //                sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        HSSFRow monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //set column widths, the width is measured in units of 1/256th of a character width
            sheet.setColumnWidth((i * 2), (5 * 256)); //the column is 5 characters wide
            sheet.setColumnWidth((i * 2 + 1), (13 * 256)); //the column is 13 characters wide
            //sheet.addMergedRegion(new Region(1, (short) 1, i*2, (short) (i * 2 + 1)));
            sheet.addMergedRegion(new CellRangeAddress(1, i * 2, 1, (i * 2 + 1))); // TODO Test
            HSSFCell monthCell = monthRow.createCell((i * 2));
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            HSSFRow row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                HSSFCell dayCell_1 = row.createCell((i * 2));
                HSSFCell dayCell_2 = row.createCell((i * 2 + 1));

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file        

    FileOutputStream out = new FileOutputStream(this.filename);
    wb.write(out);
    out.close();
}

---

From source file:de.powerstaff.web.backingbean.profile.ProfileBackingBean.java

License:Open Source License

public void commandSearchExportExcel() {
    try {/*from w  ww .ja  v a 2  s  .co m*/
        FacesContext theContext = FacesContext.getCurrentInstance();

        ExternalContext externalContext = theContext.getExternalContext();
        HttpServletResponse response = (HttpServletResponse) externalContext.getResponse();

        response.reset(); // Some JSF component library or some Filter might have set some headers in the buffer beforehand. We want to get rid of them, else it may collide.
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment; filename=\"ExportSuche.xls\"");

        HSSFWorkbook theWorkbook = new HSSFWorkbook();
        HSSFSheet theWorkSheet = theWorkbook.createSheet("ExportSuche");

        HSSFCellStyle theDateStyle = theWorkbook.createCellStyle();
        theDateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("d/m/jj"));

        int aRow = 0;
        // Header
        HSSFRow theRow = theWorkSheet.createRow(aRow++);
        ExcelUtils.addCellToRow(theRow, 0, "Anrede");
        ExcelUtils.addCellToRow(theRow, 1, "Name1");
        ExcelUtils.addCellToRow(theRow, 2, "Name2");
        ExcelUtils.addCellToRow(theRow, 3, "eMail");
        ExcelUtils.addCellToRow(theRow, 4, "Code");
        ExcelUtils.addCellToRow(theRow, 5, "Verfgbarkeit");
        ExcelUtils.addCellToRow(theRow, 6, "Satz");
        ExcelUtils.addCellToRow(theRow, 7, "Plz");
        ExcelUtils.addCellToRow(theRow, 8, "Letzter Kontakt");
        ExcelUtils.addCellToRow(theRow, 9, "Skills");
        ExcelUtils.addCellToRow(theRow, 10, "Tags");

        // Rows
        PagedListDataModel<ProfileSearchEntry> theData = getData().getSearchResult();
        for (int i = 0; i < theData.getRowCount() && i < profileSearchService.getPageSize(); i++) {
            theData.setRowIndex(i);

            ProfileSearchEntry theDataRow = (ProfileSearchEntry) theData.getRowData();

            Freelancer theFreelancer = freelancerService.findByPrimaryKey(theDataRow.getFreelancer().getId());

            String theSkills = ExcelUtils.saveObject(
                    theFreelancer.getSkills().replace("\f", "").replace("\n", "").replace("\t", ""));

            StringBuilder theTagList = new StringBuilder();
            for (FreelancerToTag theTagAssignment : theFreelancer.getTags()) {
                if (theTagList.length() > 0) {
                    theTagList.append(" ");
                }
                theTagList.append(theTagAssignment.getTag().getName());
            }

            HSSFRow theFreelancerRow = theWorkSheet.createRow(aRow++);
            ExcelUtils.addCellToRow(theFreelancerRow, 0, ExcelUtils.saveObject(theFreelancer.getTitel()));
            ExcelUtils.addCellToRow(theFreelancerRow, 1, ExcelUtils.saveObject(theFreelancer.getName1()));
            ExcelUtils.addCellToRow(theFreelancerRow, 2, ExcelUtils.saveObject(theFreelancer.getName2()));
            ExcelUtils.addCellToRow(theFreelancerRow, 3,
                    ExcelUtils.saveObject(theFreelancer.getFirstContactEMail())); // eMail
            ExcelUtils.addCellToRow(theFreelancerRow, 4, ExcelUtils.saveObject(theFreelancer.getCode()));
            ExcelUtils.addCellToRow(theFreelancerRow, 5,
                    ExcelUtils.saveObject(theFreelancer.getAvailabilityAsDate()), theDateStyle);
            ExcelUtils.addCellToRow(theFreelancerRow, 6, ExcelUtils.saveObject(theFreelancer.getSallaryLong()));
            ExcelUtils.addCellToRow(theFreelancerRow, 7, ExcelUtils.saveObject(theFreelancer.getPlz()));
            ExcelUtils.addCellToRow(theFreelancerRow, 8,
                    ExcelUtils.saveObject(theFreelancer.getLastContactDate()), theDateStyle);
            ExcelUtils.addCellToRow(theFreelancerRow, 9, ExcelUtils.saveObject(theSkills));
            ExcelUtils.addCellToRow(theFreelancerRow, 10, theTagList.toString());
        }

        theWorkbook.write(response.getOutputStream());

        theContext.responseComplete(); // Important!

    } catch (Exception e) {
        JSFMessageUtils.addGlobalErrorMessage(MSG_FEHLERBEIDERPROFILSUCHE, e.getMessage());
        LOGGER.error("Fehler bei Profilsuche", e);
    }
}

---

From source file:demo.admin.controller.UserController.java

@RequestMapping(value = "/user/downloadData")
@VerifyAuthentication(Trader = true, Admin = true, Operation = true)
public HttpEntity<byte[]> downloadUserData(String status, String securephone,
        @RequestParam(value = "startDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate,
        @RequestParam(value = "endDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate)
        throws IOException, DocumentException {
    if (securephone != null && securephone != "") {
        securephone = Where.$like$(securephone);
    }/*from  w  ww . ja  v a2s.c  om*/
    List<Map<String, Object>> users = userMapper.userExport(status, securephone, startDate, endDate);
    String type = status + "?";
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(type);
    HSSFRow row = sheet.createRow(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    sheet.setVerticallyCenter(true);
    sheet.setHorizontallyCenter(true);
    String[] excelHeader = { "??", "???", "??", "", "??" };
    for (int i = 0; i < excelHeader.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellValue(excelHeader[i]);
        cell.setCellStyle(cellStyle);
        sheet.autoSizeColumn(i, true);
    }
    for (int i = 0; i < users.size(); i++) {
        Map<String, Object> resultSet = users.get(i);
        sheet.autoSizeColumn(i, true);
        row = sheet.createRow(i + 1);
        row.setRowStyle(cellStyle);
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(String.valueOf(resultSet.get("name")));
        row.createCell(2).setCellValue(String.valueOf(resultSet.get("address")));
        row.createCell(3).setCellValue(String.valueOf(resultSet.get("registertime")));
        row.createCell(4).setCellValue(String.valueOf(resultSet.get("securephone")));

    }
    File file = File.createTempFile(".xls", ".xls");
    OutputStream out = new FileOutputStream(file);
    wb.write(out);
    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    headers.setContentDispositionFormData("attachment",
            URLEncoder.encode(type, "UTF-8") + LocalDate.now() + ".xls");
    return new HttpEntity<byte[]>(FileUtils.readFileToByteArray(file), headers);
}

---

From source file:demo.poi.Outlines.java

License:Apache License

private static void createCase1(String filename) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");

    sheet1.groupColumn(4, 7);/*  w  w w  .  j  ava 2 s  .c o  m*/

    for (int row = 0; row < 200; row++) {
        HSSFRow r = sheet1.createRow(row);
        for (int column = 0; column < 200; column++) {
            HSSFCell c = r.createCell(column);
            c.setCellValue(column);
        }
    }

    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    fileOut.close();
}

---

From source file:demo.poi.Outlines.java

License:Apache License

private static void createCase2(String filename) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");

    sheet1.groupColumn(2, 10);//from  w  ww . j  a  v a2  s . co m
    sheet1.groupColumn(4, 7);
    sheet1.setColumnGroupCollapsed(4, true);

    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    fileOut.close();
}

---

From source file:demo.poi.Outlines.java

License:Apache License

private static void createCase3(String filename) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");

    sheet1.groupColumn(2, 10);/*from w w  w  .j av a 2s  . c om*/
    sheet1.groupColumn(4, 7);
    sheet1.setColumnGroupCollapsed(4, true);
    sheet1.setColumnGroupCollapsed(2, true);

    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    fileOut.close();
}

---

From source file:demo.poi.Outlines.java

License:Apache License

private static void createCase4(String filename) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");

    sheet1.groupColumn(2, 10);//from ww w.  j av  a2  s  .c om
    sheet1.groupColumn(4, 7);
    sheet1.setColumnGroupCollapsed(4, true);
    sheet1.setColumnGroupCollapsed(2, true);

    sheet1.setColumnGroupCollapsed(4, false);

    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    fileOut.close();
}

---

From source file:demo.poi.Outlines.java

License:Apache License

private static void createCase5(String filename) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("new sheet");

    sheet1.groupColumn(2, 10);// w w  w  . j a v  a2 s .c  o  m
    sheet1.groupColumn(4, 7);
    sheet1.setColumnGroupCollapsed(4, true);
    sheet1.setColumnGroupCollapsed(2, true);

    sheet1.setColumnGroupCollapsed(4, false);
    sheet1.setColumnGroupCollapsed(3, false);

    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    fileOut.close();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• »