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

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

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

Introduction

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

Prototype

@Override
public void autoSizeColumn(int column)

Source Link

Document

Adjusts the column width to fit the contents.

This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.

Usage

From source file:excel.FileExcel.java

public File excel_create_order_pusat_coming(ArrayList<ComingOrderReportData> comingorder,
        Date comingorderdate) {/*w  ww  .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  ww. j a v a  2s .com

        //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);//from   w  w  w. ja  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 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 w  ww  .j  av  a2 s  . c  om
        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);// w  w  w . j a  v  a  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);/* w  w  w  .  j  ava2s . 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:excel.FileExcel.java

public File create_excel_test() {
    File Testing = new File("ujicoba.xls");
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();

    // Create header form
    // tanggal//from  ww w  . ja  v a2  s.co  m

    HSSFRow header = sheet.createRow(2);
    HSSFCell cell = header.createCell(3);
    cell.setCellValue("Tanggal Jadi Kerjaan : ");
    sheet.autoSizeColumn(3);
    cell = header.createCell(4);
    cell.setCellValue(" 10 Februari 2016 ");
    sheet.autoSizeColumn(4);

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

From source file:exportToExcel.ResultSetToExcel.java

License:Open Source License

private void dumpData(ResultSet resultSet, HSSFSheet sheet, int columnCount, HSSFCellStyle[] dataStyles)
        throws SQLException {
    int currentRow = 1;
    // The result set can have been already browsed
    resultSet.beforeFirst();//from   w w w  .  j a va2s . c  om
    while (resultSet.next()) {
        HSSFRow row = sheet.createRow(currentRow++);
        for (int colIndex = 0; colIndex < columnCount; colIndex++) {
            Object value = resultSet.getObject(colIndex + 1);
            final HSSFCell cell = row.createCell(colIndex);
            if (value == null) {
                cell.setCellValue("");
            } else {
                cell.setCellStyle(dataStyles[colIndex]);
                if (value instanceof Calendar) {
                    cell.setCellValue((Calendar) value);
                } else if (value instanceof Date) {
                    cell.setCellValue((Date) value);
                } else if (value instanceof String) {
                    cell.setCellValue((String) value);
                } else if (value instanceof Boolean) {
                    cell.setCellValue((Boolean) value);
                } else if (value instanceof Double) {
                    cell.setCellValue((Double) value);
                } else if (value instanceof BigDecimal) {
                    cell.setCellValue(((BigDecimal) value).doubleValue());
                }
            }
        }
    }
    for (int i = 0; i < columnCount; i++) {
        sheet.autoSizeColumn(i);
    }
}

From source file:gov.guilin.ExcelView.java

License:Open Source License

/**
 * ?Excel//from w  ww.  ja va  2 s  .c  om
 * 
 * @param model
 *            ?
 * @param workbook
 *            workbook
 * @param request
 *            request
 * @param response
 *            response
 */
public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    Assert.notEmpty(properties);
    HSSFSheet sheet;
    if (StringUtils.isNotEmpty(sheetName)) {
        sheet = workbook.createSheet(sheetName);
    } else {
        sheet = workbook.createSheet();
    }
    int rowNumber = 0;
    if (titles != null && titles.length > 0) {
        HSSFRow header = sheet.createRow(rowNumber);
        header.setHeight((short) 400);
        for (int i = 0; i < properties.length; i++) {
            HSSFCell cell = header.createCell(i);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 11);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            if (i == 0) {
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                HSSFComment comment = patriarch
                        .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4));
                comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B"
                        + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+"));
                cell.setCellComment(comment);
            }
            if (titles.length > i && titles[i] != null) {
                cell.setCellValue(titles[i]);
            } else {
                cell.setCellValue(properties[i]);
            }
            if (widths != null && widths.length > i && widths[i] != null) {
                sheet.setColumnWidth(i, widths[i]);
            } else {
                sheet.autoSizeColumn(i);
            }
        }
        rowNumber++;
    }
    if (data != null) {
        for (Object item : data) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell cell = row.createCell(i);
                if (converters != null && converters.length > i && converters[i] != null) {
                    Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]);
                    ConvertUtils.register(converters[i], clazz);
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                    ConvertUtils.deregister(clazz);
                    if (clazz.equals(Date.class)) {
                        DateConverter dateConverter = new DateConverter();
                        dateConverter.setPattern(DEFAULT_DATE_PATTERN);
                        ConvertUtils.register(dateConverter, Date.class);
                    }
                } else {
                    cell.setCellValue(BeanUtils.getProperty(item, properties[i]));
                }
                if (rowNumber == 0 || rowNumber == 1) {
                    if (widths != null && widths.length > i && widths[i] != null) {
                        sheet.setColumnWidth(i, widths[i]);
                    } else {
                        sheet.autoSizeColumn(i);
                    }
                }
            }
            rowNumber++;
        }
    }
    if (contents != null && contents.length > 0) {
        rowNumber++;
        for (String content : contents) {
            HSSFRow row = sheet.createRow(rowNumber);
            HSSFCell cell = row.createCell(0);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.GREY_50_PERCENT.index);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(content);
            rowNumber++;
        }
    }
    response.setContentType("application/force-download");
    if (StringUtils.isNotEmpty(filename)) {
        response.setHeader("Content-disposition",
                "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
    } else {
        response.setHeader("Content-disposition", "attachment");
    }
}

From source file:gov.nih.nci.evs.app.neopl.CSVtoExcel.java

License:Open Source License

public void runHSSF(String inputfile) {
    int size = checkSpecialCharacters(inputfile);

    int n = inputfile.lastIndexOf(".");
    String outputfile = getOutputFile(inputfile, "xls");

    try {/*w ww.j  a  v  a  2  s  . c  o  m*/
        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFCellStyle cellStyle = wb.createCellStyle();
        //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //font.setColor(HSSFColor.WHITE.index);
        cellStyle.setFont(font);

        HSSFCellStyle linkCellStyle = wb.createCellStyle();
        //cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        HSSFFont linkfont = wb.createFont();
        linkfont.setColor(HSSFColor.LIGHT_BLUE.index);
        linkCellStyle.setFont(linkfont);

        //CreationHelper helper = wb.getCreationHelper();
        CSVReader reader = new CSVReader(new FileReader(inputfile));//CSV file
        String[] line;
        int r = 0;
        Cell cell = null;

        //"C7419","Acanthoma","C0846967","Acanthoma","MDR","Acanthoma","10059394","LT"
        // skip heading
        HSSFHyperlink url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
        HSSFSheet sheet = null;
        int page_num = 1;
        Row row = null;
        line = reader.readNext();
        int lcv = 0;
        int row_count = 0;
        Vector w = new Vector();
        while ((line = reader.readNext()) != null) {
            if (lcv % PAGE_SIZE == 0) {
                r = 0;
                String sheetLabel = SHEET_LABEL;
                if (size > PAGE_SIZE) {
                    sheetLabel = sheetLabel + " (Page " + page_num + ")";
                }
                sheet = wb.createSheet(sheetLabel);
                for (int j = 0; j < HEADINGS.length; j++) {
                    sheet.autoSizeColumn(j);
                }
                row = sheet.createRow((short) r);
                for (int i = 0; i < HEADINGS.length; i++) {
                    String heading = HEADINGS[i];
                    cell = row.createCell(i);
                    cell.setCellValue(heading);
                    cell.setCellStyle(cellStyle);
                }
                sheet.createFreezePane(0, 1); // this will freeze the header row
                page_num++;
            }

            String s4 = (String) line[4];
            if (s4.compareTo("NCI") == 0) {
                w.add("NCI Line number: " + r);
            } else if (s4.compareTo("NCI") != 0) {
                r++;
                row = sheet.createRow((short) r);
                row_count++;

                row = sheet.createRow((short) r);
                row_count++;

                for (int i = 0; i < HEADINGS.length; i++) {
                    cell = row.createCell(i);
                    int codeCol = codeColumn[i];
                    cell.setCellValue(line[i]);
                    if (NCIT_LINK && codeCol == LINK_NCIT) {
                        url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                        String code = line[i];
                        url_link.setAddress(getNCItHyperlink(code));
                        cell.setHyperlink(url_link);
                        cell.setCellStyle(linkCellStyle);
                    } else if (NCIM_LINK && codeCol == LINK_NCIM) {
                        url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                        String code = line[i];
                        url_link.setAddress(getNCImHyperlink(code));
                        cell.setHyperlink(url_link);
                        cell.setCellStyle(linkCellStyle);
                    } else if (SOURCE_LINK && codeCol == LINK_OTHER) {
                        url_link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                        String code = line[i];
                        String source = line[SOURCE_INDEX];
                        if (localNameMap.containsKey(source)) {
                            url_link.setAddress(getSourceHyperlink(source, code));
                            cell.setHyperlink(url_link);
                            cell.setCellStyle(linkCellStyle);
                        }
                    }
                }
            }
            lcv++;
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(outputfile);
        wb.write(fileOut);
        fileOut.close();
        System.out.println("Output file " + outputfile + " generated.");
        System.out.println("row_count: " + row_count);
        System.out.println("NCI: " + w.size());
        for (int i = 0; i < w.size(); i++) {
            String t = (String) w.elementAt(i);
            System.out.println(t);
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}