POI小demo

使用poi需要先下载相关jar包(http://download.csdn.net/detail/wangkunisok/9454545)

poi-3.14-20160307.jar

poi-ooxml-3.14-20160307.jar

poi-ooxml-schemas-3.14-20160307.jar

xmlbeans-2.6.0.jar

PoiUtil.java

  1 package com.zto.branchaging.web.controller;
  2 
  3 import java.io.IOException;
  4 import java.io.OutputStream;
  5 import java.lang.reflect.Field;
  6 import java.lang.reflect.InvocationTargetException;
  7 import java.lang.reflect.Method;
  8 import java.util.ArrayList;
  9 import java.util.Arrays;
 10 import java.util.Collection;
 11 import java.util.Iterator;
 12 import java.util.List;
 13 
 14 import org.apache.poi.hssf.usermodel.HSSFCell;
 15 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 16 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
 17 import org.apache.poi.hssf.usermodel.HSSFRow;
 18 import org.apache.poi.hssf.usermodel.HSSFSheet;
 19 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 20 
 21 /**
 22  * 
 23  * 
 24  * @author leno
 25  * @version v1.0
 26  * @param <T>
 27  *            应用泛型，代表任意一个符合javabean风格的类
 28  *            注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 29  *            byte[]表jpg格式的图片数据
 30  */
 31 public class PoiUtil<T> {
 32 
 33     public void exportExcel(Collection<T> dataset, OutputStream out) {
 34         exportExcel("POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");
 35     }
 36 
 37     public void exportExcel(String[] headers, Collection<T> dataset,
 38             OutputStream out) {
 39         exportExcel("POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
 40     }
 41 
 42     public void exportExcel(String[] headers, Collection<T> dataset,
 43             OutputStream out, String pattern) {
 44         exportExcel("POI导出EXCEL文档", headers, dataset, out, pattern);
 45     }
 46 
 47     /**
 48      * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
 49      * 
 50      * @param title
 51      *            表格标题名
 52      * @param headers
 53      *            表格属性列名数组
 54      * @param dataset
 55      *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
 56      *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
 57      * @param out
 58      *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
 59      * @param pattern
 60      *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
 61      */
 62     @SuppressWarnings("unchecked")
 63     public void exportExcel(String title, String[] headers,
 64             Collection<T> dataset, OutputStream out, String pattern) {
 65        
 66         List result = new ArrayList();
 67         List paramterList = Arrays.asList(dataset.toArray());
 68         // 声明一个工作薄
 69         HSSFWorkbook workbook = new HSSFWorkbook();
 70         
 71         //设定每个sheet存储的数据量为五万，不能超过六万五
 72         int pageCount = (int)Math.ceil(dataset.size()/50000)+1;//需要创建的sheet个数
 73         if(dataset.size()<=50000){
 74             result = paramterList.subList(0, dataset.size());
 75         }
 76         
 77         // 生成一个样式
 78         HSSFCellStyle style = workbook.createCellStyle();
 79         style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中  
 80         
 81         for(int j=0;j<pageCount;j++){
 82             int startIndex = j*50000;
 83             int endIndex = startIndex+50000;
 84             if(pageCount-j==1){
 85                 endIndex = dataset.size();
 86             }          
 87             result = paramterList.subList(startIndex, endIndex);
 88             // 生成一个表格
 89             HSSFSheet sheet = workbook.createSheet(title+"-"+String.valueOf(j+1));               
 90             // 设置表格默认列宽度为15个字节
 91             sheet.setDefaultColumnWidth((short) 15);
 92             sheet.setColumnWidth(1, 24*256);  //设置列宽，第二列24个字符宽
 93             // 产生表格标题行
 94             HSSFRow row = sheet.createRow(0);
 95             for (short i = 0; i < headers.length; i++) {
 96                 HSSFCell cell = row.createCell(i);
 97                 cell.setCellStyle(style);
 98                 HSSFRichTextString text = new HSSFRichTextString(headers[i]);
 99                 cell.setCellValue(text);
100             }
101             // 遍历集合数据，产生数据行
102             Iterator<T> it = result.iterator();
103             int index = 0;
104             while (it.hasNext()) {
105                 index++;
106                 row = sheet.createRow(index);
107                 T t = (T) it.next();
108                 // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
109                 Field[] fields = t.getClass().getDeclaredFields();
110                 for (short i = 0; i < fields.length; i++) {
111                     HSSFCell cell = row.createCell(i);
112                     cell.setCellStyle(style);
113                     Field field = fields[i];
114                     String fieldName = field.getName();
115                     String getMethodName = "get"
116                             + fieldName.substring(0, 1).toUpperCase()
117                             + fieldName.substring(1);
118                     try {
119                         Class tCls = t.getClass();
120                         Method getMethod = tCls.getMethod(getMethodName,
121                                 new Class[] {});
122                         Object value = getMethod.invoke(t, new Object[] {});
123                        
124                          // 数据类型都当作字符串简单处理
125                         String textValue = String.valueOf(null==value?"":value);
126                         
127                         if (textValue != null) {
128                             cell.setCellValue(textValue);
129                         }
130                     } catch (SecurityException e) {
131                         // TODO Auto-generated catch block
132                         e.printStackTrace();
133                     } catch (NoSuchMethodException e) {
134                         // TODO Auto-generated catch block
135                         e.printStackTrace();
136                     } catch (IllegalArgumentException e) {
137                         // TODO Auto-generated catch block
138                         e.printStackTrace();
139                     } catch (IllegalAccessException e) {
140                         // TODO Auto-generated catch block
141                         e.printStackTrace();
142                     } catch (InvocationTargetException e) {
143                         // TODO Auto-generated catch block
144                         e.printStackTrace();
145                     } finally {
146                         // 清理资源
147                     }
148                 }
149 
150             }
151             
152         }
153         try {
154             workbook.write(out);
155         } catch (IOException e) {
156             // TODO Auto-generated catch block
157             e.printStackTrace();
158         }
159 
160     }
161 
162 }

POIController.java

package com.branchaging.web.controller;
@Controller
@RequestMapping("/singleAmount")
public class POIController{

    @Autowired
    private IQuantityStatisBiz quantityStatisBiz;
 
    @RequestMapping("/doExclExport")
    @ResponseBody
    public String doExclExportt(NewLine line,HttpServletRequest req,HttpServletResponse res) {
       
        String realFilename = "..."+fileNameTime+".xls";        
        res.setContentType("application/vnd.ms-excel; charset=utf-8");
        try {
            res.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(realFilename,"UTF-8"));
        } catch (UnsupportedEncodingException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        res.setCharacterEncoding("utf-8");
           
        PoiUtil<TimeLineAmountExport> ex = new PoiUtil<TimeLineAmountExport>();
        String[] headers = { "排名", "线路名称", "运输类型", "","",""... };
        
        try {
            OutputStream out=res.getOutputStream();
            //OutputStream out = new FileOutputStream("E://singleAmount.xls");
            ex.exportExcel(headers, listResult, out);
            out.flush();
            out.close();
            System.out.println("excel导出成功！");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

前台访问：(这种方式是在当前窗口下弹出下载提示，其他两种注释的方式，效果不好)

function doExclExport() {
    var result = doCheck();
    if("back"==result){
        return;
    }
    if($("#endTime").val()==getEndDate()){
        return;
    }

    location.href="/singleAmount/doExclExport?" + 'time=' + $("#endTime").val() + '&type='
        + $("#mySelect").val() + "&ationType=" + $("#typeAtion").val()
        + "&startSite=" + $("#startSite").val() + "&endSite="
        + $("#endSite").val();  

//    window.open("/singleAmount/doExclExport?" + 'time=' + $("#endTime").val() + '&type='
//    + $("#mySelect").val() + "&ationType=" + $("#typeAtion").val()
//    + "&startSite=" + $("#startSite").val() + "&endSite="
//    + $("#endSite").val());

//    var form=$("<form action='/singleAmount/doExclExport' target='_blank' ></form>");
//    form.append('<input type="text"  name="time" value="'+$("#endTime").val()+'"/>');
//    form.append('<input type="text"  name="type" value="'+$("#mySelect").val()+'"/>');
//    form.append('<input type="text"  name="ationType" value="'+$("#typeAtion").val()+'"/>');
//    form.append('<input type="text"  name="startSite" value="'+$("#startSite").val()+'"/>');
//    form.append('<input type="text"  name="endSite" value="'+$("#endSite").val()+'"/>');
//    form.submit();
}