SpringMVC+Mybatis+POI 导出Excel

最新推荐文章于 2022-05-14 21:41:28 发布

原创最新推荐文章于 2022-05-14 21:41:28 发布 · 550 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#spring mvc #mybatis #poi #excel

本文介绍了一种利用Java POI库实现从数据库查询数据并导出为Excel文件的方法。文章详细展示了如何创建Excel文件、设置样式、填充数据以及通过HTTP响应下载Excel文件的过程。

在你的jar包没有问题的情况下：
1.首先工具类：

package com.ambow.invoic.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.ambow.invoic.indepot.entity.Depot;
public class ExcelUtil {
    ExcelUtil(){}
    /**
     * 导出excel
     * @throws Exception 
     */
    public static void exportExcel(List<Depot> depots,HttpServletResponse response,String title) throws Exception{
        HSSFWorkbook workbook = new HSSFWorkbook();
        //sheet
        HSSFSheet sheet = workbook.createSheet("用户数据");
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell1 = row1.createCell(0);
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, 5);
        //样式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short)15);
        style.setFont(font);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        sheet.addMergedRegion(range);
        sheet.setDefaultColumnWidth(10);
        cell1.setCellStyle(style);
        cell1.setCellValue("用户列表");
        //标题行
        HSSFRow headRow = sheet.createRow(1);//头行
        headRow.createCell(0).setCellValue("姓名");
        headRow.createCell(1).setCellValue("姓别");
        headRow.createCell(2).setCellValue("年龄");
        headRow.createCell(3).setCellValue("押金");
        headRow.createCell(4).setCellValue("读者编号");
        headRow.createCell(5).setCellValue("最大借阅量");

        //向excel写数据
        for(Depot depot:depots){
        //每个分区创建一行
              HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum()+1);
              dataRow.createCell(0).setCellValue(depot.getDsName());
              dataRow.createCell(1).setCellValue(depot.getDsName());
              dataRow.createCell(2).setCellValue(depot.getDsName());
              dataRow.createCell(3).setCellValue(depot.getDsName());
              dataRow.createCell(4).setCellValue(depot.getDsName());
              dataRow.createCell(5).setCellValue(depot.getDsName());

        }
         try {
                response.reset();
                response.setContentType("application/vnd..ms-excel");
              //  response.setHeader("content-Disposition","attachment;filename="+URLEncoder.encode(title + ".xls","utf-8"));
                response.setHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes("gbk"),"ISO-8859-1"));  
                OutputStream out = response.getOutputStream();
                workbook.write(out);
                out.flush();
            } catch (IOException e) {
                e.printStackTrace();
            } 

    }
}

2.controller类中的方法：

@RequestMapping(value="/exportExcel")
    public void exportExcel(HttpServletResponse response,HttpServletRequest request){
        List<Depot> findAll = depotService.findAll();
        //获得用户使用的浏览器类型
        String title = "测试.xls";
        try {
            ExcelUtil.exportExcel(findAll, response, title);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

启动你的项目，访问上面方法里的地址即可！