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

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

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

导入

触发点击事件,这里使用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 bindingList = new ArrayList<>();List bookcaseList = new ArrayList<>();//Excel导入导出的单元类List 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 header, List 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 header, List 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 readData(String fileType, int startRows, boolean ignoreRowBlank, InputStream is) throws IOException {List 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;}//读取excelprivate 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);}
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！