Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

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  ww.j  a  v a 2  s. 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 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;//  www .  j  av a2s.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   www.j  a v  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 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   w  ww .  ja v  a2s .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 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:Export.ExportMapaProducaoExcel__.java

public void CreateCell(Cell c, Row r, Sheet s, CellStyle cs, int colinaI, int colinaF, String valorS,
        int linhaI, int linhaF) {

    c = r.createCell(linhaI);/*from   w w w  . j  a  v a  2  s  . co m*/
    c.setCellStyle(cs);
    c.setCellValue(valorS);
    s.addMergedRegion(new CellRangeAddress(colinaI, colinaF, linhaI, linhaF));
    for (int e = (linhaI + 1); e <= linhaF; e++) {
        c = r.createCell(e);
        c.setCellStyle(cs);
    }
}

From source file:Export.GenericExcel.java

public static void createCellM(Cell c, Row r, Sheet s, CellStyle cs, int colinaI, int colinaF, String valorS,
        int linhaI, int linhaF) {

    c = r.createCell(linhaI);/*  w w w. j a v a 2  s.c om*/
    c.setCellStyle(cs);
    c.setCellValue(valorS);
    s.addMergedRegion(new CellRangeAddress(colinaI, colinaF, linhaI, linhaF));
    for (int e = (linhaI + 1); e <= linhaF; e++) {
        c = r.createCell(e);
        c.setCellStyle(cs);
    }
}

From source file:Export.ListaVeiculo.java

public static void CreateCellM(Cell c, Row r, Sheet s, CellStyle cs, int colinaI, int colinaF, String valorS,
        int linhaI, int linhaF) {
    c = r.createCell(linhaI);//from   w  w  w.  ja v  a 2 s  .c om
    c.setCellStyle(cs);
    c.setCellValue(valorS);
    s.addMergedRegion(new CellRangeAddress(colinaI, colinaF, linhaI, linhaF));
    for (int e = (linhaI + 1); e <= linhaF; e++) {
        c = r.createCell(e);
        c.setCellStyle(cs);
    }
}

From source file:fi.hsl.parkandride.core.service.reporting.Excel.java

License:EUPL

public <T> void addSheet(String name, List<T> rows, List<TableColumn<T>> columns) {
    sheet = wb.createSheet(name);//  ww w. ja  v  a2  s. co  m
    sheet.createFreezePane(0, 1, 0, 1);

    int maxColumns = 0;
    Row headerRow = sheet.createRow(0);
    for (int column = 0; column < columns.size(); ++column, maxColumns = max(maxColumns, column)) {
        Cell cell = headerRow.createCell(column, CELL_TYPE_STRING);
        TableColumn<T> colType = columns.get(column);
        cell.setCellStyle(title);
        cell.setCellValue(colType.name);
    }

    for (int r = 0; r < rows.size(); ++r) {
        Row row = sheet.createRow(r + 1);
        for (int column = 0; column < columns.size(); ++column) {
            TableColumn<T> colType = columns.get(column);
            Object value;
            Optional<CellStyle> style;
            final T v = rows.get(r);
            try {
                value = colType.valueFunction.apply(v);
            } catch (RuntimeException ex) {
                log.error("Failed to generate cell for column " + colType.name, ex);
                value = cleanExceptionMessage(ex);
            }
            style = Optional.ofNullable(colType.styleFn.apply(v));
            if (value == null) {
                row.createCell(column, CELL_TYPE_BLANK);
            } else if (value instanceof Double) {
                Cell cell = row.createCell(column, CELL_TYPE_NUMERIC);
                cell.setCellStyle(style.orElse(decimal));
                cell.setCellValue((Double) value);
            } else if (value instanceof Integer) {
                Cell cell = row.createCell(column, CELL_TYPE_NUMERIC);
                cell.setCellStyle(style.orElse(integer));
                cell.setCellValue((Integer) value);
            } else if (value instanceof MultilingualString) {
                Cell cell = row.createCell(column, CELL_TYPE_STRING);
                cell.setCellStyle(style.orElse(text));
                cell.setCellValue(((MultilingualString) value).fi);
            } else if (value instanceof LocalDate) {
                Cell cell = row.createCell(column, CELL_TYPE_NUMERIC);
                cell.setCellStyle(style.orElse(date));
                cell.setCellValue(((LocalDate) value).toDate());
            } else if (value instanceof DateTime) {
                Cell cell = row.createCell(column, CELL_TYPE_NUMERIC);
                cell.setCellStyle(style.orElse(datetime));
                cell.setCellValue(((DateTime) value).toDate());
            } else if (value instanceof Collection) {
                // currently must be last item in list
                for (Object o : (Collection<?>) value) {
                    Cell cell = row.createCell(column++, CELL_TYPE_STRING);
                    cell.setCellStyle(text);
                    cell.setCellValue(o.toString());
                }
            } else {
                Cell cell = row.createCell(column, CELL_TYPE_STRING);
                String val = value.toString();
                if (style.isPresent()) {
                    cell.setCellStyle(style.get());
                } else if (val.indexOf('\n') > 0) {
                    cell.setCellStyle(multiline);
                } else {
                    cell.setCellStyle(text);
                }
                cell.setCellValue(value.toString());
            }
        }
    }
    if (!rows.isEmpty()) {
        sheet.setAutoFilter(new CellRangeAddress(0, rows.size(), 0, maxColumns));
    }
    autosize(maxColumns);
}

From source file:fi.thl.pivot.export.XlsxExporter.java

private void mergeTopLeftCorner(Sheet sheet, Pivot pivot) {
    sheet.addMergedRegion(
            new CellRangeAddress(0, pivot.getColumns().size() - 1, 0, pivot.getRows().size() - 1));
}

From source file:fi.thl.pivot.export.XlsxExporter.java

private void printCopyrightNotice(Sheet sheet, int initialRowNumber, Map<String, ?> params, int columns) {
    int rowNumber = initialRowNumber + 1;
    Boolean isOpenData = (Boolean) params.get("isOpenData");
    Cell c1 = sheet.createRow(++rowNumber).createCell(0);
    c1.setCellValue(//from  ww w. ja va2  s . c o  m
            String.format("%1$s %2$te.%2$tm.%2$tY", message("cube.updated", "date"), params.get("updated")));
    c1.setCellStyle(defaultStyle);
    sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, columns - 1));

    Cell c2 = sheet.createRow(++rowNumber).createCell(0);
    c2.setCellValue(String.format("(c) %s %d %s", message("site.company", "THL"),
            Calendar.getInstance().get(Calendar.YEAR),
            isOpenData != null && isOpenData ? ", " + message("site.license.dd", "CC BY 4.0") : ""));
    c2.setCellStyle(defaultStyle);
    sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, columns - 1));

}