spring mvc中导入导出excel

最新推荐文章于 2026-06-15 11:23:53 发布

原创最新推荐文章于 2026-06-15 11:23:53 发布 · 349 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#poi #spring

这篇博客介绍了如何在Spring MVC项目中利用Apache POI库进行Excel的导入和导出。首先，讲述了需要导入的依赖 poi-4.1.2.jar 和 xwork-core-2.3.15.3.jar。接着，详细讲解了如何写入和读取Excel文件，包括创建form表单，配置Spring-web.xml，以及在Controller中实现具体功能的方法。

POI介绍

 Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能

导入jar包：poi-4.1.2.jar，xwork-core-2.3.15.3.jar

1、写excel

package excel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;

import java.io.File;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

public class ExcelTest {

    public static void main(String[] args)  throws  Exception{
        HSSFWorkbook workbook =new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("服务报表");
        HSSFRow row = sheet.createRow(0);

        HSSFCell cell = row.createCell(0);
        cell.setCellType(CellType.STRING);
        cell.setCellValue("类型");

        HSSFCell cell1 = row.createCell(1);
        cell1.setCellType(CellType.STRING);
        cell1.setCellValue("次数");

        Map<String,Integer>  dbs = new HashMap<>();
        dbs.put("咨询",10);
        dbs.put("建议",20);

        Set<String> typs = dbs.keySet();
        int i = 1;
        for(String type:typs) {
            HSSFRow row2 = sheet.createRow(i++);
            HSSFCell cel2 = row2.createCell(0);
            cel2.setCellType(CellType.STRING);
            cel2.setCellValue(type);
            HSSFCell cell3 = row2.createCell(1);
            cell3.setCellType(CellType.NUMERIC);
            cell3.setCellValue(dbs.get(type));
        }

        File file = new File("E:\\a.xls");
        workbook.write(file);

    }
}

2、读取excel

package excel;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;

import java.io.File;
import java.io.FileInputStream;


public class ExcelTest2 {

    public static void main(String[] args)  throws  Exception{
        File file = new File("E:\\a.xls");
        HSSFWorkbook workbook =new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheetAt = workbook.getSheetAt(0);
        for(Row r:sheetAt) {
            for(Cell c:r) {
                c.setCellType(CellType.STRING);
                System.out.print(c.getStringCellValue() +"\t");
            }
            System.out.println();
        }

    }
}

3、准备form表单

<form enctype="multipart/form-data" method="post" action="${basePath}/product/upload">
  <input  name="file" type="file"/>
    <input  name="上传" type="submit"/>
</form >

4、Spring-web.xml

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"/>

5、在controlller中的编写的方法

package com.cc.web.controller;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;

@Controller
@RequestMapping("/product")
public class ProductController {
    @RequestMapping("/list")
    public String listSeviceReport() {
        return  "~basd/product";
    }
	//到入excel的方法
    @RequestMapping("/upload")
    public  String uploadExcel(MultipartFile file) throws  Exception {
        HSSFWorkbook workbook =new HSSFWorkbook(file.getInputStream());
        HSSFSheet sheetAt = workbook.getSheetAt(0);
       for(Row r:sheetAt) {
           for(Cell c:r) {
               c.setCellType(CellType.STRING);
               System.out.print(c.getStringCellValue() +"\t");
           }
           System.out.println();
       }

       return  null;
    }

}