SpringBoot使用poi操作excel（临时）

大佬榜：
https://www.bilibili.com/read/cv6235723
https://my.oschina.net/gentlelions/blog/1920839

 
package com.example.demo.controller;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
 
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
 
/**
 * @author leizi
 * @create 2020-12-28 22:34
 */
@Controller
@RequestMapping("/poi")
public class DemoPOI {
    // 此处@RequEstMappint必须声明produces = MediaType.APPLICATION_OCTET_STREAM_VALUE
    @RequestMapping(value = "/downLoad", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    public void downLoadXlsWright(HttpServletResponse response) throws UnsupportedEncodingException {
        // 设置xlsx最大长度为1000行
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);
        // 设置文件后缀
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String fn = dateFormat.format(new Date()).toString() + ".xlsx";
 
        // 设置表格名称
        Sheet sheet = wb.createSheet("sheet");
        // 设置默认宽度为30个字节
        sheet.setDefaultColumnWidth(30);
        // 设置表头
        Row row = sheet.createRow(0);
        for (int i = 0; i < 5; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue("Test:" + i);
        }
 
        // 设置响应头
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
        response.setHeader("Content-disposition","attachment;filename="+ URLEncoder.encode(fn,"UTF-8"));
        OutputStream os = null;
 
        try {
            os = response.getOutputStream();
            wb.write(os);
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                wb.dispose();
            }
            if (null != os) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
 
============================================================================================
 
    private void jdbcex(boolean isClose) throws Exception {
 
        //输出文件
        String xlsFile = "F:\\Downloads\\test_export.xlsx";
        //内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放。
        //关键语句
        Workbook wb = new SXSSFWorkbook(100);
        //工作表对象
        Sheet sheet = null;
        //行对象
        Row nRow;
        //列对象
        Cell nCell;
 
        //使用jdbc链接数据库
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8";
        String user = "root";
        String password = "root";
 
        //获取数据库连接
        Connection conn = DriverManager.getConnection(url, user,password);
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        //100万测试数据
        String sql = "select * from hpa_normal_tissue limit 1000000";
        ResultSet rs = stmt.executeQuery(sql);
 
        ResultSetMetaData rsmd = rs.getMetaData();
        //开始时间
        long  startTime = System.currentTimeMillis();
        System.out.println("strat execute time: " + startTime);
 
        //总行号
        int rowNo = 0;
        //页行号
        int pageRowNo = 0;
 
        while(rs.next()) {
            //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
            if(rowNo%300000==0){
                System.out.println("Current Sheet:" + rowNo/300000);
                //建立新的sheet对象
                sheet = wb.createSheet("我的第"+(rowNo/300000)+"个工作簿");
                //动态指定当前的工作表
                sheet = wb.getSheetAt(rowNo/300000);
                //每当新建了工作表就将当前工作表的行号重置为0
                pageRowNo = 0;
            }
            rowNo++;
            //新建行对象
            nRow = sheet.createRow(pageRowNo++);
            // 打印每行，每行有6列数据   rsmd.getColumnCount()==6 --- 列属性的个数
            for(int j=0;j<rsmd.getColumnCount();j++){
                nCell = nRow.createCell(j);
                nCell.setCellValue(rs.getString(j+1));
            }
 
            if(rowNo%10000==0){
                System.out.println("row no: " + rowNo);
            }
//		Thread.sleep(1);	//休息一下，防止对CPU占用，其实影响不大
        }
 
        //处理完成时间
        long finishedTime = System.currentTimeMillis();
        System.out.println("finished execute  time: " + (finishedTime - startTime)/1000 + "m");
 
        FileOutputStream fOut = new FileOutputStream(xlsFile);
        wb.write(fOut);
        //刷新缓冲区
        fOut.flush();
        fOut.close();
 
        //写文件时间
        long stopTime = System.currentTimeMillis();
        System.out.println("write xlsx file time: " + (stopTime - startTime)/1000 + "m");
 
        if(isClose){
            this.close(rs, stmt, conn);
        }
    }
 
    //执行关闭流的操作
    private void close(ResultSet rs, Statement stmt, Connection conn ) throws SQLException{
        rs.close();
        stmt.close();
        conn.close();
    }
}