采用excel导出报表

package excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

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.HSSFColor;
import org.apache.poi.hssf.util.Region;

public class ExcelExport {
   
    public void export() {
        String exportPath = "d://workbook.xls";
        HSSFWorkbook wb = new HSSFWorkbook();        //新建工作簿
        HSSFSheet sheet = wb.createSheet("资源导出");    //新建sheet并起名
        HSSFRow row = null;       
        HSSFCell cell = null;
        HSSFCellStyle style = wb.createCellStyle();    //表报名下的样式对象
        HSSFCellStyle style1 = wb.createCellStyle();    //表报名下的时间样式对象
        HSSFCellStyle style2 = wb.createCellStyle();    //表报的表头样式对象
        HSSFFont hfh = wb.createFont(); //报表名的字体对象
        HSSFFont hft = wb.createFont(); //报表名下的时间字体对象
        HSSFFont hf = wb.createFont(); //报表的表头字体对象
       
        //设置每列的宽度
        //36为像素比36*150 是在excel 中是150个像素
        sheet.setColumnWidth((short)1, (short)(36*150));
        sheet.setColumnWidth((short)2, (short)(36*150));
        sheet.setColumnWidth((short)3, (short)(36*150));
        sheet.setColumnWidth((short)4, (short)(36*150));
        sheet.setColumnWidth((short)5, (short)(36*150));
        sheet.setColumnWidth((short)6, (short)(36*150));
        sheet.setColumnWidth((short)7, (short)(36*150));
        sheet.setColumnWidth((short)8, (short)(36*150));
        sheet.setColumnWidth((short)9, (short)(36*150));
        sheet.setColumnWidth((short)10, (short)(36*150));
        sheet.setColumnWidth((short)11, (short)(36*150));
        sheet.setColumnWidth((short)12, (short)(36*150));
       
        //第一行 报表名
        row = sheet.createRow((short)1);
        sheet.addMergedRegion(new Region(1,(short)1,2,(short)13));  //合并单元格
        cell = row.createCell((short)1);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
        hfh.setBoldweight((short)1);
        hfh.setFontHeight((short)300);
        hfh.setColor(HSSFColor.BLUE.index);
        style.setFont(hfh);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("资源导出"));
       
        //第二行
        row = sheet.createRow((short)3);
        sheet.addMergedRegion(new Region(3,(short)4,3,(short)5));
        cell = row.createCell((short)4);
        style1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        hfh.setFontHeight((short)300);
        hfh.setColor(HSSFColor.BLACK.index);
        style1.setFont(hft);
        cell.setCellStyle(style1);
        cell.setCellValue(new HSSFRichTextString("出报表时间    "));
       
        sheet.addMergedRegion(new Region(3,(short)6,3,(short)7));
        Date now = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String presentTime = sdf.format(now);
        cell = row.createCell((short)6);
        style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        hft.setColor(HSSFColor.BLACK.index);
        hft.setFontHeight((short)200);
        style2.setFont(hft);
        cell.setCellStyle(style2);
        cell.setCellValue(new HSSFRichTextString(presentTime));
       
        //第四行开始，表头
        row = sheet.createRow((short)4);
        hf.setFontHeight((short)300);
        hf.setColor(HSSFColor.BLACK.index);
        createCell(wb, row, (short)1, "营销中心名称", hf);
        createCell(wb, row, (short)2, "接入间名称", hf);
        createCell(wb, row, (short)3, "接入间代码", hf);
        createCell(wb, row, (short)4, "设备编码", hf);
        createCell(wb, row, (short)5, "端口数", hf);
        createCell(wb, row, (short)6, "设备地址", hf);
        createCell(wb, row, (short)7, "资源编码", hf);
        createCell(wb, row, (short)8, "资源状态", hf);
        createCell(wb, row, (short)9, "电话号码", hf);
        createCell(wb, row, (short)10, "物理号码", hf);
        createCell(wb, row, (short)11, "上级资源编码", hf);
        createCell(wb, row, (short)12, "下级资源编码", hf);
       
        for (int i = 0; i < 5; i++) {
            row = sheet.createRow((short)5+i);
           
            for (int j = 1; j <= 12; j++) {
                createCell(wb, row, (short)j, "a-"+i+"-"+j, null);
            }
        }
       
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(exportPath);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
       
    }
   
    /**
     *
     * @param wb    工作薄对象
     * @param row   行对象
     * @param col   当前列
     * @param val   cell中要填的值
     * @param font  font当前cell中的字体 如果没有传值为null
     * description  创建新的单元格
      */
    private void createCell(HSSFWorkbook wb, HSSFRow row, short col, String val, HSSFFont font) {
        HSSFCell cell = row.createCell(col);
        cell.setCellValue(new HSSFRichTextString(val));
        HSSFCellStyle cellStyle = wb.createCellStyle();
        if(font != null) {
            cellStyle.setFont(font);
        }
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
        cell.setCellStyle(cellStyle);
    }
   
    public static void main(String[] args) {
        new ExcelExport().export();
    }
}