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

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

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

Introduction

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

Prototype

@Override
public void setDataFormat(short fmt)

Source Link

Document

set the data format (must be a valid format)

Usage

From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java

License:Apache License

private void initStyles(HSSFWorkbook wb) {
    // cache styles used to write text into cells
    HSSFCellStyle style = wb.createCellStyle();
    HSSFFont font = wb.createFont();/*www  .j a v  a  2 s. c  o m*/
    font.setBold(true);
    style.setFont(font);
    styles.put(STYLE_BOLD, style);

    style = wb.createCellStyle();
    font = wb.createFont();
    font.setItalic(true);
    style.setFont(font);
    styles.put(STYLE_ITALIC, style);

    style = wb.createCellStyle();
    font = wb.createFont();
    font.setItalic(true);
    font.setColor(Font.COLOR_RED);
    style.setFont(font);
    styles.put(STYLE_REVIEW, style);

    style = wb.createCellStyle();
    style.setFillPattern(FillPatternType.FINE_DOTS);
    style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
    style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
    styles.put(STYLE_MISSING, style);

    style = wb.createCellStyle();
    style.setFillPattern(FillPatternType.FINE_DOTS);
    style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
    style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
    style.setFont(font);
    styles.put(STYLE_MISSING_REVIEW, style);

    style = wb.createCellStyle();
    HSSFCreationHelper createHelper = wb.getCreationHelper();
    style.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-dd-mm hh:mm"));
    styles.put(STYLE_DATETIME, style);
}

From source file:demo.poi.BigExample.java

License:Apache License

public static void main(String[] args) throws IOException {
    int rownum;/*from w  ww .j a v  a 2 s .  c om*/

    // create a new file
    FileOutputStream out = new FileOutputStream("target/bigworkbook.xls");
    // create a new workbook
    HSSFWorkbook wb = new HSSFWorkbook();
    // create a new sheet
    HSSFSheet s = wb.createSheet();
    // declare a row object reference
    HSSFRow r = null;
    // declare a cell object reference
    HSSFCell c = null;
    // create 3 cell styles
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    // create 2 fonts objects
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    //set font 1 to 12 point type
    f.setFontHeightInPoints((short) 12);
    //make it red
    f.setColor(HSSFColor.RED.index);
    // make it bold
    //arial is the default font
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    //set font 2 to 10 point type
    f2.setFontHeightInPoints((short) 10);
    //make it the color at palette index 0xf (white)
    f2.setColor(HSSFColor.WHITE.index);
    //make it bold
    f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

    //set cell stlye
    cs.setFont(f);
    //set the cell format see HSSFDataFromat for a full list
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));

    //set a thin border
    cs2.setBorderBottom(CellStyle.BORDER_THIN);
    //fill w fg fill color
    cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // set foreground fill to red
    cs2.setFillForegroundColor(HSSFColor.RED.index);

    // set the font
    cs2.setFont(f2);

    // set the sheet name to HSSF Test
    wb.setSheetName(0, "HSSF Test");
    // create a sheet with 300 rows (0-299)
    for (rownum = 0; rownum < 300; rownum++) {
        // create a row
        r = s.createRow(rownum);
        // on every other row
        if ((rownum % 2) == 0) {
            // make the row height bigger  (in twips - 1/20 of a point)
            r.setHeight((short) 0x249);
        }

        //r.setRowNum(( short ) rownum);
        // create 50 cells (0-49) (the += 2 becomes apparent later
        for (int cellnum = 0; cellnum < 50; cellnum += 2) {
            // create a numeric cell
            c = r.createCell(cellnum);
            // do some goofy math to demonstrate decimals
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this cell to the first cell style we defined
                c.setCellStyle(cs);
            }

            // create a string cell (see why += 2 in the
            c = r.createCell(cellnum + 1);

            // set the cell's string value to "TEST"
            c.setCellValue("TEST");
            // make this column a bit wider
            s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20)));

            // on every other row
            if ((rownum % 2) == 0) {
                // set this to the white on red cell style
                // we defined above
                c.setCellStyle(cs2);
            }

        }
    }

    //draw a thick black border on the row at the bottom using BLANKS
    // advance 2 rows
    rownum++;
    rownum++;

    r = s.createRow(rownum);

    // define the third style to be the default
    // except with a thick black border at the bottom
    cs3.setBorderBottom(CellStyle.BORDER_THICK);

    //create 50 cells
    for (int cellnum = 0; cellnum < 50; cellnum++) {
        //create a blank type cell (no value)
        c = r.createCell(cellnum);
        // set it to the thick black border style
        c.setCellStyle(cs3);
    }

    //end draw thick black border

    // demonstrate adding/naming and deleting a sheet
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);
    //end deleted sheet

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

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);/*from ww  w. j a  v a 2  s  .c o 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_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  ww .j av  a  2  s  . c  om

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

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

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

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

        header = sheet.createRow(4);
        cell = header.createCell(2);
        cell.setCellValue("Konter : ");
        cell = header.createCell(3);
        cell.setCellValue(ItemsReadyData.get(0).kode_konter);

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

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

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

        // fill the data
        for (j = 0; j < ItemsReadyData.size(); j++) {
            net = (double) ItemsReadyData.get(j).harga_tpg
                    * (100.0 - (double) ItemsReadyData.get(j).disc_member) / 100.0;
            total_tpg = (double) ItemsReadyData.get(j).harga_tpg * (double) ItemsReadyData.get(j).jumlah_barang;
            total_net = (double) net * (double) ItemsReadyData.get(j).jumlah_barang;
            sum_total_tpg += total_tpg;
            sum_total_net += total_net;

            header = sheet.createRow(8 + j);
            cell = header.createCell(1);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).kode_barang);
            cell = header.createCell(2);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).jumlah_barang);
            cell = header.createCell(3);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).nama_barang);
            cell = header.createCell(4);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).kategori);
            cell = header.createCell(5);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).harga_tpg);
            cell = header.createCell(6);
            cell.setCellStyle(datastyle);
            cell.setCellValue(ItemsReadyData.get(j).disc_member);
            cell = header.createCell(7);
            cell.setCellStyle(datastyle);
            cell.setCellValue(net);
            cell = header.createCell(8);
            cell.setCellStyle(datastyle);
            cell.setCellValue(total_tpg);
            cell = header.createCell(9);
            cell.setCellStyle(datastyle);
            cell.setCellValue(total_net);
        }

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

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

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

From source file:excel.FileExcel.java

public File excel_create_cari_gudang(ArrayList<CariGudangReportData> CariGudang, Date waktuprint) {
    // find number of counter, save in Counter Index
    if (!CariGudang.isEmpty()) {
        DateFormat time = new SimpleDateFormat("hhmm");
        String fileName = "CariGudang_" + fmt.format(waktuprint) + "_" + time.format(waktuprint) + ".xls";
        File FileCariGudang = new File(fileName);

        HSSFWorkbook workbook;//from   w  w w.ja  v  a  2  s  . co  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;
    }
}

From source file:exportToExcel.ResultSetToExcel.java

License:Open Source License

private HSSFCellStyle getDataStyle(HSSFWorkbook workbook, ResultSetMetaData metaData, int colIndex,
        HSSFDataFormat dataFormat) throws SQLException {
    HSSFCellStyle dataStyle = workbook.createCellStyle();
    String columnType = metaData.getColumnTypeName(colIndex + 1);
    columnType += "(" + metaData.getPrecision(colIndex + 1);
    columnType += "," + metaData.getScale(colIndex + 1) + ")";
    String excelFormat = getExcelFormat(columnType);
    final short format = dataFormat.getFormat(excelFormat);
    dataStyle.setDataFormat(format);
    return dataStyle;
}

From source file:gda.hrpd.data.ExcelWorkbook.java

License:Open Source License

/**
 * modify if exist, or create if not exist, a cell in the specified row at specified position with the specified
 * value.//from w w w  . ja  va 2 s  . c o  m
 * 
 * @param row
 * @param column
 * @param date
 * @throws IOException
 */
public void setCellValue(HSSFRow row, int column, Date date) throws IOException {
    HSSFCell cell = row.getCell(column);
    if (cell == null) {
        if (!writeable) {
            logger.error("Cannot create a new sheet in file {}.", this.filename);
            throw new IOException("Cannot write to file {}." + this.filename);
        }

        // we style the cell as a date (and time). It is important to
        // create a new cell style from the workbook otherwise you can end
        // up modifying the built in style and effecting not only this cell
        // but other cells.
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell = row.createCell((short) column);
        cell.setCellValue(date);
        cell.setCellStyle(cellStyle);
    } else {
        cell.setCellValue(date);
    }
}

From source file:gda.hrpd.data.HSSF.java

License:Apache License

/**
 * Constructor HSSF - given a filename this outputs a sample sheet with just a set of rows/cells.
 *
 * @param filename//from ww  w .j ava2 s  . c  o m
 * @param write
 * @exception IOException
 */

public HSSF(String filename, @SuppressWarnings("unused") boolean write) throws IOException {
    short rownum = 0;
    FileOutputStream out = new FileOutputStream(filename);
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r;
    HSSFCell c = null;
    HSSFCellStyle cs = wb.createCellStyle();
    HSSFCellStyle cs2 = wb.createCellStyle();
    HSSFCellStyle cs3 = wb.createCellStyle();
    HSSFFont f = wb.createFont();
    HSSFFont f2 = wb.createFont();

    f.setFontHeightInPoints((short) 12);
    f.setColor((short) 0xA);
    f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    f2.setFontHeightInPoints((short) 10);
    f2.setColor((short) 0xf);
    f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.setFont(f);
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cs2.setFillPattern((short) 1); // fill w fg
    cs2.setFillForegroundColor((short) 0xA);
    cs2.setFont(f2);
    wb.setSheetName(0, "HSSF Test");
    for (rownum = (short) 0; rownum < 300; rownum++) {
        r = s.createRow(rownum);
        if ((rownum % 2) == 0) {
            r.setHeight((short) 0x249);
        }

        // r.setRowNum(( short ) rownum);
        for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) {
            c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
            c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs);
            }
            c = r.createCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING);
            c.setCellValue(new HSSFRichTextString("TEST"));
            s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
            if ((rownum % 2) == 0) {
                c.setCellStyle(cs2);
            }
        } // 50 characters divided by 1/20th of a point
    }

    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    r = s.createRow(rownum);
    cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
        c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK);

        // c.setCellValue(0);
        c.setCellStyle(cs3);
    }
    s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3));
    s.addMergedRegion(new Region((short) 100, (short) 100, (short) 110, (short) 110));

    // end draw thick black border
    // create a sheet, set its title then delete it
    s = wb.createSheet();
    wb.setSheetName(1, "DeletedSheet");
    wb.removeSheetAt(1);

    // end deleted sheet
    wb.write(out);
    out.close();
}

From source file:hr.restart.swing.raExtendedTable.java

License:Apache License

public void exportToXLS(File output) {
    String fname = output.getName();
    if (!fname.endsWith("xls") && fname.indexOf('.') < 0)
        output = new File(output.getParentFile(), fname + ".xls");
    System.out.println("exporting to XLS");
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFDataFormat df = wb.createDataFormat();

    String fontFamily = frmParam.getParam("sisfun", "excelFont", "Arial", "Font za export u Excel", true);
    if (fontFamily == null || fontFamily.length() == 0)
        fontFamily = "Arial";

    int fontSize = 10;
    String fontSizeTx = frmParam.getParam("sisfun", "excelFontSize", "10",
            "Veliina fonta za export u Excel, u tokama", true);
    if (fontSizeTx != null && Aus.getNumber(fontSizeTx) >= 6 && Aus.getNumber(fontSizeTx) <= 72)
        fontSize = Aus.getNumber(fontSizeTx);

    HSSFFont font = wb.createFont();/*from   www .j  a  va  2s  . c  o  m*/
    font.setFontName(fontFamily);
    font.setFontHeightInPoints((short) fontSize);

    HSSFFont fontTitle = wb.createFont();
    fontTitle.setFontName(fontFamily);
    fontTitle.setFontHeightInPoints((short) (fontSize * 1.8));

    HSSFFont fontSubtitle = wb.createFont();
    fontSubtitle.setFontName(fontFamily);
    fontSubtitle.setFontHeightInPoints((short) (fontSize * 1.5));

    HSSFCellStyle csHeader = wb.createCellStyle();
    csHeader.setFont(font);
    csHeader.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    csHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    csHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    csHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    csHeader.setDataFormat(df.getFormat("text"));

    HSSFCellStyle csFooter = wb.createCellStyle();
    csFooter.setFont(font);
    csFooter.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooter.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooter.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    csFooter.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    csFooter.setDataFormat(df.getFormat("text"));

    HSSFCellStyle csFooterNum2 = wb.createCellStyle();
    csFooterNum2.setFont(font);
    csFooterNum2.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooterNum2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooterNum2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    csFooterNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csFooterNum2.setDataFormat(df.getFormat("#,##0.00"));

    HSSFCellStyle csFooterNum = wb.createCellStyle();
    csFooterNum.setFont(font);
    csFooterNum.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooterNum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooterNum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    csFooterNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csFooterNum.setDataFormat(df.getFormat("#"));

    HSSFCellStyle csDate = wb.createCellStyle();
    csDate.setFont(font);
    csDate.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    csDate.setDataFormat(df.getFormat("dd.mm.yyyy"));

    HSSFCellStyle csTitle = wb.createCellStyle();
    csTitle.setFont(fontTitle);
    csTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    csTitle.setDataFormat(df.getFormat("text"));

    HSSFCellStyle csSubtitle = wb.createCellStyle();
    csSubtitle.setFont(fontSubtitle);
    csSubtitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    csSubtitle.setDataFormat(df.getFormat("text"));

    HSSFCellStyle csNum2 = wb.createCellStyle();
    csNum2.setFont(font);
    csNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csNum2.setDataFormat(df.getFormat("#,##0.00"));

    HSSFCellStyle csNum3 = wb.createCellStyle();
    csNum3.setFont(font);
    csNum3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csNum3.setDataFormat(df.getFormat("#,##0.000"));

    HSSFCellStyle csNum = wb.createCellStyle();
    csNum.setFont(font);
    csNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csNum.setDataFormat(df.getFormat("#.#"));

    HSSFCellStyle csInt = wb.createCellStyle();
    csInt.setFont(font);
    csInt.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csInt.setDataFormat(df.getFormat("#"));

    HSSFCellStyle csText = wb.createCellStyle();
    csText.setFont(font);
    csText.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    csText.setDataFormat(df.getFormat("text"));

    repDynamicProvider dp = repDynamicProvider.getInstance();
    boolean sums = dp.hasSumRow();
    int cols = getColumnModel().getColumnCount();
    int rows = getRowCount() - (sums ? 1 : 0);

    HSSFSheet sheet = wb.createSheet();
    HSSFRow row;
    HSSFCell cell;

    short cRow = 0;

    // header and title
    row = sheet.createRow(cRow = 0);
    cell = row.createCell((short) 0);
    cell.setCellStyle(csText);
    if (cols > 1)
        sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1)));
    cell.setEncoding(wb.ENCODING_UTF_16);
    cell.setCellValue(repMemo.getrepMemo().getOneLine());

    row = sheet.createRow(++cRow);
    cell = row.createCell((short) 0);
    cell.setCellStyle(csTitle);
    if (cols > 1)
        sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1)));
    cell.setEncoding(wb.ENCODING_UTF_16);
    if (dp.getTitle().length() > 0)
        cell.setCellValue(dp.getTitle().substring(1));
    else
        cell.setCellValue("");

    if (dp.getSubtitle().length() > 0) {
        row = sheet.createRow(++cRow);
        cell = row.createCell((short) 0);
        cell.setCellStyle(csSubtitle);
        if (cols > 1)
            sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1)));
        cell.setEncoding(wb.ENCODING_UTF_16);
        cell.setCellValue(dp.getSubtitle().substring(1));
    }

    for (short c = 0; c < cols; c++)
        sheet.setColumnWidth(c, (short) (getColumnModel().getColumn(c).getWidth() * 40));

    // sections
    row = sheet.createRow(++cRow);
    int secRow = 0, firstRow = 0;

    for (int r = 0; r < rows; r++) {
        if (r == 0) {
            row = sheet.createRow(++cRow);
            for (short c = 0; c < cols; c++) {
                cell = row.createCell(c);
                cell.setCellStyle(csHeader);
                cell.setEncoding(wb.ENCODING_UTF_16);
                cell.setCellValue(getColumnModel().getColumn(c).getHeaderValue().toString());
            }
            if (firstRow == 0)
                firstRow = cRow;
            secRow = cRow;
        }
        row = sheet.createRow(++cRow);
        for (short c = 0; c < cols; c++) {
            cell = row.createCell(c);
            Object o = getValueAt(r, c);
            if (o instanceof Number) {
                if (o instanceof BigDecimal) {
                    BigDecimal bd = (BigDecimal) o;
                    if (bd.scale() == 2)
                        cell.setCellStyle(csNum2);
                    else if (bd.scale() == 3)
                        cell.setCellStyle(csNum3);
                    else
                        cell.setCellStyle(csNum);
                    cell.setCellValue(bd.doubleValue());
                } else {
                    String t = dp.getValueAt(r, c);
                    if (Aus.isDigit(t)) {
                        cell.setCellStyle(csInt);
                        cell.setCellValue(((Number) o).doubleValue());
                    } else {
                        cell.setCellStyle(csText);
                        cell.setEncoding(wb.ENCODING_UTF_16);
                        cell.setCellValue(t);
                    }
                }
            } else if (o instanceof Date) {
                cell.setCellStyle(csDate);
                cell.setCellValue((Date) o);
            } else {
                cell.setCellStyle(csText);
                cell.setEncoding(wb.ENCODING_UTF_16);
                cell.setCellValue(dp.getValueAt(r, c));
            }
        }
    }
    System.out.println("sums " + sums);
    if (sums) {
        int non = 0;
        while (non < cols && dp.getValueAt(getRowCount() - 1, non).trim().length() == 0)
            ++non;
        if (non < cols) {
            System.out.println("creating row " + non);
            row = sheet.createRow(++cRow);

            if (non > 0) {
                cell = row.createCell((short) 0);
                cell.setCellStyle(csFooter);
                cell.setEncoding(wb.ENCODING_UTF_16);
                cell.setCellValue("U K U P N O");
                if (non > 1)
                    sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (non - 1)));
            }
            for (short c = (short) non; c < cols; c++) {
                cell = row.createCell(c);
                Object o = getValueAt(rows - 1, c);
                if ((o instanceof BigDecimal) && ((BigDecimal) o).scale() == 2)
                    cell.setCellStyle(csFooterNum2);
                else
                    cell.setCellStyle(csFooterNum);
                if (dp.getValueAt(getRowCount() - 1, c).trim().length() != 0)
                    cell.setCellFormula("SUBTOTAL(9;" + xlsRange(firstRow + 1, cRow, c) + ")");
                else
                    cell.setCellValue("");
            }
        }
    }
    FileOutputStream out = null;

    try {
        out = new FileOutputStream(output);
        wb.write(out);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (out != null)
            try {
                out.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}

From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java

License:Apache License

private void initHssfStyle(final HSSFWorkbook workbook) {
    // default://w ww  .  j  a v  a  2  s  . c  om
    final HSSFCellStyle oddCellStyle = createRowCellStyle(workbook, true);
    final HSSFCellStyle evenCellStyle = createRowCellStyle(workbook, true);
    oddHssfStyleCache.put(DataType.Boolean, oddCellStyle);
    oddHssfStyleCache.put(DataType.String, oddCellStyle);
    evenHssfStyleCache.put(DataType.Boolean, evenCellStyle);
    evenHssfStyleCache.put(DataType.String, evenCellStyle);

    // Nombre sans dcimal
    final HSSFCellStyle oddLongCellStyle = createRowCellStyle(workbook, true);
    final HSSFCellStyle evenLongCellStyle = createRowCellStyle(workbook, true);
    oddLongCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
    evenLongCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
    oddHssfStyleCache.put(DataType.Long, oddLongCellStyle);
    oddHssfStyleCache.put(DataType.Integer, oddLongCellStyle);
    evenHssfStyleCache.put(DataType.Long, evenLongCellStyle);
    evenHssfStyleCache.put(DataType.Integer, evenLongCellStyle);

    // Nombre a dcimal
    final HSSFCellStyle oddDoubleCellStyle = createRowCellStyle(workbook, true);
    final HSSFCellStyle evenDoubleCellStyle = createRowCellStyle(workbook, true);
    oddDoubleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
    evenDoubleCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
    oddHssfStyleCache.put(DataType.Double, oddDoubleCellStyle);
    oddHssfStyleCache.put(DataType.BigDecimal, oddDoubleCellStyle);
    evenHssfStyleCache.put(DataType.Double, evenDoubleCellStyle);
    evenHssfStyleCache.put(DataType.BigDecimal, evenDoubleCellStyle);

    // Date
    final HSSFCellStyle oddDateCellStyle = createRowCellStyle(workbook, true);
    final HSSFCellStyle evenDateCellStyle = createRowCellStyle(workbook, true);
    oddDateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy" /* "m/d/yy h:mm" */));
    evenDateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy" /* "m/d/yy h:mm" */));
    oddHssfStyleCache.put(DataType.Date, oddDateCellStyle);
    evenHssfStyleCache.put(DataType.Date, evenDateCellStyle);

}