Java如何实现导入Excel功能，亲测有用！

最近开发项目，使用到了Excel导入导出功能，以防忘记，还是做下笔记——好记性不如烂笔头！

首先在html页面上，设置一个文件上传的按钮

<div> <button type="button" class="btn-btn" @click="uploadFile" style="margin-top: 5px;float: left">导入 </button> </div>

触发点击事件,这里使用layer mobile移动版插件，用来提示和显示加载层，大数据传输，使用formData封装， var formData = new FormData(); formData.append(“file”, document.getElementById(“upload”).files[0]);

下面是js方法

//上传Excel文件 //判断文件是否存在,以及文件后缀名 uploadFile() { var file = $("#upload").val(); file = file.substring(file.lastIndexOf('.'), file.length); if (file == '') { layer.open({ content: '上传文件不能为空！' , skin: 'msg' , time: 2 //2秒后自动关闭 }); } else if (file != '.xlsx' && file != '.xls') { layer.open({ content: '请选择正确的excel类型文件！' , skin: 'msg' , time: 2 //2秒后自动关闭 }); } else { this.ajaxFileUpload(); } }, //上传文件请求 ajaxFileUpload() { var formData = new FormData(); formData.append("file", document.getElementById("upload").files[0]); $.ajax({ url: uploadExcelFielUrl, type: "POST", async: true, data: formData, processData: false, //需要这两个设置 contentType: false, beforeSend: function () { layer.open({ type: 2 , content: '文件上传中，请稍候' }); }, success: function (data) { layer.closeAll(); data = JSON.parse(data); if (data.state == 200) { layer.open({ content: data.message , skin: 'msg' , time: 1 //2秒后自动关闭 }); window.location.reload(); } else { layer.open({ content: data.message , skin: 'msg' , time: 2 //2秒后自动关闭 }); } } }); }

java中Controller层接收文件，这里偷懒，在控制层操作数据，应该在服务层，毕竟服务层加了AOP事务，有两条sql或以上都需要加事务.

@RequestMapping("InputExcel") @ResponseBody public ResultEntity InputExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) { ResultEntity result = new ResultEntity(); if (!file.isEmpty()) { try { //获取原始的文件名 String originalFilename = file.getOriginalFilename(); //获取文件类型 String fileType = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length()); //默认从第一行开始读取 Integer startRows = 1; //获取输入流 InputStream is = file.getInputStream(); List<DoorAntRel> bindingList = new ArrayList<>(); List<Bookcase> bookcaseList = new ArrayList<>(); //Excel导入导出的单元类 List<String[]> strings = ExcelUtil.readData(fileType, startRows, true, is); //遍历Excel表每一行的数据 for (String[] str : strings) { Bookcase bookcase = new Bookcase(); DoorAntRel doorAntRel = new DoorAntRel(); bookcase.setName(str[1]); bookcase.setType(str[2]); bookcase.setCom(Integer.parseInt(str[3])); doorAntRel.setDoorName(str[4]); doorAntRel.setDoorNo(Integer.parseInt(str[5])); doorAntRel.setAntennaNo(Integer.parseInt(str[6])); doorAntRel.setReadWriterId(Integer.parseInt(str[7])); doorAntRel.setBookcaseId(Integer.parseInt(str[8])); doorAntRel.setBadFlag(Integer.parseInt(str[9])); doorAntRel.setDoorSlot(Integer.parseInt(str[10])); bindingList.add(doorAntRel); bookcaseList.add(bookcase); } //将数据批量保存到数据库，底层原理其实也是遍历操作，可以自己写循环插入或修改，我这里使用了Mybatis plus插件，所以有方法批量操作，自己写也可以 boolean bookState = bookcaseService.insertOrUpdateBatch(bookcaseList); boolean doorState = doorAntRelService.insertOrUpdateBatch(bindingList); if(bookState){ if(doorState){ result.setState(HttpCode.SUCCESS); result.setMessage("上传文件成功！"); return result; } } } catch (IOException e) { e.printStackTrace(); } } result.setState(HttpCode.FAILED); result.setMessage("上传文件失败！"); return result; }

ExcelUtils类，这个网上有很多版本，下载一个，导入到自己的项目，根据ExcelUtils需要的参数，而做不同的操作，上面贴的代码则是根据我这个ExcelUtils写的，我个人觉得我这个ExcelUtils不太安全，但是简单

package com.ilas.bookcase.common.utils; import org.apache.poi.hssf.usermodel.*; 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.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; public class ExcelUtil { public static void createExcel(List<String> header, List<String[]> data, OutputStream out) throws IOException { // 创建一个Excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表 HSSFSheet sheet = workbook.createSheet("sheet1"); // 添加表头行 HSSFRow hssfRow = sheet.createRow(0); // 设置单元格格式居中 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 添加表头内容 for (int i = 0; i < header.size(); i++) { HSSFCell headCell = hssfRow.createCell(i); headCell.setCellValue(header.get(i)); headCell.setCellStyle(cellStyle); } // 添加数据内容 for (int i = 0; i < data.size(); i++) { String[] strings = data.get(i); hssfRow = sheet.createRow(i + 1); for (int j = 0; j < strings.length; j++) { HSSFCell cell = hssfRow.createCell(j); cell.setCellValue(strings[j]); cell.setCellStyle(cellStyle); } } //单元格自适应 sheet.autoSizeColumn(2,true); // 保存Excel文件 workbook.write(out); } public static void createExcel(String title,List<String> header, List<String[]> data, OutputStream out) throws IOException { // 创建一个Excel文件 HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个工作表 HSSFSheet sheet = workbook.createSheet("sheet1"); // 添加表头行 HSSFRow hssfRow = sheet.createRow(0); HSSFCell cellTitle = hssfRow.createCell(0); // 设置标题外的单元格格式居中 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置标题的样式 HSSFCellStyle titleCellStyle = workbook.createCellStyle(); titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置标题字体的样式 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12);//设置字体大小 titleCellStyle.setFont(font); //标题设置(四个参数分别表示起始行，终止行，起始列，终止列) cellTitle.setCellValue(title); int lastCol = header.size() > 1 ? header.size() : 2; CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) lastCol - 1); sheet.addMergedRegion(region1); hssfRow = sheet.createRow(1); hssfRow = sheet.createRow(2); cellTitle.setCellStyle(titleCellStyle); // 添加表头内容 for (int i = 0; i < header.size(); i++) { HSSFCell headCell = hssfRow.createCell(i); headCell.setCellValue(header.get(i)); headCell.setCellStyle(cellStyle); } // 添加数据内容 for (int i = 0; i < data.size(); i++) { String[] strings = data.get(i); hssfRow = sheet.createRow(i + 3); for (int j = 0; j < strings.length; j++) { HSSFCell cell = hssfRow.createCell(j); cell.setCellValue(strings[j]); cell.setCellStyle(cellStyle); } } // 保存Excel文件 workbook.write(out); } /** * 读取Excel的内容 * * @param fileType 文件类型，xls或xlsx * @param startRows 开始读取行数，比喻行头不需要读入 忽略的行数为1 * @param ignoreRowBlank 是否忽略空行 * @param is 文件输入流 * @return 读出的Excel中数据的内容 * @throws IOException duxxxxx */ public static List<String[]> readData(String fileType, int startRows, boolean ignoreRowBlank, InputStream is) throws IOException { List<String[]> result = new ArrayList<>(); Workbook wb = readExcel(fileType, is); for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { Sheet sheet = wb.getSheetAt(sheetIndex); for (int rowIndex = startRows, z = sheet.getLastRowNum(); rowIndex <= z; rowIndex++) { Row row = sheet.getRow(rowIndex); if (row == null) { continue; } int rowSize = row.getLastCellNum(); String[] values = new String[rowSize]; boolean hasValue = false; for (int columnIndex = 0; columnIndex < rowSize; columnIndex++) { String value = ""; Cell cell = row.getCell(columnIndex); if (cell != null) { // 注意：一定要设成这个，否则可能会出现乱码,后面版本默认设置 switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd") .format(date); } else { value = ""; } } else { //value = new DecimalFormat("0").format(cell.getNumericCellValue()); if (HSSFDateUtil.isCellDateFormatted(cell)) { value = String.valueOf(cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); String temp = cell.getStringCellValue(); // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串 if (temp.indexOf(".") > -1) { value = String.valueOf(new Double(temp)).trim(); } else { value = temp.trim(); } } } break; case HSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值 if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } else { value = cell.getNumericCellValue() + ""; } break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_ERROR: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value = (cell.getBooleanCellValue() == true ? "Y" : "N"); break; default: value = ""; } } values[columnIndex] = value; if (!value.isEmpty()) { hasValue = true; } } if (!ignoreRowBlank || hasValue) {//不为忽略空行模式或不为空行 result.add(values); } } } return result; } //读取excel private static Workbook readExcel(String fileType, InputStream is) throws IOException { if ("xls".equals(fileType)) { return new HSSFWorkbook(is); } else if ("xlsx".equals(fileType)) { return new XSSFWorkbook(is); } else { throw new IllegalArgumentException("不支持的文件类型，仅支持xls和xlsx"); } } /** * 去掉字符串右边的空格 * * @param str 要处理的字符串 * @return 处理后的字符串 */ private static String rightTrim(String str) { if (str == null) { return ""; } int length = str.length(); for (int i = length - 1; i >= 0; i--) { if (str.charAt(i) != 0x20) { break; } length--; } return str.substring(0, length); } }