pom依賴 導入poi依賴

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency>

工具類，里面也有main方法
注意：表格的列值，不同有的是整數，有的日期，有的是字符串‘
使用 row.getCell(0).setCellType(CellType.NUMERIC); 進行列格式設置
setCellType 自己點進去看類型把

package com.sxjlrj.common.util;import java.io.File; import java.io.FileInputStream; import java.math.BigDecimal; import java.util.*;import com.sxjlrj.manager.censusManager.model.DtStatisticBusiness; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil {public static void main(String[] args) {File file = new File("C:\\Users\\gaoyuan\\Desktop\\2017年太原(1).xls");// File file = new File("E:/2.xls");try {List<DtStatisticBusiness> list = importExcel(file);} catch (Exception e) {e.printStackTrace();}}public static List<DtStatisticBusiness> importExcel(File file) throws Exception {Workbook wb = null;String fileName = file.getName();// 讀取上傳文件(excel)的名字，含后綴后// 根據文件后綴名不同(xls和xlsx)獲得不同的Workbook實現類對象Iterator<Sheet> sheets = null;List<DtStatisticBusiness> returnlist = new ArrayList<DtStatisticBusiness>();try {if (fileName.endsWith("xls")) {wb = new HSSFWorkbook(new FileInputStream(file));sheets = wb.iterator();} else if (fileName.endsWith("xlsx")) {wb = new XSSFWorkbook(new FileInputStream(file));sheets = wb.iterator();}if (sheets == null) {throw new Exception("excel中不含有sheet工作表");}// 遍歷excel里每個sheet的數據。while (sheets.hasNext()) {Sheet sheet = sheets.next();String sheetName = sheet.getSheetName();//讀Sheet2的數據if(sheetName.equals("2020")){returnlist =getCellValue(sheet);}}} catch (Exception ex) {throw ex;} finally {if (wb != null) wb.close();}return returnlist;}// 獲取每一個Sheet工作表中的數。private static List<DtStatisticBusiness> getCellValue(Sheet sheet) {List<DtStatisticBusiness> list = new ArrayList<DtStatisticBusiness>();// sheet.getPhysicalNumberOfRows():獲取的是物理行數，也就是不包括那些空行（隔行）的情況for (int i = (sheet.getFirstRowNum()+1); i < sheet.getPhysicalNumberOfRows(); i++) {// 獲得第i行對象Row row = sheet.getRow(i);if (row == null) {continue;} else {// getLastCellNum：獲取列數(比最后一列列標大1)// 通常導入的excel是符合需求規范的，這里因為我們準備的excel列數是7列，如果小于7列說明傳入的excel文件有問題。if (row.getLastCellNum() < 4) {continue;}//int j = row.getFirstCellNum();// 獲取第i行第一個單元格的下標// 第一列=日期//判斷頭部if (row.getCell(0).getCellTypeEnum().equals(CellType.STRING)){continue;}//封裝數據DtStatisticBusiness dtStatisticBusiness = new DtStatisticBusiness();row.getCell(0).setCellType(CellType.NUMERIC);//row.getCell(j).setCellType(CellType.STRING);Date dateCellValue = row.getCell(0).getDateCellValue();if (StringUtil.isEmpty(dateCellValue)||dateCellValue.equals("總計")||dateCellValue.equals("日期")) {continue;}dtStatisticBusiness.setReportDate(dateCellValue);//map.put("schoolName", schoolName);//第二列=運輸起架次row.getCell(1).setCellType(CellType.STRING);String transportCell = row.getCell(1).getStringCellValue();if (StringUtil.isEmpty(transportCell)) {continue;}dtStatisticBusiness.setTransportPre(new BigDecimal(transportCell));dtStatisticBusiness.setTransportRev(new BigDecimal(transportCell));//第三列=旅客吞吐量row.getCell(2).setCellType(CellType.STRING);String passengerCell = row.getCell(2).getStringCellValue();if (StringUtil.isEmpty(passengerCell)) {continue;}BigDecimal passengerDivide = new BigDecimal(passengerCell).divide(new BigDecimal(10000));dtStatisticBusiness.setPassengerPre(passengerDivide);dtStatisticBusiness.setPassengerRev(passengerDivide);//第四列=貨郵吞吐量row.getCell(3).setCellType(CellType.STRING);String cargoCell = row.getCell(3).getStringCellValue();if (StringUtil.isEmpty(cargoCell)) {continue;}BigDecimal cargoDivide = new BigDecimal(cargoCell).divide(new BigDecimal(1000));dtStatisticBusiness.setCargoRev(cargoDivide);dtStatisticBusiness.setCargoPre(cargoDivide);list.add(dtStatisticBusiness);// System.out.println("日期="+dateCellValue+"運輸起架次="+transportCell+"旅客吞吐量="+passengerCell+"貨郵吞吐量="+cargoCell);/* //map.put("name", name);Cell cell = row.getCell(j++);// 學號(在excel中，學號可能是純數字，也可能是字母在數字)if (cell.getCellTypeEnum().equals(CellType.STRING)) {// 如果是字符串，直接獲取值map.put("sn", cell.getStringCellValue());} else if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {// 如果excel中學號是純數字，獲取的值是數值類型，再toPlainString()轉為字符類型，因為數據庫中學號字段需要是字符類型。map.put("sn", new BigDecimal(cell.getNumericCellValue()).toPlainString());}cell = row.getCell(j++);// 年級(數據庫中，年級是int類型,所以這里我們轉換為int)if (cell.getCellTypeEnum().equals(CellType.STRING)) {try {String gradeStr = cell.getStringCellValue();//int grade = Integer.parseInt(gradeStr);map.put("grade", grade);} catch (Exception e) {continue;}} else if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {map.put("grade", (int) cell.getNumericCellValue());}cell = row.getCell(j++);// 班級(數據庫中，年級是int類型,所以這里我們轉換為int)if (cell.getCellTypeEnum().equals(CellType.STRING)) {try {String klassStr = cell.getStringCellValue();int klass = Integer.parseInt(klassStr);map.put("klass", klass);} catch (Exception e) {continue;}} else {map.put("klass", (int) cell.getNumericCellValue());}String genderStr = row.getCell(j++).getStringCellValue();int gender = "男".equals(genderStr) ? 1 : "女".equals(genderStr) ? 0 : 2;map.put("gender", gender);if (j <= row.getLastCellNum()) {cell = row.getCell(j++);// 手機號if (cell.getCellTypeEnum().equals(CellType.STRING))map.put("phone", cell.getStringCellValue());else if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) map.put("phone",new BigDecimal(cell.getNumericCellValue()).toPlainString());}list.add(map);*/}}return list;}}

總結

以上是生活随笔為你收集整理的java Poi导入exel表格的数据，入库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。