前后端分离之POI实现Excel文件的上传和下载

阅读：评论：0

若有不对之处欢迎大家指出，这个也是在学习工作中的一些总结，侵删！
需要源码联系QQ:1352057131
得之在俄顷，积之在平日。
POI概述

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

结构：

HSSF：提供读写Microsoft Excel格式档案的功能。
XSSF：提供读写Microsoft Excel OOXML格式档案的功能。
HWPF：提供读写Microsoft Word格式档案的功能。
HSLF：提供读写Microsoft PowerPoint格式档案的功能。
HDGF：提供读写Microsoft Visio格式档案的功能。

Excel文件的上传

文件格式：

引入相关依赖：

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.8</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.8</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.8</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.8</version></dependency>

实体类（此处省略了get set方法）：

public class User {private Integer number;private String password;private String name;private Integer age;private String gender;private Long phone;
}

前端代码：

<div><form method="post" action="localhost:8080/upload/file" enctype="multipart/form-data"><input name = "fileName" type = "file"><br><input type = "submit" value = "点击上传"></form>
</div>

文件上传工具类：

public class LocalFileImportUtil {private final static String excel2003L =".xls";    //2003- 版本的excelprivate final static String excel2007U =".xlsx";   //2007+ 版本的excel/*** @Description：获取IO流中的数据，组装成List<List<Object>>对象* @param in,fileName* @return* @throws IOException*/public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception{List<List<Object>> list = null;//创建Excel工作薄Workbook work = getWorkbook(in,fileName);if(null == work){throw new Exception("创建Excel工作薄为空！");}Sheet sheet = null;  //页数Row row = null;  //行数Cell cell = null;  //列数list = new ArrayList<List<Object>>();//遍历Excel中所有的sheetfor (int i = 0; i < NumberOfSheets(); i++) {sheet = SheetAt(i);if(sheet==null){continue;}//遍历当前sheet中的所有行for (int j = FirstRowNum(); j <= LastRowNum(); j++) {row = Row(j);if(row==null||FirstCellNum()==j){continue;}//遍历所有的列List<Object> li = new ArrayList<Object>();for (int y = FirstCellNum(); y < LastCellNum(); y++) {cell = Cell(y);li.add(getValue(cell));}list.add(li);}}return list;}/*** @Description：根据文件后缀，自适应上传文件的版本* @param inStr,fileName* @return* @throws Exception*/public static  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{Workbook wb = null;String fileType = fileName.substring(fileName.lastIndexOf("."));if(excel2003L.equals(fileType)){wb = new HSSFWorkbook(inStr);  //2003-}else if(excel2007U.equals(fileType)){wb = new XSSFWorkbook(inStr);  //2007+}else{throw new Exception("解析的文件格式有误！");}return wb;}/*** @Description：对表格中数值进行格式化* @param cell* @return*///解决excel类型问题，获得数值public static String getValue(Cell cell) {String value = "";if(null==cell){return value;}switch (CellType()) {//数值型case Cell.CELL_TYPE_NUMERIC:if (HSSFDateUtil.isCellDateFormatted(cell)) {//如果是date类型则 ，获取该cell的date值Date date = NumericCellValue());SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");value = format.format(date);;}else {// 纯数字BigDecimal big=new NumericCellValue());value = String();//解决1234.0  去掉后面的.0if(null!=value&&!"".im())){String[] item = value.split("[.]");if(1<item.length&&"0".equals(item[1])){value=item[0];}}}break;//字符串类型case Cell.CELL_TYPE_STRING:value = StringCellValue().toString();break;// 公式类型case Cell.CELL_TYPE_FORMULA://读公式计算值value = String.NumericCellValue());if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串value = StringCellValue().toString();}break;// 布尔类型case Cell.CELL_TYPE_BOOLEAN:value = " "+ BooleanCellValue();break;default:value = StringCellValue().toString();}if("null".im())){value="";}return value;}
}

Controller：

@Controller
@RequestMapping("/upload")
@CrossOrigin(origins = "localhost:8080")
public class FrontEndFileUploadController {@RequestMapping(value = "/file", method = RequestMethod.POST)public void fileUpload(@RequestParam("fileName") MultipartFile file) {String fileName = OriginalFilename();System.out.println("文件名称+"+fileName);try {InputStream inputStream = InputStream();List<List<Object>> list = ListByExcel(inputStream, fileName);list.forEach((u)-> System.out.String()));} catch (IOException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}}
}

Excel文件下载

此处仅演示后端生成Excel文件
文件上传工具类：

public class ExportFileUtil {/*** @param sheetName sheet名称* @param title     标题* @param values    内容* @param wb        HSSFWorkbook对象* @return*/public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {// 第一步，创建一个HSSFWorkbook，对应一个Excel文件if (wb == null)wb = new HSSFWorkbook();// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheetHSSFSheet sheet = wb.createSheet(sheetName);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制HSSFRow row = ateRow(0);// 第四步，创建单元格，并设置值表头 设置表头居中HSSFCellStyle style = wb.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式//声明列对象HSSFCell cell = null;//创建标题for (int i = 0; i < title.length; i++) {cell = ateCell(i);cell.setCellValue(title[i]);cell.setCellStyle(style);}//创建内容for (int i = 0; i < values.length; i++) {row = ateRow(i + 1);for (int j = 0; j < values[i].length; j++) {//将内容按顺序赋给对应的列对象ateCell(j).setCellValue(values[i][j]);}}return wb;}
}

Controller：

@Controller
@RequestMapping("/user")
@CrossOrigin(origins = "localhost:8080")//解决跨域请求方式二
public class ExportController {@ResponseBody@RequestMapping(value = "/exportExcel", method = RequestMethod.GET)public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式String year = df.format(new Date());//为了方便大家理解，自己创建UesrList<User> list = new ArrayList<>();for (int i = 0; i <10 ; i++) {User user = new User(i*2,"aaa"+i,"xiaoming"+i,i*5,"nan",(long) 1234567890);list.add(user);}//设置Excel表头String[] title = {"账号", "密码", "姓名", "年龄", "性别", "电话"};//设置Excel文件名String filename = "LeaderList_"+year+".xls";//设置工作表名称String sheetName = "sheet1";//开始对从数据库中获取到的数据进行处理String[][] content = new String[list.size()][6];try {for (int i = 0; i < list.size(); i++) {content[i][0] = String.(i).getNumber());content[i][1] = (i).getPassword();content[i][2] = (i).getName();content[i][3] = String.(i).getAge());content[i][4] = (i).getGender();content[i][5] = String.(i).getPhone());}} catch (Exception e) {e.printStackTrace();}HSSFWorkbook wb = HSSFWorkbook(sheetName, title, content, null);try {// 响应到客户端this.setResponseHeader(response, filename);OutputStream os = OutputStream();wb.write(os);os.flush();os.close();} catch (Exception e) {e.printStackTrace();}}/*** 向客户端发送响应流方法** @param response* @param fileName*/public void setResponseHeader(HttpServletResponse response, String fileName) {try {try {fileName = new Bytes(), "UTF-8");} catch (UnsupportedEncodingException e) {e.printStackTrace();}response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename=" + de(fileName, "UTF-8"));} catch (Exception ex) {ex.printStackTrace();}}
}

测试：