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 }