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.hp.action.ReportSaleWithProductsAction.java

public String exportSaleOrderList() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize/* ww  w.  ja  v a  2s . c o  m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    reportSaleWithProductList = (List<ReportSaleWithProduct>) session.getAttribute("reportSaleWithProductList");

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Sale Order with product");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //SaleOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                8 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co bn hng theo sn phm");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                8 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write SaleOrder
    for (int i = 0; i < reportSaleWithProductList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(reportSaleWithProductList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

        //SUM ROW
        if (i == (reportSaleWithProductList.size() - 1)) {
            Row rowEnd = sheet.createRow(i + 7);
            for (int j = (titleArray().length - 3); j < (titleArray().length); j++) {
                Cell cell = rowEnd.createCell(j);
                cell.setCellValue((Double) sumArray()[j - (titleArray().length - 3)]);
            }
        }
    }

    outputFile = "BaoCaoBanHangTheoSanPham" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

From source file:com.hp.action.ReportTakeOrderAction.java

public String exportTakeOrderList() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize/*  ww w .j  a  v a  2s . c o m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    takeOrdersList = (List<TakeOrder>) session.getAttribute("takeOrdersList");
    takeOrderDetailList = (List<List<TakeOrderDetail>>) session.getAttribute("takeOrderDetailList");

    if (takeOrdersList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Take Order");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                8 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co ha n t hng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                8 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < takeOrdersList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(takeOrdersList.get(i), takeOrderDetailList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

        //SUM ROW
        if (i == (takeOrdersList.size() - 1)) {
            Row rowEnd = sheet.createRow(i + 7);
            for (int j = (titleArray().length - 5); j < (titleArray().length - 1); j++) {
                Cell cell = rowEnd.createCell(j);
                cell.setCellValue((Double) sumArray()[j - (titleArray().length - 5)]);
            }
        }
    }

    outputFile = "BaoCaoHoaDonDatHang" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

From source file:com.hp.action.SetLunchAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize//w  w  w .ja  v  a 2 s. com
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    setLunchList = (List<SetLunch>) session.getAttribute("setLunchList");

    if (setLunchList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Bo cm");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo cm vn phng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < setLunchList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(setLunchList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "BaoComVanPhong" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

From source file:com.hp.action.StaffHistoryAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize//w ww  . j av  a 2s  . c o m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    listStaffHistory = (List<StaffHistory>) session.getAttribute("listStaffHistory");

    if (listStaffHistory == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Chm sc khch hng");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co chm sc khch hng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < listStaffHistory.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(listStaffHistory.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "BaoCaoChamSocKhachHang" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

From source file:com.hp.action.StaffsAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize//  w  ww  .  j a  v a2s .  c om
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    staffsList = (List<Staff>) session.getAttribute("staffsList");

    if (staffsList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Nhn vin");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                10 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Danh sch nhn vin");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                10 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        cell1.setCellValue("");

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < staffsList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(staffsList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "DanhSachNhanVien.xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

From source file:com.hp.action.TakeOrderAction.java

public String exportOrders() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    //Authorize/*www  . ja  v a 2  s .co m*/
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    staffList = staffDAO.getListUser(null);

    String fileInput = ServletActionContext.getServletContext().getRealPath("/database/");
    System.out.println("Result: " + staff + " from date: " + fromDate + " , to date: " + toDate);

    takeOrdersList = takeOrderDAO.getTakeOrdersList(staff, fromDate, toDate);
    if (takeOrdersList.isEmpty())
        return INPUT;

    Map<String, List<TakeOrderDetail>> data = new HashMap<String, List<TakeOrderDetail>>();

    for (int i = 0; i < takeOrdersList.size(); i++) {
        data.put(i + "", takeOrderDetailDAO.getDetailTakeOrdersList(takeOrdersList.get(i).getId()));

    }

    int line = 0;
    Set<String> keyset = data.keySet();

    for (String key : keyset) {
        line += data.get(key).size();
    }

    line += keyset.size();

    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Order");

    //TakeOrder title
    for (int i = 0; i < 1; i++) {
        Row row = sheet.createRow(i);
        int cellnum = 0;

        for (Object obj : takeOrdersList.get(i).toTitleArray()) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }
    }
    //Write TakeOrder
    for (int i = 0; i < takeOrdersList.size(); i++) {
        Row row = sheet.createRow(i + 1);
        int cellnum = 0;

        for (Object obj : takeOrdersList.get(i).toArray()) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }
    }

    //Write TakeOrderDetail title
    for (String key : keyset) {
        for (int i = 0; i < 1; i++) {
            Row row = sheet.createRow(i + takeOrdersList.size() + 3);
            int cellnum = 0;
            for (Object obj : data.get(key).get(i).toTitleArray()) {
                Cell cell = row.createCell(cellnum++);

                if (obj instanceof Timestamp)
                    cell.setCellValue((Timestamp) obj);
                else if (obj instanceof Boolean)
                    cell.setCellValue((Boolean) obj);
                else if (obj instanceof String)
                    cell.setCellValue((String) obj);
                else if (obj instanceof Float)
                    cell.setCellValue((Float) obj);
                else if (obj instanceof Integer)
                    cell.setCellValue((Integer) obj);

            }

        }
    }

    //Write TakeOrderDetail
    for (String key : keyset) {
        for (int i = 0; i < data.get(key).size(); i++) {
            Row row = sheet.createRow(i + takeOrdersList.size() + 4);
            int cellnum = 0;
            for (Object obj : data.get(key).get(i).toArray()) {
                Cell cell = row.createCell(cellnum++);

                if (obj instanceof Timestamp)
                    cell.setCellValue((Timestamp) obj);
                else if (obj instanceof Boolean)
                    cell.setCellValue((Boolean) obj);
                else if (obj instanceof String)
                    cell.setCellValue((String) obj);
                else if (obj instanceof Float)
                    cell.setCellValue((Float) obj);
                else if (obj instanceof Integer)
                    cell.setCellValue((Integer) obj);

            }

        }
    }

    //Write to xls
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\test.xls"));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\test.xls"));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return SUCCESS;
}

From source file:com.hp.action.TimeKeeperAction.java

public String exportExcel() {
    HttpServletRequest request = (HttpServletRequest) ActionContext.getContext()
            .get(ServletActionContext.HTTP_REQUEST);
    HttpSession session = request.getSession();

    user = (User) session.getAttribute("USER");

    //Authorize// w w w . ja va  2s  .co m
    if (!userDAO.authorize((String) session.getAttribute("user_name"),
            (String) session.getAttribute("user_password"))) {
        return LOGIN;
    }

    //GET DATA
    timeKeeperList = (List<TimeKeeper>) session.getAttribute("timeKeeperList");

    if (timeKeeperList == null)
        return INPUT;

    String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/");
    String start = (String) session.getAttribute("startDate");
    String end = (String) session.getAttribute("endDate");

    //
    //Write
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet("Chm cng");
    //sheet.autoSizeColumn(200);
    sheet.setColumnWidth(0, 1000);
    sheet.setDefaultColumnWidth(20);

    //TakeOrder title
    for (int i = 1; i < 2; i++) {
        //
        Row rowstart = sheet.createRow(0);

        //Row Title
        Row row0 = sheet.createRow(i);
        row0.setHeight((short) 500);
        Cell cell0 = row0.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based)
                i, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER);
        CellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

        //font
        Font headerFont = workBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontHeight((short) 250);
        cellStyle.setFont(headerFont);

        cell0.setCellStyle(cellStyle);
        cell0.setCellValue("Bo co chm cng");

        //Row date
        Row row1 = sheet.createRow(i + 1);
        //row1.setHeight((short)500);
        Cell cell1 = row1.createCell(0);

        //Merge for title
        sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based)
                i + 1, //last row  (0-based)
                0, //first column (0-based)
                5 //last column  (0-based)
        ));
        CellStyle cellAlign = workBook.createCellStyle();
        cellAlign.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(cellAlign);

        if (start == null)
            start = "";
        if (end == null)
            end = "";
        cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end);

        //Row Header
        Row row = sheet.createRow(4);
        int cellnum = 0;

        for (Object obj : titleArray()) {
            Cell cell = row.createCell(cellnum++);

            CellStyle style = workBook.createCellStyle();
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);

            cell.setCellStyle(style);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float)
                cell.setCellValue((Float) obj);
        }

    }
    //Write TakeOrder
    for (int i = 0; i < timeKeeperList.size(); i++) {
        Row row = sheet.createRow(i + 5);
        int cellnum = 0;

        //Cell 0 - stt
        Cell cell0 = row.createCell(cellnum++);
        cell0.setCellValue(i + 1);

        //Set content
        for (Object obj : objectArray(timeKeeperList.get(i))) {
            Cell cell = row.createCell(cellnum++);

            if (obj instanceof Timestamp)
                cell.setCellValue((Timestamp) obj);
            else if (obj instanceof Boolean)
                cell.setCellValue((Boolean) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
            else if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Float) {

                //                    CellStyle cellStyle = workBook.createCellStyle();
                //                    DataFormat format = workBook.createDataFormat();
                //                    cellStyle.setDataFormat(format.getFormat("#.#"));
                //                    cell.setCellStyle(cellStyle);

                cell.setCellValue((Float) obj);
            } else if (obj instanceof Double)
                cell.setCellValue((Double) obj);
        }

    }

    outputFile = "BaoCaoChamCong" + start + " - " + end + ".xls";
    try {
        FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile));

        workBook.write(output);
        output.close();
        System.out.println("Excel written successfully..");
        orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile));

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

From source file:com.hris.payroll.thirteenthmonth.ExportDataGridToExcel.java

public void workSheet() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sample sheet");

    int rownum = 1;
    for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) {
        System.out.println("itemId: " + itemId);
    }//from w  w  w. j a va 2s  .  c  o m
    for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) {
        Row row = sheet.createRow(rownum);
        int cellcount = 0;
        if ((rownum - 1) == 0) {
            Row rowHeader = sheet.createRow(rownum - 1);
            for (Object propertyId : getDataGrid().getContainerDataSource().getContainerPropertyIds()) {
                Cell cell = rowHeader.createCell(cellcount);
                if (propertyId.toString().contains("salary")) {
                    if (!propertyId.toString().equals("salary grand total")) {
                        cell.setCellValue(propertyId.toString().replace("salary", "").toUpperCase());
                    } else {
                        cell.setCellValue(propertyId.toString().toUpperCase());
                    }
                } else if (propertyId.toString().contains("tax")) {
                    if (!propertyId.toString().equals("tax grand total")) {
                        cell.setCellValue(propertyId.toString().replace("tax", "").toUpperCase());
                    } else {
                        cell.setCellValue(propertyId.toString().toUpperCase());
                    }
                } else if (propertyId.toString().contains("sss")) {
                    if (!propertyId.toString().equals("sss grand total")) {
                        cell.setCellValue(propertyId.toString().replace("sss", "").toUpperCase());
                    } else {
                        cell.setCellValue(propertyId.toString().toUpperCase());
                    }
                } else if (propertyId.toString().contains("phic")) {
                    if (!propertyId.toString().equals("phic grand total")) {
                        cell.setCellValue(propertyId.toString().replace("phic", "").toUpperCase());
                    } else {
                        cell.setCellValue(propertyId.toString().toUpperCase());
                    }
                } else if (propertyId.toString().contains("hdmf")) {
                    if (!propertyId.toString().equals("hdmf grand total")) {
                        cell.setCellValue(propertyId.toString().replace("hdmf", "").toUpperCase());
                    } else {
                        cell.setCellValue(propertyId.toString().toUpperCase());
                    }
                }

                else {
                    cell.setCellValue(propertyId.toString().toUpperCase());
                }

                sheet.autoSizeColumn(cellcount);
                cellcount++;
            }
        }

        Item item = getDataGrid().getContainerDataSource().getItem(itemId);
        int cellnum = 0;
        for (Object propertyId : item.getItemPropertyIds()) {
            Cell cell = row.createCell(cellnum);
            if (propertyId.equals("employee")) {
                cell.setCellValue(item.getItemProperty(propertyId).getValue().toString().toUpperCase());
            } else {
                cell.setCellValue((item.getItemProperty(propertyId).getValue() == null) ? " "
                        : item.getItemProperty(propertyId).getValue().toString());
            }

            sheet.autoSizeColumn(cellnum);
            cellnum++;
        }
        rownum++;
    }

    FileOutputStream fos;
    try {
        Date date = new Date();
        path = "C:/payroll-files/format-" + date.getTime() + ".xls";
        file = new File(path);
        fos = new FileOutputStream(path);
        workbook.write(fos);
        fos.flush();
        fos.close();
    } catch (FileNotFoundException ex) {
        ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
        Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName());
        Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
    }

    StreamResource.StreamSource source = () -> {
        try {
            File f = new File(path);
            FileInputStream fis = new FileInputStream(f);
            return fis;
        } catch (Exception e) {
            e.getMessage();
            return null;
        }
    };

    StreamResource resource = new StreamResource(source, "ThirteenthMonth.xls");
    resource.setMIMEType("application/vnd.ms-office");

}

From source file:com.ibm.asset.trails.service.impl.CauseCodeServiceImpl.java

@Transactional(readOnly = true, propagation = Propagation.REQUIRES_NEW)
public ByteArrayOutputStream loadSpreadsheet(File file, String remoteUser, List<State> steps)
        throws IOException {

    ByteArrayOutputStream bos = null;

    FileInputStream fin = new FileInputStream(file);
    HSSFWorkbook wb = new HSSFWorkbook(fin);

    HSSFSheet sheet = wb.getSheetAt(0);//from ww  w  .  ja v  a2  s .c o  m

    HSSFCell reportNameCell = sheet.getRow(ROW_ALERT_TYPE).getCell(COL_ALERT_TYPE);
    String reportName = reportNameCell.getStringCellValue().trim();

    HSSFCellStyle errorStyle = wb.createCellStyle();
    errorStyle.setFillForegroundColor(HSSFColor.RED.index);
    errorStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    colIndexes = ECauseCodeReport.getReportByName(reportName);

    boolean error = validateExcelCauseCodeContent(sheet, errorStyle, steps);

    if (!error) {
        saveCauseCode(wb, remoteUser, steps);
    } else {
        State state = State.findStateByLable(steps, STEP3_LABEL);
        if (state == null) {
            state = new State();
            state.setDescription("Persist changes");
            state.setLabel(STEP3_LABEL);
            state.setStatus(EStatus.IGNORED);
            steps.add(state);
        }
    }

    bos = new ByteArrayOutputStream();
    wb.write(bos);

    return bos;

}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertExpiredScanReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {

    AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode")
            .setParameter("code", "EXP_SCAN").getSingleResult();

    StringBuffer dataQuery = new StringBuffer(
            "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, ");
    dataQuery.append(/*from w  w w  . j  a va 2s .c o  m*/
            "SL.Name, SL.Bios_Serial, VA.Creation_Time, VA.Alert_Age, MT.Type, HL.OS_Type, VA.Remote_User, VA.Comments, ")
            .append("VA.Record_Time , AC.name as ac_name, CC.target_date,CC.owner as cc_owner,CC.record_time as cc_record_time, ")
            .append("CC.remote_user as cc_remote_user, CC.id as cc_id ").append("FROM EAADMIN.V_Alerts VA ")
            .append("JOIN EAADMIN.Software_Lpar SL ON SL.Id = VA.FK_Id ")
            .append("JOIN EAADMIN.cause_code CC ON (VA.id = CC.alert_id AND CC.alert_type_id = :alertTypeId) ")
            .append("JOIN EAADMIN.alert_cause AC ON CC.alert_cause_id = AC.id ")
            .append("LEFT OUTER JOIN EAADMIN.hw_sw_composite HSC on HSC.software_lpar_id = SL.id ")
            .append("LEFT OUTER JOIN EAADMIN.hardware_lpar HL on HL.id = HSC.hardware_lpar_id ")
            .append("LEFT OUTER JOIN EAADMIN.hardware H on H.id = HL.hardware_id ")
            .append("LEFT OUTER JOIN EAADMIN.machine_type MT on MT.id = H.machine_type_id ")
            .append("WHERE VA.Customer_Id = :customerId AND VA.Type = :type AND VA.Open = 1 ")
            .append("ORDER BY SL.Name ASC");

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate())
            .createSQLQuery(dataQuery.toString()).setLong("customerId", pAccount.getId())
            .setString("type", "EXPIRED_SCAN").setInteger("alertTypeId", alertType.getId().intValue())
            .scroll(ScrollMode.FORWARD_ONLY);
    HSSFSheet sheet = phwb.createSheet("Alert Unexpired SW Lpar " + pAccount.getAccount() + " Report");
    printHeader(ALERT_EXPIRED_SCAN_REPORT_NAME, pAccount.getAccount(), ALERT_EXPIRED_SCAN_REPORT_COLUMN_HEADERS,
            sheet);
    int i = 3;
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert Unexpired SW Lpar " + pAccount.getAccount() + " Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId())
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}