1 packagecn.yongche.utils;2
3 importjava.io.File;4 importjava.io.FileInputStream;5 importjava.io.IOException;6 importjava.io.InputStream;7 importjava.util.ArrayList;8 importjava.util.List;9 importorg.apache.poi.hssf.usermodel.HSSFCell;10 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;11 importorg.apache.poi.ss.usermodel.Cell;12 importorg.apache.poi.ss.usermodel.Row;13 importorg.apache.poi.ss.usermodel.Sheet;14 importorg.apache.poi.ss.usermodel.Workbook;15 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;16
17
25 public classImportExecl {26
27
30 public static void main(String[] args) throwsException {31 ImportExecl poi = newImportExecl();32 List> list = poi.read("E:/批量導入客戶模闆.xlsx");33 if (list != null) {34 for (int i = 0; i < list.size(); i++) {35 List cellList =list.get(i);36 for (int j = 0; j < cellList.size(); j++) {37 System.out.print(" " +cellList.get(j));38 }39 System.out.println();40 }41 }42 }43
44 //總行數
45 private int totalRows = 0;46
47 //總列數
48 private int totalCells = 0;49
50 //錯誤資訊
51 privateString errorInfo;52
53 //構造方法
54 publicImportExecl() {55 }56
57
60 public intgetTotalRows() {61 returntotalRows;62 }63
64
67 public intgetTotalCells() {68 returntotalCells;69 }70
71
74 publicString getErrorInfo() {75 returnerrorInfo;76 }77
78
81 public booleanvalidateExcel(String filePath) {82
83 if (filePath == null || !(CheckExcelUtil.isExcel2003(filePath) ||CheckExcelUtil.isExcel2007(filePath))) {84 errorInfo = "檔案名不是excel格式";85 return false;86 }87
88
89 File file = newFile(filePath);90 if (file == null || !file.exists()) {91 errorInfo = "檔案不存在";92 return false;93 }94 return true;95 }96
97
100 public List> read(String filePath) throwsIOException {101 List> dataLst = new ArrayList>();102 InputStream is = null;103 try{104
105 if (!validateExcel(filePath)) {106 System.out.println(errorInfo);107 return null;108 }109
110
111 boolean isExcel2003 = true;112 if(CheckExcelUtil.isExcel2007(filePath)) {113 isExcel2003 = false;114 }115
116
117 File file = newFile(filePath);118 is = newFileInputStream(file);119 dataLst =read(is, isExcel2003);120 is.close();121 is = null;122 } catch(Exception ex) {123 ex.printStackTrace();124 } finally{125 if (is != null) {126 try{127 is.close();128 } catch(IOException e) {129 is = null;130 e.printStackTrace();131 }132 }133 }134 returndataLst;135 }136
137
150 public List> read(InputStream inputStream, booleanisExcel2003) {151 List> dataLst = null;152 try{153
154 Workbook wb = null;155 if(isExcel2003) {156 wb = newHSSFWorkbook(inputStream);157 } else{158 wb = newXSSFWorkbook(inputStream);159 }160 dataLst =read(wb);161 } catch(IOException e) {162 e.printStackTrace();163 }164 returndataLst;165 }166
167
170 private List>read(Workbook wb) {171 List> dataLst = new ArrayList>();172 //得到第一個shell
173 Sheet sheet = wb.getSheetAt(0);174 //得到Excel的行數
175 this.totalRows =sheet.getPhysicalNumberOfRows();176 //得到Excel的列數
177 if (this.totalRows >= 1 && sheet.getRow(0) != null) {178 this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();179 }180
181 //循環Excel的行
182 for (int r = 0; r < this.totalRows; r++) {183 Row row =sheet.getRow(r);184 if (row == null) {185 continue;186 }187 List rowLst = new ArrayList();188 //循環Excel的列
189 for (int c = 0; c < this.getTotalCells(); c++) {190 Cell cell =row.getCell(c);191 String cellValue = "";192 if (null !=cell) {193 //以下是判斷資料的類型
194 switch(cell.getCellType()) {195 case HSSFCell.CELL_TYPE_NUMERIC: //數字
196 cellValue = cell.getNumericCellValue() + "";197 break;198
199 case HSSFCell.CELL_TYPE_STRING: //字元串
200 cellValue =cell.getStringCellValue();201 break;202
203 case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean
204 cellValue = cell.getBooleanCellValue() + "";205 break;206
207 case HSSFCell.CELL_TYPE_FORMULA: //公式
208 cellValue = cell.getCellFormula() + "";209 break;210
211 case HSSFCell.CELL_TYPE_BLANK: //空值
212 cellValue = "";213 break;214
215 case HSSFCell.CELL_TYPE_ERROR: //故障
216 cellValue = "非法字元";217 break;218
219 default:220 cellValue = "未知類型";221 break;222 }223 }224 rowLst.add(cellValue);225 }226
227 //儲存第r行的第c列
228 dataLst.add(rowLst);229 }230 returndataLst;231 }232
233 }234
235 classCheckExcelUtil {236
239 public static booleanisExcel2003(String filePath) {240 return filePath.matches("^.+\\.(?i)(xls)$");241 }242
243
246 public static booleanisExcel2007(String filePath) {247 return filePath.matches("^.+\\.(?i)(xlsx)$");248 }249 }