createexceldemo.java

在java中实现的excel导出功能。这也是一个很好的例子。

package src.com.jp.or.cheng.excel;

import java.io.FileOutputStream;

import org.apache.poi.hssf.record.ChartRecord;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
 
public class CreateExcelDemo {
 
    /**
     *@paramargs
     */
	
	//报表结构不确定且复杂（带分析图）这类报表建议用poi/javascript写数据部分，
	//分析图可以用jfreechart来画，画完后插入到excel中（poi 3.0以后支持图片插入）
	//以下示例为POI写数据和格式的部分，jfreechart 生成图片及插入到Excel的代码略：

    public static void main(String[] args) {
        
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        //表名样式
        HSSFCellStyle titlecellstyle = workbook.createCellStyle();
        titlecellstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐
        HSSFFont titleFont = workbook.createFont();
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
        titleFont.setUnderline(HSSFFont.U_SINGLE);    //单下画线
        titleFont.setFontName("Arial");               //字体
        titleFont.setFontHeightInPoints((short)14);   //大小
        titlecellstyle.setFont(titleFont);
        //表头样式
        HSSFCellStyle formTitleStyle = workbook.createCellStyle();
        formTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        formTitleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        formTitleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        formTitleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        formTitleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        HSSFFont formTitleFont = workbook.createFont();
        formTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        formTitleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short)10);
        formTitleStyle.setFont(formTitleFont);
        //表内容样式
        HSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont contentFont = workbook.createFont();
        contentFont.setFontName("Arial");
        contentFont.setFontHeightInPoints((short)12);
        contentStyle.setFont(contentFont);
        //百分数显示样式
        HSSFCellStyle percentStyle = workbook.createCellStyle();
        percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        percentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        percentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        percentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        percentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        percentStyle.setDataFormat((short)9);
        percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont percentFont = workbook.createFont();
        percentFont.setFontName("Arial");
        percentStyle.setFont(percentFont);
        //写入内容
        cell.setCellStyle(titlecellstyle);
        cell.setCellValue(new HSSFRichTextString("PII-ENG采购申请流程控制分析报告"));
        
        row = sheet.createRow(1);
        
        row = sheet.createRow(2);
        cell = row.createCell(0);
        cell.setCellValue(new HSSFRichTextString("报告日期：2007-10-25      （月报）"));
        row = sheet.createRow(3);
        HSSFRichTextString str1 = new HSSFRichTextString("作业标准：(1) HKD0-10万(14日)；" +
             "(2) HKD10万-50万(14日)；(3) >=HKD50万(37日)； (4) 豁免申请(37日)");
        HSSFRichTextString str2 = new HSSFRichTextString("指标：作业标准内完成数量/总数量>=80%");
        row.createCell(0).setCellValue(str1);
        sheet.createRow(4).createCell(0).setCellValue(str2);
        row=sheet.createRow((short)5);
        cell = row.createCell(0);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("时段"));
        cell = row.createCell(1);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("HKD0-10万"));
        cell = row.createCell(4);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("HKD10万-50万"));
        cell = row.createCell(7);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString(">HKD50万"));
        cell = row.createCell(10);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("豁免申请"));
        cell = row.createCell(13);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("当月合计"));
        cell = row.createCell(17);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("指标"));
        row = sheet.createRow(6);
        cell = row.createCell(1);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("<=14日"));
        cell = row.createCell(2);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString(">14日"));
        cell = row.createCell(3);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("达标率"));
        cell = row.createCell(4);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("<=14日"));
        cell = row.createCell(5);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString(">14日"));
        cell = row.createCell(6);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("达标率"));
        cell = row.createCell(7);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("<=37日"));
        cell = row.createCell(8);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString(">37日"));
        cell = row.createCell(9);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("达标率"));
        cell = row.createCell(10);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("<=37日"));
        cell = row.createCell(11);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString(">37日"));
        cell = row.createCell(12);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("达标率"));
        cell = row.createCell(13);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("达标数"));
        cell = row.createCell(14);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("不达标数"));
        cell = row.createCell(15);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("合计"));
        cell = row.createCell(16);
        cell.setCellStyle(formTitleStyle);
        cell.setCellValue(new HSSFRichTextString("合计达标率"));
        //合并单元格
        CellRangeAddress intCRA = null;
        intCRA = new CellRangeAddress(5,6,0,0);
        sheet.addMergedRegion(intCRA);
        intCRA = new CellRangeAddress(5,5,1,3);
        sheet.addMergedRegion(intCRA);
        intCRA = new CellRangeAddress(5,5,4,6);
        sheet.addMergedRegion(intCRA);
        intCRA = new CellRangeAddress(5,5,7,9);
        sheet.addMergedRegion(intCRA);
        intCRA = new CellRangeAddress(5,5,10,12);
        sheet.addMergedRegion(intCRA);
        intCRA = new CellRangeAddress(5,5,13,16);
        sheet.addMergedRegion(intCRA);
        intCRA = new CellRangeAddress(5,6,17,17);
        sheet.addMergedRegion(intCRA);
        //加入数据，设置公式
        row = sheet.createRow(7);
        cell = row.createCell(0);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(new HSSFRichTextString("1月"));
        cell = row.createCell(1);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(256);
        cell = row.createCell(2);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(1);
        cell = row.createCell(3);
        cell.setCellStyle(percentStyle);
        cell.setCellFormula("B8/(B8+C8)");
        cell = row.createCell(4);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(3);
        cell = row.createCell(5);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(5);
        cell = row.createCell(6);
        cell.setCellStyle(percentStyle);
        cell.setCellFormula("E8/(E8+F8)");
        cell = row.createCell(7);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(0);
        cell = row.createCell(8);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(5);
        cell = row.createCell(9);
        cell.setCellStyle(percentStyle);
        cell.setCellFormula("H8/(H8+I8)");
        cell = row.createCell(10);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(2);
        cell = row.createCell(11);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(0);
        cell = row.createCell(12);
        cell.setCellStyle(percentStyle);
        cell.setCellFormula("K8/(K8+L8)");
        cell = row.createCell(13);
        cell.setCellStyle(contentStyle);
        cell.setCellFormula("sum(b8+e8+h8+k8)");
        cell = row.createCell(14);
        cell.setCellStyle(contentStyle);
        cell.setCellFormula("sum(c8+f8+i8+l8)");
        cell = row.createCell(15);
        cell.setCellStyle(contentStyle);
        cell.setCellFormula("sum(n8+o8)");
        cell = row.createCell(16);
        cell.setCellFormula("n8/p8");
        cell.setCellStyle(percentStyle);
        cell = row.createCell(17);
        cell.setCellValue(0.8);
        cell.setCellStyle(percentStyle);
        //HSSFChart chart = new HSSFChart();
        //chart.createBarChart(workbook, sheet);
        ChartRecord chart = new ChartRecord();
        chart.setHeight(200);
        chart.setWidth(400);
        chart.setX(20);
        chart.setY(20);
        
        workbook.setSheetName(0, "1月");
        
        try {
            FileOutputStream fileOut = new FileOutputStream("D:\\demo.xls");
            workbook.write(fileOut);
            fileOut.close();
            System.out.println("cheng gong!!!");
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }
}