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

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

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

Introduction

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

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

Create a new Cell style and add it to the workbook's style table.

Usage

From source file:edu.swjtu.excel.ExportCarData.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? //from   w w w. ja va2s  .  co m
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?

    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(9);
    cell.setCellValue("?");
    cell.setCellStyle(style);

    // ? ??
    Connection con = null;
    try {
        con = db.getCon();
    } catch (ClassNotFoundException | SQLException e1) {
        e1.printStackTrace();
    }

    ArrayList<Car> list = null;
    list = new CarDaoImpl().getAllCar(con);

    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car car = (Car) list.get(i);
        // ?
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(car.getLicensePlate());
        row.createCell(2).setCellValue(car.getBrand());
        row.createCell(3).setCellValue(car.getRegistrationDate());
        row.createCell(4).setCellValue(car.getInsuranceDate());
        row.createCell(5).setCellValue(car.getDrivingLicense());
        row.createCell(6).setCellValue(car.getLicense());
        row.createCell(7).setCellValue(car.getArrangeId());
        row.createCell(8).setCellValue(car.getDriver());
        row.createCell(9).setCellValue(car.getNumber());
    }

    //?
    //      FileOutputStream fout = new FileOutputStream("E:/car_data.xls");  
    //      wb.write(fout);  
    //      fout.close();  

    String myexcel = "All_Car";
    //?
    OutputStream out = response.getOutputStream();
    //??
    response.reset();
    //Excel?
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename=" + myexcel + ".xls");

    wb.write(out);
    out.close();

    //?
    System.setOut(new PrintStream(out));
    //?
    out.flush();
    //?
    if (out != null) {
        out.close();
    }

    try {
        db.closeCon(con);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:edu.swjtu.excel.ExportConCarData.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    // webbookExcel
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet
    HSSFSheet sheet = wb.createSheet("?");
    // sheet0,??poiExcel?short
    HSSFRow row = sheet.createRow((int) 0);
    // ? /*from   w w w.j  a v  a 2  s . c  om*/
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?

    HSSFCell cell = row.createCell(0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell(9);
    cell.setCellValue("?");
    cell.setCellStyle(style);

    // ? ??
    Connection con = null;
    try {
        con = db.getCon();
    } catch (ClassNotFoundException | SQLException e1) {
        e1.printStackTrace();
    }

    String condition = request.getParameter("condition");
    String sea_condition = request.getParameter("sea_condition");
    String search_cho = null;

    ArrayList<Car> list = null;
    CarDaoImpl carr = new CarDaoImpl();

    if (condition.equals("0")) {
        list = carr.getAllCar(con);
        search_cho = "All";
    } else if (condition.equals("1")) {
        list = carr.getCarByLicensePlate_V(sea_condition, con);
        search_cho = "LicensePlate";
    } else if (condition.equals("2")) {
        list = carr.getCarByBrand_V(sea_condition, con);
        search_cho = "Brand";
    } else if (condition.equals("3")) {
        list = carr.getCarByDriver_V(sea_condition, con);
        search_cho = "Driver";
    } else if (condition.equals("4")) {
        list = carr.getCarByArrangeId_V(sea_condition, con);
        search_cho = "ArrangeId";
    } else if (condition.equals("5")) {
        list = carr.getCarByNumber_V(sea_condition, con);
        search_cho = "Number";
    } else if (condition.equals("6")) {
        list = carr.getCarByDrivingLicense_V(sea_condition, con);
        search_cho = "DrivingLicenseber";
    } else if (condition.equals("7")) {
        list = carr.getCarByLicense_V(sea_condition, con);
        search_cho = "License";
    } else {
    }

    for (int i = 0; i < list.size(); i++) {
        row = sheet.createRow((int) i + 1);
        Car car = (Car) list.get(i);
        // ?
        row.createCell(0).setCellValue(i + 1);
        row.createCell(1).setCellValue(car.getLicensePlate());
        row.createCell(2).setCellValue(car.getBrand());
        row.createCell(3).setCellValue(car.getRegistrationDate());
        row.createCell(4).setCellValue(car.getInsuranceDate());
        row.createCell(5).setCellValue(car.getDrivingLicense());
        row.createCell(6).setCellValue(car.getLicense());
        row.createCell(7).setCellValue(car.getArrangeId());
        row.createCell(8).setCellValue(car.getDriver());
        row.createCell(9).setCellValue(car.getNumber());
    }

    String myexcel = search_cho + "_Car";

    //?
    OutputStream out = response.getOutputStream();
    //??
    response.reset();
    //Excel?
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment;filename=" + myexcel + ".xls");

    wb.write(out);
    out.close();

    //?
    System.setOut(new PrintStream(out));
    //?
    out.flush();
    //?
    if (out != null) {
        out.close();
    }

    try {
        db.closeCon(con);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

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

License:Apache License

/**
 * [Flow #-5]    :  ?? ?  ? ?  //w  ww.  j a v a 2 s.  c om
 */
@Test
public void testGetCellContents() throws Exception {

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

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testGetCellContents.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("testGetCellContents after loadWorkbook....");

        HSSFSheet sheet = wb.createSheet("cell test sheet");

        HSSFCellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            HSSFRow row = sheet.createRow(i);
            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 sheet");

        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("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString());
            }
        }

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

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

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? /*from  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./* www  . j a v  a  2s .c om*/
 *
 * @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 gestiona el estilo que se proporcionar a las cabeceras.
 *
 * @param wb Objeto Excel.//from   w w w  .  j  a v a2 s. c o m
 */
private void initStylesHeader(HSSFWorkbook wb) {

    // create instance of HSSFCellStyle
    HSSFCellStyle headerStyle = wb.createCellStyle();

    // Create Font Header
    createFontCell(wb, headerStyle, ep.getFillFontColorHeader());
    headerStyle.getFont(wb).setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Create style of header cell
    HSSFPalette palette = wb.getCustomPalette();
    HSSFColor colorMapfreHSSF = palette.findColor((byte) ep.getColorMapfre().getRed(),
            (byte) ep.getColorMapfre().getGreen(), (byte) ep.getColorMapfre().getBlue());

    if (colorMapfreHSSF == null) {
        palette.setColorAtIndex(HSSFColor.LAVENDER.index, (byte) ep.getColorMapfre().getRed(),
                (byte) ep.getColorMapfre().getGreen(), (byte) ep.getColorMapfre().getBlue());
        colorMapfreHSSF = palette.getColor(HSSFColor.LAVENDER.index);
    }

    short fillBGColor = colorMapfreHSSF.getIndex();

    headerStyle.setFillForegroundColor(fillBGColor);
    headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    // create border of header cell
    createBorderCells(headerStyle);

    // set
    ep.setHeaderStyle(headerStyle);
}

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

License:Apache License

/**
 * Este mtodo gestiona el estilo que se proporcionar a las celdas de la coleccin.
 *
 * @param wb Objeto Excel.//from  ww  w  .  j a  va2 s.  c o  m
 */
private void initStylesBody(HSSFWorkbook wb) {

    // Create Font body
    HSSFCellStyle bodyStyle = wb.createCellStyle();

    createFontCell(wb, bodyStyle, ep.getFillFontColorBody());

    // create border of header body
    createBorderCells(bodyStyle);
    ep.setBodyStyle(bodyStyle);
}

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

License:Apache License

private static boolean writeXLS(String outputFilePath) throws Exception {

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

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

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

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

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

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

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

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

        myCell0.setCellValue(typeKey);/* ww  w .  j a v a  2s  .  co m*/
        myCell1.setCellValue(typeValue);
        myCell2.setCellValue(myPerc);

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

        myCell2.setCellStyle(myCellStylePercent);

        rowCounter++;

    }

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

    return true;
}

From source file: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  v  a2  s  . com

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

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

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL 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);/*  www . j  a  v  a 2s .  c  o m*/

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

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

        // create the title 
        header = sheet.createRow(1);
        cell = header.createCell(1);
        boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titlestyle.setFont(boldfont);
        titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
        titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        cell.setCellStyle(titlestyle);
        cell.setCellValue("TABEL 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;
}