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

package com.zhiyou100.kfs.dao;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.List;

import java.util.Map;

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 static void exportExcel(List<Map<String, Object>> list,String sheetname,String filepath) 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++;

              }

              //通过IO流把数据写道文件上

              FileOutputStream out=new FileOutputStream(filepath);

              workbook.write(out);

              out.close();

       }

}

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();

}

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";

       }

}

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();

       }

}