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

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. write

Introduction

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

Prototype

private void write(POIFSFileSystem fs) throws IOException

Source Link

Document

Writes the workbook out to a brand new, empty POIFS

Usage

From source file:com.clonescriptscrapper.excelfile.GenerateCsvFile.java

public static void excel() throws FileNotFoundException, IOException {

    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("Monster Details");
    HSSFRow rowhead = sheet.createRow((int) 0);
    rowhead.createCell((int) 0).setCellValue("S.No.");
    rowhead.createCell((int) 1).setCellValue("CATEGORY_DATA_ID");
    rowhead.createCell((int) 2).setCellValue("CATEGORY_ID");
    rowhead.createCell((int) 3).setCellValue("TITLE");
    rowhead.createCell((int) 4).setCellValue("NAME");
    rowhead.createCell((int) 5).setCellValue("CLICKS");
    rowhead.createCell((int) 6).setCellValue("ADDED_ON");
    rowhead.createCell((int) 7).setCellValue("PAGE_RANK");
    rowhead.createCell((int) 8).setCellValue("DESCRIPTION");
    rowhead.createCell((int) 9).setCellValue("DEMO_URL");
    rowhead.createCell((int) 10).setCellValue("CATEGORY_ID");
    rowhead.createCell((int) 11).setCellValue("CATEGORY_NAME");
    rowhead.createCell((int) 12).setCellValue("CATEGORY_URL");
    rowhead.createCell((int) 13).setCellValue("ISCRAWLED");

    try {/*from   ww w  . ja  v  a2 s .  c  om*/
        Class.forName("com.mysql.jdbc.Driver");
        java.sql.Connection con = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/clonescriptdirectorydb", "root", "");
        String sql = "SELECT * FROM `categories_data`,categories where categories_data.CATEGORY_ID= categories .CATEGORY_ID;";
        java.sql.PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int k = 0;
        while (rs.next()) {

            HSSFRow row = sheet.createRow((int) k + 2);
            try {
                row.createCell((int) 0).setCellValue(k + 1);
            } catch (Exception sd) {
            }
            try {
                row.createCell((int) 1).setCellValue(rs.getString("CATEGORY_DATA_ID") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 2).setCellValue(rs.getString("CATEGORY_ID") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 3).setCellValue(rs.getString("TITLE") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 4).setCellValue(rs.getString("NAME") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 5).setCellValue(rs.getString("CLICKS") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 6).setCellValue(rs.getString("ADDED_ON") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 7).setCellValue(rs.getString("PAGE_RANK") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 8).setCellValue(rs.getString("DESCRIPTION") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 9).setCellValue(rs.getString("DEMO_URL") + "");
            } catch (Exception sd) {
            }
            try {
                row.createCell((int) 10).setCellValue(rs.getString("CATEGORY_ID") + "");
            } catch (Exception sd) {
            }
            try {
                row.createCell((int) 11).setCellValue(rs.getString("CATEGORY_NAME") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 12).setCellValue(rs.getString("CATEGORY_URL") + "");
            } catch (Exception sd) {
            }

            try {
                row.createCell((int) 13).setCellValue(rs.getString("ISCRAWLED") + "");
            } catch (Exception sd) {
            }

            k++;
        }

        try {

            String filename = "data.csv";
            System.out.println("Directory is created!");
            FileOutputStream fileOut = new FileOutputStream(filename);
            hwb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated!");
        } catch (IOException iOException) {
        }

    } catch (Exception aaa) {
    }

}

From source file:com.commander4j.util.JExcel.java

License:Open Source License

public void exportToExcel(String filename, ResultSet rs) {
    try {/*from   w ww. ja v a2  s  .  co  m*/

        ResultSetMetaData rsmd = rs.getMetaData();
        int numberOfColumns = rsmd.getColumnCount();
        int columnType = 0;
        String columnTypeName = "";
        int recordNumber = 0;
        int passwordCol = -1;

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        HSSFCellStyle cellStyle_varchar = workbook.createCellStyle();
        cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle();
        cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle();
        cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_title = workbook.createCellStyle();
        cellStyle_title.setAlignment(HorizontalAlignment.CENTER);

        HSSFCellStyle cellStyle_char = workbook.createCellStyle();
        cellStyle_char.setAlignment(HorizontalAlignment.LEFT);

        HSSFCellStyle cellStyle_date = workbook.createCellStyle();
        cellStyle_date.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle();
        cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER);
        cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        HSSFCellStyle cellStyle_decimal = workbook.createCellStyle();
        cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT);

        HSSFFont font_title = workbook.createFont();
        font_title.setColor((short) 0xc);
        font_title.setBold(true);
        ;
        font_title.setItalic(true);
        font_title.setUnderline(HSSFFont.U_DOUBLE);
        cellStyle_title.setFont(font_title);

        HSSFCell cell;
        HSSFRow row;

        // rs.beforeFirst();

        while (rs.next()) {
            recordNumber++;

            if (recordNumber == 1) {
                row = sheet.createRow((int) 0);
                for (int column = 1; column <= numberOfColumns; column++) {
                    cell = row.createCell((int) (column - 1));
                    String columnName = rsmd.getColumnLabel(column);
                    columnName = columnName.replace("_", " ");
                    columnName = JUtility.capitalize(columnName);
                    cell.setCellStyle(cellStyle_title);
                    cell.setCellValue(columnName);
                    if (columnName.equals("Password")) {
                        passwordCol = column;
                    }
                }
            }

            row = sheet.createRow((int) recordNumber);

            for (int column = 1; column <= numberOfColumns; column++) {

                columnType = rsmd.getColumnType(column);
                columnTypeName = rsmd.getColumnTypeName(column);

                cell = row.createCell((int) (column - 1));

                try {
                    switch (columnType) {
                    case java.sql.Types.NVARCHAR:
                        HSSFRichTextString rtf_nvarchar;
                        if (column == passwordCol) {
                            rtf_nvarchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_nvarchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_nvarchar);
                        cell.setCellValue(rtf_nvarchar);
                        break;
                    case java.sql.Types.VARCHAR:
                        HSSFRichTextString rtf_varchar;
                        if (column == passwordCol) {
                            rtf_varchar = new HSSFRichTextString("*****");
                        } else {
                            rtf_varchar = new HSSFRichTextString(rs.getString(column));
                        }

                        cell.setCellStyle(cellStyle_varchar);
                        cell.setCellValue(rtf_varchar);
                        break;
                    case java.sql.Types.CHAR:
                        HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column));
                        cell.setCellStyle(cellStyle_char);
                        cell.setCellValue(rtf_char);
                        break;
                    case java.sql.Types.DATE:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_date);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.TIMESTAMP:
                        try {
                            cell.setCellValue(rs.getTimestamp(column));
                            cell.setCellStyle(cellStyle_timestamp);
                        } catch (Exception ex) {

                        }
                        break;
                    case java.sql.Types.DECIMAL:
                        HSSFRichTextString rtf_decimal = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimal);
                        break;
                    case java.sql.Types.NUMERIC:
                        HSSFRichTextString rtf_decimaln = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_decimaln);
                        break;
                    case java.sql.Types.BIGINT:
                        HSSFRichTextString rtf_bigint = new HSSFRichTextString(
                                rs.getBigDecimal(column).toString());
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_bigint);
                        break;
                    case java.sql.Types.INTEGER:
                        HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_int);
                        break;
                    case java.sql.Types.FLOAT:
                        HSSFRichTextString rtf_float = new HSSFRichTextString(
                                String.valueOf(rs.getFloat(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_float);
                        break;
                    case java.sql.Types.DOUBLE:
                        HSSFRichTextString rtf_double = new HSSFRichTextString(
                                String.valueOf(rs.getDouble(column)));
                        cell.setCellStyle(cellStyle_decimal);
                        cell.setCellValue(rtf_double);
                        break;
                    default:
                        cell.setCellValue(new HSSFRichTextString(columnTypeName));
                        break;
                    }
                } catch (Exception ex) {
                    String errormessage = ex.getLocalizedMessage();
                    HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage);
                    cell.setCellStyle(cellStyle_varchar);
                    cell.setCellValue(rtf_exception);
                    break;
                }
            }

            if (recordNumber == 65535) {
                break;
            }
        }

        for (int column = 1; column <= numberOfColumns; column++) {
            sheet.autoSizeColumn((int) (column - 1));
        }

        if (recordNumber > 0) {
            try {
                FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase());
                workbook.write(fileOut);
                fileOut.close();
            } catch (Exception ex) {
                setErrorMessage(ex.getMessage());
            }
        }

        try {
            workbook.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }
}

From source file:com.company.regis.utils.PrintUtil.java

public static void ExcelBean(String reportTag, Map<String, Object> beans) {
    String reportName = reportTag + ".xls";

    HSSFWorkbook wb = null;
    InputStream is = FacesContext.getCurrentInstance().getExternalContext()
            .getResourceAsStream("/excels/" + reportName);
    XLSTransformer transformer = new XLSTransformer();

    try {//from w  ww .  j a va  2 s  .co m

        wb = transformer.transformXLS(is, beans);
        FacesContext ctx = FacesContext.getCurrentInstance();
        ExternalContext ectx = ctx.getExternalContext();

        HttpServletResponse response = (HttpServletResponse) ectx.getResponse();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + reportTag + ".xls\"");

        ServletOutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();

        ctx.responseComplete();

    } catch (Exception ex) {
        System.out.println(ex);
    }
}

From source file:com.crunchify.jsp.servlet.HSSFCreate.java

/**
 * Processes requests for both HTTP GET and POST methods.
 *
 * @param request servlet request//from w w w.ja va 2s .  c o  m
 * @param response servlet response
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    DepartamentoDAO d = new DepartamentoDAO();

    response.setContentType("application/vnd.ms-excel");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");

    Map<String, Object[]> data = new HashMap<String, Object[]>();

    data.put("1", new Object[] { "Emp No.", "Name" });
    for (int i = 0; i < d.findAll().size(); i++) {
        data.put("2", new Object[] { d.findAll().get(i).getNom_departamento(),
                d.findAll().get(i).getNom_departamento() });
    }

    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof Date) {
                cell.setCellValue((Date) obj);
            } else if (obj instanceof Boolean) {
                cell.setCellValue((Boolean) obj);
            } else if (obj instanceof String) {
                cell.setCellValue((String) obj);
            } else if (obj instanceof Double) {
                cell.setCellValue((Double) obj);
            }
        }
    }

    // Write the output 
    OutputStream out = response.getOutputStream();
    wb.write(out);
    out.close();
}

From source file:com.daphne.es.showcase.excel.service.ExcelDataService.java

License:Apache License

/**
 * workbook//from w ww  .  j  a v  a 2  s . co m
 * 1?vbs ?
 * 2?c#??
 * ? ????office 2007 ?
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2003WithOneSheetPerWorkBook(final User user, final String contextRootPath,
        final Searchable searchable) {
    int workbookCount = 0;
    List<String> workbookFileNames = new ArrayList<String>();
    int perSheetRows = 60000; //?sheet 6w?
    int totalRows = 0;
    String extension = "xls";

    int pageSize = 1000;
    Long maxId = 0L;

    BufferedOutputStream out = null;
    try {
        long beginTime = System.currentTimeMillis();

        while (true) {
            workbookCount++;
            String fileName = generateFilename(user, contextRootPath, workbookCount, extension);
            workbookFileNames.add(fileName);
            File file = new File(fileName);

            HSSFWorkbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet();
            Row headerRow = sheet.createRow(0);
            Cell idHeaderCell = headerRow.createCell(0);
            idHeaderCell.setCellValue("?");
            Cell contentHeaderCell = headerRow.createCell(1);
            contentHeaderCell.setCellValue("");

            totalRows = 1;

            Page<ExcelData> page = null;

            do {
                searchable.setPage(0, pageSize);
                //
                if (!searchable.containsSearchKey("id_in")) {
                    searchable.addSearchFilter("id", SearchOperator.gt, maxId);
                }
                page = findAll(searchable);

                for (ExcelData data : page.getContent()) {
                    Row row = sheet.createRow(totalRows);
                    Cell idCell = row.createCell(0);
                    idCell.setCellValue(data.getId());
                    Cell contentCell = row.createCell(1);
                    contentCell.setCellValue(data.getContent());
                    maxId = Math.max(maxId, data.getId());
                    totalRows++;
                }
                //clear entity manager
                RepositoryHelper.clear();
            } while (page.hasNext() && totalRows <= perSheetRows);

            out = new BufferedOutputStream(new FileOutputStream(file));
            wb.write(out);

            IOUtils.closeQuietly(out);

            if (!page.hasNext()) {
                break;
            }
        }

        String fileName = workbookFileNames.get(0);
        if (workbookCount > 1 || needCompress(new File(fileName))) {
            fileName = fileName.substring(0, fileName.lastIndexOf("_")) + ".zip";
            //
            compressAndDeleteOriginal(fileName, workbookFileNames.toArray(new String[0]));
        } else {
            String newFileName = fileName.substring(0, fileName.lastIndexOf("_")) + "." + extension;
            FileUtils.moveFile(new File(fileName), new File(newFileName));
            fileName = newFileName;
        }

        long endTime = System.currentTimeMillis();

        Map<String, Object> context = Maps.newHashMap();
        context.put("seconds", (endTime - beginTime) / 1000);
        context.put("url", fileName.replace(contextRootPath, ""));
        notificationApi.notify(user.getId(), "excelExportSuccess", context);
    } catch (Exception e) {
        e.printStackTrace();
        //
        IOUtils.closeQuietly(out);
        log.error("excel export error", e);
        Map<String, Object> context = Maps.newHashMap();
        context.put("error", e.getMessage());
        notificationApi.notify(user.getId(), "excelExportError", context);
    }
}

From source file:com.daphne.es.showcase.excel.service.ExcelDataService.java

License:Apache License

/**
 * excel 2003//from   ww  w. j a v a 2 s .  c  o m
 * ????
 * ?sheet65536(usermodel? ?flush ????)
 * @param user
 * @param contextRootPath
 * @param searchable
 */
@Async
public void exportExcel2003WithUsermodel(final User user, final String contextRootPath,
        final Searchable searchable) {
    int perSheetRows = 60000; //?sheet 6w?
    int totalRows = 0;
    Long maxId = 0L;

    String fileName = generateFilename(user, contextRootPath, "xls");
    File file = new File(fileName);
    BufferedOutputStream out = null;
    try {
        long beginTime = System.currentTimeMillis();

        HSSFWorkbook wb = new HSSFWorkbook();
        while (true) {
            Sheet sheet = wb.createSheet();
            Row headerRow = sheet.createRow(0);
            Cell idHeaderCell = headerRow.createCell(0);
            idHeaderCell.setCellValue("?");
            Cell contentHeaderCell = headerRow.createCell(1);
            contentHeaderCell.setCellValue("");

            totalRows = 1;
            Page<ExcelData> page = null;
            do {
                searchable.setPage(0, pageSize);
                //
                if (!searchable.containsSearchKey("id_in")) {
                    searchable.addSearchFilter("id", SearchOperator.gt, maxId);
                }
                page = findAll(searchable);

                for (ExcelData data : page.getContent()) {
                    Row row = sheet.createRow(totalRows);
                    Cell idCell = row.createCell(0);
                    idCell.setCellValue(data.getId());
                    Cell contentCell = row.createCell(1);
                    contentCell.setCellValue(data.getContent());
                    maxId = Math.max(maxId, data.getId());
                    totalRows++;
                }
                //clear entity manager
                RepositoryHelper.clear();
            } while (page.hasNext() && totalRows <= perSheetRows);

            if (!page.hasNext()) {
                break;
            }
        }

        out = new BufferedOutputStream(new FileOutputStream(file));
        wb.write(out);

        IOUtils.closeQuietly(out);

        if (needCompress(file)) {
            fileName = compressAndDeleteOriginal(fileName);
        }

        long endTime = System.currentTimeMillis();

        Map<String, Object> context = Maps.newHashMap();
        context.put("seconds", (endTime - beginTime) / 1000);
        context.put("url", fileName.replace(contextRootPath, ""));
        notificationApi.notify(user.getId(), "excelExportSuccess", context);
    } catch (Exception e) {
        IOUtils.closeQuietly(out);
        log.error("excel export error", e);
        Map<String, Object> context = Maps.newHashMap();
        context.put("error", e.getMessage());
        notificationApi.notify(user.getId(), "excelExportError", context);
    }
}

From source file:com.dayuan.action.BusFileAction.java

/**???Excel*/
@RequestMapping("/exportExcel")
public ModelAndView exportExcel(HttpServletRequest request, HttpServletResponse response) {
    Map<String, Object> context = getRootMap();
    SysUser user = SessionUtils.getUser(request);
    BusFileModel busFileModel = new BusFileModel();
    if (SuperAdmin.YES.key != user.getSuperAdmin() && user.getExcelAuth() == 0) {
        //busFileModel.setlUserName(user.getNickName());
        busFileModel.setlUId(user.getId().toString());
    }/*from  w w  w .j  a  va2  s  . co m*/
    busFileModel.setRows(500);
    try {
        List<BusFiles> list = busFileService.queryByList(busFileModel);
        if (list != null && list.size() > 0) {
            // webbookExcel  
            HSSFWorkbook wb = new HSSFWorkbook();
            // webbooksheet,Excelsheet  
            HSSFSheet sheet = wb.createSheet("?");
            HSSFSheet sheetLoan = wb.createSheet("?");
            // sheet0,??poiExcel?short  
            HSSFRow row = sheet.createRow((int) 0);
            HSSFRow rowLoan = sheetLoan.createRow((int) 0);
            // ?   
            HSSFCellStyle style = wb.createCellStyle();
            // ?
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            /**sheet1 ?*/
            HSSFCell cell = row.createCell((short) 0);
            cell.setCellValue("??");
            cell.setCellStyle(style);
            cell = row.createCell((short) 1);
            cell.setCellValue("??");//busfiles.lUserName
            cell.setCellStyle(style);
            cell = row.createCell((short) 2);
            cell.setCellValue("??");//busLoanInfo.applicationName 
            cell.setCellStyle(style);
            cell = row.createCell((short) 3);
            cell.setCellValue("?"); //busfiles.lStatus
            cell.setCellStyle(style);
            cell = row.createCell((short) 4);
            cell.setCellValue(""); //busfiles.createTime
            cell.setCellStyle(style);
            cell = row.createCell((short) 5);
            cell.setCellValue("??"); //busLoanInfo.channel
            cell.setCellStyle(style);
            cell = row.createCell((short) 6);
            cell.setCellValue("?"); //buslending.loanAmount
            cell.setCellStyle(style);
            cell = row.createCell((short) 7);
            cell.setCellValue("?"); //buslending.openingQuota
            cell.setCellStyle(style);
            cell = row.createCell((short) 8);
            cell.setCellValue(""); //busBiling.creditEndDate
            cell.setCellStyle(style);
            cell = row.createCell((short) 9);
            cell.setCellValue("???"); //busBiling.loanAccount
            cell.setCellStyle(style);
            cell = row.createCell((short) 10);
            cell.setCellValue("?"); //legal.deliveryAddress
            cell.setCellStyle(style);

            /**sheet2 ?*/
            HSSFCell cellLoan = rowLoan.createCell((short) 0);
            cellLoan.setCellValue("??");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 1);
            cellLoan.setCellValue("??");//busLoanInfo.applicationName  
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 2);
            cellLoan.setCellValue("?"); //busLoanInfo.urgentCont 
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 3);
            cellLoan.setCellValue("");//busLoanInfo.relationship  
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 4);
            cellLoan.setCellValue("?");//busLoanInfo.urgentContPhone  
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 5);
            cellLoan.setCellValue("?");//busLoanInfo.urgentContAddress  
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 6);
            cellLoan.setCellValue("??"); //biling.loanCardNumber 
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 7);
            cellLoan.setCellValue("1");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 8);
            cellLoan.setCellValue("?1");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 9);
            cellLoan.setCellValue("???1");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 10);
            cellLoan.setCellValue("?1");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 11);
            cellLoan.setCellValue("2");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 12);
            cellLoan.setCellValue("?2");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 13);
            cellLoan.setCellValue("???2");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 14);
            cellLoan.setCellValue("?2");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 15);
            cellLoan.setCellValue("");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 16);
            cellLoan.setCellValue("??");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 17);
            cellLoan.setCellValue("????");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 18);
            cellLoan.setCellValue("??");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 19);
            cellLoan.setCellValue("??");
            cellLoan.setCellStyle(style);
            cellLoan = rowLoan.createCell((short) 20);
            cellLoan.setCellValue("?");
            cellLoan.setCellStyle(style);

            for (int i = 0; i < list.size(); i++) {
                BusFiles busFiles = list.get(i);
                Integer lId = busFiles.getId();
                BusLoanInfo busLoanInfo = busLoanInfoService.queryByLId(lId);
                BusLending busLending = busLendingService.queryByBId(lId);
                BusBiling busBiling = busBilingService.queryByBId(lId);
                BusLoanInfoLegal legal = busLoanInfoLegalService.getBusLoanInfoLegal(lId);
                List<BusLoanInfoShop> shopList = busLoanInfoShopService.queryListByBId(lId);

                /**rowsheet1*/
                row = sheet.createRow((int) 1 + i);
                row.createCell((short) 0).setCellValue(i + 1);
                row.createCell((short) 1).setCellValue(busFiles.getlUserName());
                row.createCell((short) 3).setCellValue(busFiles.getlStatus());
                row.createCell((short) 4).setCellValue(
                        DateUtil.getFormattedDateUtil((Date) busFiles.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));

                /**rowLoansheet2*/
                rowLoan = sheetLoan.createRow((int) 1 + i);
                rowLoan.createCell((short) 0).setCellValue(i + 1);

                if (busLoanInfo != null) {
                    row.createCell((short) 2).setCellValue(busLoanInfo.getApplicationName());
                    row.createCell((short) 5).setCellValue(busLoanInfo.getChannel());

                    rowLoan.createCell((short) 1).setCellValue(busLoanInfo.getApplicationName());
                    rowLoan.createCell((short) 2).setCellValue(busLoanInfo.getUrgentCont());
                    rowLoan.createCell((short) 3).setCellValue(busLoanInfo.getRelationship());
                    rowLoan.createCell((short) 4).setCellValue(busLoanInfo.getUrgentContPhone());
                    rowLoan.createCell((short) 5).setCellValue(busLoanInfo.getUrgentContAddress());

                } else {
                    row.createCell((short) 2).setCellValue("");
                    row.createCell((short) 5).setCellValue("");

                    rowLoan.createCell((short) 1).setCellValue("");
                    rowLoan.createCell((short) 2).setCellValue("");
                    rowLoan.createCell((short) 3).setCellValue("");
                    rowLoan.createCell((short) 4).setCellValue("");
                    rowLoan.createCell((short) 5).setCellValue("");
                }
                if (busLending != null) {
                    row.createCell((short) 6).setCellValue(busLending.getLoanAmount());
                    row.createCell((short) 7).setCellValue(busLending.getOpeningQuota());
                } else {
                    row.createCell((short) 6).setCellValue("");
                    row.createCell((short) 7).setCellValue("");
                }
                if (busBiling != null) {
                    row.createCell((short) 8).setCellValue(busBiling.getCreditEndDate());
                    row.createCell((short) 9).setCellValue(busBiling.getLoanAccount());

                    rowLoan.createCell((short) 6).setCellValue(busBiling.getLoanCardNumber());//??
                    rowLoan.createCell((short) 15).setCellValue(DateUtil
                            .getFormattedDateUtil((Date) busBiling.getCheckDate(), "yyyy-MM-dd HH:mm:ss"));//
                    rowLoan.createCell((short) 16).setCellValue(busBiling.getCreditorIfNormal());//??
                    rowLoan.createCell((short) 17).setCellValue(busBiling.getGuarantorIfNormal());//????
                    rowLoan.createCell((short) 18).setCellValue(busBiling.getCloudLoanIfWarning());//??
                    rowLoan.createCell((short) 19).setCellValue(busBiling.getShopOperation());//??
                    rowLoan.createCell((short) 20).setCellValue(busBiling.getOtherNeedToExplained());//?
                } else {
                    row.createCell((short) 8).setCellValue("");
                    row.createCell((short) 9).setCellValue("");

                    rowLoan.createCell((short) 6).setCellValue("");//??
                    rowLoan.createCell((short) 15).setCellValue("");//
                    rowLoan.createCell((short) 16).setCellValue("");//??
                    rowLoan.createCell((short) 17).setCellValue("");//????
                    rowLoan.createCell((short) 18).setCellValue("");//??
                    rowLoan.createCell((short) 19).setCellValue("");//??
                    rowLoan.createCell((short) 20).setCellValue("");//?

                }
                if (legal != null) {
                    row.createCell((short) 10).setCellValue(legal.getDeliveryAddress());
                } else {
                    row.createCell((short) 10).setCellValue("");
                }

                if (shopList != null && shopList.size() > 0) {
                    if (shopList.size() == 1) {
                        BusLoanInfoShop shop = shopList.get(0);
                        rowLoan.createCell((short) 7).setCellValue(shop.getShopName());//
                        rowLoan.createCell((short) 8).setCellValue(shop.getPlatformName());//?
                        rowLoan.createCell((short) 9).setCellValue(shop.getSubAccount());//???
                        rowLoan.createCell((short) 10).setCellValue(shop.getSbuPassword());//?
                        rowLoan.createCell((short) 11).setCellValue("");//
                        rowLoan.createCell((short) 12).setCellValue("");//?
                        rowLoan.createCell((short) 13).setCellValue("");//???
                        rowLoan.createCell((short) 14).setCellValue("");//?
                    } else if (shopList.size() == 2) {
                        BusLoanInfoShop shop = shopList.get(0);
                        rowLoan.createCell((short) 7).setCellValue(shop.getShopName());//
                        rowLoan.createCell((short) 8).setCellValue(shop.getPlatformName());//?
                        rowLoan.createCell((short) 9).setCellValue(shop.getSubAccount());//???
                        rowLoan.createCell((short) 10).setCellValue(shop.getSbuPassword());//?
                        BusLoanInfoShop shop1 = shopList.get(1);
                        rowLoan.createCell((short) 11).setCellValue(shop1.getShopName());//
                        rowLoan.createCell((short) 12).setCellValue(shop1.getPlatformName());//?
                        rowLoan.createCell((short) 13).setCellValue(shop1.getSubAccount());//???
                        rowLoan.createCell((short) 14).setCellValue(shop1.getSbuPassword());//?
                    }

                } else {
                    rowLoan.createCell((short) 7).setCellValue("");
                    rowLoan.createCell((short) 8).setCellValue("");
                    rowLoan.createCell((short) 9).setCellValue("");
                    rowLoan.createCell((short) 10).setCellValue("");
                    rowLoan.createCell((short) 11).setCellValue("");
                    rowLoan.createCell((short) 12).setCellValue("");
                    rowLoan.createCell((short) 13).setCellValue("");
                    rowLoan.createCell((short) 14).setCellValue("");
                }

            }
            String savePath = request.getSession().getServletContext().getRealPath(
                    File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,??
            File savePathFile = new File(savePath);
            if (!savePathFile.exists()) {
                savePathFile.mkdirs();
            }
            savePath = savePath + File.separator + UUID.randomUUID();//?
            File fileSavePath = new File(savePath);
            /**??*/
            if (fileSavePath.exists()) {
                if (fileSavePath.isDirectory()) {
                    File[] files = fileSavePath.listFiles();
                    for (File file : files) {
                        file.delete();
                    }
                    fileSavePath.delete();
                } else {
                    fileSavePath.delete();
                }
                fileSavePath.mkdirs();
            } else {
                fileSavePath.mkdirs();
            }
            String excel = "????" + DateUtil.getNowLongTime() + ".xls";//eccel??
            BufferedOutputStream fout = new BufferedOutputStream(
                    new FileOutputStream(savePath + File.separator + excel)); //excel
            wb.write(fout); //excel?
            fout.flush();
            fout.close();
            //MIME
            response.setContentType(request.getSession().getServletContext().getMimeType(excel));
            //Content-Disposition
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(excel, "UTF-8"));
            BufferedInputStream in = new BufferedInputStream(
                    new FileInputStream(savePath + File.separator + excel));//,io?
            OutputStream out = new BufferedOutputStream(response.getOutputStream());
            byte buffer[] = new byte[1024];
            int len = 0;
            while ((len = in.read(buffer)) > 0) {
                out.write(buffer, 0, len);//?response?
            }
            in.close();
            out.flush();
            out.close();
            /***/
            File file = new File(savePath + File.separator + excel);
            if (file != null) {
                if (file.exists()) {
                    file.delete();//
                }
                file = null;
            }
            /***/
            if (fileSavePath != null) {
                if (fileSavePath.exists()) {
                    fileSavePath.delete();
                }
                fileSavePath = null;
            }
            if (savePathFile != null) {
                savePathFile = null;
            }
            log.info("Excel?");
        }
    } catch (Exception e) {
        //e.printStackTrace();
        log.error("exportExcel" + e.getMessage());
        context.put("message", "??");
        return forword("message/message", context);
    }
    return null;
}

From source file:com.dayuan.action.BusinessLoanAction.java

/**
 * ?excel/*from   w  w  w.ja va2  s .  com*/
 * */
@RequestMapping("/exportAllExcel")
public void exportAllExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
    SysUser user = SessionUtils.getUser(request);
    BusLoanInfoModel busLoanInfoModel = new BusLoanInfoModel();
    busLoanInfoModel.setuId(user.getId().toString());
    busLoanInfoModel.setuName(user.getNickName());
    List<BusLoanInfo> list = busLoanInfoService.queryList(busLoanInfoModel);
    if (list != null && list.size() > 0) {
        // webbookExcel  
        HSSFWorkbook wb = new HSSFWorkbook();
        // webbooksheet,Excelsheet  
        HSSFSheet sheet = wb.createSheet("???");

        // sheet0,??poiExcel?short  
        HSSFRow row = sheet.createRow((int) 0);
        // ?   
        HSSFCellStyle style = wb.createCellStyle();
        // ?
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("??");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("??"); //applicationName 
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("?");//urgentCont 
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue(""); // relationship
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("?"); //legalPhone
        cell.setCellStyle(style);
        cell = row.createCell((short) 5);
        cell.setCellValue("?"); //houseAddress
        cell.setCellStyle(style);
        cell = row.createCell((short) 6);
        cell.setCellValue("?"); //companyName
        cell.setCellStyle(style);
        cell = row.createCell((short) 7);
        cell.setCellValue("?");//  ?platformName
        cell.setCellStyle(style);
        cell = row.createCell((short) 8);
        cell.setCellValue(""); //?shopName
        cell.setCellStyle(style);
        cell = row.createCell((short) 9);
        cell.setCellValue("???"); //?,subAccount
        cell.setCellStyle(style);
        cell = row.createCell((short) 10);
        cell.setCellValue("?"); //?,sbuPassword
        cell.setCellStyle(style);

        for (int i = 0; i < list.size(); i++) {
            BusLoanInfo busLoanInfo = list.get(i);
            Integer bid = busLoanInfo.getId();
            BusLoanInfoLegal busLoanInfoLegal = this.busLoanInfoLegalService.getBusLoanInfoLegal(bid);
            BusLoanInfoShop busLoanInfoShop = this.busLoanInfoShopService.getBusLoanInfoShop(bid);

            row = sheet.createRow((int) 1 + i);
            row.createCell((short) 0).setCellValue(i + 1);
            row.createCell((short) 1).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getApplicationName()));
            row.createCell((short) 2).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getUrgentCont()));
            row.createCell((short) 3).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getRelationship()));
            if (busLoanInfoLegal != null) {
                row.createCell((short) 4)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getLegalPhone()));
                row.createCell((short) 5)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getHouseAddress()));
                row.createCell((short) 6)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getCompanyName()));
            } else {
                row.createCell((short) 4).setCellValue("");
                row.createCell((short) 5).setCellValue("");
                row.createCell((short) 6).setCellValue("");
            }
            if (busLoanInfoShop != null) {
                row.createCell((short) 7)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getPlatformName()));
                row.createCell((short) 8).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getShopName()));
                row.createCell((short) 9)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSubAccount()));
                row.createCell((short) 10)
                        .setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSbuPassword()));
            } else {
                row.createCell((short) 7).setCellValue("");
                row.createCell((short) 8).setCellValue("");
                row.createCell((short) 9).setCellValue("");
                row.createCell((short) 10).setCellValue("");
            }
        }
        String savePath = request.getSession().getServletContext().getRealPath(
                File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,??
        savePath = savePath + File.separator + UUID.randomUUID();//?
        File fileSavePath = new File(savePath);
        /**??*/
        if (fileSavePath.exists()) {
            if (fileSavePath.isDirectory()) {
                File[] files = fileSavePath.listFiles();
                for (File file : files) {
                    file.delete();
                }
                fileSavePath.delete();
            } else {
                fileSavePath.delete();
            }
            fileSavePath.mkdirs();
        } else {
            fileSavePath.mkdirs();
        }
        String excel = "????" + DateUtil.getNowPlusTimeMill() + ".xls";//eccel??
        BufferedOutputStream fout = new BufferedOutputStream(
                new FileOutputStream(savePath + File.separator + excel)); //
        wb.write(fout); //excel?
        fout.flush();
        fout.close();
        //MIME
        response.setContentType(request.getSession().getServletContext().getMimeType(excel));
        //Content-Disposition
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excel, "UTF-8"));

        BufferedInputStream in = new BufferedInputStream(
                new FileInputStream(savePath + File.separator + excel));//,io?
        OutputStream out = new BufferedOutputStream(response.getOutputStream());
        byte buffer[] = new byte[1024];
        int len = 0;
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);//?response?
        }
        in.close();
        out.flush();
        out.close();
        /***/
        File file = new File(savePath + File.separator + excel);
        if (file != null) {
            if (file.exists()) {
                file.delete();//
            }
            file = null;
        }
        /***/
        if (fileSavePath != null) {
            if (fileSavePath.exists()) {
                fileSavePath.delete();
            }
            fileSavePath = null;
        }
        log.info("excel?");
    }
}

From source file:com.dayuan.action.BusinessLoanAction.java

/**
 * excel/*from   www  .  j a  va2s  .com*/
 * 
 * */
@RequestMapping("/exportExcel")
public void exportExcel(Integer id, HttpServletRequest request, HttpServletResponse response) throws Exception {
    if (id == null || id.equals("")) {
        log.error("?");
        return;
    }
    BusLoanInfo busLoanInfo = this.busLoanInfoService.queryById(id);
    if (busLoanInfo == null) {
        log.error("busLoanInfo?");
        return;
    }
    Integer bid = busLoanInfo.getId();
    if (bid == null) {
        log.error("busLoanInfo?");
        return;
    }
    BusLoanInfoLegal busLoanInfoLegal = this.busLoanInfoLegalService.getBusLoanInfoLegal(bid);
    BusLoanInfoShop busLoanInfoShop = this.busLoanInfoShopService.getBusLoanInfoShop(bid);

    // webbookExcel  
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet  
    HSSFSheet sheet = wb.createSheet("???");

    // sheet0,??poiExcel?short  
    HSSFRow row = sheet.createRow((int) 0);
    // ?   
    HSSFCellStyle style = wb.createCellStyle();
    // ?
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell((short) 1);
    cell.setCellValue("??"); //applicationName 
    cell.setCellStyle(style);
    cell = row.createCell((short) 2);
    cell.setCellValue("?");//urgentCont 
    cell.setCellStyle(style);
    cell = row.createCell((short) 3);
    cell.setCellValue(""); // relationship
    cell.setCellStyle(style);
    cell = row.createCell((short) 4);
    cell.setCellValue("?"); //legalPhone
    cell.setCellStyle(style);
    cell = row.createCell((short) 5);
    cell.setCellValue("?"); //houseAddress
    cell.setCellStyle(style);
    cell = row.createCell((short) 6);
    cell.setCellValue("?"); //companyName
    cell.setCellStyle(style);
    cell = row.createCell((short) 7);
    cell.setCellValue("?");//  ?platformName
    cell.setCellStyle(style);
    cell = row.createCell((short) 8);
    cell.setCellValue(""); //?shopName
    cell.setCellStyle(style);
    cell = row.createCell((short) 9);
    cell.setCellValue("???"); //?,subAccount
    cell.setCellStyle(style);
    cell = row.createCell((short) 10);
    cell.setCellValue("?"); //?,sbuPassword
    cell.setCellStyle(style);

    // ? ??  
    try {

        row = sheet.createRow((int) 0 + 1);
        row.createCell((short) 0).setCellValue(1);
        row.createCell((short) 1).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getApplicationName()));
        row.createCell((short) 2).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getUrgentCont()));
        row.createCell((short) 3).setCellValue(StringUtil.getNotNullStr(busLoanInfo.getRelationship()));
        if (busLoanInfoLegal != null) {
            row.createCell((short) 4).setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getLegalPhone()));
            row.createCell((short) 5)
                    .setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getHouseAddress()));
            row.createCell((short) 6).setCellValue(StringUtil.getNotNullStr(busLoanInfoLegal.getCompanyName()));
        } else {
            row.createCell((short) 4).setCellValue("");
            row.createCell((short) 5).setCellValue("");
            row.createCell((short) 6).setCellValue("");
        }
        if (busLoanInfoShop != null) {
            row.createCell((short) 7).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getPlatformName()));
            row.createCell((short) 8).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getShopName()));
            row.createCell((short) 9).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSubAccount()));
            row.createCell((short) 10).setCellValue(StringUtil.getNotNullStr(busLoanInfoShop.getSbuPassword()));
        } else {
            row.createCell((short) 7).setCellValue("");
            row.createCell((short) 8).setCellValue("");
            row.createCell((short) 9).setCellValue("");
            row.createCell((short) 10).setCellValue("");
        }
        // Student stu = (Student) list.get(i);  
        // ?  
        //  cell = row.createCell((short) 3);  
        //   cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(stu.getBirth()));  
        //             row.createCell((short) 10).setCellValue(busLoanInfoShop.getSbuPassword());?

        String savePath = request.getSession().getServletContext().getRealPath(
                File.separator + "WEB-INF" + File.separator + "downloads" + File.separator + "excelfiles");//??,??
        savePath = savePath + File.separator + UUID.randomUUID();//?
        File fileSavePath = new File(savePath);
        /**??*/
        if (fileSavePath.exists()) {
            if (fileSavePath.isDirectory()) {
                File[] files = fileSavePath.listFiles();
                for (File file : files) {
                    file.delete();
                }
                fileSavePath.delete();
            } else {
                fileSavePath.delete();
            }
            fileSavePath.mkdirs();
        } else {
            fileSavePath.mkdirs();
        }
        String excel = "????" + DateUtil.getNowPlusTimeMill() + ".xls";//eccel??
        BufferedOutputStream fout = new BufferedOutputStream(
                new FileOutputStream(savePath + File.separator + excel)); //
        wb.write(fout); //excel?
        fout.flush();
        fout.close();
        //MIME
        response.setContentType(request.getSession().getServletContext().getMimeType(excel));
        //Content-Disposition
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excel, "UTF-8"));
        //,io?
        BufferedInputStream in = new BufferedInputStream(
                new FileInputStream(savePath + File.separator + excel));
        OutputStream out = new BufferedOutputStream(response.getOutputStream());
        byte buffer[] = new byte[1024];
        int len = 0;
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);//?response?
        }
        in.close();
        out.flush();
        out.close();
        /***/
        File file = new File(savePath + File.separator + excel);
        if (file != null) {
            if (file.exists()) {
                file.delete();//
            }
            file = null;
        }
        /***/
        if (fileSavePath != null) {
            if (fileSavePath.exists()) {
                fileSavePath.delete();
            }
            fileSavePath = null;
        }
        log.info("???excel?");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:com.dexter.fuelcard.mbean.UserMBean.java

public void offloadCards() {
    FacesContext curContext = FacesContextImpl.getCurrentInstance();
    if (getUploadItem() != null) {
        try {/*from  ww w . jav a2 s.c  o m*/
            HSSFWorkbook workbook = new HSSFWorkbook(getUploadItem().getInputstream());
            int sheetCount = workbook.getNumberOfSheets();
            if (sheetCount >= 1) {
                String password = getRandomDigitPassword();

                HSSFSheet sheet = workbook.getSheetAt(0); // first sheet should be the main sheet
                sheet.protectSheet(password);

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

                CardRequest cr = new CardRequest();
                cr.setRequestRefNum(getActiveUser().getPartner().getCode() + "-" + password);
                cr.setAdditionalComment(getRequestComment());
                cr.setCrt_dt(new Date());
                cr.setExcelFile(byout.toByteArray());
                cr.setPartner(getActiveUser().getPartner());
                cr.setRequest_dt(new Date());
                cr.setRequestedBy(getActiveUser());
                cr.setRequestType("OFFLOAD-CARDS");
                cr.setStatus("PENDING");

                GeneralDAO gDAO = new GeneralDAO();
                gDAO.startTransaction();
                boolean ret = gDAO.save(cr);
                if (ret) {
                    gDAO.commit();
                    setRequestComment(null);
                    setMyPendingCardCancelRequests(null);
                    curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Success",
                            "Request submitted successfully!"));

                    // Send email to user that submitted that their request is been treated
                    String body = "<html><body><p>Dear " + getActiveUser().getFullname()
                            + ",</p><p>Your request to offload cards has been recieved and will be treated as soon as possible. You will be notified of every progress.</p><p>Regards<br/>Fuel Card Platform</p></body></html>";
                    Emailer.sendEmail("fuelcard@sattrakservices.com",
                            new String[] { getActiveUser().getEmail() },
                            "Card OffLoad Request - " + cr.getRequestRefNum() + " Received", body);
                    // Send email to sattrak that request is submitted
                    byte[] doc = cr.getExcelFile();
                    String body2 = "<html><body><p>Hello,</p><p>A request to offload cards has been submitted. The document is attached.</p><p>Regards<br/>Fuel Card Platform</p></body></html>";
                    if (getSattrakPartner() != null && getSattrakPartner().getContactEmails() != null
                            && getSattrakPartner().getContactEmails().trim().length() > 0) {
                        String[] to = getSattrakPartner().getContactEmails().split(",");
                        Emailer.sendEmail("fuelcard@sattrakservices.com", to,
                                "Card OffLoad Request - " + cr.getRequestRefNum() + " Submitted", body2, doc,
                                cr.getRequestRefNum() + ".xls", "application/vnd.ms-excel");
                    }
                } else {
                    gDAO.rollback();
                    curContext.addMessage(null,
                            new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", gDAO.getMessage()));
                }
                gDAO.destroy();
            } else {
                curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed",
                        "Excel document is not valid!"));
            }
        } catch (Exception ex) {
            curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Severe",
                    "Please upload an excel document! Error: " + ex.getMessage()));
        }
    } else {
        curContext.addMessage(null,
                new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Please upload an excel document!"));
    }
}