使用Apache-poi导出Excel文件

步骤

1.从数据库读取数据

(1)首先在mapper层中将数据查出来

//mapper接口
 List<Activity> getAllActivity();

//mapper.xml中实现查询数据
 <select id="getAllActivity" resultMap="BaseResultMap">
        select a.id,
               u1.name as owner,
               a.name,
               a.start_date,
               a.end_date,
               a.cost,
               a.description,
               a.create_time,
               u2.name as create_by,
               a.edit_time,
               u3.name as edit_by
        from tbl_activity a
                 join tbl_user u1 on a.owner = u1.id
                 join tbl_user u2 on a.create_by = u2.id
                 left join tbl_user u3 on a.edit_by = u3.id
    </select>

2.将数据插入到Excel对象

(1)主要在service层实现HSSFWorkbook对象的封装，将数据库查询出的数据插入的Excel对象中；

		List<Activity> activityList = activityMapper.getAllActivity();
        //第二步，创建Excel对象，将数据全部插入到Excel表格中
        //(1)首先创建一个HSSWorkbook对象，对应一个excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //(2)使用wb创建HSSFSheet对象，对应wb文件中的一页
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("市场活动列表");
        //(3)然后创建Excel中的一行
        HSSFRow row = hssfSheet.createRow(0);
        //(4)从行中插入数据(第一行就是放一些标题,后面的行就插入数据)
        HSSFCell cell = row.createCell(0);//一行的第一列
        cell.setCellValue("ID号");//列中的属性
        cell = row.createCell(1);
        cell.setCellValue("所有者");
        cell = row.createCell(2);
        cell.setCellValue("名称");
        cell = row.createCell(3);
        cell.setCellValue("开始日期");
        cell = row.createCell(4);
        cell.setCellValue("结束日期");
        cell = row.createCell(5);
        cell.setCellValue("花费");
        cell = row.createCell(6);
        cell.setCellValue("描述");
        cell = row.createCell(7);
        cell.setCellValue("修改时间");
        cell = row.createCell(8);
        cell.setCellValue("修改人");
        for (int i = 0; i < activityList.size(); i++) {
            row = hssfSheet.createRow(i + 1);
            cell = row.createCell(0);//一行的第一列
            cell.setCellValue(activityList.get(i).getId());
            cell = row.createCell(1);
            cell.setCellValue(activityList.get(i).getOwner());
            cell = row.createCell(2);
            cell.setCellValue(activityList.get(i).getName());
            cell = row.createCell(3);
            cell.setCellValue(activityList.get(i).getStartDate());
            cell = row.createCell(4);
            cell.setCellValue(activityList.get(i).getEndDate());
            cell = row.createCell(5);
            cell.setCellValue(activityList.get(i).getCost());
            cell = row.createCell(6);
            cell.setCellValue(activityList.get(i).getDescription());
            cell = row.createCell(7);
            cell.setCellValue(activityList.get(i).getEditTime());
            cell = row.createCell(8);
            cell.setCellValue(activityList.get(i).getEditBy());
        }

3.导出Excel文件

(1)然后将Excel对象写入到文件(接上代码)

//第三步，将Excel表格写入到服务器的.xsl文件中(传入File对象或者OutPutStream输出流)
        File file = new File("E:\\codeworkspace\\idea\\ssm\\file\\activityList.xls");
        if (!(file.exists())) {
            file.createNewFile();
        }
        hssfWorkbook.write(file);
        //不要忘记关闭资源
        hssfWorkbook.close();

4.浏览器进行下载

(1)传入HttpServletResponse对象在Controller层传入对象，就可以将导出的文件下载到浏览器(这里注意要设置response的请求头，不然就获取不到Excel文件，名字是错的；还要设置编码格式防止乱码；)

 //第四步，将.xsl文件导出到respones的输出流，下载到浏览器
        //(1)设置响应类型
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.addHeader("Content-Disposition","attachment;filename=activityList.xls");
        //(2)获取输出流
        OutputStream out = response.getOutputStream();

        FileInputStream is = new FileInputStream("E:\\codeworkspace\\idea\\ssm\\file\\activityList.xls");
        byte[] buff = new byte[256];
        int len = 0;
        while ((len = is.read(buff)) != -1) {
            out.write(buff, 0, len);
        }
        //关闭资源
        is.close();
        out.flush();

优化

如果仅仅只是想直接导出文件到浏览器下载，而不用导出到某个目录，就可以跳过
写入文件磁盘的步骤，直接将Excel文件写入到浏览器response的输出流；

		response.setContentType("application/octet-stream;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=activityList.xls");
        //(2)获取输出流
        OutputStream out = response.getOutputStream();
        hssfWorkbook.write(out);
        hssfWorkbook.close();
        //关闭资源
        out.flush();

完整代码

public void exportAllActivity(HttpServletResponse response) throws IOException {
        //第一步：数据库查询出所有的市场活动记录
        List<Activity> activityList = activityMapper.getAllActivity();
        //第二步，创建Excel对象，将数据全部插入到Excel表格中
        //(1)首先创建一个HSSWorkbook对象，对应一个excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //(2)使用wb创建HSSFSheet对象，对应wb文件中的一页
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("市场活动列表");
        //(3)然后创建Excel中的一行
        HSSFRow row = hssfSheet.createRow(0);
        //(4)从行中插入数据(第一行就是放一些标题,后面的行就插入数据)
        HSSFCell cell = row.createCell(0);//一行的第一列
        cell.setCellValue("ID号");//列中的属性
        cell = row.createCell(1);
        cell.setCellValue("所有者");
        cell = row.createCell(2);
        cell.setCellValue("名称");
        cell = row.createCell(3);
        cell.setCellValue("开始日期");
        cell = row.createCell(4);
        cell.setCellValue("结束日期");
        cell = row.createCell(5);
        cell.setCellValue("花费");
        cell = row.createCell(6);
        cell.setCellValue("描述");
        cell = row.createCell(7);
        cell.setCellValue("修改时间");
        cell = row.createCell(8);
        cell.setCellValue("修改人");
        for (int i = 0; i < activityList.size(); i++) {
            row = hssfSheet.createRow(i + 1);
            cell = row.createCell(0);//一行的第一列
            cell.setCellValue(activityList.get(i).getId());
            cell = row.createCell(1);
            cell.setCellValue(activityList.get(i).getOwner());
            cell = row.createCell(2);
            cell.setCellValue(activityList.get(i).getName());
            cell = row.createCell(3);
            cell.setCellValue(activityList.get(i).getStartDate());
            cell = row.createCell(4);
            cell.setCellValue(activityList.get(i).getEndDate());
            cell = row.createCell(5);
            cell.setCellValue(activityList.get(i).getCost());
            cell = row.createCell(6);
            cell.setCellValue(activityList.get(i).getDescription());
            cell = row.createCell(7);
            cell.setCellValue(activityList.get(i).getEditTime());
            cell = row.createCell(8);
            cell.setCellValue(activityList.get(i).getEditBy());
        }
        //第四步，将.xsl文件导出到respones的输出流，下载到浏览器
        //(1)设置响应类型
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=activityList.xls");
        //(2)获取输出流
        OutputStream out = response.getOutputStream();
        hssfWorkbook.write(out);
        hssfWorkbook.close();
        //关闭资源
        out.flush();
    }