Example usage for org.apache.poi.hssf.usermodel HSSFDataFormat getFormat

List of usage examples for org.apache.poi.hssf.usermodel HSSFDataFormat getFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFDataFormat
  6. getFormat

Introduction

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

Prototype

public String getFormat(short index)

Source Link

Document

get the format string that matches the given format index

Usage

From source file:com.ssic.education.provider.controller.ProSupplierController.java

@RequestMapping(value = "/excel")
@ResponseBody/*from w w w.jav a 2 s .  c om*/
public ModelAndView exportExcel(SupplierDto supplierDto, HttpServletRequest request,
        HttpServletResponse response) {
    SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME);
    if (info == null) {
        return null;
    }
    supplierDto.setReceiverId(info.getSupplierId());
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d");
    Date date = new Date();
    String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
    HSSFSheet sheet;
    HSSFCell cell;
    response.setContentType("application/octet-stream");
    response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
    Workbook workbook = new HSSFWorkbook();
    sheet = (HSSFSheet) workbook.createSheet("");
    try {
        List<String> titles = new ArrayList<String>();
        titles.add("????");
        titles.add("???");
        //         titles.add("????");
        //         titles.add("??????");
        titles.add("????");
        titles.add("???");
        //         titles.add("?");
        //         titles.add("???");
        titles.add("?");
        titles.add("?");
        int len = titles.size();
        HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ?
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont headerFont = (HSSFFont) workbook.createFont(); // 
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints((short) 11);
        headerStyle.setFont(headerFont);
        short width = 20, height = 25 * 20;
        sheet.setDefaultColumnWidth(width);
        HSSFRow sheetRow = sheet.createRow(0);
        for (int i = 0; i < len; i++) { // 
            String title = titles.get(i);
            cell = sheetRow.createCell(i);
            cell.setCellStyle(headerStyle);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(title);
        }
        sheet.getRow(0).setHeight(height);
        HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ?
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        DataGrid dg = supplierService.findProSupplier(supplierDto, null);
        List<SupplierDto> expList = dg.getRows();
        List<PageData> varList = new ArrayList<PageData>();
        if (!CollectionUtils.isEmpty(expList)) {
            for (int i = 0; i < expList.size(); i++) {
                PageData vpd = new PageData();
                vpd.put("var1", expList.get(i).getSupplierName());
                vpd.put("var2", expList.get(i).getAddress());
                //               vpd.put("var3", expList.get(i).getFoodServiceCode());
                //               vpd.put("var4", expList.get(i).getFoodBusinessCode());
                vpd.put("var3", expList.get(i).getFoodCirculationCode());
                vpd.put("var4", expList.get(i).getFoodProduceCode());
                //               vpd.put("var7", expList.get(i).getBusinessLicense());
                //               vpd.put("var8", expList.get(i).getSupplierCode());
                vpd.put("var5", expList.get(i).getCorporation());
                vpd.put("var6", expList.get(i).getContactWay());
                varList.add(vpd);
            }
        }
        for (int i = 0; i < varList.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            PageData vpd = varList.get(i);
            for (int j = 0; j < len; j++) {
                String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
                cell = row.createCell(j);
                HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle();
                HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat();
                cellStyle2.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(cellStyle2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(varstr);

            }

        }
        OutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
    } catch (Exception e) {

    }
    return null;
}

From source file:com.ssic.education.provider.controller.WaresController.java

@RequestMapping(value = "/excel")
@ResponseBody//from   w ww  . j a v a  2  s  .c om
public ModelAndView exportExcel(ProWaresDto proWaresDto, HttpServletRequest request,
        HttpServletResponse response) {
    SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME);
    if (info == null) {
        return null;
    }
    proWaresDto.setSupplierId(info.getSupplierId());
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d");
    Date date = new Date();
    String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
    HSSFSheet sheet;
    HSSFCell cell;
    response.setContentType("application/octet-stream");
    response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
    Workbook workbook = new HSSFWorkbook();
    sheet = (HSSFSheet) workbook.createSheet("");
    try {
        List<String> titles = new ArrayList<String>();
        titles.add("??");
        titles.add("???");
        titles.add("");
        titles.add("?");
        titles.add("?");
        //         titles.add("??");
        //         titles.add("???");
        //         titles.add("??");
        titles.add("?");
        titles.add("???");
        titles.add("");
        int len = titles.size();
        HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ?
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont headerFont = (HSSFFont) workbook.createFont(); // 
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints((short) 11);
        headerStyle.setFont(headerFont);
        short width = 20, height = 25 * 20;
        sheet.setDefaultColumnWidth(width);
        HSSFRow sheetRow = sheet.createRow(0);
        for (int i = 0; i < len; i++) { // 
            String title = titles.get(i);
            cell = sheetRow.createCell(i);
            cell.setCellStyle(headerStyle);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(title);
        }
        sheet.getRow(0).setHeight(height);
        HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ?
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        List<ProWaresDto> expList = waresService.findAllWares(proWaresDto, null);
        List<PageData> varList = new ArrayList<PageData>();
        if (!CollectionUtils.isEmpty(expList)) {
            for (int i = 0; i < expList.size(); i++) {
                PageData vpd = new PageData();
                vpd.put("var1", expList.get(i).getWaresName());
                vpd.put("var2", expList.get(i).getAmountUnit());
                vpd.put("var3", expList.get(i).getSpec());
                vpd.put("var4", ProductClass.getName(expList.get(i).getWaresType()));
                vpd.put("var5", expList.get(i).getManufacturer());
                //               vpd.put("var5", expList.get(i).getEnName());
                //               vpd.put("var6", expList.get(i).getBarCode());
                //               vpd.put("var7", expList.get(i).getCustomCode());
                vpd.put("var6", expList.get(i).getShelfLife());
                vpd.put("var7", expList.get(i).getUnit());
                vpd.put("var8", expList.get(i).getPlace());
                varList.add(vpd);
            }
        }
        for (int i = 0; i < varList.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            PageData vpd = varList.get(i);
            for (int j = 0; j < len; j++) {
                String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
                cell = row.createCell(j);
                HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle();
                HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat();
                cellStyle2.setDataFormat(format.getFormat("@"));
                cell.setCellStyle(cellStyle2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(varstr);

            }

        }
        OutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
    } catch (Exception e) {

    }
    return null;
}

From source file:com.taobao.itest.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

protected static HSSFCellStyle createDateCellStyle(HSSFWorkbook workbook) {
    HSSFDataFormat format = workbook.createDataFormat();
    short dateFormatCode = format.getFormat(DATE_FORMAT_AS_NUMBER_DBUNIT);
    HSSFCellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(dateFormatCode);
    return dateCellStyle;
}

From source file:com.taobao.itest.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

protected void setNumericCell(HSSFCell cell, BigDecimal value, HSSFWorkbook workbook) {
    if (logger.isDebugEnabled())
        logger.debug("setNumericCell(cell={}, value={}, workbook={}) - start",
                new Object[] { cell, value, workbook });

    cell.setCellValue(((BigDecimal) value).doubleValue());

    HSSFDataFormat df = workbook.createDataFormat();
    int scale = ((BigDecimal) value).scale();
    short format;
    if (scale <= 0) {
        format = df.getFormat("####");
    } else {/*from   w w w  .  j a  v  a 2  s . com*/
        String zeros = createZeros(((BigDecimal) value).scale());
        format = df.getFormat("####." + zeros);
    }
    if (logger.isDebugEnabled())
        logger.debug("Using format '{}' for value '{}'.", String.valueOf(format), value);

    HSSFCellStyle cellStyleNumber = workbook.createCellStyle();
    cellStyleNumber.setDataFormat(format);
    cell.setCellStyle(cellStyleNumber);
}

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLS.java

License:Open Source License

/**
 * create a library of cell styles//from  w w w.ja v  a  2 s.  co m
 */
private static Map<String, HSSFCellStyle> createStyles(HSSFWorkbook wb) {
    Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>();
    HSSFDataFormat df = wb.createDataFormat();

    HSSFCellStyle style;
    HSSFFont headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("header_date", style);

    HSSFFont font1 = wb.createFont();
    font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font1);
    styles.put("cell_b", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font1);
    styles.put("cell_b_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("cell_b_date", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("cell_g", style);

    HSSFFont font2 = wb.createFont();
    font2.setColor(IndexedColors.BLUE.getIndex());
    font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font2);
    styles.put("cell_bb", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("cell_bg", style);

    HSSFFont font3 = wb.createFont();
    font3.setFontHeightInPoints((short) 14);
    font3.setColor(IndexedColors.DARK_BLUE.getIndex());
    font3.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font3);
    style.setWrapText(true);
    styles.put("cell_h", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setWrapText(true);
    styles.put("cell_normal", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    styles.put("cell_normal_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setWrapText(true);
    style.setDataFormat(df.getFormat("dd.MM.yyyy"));
    styles.put("cell_normal_date", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setIndention((short) 1);
    style.setWrapText(true);
    styles.put("cell_indented", style);

    style = createBorderedStyle(wb);
    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styles.put("cell_blue", style);

    return styles;
}

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

License:Apache License

private static boolean writeXLS(String outputFilePath) throws Exception {

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

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

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

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

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

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

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

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

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

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

        myCell2.setCellStyle(myCellStylePercent);

        rowCounter++;

    }

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

    return true;
}

From source file: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  www  . j  a  va  2s .  c  o m*/

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

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

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

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

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

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

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

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

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

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

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

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

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

From source file:excel.FileExcel.java

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

        HSSFWorkbook workbook;/*from  www .j av  a 2s.c  o m*/
        HSSFSheet sheet;

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

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

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

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

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

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

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

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

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

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

        int row_num = 0;

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

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

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

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

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

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

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);//from w  w  w. j  av  a2 s.  co m
    return dataStyle;
}

From source file:hr.restart.sisfun.frmReportxList.java

License:Apache License

void fillDataProc(File orig, HSSFWorkbook wb) {
    DataSet logo = dM.getDataModule().getLogotipovi();
    DataSet orgs = dM.getDataModule().getOrgstruktura();
    String corg = jpc.getCorg();/*w w w.j  a va2 s. c  o m*/
    while (!ld.raLocate(logo, "CORG", corg)) {
        if (!ld.raLocate(orgs, "CORG", corg)) {
            JOptionPane.showMessageDialog(this.getWindow(), "Greka u organizacijskim jedinicama!", "Greka",
                    JOptionPane.ERROR_MESSAGE);
            return;
        }
        if (orgs.getString("PRIPADNOST").equals(corg)) {
            JOptionPane.showMessageDialog(this.getWindow(), "Nije definiran logotip za knjigovodstvo!",
                    "Greka", JOptionPane.ERROR_MESSAGE);
            return;
        }
        corg = orgs.getString("PRIPADNOST");
    }
    raProcess.checkClosing();

    StorageDataSet gk = Gkstavke.getDataModule().getScopedSet("BROJKONTA ID IP");
    raProcess.fillScratchDataSet(gk, "SELECT brojkonta,id,ip FROM gkstavke WHERE "
            + jpc.getCondition().and(Condition.between("DATUMKNJ", fld, "DATFROM", "DATTO")));
    StorageDataSet ogk = Gkstavke.getDataModule().getScopedSet("BROJKONTA ID IP");
    Timestamp old = Util.getUtil().addYears(fld.getTimestamp("DATFROM"), -1);
    raProcess.fillScratchDataSet(ogk,
            "SELECT brojkonta,id,ip FROM gkstavke WHERE " + jpc.getCondition().and(Condition.between("DATUMKNJ",
                    Util.getUtil().getFirstDayOfYear(old), Util.getUtil().getLastDayOfYear(old))));
    gk.enableDataSetEvents(false);
    gk.setSort(new SortDescriptor(new String[] { "BROJKONTA" }));
    ogk.enableDataSetEvents(false);
    ogk.setSort(new SortDescriptor(new String[] { "BROJKONTA" }));

    HSSFDataFormat df = wb.createDataFormat();

    HSSFSheet sh = wb.getSheetAt(0);
    if (sh == null)
        throw new RuntimeException("Greka u plahti!");

    DataSet rep = Repxdata.getDataModule().getTempSet(Condition.equal("CREP", reps));
    rep.open();

    raProcess.checkClosing();
    for (rep.first(); rep.inBounds(); rep.next()) {
        HSSFRow hr = sh.getRow((short) (rep.getInt("RED") - 1));
        HSSFCell cell = hr.getCell((short) (rep.getInt("KOL") - 1));
        if ("S".equals(rep.getString("TIP"))) {
            fillString(cell, logo, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("text"));
        } else if ("2".equals(rep.getString("TIP"))) {
            fillNum(cell, gk, ogk, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00"));
        } else if ("D".equals(rep.getString("TIP"))) {
            fillDate(cell, rep.getString("DATA"));
            cell.getCellStyle().setDataFormat(df.getFormat("dd.mm.yyyy"));
        }
        raProcess.checkClosing();
    }
    String oname = orig.getAbsolutePath();
    oname = oname.substring(0, oname.length() - 4);

    FileOutputStream out = null;

    try {
        out = new FileOutputStream(oname + "-RA.xls");
        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();
            }
    }
}