Apache poi 导入导出Excel文件

小火柴127

已于 2023-03-17 15:05:58 修改

阅读量584

点赞数

分类专栏：后端文章标签： layui poi

于 2022-05-31 16:01:32 首次发布

本文链接：https://blog.csdn.net/Java721/article/details/125066163

版权

后端专栏收录该内容

27 篇文章 3 订阅

订阅专栏

引入依赖

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

上传Excel

上传文件Dom

  <div class="layui-body" style="margin: 1%">
      <div class="layui-upload-drag" id="fileUpload">
          <i class="layui-icon">&#xe67c;</i>
          <p>点击上传，或将文件拖拽到此处</p>
      </div>
  </div>

 layui.use(['upload', 'element','table'], function() {
        var $ = layui.jquery,upload = layui.upload,table = layui.table;
            upload.render({
            elem: '#fileUpload',
            url: '/file/import',
            accept:'file',
            done: function(res){
                console.log(res.msg);
            }
        });
}

后端代码
实体类，数据库需设置主键id自增

@Table(name = "city")
@ToString
public class City {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "value")
    private Integer value;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setCity(String name) {
        this.name = name;
    }

    public Integer getValue() {
        return value;
    }

    public void setValue(Integer value) {
        this.value = value;
    }
}

@RequestMapping("/import")
@ResponseBody
public ResponseResult importExcel(@RequestParam("file")MultipartFile file) throws Exception{
ResponseResult res=new ResponseResult();
// 1.文件不能为空
if (file.isEmpty()){
    res.setMessage("文件为空，不能上传");
}
// 2.POI获取Excel解析数据
HSSFWorkbook wb=new HSSFWorkbook(file.getInputStream());
HSSFSheet sheet=wb.getSheetAt(0);

// 3.定义一个集合，接收文件中的数据
List<City> dataList=new ArrayList();

HSSFRow row=null;


// 4.解析数据，装到集合里面
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
    //定义实体
    City data=new City();

    //每一行的数据
    row=sheet.getRow(i);

    //解析数据
    data.setCity(row.getCell(0).getStringCellValue());
    data.setValue((int)row.getCell(1).getNumericCellValue());
    dataList.add(data);
     //3.插入数据库
    cityService.addCity(dataList);
}
res.setCode("200");
res.setMessage("上传成功");
res.setObj(dataList);
return res;
}

导出Excel

导出Dom

  <button type="button" class="layui-btn layui-btn-sm latui-btn-radius" lay-submit="" lay-filter="doExport">
      <i class="layui-icon layui-icon-file">导出Excel</i>
  </button>

        form.on('submit(doExport)',function () {
            window.location.href="/export"
        })

Java代码


    @RequestMapping("/export")
    @ResponseBody
    public void exportExcel(HttpServletResponse response) throws Exception {
        //1.查询数据库，
        List<City> datalist = cityService.findAll();
        //2.建立Excel对象,封装数据
        response.setCharacterEncoding("UTF-8");
        //2.1 创建Excel对象
        HSSFWorkbook wb=new HSSFWorkbook();
        //2.2 创建sheet对象
        HSSFSheet sheet = wb.createSheet("中国疫情数据Sheet1");
        //2.3 创建表头
        HSSFRow hssfRow=sheet.createRow(0);
        hssfRow.createCell(0).setCellValue("城市名称");
        hssfRow.createCell(1).setCellValue("确诊数量");
        //3.遍历数据
        for(City data:datalist){
            HSSFRow dataRow=sheet.createRow(sheet.getLastRowNum()+1);
            dataRow.createCell(0).setCellValue(data.getName());
            dataRow.createCell(1).setCellValue(data.getValue());
        }
        //4.建立输出流，输出文件到浏览器
        OutputStream os=null;
        //4.1 设置Excel文件名称
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setHeader( "Content-Disposition", "attachment;filename=" + new String( "疫情数据表".getBytes("gb2312"), "ISO8859-1" ));
        //4.2 输出文件
        os= response.getOutputStream();
        wb.write(os);
        os.flush();
        //5.关闭输出流
        os.close();
    }

方式二

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Excel批量处理</title>
    <script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.js"></script>
    <script src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.core.min.js"></script>
</head>
<body>
<input type="file" id="export">
<textarea id="area"></textarea>
<script>
    //给input标签绑定change事件，一上传选中的.xls文件就会触发该函数
    $('#export').change(function(e) {
        var files = e.target.files;
        var fileReader = new FileReader();
        fileReader.onload = function(ev) {
            try {
                var data = ev.target.result
                var workbook = XLSX.read(data, {
                    type: 'binary'
                }) // 以二进制流方式读取得到整份excel表格对象
                var persons = []; // 存储获取到的数据
            } catch (e) {
                console.log('文件类型不正确');
                return;
            }
            // 表格的表格范围，可用于判断表头是否数量是否正确
            var fromTo = '';
            // 遍历每张表读取
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    fromTo = workbook.Sheets[sheet]['!ref'];
                    console.log(fromTo);
                    persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
                    //break; // 如果只取第一张表，就取消注释这行
                }
            }
            //在控制台打印出来表格中的数据
            console.log(persons);
            $("#area").val(JSON.stringify(persons));
        };
        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
    });
</script>
</body>
</html>

小火柴127

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
Apache poi 导入导出Excel文件

引入依赖<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>p
复制链接

扫一扫