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

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

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

Introduction

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

Prototype


@Override
public HSSFFont createFont()

Source Link

Document

create a new Font and add it to the workbook's font table

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);/*from w  w w.ja  v  a2 s.  c  o 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 {// w ww .  ja  va 2 s.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

/**
 * Converts a tableModel to an Excel Spreadsheet.
 * @param toFile the file object to write it to.
 * @param title the title of the spreadsheet.
 * @param tableModel the table model/*from w w w  . j  ava2s .c o  m*/
 * @return a file to a spreadsheet
 */
public static File convertToExcel(final File toFile, final String title, final TableModel tableModel) {
    if (toFile == null) {
        UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_EXISTS",
                toFile != null ? toFile.getAbsolutePath() : "");
        return null;
    }

    if (tableModel != null && tableModel.getRowCount() > 0) {
        try {
            // create a new file
            FileOutputStream out;
            try {
                out = new FileOutputStream(toFile);

            } catch (FileNotFoundException ex) {
                UIRegistry.showLocalizedMsg("WARNING", "FILE_NO_WRITE",
                        toFile != null ? toFile.getAbsolutePath() : "");
                return null;
            }

            // create a new workbook
            HSSFWorkbook wb = new HSSFWorkbook();

            // create a new sheet
            HSSFSheet sheet = wb.createSheet();
            // declare a row object reference

            // Header Captions
            HSSFFont headerFont = wb.createFont();
            headerFont.setFontHeightInPoints((short) 12);
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            // create a style for the header cell
            HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            headerStyle.setFont(headerFont);
            setBordersOnStyle(headerStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);

            short numColumns = (short) tableModel.getColumnCount();

            HSSFRow headerRow = sheet.createRow(0);
            for (int i = 0; i < numColumns; i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                headerCell.setCellStyle(headerStyle);

                //add the date to the header cell
                headerCell.setCellValue(tableModel.getColumnName(i));
                sheet.setColumnWidth((short) i, (short) (30 * 256));
            }

            //--------------------------
            // done header
            //--------------------------

            // create 3 cell styles
            HSSFCellStyle oddCellStyle = wb.createCellStyle();
            HSSFCellStyle evenCellStyle = wb.createCellStyle();

            setBordersOnStyle(oddCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);
            setBordersOnStyle(evenCellStyle, HSSFColor.GREY_25_PERCENT.index, HSSFCellStyle.BORDER_THIN);

            // create 2 fonts objects
            HSSFFont cellFont = wb.createFont();
            //set font 1 to 12 point type
            cellFont.setFontHeightInPoints((short) 11);
            oddCellStyle.setFont(cellFont);
            evenCellStyle.setFont(cellFont);

            evenCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            oddCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

            oddCellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
            evenCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);

            oddCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            evenCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            // set the sheet name to HSSF Test
            wb.setSheetName(0, title);

            for (short rownum = 0; rownum < (short) tableModel.getRowCount(); rownum++) {
                // create a row
                HSSFRow row = sheet.createRow(rownum + 1);

                for (short cellnum = (short) 0; cellnum < numColumns; cellnum++) {
                    // create a numeric cell
                    HSSFCell cell = row.createCell(cellnum);

                    Object dataVal = tableModel.getValueAt(rownum, cellnum);
                    cell.setCellValue(dataVal != null ? dataVal.toString() : "");

                    // on every other row
                    cell.setCellStyle((rownum % 2) == 0 ? evenCellStyle : oddCellStyle);
                }
            }

            // write the workbook to the output stream
            // close our file (don't blow out our file handles
            wb.write(out);
            out.close();

        } catch (Exception ex) {
            edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount();
            edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(TableModel2Excel.class, ex);
            log.error("convertToExcel", ex); //$NON-NLS-1$
        }
    }
    return toFile;
}

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? /* w  w  w. j  a  v  a2s .c  om*/
 */
@Test
public void testModifyCellAttribute() throws Exception {

    try {
        log.debug("testModifyCellAttribute start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            log.debug("Delete file...." + sb.toString());
        }

        HSSFWorkbook wbTmp = new HSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        HSSFWorkbook wb = excelService.loadWorkbook(sb.toString());
        log.debug("testModifyCellAttribute after loadWorkbook....");

        HSSFSheet sheet = wb.createSheet("cell test sheet2");
        //           sheet.setColumnWidth((short) 3, (short) 200);   // column Width

        HSSFCellStyle cs = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontHeight((short) 16);
        font.setBoldweight((short) 3);
        font.setFontName("fixedsys");

        cs.setFont(font);
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // cell 
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            HSSFRow row = sheet.createRow(i);
            //              row.setHeight((short)300); // row? height 

            for (int j = 0; j < 5; j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString());
        HSSFSheet sheetT = wbT.getSheet("cell test sheet2");
        log.debug("getNumCellStyles : " + wbT.getNumCellStyles());

        HSSFCellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1));

        HSSFFont fontT = cs1.getFont(wbT);
        log.debug("font getFontHeight : " + fontT.getFontHeight());
        log.debug("font getBoldweight : " + fontT.getBoldweight());
        log.debug("font getFontName : " + fontT.getFontName());
        log.debug("getAlignment : " + cs1.getAlignment());
        log.debug("getWrapText : " + cs1.getWrapText());

        for (int i = 0; i < 100; i++) {
            HSSFRow row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                HSSFCell cell1 = row1.getCell(j);
                log.debug("row " + i + ", cell " + j + " : " + cell1.getRichStringCellValue());
                assertEquals(16, fontT.getFontHeight());
                assertEquals(3, fontT.getBoldweight());
                assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());
                assertTrue(cs1.getWrapText());
            }
        }

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testModifyCellAttribute end....");
    }
}

From source file:eionet.cr.util.export.XlsExporter.java

License:Mozilla Public License

/**
 * exports custom search to XLS format.//from   ww  w . j a  va 2  s.co  m
 *
 * @param customSearch
 * @return
 * @throws IOException
 * @throws DAOException
 */
protected InputStream doExport() throws IOException, DAOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    sheet = workbook.createSheet("exported data");

    // some pretty print with headers
    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);

    // output headers
    HSSFRow headers = sheet.createRow(0);
    // store width of each column +1 for Uri or Label column
    columnWidth = new int[getSelectedColumns().size() + 1];
    // output Uri or Label column
    String uriOrLabelColumn = getUriOrLabel();

    columnWidth[0] = uriOrLabelColumn.length();
    HSSFCell uriOrLabelCell = headers.createCell(0);
    XlsUtil.setCellValue(uriOrLabelCell, uriOrLabelColumn);
    uriOrLabelCell.setCellStyle(headerStyle);

    // output rest of the headers
    int columnNumber = 1;
    for (Pair<String, String> columnPair : getSelectedColumns()) {
        // label is already added to the list of elements
        if (Predicates.RDFS_LABEL.equals(columnPair.getLeft()))
            continue;

        String column = columnPair.getRight() != null ? columnPair.getRight() : columnPair.getLeft();
        columnWidth[columnNumber] = column.length();
        HSSFCell cell = headers.createCell(columnNumber++);
        XlsUtil.setCellValue(cell, column);
        cell.setCellStyle(headerStyle);
    }
    sheet.createFreezePane(0, 1);

    // output serarch results
    SubjectExportReader reader = new SubjectExportReader(this);
    doExportQueryAndWriteDataIntoOutput(reader);

    // set column width
    for (int i = 0; i < getSelectedColumns().size() + 1; i++) {
        sheet.setColumnWidth(i, Math.min(256 * columnWidth[i], 256 * 255));
    }

    ByteArrayOutputStream output = new ByteArrayOutputStream();
    workbook.write(output);
    return new ByteArrayInputStream(output.toByteArray());
}

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

License:Apache License

/**
 * Este mtodo asigna el formato a los caracteres.
 *
 * @param wb Objeto Excel./*from  w w  w.j a v a  2s.  c  om*/
 * @param cellStyle Estilo de celda para crearl formato a los caracteres.
 * @param fillFontColor Color que se asignara a los cartares.
 */
private void createFontCell(HSSFWorkbook wb, HSSFCellStyle cellStyle, short fillFontColor) {

    // create instance of HSSFCellStyle
    HSSFFont fontHead = wb.createFont();

    // Create Font Header
    fontHead.setFontHeightInPoints(ep.getSizeFont());

    if (ep.getFontName() != null) {
        fontHead.setFontName(ep.getFontName());
    }

    fontHead.setColor(fillFontColor);

    // Fonts are set into a style so create a new one to use.
    cellStyle.setFont(fontHead);
}

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 www  .j  a va2 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);//w  ww .ja va  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 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  w ww.  j  a  va  2  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);/*from  w w  w.  jav  a2  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 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;
    }
}