java 导入excel
前几天做了一个Excel多个sheet页导入功能,有意思的东西习惯于一边开发,一边记录,供有需要的同学一个参考
1.JAR包准备
我这里用的maven,所以jar报直接引入了
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
2.核心代码
public static List<ExcelSheetDto> getExeclStringArray(InputStream in, String fileName) throws Exception { List<ExcelSheetDto> list = importExcel(in, 1, fileName);// 这里的1代表忽略的行数,比方说excel中有标题, 那么则从第2行开始读取数据 log.info("####导入excel页码####" + list.size()); return list; } /** * 读取Excel的内容,第一维数组存储的是一行中格列的值,二维数组存储的是多少个行 * * @param in 读取数据的源Excel * @param ignoreRows 读取数据忽略的行数,比喻行头不需要读入 忽略的行数为1 * @return 读出的Excel中数据的内容 * @throws FileNotFoundException * @throws IOException */ public static List<ExcelSheetDto> importExcel(InputStream in, int ignoreRows, String fileName) throws FileNotFoundException, IOException { List<ExcelSheetDto> list = new ArrayList<>(); int rowSize = 0; Workbook wb; // 当excel是2003时,创建excel2003 if (isExcel2007(fileName)) { wb = new XSSFWorkbook(in); } else { // 当excel是2007时,创建excel2007 wb = new HSSFWorkbook(in); } Cell cell = null; String value; for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { ExcelSheetDto sheetDto = new ExcelSheetDto(); List<String[]> result = new ArrayList<>(); Sheet st = wb.getSheetAt(sheetIndex); // 第一行为标题,不取 for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) { Row row = st.getRow(rowIndex); if (row == null) { continue; } int tempRowSize = row.getLastCellNum() + 1; if (tempRowSize > rowSize) { rowSize = tempRowSize; } String[] values = new String[rowSize]; Arrays.fill(values, ""); boolean hasValue = false; for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) { value=getValue(row.getCell(columnIndex)); if (columnIndex == 0 && value.trim().equals("")) { continue; } values[columnIndex] = rightTrim(value); hasValue = true; } if (hasValue) { result.add(values); } } String[][] returnArray = new String[result.size()][rowSize]; for (int i = 0; i < returnArray.length; i++) { returnArray[i] = result.get(i); } sheetDto.setSheetValue(returnArray); list.add(sheetDto); } //in.close(); return list; } /** * 解决excel类型问题,获得数值 */ public static String getValue(Cell cell) { String value = ""; if(null==cell){ return value; } switch (cell.getCellType()) { //数值型 case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { //如果是date类型则 ,获取该cell的date值 Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); value = format.format(date); }else {// 纯数字 BigDecimal big=new BigDecimal(cell.getNumericCellValue()); value = big.toString(); //解决1234.0 去掉后面的.0 if(null!=value&&!"".equals(value.trim())){ String[] item = value.split("[.]"); if(1<item.length&&"0".equals(item[1])){ value=item[0]; } } } break; //字符串类型 case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue().toString(); break; // 公式类型 case Cell.CELL_TYPE_FORMULA: //读公式计算值 value = String.valueOf(cell.getNumericCellValue()); if (value.equals("NaN")) { // 如果获取的数据值为非法值,则转换为获取字符串 value = cell.getStringCellValue().toString(); } break; // 布尔类型 case Cell.CELL_TYPE_BOOLEAN: value = " "+ cell.getBooleanCellValue(); break; // 空值 case Cell.CELL_TYPE_BLANK: value = ""; break; // 故障 case Cell.CELL_TYPE_ERROR: value = ""; break; default: value = cell.getStringCellValue().toString(); } if("null".endsWith(value.trim())){ value=""; } return value; } public static boolean isExcel2007(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } /** * 去掉字符串右边的空格 * * @param str 要处理的字符串 * @return 处理后的字符串 */ public 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); }
3.测试代码
/** * 测试导入 */ @Test public void testImport(){ String savePath="C:\\Users\\WIN7\\Desktop\\mbbootstrap-angular\\Book1.xlsx"; try { File targetFile = new File(savePath); InputStream input = new FileInputStream(targetFile); List<ExcelSheetDto> listSheet=ExeclUtil.getExeclStringArray(input,"flight_inventory.xlsx"); List<FlightInventory> flightInventory=new ArrayList<>(); FlightInventory flightInventory1; System.out.println(listSheet.get(0).getSheetValue().length); for (int i = 0; i <listSheet.size(); i++) { //第一行循环多个sheet页 for (int j = 0; j<listSheet.get(i).getSheetValue().length; j++) { //循环多少行数据 flightInventory1=new FlightInventory(); flightInventory1.setOriginCity(listSheet.get(i).getSheetValue()[j][3]); //代表第i个sheet页,第j行第3列数据 flightInventory.add(flightInventory1); } } flightInventoryDao.batchInsertFlightInventory(flightInventory); //这里用的mabits批量插入, 不会的可以往下看,给出xml配置 System.out.println(flightInventory); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } }
4.xml 配置
<insert id="batchInsertFlightInventory" parameterType="java.util.List"> insert into text(name, age, sex ) values <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.name}, #{item.age}, #{item.sex} </foreach> </insert>
最后测试代码有删减,但整体逻辑和思路不变,如有问题,欢迎留言