POI通用导出Excel(.xls,.xlsx)的方法

package sy.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class ExcelUtil{
  public static String NO_DEFINE = "no_define";//未定义的字段
  public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式
  public static int DEFAULT_COLOUMN_WIDTH = 17;
  /**
   * 导出Excel 97(.xls)格式 ，少量数据
   * @param title 标题行 
   * @param headMap 属性-列名
   * @param jsonArray 数据集
   * @param datePattern 日期格式，null则用默认日期格式
   * @param colWidth 列宽 默认 至少17个字节
   * @param out 输出流
   */
  public static void exportExcel(String title,Map headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
    if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
    // 声明一个工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    workbook.createInformationProperties();
    workbook.getDocumentSummaryInformation().setCompany("*****公司");
    SummaryInformation si = workbook.getSummaryInformation();
    si.setAuthor("JACK"); //填加xls文件作者信息
    si.setApplicationName("导出程序"); //填加xls文件创建程序信息
    si.setLastAuthor("最后保存者信息"); //填加xls文件最后保存者信息
    si.setComments("JACK is a programmer!"); //填加xls文件作者信息
    si.setTitle("POI导出Excel"); //填加xls文件标题信息
    si.setSubject("POI导出Excel");//填加文件主题信息
    si.setCreateDateTime(new Date());
     //表头样式
    HSSFCellStyle titleStyle = workbook.createCellStyle();
    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont titleFOnt= workbook.createFont();
    titleFont.setFontHeightInPoints((short) 20);
    titleFont.setBoldweight((short) 700);
    titleStyle.setFont(titleFont);
    // 列头样式
    HSSFCellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont headerFOnt= workbook.createFont();
    headerFont.setFontHeightInPoints((short) 12);
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // 单元格样式
    HSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    HSSFFont cellFOnt= workbook.createFont();
    cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    cellStyle.setFont(cellFont);
    // 生成一个(带标题)表格
    HSSFSheet sheet = workbook.createSheet();
    // 声明一个画图的顶级管理器
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    // 定义注释的大小和位置,详见文档
    HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
        0, 0, 0, (short) 4, 2, (short) 6, 5));
    // 设置注释内容
    comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
    // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
    comment.setAuthor("JACK");
    //设置列宽
    int minBytes = colWidth iter = headMap.keySet().iterator(); iter
        .hasNext();) {
      String fieldName = iter.next();

      properties[ii] = fieldName;
      headers[ii] = fieldName;

      int bytes = fieldName.getBytes().length;
      arrColWidth[ii] = bytes  iter = headMap.keySet().iterator(); iter
        .hasNext();) {
      String fieldName = iter.next();

      properties[ii] = fieldName;
      headers[ii] = headMap.get(fieldName);

      int bytes = fieldName.getBytes().length;
      arrColWidth[ii] = bytes  headMap = new LinkedHashMap();
    headMap.put("name","姓名");
    headMap.put("age","年龄");
    headMap.put("birthday","生日");
    headMap.put("height","身高");
    headMap.put("weight","体重");
    headMap.put("sex","性别");

    String title = "测试";
    /*
    OutputStream outXls = new FileOutputStream("E://a.xls");
    System.out.println("正在导出xls....");
    Date d = new Date();
    ExcelUtil.exportExcel(title,headMap,ja,null,outXls);
    System.out.println("共"+count+"条数据,执行"+(new Date().getTime()-d.getTime())+"ms");
    outXls.close();*/
    //
    OutputStream outXlsx = new FileOutputStream("E://b.xlsx");
    System.out.println("正在导出xlsx....");
    Date d2 = new Date();
    ExcelUtil.exportExcelX(title,headMap,ja,null,0,outXlsx);
    System.out.println("共"+count+"条数据,执行"+(new Date().getTime()-d2.getTime())+"ms");
    outXlsx.close();

  }
}
class Student {
  private String name;
  private int age;
  private Date birthday;
  private float height;
  private double weight;
  private boolean sex;

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public Integer getAge() {
    return age;
  }

  public Date getBirthday() {
    return birthday;
  }

  public void setBirthday(Date birthday) {
    this.birthday = birthday;
  }

  public float getHeight() {
    return height;
  }

  public void setHeight(float height) {
    this.height = height;
  }

  public double getWeight() {
    return weight;
  }

  public void setWeight(double weight) {
    this.weight = weight;
  }

  public boolean isSex() {
    return sex;
  }

  public void setSex(boolean sex) {
    this.sex = sex;
  }

  public void setAge(Integer age) {
    this.age = age;
  }
}

//导出配件列表
  @RequestMapping(value = "partExport")
  @ResponseBody
  public void partExportHttpServletResponse response){

    JSONArray ja = ptmpOrderService.selectStatExport();//获取业务数据集
    Map headMap = ptmpOrderService.getPartStatHeadMap();//获取属性-列头
    String title = "配件统计表";
    ExcelUtil.downloadExcelFile(title,headMap,ja,response);
  }

//可以点击一个按钮事件触发下面的代码进行导出
window.open("partExport","_blank");
//或者可以提交表单
$('#form').attr('action','partExport');
$('#form').attr('target','_blank');
$('#form').submit();

  org.apache.poi
  poi-ooxml
  3.14