快捷導(dǎo)航

springboot導(dǎo)出excel多個(gè)sheet導(dǎo)出的實(shí)現(xiàn)

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

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

springboot導(dǎo)出excel多個(gè)sheet導(dǎo)出

1.pom.xml

 <!--文件導(dǎo)出-->
        <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);
        }
        //導(dǎo)出
        ExcelExportUtil.exportExcel(response, dataList,"用戶信息.xls",9L);
    }

3、導(dǎo)出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 {

    /**
     * 導(dǎo)出excel 多個(gè)sheet
     * @param response  響應(yīng)流
     * @param dataList  導(dǎo)出數(shù)據(jù)
     * @param fileName  文件名
     * @param sheetSize 每個(gè)sheet容量
     */
    public static void exportExcel(HttpServletResponse response, List<User> dataList, String fileName, Long sheetSize) {
        //創(chuàng)建HSSFWorkbook對(duì)象(excel的文檔對(duì)象)
        HSSFWorkbook workbook = new HSSFWorkbook();
        try (ServletOutputStream out = response.getOutputStream()) {
            //計(jì)算要分幾個(gè)sheet
            int sheetNum = dataList.size() / sheetSize.intValue();
            if (dataList.size() % sheetSize.intValue() != 0) {
                sheetNum += 1;
            }
            //依次對(duì)每個(gè)sheet頁(yè)面進(jìn)行操作
            for (int i = 0; i < sheetNum; i++) {
                int num = 1;
                HSSFSheet sheet = workbook.createSheet("sheet" + (i+1));
                HSSFRow row = sheet.createRow(0);
                //創(chuàng)建單元格并設(shè)置單元格內(nèi)容
                row.createCell(0).setCellValue("學(xué)號(hào)");
                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);
                    //每個(gè)頁(yè)面把取夠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();
        }
    }
}