Example usage for org.apache.poi.ss.usermodel Workbook write

List of usage examples for org.apache.poi.ss.usermodel Workbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. write

Introduction

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

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

From source file:com.emi.loan.test.ExportToExcel.java

public static void main(String[] args) {
    try {/*w  w  w .  ja v a 2 s  .co m*/

        //Populate DefaultTableModel data
        DefaultTableModel dtm = new DefaultTableModel();
        Vector<String> cols = new Vector<String>();
        dtm.addColumn("Col 1");
        dtm.addColumn("Col 2");
        dtm.addColumn("Col 3");

        Vector<String> dtmrow = null;
        for (int i = 1; i <= 10; i++) {
            dtmrow = new Vector<String>();
            for (int j = 1; j <= 3; j++) {
                dtmrow.add("Cell " + j + "." + i);
            }
            dtm.addRow(dtmrow);
        }

        //Exporting to Excel           
        Workbook wb = new HSSFWorkbook();
        CreationHelper createhelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");
        Row row = null;
        Cell cell = null;
        for (int i = 0; i < dtm.getRowCount(); i++) {
            row = sheet.createRow(i);
            for (int j = 0; j < dtm.getColumnCount(); j++) {

                cell = row.createCell(j);
                cell.setCellValue((String) dtm.getValueAt(i, j));
            }
        }

        FileOutputStream out = new FileOutputStream(new File("C:\\26276\\workbook.xls"));
        wb.write(out);
        out.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExportToExcel.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(ExportToExcel.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:com.emi.loan.util.Utilities.java

public static void exportTOExcel(DefaultTableModel dtm, Map<String, String> ln_info) {
    FileOutputStream out = null;/*  w  w  w .  ja  va  2  s.c  om*/
    try {
        Workbook wb = new HSSFWorkbook();
        //            CreationHelper createhelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("EMI TABLE");
        Row row;
        Cell cell;
        File file = chooseFile();
        out = new FileOutputStream(file);

        HSSFFont headerFont = (HSSFFont) wb.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setFontName("CENTURY GOTHIC");
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setColor(HSSFColor.WHITE.index);

        HSSFFont infoFont = (HSSFFont) wb.createFont();
        infoFont.setFontHeightInPoints((short) 14);
        infoFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        //            HSSFFont font = (HSSFFont) wb.createFont();
        //            font.setFontHeightInPoints((short) 10);
        //            font.setFontName("CENTURY GOTHIC");
        //            font.setColor(HSSFColor.BLACK.index);
        CellStyle defaultStyle = wb.createCellStyle();
        defaultStyle.setFillForegroundColor(HSSFColor.AQUA.index);
        defaultStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        defaultStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
        defaultStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);
        defaultStyle.setFont(headerFont);

        CellStyle borderStyle = wb.createCellStyle();
        borderStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        borderStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        borderStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        borderStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        borderStyle.setFont(infoFont);

        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellStyle(defaultStyle);
        cell.setCellValue("Loan Amount(Rs.)");

        cell = row.createCell(1);
        cell.setCellStyle(borderStyle);
        cell.setCellValue(Double.parseDouble(ln_info.get("Loan Amount")));

        row = sheet.createRow(3);
        cell = row.createCell(0);
        cell.setCellStyle(defaultStyle);
        cell.setCellValue("Interest %");

        cell = row.createCell(1);
        cell.setCellStyle(borderStyle);
        cell.setCellValue(Double.parseDouble(ln_info.get("Interest")));

        row = sheet.createRow(5);
        cell = row.createCell(0);
        cell.setCellStyle(defaultStyle);
        cell.setCellValue("Period (months)");

        cell = row.createCell(1);
        cell.setCellStyle(borderStyle);
        cell.setCellValue(Integer.parseInt(ln_info.get("Period")));

        for (int i = 0; i <= dtm.getRowCount(); i++) {

            row = sheet.createRow(i + 8);
            for (int j = 0; j < dtm.getColumnCount(); j++) {
                cell = row.createCell(j);

                if (i == 0) { // writing the column headers 

                    cell.setCellStyle(defaultStyle);
                    cell.setCellValue(dtm.getColumnName(j));

                } else if (j == 0 || j == 5) {
                    cell.setCellValue(Integer.parseInt(dtm.getValueAt(i - 1, j).toString()));
                } else {
                    cell.setCellValue(Double.parseDouble(dtm.getValueAt(i - 1, j).toString()));
                }

            }
        }

        row = sheet.createRow(dtm.getRowCount() + 12);
        cell = row.createCell(0);
        cell.setCellValue("-- END OF REPORT --");

        for (int j = 0; j < dtm.getColumnCount(); j++) {
            sheet.autoSizeColumn(j, true);
        }
        wb.write(out);
    } catch (FileNotFoundException ex) {
        System.out.println("File not Found");
        Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        System.out.println("IOException");
        Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            out.close();
        } catch (IOException ex) {
            Logger.getLogger(Utilities.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.epitech.oliver_f.astextexls.WriteXLSFile.java

public void write() {
    FileInputStream file = null;// w  ww  . j ava  2 s.c o m
    try {
        file = new FileInputStream(pathToFile);
        Workbook wb = WorkbookFactory.create(file);
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        int i = 0;
        int listIndex = 0;
        while (rowIterator.hasNext() && listIndex < results.size()) {
            Row row = rowIterator.next();
            if (i > 1) {
                Iterator<Cell> cellIterator = row.cellIterator();
                int cellIndex = 0;
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String r = results.get(listIndex).result.get(cellIndex);
                    try {
                        if (r == null)
                            throw new NumberFormatException();
                        Double resDouble = Double.parseDouble(r);
                        Integer resInt = resDouble.intValue();
                        cell.setCellValue(resInt.toString());
                    } catch (NumberFormatException e) {
                        cell.setCellValue(results.get(listIndex).result.get(cellIndex));
                    }
                    cellIndex++;
                }
                listIndex++;
            }
            i++;
        }
        System.out.println("listindex " + listIndex);
        file.close();
        FileOutputStream outFile = new FileOutputStream(new File(pathToFile));
        wb.write(outFile);
        outFile.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            file.close();
        } catch (IOException ex) {
            Logger.getLogger(WriteXLSFile.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.esri.geoevent.test.performance.report.XLSXReportWriter.java

License:Apache License

@Override
public void writeReport(String reportFile, List<String> testNames, List<String> columnNames,
        Map<String, List<FixtureStatistic>> stats) throws IOException {
    //create the parent directories - if needed
    createParentDirectoriesIfNeeded(reportFile);

    // rollover the file - keep backups
    rollOver(reportFile);/*  w  ww .j  ava 2  s  .c o  m*/

    Workbook workbook = null;
    try {
        workbook = new XSSFWorkbook();

        // header style
        CellStyle headerStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerStyle.setFont(font);

        // copy the column names - add the test name as the first column
        List<String> columnNamesCopy = new ArrayList<String>();
        columnNamesCopy.add("Test Name");
        columnNamesCopy.addAll(columnNames);

        // create the sheet
        Sheet sheet = workbook.createSheet("Summary");

        // create the header row
        int rowIndex = 0;
        Row headers = sheet.createRow(rowIndex);
        headers.setRowStyle(headerStyle);
        int cellIndex = 0;
        for (String columnName : columnNamesCopy) {
            Cell cell = headers.createCell(cellIndex);
            cell.setCellValue(columnName);
            cell.setCellStyle(headerStyle);
            cellIndex++;
        }
        for (String testName : testNames) {
            // get each test's fixture stats and sort them accordingly
            List<FixtureStatistic> fixtureStats = stats.get(testName);
            if (fixtureStats == null || fixtureStats.size() == 0) {
                continue;
            }
            Collections.sort(fixtureStats);
            rowIndex++;

            for (FixtureStatistic fixtureStat : fixtureStats) {
                Row data = sheet.createRow(rowIndex);
                cellIndex = 0;

                //write out the test name first
                Cell cell = data.createCell(cellIndex);
                cell.setCellValue(testName);
                cellIndex++;

                for (String columnName : columnNames) {
                    cell = data.createCell(cellIndex);
                    Object rawValue = fixtureStat.getStat(columnName);
                    if (rawValue == null) {
                        cell.setCellValue("");
                    } else {
                        if (rawValue instanceof Integer) {
                            cell.setCellValue((Integer) rawValue);
                        } else if (rawValue instanceof Double) {
                            cell.setCellValue((Double) rawValue);
                        } else if (rawValue instanceof Long) {
                            cell.setCellValue((Long) rawValue);
                        } else if (rawValue instanceof Boolean) {
                            cell.setCellValue((Boolean) rawValue);
                        } else {
                            cell.setCellValue(rawValue.toString());
                        }
                    }
                    // adjust column width to fit the content
                    sheet.autoSizeColumn(cellIndex);
                    cellIndex++;
                }
                //rowIndex++;
            }
        }

        //write out the total time
        if (getTotalTestingTime() != -1) {
            rowIndex = rowIndex + 2;
            Row data = sheet.createRow(rowIndex);
            Cell cell = data.createCell(0);
            cell.setCellValue("Total Testing Time:");
            cell.setCellStyle(headerStyle);
            cell = data.createCell(1);
            cell.setCellValue(formatTime(getTotalTestingTime()));
        }
    } finally {
        // write out the file
        FileOutputStream out = null;
        try {
            String fullPath = FilenameUtils.getFullPathNoEndSeparator(reportFile);
            // create all non exists folders else you will hit FileNotFoundException for report file path
            new File(fullPath).mkdirs();

            out = new FileOutputStream(reportFile);
            if (workbook != null) {
                workbook.write(out);
            }
        } finally {
            IOUtils.closeQuietly(out);
        }
    }
}

From source file:com.eurodyn.qlack2.fuse.lexicon.impl.LanguageServiceImpl.java

License:EUPL

@Override
@Transactional(TxType.REQUIRED)/*w  w w.  j  a va 2s  .co  m*/
public byte[] downloadLanguage(String languageID) {
    byte[] retVal = null;

    // Check that the language exists and get its translations
    Language language = Language.find(languageID, em);

    // Create an Excel workbook. The workbook will contain a sheet for each
    // group.
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    // Iterate over all existing groups and create a sheet for each one.
    // Creating a new list below and not using the one retrieved from
    // Group.getAllGroups since result lists are read only and
    // we need to add the empty group below to the list.
    List<Group> groups = new ArrayList<>(Group.getAllGroups(em));
    // Add an dummy entry to the list to also check for translations without
    // a group.
    Group emptyGroup = new Group();
    emptyGroup.setId(null);
    emptyGroup.setTitle("<No group>");
    groups.add(0, emptyGroup);
    for (Group group : groups) {
        Map<String, String> translations;
        translations = keyService.getTranslationsForGroupAndLocale(group.getId(), language.getLocale());
        if (!translations.isEmpty()) {
            Sheet sheet = wb.createSheet(group.getTitle());

            // Add the header.
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue(createHelper.createRichTextString("Key"));
            headerRow.createCell(1).setCellValue(createHelper.createRichTextString("Translation"));

            // Add the data.
            int rowCounter = 1;
            for (String key : translations.keySet()) {
                Row row = sheet.createRow(rowCounter++);
                row.createCell(0).setCellValue(createHelper.createRichTextString(key));
                row.createCell(1).setCellValue(createHelper.createRichTextString(translations.get(key)));
            }
        }
    }

    // Create the byte[] holding the Excel data.
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    try {
        wb.write(bos);
        retVal = bos.toByteArray();
    } catch (IOException ex) {
        // Convert to a runtime exception in order to roll back transaction
        LOGGER.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        throw new QLanguageProcessingException("Error creating Excel file for language " + languageID);
    }

    return retVal;
}

From source file:com.evidon.areweprivateyet.Aggregator.java

License:Open Source License

public void createSpreadSheet() throws Exception {
    int row = 2, cell = 0, sheet = 0;
    FileOutputStream file = new FileOutputStream(path + "analysis.xls");

    Workbook wb = new HSSFWorkbook();

    // content: total content length sheet.
    Sheet s = wb.createSheet();/*from w w w  . j  a  v a  2  s .  c  o m*/
    wb.setSheetName(sheet, "Content Length");
    this.createHeader(wb, s, "Total Content Length in MB", 0);

    Row r = s.createRow(row);
    for (String database : results.keySet()) {
        Cell c = r.createCell(cell);
        c.setCellValue(results.get(database).totalContentLength / 1024 / 1024);
        cell++;
    }

    row++;
    cell = 0;
    r = s.createRow(row);

    for (String database : results.keySet()) {
        Cell c = r.createCell(cell);
        if (database.equals("baseline")) {
            c.setCellValue("Decrease:");

            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), "0");
            decrease.put(database, contents);
        } else {
            c = r.createCell(cell);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellFormula("ROUND((100-(" + getCellLetter(cell - 1) + "3*100/A3)),0)");

            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            evaluator.evaluateFormulaCell(c);

            Map<String, String> contents = new LinkedHashMap<String, String>();
            contents.put(s.getSheetName(), c.getNumericCellValue() + "");
            decrease.put(database, contents);
        }
        cell++;
    }
    sheet++;

    // When content is created, baseline is used as a base for every entry. For example,
    // if baseline contained doubleclick.com, this will be output and each other analyzer's
    // map, like ghosterys analyzer is then asked for the content's mapping for doubleclick.
    // So, if baseline does not contain blah.com, yet ghostery map does, this entry is never
    // shown in the spreadsheet or any other results.

    // so this means if we have tracker/whatever URLs in a non-baseline profile
    // and these URLs are NOT in the baseline profile,
    // we wouldn't see those trackers/whatever in the final comparison.

    // content: HTTP Requests
    s = wb.createSheet();
    wb.setSheetName(sheet, "HTTP Requests");
    this.createHeader(wb, s, "Pages with One or More HTTP Requests to the Public Suffix", 1);
    this.createContent(wb, s, "requestCountPerDomain");
    sheet++;

    // content: HTTP Set-Cookie Responses
    s = wb.createSheet();
    wb.setSheetName(sheet, "HTTP Set-Cookie Responses");
    this.createHeader(wb, s,
            "Pages with One or More HTTP Responses from the Public Suffix That Include a Set-Cookie Header", 1);
    this.createContent(wb, s, "setCookieResponses");
    sheet++;

    // content: Cookie Added - Cookie Deleted
    s = wb.createSheet();
    wb.setSheetName(sheet, "Cookies Added-Deleted");
    this.createHeader(wb, s, "Cookies Added - Cookies Deleted Per Domain", 1);
    this.createContent(wb, s, "cookieTotals");
    sheet++;

    // content: Local Storage counts per domain
    s = wb.createSheet();
    wb.setSheetName(sheet, "Local Storage");
    this.createHeader(wb, s, "Local Storage counts per domain", 1);
    this.createContent(wb, s, "localStorageContents");
    sheet++;

    // content: Pretty Chart
    s = wb.createSheet();
    wb.setSheetName(sheet, "Overall");

    int rownum = 0, cellnum = 0;

    // Header
    r = s.createRow(rownum);
    Cell c = r.createCell(0);
    s.setColumnWidth(0, 8000);
    c.setCellValue(
            "Overall effectiveness measured by percentage of decrease vs baseline (0 for any negative effect)");

    rownum++;
    r = s.createRow(rownum);

    cellnum++;

    for (String database : decrease.keySet()) {
        if (database.equals("baseline")) {
            continue;
        }

        c = r.createCell(cellnum);
        c.setCellValue(database);

        CellStyle cs = wb.createCellStyle();
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        cs.setFont(f);

        c.setCellStyle(cs);
        cellnum++;
    }

    CellStyle numberStyle = wb.createCellStyle();
    numberStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("number"));

    // Content
    for (String type : decrease.get("baseline").keySet()) {
        cellnum = 0;
        rownum++;

        r = s.createRow(rownum);

        c = r.createCell(cellnum);
        c.setCellValue(type);
        cellnum++;

        for (String database : decrease.keySet()) {
            if (database.equals("baseline")) {
                continue;
            }

            c = r.createCell(cellnum);
            c.setCellStyle(numberStyle);

            double decreaseValue = Double.parseDouble(decrease.get(database).get(type));

            if (decreaseValue < 0)
                decreaseValue = 0;

            c.setCellValue(decreaseValue);
            cellnum++;
        }
    }

    /*
    for (String database : decrease.keySet()) {
       for (String type : decrease.get(database).keySet()) {
    System.out.println(database + "|" + type + "|" + decrease.get(database).get(type));
       }
    }
    */

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

From source file:com.excelsiorsoft.transformer.TransformationHandler.java

License:Apache License

/**
 * Actual Spring Integration transformation handler.
 *
 * @param inputMessage Spring Integration input message
 * @return New Spring Integration message with updated headers
 *//*from w ww.  jav a 2 s. c  om*/
@Transformer
public Message<byte[]> handleFile(final Message<File> inputMessage) {

    final File inputFile = inputMessage.getPayload();
    final String filename = inputFile.getName();

    final String inputAsString;

    try {
        inputAsString = FileUtils.readFileToString(inputFile);
    } catch (IOException e) {
        throw new IllegalStateException(e);
    }

    ByteArrayOutputStream bout = new ByteArrayOutputStream();

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Sample Sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(0);

    cell.setCellValue(inputAsString);

    try {
        wb.write(bout);
    } catch (IOException e) {
        throw new IllegalStateException(e);
    }

    final Message<byte[]> message = MessageBuilder.withPayload(bout.toByteArray())
            .setHeader(FileHeaders.FILENAME, filename + ".xls").setHeader(FileHeaders.ORIGINAL_FILE, inputFile)
            .setHeader("file_size", inputFile.length()).build();

    return message;
}

From source file:com.exilant.exility.core.XlxUtil.java

License:Open Source License

private boolean save(Workbook workbook, String fullyQualifiedName) {
    File file = new File(fullyQualifiedName);
    if (file.exists()) {
        ResourceManager.renameAsBackup(file);
        file = new File(fullyQualifiedName);
    }/*from   w  w w.j a  va2s . com*/
    OutputStream os = null;
    try {
        os = new FileOutputStream(file);
        workbook.write(os);
        os.close();
        return true;
    } catch (Exception e) {
        Spit.out("Error while saving " + fullyQualifiedName + ". " + e.getMessage());
        try {
            if (os != null) {
                os.close();
            }
        } catch (IOException e1) {
            //
        }
        return false;
    }
}

From source file:com.ferid.app.classroom.statistics.StatisticsFragment.java

License:Apache License

/**
 * Write into an excel file/*from w w w. ja v a2s  . com*/
 * @param wb
 */
private void writeIntoFile(Workbook wb) {
    boolean isFileOperationSuccessful = true;

    FileOutputStream fileOut = null;

    if (DirectoryUtility.isExternalStorageMounted()) {

        DirectoryUtility.createDirectory();

        try {
            fileOut = new FileOutputStream(DirectoryUtility.getPathFolder() + FILE_NAME);
            wb.write(fileOut);
        } catch (IOException e) {
            isFileOperationSuccessful = false;
        } finally {
            if (fileOut != null) {
                try {
                    fileOut.flush();
                    fileOut.close();
                } catch (IOException e) {
                    isFileOperationSuccessful = false;
                }
            }
        }

        //if file is successfully created and closed
        //open file, otherwise display error
        if (isFileOperationSuccessful) {
            openExcelFile();
        } else {
            excelFileError(getString(R.string.excelError));
        }

    } else { //external storage is not available
        excelFileError(getString(R.string.mountExternalStorage));
    }
}

From source file:com.firstonesoft.poi.TimesheetDemo.java

License:Apache License

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

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else//from  w  w w .  j av  a2s.  c o  m
        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("Resumen de Horas");
    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 = "D://timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}