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

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

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

Introduction

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

Prototype

int addMergedRegion(CellRangeAddress region);

Source Link

Document

Adds a merged region of cells (hence those cells form one)

Usage

From source file:project1.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*www . j  a va 2  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("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

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

From source file:ro.fortsoft.wicket.pivot.exporter.PivotXlsExporter.java

License:Apache License

@Override
public void exportPivot(PivotModel pivotModel, OutputStream outputStream) throws IOException {
    PivotTableRenderModel renderModel = PivotTableRenderModel.create(pivotModel);

    HSSFWorkbook wb = new HSSFWorkbook();
    Sheet sheetData = wb.createSheet("Pivot");

    Map<Integer, Integer> rowSpanMap = new HashMap<Integer, Integer>();
    StyleContext styleContext = new StyleContext(wb);

    int rowNumber = 0;
    int maxColNum = 0;
    for (RenderRow row : renderModel.getAllRenderRows()) {
        int col = 0;
        Row poiRow = sheetData.createRow(rowNumber);
        for (RenderCell cell : row.getRenderCells()) {
            maxColNum = Math.max(maxColNum, col);

            /*/*from w w w.j  av  a  2 s.co  m*/
             * Check if we currently have a rowspan at this column from the
             * parent row. We only support a colspan of 1 at the moment
             * here, if rowspan > 1
             */
            Integer rowSpan = rowSpanMap.get(col);
            if (rowSpan != null) {
                rowSpan--;
                if (rowSpan == 0)
                    rowSpanMap.remove(col);
                else
                    rowSpanMap.put(col, rowSpan);
                col++;
            }

            Cell poiCell = poiRow.createCell(col);

            /*
             * Output the Value
             */
            Object rawValue = cell.getRawValue();
            if (rawValue != null) {
                if (rawValue instanceof Double) {
                    poiCell.setCellValue((Double) rawValue);
                } else {
                    poiCell.setCellValue(String.valueOf(rawValue));
                }
            }

            styleCell(poiCell, cell, styleContext);

            if (cell.getRowspan() > 1) {
                rowSpanMap.put(col, cell.getRowspan() - 1);
                sheetData.addMergedRegion(
                        new CellRangeAddress(rowNumber, rowNumber + cell.getRowspan() - 1, col, col));
            }

            /*
             * We only support colspan _OR_ rowspan. The current PivotTable
             * also doesnt have rowspan and colspan at the same time.
             */
            if (cell.getColspan() > 1) {
                sheetData.addMergedRegion(
                        new CellRangeAddress(rowNumber, rowNumber, col, col + cell.getColspan() - 1));
            }

            col++;
            for (int i = 1; i < cell.getColspan(); i++) {
                col++;
            }
        }
        rowNumber++;
    }

    autoSizeColumns(sheetData, maxColNum);

    wb.write(outputStream);
    outputStream.flush();
}

From source file:ru.icc.cells.ssdc.model.CTable.java

License:Apache License

public void writeToExcel(File outputFile) {
    try {//from w ww .j  a v  a  2 s .  c  om
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();

        Iterator<CRow> rows = this.rows.iterator();
        int i = 0;
        while (rows.hasNext()) {
            Row excelRow = sheet.createRow(i);
            CRow row = rows.next();

            Iterator<CCell> rcells = row.getCells();
            while (rcells.hasNext()) {
                CCell cll = rcells.next();
                excelRow.createCell(cll.getCl() - 1).setCellValue(cll.getRawText());
            }
            i++;
        }

        Iterator<CCell> cells = this.getCells();
        while (cells.hasNext()) {
            CCell c = cells.next();
            if (c.getRt() != c.getRb() || c.getCl() != c.getCr()) {
                CellRangeAddress rngAdr = new CellRangeAddress(c.getRt() - 1, c.getRb() - 1, c.getCl() - 1,
                        c.getCr() - 1);
                sheet.addMergedRegion(rngAdr);
            }
        }
        FileOutputStream fileOut = new FileOutputStream(outputFile);
        wb.write(fileOut);
        fileOut.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:sample.poi.CalendarDemo.java

License:Apache License

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

    Calendar calendar = Calendar.getInstance();

    SimpleDateFormat formatFile = new SimpleDateFormat("dd-MM-yyyy-hh-mm-ss");

    HSSFWorkbook wb = 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);//w w  w.ja  va 2  s  .co m
        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] + " " + "2013");
        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";

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

From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java

License:Apache License

/**
 * {@inheritDoc}/*from   w  w  w. j  a  v a  2 s  .  co m*/
 */
@Override
public Sheet createStandardExcelSheet(@NotNull final Workbook workbook, @NotNull final String sheetName,
        @NotNull final String sheetTitle, @NotNull final List<String> columnTitles) {

    // Check sanity
    Validate.notEmpty(sheetName, "sheetName");
    Validate.notEmpty(sheetTitle, "sheetTitle");
    Validate.notEmpty(columnTitles, "columnTitles");
    Validate.notNull(workbook, "workbook");

    // Create a new Workbook if required.
    final LocalDateTime timestamp = LocalDateTime.now();
    final String now = TimeFormat.YEAR_MONTH_DATE_HOURS_MINUTES.print(timestamp).replace(":", " ");

    // Create the Sheet to return
    final Sheet toReturn = workbook.createSheet(sheetName + "_" + now);
    toReturn.setFitToPage(true);
    toReturn.setHorizontallyCenter(true);

    // Create a "Title" row containing a single cell (i.e. merged cells)
    // and where the sheet title is presented and centered.
    final Row titleRow = toReturn.createRow(0);
    titleRow.setHeightInPoints(45);
    final Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(sheetTitle + " " + now);
    titleCell.setCellStyle(getCellStyle(ExcelElement.TITLE, workbook));
    // toReturn.addMergedRegion(CellRangeAddress.valueOf("$A$1:$E$1"));
    toReturn.addMergedRegion(CellRangeAddress
            .valueOf("$A$1:$" + CellReference.convertNumToColString(columnTitles.size() - 1) + "1"));

    // Create a header Row with the column names defined above.
    final Row headerRow = toReturn.createRow(1);

    // headerRow.setHeightInPoints(40);
    // This *could* adjust the header row to fit its internal height.
    titleRow.setHeight((short) -1);

    Cell headerCell;

    for (int i = 0; i < columnTitles.size(); i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(columnTitles.get(i));
        headerCell.setCellStyle(getCellStyle(ExcelElement.HEADER, workbook));
        toReturn.setDefaultColumnStyle(i, getCellStyle(ExcelElement.NON_WRAPPING, workbook));
        toReturn.autoSizeColumn(i);
    }

    // All done
    return toReturn;
}

From source file:servlet.exportScoreSheet.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from   w  w w  .ja  v a 2 s .  c  o  m*/
 *
 * @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 {
    HttpSession ss = request.getSession();
    Account ac = (Account) ss.getAttribute("ac");
    int cId = Integer.parseInt((Long) ss.getAttribute("cId") + "");
    Course c = Course.getCourseByID(cId);

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

    Sheet sheet = wb.createSheet("scoresheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Score sheet of " + c.getName() + " course");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

    List<Account> listStudentScore = (List<Account>) ss.getAttribute("listStudentScore");
    int rownum = 2;
    int cellcount = 1;
    Row sumRow = sheet.createRow(rownum);
    sumRow.setHeightInPoints(55);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellValue("Student name");
    cell.setCellStyle(styles.get("header"));
    int countback = listStudentScore.get(0).getListStudentScore().size();
    int maxScore = 0;
    for (int i = countback - 1; i >= 0; i--) {
        cell = sumRow.createCell(cellcount);
        UserScore u = listStudentScore.get(0).getListStudentScore().get(i);
        cell.setCellValue("(" + cellcount + ") " + u.getAm_name() + " (" + u.getFull_mark() + ")");
        cell.setCellStyle(styles.get("header"));
        cellcount++;
        maxScore += u.getFull_mark();
    }
    cell = sumRow.createCell(cellcount);
    cell.setCellValue("Total (" + maxScore + ")");
    cell.setCellStyle(styles.get("header"));
    rownum++;

    for (Account account : listStudentScore) {
        sumRow = sheet.createRow(rownum);
        sumRow.setHeightInPoints(35);
        cell = sumRow.createCell(0);
        cell.setCellValue(account.getFirstname() + " " + account.getLastname());
        int j = 1;
        for (int i = account.getListStudentScore().size() - 1; i >= 0; i--) {
            UserScore usc = (UserScore) account.getListStudentScore().get(i);
            cell = sumRow.createCell(j);
            Assignment a = null;
            if (usc.getAss_type().equalsIgnoreCase("web")) {
                a = Assignment.getAmTimeByAmID(usc.getStof().getAm_id());
                String status = Assignment.lastedSentStatus(usc.getStof().getLasted_send_date(), a);
                if (status.equalsIgnoreCase("ontime") || status.equalsIgnoreCase("hurryup")
                        || status.equalsIgnoreCase("late")) {
                    cell.setCellValue(usc.getStof().getScore());
                } else {
                    status = Assignment.calculateTime(a);
                    if (status.equalsIgnoreCase("miss")) {
                        cell.setCellValue(usc.getStof().getScore());
                    } else {
                        cell.setCellValue("-");
                    }
                }

            } else if (usc.getAss_type().equalsIgnoreCase("file")) {
                a = Assignment.getAmTimeByAmID(usc.getStf().getAm_id());
                String status = Assignment.lastedSentStatus(usc.getStf().getLasted_send_date(), a);
                if (status.equalsIgnoreCase("ontime") || status.equalsIgnoreCase("hurryup")
                        || status.equalsIgnoreCase("late")) {
                    cell.setCellValue(usc.getStf().getScore());
                } else {
                    status = Assignment.calculateTime(a);
                    if (status.equalsIgnoreCase("miss")) {
                        cell.setCellValue(usc.getStf().getScore());
                    } else {
                        cell.setCellValue("-");
                    }
                }
            }
            j++;
        }
        cell = sumRow.createCell(j);
        int lastcol = account.getListStudentScore().size();

        //calculate column
        int dv = lastcol / 26;
        String coltmp = "";
        for (int i = 0; i < dv; i++) {
            coltmp += "A";
        }
        coltmp += (char) ('A' + (lastcol - (dv * 26)));
        System.out.println(coltmp);
        //

        String ref = (char) ('A' + 1) + "" + (rownum + 1) + ":" + coltmp + (rownum + 1);
        System.out.println(ref);
        cell.setCellFormula("SUM(" + ref + ")");
        rownum++;
    }

    // Write the output to a file
    String filename = "scoresheet_" + c.getName() + ".xlsx";
    String file = getServletContext().getRealPath("/") + "/file/scoresheet/" + filename;
    //        String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    response.sendRedirect("file/scoresheet/" + filename);

    //
    //        Workbook wb = new XSSFWorkbook();
    //        Sheet sheet = wb.createSheet("scoresheet");
    //        PrintSetup printSetup = sheet.getPrintSetup();
    //        printSetup.setLandscape(true);
    //        sheet.setFitToPage(true);
    //        sheet.setHorizontallyCenter(true);
    //
    //        //title row
    //        Row titleRow = sheet.createRow(0);
    //        titleRow.setHeightInPoints(45);
    //        Cell titleCell = titleRow.createCell(0);
    //        titleCell.setCellValue("Score sheet of " + "...." + " course");
    //        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1"));
    //
    //        //row with totals below
    //        int rownum = 2;
    //        Row sumRow = sheet.createRow(rownum);
    //       sumRow.setHeightInPoints(35);
    //        Cell cell;
    //        cell = sumRow.createCell(0);
    //        cell.setCellValue("Name:");
    //
    //        for (int j = 1; j < 12; j++) {
    //            cell = sumRow.createCell(j);
    //            String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
    //            cell.setCellFormula("SUM(" + ref + ")");
    //        }
    //
    //        // Write the output to a file
    //        String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
    //        FileOutputStream out = new FileOutputStream(file);
    //        wb.write(out);
    //        out.close();
}

From source file:test.poi.LoanCalculator.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);//  w  w  w  .j a  va2s . c o m
    sheet.setDisplayGridlines(false);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "E:/loan-calculator.xls";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:test.poi.MyExcelDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook();

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

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);//from  ww  w .  j av  a2s . co m
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("XX????20130506-20140503");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$O$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    sheet.setDefaultColumnWidth(20);
    headerRow.setHeightInPoints(20);
    Cell headerCell;
    for (int i = 1; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i - 1]);
        CellRangeAddress cra = new CellRangeAddress(1, 2, i, i);
        sheet.addMergedRegion(cra);
        setBorder(cra, sheet, wb);
        headerCell.setCellStyle(styles.get("header"));

    }

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

From source file:test.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new HSSFWorkbook();

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

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

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);//  w w w  .  j av  a  2  s  .  c  o  m
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

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

From source file:vistas.reportes.procesos.pruebaExcel.java

public static void main(String[] args) throws Exception {
    Workbook wb;/*w  w w . j  av a2 s  .  co  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("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
    try {
        File path = new File(file);
        Desktop.getDesktop().open(path);
    } catch (IOException ex) {
        ex.printStackTrace();
    }
}