springboot中Excel文件导入导出

Java学习大纲(持续更新):https://blog.csdn.net/weixin_39778570/article/details/94667501
更多IT学习资源:https://blog.csdn.net/weixin_39778570/article/details/100052454

Excel文件导入

从前端传递excel文件到后端,通过ajax
这里使用的是lay-ui的控件

upload.render({
            elem: '#test' //绑定元素
            ,accept: 'file'
            ,url: '/xxx/xxx/upload' //上传接口
            ,before: function(obj) {
                layer.msg('文件上传中...', {
                    icon: 16,
                    shade: 0.01,
                    time: 0
                })
            },
            done: function(res) {
                layer.close(layer.msg(res.msg));//关闭上传提示窗口
            }
        });

后端Controller获取数据并处理

@RequestMapping("upload")
	@ResponseBody
	public BaseResponse doUpload(HttpServletRequest request) throws IOException {
		MultipartHttpServletRequest mreq = null;
		if(request instanceof  MultipartHttpServletRequest){
			mreq = (MultipartHttpServletRequest) request;
		}else {
			return BaseResponse.fail ();
		}
		try{
			boolean flag = xxx.analysisFile(mreq);
			if(!flag){
				return BaseResponse.fail ();
			}
			return BaseResponse.success();
		}catch (Exception e){
			return BaseResponse.fail ();
		}
	}

Server层xxx.analysisFile(mreq)

public boolean analysisFile(MultipartHttpServletRequest mreq){
		List<Map> maps = null;
		try {
			maps = ExcelUtils.analysisFile (mreq);
		} catch (Exception e) {
			return false;
		}
		if(maps==null){
			return false;
		}else{
			// 处理信息
			return true;
		}
	}

解析Excel文件请求的工具类(可以直接复用)

import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.List;

/**
 * Excel处理工具
 */
public class ExcelUtils {

    /**
     * 处理HTTP请求里是file文件
     * @param mreq
     * @return 返回List<Map>每个Map存放一行
     */
    public static List<Map> analysisFile(MultipartHttpServletRequest mreq)throws Exception{
        InputStream inputStream= null;
        try {
            inputStream = mreq.getFile ("file").getInputStream ();
        } catch (IOException e) {
            throw e;
        }
        String fileName = mreq.getFile ("file").getOriginalFilename ();
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                workbook = WorkbookFactory.create(inputStream);
            }
        } catch (Exception e) {
            throw e;
        }
        if (workbook == null) {
            return null;
        } else {
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets ();
            System.out.println (numOfSheet + "--->numOfSheet");
            List<Map> mapList = new ArrayList<> ();
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt (i);
                if (sheet == null) {
                    continue;
                }
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum ();
                if (lastRowNum == 0) {
                    continue;
                }
                Row row;
                // 行头
                List<String> headName = new ArrayList<>();
                if(lastRowNum>0){
                    row = sheet.getRow (0);
                    // 获取一个Row有多少Cell
                    short lastCellNum = row.getLastCellNum ();
                    for (int k = 0; k <= lastCellNum; k++) {
                        if (row.getCell (k) == null) {
                            continue;
                        }
                        String res = getCellVal(row.getCell (k));
                        headName.add (res);
                    }
                }

                // 每一行的内容
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow (j);
                    if (row == null) {
                        continue;
                    }
                    //获取一个Row有多少Cell
                    short lastCellNum = row.getLastCellNum ();
                    Map<String,String> rowMap = new HashMap ();
                    for (int k = 0; k <= lastCellNum; k++) {
                        if (row.getCell (k) == null) {
                            continue;
                        }
                        String res = getCellVal(row.getCell (k));
                        rowMap.put (headName.get (k), res);
                    }
                    mapList.add (rowMap);
                }
            }
            return mapList;
        }
    }

    private static String getCellVal(Cell cell) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); //日期格式yyyy-mm-dd
        DecimalFormat df = new DecimalFormat("0");
        String val;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    val = fmt.format(cell.getDateCellValue()); //日期型
                } else {
                    val = df.format(cell.getNumericCellValue()); //数字型
                }
                break;
            case Cell.CELL_TYPE_STRING: //文本类型
                val = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                val = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: //空白
                val = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: //错误
                val = "错误";
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                try {
                    val = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    val = String.valueOf(cell.getNumericCellValue());
                }
                break;
            default:
                val = cell.getRichStringCellValue() == null ? null : cell.getRichStringCellValue().toString();
        }
        return val;
    }
}

Excel 文件导出

由于ajax 是无法直接导出excel的,因为ajax返回值只能是字符流,而导出excel是后台往浏览器中写入二进制的字节流
流程是这样的,前端发送一个ajax请求到后端,后端缓存请求数据,并保存请求,返回给浏览器一个UUID,浏览器通过UUID再发起请求(可以通过window.location.href的方式),服务器把excel文件写入response中显示到浏览器

前端请求

form.on('submit(export)', function(data){
            var field = data.field;
            $.ajax({
                url:'<@spring.url "/xxx/ttt/export"/>',
                dataType : "json",
                data : JSON.stringify(field),
                contentType : "application/json; charset=utf-8",
                type : "POST",
                async: true,
                success : function(data){
                    var key =data.msg
                    window.location.href = "/xxx/ttt/doExport?key="+key
                }
            })
        });

后端处理请求,先缓存,再查询(为了简单起见这里就不查询了),再清楚缓存

/**
     * 由于ajax无法直接导出excel,所以第一次把请求生成的ExcelParam缓存起来,然后前端再次window.open(url);
     */
    public static Map<String, Xxx> excelParamCache = new ConcurrentHashMap<> ();

    //第一步缓存参数
    @SuppressWarnings("unchecked")
    @RequestMapping(value = "/export")
    @ResponseBody
    public BaseResponse export(@RequestBody Xxx requestVo) {
        String key = UUID.randomUUID().toString();
        excelParamCache.put(key,requestVo);
        return new BaseResponse(0,key);
    }

    @RequestMapping(value = "doExport")
    public BaseResponse doExport(String key, HttpServletRequest request, HttpServletResponse response) throws IOException {
        if(key==null){
            return BaseResponse.fail ();
        }
        try {
            if(!excelParamCache.containsKey (key)){
                return BaseResponse.fail ();
            }
            HSSFWorkbook workbook = new HSSFWorkbook ();
            HSSFSheet sheet = workbook.createSheet ("信息表");

            // 设置要导出的文件的名字
            String fileName = "报表-" + new Date () + ".xls";

            // 新增数据行,并且设置单元格数据
            int rowNum = 1;

            // headers表示excel表中第一行的表头 在excel表中添加表头
            String[] headers = {"id", "uid", "地址", "城市"};
            HSSFRow row = sheet.createRow (0);
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell (i);
                HSSFRichTextString text = new HSSFRichTextString (headers[i]);
                cell.setCellValue (text);
            }

            //在表中存放查询到的数据放入对应的列
            for (int i = 0; i < 4; i++) {
                HSSFRow row1 = sheet.createRow (rowNum);
                row1.createCell (0).setCellValue (1);
                row1.createCell (1).setCellValue (2);
                row1.createCell (2).setCellValue (3);
                row1.createCell (3).setCellValue ("hhh");
                rowNum++;

            }

            response.setContentType ("application/octet-stream");
            response.setHeader ("Content-disposition", "attachment;filename=" + fileName);
            response.flushBuffer ();
            workbook.write (response.getOutputStream ());
        }catch (Exception e){
            excelParamCache.remove(key);
        }finally {
            excelParamCache.remove(key);
        }
        return BaseResponse.success ();
    }

依赖

<!-- 文件上传 -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.14</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.14</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>3.14</version>
    </dependency>

执行结果
在这里插入图片描述

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页