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 closewookbook = 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文件中的sheetHSSFSheet 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{@Resourceprivate 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 = "学生信息表";//创建HSSFWorkbookHSSFWorkbook 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，表头的数据和代码中数据要一一对应，自己琢磨琢磨思考思考，问题不大。