java 导入excel 第三方工具 java excel导入导出工具类

1 packagecom.linrain.jcs.test;2
3
4 importjxl.Cell;5 importjxl.Sheet;6 importjxl.Workbook;7 importjxl.write.Label;8 importjxl.write.WritableSheet;9
10 importjava.io.InputStream;11 importjava.lang.reflect.Field;12 importjava.text.SimpleDateFormat;13 import java.util.*;14
15
16 /**
17 * @Description: 导入excel工具类18 * @CreateDate: 2019/1/24/0024$ 19:39$19 * @Version: 1.020 */
21 public classImportExcelUtil {22
23
24 /**
25 *@paramin ：承载着Excel的输入流26 *@param：要导入的工作表序号27 *@paramentityClass ：List中对象的类型(Excel中的每一行都要转化为该类型的对象)28 *@paramfieldMap ：Excel中的中文列头和类的英文属性的对应关系Map29 *@paramuniqueFields ：指定业务主键组合(即复合主键)，这些列的组合不能重复30 *@return：List31 *@throwsExcelException32 * @MethodName : excelToList33 * @Description : 将Excel转化为List34 */
35 public static List excelToList(InputStream in, String sheetName, ClassentityClass,36 LinkedHashMap fieldMap, String[] uniqueFields) throwsExcelException {37
38 //定义要返回的list
39 List resultList = new ArrayList();40
41 try{42
43 //根据Excel数据源创建WorkBook
44 Workbook wb =Workbook.getWorkbook(in);45 //获取工作表
46 Sheet sheet =wb.getSheet(sheetName);47
48 //获取工作表的有效行数
49 int realRows = 0;50 for (int i = 0; i < sheet.getRows(); i++) {51
52 int nullCols = 0;53 for (int j = 0; j < sheet.getColumns(); j++) {54 Cell currentCell =sheet.getCell(j, i);55 if (currentCell == null || "".equals(currentCell.getContents().toString())) {56 nullCols++;57 }58 }59
60 if (nullCols ==sheet.getColumns()) {61 break;62 } else{63 realRows++;64 }65 }66
67
68 //如果Excel中没有数据则提示错误
69 if (realRows <= 1) {70 throw new ExcelException("Excel文件中没有任何数据");71 }72
73
74 Cell[] firstRow = sheet.getRow(0);75
76 String[] excelFieldNames = newString[firstRow.length];77
78 //获取Excel中的列名
79 for (int i = 0; i < firstRow.length; i++) {80 excelFieldNames[i] =firstRow[i].getContents().toString().trim();81 }82
83 //判断需要的字段在Excel中是否都存在
84 boolean isExist = true;85 List excelFieldList =Arrays.asList(excelFieldNames);86 for(String cnName : fieldMap.keySet()) {87 if (!excelFieldList.contains(cnName)) {88 isExist = false;89 break;90 }91 }92
93 //如果有列名不存在，则抛出异常，提示错误
94 if (!isExist) {95 throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");96 }97
98
99 //将列名和列号放入Map中,这样通过列名就可以拿到列号
100 LinkedHashMap colMap = new LinkedHashMap();101 for (int i = 0; i < excelFieldNames.length; i++) {102 colMap.put(excelFieldNames[i], firstRow[i].getColumn());103 }104
105
106 //判断是否有重复行107 //1.获取uniqueFields指定的列
108 Cell[][] uniqueCells = newCell[uniqueFields.length][];109 for (int i = 0; i < uniqueFields.length; i++) {110 int col =colMap.get(uniqueFields[i]);111 uniqueCells[i] =sheet.getColumn(col);112 }113
114 //2.从指定列中寻找重复行
115 for (int i = 1; i < realRows; i++) {116 int nullCols = 0;117 for (int j = 0; j < uniqueFields.length; j++) {118 String currentContent =uniqueCells[j][i].getContents();119 Cell sameCell =sheet.findCell(currentContent,120 uniqueCells[j][i].getColumn(),121 uniqueCells[j][i].getRow() + 1,122 uniqueCells[j][i].getColumn(),123 uniqueCells[j][realRows - 1].getRow(),124 true);125 if (sameCell != null) {126 nullCols++;127 }128 }129
130 if (nullCols ==uniqueFields.length) {131 throw new ExcelException("Excel中有重复行，请检查");132 }133 }134
135 //将sheet转换为list
136 for (int i = 1; i < realRows; i++) {137 //新建要转换的对象
138 T entity =entityClass.newInstance();139
140 //给对象中的字段赋值
141 for (Map.Entryentry : fieldMap.entrySet()) {142 //获取中文字段名
143 String cnNormalName =entry.getKey();144 //获取英文字段名
145 String enNormalName =entry.getValue();146 //根据中文字段名获取列号
147 int col =colMap.get(cnNormalName);148
149 //获取当前单元格中的内容
150 String content =sheet.getCell(col, i).getContents().toString().trim();151
152 //给对象赋值
153 setFieldValueByName(enNormalName, content, entity);154 }155
156 resultList.add(entity);157 }158 } catch(Exception e) {159 e.printStackTrace();160 //如果是ExcelException，则直接抛出
161 if (e instanceofExcelException) {162 throw(ExcelException) e;163
164 //否则将其它异常包装成ExcelException再抛出
165 } else{166 e.printStackTrace();167 throw new ExcelException("导入Excel失败");168 }169 }170 returnresultList;171 }172
173 /**
174 * 根据字段名给对象的字段赋值175 *176 *@paramfieldName 字段名177 *@paramfieldValue 字段值178 *@paramo 对象179 *@throwsException 异常180 */
181 public static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throwsException {182
183 Field field =getFieldByName(fieldName, o.getClass());184 if (field != null) {185 field.setAccessible(true);186 //获取字段类型
187 Class> fieldType =field.getType();188
189 //根据字段类型给字段赋值
190 if (String.class ==fieldType) {191 field.set(o, String.valueOf(fieldValue));192 } else if ((Integer.TYPE == fieldType) || (Integer.class ==fieldType)) {193 field.set(o, Integer.parseInt(fieldValue.toString()));194 } else if ((Long.TYPE == fieldType) || (Long.class ==fieldType)) {195 field.set(o, Long.valueOf(fieldValue.toString()));196 } else if ((Float.TYPE == fieldType) || (Float.class ==fieldType)) {197 field.set(o, Float.valueOf(fieldValue.toString()));198 } else if ((Short.TYPE == fieldType) || (Short.class ==fieldType)) {199 field.set(o, Short.valueOf(fieldValue.toString()));200 } else if ((Double.TYPE == fieldType) || (Double.class ==fieldType)) {201 field.set(o, Double.valueOf(fieldValue.toString()));202 } else if (Character.TYPE ==fieldType) {203 if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {204 field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));205 }206 } else if (Date.class ==fieldType) {207 if (!fieldValue.toString().isEmpty()) {208 if (fieldValue.toString().length() > 10) {209
210 field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));211 } else{212 field.set(o, new SimpleDateFormat("yyyy-MM-dd").parse(fieldValue.toString()));213 }214 }215 } else{216 field.set(o, fieldValue);217 }218 } else{219 throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " +fieldName);220 }221 }222
223 /**
224 *@paramsourceSheet225 *@parameSheet 错误列表226 *@paramerrorMap 错误原因227 *@throwsException228 * @MethodName : addErrorRow229 * @Description : 添加一行错误列表230 */
231 private void addErrorRow(Sheet sourceSheet, WritableSheet eSheet, LinkedHashMap errorMap) throwsException {232
233 //复制错误的数据到错误列表
234 for (Map.Entryentry : errorMap.entrySet()) {235 int errorNo =entry.getKey();236 String reason =entry.getValue();237 int rows =eSheet.getRows();238 for (int i = 0; i < sourceSheet.getColumns(); i++) {239 System.out.println("错误列表当前列号" +i);240 eSheet.addCell(newLabel(i, rows, sourceSheet.getCell(i, errorNo).getContents()));241 }242
243 //添加错误原因和所在行号
244 eSheet.addCell(newLabel(sourceSheet.getColumns(), rows, reason));245 eSheet.addCell(new Label(sourceSheet.getColumns() + 1, rows, String.valueOf(errorNo + 1)));246
247 }248
249 }250
251 /**
252 * 设置工作表自动列宽和首行加粗253 *254 *@paramws 要设置格式的工作表255 *@paramextraWith 额外的宽度256 */
257 public static void setColumnAutoSize(WritableSheet ws, intextraWith) {258 //获取本列的最宽单元格的宽度
259 for (int i = 0; i < ws.getColumns(); i++) {260 int colWith = 0;261 for (int j = 0; j < ws.getRows(); j++) {262 String content =ws.getCell(i, j).getContents().toString();263 int cellWith =content.length();264 if (colWith 
269 ws.setColumnView(i, colWith +extraWith);270 }271
272 }273
274 /**
275 * 根据字段名获取字段276 *277 *@paramfieldName 字段名278 *@paramclazz 包含该字段的类279 *@return字段280 */
281 public static Field getFieldByName(String fieldName, Class>clazz) {282 //拿到本类的所有字段
283 Field[] selfFields =clazz.getDeclaredFields();284
285 //如果本类中存在该字段，则返回
286 for(Field field : selfFields) {287 if(field.getName().equals(fieldName)) {288 returnfield;289 }290 }291
292 //否则，查看父类中是否存在此字段，如果有则返回
293 Class> superClazz =clazz.getSuperclass();294 if (superClazz != null && superClazz != Object.class) {295 returngetFieldByName(fieldName, superClazz);296 }297
298 //如果本类和父类都没有，则返回空
299 return null;300 }301
302 /**
303 * 根据实体拿到该实体的所有属性304 *305 *@paramclazz 实体306 *@return返回属性的list集合307 */
308 public static List getSuperClassFieldByClass(Class>clazz) {309
310 List list = new ArrayList();311
312 //否则，查看父类中是否存在此字段，如果有则返回
313 Class> superClazz =clazz.getSuperclass();314
315 Field[] superFields =superClazz.getDeclaredFields();316 for(Field field : superFields) {317 list.add(field.getName());318 }319
320 //如果父类没有，则返回空
321 returnlist;322 }323
324
325 /**
326 *@paramclazz ：对象对应的类327 *@paramequalFields ：复合业务主键对应的map328 *@return查询到的对象329 * @MethodName : getObjByFields330 * @Description :根据复合业务主键查询对象331 */
332 private T getObjByFields(Class clazz, LinkedHashMapequalFields) {333
334 List list = null;335 if (equalFields.size() != 0) {336 //list=commonBean.findResultListByEqual(clazz, equalFields);
337 }338
339 return list == null || list.size() == 0 ? null : list.get(0);340 }341
342 /**
343 *@paramnormalFieldMap 普通字段Map344 *@paramreferFieldMap 引用字段Map345 *@return组合后的Map346 * @MethodName : combineFields347 * @Description : 组合普通和引用中英文字段Map348 */
349 private LinkedHashMap combineFields(LinkedHashMap normalFieldMap, LinkedHashMap>, LinkedHashMap>referFieldMap) {350
351 LinkedHashMap combineMap = new LinkedHashMap();352
353 //如果存在普通字段，则添加之
354 if (normalFieldMap != null && normalFieldMap.size() != 0) {355 combineMap.putAll(normalFieldMap);356 }357
358 //如果存在引用字段，则添加之
359 if (referFieldMap != null && referFieldMap.size() != 0) {360
361 //组建引用中英文字段Map
362 LinkedHashMap simpleReferFieldMap = new LinkedHashMap();363 for (Map.Entry>, LinkedHashMap>entry : referFieldMap.entrySet()) {364 LinkedHashMap> keyMap =entry.getKey();365 LinkedHashMap valueMap =entry.getValue();366
367 //获取引用中文字段名
368 String referField = "";369 for (Map.Entry>keyEntry : keyMap.entrySet()) {370 referField =keyEntry.getKey();371 break;372 }373
374 for (Map.EntryvalueEntry : valueMap.entrySet()) {375 String enField =valueEntry.getValue();376 String cnField =valueEntry.getKey();377 //拼接英文引用字段
378 String fullEnField = referField + "." +enField;379
380 //放入simpleReferFieldMap
381 simpleReferFieldMap.put(cnField, fullEnField);382 }383
384 }385
386 //放入combineMap
387 combineMap.putAll(simpleReferFieldMap);388 }389
390 returncombineMap;391
392 }393
394
395 }