Example usage for org.apache.poi.hssf.usermodel HSSFSheet getPrintSetup

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getPrintSetup

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getPrintSetup

Introduction

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

Prototype

@Override
public HSSFPrintSetup getPrintSetup()

Source Link

Document

Gets the print setup object.

Usage

From source file:excel.FileExcel.java

public File excel_create_order_pusat_coming(ArrayList<ComingOrderReportData> comingorder,
        Date comingorderdate) {/*from   ww w. j a va 2  s .  c o  m*/
    if (comingorder.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "ComingOrder_" + fmt.format(comingorderdate) + "_" + time.format(comingorderdate)
                + ".xls";
        File ExcelComingOrder = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL COMING ORDER");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal Stock : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(comingorderdate));

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Konter");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Nama Barang");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kategori");

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        // Sorting
        Collections.sort(comingorder, new Comparator<ComingOrderReportData>() {
            @Override
            public int compare(ComingOrderReportData data2, ComingOrderReportData data1) {
                return data1.nama_barang.compareTo(data2.nama_barang);
            }
        });

        for (int i = 0; i < comingorder.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kode_konter);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kode_barang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).jumlah_barang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).nama_barang);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(comingorder.get(i).kategori);

        }

        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);

        try {
            // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
            FileOutputStream out = new FileOutputStream(ExcelComingOrder);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelComingOrder = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelComingOrder = null;
        }
        return ExcelComingOrder;
    } else {
        return null;
    }
}

From source file:excel.FileExcel.java

public File excel_create_popular(ArrayList<PopulerData> PopulerItem, Date startdate, Date enddate) {
    if (PopulerItem.size() != 0) {
        String fileName = "PopularItem_" + fmt.format(startdate) + "-" + fmt.format(enddate) + ".xls";
        File ExcelPopular = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);//w  w w .ja  v a2 s.  c  o  m

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL ORDER POPULAR");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(1);
        cell.setCellValue("Tanggal : ");
        cell = header.createCell(2);
        cell.setCellValue(fmt.format(startdate));
        cell = header.createCell(3);
        cell.setCellValue(" - ");
        cell = header.createCell(4);
        cell.setCellValue(fmt.format(enddate));

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(6);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah Order");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah Konter");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Stok");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        for (int i = 0; i < PopulerItem.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(PopulerItem.get(i).kode_barang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(PopulerItem.get(i).jumlah_order);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(PopulerItem.get(i).jumlah_konter);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(PopulerItem.get(i).stock);
        }

        try {
            FileOutputStream out = new FileOutputStream(ExcelPopular);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelPopular = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelPopular = null;
        }
        return ExcelPopular;
    } else {
        return null;
    }
}

From source file:excel.FileExcel.java

public File excel_create_kirim_barang(ArrayList<ItemReadyData> ItemsReadyData, Date currentdate) {
    if (ItemsReadyData.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "Kirim_" + fmt.format(currentdate) + "_" + time.format(currentdate) + "_konter_"
                + ItemsReadyData.get(0).kode_konter + ".xls";
        File ExcelKirimBarang = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(true);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);// w  w  w  .  jav  a  2s  .c o  m

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL KIRIM BARANG");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 9));

        // create file info
        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(currentdate));

        header = sheet.createRow(4);
        cell = header.createCell(2);
        cell.setCellValue("Konter : ");
        cell = header.createCell(3);
        cell.setCellValue(ItemsReadyData.get(0).kode_konter);

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(7);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Nama Barang");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kategori");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga TPG");
        cell = header.createCell(6);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Discount");
        cell = header.createCell(7);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga Net");
        cell = header.createCell(8);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Total TPG");
        cell = header.createCell(9);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Total Net");

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        HSSFDataFormat df = workbook.createDataFormat();
        datastyle.setDataFormat(df.getFormat("#,###"));
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        double net = 0;
        double total_tpg = 0;
        double total_net = 0;
        double sum_total_tpg = 0;
        double sum_total_net = 0;
        int j;

        // fill the data
        for (j = 0; j < ItemsReadyData.size(); j++) {
            net = (double) ItemsReadyData.get(j).harga_tpg
                    * (100.0 - (double) ItemsReadyData.get(j).disc_member) / 100.0;
            total_tpg = (double) ItemsReadyData.get(j).harga_tpg * (double) ItemsReadyData.get(j).jumlah_barang;
            total_net = (double) net * (double) ItemsReadyData.get(j).jumlah_barang;
            sum_total_tpg += total_tpg;
            sum_total_net += total_net;

            header = sheet.createRow(8 + j);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).kode_barang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).jumlah_barang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).nama_barang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).kategori);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).harga_tpg);
            cell = header.createCell(6);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).disc_member);
            cell = header.createCell(7);
            cell.setCellStyle(datastyle);
            cell.setCellValue(net);
            cell = header.createCell(8);
            cell.setCellStyle(datastyle);
            cell.setCellValue(total_tpg);
            cell = header.createCell(9);
            cell.setCellStyle(datastyle);
            cell.setCellValue(total_net);
        }

        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);
        sheet.autoSizeColumn(6);
        sheet.autoSizeColumn(7);
        sheet.autoSizeColumn(8);
        sheet.autoSizeColumn(9);

        datastyle.setFont(boldfont);
        header = sheet.createRow(j + 8);
        cell = header.createCell(7);
        cell.setCellStyle(datastyle);
        cell.setCellValue("TOTAL");
        cell = header.createCell(8);
        cell.setCellStyle(datastyle);
        cell.setCellValue(sum_total_tpg);
        cell = header.createCell(9);
        cell.setCellStyle(datastyle);
        cell.setCellValue(sum_total_net);

        try {
            FileOutputStream out = new FileOutputStream(ExcelKirimBarang);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelKirimBarang = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelKirimBarang = null;
        }
        return ExcelKirimBarang;
    } else {
        return null;
    }
}

From source file:excel.FileExcel.java

public File excel_create_cari_gudang(ArrayList<CariGudangReportData> CariGudang, Date waktuprint) {
    // find number of counter, save in Counter Index
    if (!CariGudang.isEmpty()) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "CariGudang_" + fmt.format(waktuprint) + "_" + time.format(waktuprint) + ".xls";
        File FileCariGudang = new File(fileName);

        HSSFWorkbook workbook;//from  ww  w  .ja v  a 2  s . c  o  m
        HSSFSheet sheet;

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

        // set page
        HSSFPrintSetup ps = sheet.getPrintSetup();
        ps.setLandscape(true);
        ps.setFitHeight((short) 1);
        ps.setFitWidth((short) 1);
        sheet.setFitToPage(true);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL CARI GUDANG");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));

        // create file info
        header = sheet.createRow(4);
        cell = header.createCell(1);
        cell.setCellValue("Tanggal : ");
        cell = header.createCell(2);
        cell.setCellValue(fmt.format(waktuprint));

        header = sheet.createRow(5);
        cell = header.createCell(1);
        cell.setCellValue("Jam : ");
        cell = header.createCell(2);
        cell.setCellValue(time.format(waktuprint));

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(7);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Order");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Konter");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Nama Barang");
        cell = header.createCell(6);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kategori");
        cell = header.createCell(7);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("HargaTPG");
        cell = header.createCell(8);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Disc");
        cell = header.createCell(9);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga Net");
        cell = header.createCell(10);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Total Net");

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        HSSFDataFormat df = workbook.createDataFormat();
        datastyle.setDataFormat(df.getFormat("#,###"));
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        int row_num = 0;

        double net = 0;
        double total_tpg = 0;
        double total_net = 0;
        double sum_total_tpg = 0;
        double sum_total_net = 0;

        int j;
        for (j = 0; j < CariGudang.size(); j++) {
            net = (double) CariGudang.get(j).harga_tpg * (100.0 - (double) CariGudang.get(j).disc) / 100.0;
            total_net = (double) net * (double) CariGudang.get(j).jumlah;
            sum_total_tpg += total_tpg;
            sum_total_net += total_net;

            header = sheet.createRow(8 + row_num);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(CariGudang.get(j).kode_order);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(CariGudang.get(j).kode_konter);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(CariGudang.get(j).kode_barang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(CariGudang.get(j).jumlah);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(CariGudang.get(j).nama_barang);
            cell = header.createCell(6);
            cell.setCellStyle(datastyle);
            cell.setCellValue(CariGudang.get(j).kategori);
            cell = header.createCell(7);
            cell.setCellStyle(datastyle);
            cell.setCellValue(CariGudang.get(j).harga_tpg);
            cell = header.createCell(8);
            cell.setCellStyle(datastyle);
            cell.setCellValue(CariGudang.get(j).disc);
            cell = header.createCell(9);
            cell.setCellStyle(datastyle);
            cell.setCellValue(net);
            cell = header.createCell(10);
            cell.setCellStyle(datastyle);
            cell.setCellValue(total_net);
            row_num++;
        }

        datastyle.setFont(boldfont);
        header = sheet.createRow(j + 8);
        cell = header.createCell(9);
        cell.setCellStyle(datastyle);
        cell.setCellValue("TOTAL");
        cell = header.createCell(10);
        cell.setCellStyle(datastyle);
        cell.setCellValue(sum_total_net);

        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);
        sheet.autoSizeColumn(6);
        sheet.autoSizeColumn(7);
        sheet.autoSizeColumn(8);
        sheet.autoSizeColumn(9);
        sheet.autoSizeColumn(10);

        try {
            FileOutputStream out = new FileOutputStream(FileCariGudang);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            FileCariGudang = null;
        } catch (IOException e) {
            e.printStackTrace();
            FileCariGudang = null;
        }
        return FileCariGudang;
    } else {
        return null;
    }
}

From source file:excel.FileExcel.java

public File excel_create_dead_style(ArrayList<DeadStyleData> DSData, Date currentdate, int discDS) {
    if (DSData.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "DeadStyle_" + fmt.format(currentdate) + "_" + time.format(currentdate) + ".xls";
        File ExcelDeadStyle = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);//from   w w  w  . ja  va  2 s.  c o  m

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL DEAD STYLE");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 9));

        // create file info
        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(currentdate));

        header = sheet.createRow(4);
        cell = header.createCell(2);
        cell.setCellValue("Jam : ");
        cell = header.createCell(3);
        cell.setCellValue(time.format(currentdate));

        header = sheet.createRow(5);
        cell = header.createCell(2);
        cell.setCellValue("Diskon : ");
        cell = header.createCell(3);
        cell.setCellValue(discDS);

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(7);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Nama Barang");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Jumlah");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Kategori");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga TPG");
        cell = header.createCell(6);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Discount");
        cell = header.createCell(7);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga Net");
        cell = header.createCell(8);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Total TPG");
        cell = header.createCell(9);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Total Net");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);
        sheet.autoSizeColumn(6);
        sheet.autoSizeColumn(7);
        sheet.autoSizeColumn(8);
        sheet.autoSizeColumn(9);

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        double net = 0;
        double total_tpg = 0;
        double total_net = 0;
        double sum_total_tpg = 0;
        double sum_total_net = 0;
        int j;
        double dsdisc;

        // fill the data
        for (j = 0; j < DSData.size(); j++) {
            dsdisc = (DSData.get(j).disc_member == 10) ? 0 : discDS;
            net = (double) DSData.get(j).harga_tpg * (100.0 - dsdisc) / 100.0;
            total_tpg = (double) DSData.get(j).harga_tpg * (double) DSData.get(j).Jumlah;
            total_net = (double) net * (double) DSData.get(j).Jumlah;
            sum_total_tpg += total_tpg;
            sum_total_net += total_net;

            header = sheet.createRow(8 + j);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(DSData.get(j).kode_barang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(DSData.get(j).nama_barang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(DSData.get(j).Jumlah);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(DSData.get(j).kategori);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(DSData.get(j).harga_tpg);
            cell = header.createCell(6);
            cell.setCellStyle(datastyle);
            cell.setCellValue(DSData.get(j).disc_member);
            cell = header.createCell(7);
            cell.setCellStyle(datastyle);
            cell.setCellValue(net);
            cell = header.createCell(8);
            cell.setCellStyle(datastyle);
            cell.setCellValue(total_tpg);
            cell = header.createCell(9);
            cell.setCellStyle(datastyle);
            cell.setCellValue(total_net);
        }

        datastyle.setFont(boldfont);
        header = sheet.createRow(j + 8);
        cell = header.createCell(7);
        cell.setCellStyle(datastyle);
        cell.setCellValue("TOTAL");
        cell = header.createCell(8);
        cell.setCellStyle(datastyle);
        cell.setCellValue(sum_total_tpg);
        cell = header.createCell(9);
        cell.setCellStyle(datastyle);
        cell.setCellValue(sum_total_net);

        try {
            FileOutputStream out = new FileOutputStream(ExcelDeadStyle);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelDeadStyle = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelDeadStyle = null;
        }
        return ExcelDeadStyle;
    } else {
        return null;
    }
}

From source file:excel.FileExcel.java

public File excel_mutation_report(ArrayList<MutationReportData> datamutation, KodeNamaKonter konterinfo,
        Date Start, Date End, Date CurrentDate) {
    if (datamutation.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "Mutation_" + konterinfo.nama_konter + "_" + fmt.format(CurrentDate) + "_"
                + time.format(CurrentDate) + ".xls";
        File ExcelMutation = new File(fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // set page
        sheet.getPrintSetup().setLandscape(false);

        //Set Header Information 
        Header headerPage = sheet.getHeader();
        headerPage.setCenter(HeaderFooter.page());
        headerPage.setRight(fileName);//from   www  . j  a va  2s  .co  m

        //Set Footer Information with Page Numbers
        Footer footerPage = sheet.getFooter();
        footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages());

        // prepare variable to edit the xls
        HSSFRow header;
        HSSFCell cell;
        HSSFCellStyle titlestyle = workbook.createCellStyle();
        HSSFCellStyle headerstyle = workbook.createCellStyle();
        HSSFCellStyle datastyle = workbook.createCellStyle();
        HSSFFont boldfont = workbook.createFont();
        HSSFFont normalfont = workbook.createFont();

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL MUTATION REPORT");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3));

        // create file info
        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Konter : ");
        cell = header.createCell(3);
        cell.setCellValue(konterinfo.nama_konter);

        header = sheet.createRow(4);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(Start));
        cell = header.createCell(4);
        cell.setCellValue("-");
        cell = header.createCell(5);
        cell.setCellValue(fmt.format(End));

        // create the header
        headerstyle.setFont(boldfont);
        headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        header = sheet.createRow(7);
        cell = header.createCell(1);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Tanggal");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Barang Masuk");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Setoran Masuk");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);

        normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        datastyle.setFont(normalfont);
        datastyle.setAlignment(CellStyle.ALIGN_RIGHT);
        datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        int i;
        // fill the data
        for (i = 0; i < datamutation.size(); i++) {
            header = sheet.createRow(8 + i);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(fmt.format(datamutation.get(i).tanggal_mutasi));
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(datamutation.get(i).barang_keluar);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(datamutation.get(i).setoran);
        }

        datastyle.setFont(boldfont);
        header = sheet.createRow(i + 10);
        cell = header.createCell(1);
        cell.setCellStyle(datastyle);
        cell.setCellValue("Hutang Konter");
        cell = header.createCell(2);
        cell.setCellStyle(datastyle);
        cell.setCellValue(fmt.format(CurrentDate));
        cell = header.createCell(3);
        cell.setCellStyle(datastyle);
        cell.setCellValue(konterinfo.hutang_konter);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);

        try {
            FileOutputStream out = new FileOutputStream(ExcelMutation);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelMutation = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelMutation = null;
        }
        return ExcelMutation;
    } else {
        return null;
    }
}

From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java

License:Apache License

/**
 * Ralise l'export des donnes de contenu et de la ligne d'en-tte.
 *
 * @param parameters Paramtre de cet export
 * @param workbook Document excel//from  ww w. jav  a  2  s.c  o m
 * @param sheet Feuille Excel
 * @param forceLandscape Indique si le parametrage force un affichage en paysage
 */
private void exportData(final ExportSheet parameters, final HSSFWorkbook workbook, final HSSFSheet sheet,
        final boolean forceLandscape) {
    initHssfStyle(workbook);

    // Column width
    final Map<Integer, Double> maxWidthPerColumn = new HashMap<>();
    if (parameters.hasDtObject()) {
        exportObject(parameters, workbook, sheet, maxWidthPerColumn);
    } else {
        exportList(parameters, workbook, sheet, maxWidthPerColumn);
    }
    // On definit la largeur des colonnes:
    double totalWidth = 0;
    int cellIndex;
    for (final Map.Entry<Integer, Double> entry : maxWidthPerColumn.entrySet()) {
        cellIndex = entry.getKey();
        final Double maxLength = entry.getValue();
        final double usesMaxLength = Math.min(maxLength.doubleValue(), MAX_COLUMN_WIDTH);
        sheet.setColumnWidth(cellIndex, Double.valueOf(usesMaxLength * 256).intValue());
        totalWidth += usesMaxLength;
    }
    /**
     * @todo ne serait-il pas plus simple d'utilisersheet.autoSizeColumn(i); de poi 3.0.1 ?
     */

    // note: il ne semble pas simple de mettre title et author dans les
    // proprits du document
    final String title = parameters.getTitle();
    if (title != null) {
        final HSSFHeader header = sheet.getHeader();
        header.setLeft(title);
    }
    sheet.setHorizontallyCenter(true);
    sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE);
    if (forceLandscape || totalWidth > 85) {
        sheet.getPrintSetup().setLandscape(true);
    }

    // On dfinit le footer
    final HSSFFooter footer = sheet.getFooter();
    footer.setRight("Page " + HeaderFooter.page() + " / " + HeaderFooter.numPages());
}

From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java

License:Apache License

/**
 * Ralise l'export des donnes de contenu et de la ligne d'en-tte.
 *
 * @param parameters Paramtre de cet export
 * @param workbook Document excel/*w  w  w.j  a  va 2s . co m*/
 * @param sheet Feuille Excel
 * @param forceLandscape Indique si le parametrage force un affichage en paysage
 */
private void exportData(final ExportSheet parameters, final HSSFWorkbook workbook, final HSSFSheet sheet,
        final boolean forceLandscape) {
    // Column width
    final Map<Integer, Double> maxWidthPerColumn = new HashMap<>();
    if (parameters.hasDtObject()) {
        exportObject(parameters, workbook, sheet, maxWidthPerColumn);
    } else {
        exportList(parameters, workbook, sheet, maxWidthPerColumn);
    }
    // On definit la largeur des colonnes:
    double totalWidth = 0;
    int cellIndex;
    for (final Map.Entry<Integer, Double> entry : maxWidthPerColumn.entrySet()) {
        cellIndex = entry.getKey();
        final Double maxLength = entry.getValue();
        final int usesMaxLength = Double.valueOf(Math.min(maxLength.doubleValue(), MAX_COLUMN_WIDTH))
                .intValue();
        sheet.setColumnWidth(cellIndex, usesMaxLength * 256);
        totalWidth += usesMaxLength;
    }
    /**
     * @todo ne serait-il pas plus simple d'utilisersheet.autoSizeColumn(i); de poi 3.0.1 ?
     */

    // note: il ne semble pas simple de mettre title et author dans les proprits du document
    final String title = parameters.getTitle();
    if (title != null) {
        final HSSFHeader header = sheet.getHeader();
        header.setLeft(title);
    }
    sheet.setHorizontallyCenter(true);
    sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE);
    if (forceLandscape || totalWidth > 85) {
        sheet.getPrintSetup().setLandscape(true);
    }

    // On dfinit le footer
    final HSSFFooter footer = sheet.getFooter();
    footer.setRight("Page " + HeaderFooter.page() + " / " + HeaderFooter.numPages());
}

From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java

@Override
public void generateXSL(String user, String startDate, String endDate) {
    try {/*from   w ww .j  a  v a2 s .co m*/

        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(wb);
        HSSFSheet sheet = wb.createSheet("Users_Sheet1");

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

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

        String[] titles = { "Mobile", "Source Address", "Message", "Time Spent", "Last Update", "User",
                "Status", "No. of SMS" };

        HSSFRow row = sheet.createRow(1);
        row.setHeightInPoints(40);

        Cell headerCell;
        for (int i = 0; i < titles.length; i++) {
            headerCell = row.createCell(i);
            headerCell.setCellValue(titles[i]);
            headerCell.setCellStyle(styles.get("header"));
        }

        List<SMSOut> exportSMSOutReport = (List) userSMSOutReport(user, startDate, endDate).get("result");
        int rowNum = 2;

        for (SMSOut anSMS : exportSMSOutReport) {
            row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(anSMS.getDestinationAddr());
            row.createCell(1).setCellValue(anSMS.getSourceAddr());
            row.createCell(2).setCellValue(anSMS.getMessagePayload());
            row.createCell(3).setCellValue(anSMS.getTimeSubmitted());
            row.createCell(4).setCellValue(anSMS.getTimeProcessed());

            row.createCell(5).setCellValue(anSMS.getUser());
            row.createCell(6).setCellValue(anSMS.getRealStatus());
            row.createCell(7).setCellValue(anSMS.getSmsCount());
            rowNum++;
        }

        sheet.setColumnWidth(0, 20 * 256); //30 characters wide
        sheet.setColumnWidth(1, 15 * 256);
        for (int i = 2; i < 5; i++) {
            sheet.setColumnWidth(i, 20 * 256); //6 characters wide
        }
        sheet.setColumnWidth(5, 10 * 256);

        sheet.setColumnWidth(6, 20 * 256);
        sheet.setColumnWidth(7, 10 * 256); //10 characters wide

        FacesContext context = FacesContext.getCurrentInstance();
        HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse();
        res.setContentType("application/vnd.ms-excel");
        res.setHeader("Content-disposition", "attachment;filename=mydata.xlsx");

        ServletOutputStream out = res.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
        FacesContext.getCurrentInstance().responseComplete();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:model.Reports.java

public void generateXSL(List<SmsOutUserBean> smsOutUserBeans, int count) {
    try {//from  w  w w.  j av a  2s  . com

        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(wb);
        HSSFSheet sheet = wb.createSheet("Users_Sheet1");

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

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

        String[] titles = { "Mobile", "Source Address", "Message", "Time Sent", "Last Update", "User", "Status",
                "Number of SMS", };

        HSSFRow row = sheet.createRow(1);
        row.setHeightInPoints(40);

        Cell headerCell;
        for (int i = 0; i < titles.length; i++) {
            headerCell = row.createCell(i);
            headerCell.setCellValue(titles[i]);
            headerCell.setCellStyle(styles.get("header"));
        }

        int rowNum = 2;
        for (SmsOutUserBean bean : smsOutUserBeans) {
            row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(bean.getSmsOutModel().getDestinationAddress());
            row.createCell(1).setCellValue(bean.getSmsOutModel().getSourceAddress());
            row.createCell(2).setCellValue(bean.getSmsOutModel().getMessagePayload());

            row.createCell(3)
                    .setCellValue(HelperUtil.conDateToString(bean.getSmsOutModel().getTimeSubmitted()));
            row.createCell(4)
                    .setCellValue(HelperUtil.conDateToString(bean.getSmsOutModel().getTimeProcessed()));

            row.createCell(5).setCellValue(bean.getUserBean().getUsername());
            row.createCell(6).setCellValue(bean.getSmsOutModel().getRealStatus());
            row.createCell(7).setCellValue(bean.getSmsOutModel().getSmsCount());

            rowNum++;
        }

        sheet.setColumnWidth(0, 20 * 256); //30 characters wide
        sheet.setColumnWidth(1, 15 * 256);
        for (int i = 2; i < 5; i++) {
            sheet.setColumnWidth(i, 20 * 256); //6 characters wide
        }
        sheet.setColumnWidth(5, 10 * 256);

        sheet.setColumnWidth(6, 20 * 256);
        sheet.setColumnWidth(7, 10 * 256); //10 characters wide

        FacesContext context = FacesContext.getCurrentInstance();
        HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse();
        res.setContentType("application/vnd.ms-excel");
        res.setHeader("Content-disposition", "attachment;filename=mydata.xls");

        ServletOutputStream out = res.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
        FacesContext.getCurrentInstance().responseComplete();
    } catch (Exception e) {
        e.printStackTrace();
    }
}