使用JAVA 导出(数据库表)到excel功能

此功能基于maven工程

第一步:使用maven导入所需的包(这个包名很好听，poi~,我记得有个番的某个妹子的口头禅叫poi~):

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.14</version>
    </dependency>

第二步：写一个工具类

package com.turntable.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ViewExcel extends AbstractExcelView {

    private String[] titles;

    //传入指定的标题头
    public ViewExcel(String[] titles) {
        this.titles=titles;
    }

    @Override
    protected void buildExcelDocument(Map<String, Object> model,
                                      HSSFWorkbook workbook, HttpServletRequest request,
                                      HttpServletResponse response) throws Exception {
        //获取数据
        List<Map<String, String>> list = (List<Map<String, String>>) model.get("excelList");
        //在workbook添加一个sheet
        HSSFSheet sheet = workbook.createSheet();
        sheet.setDefaultColumnWidth(15);
        HSSFCell cell=null;
        //遍历标题
        for (int i = 0; i < titles.length; i++) {
            //获取位置
            cell = getCell(sheet, 0, i);
            setText(cell, titles[i]);
        }
        //数据写出
        for (int i = 0; i < list.size(); i++) {
            //获取每一个map
            Map<String, String> map=list.get(i);
            //一个map一行数据
            HSSFRow row = sheet.createRow(i+1);
            for (int j = 0; j < titles.length; j++) {
                //遍历标题，把key与标题匹配
                String title=titles[j];
                //判断该内容存在mapzhong
                if(map.containsKey(title)){
                    row.createCell(j).setCellValue(map.get(title));
                }
            }
        }
        //设置下载时客户端Excel的名称
        String filename = new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        OutputStream ouputStream = response.getOutputStream();
        workbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
    }

}

第三步:映射xml，服务层写好

XML:

<select id="listwinning" resultMap="BaseResultMap">

  select * from winning
</select>

服务实现类:

public List<Map<String, String>> selectAllAssetInlibraryInfo(){
    List<winning> list = mapper.listwinning();
    List<Map<String, String>> mapList=new ArrayList<Map<String,String>>();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
    for(int i =0 ;i<list.size();i++){
        Map<String, String> map=new HashMap<String, String>();
        map.put("编号",list.get(i).getId()+"");
        map.put("手机号码",list.get(i).getMobile()+"");
        map.put("奖品名称",list.get(i).getWinningName()+"");
        if(list.get(i).getCreateTime()==null){
            map.put("获奖日期","");
        }else {
            map.put("获奖日期", sdf.format(list.get(i).getCreateTime()) + "");
        }
        mapList.add(map);
    }
    return mapList;
}

第四步,控制层:

@RequestMapping("/Winner/export")
public ModelAndView export(ModelMap map) throws Exception{
    List<Map<String ,String >> list = winningService.selectAllAssetInlibraryInfo();
    String[] titles={"编号","手机号码","奖品名称","获奖日期"};
    ViewExcel excel=new ViewExcel(titles);
    map.put("excelList", list);
    return new ModelAndView(excel,map);
}

大功告成，直接访问链接就能下载。这里要注意一个问题，如果查询出来的某个值有null值，会导致500错误，所以需要做一个判断是否为空。