Example usage for org.apache.poi.ss.usermodel Sheet getPrintSetup

List of usage examples for org.apache.poi.ss.usermodel Sheet getPrintSetup

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getPrintSetup

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getPrintSetup.

Prototype

PrintSetup getPrintSetup();

Source Link

Document

Gets the print setup object.

Usage

From source file:com.griffinslogistics.document.excel.BookLabelGenerator.java

private static void generate(XSSFWorkbook workbook, BookLabelModel bookLabelModel) {
    Map<String, CellStyle> styles = createStyles(workbook);
    String title = bookLabelModel.getTitle().replace("/", "-");
    bookLabelModel.setTitle(title);//  ww w  .ja v a  2  s.com
    //        Sheet sheet = workbook.createSheet(bookLabelModel.getBookNumber() + " " + bookLabelModel.getTitle());
    Sheet sheet = workbook.createSheet();

    for (int i = 0; i < 20; i++) {
        sheet.createRow(i);
    }

    generateHeaders(sheet, styles);
    generateAddress(sheet, styles, bookLabelModel);
    generateClient(sheet, styles, bookLabelModel);
    generateTransportation(sheet, styles, bookLabelModel);
    generateTitle(sheet, styles, bookLabelModel);
    generateCountPerBox(sheet, styles, bookLabelModel);
    generateCountPerAddress(sheet, styles, bookLabelModel);

    sheet.setColumnWidth(1, 20000);
    sheet.setColumnWidth(4, 20000);
    sheet.autoSizeColumn(0, false);
    sheet.autoSizeColumn(3, false);
    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
    sheet.setFitToPage(true);
}

From source file:com.griffinslogistics.excel.BDLGenerator.java

public static void generateAll(OutputStream outputStream,
        Map<String, List<BookBoxModel>> bookBoxModelsForTransportation, Pulsiodetails pulsioDetails,
        String packageNumber) {/*from w ww  .  j a  v  a 2s .  co  m*/
    try {
        Workbook workbook = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(workbook);

        for (String bookspackageNumber : bookBoxModelsForTransportation.keySet()) {

            Sheet sheet = workbook.createSheet(bookspackageNumber);

            //Default sheet styles
            sheet.setDisplayGridlines(false);
            sheet.setPrintGridlines(false);
            sheet.getPrintSetup().setLandscape(true);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);
            sheet.setDefaultRowHeight((short) 300);
            sheet.setDefaultColumnWidth(15);

            sheet.setColumnWidth(1, 6000);
            sheet.setColumnWidth(5, 3000);
            sheet.setColumnWidth(6, 3000);
            sheet.setColumnWidth(7, 3000);
            sheet.setColumnWidth(8, 3000);

            // Generate header part
            insertPulsioImage(workbook, sheet, pulsioDetails);
            insertDate(sheet, styles.get("title"));
            insertContacts(sheet, styles.get("pulsioName"), styles.get("contacts"), pulsioDetails);
            insertTitle(sheet, styles.get("title"));

            // Generate table part
            insertTableHeaders(sheet, styles.get("tableHeadersLeft"), styles.get("tableHeadersMiddle"),
                    styles.get("tableHeadersRight"));
            List<BookBoxModel> bookBoxModels = bookBoxModelsForTransportation.get(bookspackageNumber);
            int index = insertTableBody(sheet, styles.get("tableBodyLeft"), styles.get("tableBodyMiddle"),
                    styles.get("tableBodyRight"), styles.get("tableFooters"), bookBoxModels);

            String deliveryAddress = bookBoxModels.get(0).getDeliveryAddress();
            String client = bookBoxModels.get(0).getClient();
            insertFooter(sheet, styles.get("footer"), index, bookspackageNumber, deliveryAddress, client);
        }

        workbook.write(outputStream);
    } catch (FileNotFoundException ex) {
        logger.log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        logger.log(Level.SEVERE, null, ex);
    }
}

From source file:com.griffinslogistics.excel.BDLGenerator.java

public static OutputStream generateSingle(OutputStream outputStream, List<BookBoxModel> bookBoxModels,
        Pulsiodetails pulsiodetails, String packageNumber) {

    try {/*w  w  w.  ja  va2 s  .  c o  m*/
        Workbook workbook = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(workbook);
        Sheet sheet = workbook.createSheet("Bon de livraison");

        //Default sheet styles
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.getPrintSetup().setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.setDefaultRowHeight((short) 300);
        sheet.setDefaultColumnWidth(15);

        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(5, 3000);
        sheet.setColumnWidth(6, 3000);
        sheet.setColumnWidth(7, 3000);
        sheet.setColumnWidth(8, 3000);

        // Generate header part
        insertPulsioImage(workbook, sheet, pulsiodetails);
        insertDate(sheet, styles.get("title"));
        insertContacts(sheet, styles.get("pulsioName"), styles.get("contacts"), pulsiodetails);
        insertTitle(sheet, styles.get("title"));

        // Generate table part
        insertTableHeaders(sheet, styles.get("tableHeadersLeft"), styles.get("tableHeadersMiddle"),
                styles.get("tableHeadersRight"));
        int index = insertTableBody(sheet, styles.get("tableBodyLeft"), styles.get("tableBodyMiddle"),
                styles.get("tableBodyRight"), styles.get("tableFooters"), bookBoxModels);

        String deliveryAddress = bookBoxModels.get(0).getDeliveryAddress();
        String client = bookBoxModels.get(0).getClient();

        insertFooter(sheet, styles.get("footer"), index, packageNumber, deliveryAddress, client);

        workbook.write(outputStream);
    } catch (FileNotFoundException ex) {
        logger.log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        logger.log(Level.SEVERE, null, ex);
    }

    return outputStream;
}

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/*from  w  w w . j av  a 2 s. c  o m*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

    //turn off gridlines
    sheet.setDisplayGridlines(true);
    sheet.autoSizeColumn(0);
    sheet.setPrintGridlines(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                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
    String file = RutaFile;
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    wb.close();

}

From source file:com.inet.web.service.mail.utils.ExportUtils.java

License:Open Source License

/**
 * /* w w  w  .  j  av a2  s .  c o m*/
 * @param users
 * @param domain
 * @return
 * @throws WebOSException
 */
public static byte[] exportAccount(List<LdapUser> users, String domain) throws WebOSException {
    try {
        ByteArrayOutputStream output = new ByteArrayOutputStream();

        Workbook workbook = new HSSFWorkbook();
        //Map<String, CellStyle> styles = createStyles(workbook);

        Sheet sheet = workbook.createSheet("Email list");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        for (int i = 0; i < users.size(); i++) {
            writeRecordAccount(sheet, users.get(i), i + 1);
        }
        workbook.write(output);
        output.close();

        return output.toByteArray();
    } catch (Exception e) {
        e.printStackTrace();
        throw new WebOSException(e.getMessage(), e);
    }
}

From source file:com.inet.web.service.mail.utils.ExportUtils.java

License:Open Source License

/**
 * // w  w w.j  av  a2  s  .c o  m
 * @param key
 * @return
 * @throws WebOSException
 */
public static byte[] exportErrorAccount(String key) throws WebOSException {
    AccountImport accountImport = AccountImportCacheService.get(key);
    if (accountImport == null) {
        return null;
    }
    try {
        ByteArrayOutputStream output = new ByteArrayOutputStream();
        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet("Email list");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(30);
        headerRow.createCell(STT).setCellValue("STT");
        headerRow.createCell(FULL_NAME).setCellValue("FULL NAME");
        headerRow.createCell(USER).setCellValue("USER");
        headerRow.createCell(LAST_NAME).setCellValue("LAST NAME");
        headerRow.createCell(MIDDLE_NAME).setCellValue("MIDDLE NAME");
        headerRow.createCell(FIRST_NAME).setCellValue("FIRST NAME");
        headerRow.createCell(STATUS).setCellValue("STATUS");
        headerRow.createCell(DUPLICATE).setCellValue("DUPLICATE");

        for (int i = 0; i < accountImport.getError().size(); i++) {
            AccountImportInfo account = accountImport.getError().get(i);

            Row row = sheet.createRow(i + 1);
            row.setHeightInPoints(40);
            row.createCell(STT).setCellValue(account.getNumber());
            row.createCell(FULL_NAME).setCellValue(account.getFullName());
            row.createCell(USER).setCellValue(account.getAccount());
            row.createCell(LAST_NAME).setCellValue(account.getLastName());
            row.createCell(MIDDLE_NAME).setCellValue(account.getMiddleName());
            row.createCell(FIRST_NAME).setCellValue(account.getFirstName());
            row.createCell(STATUS).setCellValue(getStatus(account.getStatus()));
            row.createCell(DUPLICATE).setCellValue(account.getExistAccount());
        }

        workbook.write(output);
        output.close();

        return output.toByteArray();
    } catch (Exception e) {
        e.printStackTrace();
        throw new WebOSException(e.getMessage(), e);
    }
}

From source file:com.inet.web.service.spi.download.ExportEmailWriterSpiService.java

License:Open Source License

/**
 * Export email of domain/*from   w  w  w  .ja  v a2  s . c o m*/
 * 
 * @param contacts
 * @return
 * @throws WebOSException
 */
private byte[] exportDomain(AccountExport accountExport) throws WebOSException {
    try {

        Workbook workbook = new HSSFWorkbook();
        // style
        CellStyle style = workbook.createCellStyle();
        style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);

        // sheet config
        Sheet sheet = workbook.createSheet("Email list");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        writeHeaderEmail(sheet);

        Map<LdapGroup, List<AccountExportInfo>> map = accountExport.getMap();
        for (LdapGroup group : map.keySet()) {
            writeGroup(sheet, group, accountExport.getDomain(), map.get(group), style);
        }

        ByteArrayOutputStream output = new ByteArrayOutputStream();
        workbook.write(output);
        output.close();

        return output.toByteArray();
    } catch (Exception e) {
        e.printStackTrace();
        throw new WebOSException(e.getMessage(), e);
    }
}

From source file:com.ipcglobal.fredimport.xls.DistinctCategoriesSpreadsheet.java

License:Apache License

/**
 * Creates the sheet./*from  ww  w.jav  a 2s  .  co  m*/
 *
 * @param distinctCategoryItems the distinct category items
 * @throws Exception the exception
 */
public void createSheet(Collection<DistinctCategoryItem> distinctCategoryItems) throws Exception {
    List<XlsDefItem> xlsDefItems = initHhdrWidthItems();
    String sheetName = "DistinctCategoryItems";
    Sheet sheet = wb.createSheet(sheetName);
    processColumnWidths(sheet, xlsDefItems);
    sheet.createFreezePane(0, 1, 0, 1); // freeze top row
    sheet.setAutoFilter(CellRangeAddress.valueOf("A1:AB1")); // hack - i know the number of columns
    sheet.getPrintSetup().setLandscape(true);
    sheet.setAutobreaks(true);
    sheet.getPrintSetup().setFitWidth((short) 1);
    sheet.getPrintSetup().setFitHeight((short) 1);

    int rowCnt = 0;

    // Header
    int colCnt = 0;
    Row rowHdr = sheet.createRow(rowCnt);
    for (XlsDefItem xlsDefItem : xlsDefItems) {
        Cell cellHdr = rowHdr.createCell(colCnt, Cell.CELL_TYPE_STRING);
        CellStyle style = findCellStyle("Arial", HSSFColor.WHITE.index, (short) 11, XSSFFont.BOLDWEIGHT_BOLD,
                cellStyleFromHdrAlign(HdrAlign.Left), XSSFCellStyle.VERTICAL_TOP, HSSFColor.LIGHT_BLUE.index,
                CellBorder.All_Thin, formatGeneral);
        style.setWrapText(true);
        cellHdr.setCellStyle(style);
        cellHdr.setCellValue(xlsDefItem.getName());
        colCnt++;
    }
    rowCnt++;

    // Data
    for (DistinctCategoryItem distinctCategoryItem : distinctCategoryItems) {
        Row rowData = sheet.createRow(rowCnt);
        int colNum = 0;
        for (XlsDefItem xlsDefItem : xlsDefItems)
            populateCell(rowData, colNum++, xlsDefItem.getDataType(),
                    getByNameAsString(distinctCategoryItem, xlsDefItem.getName()));
        rowCnt++;
    }
}

From source file:com.kafeidev.test.BusinessPlan.java

License:Apache License

@Test
public static void main(String[] args) throws Exception {
    Workbook wb;/* w ww .  ja v a2 s.  c  o m*/

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Business Plan");

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup 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
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    //columns for 11 weeks starting from 9-Nov
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);
    //        {
    //           String inputDate = "2010-Nov-04 01:32:27";
    //           Date date = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss", new Locale("en,EN")).parse(inputDate);
    //            String str= new SimpleDateFormat("dd.MMM.yyyy HH:mm:ss").format(date);
    //            System.out.println("data:"+str);
    //            
    //        }
    calendar.setTime(fmt.parse("19-Nov"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell 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);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    //group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    //set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:com.lufs.java.apache.poi.example.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (String arg : args) {
        if (arg.charAt(0) == '-') {
            xlsx = arg.equals("-xlsx");
        } else {//ww w .  j  a  va2  s.com
            calendar.set(Calendar.YEAR, Integer.parseInt(arg));
        }
    }
    int year = calendar.get(Calendar.YEAR);

    try (Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook()) {

        Map<String, CellStyle> 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
            Sheet sheet = wb.createSheet(months[month]);

            //turn off gridlines
            sheet.setDisplayGridlines(false);
            sheet.setPrintGridlines(false);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);
            PrintSetup 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
            Row headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(80);
            Cell 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
            Row 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 CellRangeAddress(1, 1, i * 2, i * 2 + 1));
                Cell 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++) {
                Row row = sheet.createRow(rownum++);
                row.setHeightInPoints(100);
                for (int i = 0; i < days.length; i++) {
                    Cell dayCell_1 = row.createCell(i * 2);
                    Cell 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
        String file = "calendar.xls";
        if (wb instanceof XSSFWorkbook)
            file += "x";

        try (FileOutputStream out = new FileOutputStream(file)) {
            wb.write(out);
        }
    }
}