Excel上传与下载

Excel上传与下载

pom文件添加依赖

 <!-- excel导入导出所用jar包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.7</version>
        </dependency>

导入类

package com.skytech.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by HP on 2020/7/1.
 */
public class ExcelImport {
    /**
     * @param dest 表格文件
     * @param cellLength 一行多少个单元格
     * @return 返回list集合
     * @throws Exception
     */
    public static List<Map<Integer,String>> read(File dest, Integer cellLength) throws Exception{
        Workbook wookbook = null;
        FileInputStream fis = null;
        int cellType = 1;
        try {
            fis = new FileInputStream(dest);
            //用HSSF来处理，有异常即为xlsx格式，用XSSF处理
            wookbook = new HSSFWorkbook(fis);//得到工作簿
            cellType = HSSFCell.CELL_TYPE_STRING;
        } catch (Exception e) {
            try {
                fis = new FileInputStream(dest);//这里不创建输入流就会报错stream close
                wookbook = new XSSFWorkbook(fis);
                cellType = XSSFCell.CELL_TYPE_STRING;
            } catch (Exception e1) {
                //返回文件格式错误异常
                throw new Exception("This file is not in excel format");
            }//得到工作簿
        } finally {
            fis.close();
        }
        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);
        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();
        //要获得属性
        List<Map<Integer,String>> list = new ArrayList<Map<Integer,String>>();
        Map<Integer,String> map = null;
        //获得所有数据
        //从第x行开始获取
        for(int x = 1 ; x <= totalRowNum ; x++){
            map = new HashMap<Integer,String>();
            //获得第i行对象
            Row row = sheet.getRow(x);
            //如果一行里的所有单元格都为空则不放进list里面
            int a = 0;
            for(int y=0;y<cellLength;y++){
                Cell cell = row.getCell(y);
                if(cell == null){
                    map.put(y,"");
                }else{
                    cell.setCellType(cellType);
                    map.put(y, cell.getStringCellValue().toString());
                }
                if(map.get(y)==null||"".equals(map.get(y))){
                    a++;
                }
            }
            if(a!=cellLength){
                list.add(map);
            }
        }
        return list;
    }
}

导出类

package com.skytech.utils;

import org.apache.poi.hssf.usermodel.*;

/**
 * Created by HP on 2020/7/1.
 */
public class ExcelExport {
    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @return
     */
    public static HSSFWorkbook createWorkbook(String sheetName, String []title, String [][]values){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
}

Controller 代码

package com.skytech.controller;

import com.alibaba.fastjson.JSONObject;
import com.skytech.service.ExcelService;
import com.skytech.utils.ExcelExport;
import com.skytech.utils.ExcelImport;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.File;
import java.io.OutputStream;
import java.util.*;

/**
 * Created by Sugar  on 2020/7/1.
 */
@RequestMapping("/excel")
@RestController
public class ExcelController extends BaseController{
    @Resource
    private ExcelService excelService;

    /*
    *
    * Excel上传
    *
    * */
    @RequestMapping(value = "/excelUpLoad", method = RequestMethod.POST)
     public Boolean importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws Exception {
        //用来获取用户上传excel字段，存到数据库
        JSONObject params=new JSONObject();
        //获取用户上传表格的地址，执行完程序后表格将会删除，避免占用内存
        String filePath = request.getSession().getServletContext().getRealPath("/");
        //根据地址和文件名准确获取用户上传的表格
        File tempFile = new File(filePath+file.getOriginalFilename());
        try {
            //将MultipartFile转换为File类型
            file.transferTo(tempFile);
            List<Map<Integer,String>> dataList = ExcelImport.read(tempFile,5);
            for (int i = 0; i < dataList.size(); i++) {
                //此处的取值顺序取决于Excel表的表头顺序，一一对应
                //学生id  姓名  性别  年龄  学校
                params.put("id",dataList.get(i).get(0));
                params.put("name",dataList.get(i).get(1));
                params.put("sex",dataList.get(i).get(2));
                params.put("age",dataList.get(i).get(3));
                params.put("scholl",dataList.get(i).get(4));
                //此处应该将user对象插入数据库中
                excelService.uploadExcel(params);
            }
            //执行完程序后删除用户上传文件
            tempFile.delete();
        } catch (Exception e) {
            tempFile.delete();
            throw new Exception("批量录入用户失败，请检查表格中的数据是否和数据库中的数据冲突！");
        }
        return true;
    }

    /**
     * Excel导出
     *
     */
    @RequestMapping(value = "/getFile", method = RequestMethod.GET)
    public void getFile(@RequestParam Map map, HttpServletResponse response, HttpServletRequest request) throws Exception {
        JSONObject params = new JSONObject(map);
        List<JSONObject> userList=excelService.getExcel(params);
        //excel标题
        String[] title = {"id", "姓名", "性别", "年龄", "学校"};
        String[][] objects = new String[userList.size()][title.length];
        for (int i = 0; i < userList.size(); i++) {
            JSONObject user = userList.get(i);
            objects[i][0] = user.getString("id");
            objects[i][1] = user.getString("name");
            objects[i][2] = user.getString("sex");
            objects[i][3] = user.getString("age");
            objects[i][4] = user.getString("scholl");
        }
        String fileName = "学生信息表";
        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelExport.createWorkbook(fileName, title, objects);
        //响应到客户端
        try {
            //设置编码、输出文件格式
            response.reset();
            fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

前端页面代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form>
    <input type="file" id="upFile" class="form-control form-control-file"
           accept=".csv, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
           name="importFile">
    <input type="button" onclick="post();" value="上传"/>
</form>


</body>
<script src="libs/js/jquery/jquery.min.js"></script>
<script type="text/javascript">
    function post() {
        var formData = new FormData();
        //接口接收参数 键值形式 添加到formData中
        formData.append("file", $("#upFile")[0].files[0]);
        $.ajax({
            url: "excel/excelUpLoad.do",//url地址
            type: 'post',
            data: formData,
            contentType: false,
            processData: false,
            success: function (res) {
                console.log(res);
            }
        })
    }
</script>

</html>

注意注意

本文简单介绍了实现步骤，贴出了大部分代码，具体的数据存储，以及excel生成数据的获取是需要自己对数据库进行存取数据滴，自己创建一个excel，表头的数据和代码中数据要一一对应，自己琢磨琢磨思考思考，问题不大。