ExportExcel（用poi导出Excel文件：用List<Map<String,Object>>）

1.导入jar包（maven）

              <dependency>

                            <groupId>org.apache.poi</groupId>

                            <artifactId>poi</artifactId>

                            <version>3.10.1</version>

                     </dependency>

 

2.ExportExcel工具类

 

package com.zhiyou100.kfs.util;

 

import java.io.IOException;

import java.io.OutputStream;

import java.io.UnsupportedEncodingException;

import java.util.List;

import java.util.Map;

 

import javax.servlet.http.HttpServletResponse;

 

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

 

/**

 * 用poi导出Excel文件的工具类

 *

 * @author KFS

 *

 */

public class ExportExcel {

       /**

        *   用poi导出Excel文件的静态方法

        * @param list                    数据：只能是List<Map<String, Object>>类型

        * @param sheetname        Excel的sheet名字

        * @param filepath             保存文件的地址

        * @throws IOException

        */

       public void exportExcel(List<Map<String, Object>> list,String sheetname,String fileName,HttpServletResponse response) throws IOException {

              //新建工作簿

              HSSFWorkbook workbook=new HSSFWorkbook();

              //创建Excel的sheet

              HSSFSheet sheet=workbook.createSheet(sheetname);

             

              //从list任意一个Map对象里获取标题（字段名或属性名）放到sheet的第一行上，若第一条记录某字段值没有，则会没有该字段

              Map<String, Object> map=list.get(0);

              int num=0;

              HSSFRow first=sheet.createRow(0);//创建sheet的第一行

              for(String key:map.keySet()) {

                     first.createCell(num).setCellValue(key);//创建num+1行并在第num+1列上赋值（字段名）

                     num++;

              }

             

              //从list取第一行到最后一行的内容并放到对应的Excel里，若记录里某字段值没有会有问题

              int rownum=1;//行数

              for(Map<String, Object> data:list) {

                     HSSFRow row=sheet.createRow(rownum);//创建sheet的第rownum+1行

                     int n=0;//列数

                     for(String key:data.keySet()) {

                            row.createCell(n).setCellValue(data.get(key).toString());//创建n+1行并在第n+1列上赋值

                            n++;

                     }

                     rownum++;

              }

             

              //1.通过IO流把数据写到文件上：只能写到服务器端

              /*FileOutputStream out=new FileOutputStream(fileName);

              workbook.write(out);

              out.close();*/

             

              //2.响应到客户端：可以下载到客户端（两个选一个）

              try {

                     this.setResponseHeader(response, fileName);

                     OutputStream os = response.getOutputStream();

                     workbook.write(os);

                     os.flush();

                     os.close();

              } catch (Exception e) {

                     e.printStackTrace();

              }

             

       }

 

       // 发送响应流方法

       public void setResponseHeader(HttpServletResponse response, String fileName) {

              try {

                     try {

                            fileName = new String(fileName.getBytes(), "ISO8859-1");

                     } catch (UnsupportedEncodingException e) {

                            e.printStackTrace();

                     }

                     response.setContentType("application/octet-stream;charset=ISO8859-1");

                     response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

                     response.addHeader("Pargam", "no-cache");

                     response.addHeader("Cache-Control", "no-cache");

              } catch (Exception ex) {

                     ex.printStackTrace();

              }

       }

      

}

3.mapper.xml和对应的接口

 

1. mapper.xml：

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.zhiyou100.kfs.dao.UserMapper">

      

       <sql id="baseSql">

       id,user_name username,password,name,age,sex,birthday,created,updated

       </sql>

      

       <select id="selectListMap" resultType="java.util.LinkedHashMap">

              select

              <include refid="baseSql"/>

              from tb_user

       </select>

 

</mapper>

1. 对应的接口：

package com.zhiyou100.kfs.dao;

 

import java.util.List;

import java.util.Map;

 

public interface UserMapper{

       /**

        *   ExportExcel：导出Excel

        * @return

        */

       List<Map<String, Object>> selectListMap();

}

 

 

4.service层

package com.zhiyou100.kfs.service;

 

import java.util.List;

import java.util.Map;

 

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

 

import com.github.pagehelper.PageHelper;

import com.github.pagehelper.PageInfo;

import com.zhiyou100.kfs.dao.UserMapper;

 

@Service("userService")

public class UserServiceImp {

       @Autowired

       private UserMapper userMapper;

      

       public List<Map<String, Object>> selectListMap(Integer pageNum,Integer pageSize){

              PageHelper.startPage(pageNum, pageSize);

              List<Map<String, Object>> list = userMapper.selectListMap();

              PageInfo<Map<String, Object>> pageinfo=new PageInfo<>(list);

              return pageinfo.getList();

       }

}

 

5.测试代码：controller层

package com.zhiyou100.kfs.controller;

 

import java.io.IOException;

import java.util.List;

import java.util.Map;

 

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import com.zhiyou100.kfs.service.UserServiceImp;

import com.zhiyou100.kfs.util.ExportExcel;

 

@Controller

@RequestMapping("user")

public class UserController {

       @Autowired

       private UserServiceImp userServiceImp;

      

       @RequestMapping("exportExcel")

       public String exportExcel(Integer page,Integer rows) throws IOException {

              List<Map<String, Object>> list = userServiceImp.selectListMap(page,rows);

              String sheetname="用户管理";

              String filepath="d://用户管理：第"+page+"页，每页"+rows+"记录.xls";

              ExportExcel.exportExcel(list, sheetname, filepath);

              return "redirect:/rest/user/toUsers";

       }

}