jxl，poi导出Excel

excel导出，参考一下资料

http://blog.youkuaiyun.com/houxuehan/article/details/50960259

http://blog.youkuaiyun.com/yunsyz/article/details/42561107

在使用poi3.9.jar导出excel时还要在pom.xml中引入poi-ooxml架包否则没有相关类

package com.controller;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

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 org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bean.MyExcel;
import com.bean.Student;
import com.mapper.MyExcelMapper;


import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

@Controller
public class MyController {
@Autowired
private MyExcelMapper mem;
public static int DEFAULT_COLOUMN_WIDTH = 17;

@RequestMapping("getall")
//@ResponseBody
public ModelAndView getall(){
    ModelAndView mav=new ModelAndView();
    List<MyExcel> list=mem.getall();
    mav.addObject("excels",list);
    mav.addObject("name","jack");
    mav.setViewName("index");
    for(MyExcel s:list){
        System.out.println(s);
    }
    return mav;
}

@RequestMapping("exceloutbyjxl")
public void exceloutbyjxl(HttpServletResponse os){
    //使用jxl架包进行导出excel
    WritableWorkbook bWorkbook = null;
 try {
        os.reset();
    //设置文件标题及格式
    os.setHeader("Content-disposition","attachment;filename=
    "+new String("我的Excel".getBytes("GB2312"),"iso8859_1")+".xls");
    //os.setContentType("MSEXCEL");
    // 创建Excel对象
    bWorkbook = Workbook.createWorkbook(os.getOutputStream());
    // 通过Excel对象创建一个选项卡对象
    WritableSheet sheet = bWorkbook.createSheet("我的文档", 0);
    //获取要输出的数据
    List<MyExcel> arrayList=mem.getall();
    //使用循环将数据读出
    String[] str=new String[]{"序号","帐号","密码","时间"};
    for(int i=0;i<str.length;i++){
        Label label=new Label(i,0,str[i]);
        sheet.addCell(label);
    }
    for (int i =0; i <arrayList.size(); i++) {
         MyExcel book=arrayList.get(i);
         //label是单元格   数字表示的是列号    i表示行号
         Label label=new Label(0,i+1,String.valueOf(book.getEid()));
         Label label1=new Label(1,i+1,String.valueOf(book.getEname()));
         Label label2=new Label(2,i+1,String.valueOf(book.getPswd()));
         Label label3=new Label(3,i+1,String.valueOf(book.getDate()));
         sheet.addCell(label);
         sheet.addCell(label1);
         sheet.addCell(label2);
         sheet.addCell(label3);
    }
        // 创建一个单元格对象，第一个为列，第二个为行，第三个为值
        //Label label = new Label(0, 2, "test");
        // 将创建好的单元格放入选项卡中
        //sheet.addCell(label);
        // 写入目标路径
        bWorkbook.write();
        bWorkbook.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
//title是sheet里面第一行的标题，headMap是第二行的字段类型，jsonArray是数据，colWidth列宽
public static void exceloutbypoi(String title,Map<String, String> headMap,JSONArray jsonArray,int colWidth, OutputStream out){
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);     
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;
        //至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> 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 < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
        //excel最多显示6553行，也就是说如果行数是第一行或者最后一行生成标题和字段类型
            if(rowIndex == 65535 || rowIndex == 0){
            //如果数据超过了，则在第二页显示
                if ( rowIndex != 0 ) sheet = workbook.createSheet();
                //设置标题
                SXSSFRow titleRow = sheet.createRow(0);
                titleRow.createCell(0).setCellValue(title);
                //sheet.addMergedRegion（new CellRangeAddress(起始行号，终止行号， 起始列        
 //号，终止列号)）用来合并单元格
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                //设置字段类型
                SXSSFRow headerRow = sheet.createRow(1);
                for(int i=0;i<headers.length;i++)
                {
                    headerRow.createCell(i).setCellValue(headers[i]);
                }
                //数据内容从 rowIndex=2开始
                rowIndex = 2;
            }
            //写完标题及字段类型之后把输出的对象转换为JSONObject对象然后输出到Excel
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++)
            {
                SXSSFCell newCell = dataRow.createCell(i);
                Object o =  jo.get(properties[i]);
                //1,2步骤完成即可把这个对象中的每个属相放入到excel中
                String cellValue = o.toString();
                newCell.setCellValue(cellValue);
            }
            rowIndex++;
        }
        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
}
public static void main(String[] args) throws IOException {
       JSONArray ja = new JSONArray();
       for(int i=0;i<100000;i++){
           Student s = new Student();
           s.setName("POI"+i);
           s.setAge(i);
           s.setBirthday(new Date());
           s.setHeight(i);
           s.setWeight(i);
           ja.add(s);
       }
       Map<String,String> headMap = new LinkedHashMap<String,String>();
       headMap.put("name","姓名");
       headMap.put("age","年龄");
       headMap.put("birthday","生日");
       headMap.put("height","身高");
       headMap.put("weight","体重");
       String title = "测试";
       OutputStream outXlsx = new FileOutputStream("d://myexcel.xlsx");
       System.out.println("正在导出xlsx....");
       Date d2 = new Date();
       MyController.exceloutbypoi(title,headMap,ja,0,outXlsx);
       System.out.println((new Date().getTime()-d2.getTime())+"ms");
       outXlsx.close();
   }
}