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

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

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

Introduction

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

Prototype

private void write(POIFSFileSystem fs) throws IOException

Source Link

Document

Writes the workbook out to a brand new, empty POIFS

Usage

From source file:eu.itesla_project.entsoe.util.BoundaryPointXlsParserTest.java

License:Mozilla Public License

@Test
public void test() throws IOException {
    HSSFWorkbook workbook = createWorkbook();

    byte[] buffer;
    try (ByteArrayOutputStream stream = new ByteArrayOutputStream(1024)) {
        workbook.write(stream);
        stream.flush();//from www  . ja v  a 2  s. c om
        buffer = stream.toByteArray();
    }

    Map<String, BoundaryPoint> boundaryPoints;
    try (InputStream stream = new ByteArrayInputStream(buffer)) {
        BoundaryPointXlsParser parser = new BoundaryPointXlsParser();
        boundaryPoints = parser.parse(stream);
    }

    assertEquals(1, boundaryPoints.size());
    BoundaryPoint point = boundaryPoints.get("BoundaryPoint FR-BE");
    assertNotNull(point);
    assertEquals("BoundaryPoint FR-BE", point.getName());
    assertEquals(Country.FR, point.getBorderFrom());
    assertEquals(Country.BE, point.getBorderTo());
}

From source file:eu.scape_project.pc.tika.cli.MergeReports.java

License:Apache License

private static boolean writeXLS(String outputFilePath) throws Exception {

    int rowCounter = 1; //Start in row 1 (which is the 2nd row). Row 0 holds the description.
    FileOutputStream fileOut = new FileOutputStream(outputFilePath);
    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFSheet worksheet = workbook.createSheet("Type Report");

    HSSFCellStyle myCellStyle = workbook.createCellStyle();
    myCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    HSSFCellStyle myCellStylePercent = workbook.createCellStyle();
    HSSFDataFormat df = workbook.createDataFormat();
    myCellStylePercent.setDataFormat(df.getFormat("0.00%"));
    myCellStylePercent.setAlignment(CellStyle.ALIGN_CENTER);

    HSSFRow myHeaderRow = worksheet.createRow(0);
    HSSFCell myHeaderCell0 = myHeaderRow.createCell(0);
    HSSFCell myHeaderCell1 = myHeaderRow.createCell(1);
    HSSFCell myHeaderCell2 = myHeaderRow.createCell(2);

    myHeaderCell0.setCellValue("TYPE");
    myHeaderCell1.setCellValue("COUNT");
    myHeaderCell2.setCellValue("PERCENTAGE");
    myHeaderCell0.setCellStyle(myCellStyle);
    myHeaderCell1.setCellStyle(myCellStyle);
    myHeaderCell2.setCellStyle(myCellStyle);

    Iterator it = myCollection.keySet().iterator();
    while (it.hasNext()) {
        String typeKey = it.next().toString();
        float typeValue = myCollection.get(typeKey);
        float myPerc = typeValue / countAllGoodItems;
        //System.out.println("    ***: " + typeKey + "#" + (int) typeValue + "#" + myPerc);

        HSSFRow myRow = worksheet.createRow(rowCounter);
        HSSFCell myCell0 = myRow.createCell(0);
        HSSFCell myCell1 = myRow.createCell(1);
        HSSFCell myCell2 = myRow.createCell(2);

        myCell0.setCellValue(typeKey);//  w w w. jav a2  s  .  co  m
        myCell1.setCellValue(typeValue);
        myCell2.setCellValue(myPerc);

        myCell1.setCellStyle(myCellStyle);
        myCell2.setCellStyle(myCellStyle);

        myCell2.setCellStyle(myCellStylePercent);

        rowCounter++;

    }

    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();

    return true;
}

From source file:eu.squadd.timesheets.eolas.TimeTemplate.java

public String prepareTimesheet(String[] args) {
    String response = null;/* w ww  .ja  v a  2 s.  c  om*/
    try {
        String[] ym = args[0].split("/");
        month = Integer.parseInt(ym[0]);
        year = Integer.parseInt(ym[1]);

        Calendar cal = Calendar.getInstance(TimeZone.getDefault());
        cal.set(Calendar.YEAR, year);
        cal.set(Calendar.MONTH, month - 1);
        int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        monthName = cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.ENGLISH);
        String periodName = monthName + "-" + year;
        cal.set(Calendar.DATE, 1);
        String dayOfWeek = new SimpleDateFormat("EE").format(cal.getTime());

        System.out.println("Month: " + periodName);
        System.out.println("Days in month: " + days);
        System.out.println("Month starts in: " + dayOfWeek);

        Map<String, String> bankHolidays = year == 2016 ? publicHolidays2016 : publicHolidays2017;
        Map<String, String> holidays = this.extractHolidays(args);

        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(template));
        HSSFSheet sheet = wb.getSheet("timesheet"); //getSheetAt(0);
        HSSFRow currentRow;
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        sheet.getRow(4).getCell(1).setCellValue(periodName);
        int row = 7;
        int startRow = 0;
        int i = 1;
        while (i <= days) {
            currentRow = sheet.getRow(row);
            if (currentRow.getRowNum() > 47)
                break;
            String day = currentRow.getCell(0).getStringCellValue();

            if (day.startsWith("Total")) {
                evaluator.evaluateFormulaCell(currentRow.getCell(2));
                evaluator.evaluateFormulaCell(currentRow.getCell(4));
                row++;
                continue;
            }

            if (startRow == 0) {
                if (dayOfWeek.equals(day.substring(0, 3))) {
                    startRow = currentRow.getRowNum();
                    System.out.println("Starting row found: " + startRow + 1);
                } else {
                    row++;
                    continue;
                }
            }
            cal.set(Calendar.DATE, i);
            String date = sdf.format(cal.getTime());
            if (!day.equals("Saturday") && !day.equals("Sunday") && bankHolidays.get(date) == null
                    && holidays.get(date) == null) {
                currentRow.getCell(1).setCellValue(date);
                currentRow.getCell(2).setCellValue(defaultHours); // regular hours
                //currentRow.getCell(3).setCellValue(defaultHours);   // overtime hours
                currentRow.getCell(4).setCellValue(defaultHours); // total hours                    
            }
            i++;
            row++;
        }
        currentRow = sheet.getRow(46);
        evaluator.evaluateFormulaCell(currentRow.getCell(2));
        evaluator.evaluateFormulaCell(currentRow.getCell(4));
        currentRow = sheet.getRow(47);
        evaluator.evaluateFormulaCell(currentRow.getCell(2));
        evaluator.evaluateFormulaCell(currentRow.getCell(4));
        response = outFilePath.replace("#MONTH#", periodName);
        wb.write(new FileOutputStream(response));

    } catch (IOException ex) {
        Logger.getLogger(Timesheets.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Timesheet created.");
    return response;
}

From source file:excel.FileExcel.java

public File excel_create_katalog_update(ArrayList<UpdKatalog> newKatalogUpdate) {
    if (newKatalogUpdate.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "KatalogDiff_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + "_"
                + time.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
        File ExcelKatalogDiff = 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  .  ja  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 PERUBAHAN HARGA");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));

        // create file info
        header = sheet.createRow(3);
        cell = header.createCell(2);
        cell.setCellValue("Tanggal Update : ");
        cell = header.createCell(3);
        cell.setCellValue(fmt.format(newKatalogUpdate.get(0).update_date_new));

        // create the header
        datastyle.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("Harga TPG Lama");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Tanggal Update Lama");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Harga TPG Baru");
        cell = header.createCell(5);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Tanggal Update Baru");
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);

        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 < newKatalogUpdate.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).kode_barang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).harga_tpg_old);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(fmt.format(newKatalogUpdate.get(i).update_date_old));
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(newKatalogUpdate.get(i).harga_tpg_new);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(fmt.format(newKatalogUpdate.get(i).update_date_new));
        }
        try {
            // String pathname = "D:\\Document\\Dropbox\\sophie\\DB\\update_" + fmt.format(newKatalogUpdate.get(0).update_date_new) + ".xls";
            FileOutputStream out = new FileOutputStream(ExcelKatalogDiff);
            workbook.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            ExcelKatalogDiff = null;
        } catch (IOException e) {
            e.printStackTrace();
            ExcelKatalogDiff = null;
        }
        return ExcelKatalogDiff;
    } else
        return null;
}

From source file:excel.FileExcel.java

public File excel_create_diff_upd_stock(ArrayList<DiffUpdStock> diffinput, Date currtime) {
    if (diffinput.size() != 0) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "DailyDiff_" + fmt.format(currtime) + "_" + time.format(currtime) + ".xls";
        File ExcelDailyDiff = 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  .j  a  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 UPDATE DAILY STOK");
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 4));

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

        // 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("Unedited Kode Barang");
        cell = header.createCell(2);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Unedited Jumlah Barang");
        cell = header.createCell(3);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Edited Kode Barang");
        cell = header.createCell(4);
        cell.setCellStyle(headerstyle);
        cell.setCellValue("Edited Jumlah Barang");
        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 < diffinput.size(); i++) {
            header = sheet.createRow(i + 7);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriKodeBarang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriJumlahBarang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).edtKodeBarang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(diffinput.get(i).oriJumlahBarang);

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

From source file:excel.FileExcel.java

public File excel_create_order_pusat_coming(ArrayList<ComingOrderReportData> comingorder,
        Date comingorderdate) {// www .  j  ava2 s .  c  om
    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 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 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);// ww  w .  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 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;
        HSSFSheet sheet;// w w w  .ja v  a2s .co  m

        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   ww w .j a v a 2  s .c om*/

        //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;
    }
}