前端+后台读取exl文件生成导入数据库数组

编程入门行业动态更新时间:2024-10-15 04:23:49

前端+后台读取exl文件生成导入数据库<a href=https://www.elefans.com/category/jswz/34/1771288.html style= 数组"/>

前端+后台读取exl文件生成导入数据库数组

事先声明，本文工具类中80%代码为复制别人，其余为业务需求自己改动

1. 后台依赖

		<!-- POI,excel解析相关 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><!-- .apache.poi/poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency>

2. 工具类

/***  从exl中导入数据——读取*/
public class ImportDataFromExl {/*** @Author* @Date 2020/11/09* @Param file ：上传的excel文件* @return*/public static List<List<String>> getExcelData(MultipartFile file) throws IOException {checkFile(file);//获得Workbook工作薄对象Workbook workbook = getWorkBook(file);//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回List<List<String>> list = new ArrayList<>();if (workbook != null) {for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {//获得当前sheet工作表Sheet sheet = workbook.getSheetAt(sheetNum);if (sheet == null) {continue;}//获得当前sheet的开始行int firstRowNum = sheet.getFirstRowNum();//获得当前sheet的结束行int lastRowNum = sheet.getLastRowNum();//循环除了所有行,如果要循环除第一行以外的就firstRowNum+1for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {//获得当前行Row row = sheet.getRow(rowNum);if (row == null) {continue;}//获得当前行的开始列int firstCellNum = row.getFirstCellNum();//获得当前行的列数int lastCellNum = row.getLastCellNum();// start---// 判断exl中除了分类商品数据外还有其它数据或空格列// 应为我导入的数据只有3个类别一级一个商品数据所有只有4列有效数据，如果你的并不需要则删除// -------------start---------------Cell cell1 = null;if (lastCellNum > 4){for (int a = lastCellNum; a >= 0; a --){cell1 = row.getCell(a);if (cell1 != null && !cell1.toString().trim().equals("")){lastCellNum = a;break;}}if (lastCellNum > 4){for (int a = 4; a >= 0; a --){cell1 = row.getCell(a);if (cell1 != null && !cell1.toString().trim().equals("")){lastCellNum = a;break;}}}}// -------------end---------------if (lastCellNum > 0) {ArrayList<String> cellValues = new ArrayList<>();//循环当前行for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {Cell cell = row.getCell(cellNum);String cellValue = getCellValue(cell);// 有的商品就是三级分类如鸡蛋等再填充一级分类if (cellValue.equals("")){List list1 = list.get(rowNum - (1 + 1));Object o = list1.get(cellNum);cellValue = o.toString();}cellValues.add(cellValue);}// 分类即是商品if (lastCellNum == 3){cellValues.add(2, cellValues.get(1));}list.add(cellValues);}}}}return list;}/*** 检查文件** @param file* @throws IOException*/public static void checkFile(MultipartFile file) throws IOException {//判断文件是否存在if (null == file) {System.err.println("文件不存在！");}//获得文件名String fileName = file.getOriginalFilename();//判断文件是否是excel文件if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {System.err.println("不是excel文件");}}public static Workbook getWorkBook(MultipartFile file) {//获得文件名String fileName = file.getOriginalFilename();//创建Workbook工作薄对象，表示整个excelWorkbook workbook = null;try {//获取excel文件的io流InputStream is = file.getInputStream();//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象if (fileName.endsWith("xls")) {//2003workbook = new HSSFWorkbook(is);} else if (fileName.endsWith("xlsx")) {//2007 及2007以上workbook = new XSSFWorkbook(is);}} catch (IOException e) {e.getMessage();}return workbook;}public static String getCellValue(Cell cell) {String cellValue = "";if (cell == null) {return cellValue;}//判断数据的类型//判断数据的类型switch (cell.getCellTypeEnum()) {case NUMERIC: //数字cellValue = stringDateProcess(cell);break;case STRING: //字符串cellValue = String.valueOf(cell.getStringCellValue());break;case BOOLEAN: //BooleancellValue = String.valueOf(cell.getBooleanCellValue());break;case FORMULA: //公式cellValue = String.valueOf(cell.getCellFormula());break;case BLANK: //空值cellValue = "";break;case ERROR: //故障cellValue = "非法字符";break;default:cellValue = "未知类型";break;}return cellValue;}public static String stringDateProcess(Cell cell) {String result = new String();if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式SimpleDateFormat sdf = null;if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {sdf = new SimpleDateFormat("HH:mm");} else {// 日期sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");}Date date = cell.getDateCellValue();result = sdf.format(date);} else if (cell.getCellStyle().getDataFormat() == 58) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");double value = cell.getNumericCellValue();Date date = DateUtil.getJavaDate(value);result = sdf.format(date);} else {double value = cell.getNumericCellValue();CellStyle style = cell.getCellStyle();DecimalFormat format = new DecimalFormat();String temp = style.getDataFormatString();// 单元格设置成常规if (temp.equals("General")) {format.applyPattern("#");}result = format.format(value);}return result;}public static InputStream convertorStream(Workbook workbook) {InputStream in = null;try {//临时缓冲区ByteArrayOutputStream out = new ByteArrayOutputStream();//创建临时文件workbook.write(out);byte[] bookByteAry = out.toByteArray();in = new ByteArrayInputStream(bookByteAry);} catch (Exception e) {e.printStackTrace();}return in;}/*** @Author* @Param file ：获取分类数据* @return*/public static HashMap<String, HashSet<String>> getClassData(List<List<String>> list) throws IOException {// 用户存储分类数据HashMap<String, HashSet<String>> classData = new HashMap<>();// 一级分类HashSet<String> one = new HashSet<>();// 二级级分类HashSet<String> two = new HashSet<>();// 三级分类HashSet<String> three = new HashSet<>();for (List<String> strings : list) {one.add(strings.get(0));two.add(strings.get(1));three.add(strings.get(2));}classData.put("ONE", one);classData.put("TWO", two);classData.put("THREE", three);return classData;}
}

3. 控制层代码

    /***  导入exl文件数据* @param file exl文件* @return*/@ResponseBody@RequestMapping(value = "importFileData")public String importFileData(MultipartFile file){try {List<List<String>> excelData = ImportDataFromExl.getExcelData(file);
//            HashMap<String, HashSet<String>> classData = ImportDataFromExl.getClassData(excelData);
//            return JSON.toJSONString(excelData) + JSON.toJSONString(classData);return JSON.toJSONString(excelData);} catch (Exception e) {e.printStackTrace();}return null;}

4. 前端代码

<!DOCTYPE html>
<html lang="en" xmlns:th="">
<head><meta charset="UTF-8"><title>listBooks</title>
</head>
<body><center><h3>导入exl文件数据</h3><form enctype="multipart/form-data" id="fileForm" action="importFileData"  method="post" ><table class="table"><tr><td>Exl文件</td><td><!--<input type="file" name="file" id="importFile" onchange="importFileS()">--><input type="file" accept=".xls,.xlsx" id="exlFile" name="file" ></td><td><input type="button" onclick="importFileS()" value="导入"></td></tr></table></form></center>
<script th:src="@{js/jquery-2.1.4.min.js}"></script>
<script th:inline="javascript">function importFileS(){var files = $("#exlFile").get(0).files[0]; //获取file控件中的内容// var form = $("#fileForm").serialize();var formData = new FormData();formData.append("file", files);$.ajax({type:"POST",url:"importFileData",//接口请求地址data:formData,dataType:'json',processData : false, // 使数据不做处理contentType : false, // 不要设置Content-Type请求头success:function(data){console.log(data);}});}
</script>
</body>
</html>