Example usage for org.apache.poi.hssf.usermodel HSSFCellStyle setBorderRight

List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setBorderRight

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFCellStyle
  6. setBorderRight

Introduction

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

Prototype

@Override
public void setBorderRight(BorderStyle border)

Source Link

Document

set the type of border to use for the right border of the cell

Usage

From source file:edu.duke.cabig.c3pr.service.Summary3ReportServiceTest.java

License:BSD License

public void testGenerateEXCEL() throws Exception {
    HealthcareSite hcs = healthcareSiteDao.getById(1100);

    DateFormat format = new SimpleDateFormat("MM/dd/yyyy");
    Date startDate = (Date) format.parse("01/11/1990");
    Date endDate = (Date) format.parse("01/01/2007");
    String grantNumber = "GRANT-NO 1232";

    Summary3Report summary3Report = new Summary3Report(hcs, grantNumber, startDate, endDate);
    String reportingSource = healthcareSiteDao.getById(1000).getName();
    summary3Report.setReportingSource(reportingSource);

    summaryReportService.buildSummary3Report(summary3Report);

    String xmlString = summaryReportService.generateXML(summary3Report);
    File file = new File("testReport.xml");

    FileWriter fileWriter = new FileWriter(file);
    fileWriter.write(xmlString);//  w ww . j  a  v a  2  s  .  co  m
    fileWriter.flush();
    fileWriter.close();
    System.out.println(xmlString);

    // creating the workbook and the spreadsheet

    try {

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        InputStream inputStream = new FileInputStream(file);
        Document document = builder.parse(inputStream);

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report");

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

        spreadSheet.setColumnWidth((short) 0, (short) (60 * 256));
        spreadSheet.setColumnWidth((short) 1, (short) (15 * 256));
        spreadSheet.setColumnWidth((short) 2, (short) (30 * 256));

        HSSFRow titleRow = spreadSheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell((short) 0);
        titleRow.setHeightInPoints(40);

        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER);
        titleCell.setCellStyle(titleCellStyle);

        String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null
                && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null)
                        ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue()
                        : "";
        titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols"
                + "                      " + nullSafeGrantNumber);

        HSSFRow orgRow = spreadSheet.createRow(1);
        orgRow.setHeightInPoints(30);
        HSSFCell organizationCell = orgRow.createCell((short) 0);

        HSSFCellStyle orgCellStyle = wb.createCellStyle();
        orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        orgCellStyle.setFont(font);
        organizationCell.setCellStyle(orgCellStyle);
        organizationCell
                .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0)))
                        .getElementsByTagName("name").item(0).getFirstChild().getNodeValue());

        HSSFRow reportingPeriodRow = spreadSheet.createRow(2);
        HSSFFont reportingPeriodFont = wb.createFont();
        reportingPeriodFont.setFontHeightInPoints((short) 9);
        HSSFCellStyle reportingPeriodStyle = wb.createCellStyle();
        reportingPeriodStyle.setFont(reportingPeriodFont);

        reportingPeriodRow.setHeightInPoints(20);
        HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0);
        reportingPeriodCell.setCellStyle(titleCellStyle);

        reportingPeriodCell.setCellValue("Reporting Period "
                + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - "
                + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue()));

        // creating the first row of table the table header
        HSSFRow row = spreadSheet.createRow(3);
        HSSFCell tableHeaderCell1 = row.createCell((short) 0);
        HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle();
        tableHeaderCellStyle1.setWrapText(true);

        tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        tableHeaderCellStyle1.setFont(font);
        tableHeaderCell1.setCellStyle(tableHeaderCellStyle1);
        tableHeaderCell1.setCellValue("Disease Site");

        // creating table header 2nd & 3rd cells

        HSSFCell tableHeaderCell2 = row.createCell((short) 1);
        HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle();

        tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setWrapText(true);

        tableHeaderCell2.setCellStyle(tableHeaderCellStyle2);
        tableHeaderCell2.setCellValue("Newly Registered Patients");

        HSSFCell tableHeaderCell3 = row.createCell((short) 2);

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

        tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setWrapText(true);
        tableHeaderCell3.setCellStyle(tableHeaderCellStyle3);
        tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols");

        NodeList nodeList = document.getElementsByTagName("reportData");

        HSSFCellStyle tableCellStyle = wb.createCellStyle();
        tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3"));
        for (int i = 4; i < nodeList.getLength() + 4; i++) {

            row = spreadSheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);
            if (i == (4 + nodeList.getLength() - 1)) {
                HSSFCellStyle totalCellStyle = wb.createCellStyle();
                totalCellStyle.setFont(font);
                totalCellStyle.setRightBorderColor((short) 10);
                totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
                tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
                cell.setCellStyle(totalCellStyle);
            } else {
                cell.setCellStyle(tableCellStyle);
            }
            cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0))
                    .getAttribute("name"));

            cell = row.createCell((short) 1);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue("");

            cell = row.createCell((short) 2);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue(
                    ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3)))
                            .getFirstChild().getNodeValue());
        }
        File outputFile = new File(System.getProperty("user.home") + File.separator + "Summary3Report.xls");
        FileOutputStream output = new FileOutputStream(outputFile);
        wb.write(output);
        output.flush();
        output.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParserConfigurationException e) {
        e.printStackTrace();
    } catch (SAXException e) {
        e.printStackTrace();
    }

}

From source file:edu.duke.cabig.c3pr.xml.Summary3ReportGenerator.java

License:BSD License

public void generateExcel(String summary3ReportXml, String file) throws Exception {

    // creating the workbook and the spreadsheet
    try {/*from www  . ja v  a2s . co  m*/

        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        String fileName = "";

        fileName = file;

        InputStream inputStream = new FileInputStream((fileName + ".xml"));
        Document document = builder.parse(inputStream);

        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet spreadSheet = wb.createSheet("Sumary 3 Report");

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

        spreadSheet.setColumnWidth((short) 0, (short) (60 * 256));
        spreadSheet.setColumnWidth((short) 1, (short) (15 * 256));
        spreadSheet.setColumnWidth((short) 2, (short) (30 * 256));

        HSSFRow titleRow = spreadSheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell((short) 0);
        titleRow.setHeightInPoints(40);

        HSSFCellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        titleCellStyle.setVerticalAlignment(titleCellStyle.VERTICAL_CENTER);
        titleCell.setCellStyle(titleCellStyle);

        String nullSafeGrantNumber = (document.getElementsByTagName("grantNumber").item(0) != null
                && document.getElementsByTagName("grantNumber").item(0).getFirstChild() != null)
                        ? (document.getElementsByTagName("grantNumber")).item(0).getFirstChild().getNodeValue()
                        : "";
        titleCell.setCellValue("Summary 3: Reportable Patients/Participation " + "in Therapeutic Protocols"
                + "                      " + nullSafeGrantNumber);

        HSSFRow orgRow = spreadSheet.createRow(1);
        orgRow.setHeightInPoints(30);
        HSSFCell organizationCell = orgRow.createCell((short) 0);

        HSSFCellStyle orgCellStyle = wb.createCellStyle();
        orgCellStyle.setAlignment(titleCellStyle.ALIGN_CENTER_SELECTION);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        orgCellStyle.setFont(font);
        organizationCell.setCellStyle(orgCellStyle);
        organizationCell
                .setCellValue(((Element) (document.getElementsByTagName("reportingOrganization").item(0)))
                        .getElementsByTagName("name").item(0).getFirstChild().getNodeValue());

        HSSFRow reportingPeriodRow = spreadSheet.createRow(2);
        HSSFFont reportingPeriodFont = wb.createFont();
        reportingPeriodFont.setFontHeightInPoints((short) 9);
        HSSFCellStyle reportingPeriodStyle = wb.createCellStyle();
        reportingPeriodStyle.setFont(reportingPeriodFont);

        reportingPeriodRow.setHeightInPoints(20);
        HSSFCell reportingPeriodCell = reportingPeriodRow.createCell((short) 0);
        reportingPeriodCell.setCellStyle(titleCellStyle);

        reportingPeriodCell.setCellValue("Reporting Period "
                + (document.getElementsByTagName("startDate").item(0).getFirstChild().getNodeValue()) + " - "
                + (document.getElementsByTagName("endDate").item(0).getFirstChild().getNodeValue()));

        // creating the first row of table the table header
        HSSFRow row = spreadSheet.createRow(3);
        HSSFCell tableHeaderCell1 = row.createCell((short) 0);
        HSSFCellStyle tableHeaderCellStyle1 = wb.createCellStyle();
        tableHeaderCellStyle1.setWrapText(true);

        tableHeaderCellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle1.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        tableHeaderCellStyle1.setFont(font);
        tableHeaderCell1.setCellStyle(tableHeaderCellStyle1);
        tableHeaderCell1.setCellValue("Disease Site");

        // creating table header 2nd & 3rd cells

        HSSFCell tableHeaderCell2 = row.createCell((short) 1);
        HSSFCellStyle tableHeaderCellStyle2 = wb.createCellStyle();

        tableHeaderCellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle2.setWrapText(true);

        tableHeaderCell2.setCellStyle(tableHeaderCellStyle2);
        tableHeaderCell2.setCellValue("Newly Registered Patients");

        HSSFCell tableHeaderCell3 = row.createCell((short) 2);

        HSSFCellStyle tableHeaderCellStyle3 = wb.createCellStyle();

        tableHeaderCellStyle3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableHeaderCellStyle3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        tableHeaderCellStyle3.setWrapText(true);
        tableHeaderCell3.setCellStyle(tableHeaderCellStyle3);
        tableHeaderCell3.setCellValue("Total patients newly enrolled in therapeutic protocols");

        NodeList nodeList = document.getElementsByTagName("reportData");

        HSSFCellStyle tableCellStyle = wb.createCellStyle();
        tableCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        tableCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$C$1"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2"));
        spreadSheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$C$3"));
        for (int i = 4; i < nodeList.getLength() + 4; i++) {

            row = spreadSheet.createRow(i);
            HSSFCell cell = row.createCell((short) 0);
            if (i == (4 + nodeList.getLength() - 1)) {
                HSSFCellStyle totalCellStyle = wb.createCellStyle();
                totalCellStyle.setFont(font);
                totalCellStyle.setRightBorderColor((short) 10);
                totalCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
                tableCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
                totalCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
                cell.setCellStyle(totalCellStyle);
            } else {
                cell.setCellStyle(tableCellStyle);
            }
            cell.setCellValue(((Element) ((Element) (nodeList.item(i - 4))).getElementsByTagName("key").item(0))
                    .getAttribute("name"));

            cell = row.createCell((short) 1);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue("");

            cell = row.createCell((short) 2);
            cell.setCellStyle(tableCellStyle);

            cell.setCellValue(
                    ((Element) (((Element) (nodeList.item(i - 4))).getElementsByTagName("value").item(3)))
                            .getFirstChild().getNodeValue());
        }
        FileOutputStream output = new FileOutputStream(new File(addExtension(file, "Excel")));
        wb.write(output);
        output.flush();
        output.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParserConfigurationException e) {
        e.printStackTrace();
    } catch (SAXException e) {
        e.printStackTrace();
    }
}

From source file:edu.ku.brc.dbsupport.TableModel2Excel.java

License:Open Source License

protected static void setBordersOnStyle(final HSSFCellStyle style, final short colorIndex,
        final short borderStyle) {
    style.setBorderBottom(borderStyle);/* w  w w .j ava 2s  . co  m*/
    style.setBottomBorderColor(colorIndex);
    style.setBorderLeft(borderStyle);
    style.setLeftBorderColor(colorIndex);
    style.setBorderRight(borderStyle);
    style.setRightBorderColor(colorIndex);
    style.setBorderTop(borderStyle);
    style.setTopBorderColor(colorIndex);
}

From source file:es.jamisoft.comun.io.excel.ExcelGenerator.java

License:Apache License

/**
 * Este mtodo crea el border para una celda.
 *
 * @param cellStyle Estilo de celda para crearl el border.
 *///from   w  w w.jav  a  2s  .  com
private void createBorderCells(HSSFCellStyle cellStyle) {

    // create border
    cellStyle.setBorderBottom(ep.getBorder());
    cellStyle.setBorderLeft(ep.getBorder());
    cellStyle.setBorderRight(ep.getBorder());
    cellStyle.setBorderTop(ep.getBorder());
}

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);/*from  w  w w.  j  av  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 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);/*ww w  .  j a va  2s .  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 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) {/*from www. j  a  v a  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  a2s .  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 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  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 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;/*  w  w  w. ja  v  a2 s  . c o  m*/
        HSSFSheet sheet;

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

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

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

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

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

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

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

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

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

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

        int row_num = 0;

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

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

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

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

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

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