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.skt.adcas.lte.action.DownLinkBySTDAction.java

public String selectCellTrafficCompCQIExcelDownload() {

    this.log.debug("selectCellTrafficCompCQIExcelDownload Start");

    FileOutputStream fileOut = null;

    try {/*from ww  w . j  a va 2  s .c o m*/
        parseParam();

        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);
        Map<String, Object> mapAfter = gson.fromJson(this.JSONDATA2, type);

        Workbook wb = new HSSFWorkbook();

        String sheetName = " CQI PDF";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet PDFsheet = wb.createSheet(safeName);

        createCellTrafficCQIExcelSheet(PDFsheet, "PDF", map);

        sheetName = " CQI CDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet CDFsheet = wb.createSheet(safeName);

        createCellTrafficCQIExcelSheet(CDFsheet, "CDF", map);

        sheetName = " CQI PDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet PDFsheetAfter = wb.createSheet(safeName);

        createCellTrafficCQIExcelSheet(PDFsheetAfter, "PDF", mapAfter);

        sheetName = " CQI CDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet CDFsheetAfter = wb.createSheet(safeName);

        createCellTrafficCQIExcelSheet(CDFsheetAfter, "CDF", mapAfter);

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkCompCQI(PDF_CDF)(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficCompCQIExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDAction.java

public String selectCellTrafficExcelDownload() {

    this.log.debug("selectCellTrafficExcelDownload Start");
    FileOutputStream fileOut = null;

    try {/*from ww  w.  j av a 2  s  .c  o m*/
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        Workbook wb = new HSSFWorkbook();
        //CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = "data";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheet = wb.createSheet(safeName);

        createCellTrafficExcelSheet(sheet, map);

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkData(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDAction.java

public String selectCellTrafficCompExcelDownload() {

    this.log.debug("selectCellTrafficCompExcelDownload Start");
    FileOutputStream fileOut = null;

    try {/*ww w. ja  v a 2 s .  c  om*/

        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);
        Map<String, Object> mapAfter = gson.fromJson(this.JSONDATA2, type);

        log.debug("json data : " + this.JSONDATA);
        log.debug("json data : " + this.JSONDATA2);

        Workbook wb = new HSSFWorkbook();
        //CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = "";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheet = wb.createSheet(safeName);

        createCellTrafficExcelSheet(sheet, map);

        sheetName = "";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheetAfter = wb.createSheet(safeName);

        createCellTrafficExcelSheet(sheetAfter, mapAfter);

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkCompData(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficCompExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDAction.java

public String selectCellTrafficThrpCompGraphExcelDownload() {

    this.log.debug("selectCellTrafficThrpCompGraphExcelDownload Start");
    SqlSession session = null;/*from w  w w .  j  a va2s . com*/
    FileOutputStream fileOut = null;

    try {
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        Workbook wb = new HSSFWorkbook();
        //CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = " ?";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);

        //sheet 
        Sheet sheet = wb.createSheet(safeName);

        //header ?
        Row hrow0 = sheet.createRow((short) 0);
        hrow0.setHeightInPoints(20);
        hrow0.createCell(0).setCellValue("");
        hrow0.createCell(1).setCellValue("(" + this.FROMYMD + ")");
        hrow0.createCell(2).setCellValue("(" + this.TOYMD + ")");

        StringMap categories = (StringMap) map.get("categories");
        StringMap beforeSeries = (StringMap) map.get("beforeSeries");
        StringMap afterSeries = (StringMap) map.get("afterSeries");

        short i = 1;
        for (int j = 0; j < categories.size(); j++) {
            //  
            Row row = sheet.createRow((short) i);
            row.setHeightInPoints(20);
            row.createCell(0)
                    .setCellValue(categories.get(String.valueOf(j)).toString().replaceAll("<br>", " : "));
            row.createCell(1).setCellValue(Double.parseDouble(beforeSeries.get(String.valueOf(j)).toString()));
            row.createCell(2).setCellValue(Double.parseDouble(afterSeries.get(String.valueOf(j)).toString()));
            i++;
        }

        log.debug("selectCellTrafficThrpCompGraphExcelDownload : file start");

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkThrpCompGraph(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);
        log.debug("selectCellTrafficThrpCompGraphExcelDownload : file end");

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        if (session != null) {
            session.rollback();
        }
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (session != null) {
            session.close();
        }
    }

    this.log.debug("selectCellTrafficThrpCompGraphExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsCQIExcelDownload() {

    this.log.debug("selectCellTrafficStatsCQIExcelDownload Start");
    FileOutputStream fileOut = null;

    try {/*from   ww w  . j  av a2 s.  co m*/
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        String searchType = this.SEARCHTYPE;
        log.debug("SEARCHTYPE : " + searchType);

        Workbook wb = new HSSFWorkbook();

        //PDFsheet 
        String sheetName = "CQI PDF";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet PDFsheet = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(PDFsheet, map, searchType, "PDF");

        //CDFsheet 
        sheetName = "CQI CDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet CDFsheet = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(CDFsheet, map, searchType, "CDF");

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsCQI(PDF_CDF)(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsCQIExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsCompCQIExcelDownload() {

    this.log.debug("selectCellTrafficStatsCompCQIExcelDownload Start");
    FileOutputStream fileOut = null;

    try {/*from   w  ww  .j  a va 2s .c om*/
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);
        Map<String, Object> mapAfter = gson.fromJson(this.JSONDATA2, type);

        log.debug("json data : " + this.JSONDATA);
        log.debug("json data : " + this.JSONDATA2);

        String searchType = this.SEARCHTYPE;
        log.debug("SEARCHTYPE : " + searchType);

        Workbook wb = new HSSFWorkbook();

        String sheetName = " CQI PDF";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet PDFsheet = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(PDFsheet, map, searchType, "PDF");

        sheetName = " CQI CDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet CDFsheet = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(CDFsheet, map, searchType, "CDF");

        sheetName = " CQI PDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet PDFsheetAfter = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(PDFsheetAfter, mapAfter, searchType, "PDF");

        sheetName = " CQI CDF";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet CDFsheetAfter = wb.createSheet(safeName);

        createCellTrafficStatsCQIExcelSheet(CDFsheetAfter, mapAfter, searchType, "CDF");

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsCompCQI(PDF_CDF)(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsCQIExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsExcelDownload() {

    this.log.debug("selectCellTrafficStatsExcelDownload Start");
    FileOutputStream fileOut = null;

    try {//from  w  w w  .  j  av  a 2s  .  c o m
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        String searchType = this.SEARCHTYPE;

        Workbook wb = new HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = "data";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);

        //sheet 
        Sheet sheet = wb.createSheet(safeName);

        createCellTrafficStatsExcelSheet(sheet, map, searchType);

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsData(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsCompExcelDownload() {

    this.log.debug("selectCellTrafficStatsCompExcelDownload Start");
    FileOutputStream fileOut = null;

    try {/*from ww  w . jav  a 2  s  .  c  om*/
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);
        Map<String, Object> mapAfter = gson.fromJson(this.JSONDATA2, type);

        log.debug("json data : " + this.JSONDATA);
        log.debug("json data : " + this.JSONDATA2);

        String searchType = this.SEARCHTYPE;

        Workbook wb = new HSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = "";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheet = wb.createSheet(safeName);

        createCellTrafficStatsExcelSheet(sheet, map, searchType);

        sheetName = "";
        safeName = WorkbookUtil.createSafeSheetName(sheetName);
        Sheet sheetAfter = wb.createSheet(safeName);

        createCellTrafficStatsExcelSheet(sheetAfter, mapAfter, searchType);

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkStatsCompData(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    this.log.debug("selectCellTrafficStatsCompExcelDownload End");
    return SUCCESS;
}

From source file:com.skt.adcas.lte.action.DownLinkBySTDStatsAction.java

public String selectCellTrafficStatsThrpCompGraphExcelDownload() {

    this.log.debug("selectCellTrafficStatsThrpCompGraphExcelDownload Start");
    SqlSession session = null;/*from  ww  w .  ja va2  s  .  co m*/
    FileOutputStream fileOut = null;

    try {
        //parseParam();
        Type type = new TypeToken<Map<String, Object>>() {
        }.getType();
        Gson gson = new Gson();
        Map<String, Object> map = gson.fromJson(this.JSONDATA, type);

        log.debug("json data : " + this.JSONDATA);

        Workbook wb = new HSSFWorkbook();
        //CreationHelper createHelper = wb.getCreationHelper();

        String sheetName = " ?";
        String safeName = WorkbookUtil.createSafeSheetName(sheetName);

        //sheet 
        Sheet sheet = wb.createSheet(safeName);

        //header ?
        Row hrow0 = sheet.createRow((short) 0);
        hrow0.setHeightInPoints(20);
        hrow0.createCell(0).setCellValue("");
        hrow0.createCell(1).setCellValue("(" + this.FROMYMD + ")");
        hrow0.createCell(2).setCellValue("(" + this.TOYMD + ")");

        StringMap categories = (StringMap) map.get("categories");
        StringMap beforeSeries = (StringMap) map.get("beforeSeries");
        StringMap afterSeries = (StringMap) map.get("afterSeries");

        short i = 1;
        for (int j = 0; j < categories.size(); j++) {
            //  
            Row row = sheet.createRow((short) i);
            row.setHeightInPoints(20);
            row.createCell(0)
                    .setCellValue(categories.get(String.valueOf(j)).toString().replaceAll("<br>", " : "));
            row.createCell(1).setCellValue(Double.parseDouble(beforeSeries.get(String.valueOf(j)).toString()));
            row.createCell(2).setCellValue(Double.parseDouble(afterSeries.get(String.valueOf(j)).toString()));
            i++;
        }

        log.debug("selectCellTrafficStatsThrpCompGraphExcelDownload : file start");

        String writeFolderPath = (String) super.properties.get("TEMP_FOLDER_PATH");
        String tempFolder = "/" + UUID.randomUUID().toString();
        String xlsFileName = "/DownLinkThrpCompGraph(STD).xls";

        if (!(new File(writeFolderPath + tempFolder)).mkdir()) {
            throw new Exception("? ??  .");
        }

        String xlsFileFullPath = writeFolderPath + tempFolder + xlsFileName;
        fileOut = new FileOutputStream(xlsFileFullPath);
        wb.write(fileOut);
        log.debug("selectCellTrafficStatsThrpCompGraphExcelDownload : file end");

        this.msg = "? ? ?";
        this.status = "SUCCESS";
        this.downloadurl = "download" + tempFolder + xlsFileName;

    } catch (Exception e) {
        this.msg = e.getMessage();
        this.status = "ERROR";
        this.error = true;
        if (session != null) {
            session.rollback();
        }
        e.printStackTrace();
    } finally {
        try {
            if (fileOut != null)
                fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (session != null) {
            session.close();
        }
    }

    this.log.debug("selectCellTrafficStatsThrpCompGraphExcelDownload End");
    return SUCCESS;
}

From source file:com.solidmaps.webapp.report.TimesheetDemo.java

License:Apache License

public 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);/* ww  w  .  j a  va  2 s. 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("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 = "/home/brunorocca/Desktop/mapa_teste.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}