通过POI实现上传EXCEL的批量读取数据写入数据库
最近公司新增功能要求导入excel，并读取其中数据批量写入数据库。于是就开始了这个事情，之前的文章，记录了上传文件，本篇记录如何通过POI读取excel数据并封装为对象上传。
上代码：
1、首先这是一个依赖第三方的事情，故而需要导入jar包
1 <!-- read excel start --> 2 <dependency> 3 <groupId>org.apache.poi</groupId> 4 <artifactId>poi</artifactId> 5 <version>3.17</version> 6 </dependency> 7 <dependency> 8 <groupId>org.apache.poi</groupId> 9 <artifactId>poi-ooxml</artifactId> 10 <version>3.17</version> 11 </dependency> 12 <!-- read excel start -->
切记下面的那个包不可忘记，否则报错RETURN_NULL_AND_BLANK
之前我们已经上传含有数据的excel模板，返回的是一个url，本博主对应的url的excel表内容如下：
那么我们传入导入的excel的url来进行读取。不做过多解释，该有的解释代码注释里面均有
1 package com.topband.sweepmachine.utils; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.IOException; 6 import java.io.InputStream; 7 import java.text.DecimalFormat; 8 import java.util.ArrayList; 9 import java.util.List; 10 11 import org.apache.commons.io.FileUtils; 12 import org.apache.poi.hssf.usermodel.HSSFDateUtil; 13 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 14 import org.apache.poi.ss.usermodel.Cell; 15 import org.apache.poi.ss.usermodel.CellType; 16 import org.apache.poi.ss.usermodel.Row; 17 import org.apache.poi.ss.usermodel.Sheet; 18 import org.apache.poi.ss.usermodel.Workbook; 19 import org.apache.poi.ss.usermodel.WorkbookFactory; 20 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 21 import org.slf4j.Logger; 22 import org.slf4j.LoggerFactory; 23 24 import com.topband.cloud.common.utils.DateFormatUtil; 25 import com.topband.cloud.common.utils.StringUtil; 26 27 public class ReadExcelUtil { 28 29 private Logger logger = LoggerFactory.getLogger(this.getClass()); 30 private static final String EXCEL_XLS = ".xls"; 31 private static final String EXCEL_XLSX = ".xlsx"; 32 33 /** 34 *读取excel数据 35 * @throws Exception 36 * 37 */ 38 public static List<List<String>> readExcelInfo(String url) throws Exception{ 39 /* 40 * workbook:工作簿,就是整个Excel文档 41 * sheet:工作表 42 * row:行 43 * cell:单元格 44 */ 45 46 // BufferedWriter bw = new BufferedWriter(new FileWriter(new File(url))); 47 // 支持excel2003、2007 48 File excelFile = new File(url);//创建excel文件对象 49 InputStream is = new FileInputStream(excelFile);//创建输入流对象 50 checkExcelVaild(excelFile); 51 Workbook workbook = getWorkBook(is, excelFile); 52 // Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、2010 53 // 获取Sheet数量 54 int sheetNum = workbook.getNumberOfSheets(); 55 // 创建二维数组保存所有读取到的行列数据，外层存行数据，内层存单元格数据 56 List<List<String>> dataList = new ArrayList<List<String>>(); 57 // FormulaEvaluator formulaEvaluator = null; 58 // 遍历工作簿中的sheet,第一层循环所有sheet表 59 for(int index = 0;index<sheetNum;index++){ 60 Sheet sheet = workbook.getSheetAt(index); 61 if(sheet==null){ 62 continue; 63 } 64 System.out.println("表单行数："+sheet.getLastRowNum()); 65 // 如果当前行没有数据跳出循环，第二层循环单sheet表中所有行 66 for(int rowIndex=0;rowIndex<=sheet.getLastRowNum();rowIndex++){ 67 Row row = sheet.getRow(rowIndex); 68 // 根据文件头可以控制从哪一行读取，在下面if中进行控制 69 if(row==null){ 70 continue; 71 } 72 // 遍历每一行的每一列，第三层循环行中所有单元格 73 List<String> cellList = new ArrayList<String>(); 74 for(int cellIndex=0;cellIndex<row.getLastCellNum();cellIndex++){ 75 Cell cell = row.getCell(cellIndex); 76 System.out.println("遍历行中cell数据:"+getCellValue(cell)); 77 cellList.add(getCellValue(cell)); 78 System.out.println("第"+cellIndex+"个: cell个数："+cellList.size()); 79 } 80 dataList.add(cellList); 81 System.out.println("第"+rowIndex+"行: 共几行："+dataList.size()); 82 } 83 84 } 85 is.close(); 86 return dataList; 87 } 88 /** 89 *获取单元格的数据,暂时不支持公式 90 * 91 * 92 */ 93 public static String getCellValue(Cell cell){ 94 CellType cellType = cell.getCellTypeEnum(); 95 String cellValue = ""; 96 if(cell==null || cell.toString().trim().equals("")){ 97 return null; 98 } 99 100 if(cellType==CellType.STRING){ 101 cellValue = cell.getStringCellValue().trim(); 102 return cellValue = StringUtil.isEmpty(cellValue)?"":cellValue; 103 } 104 if(cellType==CellType.NUMERIC){ 105 if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断日期类型 106 cellValue = DateFormatUtil.formatDurationYMD(cell.getDateCellValue().getTime()); 107 } else { //否 108 cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue()); 109 } 110 return cellValue; 111 } 112 if(cellType==CellType.BOOLEAN){ 113 cellValue = String.valueOf(cell.getBooleanCellValue()); 114 return cellValue; 115 } 116 return null; 117 118 } 119 /** 120 *判断excel的版本，并根据文件流数据获取workbook 121 * @throws IOException 122 * 123 */ 124 public static Workbook getWorkBook(InputStream is,File file) throws Exception{ 125 126 Workbook workbook = null; 127 if(file.getName().endsWith(EXCEL_XLS)){ 128 workbook = new HSSFWorkbook(is); 129 }else if(file.getName().endsWith(EXCEL_XLSX)){ 130 workbook = new XSSFWorkbook(is); 131 } 132 133 return workbook; 134 } 135 /** 136 *校验文件是否为excel 137 * @throws Exception 138 * 139 * 140 */ 141 public static void checkExcelVaild(File file) throws Exception { 142 String message = "该文件是EXCEL文件！"; 143 if(!file.exists()){ 144 message = "文件不存在！"; 145 throw new Exception(message); 146 } 147 if(!file.isFile()||((!file.getName().endsWith(EXCEL_XLS)&&!file.getName().endsWith(EXCEL_XLSX)))){ 148 System.out.println(file.isFile()+"==="+file.getName().endsWith(EXCEL_XLS)+"==="+file.getName().endsWith(EXCEL_XLSX)); 149 System.out.println(file.getName()); 150 message = "文件不是Excel"; 151 throw new Exception(message); 152 } 153 } 154 /* public static void main(String[] args) throws Exception { 155 readExcelInfo("g://批量新增设备表.xlsx"); 156 }*/ 157 }
封装为对象，插入数据库，这里本博主不放入公司业务对象，删掉了
1 @PostMapping("/addBatchDevice") 2 public ResponseObj addBatchDevice(@RequestBody JSONObject jsonObject){ 3 ResponseObj response = new ResponseObj(); 4 response.setData(Defined.STATUS_SUCCESS); 5 response.setMessage("插入成功！"); 6 String url = jsonObject.getString("url"); 7 // 存放封装的设备 8 List<Device> devices = new ArrayList<Device>(); 9 try { 10 List<List<String>> list = ReadExcelUtil.readExcelInfo(url); 11 for(int i=0;i<list.size();i++){ 12 // new一个对象按照相应的字段设置进去就可以了，这里省略对象设置值，字段如下： 13 System.out.println("封装成对象后："+"\t"+"设备名称--->"+list.get(i).get(0)+"型号--->"+list.get(i).get(1)+"数量---> "+list.get(i).get(2)); 14 } 15 } catch (Exception e) { 16 // TODO Auto-generated catch block 17 e.printStackTrace(); 18 } 19 // 插入数据库 20 // 调用相关插入方法，可以批量也可单条插入循环实现，看具体业务需要选择 21 return response; 22 }
调用接口运行效果如下：
OK，成功运行！