springboot導出excel多個sheet導出的實現(xiàn)

更新時間：2024年10月24日 08:51:07 作者：Carver0808

在Java開發(fā)過程中,合理配置pom.xml文件對項目的管理和構(gòu)建至關(guān)重要,通過添加依賴管理項目所需的庫,簡化了項目構(gòu)建過程,同時,掌握導出excel工具類的使用,可以有效地處理數(shù)據(jù)導出需求,提高工作效率,本文結(jié)合個人經(jīng)驗

springboot導出excel多個sheet導出

1.pom.xml

 <!--文件導出-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

2、controller

    @GetMapping("/export")
    public void export(HttpServletResponse response) {
        //創(chuàng)建模擬數(shù)據(jù)
        List<User> dataList = new ArrayList<>();
        for (int i = 1; i <= 20; i++) {
            User user = new User(i,"張三" + i, 23, "男", 173);
            dataList.add(user);
        }
        //導出
        ExcelExportUtil.exportExcel(response, dataList,"用戶信息.xls",9L);
    }

3、導出excel工具類

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

public class ExcelExportUtil {

    /**
     * 導出excel 多個sheet
     * @param response  響應流
     * @param dataList  導出數(shù)據(jù)
     * @param fileName  文件名
     * @param sheetSize 每個sheet容量
     */
    public static void exportExcel(HttpServletResponse response, List<User> dataList, String fileName, Long sheetSize) {
        //創(chuàng)建HSSFWorkbook對象(excel的文檔對象)
        HSSFWorkbook workbook = new HSSFWorkbook();
        try (ServletOutputStream out = response.getOutputStream()) {
            //計算要分幾個sheet
            int sheetNum = dataList.size() / sheetSize.intValue();
            if (dataList.size() % sheetSize.intValue() != 0) {
                sheetNum += 1;
            }
            //依次對每個sheet頁面進行操作
            for (int i = 0; i < sheetNum; i++) {
                int num = 1;
                HSSFSheet sheet = workbook.createSheet("sheet" + (i+1));
                HSSFRow row = sheet.createRow(0);
                //創(chuàng)建單元格并設置單元格內(nèi)容
                row.createCell(0).setCellValue("學號");
                row.createCell(1).setCellValue("姓名");
                row.createCell(2).setCellValue("年齡");
                row.createCell(3).setCellValue("性別");
                row.createCell(4).setCellValue("身高");
                //i * sheetSize.intValue()  記錄上次取值得位置
                for (int j = i * sheetSize.intValue(); j < dataList.size(); j++) {
                    User user = dataList.get(j);
                    //每個頁面把取夠sheetSize條數(shù)據(jù)即可
                    if(num < sheetSize + 1){
                        HSSFRow row1 = sheet.createRow(num);
                        row1.createCell(0).setCellValue(user.getNo());
                        row1.createCell(1).setCellValue(user.getName());
                        row1.createCell(2).setCellValue(user.getAge());
                        row1.createCell(3).setCellValue(user.getSex());
                        row1.createCell(4).setCellValue(user.getHeight());
                    }else{break;}
                    num++;
                }
            }
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}