导入导出Excel工具类ExcelUtil

2021-11-15 15:22:07

前言

前段时间做的分布式集成平台项目中，许多模块都用到了导入导出Excel的功能，于是决定封装一个ExcelUtil类，专门用来处理Excel的导入和导出

本项目的持久化层用的是JPA（底层用hibernate实现），所以导入和导出也都是基于实体类的。

在编写ExcelUtil之前，在网上查了一些资料。Java中用来处理Excel的第三方开源项目主要就是POI和JXL。poi功能强大，但是比较耗资源，对于大数据量的导入导出性能不是太好；jxl功能简单，但是性能比较好。

由于本项目的导入导出更多关注性能问题，而且jxl提供的功能基本也都够用了，于是选择了jxl作为支持。

实战

导出就是将List转化为Excel（listToExcel）

导入就是将Excel转化为List（excelToList）

导入导出中会出现各种各样的问题，比如：数据源为空、有重复行等，我自定义了一个ExcelException异常类，用来处理这些问题。

ExcelException类

  1. package common.tool.excel;
  2. public class ExcelException extends Exception {
  3. public ExcelException() {
  4. // TODO Auto-generated constructor stub
  5. }
  6. public ExcelException(String message) {
  7. super(message);
  8. // TODO Auto-generated constructor stub
  9. }
  10. public ExcelException(Throwable cause) {
  11. super(cause);
  12. // TODO Auto-generated constructor stub
  13. }
  14. public ExcelException(String message, Throwable cause) {
  15. super(message, cause);
  16. // TODO Auto-generated constructor stub
  17. }
  18. }

下面就是该文的主角ExcelUtil登场了，作为一个工具类，其内的所有方法都是静态的，方便使用。

ExcelUitl类

  1. /**
  2. * @author     : WH
  3. * @group      : tgb8
  4. * @Date       : 2014-1-2 下午9:13:21
  5. * @Comments   : 导入导出Excel工具类
  6. * @Version    : 1.0.0
  7. */
  8. public class ExcelUtil  {
  9. /**
  10. * @MethodName  : listToExcel
  11. * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，可自定义工作表大小）
  12. * @param list      数据源
  13. * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
  14. * 如果需要的是引用对象的属性，则英文属性使用类似于EL表达式的格式
  15. * 如：list中存放的都是student，student中又有college属性，而我们需要学院名称，则可以这样写
  16. * fieldMap.put("college.collegeName","学院名称")
  17. * @param sheetName 工作表的名称
  18. * @param sheetSize 每个工作表中记录的最大个数
  19. * @param out       导出流
  20. * @throws ExcelException
  21. */
  22. public static <T>  void   listToExcel (
  23. List<T> list ,
  24. LinkedHashMap<String,String> fieldMap,
  25. String sheetName,
  26. int sheetSize,
  27. OutputStream out
  28. ) throws ExcelException{
  29. if(list.size()==0 || list==null){
  30. throw new ExcelException("数据源中没有任何数据");
  31. }
  32. if(sheetSize>65535 || sheetSize<1){
  33. sheetSize=65535;
  34. }
  35. //创建工作簿并发送到OutputStream指定的地方
  36. WritableWorkbook wwb;
  37. try {
  38. wwb = Workbook.createWorkbook(out);
  39. //因为2003的Excel一个工作表最多可以有65536条记录，除去列头剩下65535条
  40. //所以如果记录太多，需要放到多个工作表中，其实就是个分页的过程
  41. //1.计算一共有多少个工作表
  42. double sheetNum=Math.ceil(list.size()/new Integer(sheetSize).doubleValue());
  43. //2.创建相应的工作表，并向其中填充数据
  44. for(int i=0; i<sheetNum; i++){
  45. //如果只有一个工作表的情况
  46. if(1==sheetNum){
  47. WritableSheet sheet=wwb.createSheet(sheetName, i);
  48. fillSheet(sheet, list, fieldMap, 0, list.size()-1);
  49. //有多个工作表的情况
  50. }else{
  51. WritableSheet sheet=wwb.createSheet(sheetName+(i+1), i);
  52. //获取开始索引和结束索引
  53. int firstIndex=i*sheetSize;
  54. int lastIndex=(i+1)*sheetSize-1>list.size()-1 ? list.size()-1 : (i+1)*sheetSize-1;
  55. //填充工作表
  56. fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
  57. }
  58. }
  59. wwb.write();
  60. wwb.close();
  61. }catch (Exception e) {
  62. e.printStackTrace();
  63. //如果是ExcelException，则直接抛出
  64. if(e instanceof ExcelException){
  65. throw (ExcelException)e;
  66. //否则将其它异常包装成ExcelException再抛出
  67. }else{
  68. throw new ExcelException("导出Excel失败");
  69. }
  70. }
  71. }
  72. /**
  73. * @MethodName  : listToExcel
  74. * @Description : 导出Excel（可以导出到本地文件系统，也可以导出到浏览器，工作表大小为2003支持的最大值）
  75. * @param list      数据源
  76. * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
  77. * @param out       导出流
  78. * @throws ExcelException
  79. */
  80. public static  <T>  void   listToExcel (
  81. List<T> list ,
  82. LinkedHashMap<String,String> fieldMap,
  83. String sheetName,
  84. OutputStream out
  85. ) throws ExcelException{
  86. listToExcel(list, fieldMap, sheetName, 65535, out);
  87. }
  88. /**
  89. * @MethodName  : listToExcel
  90. * @Description : 导出Excel（导出到浏览器，可以自定义工作表的大小）
  91. * @param list      数据源
  92. * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
  93. * @param sheetSize    每个工作表中记录的最大个数
  94. * @param response  使用response可以导出到浏览器
  95. * @throws ExcelException
  96. */
  97. public static  <T>  void   listToExcel (
  98. List<T> list ,
  99. LinkedHashMap<String,String> fieldMap,
  100. String sheetName,
  101. int sheetSize,
  102. HttpServletResponse response
  103. ) throws ExcelException{
  104. //设置默认文件名为当前时间：年月日时分秒
  105. String fileName=new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString();
  106. //设置response头信息
  107. response.reset();
  108. response.setContentType("application/vnd.ms-excel");        //改成输出excel文件
  109. response.setHeader("Content-disposition","attachment; filename="+fileName+".xls" );
  110. //创建工作簿并发送到浏览器
  111. try {
  112. OutputStream out=response.getOutputStream();
  113. listToExcel(list, fieldMap, sheetName, sheetSize,out );
  114. } catch (Exception e) {
  115. e.printStackTrace();
  116. //如果是ExcelException，则直接抛出
  117. if(e instanceof ExcelException){
  118. throw (ExcelException)e;
  119. //否则将其它异常包装成ExcelException再抛出
  120. }else{
  121. throw new ExcelException("导出Excel失败");
  122. }
  123. }
  124. }
  125. /**
  126. * @MethodName  : listToExcel
  127. * @Description : 导出Excel（导出到浏览器，工作表的大小是2003支持的最大值）
  128. * @param list      数据源
  129. * @param fieldMap      类的英文属性和Excel中的中文列名的对应关系
  130. * @param response  使用response可以导出到浏览器
  131. * @throws ExcelException
  132. */
  133. public static <T>  void   listToExcel (
  134. List<T> list ,
  135. LinkedHashMap<String,String> fieldMap,
  136. String sheetName,
  137. HttpServletResponse response
  138. ) throws ExcelException{
  139. listToExcel(list, fieldMap, sheetName, 65535, response);
  140. }
  141. /**
  142. * @MethodName          : excelToList
  143. * @Description             : 将Excel转化为List
  144. * @param in                    ：承载着Excel的输入流
  145. * @param sheetIndex        ：要导入的工作表序号
  146. * @param entityClass       ：List中对象的类型（Excel中的每一行都要转化为该类型的对象）
  147. * @param fieldMap          ：Excel中的中文列头和类的英文属性的对应关系Map
  148. * @param uniqueFields  ：指定业务主键组合（即复合主键），这些列的组合不能重复
  149. * @return                      ：List
  150. * @throws ExcelException
  151. */
  152. public static <T>  List<T>  excelToList(
  153. InputStream in,
  154. String sheetName,
  155. Class<T> entityClass,
  156. LinkedHashMap<String, String> fieldMap,
  157. String[] uniqueFields
  158. ) throws ExcelException{
  159. //定义要返回的list
  160. List<T> resultList=new ArrayList<T>();
  161. try {
  162. //根据Excel数据源创建WorkBook
  163. Workbook wb=Workbook.getWorkbook(in);
  164. //获取工作表
  165. Sheet sheet=wb.getSheet(sheetName);
  166. //获取工作表的有效行数
  167. int realRows=0;
  168. for(int i=0;i<sheet.getRows();i++){
  169. int nullCols=0;
  170. for(int j=0;j<sheet.getColumns();j++){
  171. Cell currentCell=sheet.getCell(j,i);
  172. if(currentCell==null || "".equals(currentCell.getContents().toString())){
  173. nullCols++;
  174. }
  175. }
  176. if(nullCols==sheet.getColumns()){
  177. break;
  178. }else{
  179. realRows++;
  180. }
  181. }
  182. //如果Excel中没有数据则提示错误
  183. if(realRows<=1){
  184. throw new ExcelException("Excel文件中没有任何数据");
  185. }
  186. Cell[] firstRow=sheet.getRow(0);
  187. String[] excelFieldNames=new String[firstRow.length];
  188. //获取Excel中的列名
  189. for(int i=0;i<firstRow.length;i++){
  190. excelFieldNames[i]=firstRow[i].getContents().toString().trim();
  191. }
  192. //判断需要的字段在Excel中是否都存在
  193. boolean isExist=true;
  194. List<String> excelFieldList=Arrays.asList(excelFieldNames);
  195. for(String cnName : fieldMap.keySet()){
  196. if(!excelFieldList.contains(cnName)){
  197. isExist=false;
  198. break;
  199. }
  200. }
  201. //如果有列名不存在，则抛出异常，提示错误
  202. if(!isExist){
  203. throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
  204. }
  205. //将列名和列号放入Map中,这样通过列名就可以拿到列号
  206. LinkedHashMap<String, Integer> colMap=new LinkedHashMap<String, Integer>();
  207. for(int i=0;i<excelFieldNames.length;i++){
  208. colMap.put(excelFieldNames[i], firstRow[i].getColumn());
  209. }
  210. //判断是否有重复行
  211. //1.获取uniqueFields指定的列
  212. Cell[][] uniqueCells=new Cell[uniqueFields.length][];
  213. for(int i=0;i<uniqueFields.length;i++){
  214. int col=colMap.get(uniqueFields[i]);
  215. uniqueCells[i]=sheet.getColumn(col);
  216. }
  217. //2.从指定列中寻找重复行
  218. for(int i=1;i<realRows;i++){
  219. int nullCols=0;
  220. for(int j=0;j<uniqueFields.length;j++){
  221. String currentContent=uniqueCells[j][i].getContents();
  222. Cell sameCell=sheet.findCell(currentContent,
  223. uniqueCells[j][i].getColumn(),
  224. uniqueCells[j][i].getRow()+1,
  225. uniqueCells[j][i].getColumn(),
  226. uniqueCells[j][realRows-1].getRow(),
  227. true);
  228. if(sameCell!=null){
  229. nullCols++;
  230. }
  231. }
  232. if(nullCols==uniqueFields.length){
  233. throw new ExcelException("Excel中有重复行，请检查");
  234. }
  235. }
  236. //将sheet转换为list
  237. for(int i=1;i<realRows;i++){
  238. //新建要转换的对象
  239. T entity=entityClass.newInstance();
  240. //给对象中的字段赋值
  241. for(Entry<String, String> entry : fieldMap.entrySet()){
  242. //获取中文字段名
  243. String cnNormalName=entry.getKey();
  244. //获取英文字段名
  245. String enNormalName=entry.getValue();
  246. //根据中文字段名获取列号
  247. int col=colMap.get(cnNormalName);
  248. //获取当前单元格中的内容
  249. String content=sheet.getCell(col, i).getContents().toString().trim();
  250. //给对象赋值
  251. setFieldValueByName(enNormalName, content, entity);
  252. }
  253. resultList.add(entity);
  254. }
  255. } catch(Exception e){
  256. e.printStackTrace();
  257. //如果是ExcelException，则直接抛出
  258. if(e instanceof ExcelException){
  259. throw (ExcelException)e;
  260. //否则将其它异常包装成ExcelException再抛出
  261. }else{
  262. e.printStackTrace();
  263. throw new ExcelException("导入Excel失败");
  264. }
  265. }
  266. return resultList;
  267. }
  268. /*<-------------------------辅助的私有方法----------------------------------------------->*/
  269. /**
  270. * @MethodName  : getFieldValueByName
  271. * @Description : 根据字段名获取字段值
  272. * @param fieldName 字段名
  273. * @param o 对象
  274. * @return  字段值
  275. */
  276. private static  Object getFieldValueByName(String fieldName, Object o) throws Exception{
  277. Object value=null;
  278. Field field=getFieldByName(fieldName, o.getClass());
  279. if(field !=null){
  280. field.setAccessible(true);
  281. value=field.get(o);
  282. }else{
  283. throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 "+fieldName);
  284. }
  285. return value;
  286. }
  287. /**
  288. * @MethodName  : getFieldByName
  289. * @Description : 根据字段名获取字段
  290. * @param fieldName 字段名
  291. * @param clazz 包含该字段的类
  292. * @return 字段
  293. */
  294. private static Field getFieldByName(String fieldName, Class<?>  clazz){
  295. //拿到本类的所有字段
  296. Field[] selfFields=clazz.getDeclaredFields();
  297. //如果本类中存在该字段，则返回
  298. for(Field field : selfFields){
  299. if(field.getName().equals(fieldName)){
  300. return field;
  301. }
  302. }
  303. //否则，查看父类中是否存在此字段，如果有则返回
  304. Class<?> superClazz=clazz.getSuperclass();
  305. if(superClazz!=null  &&  superClazz !=Object.class){
  306. return getFieldByName(fieldName, superClazz);
  307. }
  308. //如果本类和父类都没有，则返回空
  309. return null;
  310. }
  311. /**
  312. * @MethodName  : getFieldValueByNameSequence
  313. * @Description :
  314. * 根据带路径或不带路径的属性名获取属性值
  315. * 即接受简单属性名，如userName等，又接受带路径的属性名，如student.department.name等
  316. *
  317. * @param fieldNameSequence  带路径的属性名或简单属性名
  318. * @param o 对象
  319. * @return  属性值
  320. * @throws Exception
  321. */
  322. private static  Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception{
  323. Object value=null;
  324. //将fieldNameSequence进行拆分
  325. String[] attributes=fieldNameSequence.split("\\.");
  326. if(attributes.length==1){
  327. value=getFieldValueByName(fieldNameSequence, o);
  328. }else{
  329. //根据属性名获取属性对象
  330. Object fieldObj=getFieldValueByName(attributes[0], o);
  331. String subFieldNameSequence=fieldNameSequence.substring(fieldNameSequence.indexOf(".")+1);
  332. value=getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
  333. }
  334. return value;
  335. }
  336. /**
  337. * @MethodName  : setFieldValueByName
  338. * @Description : 根据字段名给对象的字段赋值
  339. * @param fieldName  字段名
  340. * @param fieldValue    字段值
  341. * @param o 对象
  342. */
  343. private static void setFieldValueByName(String fieldName,Object fieldValue,Object o) throws Exception{
  344. Field field=getFieldByName(fieldName, o.getClass());
  345. if(field!=null){
  346. field.setAccessible(true);
  347. //获取字段类型
  348. Class<?> fieldType = field.getType();
  349. //根据字段类型给字段赋值
  350. if (String.class == fieldType) {
  351. field.set(o, String.valueOf(fieldValue));
  352. } else if ((Integer.TYPE == fieldType)
  353. || (Integer.class == fieldType)) {
  354. field.set(o, Integer.parseInt(fieldValue.toString()));
  355. } else if ((Long.TYPE == fieldType)
  356. || (Long.class == fieldType)) {
  357. field.set(o, Long.valueOf(fieldValue.toString()));
  358. } else if ((Float.TYPE == fieldType)
  359. || (Float.class == fieldType)) {
  360. field.set(o, Float.valueOf(fieldValue.toString()));
  361. } else if ((Short.TYPE == fieldType)
  362. || (Short.class == fieldType)) {
  363. field.set(o, Short.valueOf(fieldValue.toString()));
  364. } else if ((Double.TYPE == fieldType)
  365. || (Double.class == fieldType)) {
  366. field.set(o, Double.valueOf(fieldValue.toString()));
  367. } else if (Character.TYPE == fieldType) {
  368. if ((fieldValue!= null) && (fieldValue.toString().length() > 0)) {
  369. field.set(o, Character
  370. .valueOf(fieldValue.toString().charAt(0)));
  371. }
  372. }else if(Date.class==fieldType){
  373. field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
  374. }else{
  375. field.set(o, fieldValue);
  376. }
  377. }else{
  378. throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 "+fieldName);
  379. }
  380. }
  381. /**
  382. * @MethodName  : setColumnAutoSize
  383. * @Description : 设置工作表自动列宽和首行加粗
  384. * @param ws
  385. */
  386. private static void setColumnAutoSize(WritableSheet ws,int extraWith){
  387. //获取本列的最宽单元格的宽度
  388. for(int i=0;i<ws.getColumns();i++){
  389. int colWith=0;
  390. for(int j=0;j<ws.getRows();j++){
  391. String content=ws.getCell(i,j).getContents().toString();
  392. int cellWith=content.length();
  393. if(colWith<cellWith){
  394. colWith=cellWith;
  395. }
  396. }
  397. //设置单元格的宽度为最宽宽度+额外宽度
  398. ws.setColumnView(i, colWith+extraWith);
  399. }
  400. }
  401. /**
  402. * @MethodName  : fillSheet
  403. * @Description : 向工作表中填充数据
  404. * @param sheet     工作表
  405. * @param list  数据源
  406. * @param fieldMap 中英文字段对应关系的Map
  407. * @param firstIndex    开始索引
  408. * @param lastIndex 结束索引
  409. */
  410. private static <T> void fillSheet(
  411. WritableSheet sheet,
  412. List<T> list,
  413. LinkedHashMap<String,String> fieldMap,
  414. int firstIndex,
  415. int lastIndex
  416. )throws Exception{
  417. //定义存放英文字段名和中文字段名的数组
  418. String[] enFields=new String[fieldMap.size()];
  419. String[] cnFields=new String[fieldMap.size()];
  420. //填充数组
  421. int count=0;
  422. for(Entry<String,String> entry:fieldMap.entrySet()){
  423. enFields[count]=entry.getKey();
  424. cnFields[count]=entry.getValue();
  425. count++;
  426. }
  427. //填充表头
  428. for(int i=0;i<cnFields.length;i++){
  429. Label label=new Label(i,0,cnFields[i]);
  430. sheet.addCell(label);
  431. }
  432. //填充内容
  433. int rowNo=1;
  434. for(int index=firstIndex;index<=lastIndex;index++){
  435. //获取单个对象
  436. T item=list.get(index);
  437. for(int i=0;i<enFields.length;i++){
  438. Object objValue=getFieldValueByNameSequence(enFields[i], item);
  439. String fieldValue=objValue==null ? "" : objValue.toString();
  440. Label label =new Label(i,rowNo,fieldValue);
  441. sheet.addCell(label);
  442. }
  443. rowNo++;
  444. }
  445. //设置自动列宽
  446. setColumnAutoSize(sheet, 5);
  447. }
  448. }

该工具类有4个重载的导出方法和1个导入方法，大家可以根据实际情况进行选择。

总结

导入和导出方法都是通过传一个fieldMap参数（类的英文属性和Excel的中文列头的对应关系）来连接实体类和Excel的

导出的时候可以选择导出到本地文件系统或导出到浏览器，也可以自定义每个工作表的大小

导入的时候可以自定义业务主键组合uniqueFields，这样就可以检测Excel中是否有重复行了

转自他人！！！！

上一篇：jsp语法与标签


下一篇：偷懒小工具 - Excel导出公共类