SpringBoot POI导出Excel

SpringBoot导出Excel

POI操作Excel
EasyPOI操作Excel
解决导出excel中文名称乱码问题
解决导出excel内容乱码问题

其他 https://blog.csdn.net/typ1805/article/details/83279532
未更新

HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook的区别:

HSSFWorkbook一般用于Excel2003版及更早版本(扩展名为.xls)的导出。
XSSFWorkbook一般用于Excel2007版(扩展名为.xlsx)的导出。
SXSSFWorkbook一般用于大数据量的导出。

注：HSSFWorkbook和XSSFWorkbook的Excel Sheet导出条数上限(<=2003版)是65535行、256列,(>=2007版)是
1048576行,16384列,如果数据量超过了此上限,那么可以使用SXSSFWorkbook来导出。实际上上万条数据，
甚至上千条数据就可以考虑使用SXSSFWorkbook了。

HSSSFWorkbook 和SXSSFWorkbook在导出excel workbook对象在执行write方法写入数据到ServletOutputStream时 是有点区别的

HSSFWorkbook在write方法执行完的时候并没有关闭流 ，所以我们可以对这个流统计大小来获取导出文件的大小，并且最后需要我们手动关闭
而SXSSFWorkbook的write方法把流关闭了，write方法执行完之后out流就空了拿不到大小了

SXSSFWorkbook是XSSFWorkbook的Streaming版本，实现了"BigGridDemo"的策略，在导出大量数据的时候，可以避免OOM。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>

getFirstRowNum、getLastRowNum 起始都是0

接口代码

//上传Excel
@RequestMapping("/uploadDevMachine")
public Object uploadDevMachine(MultipartFile file) {
    
    int successCount = 0;//上传成功条数
    try {
        if(file == null || file.isEmpty()){
            return Rsp.end(Result.Err.ERR_NOT_EXISTS);
        }
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        if(lastRowNum < 1){
            return Rsp.end(Result.Err.ERR_NO_RECORD);
        }
        logger.info("import file name:{}, lastRowNum:{}",file.getOriginalFilename(), lastRowNum);
        for(int i = 1; i <= lastRowNum; i++)
        {
            XSSFRow row = sheet.getRow(i);
            //包装实体类
            DevMachine dm = new DevMachine();
            dm.number = row.getCell(0) == null ? "" : row.getCell(0).getStringCellValue();
            dm.name = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
            if(row.getCell(2) != null){
                dm.meterId = row.getCell(2).getStringCellValue();//计量插座Id唯一索引
            }
            if(row.getCell(3) != null){
                dm.tagId = row.getCell(3).getStringCellValue();电子标签Id唯一索引
            }
            dm.remark = row.getCell(4) == null ? "" : row.getCell(4).getStringCellValue();
            if(cu.isCustomer()){
                dm.customerId = cu.userId;//所属用户
            }
            //判断id是否为空
            XSSFCell cell = row.getCell(5);
            if(cell == null){
                dm.id = Misc.makeUuid();
                boolean add = devMachineService.addDevMachine(dm);
                if(add){
                    successCount++;
                }
            }else{
                dm.id = cell.getStringCellValue();
                if(dm.id.length() > 20){
                    boolean update = devMachineService.updateDevMachine(dm);
                    if(update){
                        successCount++;
                    }
                }
            }
        }
    }catch(Exception e){
        e.printStackTrace();
    }
    return Rsp.end(Result.Err.SUCCESS, "上传成功条数:" + successCount);
}

前端代码

<el-dialog title="导入" width="30%" :visible.sync="handleUploadVisible">
    <el-upload class="upload-demo" drag style="display: inline-block;" action="#" :file-list="fileList" :on-change="handleChange" :limit="1" :http-request="dragSubmit" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel" :auto-upload="false">
      <i class="el-icon-upload"></i>
      <div class="el-upload__text">将Excel文件拖到此处，或<em>点击上传</em></div>
      <div class="el-upload__tip" slot="tip">提示：文件格式为.xlsx</div>
    </el-upload>
        <div class="dialog-footer" slot="footer">
          <el-button @click="handleUploadVisible=false">取消</el-button>
          <el-button @click="exportDevMachine()">下载模板</el-button>
          <el-button @click="doUploadFile()" slot="trigger">确定</el-button>
        </div>
</el-dialog>

methods

handleUpload(){
            this.handleUploadVisible = true
        },
        handleChange(file, fileList){
            this.fileTemp = file.raw
            //console.log(file)
        },
        handleRemove(file, fileList){
            this.fileTemp = null;
        },
        dragSubmit(param){
            console.log('---dragSubmit param---')
            this.fileTemp = param.file;
            //fetchPost("uploadDevMachine", formData).then((rsp) => {})//自动上传
        },
        upload() {
            //console.log("do upload refs submit")
            this.$refs.upload.submit();
        },
        /* 手动上传excel文件 */
        doUploadFile(){
            if(this.fileTemp == null){
                this.$message.success("未选择上传的excel文件！")
                return
            }
            //console.log(this.fileList[0].raw)
            //console.log(this.fileTemp)
            //
            const formData = new FormData();
            formData.append("file", this.fileTemp);
            fetchPost("uploadData", formData).then((rsp) => {
                if (rsp.code == 0) {
                    this.$message.success(rsp.msg)
                    return
                }
                this.$message.error(rsp.msg)
            })
        },

参考地址
https://www.cnblogs.com/linjiqin/p/10975761.html

https://www.jianshu.com/p/3a89e19a1bc3

https://www.cnblogs.com/toutou/p/9857963.html

https://blog.csdn.net/typ1805/article/details/83279532

POI之SXSSFWorkbook大量数据导出至excel
https://blog.csdn.net/k_520_w/article/details/84404652