1.添加依賴

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

2.案例描述：從數(shù)據(jù)庫student1表中查詢出來的數(shù)據(jù)進(jìn)行報(bào)表導(dǎo)出(前臺頁面使用a標(biāo)簽進(jìn)行鏈接就可以，沒必要ajax)
3.報(bào)表導(dǎo)出工具類：

package com.ansheng.util;
 
import org.apache.poi.hssf.usermodel.*;
 
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
 
public class ExcelUtils {
 
    static final short borderpx = 1;
 
    /**
     * 導(dǎo)出excel表格
     * @param head
     * @param body
     * @return
     */
    public static HSSFWorkbook expExcel(List<String> head, List<List<String>> body) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell= null;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        setBorderStyle(cellStyle, borderpx);
        cellStyle.setFont(setFontStyle(workbook, "黑體", (short) 14));
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        sheet.createFreezePane(0,1,0,1);
 
        for (int i = 0; i<head.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle);
        }
 
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        setBorderStyle(cellStyle2, borderpx);
        cellStyle2.setFont(setFontStyle(workbook, "宋體", (short) 12));
        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < body.size(); i++) {
            row = sheet.createRow(i + 1);
            List<String> paramList = body.get(i);
            for (int p = 0; p < paramList.size(); p++) {
                cell = row.createCell(p);
                cell.setCellValue(paramList.get(p));
                cell.setCellStyle(cellStyle2);
            }
        }
        for (int i = 0, isize = head.size(); i < isize; i++) {
            sheet.autoSizeColumn(i);
        }
        return workbook;
    }
 
    /**
     * 文件輸出
     * @author LiuYang
     * @param workbook 填充好的workbook
     * @param path 存放的位置
     */
    public static void outFile(HSSFWorkbook workbook,String path,HttpServletResponse response) {
        SimpleDateFormat fdate=new SimpleDateFormat("yyyyMMdd-HH點(diǎn)mm分");
        path = path.substring(0, path.lastIndexOf(".")) + fdate.format(new Date()) + path.substring(path.lastIndexOf("."));
        OutputStream os=null;
        File file = null;
        try {
            file = new File(path);
            String filename = file.getName();
            os = new FileOutputStream(file);
            response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "UTF-8"));
            os= new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            workbook.write(os);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            os.flush();
            os.close();
            System.gc();
            System.out.println(file.delete());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
    /**
     * 設(shè)置字體樣式
     * @author LiuYang
     * @param workbook 工作簿
     * @param name 字體類型
     * @param height 字體大小
     * @return HSSFFont
     */
    private static HSSFFont setFontStyle(HSSFWorkbook workbook, String name, short height) {
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(height);
        font.setFontName(name);
        return font;
    }
 
    /**
     * 設(shè)置單元格樣式
     * @author LiuYang
     * @param cellStyle 工作簿
     * @param border border樣式
     */
    private static void setBorderStyle(HSSFCellStyle cellStyle, short border) {
        cellStyle.setBorderBottom(border); // 下邊框
        cellStyle.setBorderLeft(border);// 左邊框
        cellStyle.setBorderTop(border);// 上邊框
        cellStyle.setBorderRight(border);// 右邊框
    }
}

4.controller代碼：

package com.ansheng.controller;
 
import com.ansheng.entity.Student1;
import com.ansheng.service.Excel2Service;
import com.ansheng.util.ExcelUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
 
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
 
@Controller
public class Excel2Controller {
   @Autowired
   Excel2Service excel2Service;
    @RequestMapping("/excel2")
    public void testExcel2(HttpServletResponse response){
        //查詢出需要導(dǎo)出的數(shù)據(jù)
        List<Student1> lisx=excel2Service.queryFromTables();
        //創(chuàng)建報(bào)表數(shù)據(jù)頭
        List<String> head = new ArrayList<>();
        head.add("學(xué)號");
        head.add("姓名");
        head.add("性別");
        head.add("年齡");
        head.add("學(xué)校");
        head.add("專業(yè)");
        head.add("個人描述");
        //創(chuàng)建報(bào)表體
        List<List<String>> body = new ArrayList<>();
        for (Student1 stu : lisx) {
            List<String> bodyValue = new ArrayList<>();
            bodyValue.add(String.valueOf(stu.getId()));
            bodyValue.add(stu.getName());
            if("0".equals(stu.getSex())){
                bodyValue.add("女");
            }else{
                bodyValue.add("男");
            }
            bodyValue.add(String.valueOf(stu.getAge()));
            bodyValue.add(stu.getSchool());
            bodyValue.add(stu.getCollege());
            bodyValue.add(stu.getDescr());
            //將數(shù)據(jù)添加到報(bào)表體中
            body.add(bodyValue);
        }
        String fileName = "學(xué)生信息統(tǒng)計(jì).xls";
        HSSFWorkbook excel = ExcelUtils.expExcel(head,body);
        ExcelUtils.outFile(excel,"./"+fileName,response);
    }
}

注意：只有一個查詢方法，所有service和mapper就不寫代碼了
5.數(shù)據(jù)庫中數(shù)據(jù)截圖如下：

image.png