java中excel导入\导出工具类

1.导入工具

 package com.linrain.jcs.test;

 import jxl.Cell;
 import jxl.Sheet;
 import jxl.Workbook;
 import jxl.write.Label;
 import jxl.write.WritableSheet;

 import java.io.InputStream;
 import java.lang.reflect.Field;
 import java.text.SimpleDateFormat;
 import java.util.*;

 /**
  * @Description: 导入excel工具类
  * @CreateDate: 2019/1/24/0024$ 19:39$
  * @Version: 1.0
  */
 public class ImportExcelUtil {

     /**
      * @param in           ：承载着Excel的输入流
      * @param ：要导入的工作表序号
      * @param entityClass  ：List中对象的类型（Excel中的每一行都要转化为该类型的对象）
      * @param fieldMap     ：Excel中的中文列头和类的英文属性的对应关系Map
      * @param uniqueFields ：指定业务主键组合（即复合主键），这些列的组合不能重复
      * @return ：List
      * @throws ExcelException
      * @MethodName : excelToList
      * @Description : 将Excel转化为List
      */
     public static <T> List<T> excelToList(InputStream in, String sheetName, Class<T> entityClass,
                                           LinkedHashMap<String, String> fieldMap, String[] uniqueFields) throws ExcelException {

         //定义要返回的list
         List<T> resultList = new ArrayList<T>();

         try {

             //根据Excel数据源创建WorkBook
             Workbook wb = Workbook.getWorkbook(in);
             //获取工作表
             Sheet sheet = wb.getSheet(sheetName);

             //获取工作表的有效行数
             int realRows = 0;
             for (int i = 0; i < sheet.getRows(); i++) {

                 int nullCols = 0;
                 for (int j = 0; j < sheet.getColumns(); j++) {
                     Cell currentCell = sheet.getCell(j, i);
                     if (currentCell == null || "".equals(currentCell.getContents().toString())) {
                         nullCols++;
                     }
                 }

                 if (nullCols == sheet.getColumns()) {
                     break;
                 } else {
                     realRows++;
                 }
             }

             //如果Excel中没有数据则提示错误
             if (realRows <= 1) {
                 throw new ExcelException("Excel文件中没有任何数据");
             }

             Cell[] firstRow = sheet.getRow(0);

             String[] excelFieldNames = new String[firstRow.length];

             //获取Excel中的列名
             for (int i = 0; i < firstRow.length; i++) {
                 excelFieldNames[i] = firstRow[i].getContents().toString().trim();
             }

             //判断需要的字段在Excel中是否都存在
             boolean isExist = true;
             List<String> excelFieldList = Arrays.asList(excelFieldNames);
             for (String cnName : fieldMap.keySet()) {
                 if (!excelFieldList.contains(cnName)) {
                     isExist = false;
                     break;
                 }
             }

             //如果有列名不存在，则抛出异常，提示错误
             if (!isExist) {
                 throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
             }

             //将列名和列号放入Map中,这样通过列名就可以拿到列号
             LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
             for (int i = 0; i < excelFieldNames.length; i++) {
                 colMap.put(excelFieldNames[i], firstRow[i].getColumn());
             }

             //判断是否有重复行
             //1.获取uniqueFields指定的列
             Cell[][] uniqueCells = new Cell[uniqueFields.length][];
             for (int i = 0; i < uniqueFields.length; i++) {
                 int col = colMap.get(uniqueFields[i]);
                 uniqueCells[i] = sheet.getColumn(col);
             }

             //2.从指定列中寻找重复行
             for (int i = 1; i < realRows; i++) {
                 int nullCols = 0;
                 for (int j = 0; j < uniqueFields.length; j++) {
                     String currentContent = uniqueCells[j][i].getContents();
                     Cell sameCell = sheet.findCell(currentContent,
                             uniqueCells[j][i].getColumn(),
                             uniqueCells[j][i].getRow() + 1,
                             uniqueCells[j][i].getColumn(),
                             uniqueCells[j][realRows - 1].getRow(),
                             true);
                     if (sameCell != null) {
                         nullCols++;
                     }
                 }

                 if (nullCols == uniqueFields.length) {
                     throw new ExcelException("Excel中有重复行，请检查");
                 }
             }

             //将sheet转换为list
             for (int i = 1; i < realRows; i++) {
                 //新建要转换的对象
                 T entity = entityClass.newInstance();

                 //给对象中的字段赋值
                 for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
                     //获取中文字段名
                     String cnNormalName = entry.getKey();
                     //获取英文字段名
                     String enNormalName = entry.getValue();
                     //根据中文字段名获取列号
                     int col = colMap.get(cnNormalName);

                     //获取当前单元格中的内容
                     String content = sheet.getCell(col, i).getContents().toString().trim();

                     //给对象赋值
                     setFieldValueByName(enNormalName, content, entity);
                 }

                 resultList.add(entity);
             }
         } catch (Exception e) {
             e.printStackTrace();
             //如果是ExcelException，则直接抛出
             if (e instanceof ExcelException) {
                 throw (ExcelException) e;

                 //否则将其它异常包装成ExcelException再抛出
             } else {
                 e.printStackTrace();
                 throw new ExcelException("导入Excel失败");
             }
         }
         return resultList;
     }

     /**
      * 根据字段名给对象的字段赋值
      *
      * @param fieldName  字段名
      * @param fieldValue 字段值
      * @param o          对象
      * @throws Exception 异常
      */
     public static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {

         Field field = getFieldByName(fieldName, o.getClass());
         if (field != null) {
             field.setAccessible(true);
             // 获取字段类型
             Class<?> fieldType = field.getType();

             // 根据字段类型给字段赋值
             if (String.class == fieldType) {
                 field.set(o, String.valueOf(fieldValue));
             } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                 field.set(o, Integer.parseInt(fieldValue.toString()));
             } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                 field.set(o, Long.valueOf(fieldValue.toString()));
             } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                 field.set(o, Float.valueOf(fieldValue.toString()));
             } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
                 field.set(o, Short.valueOf(fieldValue.toString()));
             } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                 field.set(o, Double.valueOf(fieldValue.toString()));
             } else if (Character.TYPE == fieldType) {
                 if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
                     field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));
                 }
             } else if (Date.class == fieldType) {
                 if (!fieldValue.toString().isEmpty()) {
                     if (fieldValue.toString().length() > 10) {

                         field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
                     } else {
                         field.set(o, new SimpleDateFormat("yyyy-MM-dd").parse(fieldValue.toString()));
                     }
                 }
             } else {
                 field.set(o, fieldValue);
             }
         } else {
             throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
         }
     }

     /**
      * @param sourceSheet
      * @param eSheet      错误列表
      * @param errorMap    错误原因
      * @throws Exception
      * @MethodName : addErrorRow
      * @Description : 添加一行错误列表
      */
     private void addErrorRow(Sheet sourceSheet, WritableSheet eSheet, LinkedHashMap<Integer, String> errorMap) throws Exception {

         // 复制错误的数据到错误列表
         for (Map.Entry<Integer, String> entry : errorMap.entrySet()) {
             int errorNo = entry.getKey();
             String reason = entry.getValue();
             int rows = eSheet.getRows();
             for (int i = 0; i < sourceSheet.getColumns(); i++) {
                 System.out.println("错误列表当前列号" + i);
                 eSheet.addCell(new Label(i, rows, sourceSheet.getCell(i, errorNo).getContents()));
             }

             // 添加错误原因和所在行号
             eSheet.addCell(new Label(sourceSheet.getColumns(), rows, reason));
             eSheet.addCell(new Label(sourceSheet.getColumns() + 1, rows, String.valueOf(errorNo + 1)));

         }

     }

     /**
      * 设置工作表自动列宽和首行加粗
      *
      * @param ws        要设置格式的工作表
      * @param extraWith 额外的宽度
      */
     public static void setColumnAutoSize(WritableSheet ws, int extraWith) {
         // 获取本列的最宽单元格的宽度
         for (int i = 0; i < ws.getColumns(); i++) {
             int colWith = 0;
             for (int j = 0; j < ws.getRows(); j++) {
                 String content = ws.getCell(i, j).getContents().toString();
                 int cellWith = content.length();
                 if (colWith < cellWith) {
                     colWith = cellWith;
                 }
             }
             // 设置单元格的宽度为最宽宽度+额外宽度
             ws.setColumnView(i, colWith + extraWith);
         }

     }

     /**
      * 根据字段名获取字段
      *
      * @param fieldName 字段名
      * @param clazz     包含该字段的类
      * @return 字段
      */
     public static Field getFieldByName(String fieldName, Class<?> clazz) {
         // 拿到本类的所有字段
         Field[] selfFields = clazz.getDeclaredFields();

         // 如果本类中存在该字段，则返回
         for (Field field : selfFields) {
             if (field.getName().equals(fieldName)) {
                 return field;
             }
         }

         // 否则，查看父类中是否存在此字段，如果有则返回
         Class<?> superClazz = clazz.getSuperclass();
         if (superClazz != null && superClazz != Object.class) {
             return getFieldByName(fieldName, superClazz);
         }

         // 如果本类和父类都没有，则返回空
         return null;
     }

     /**
      * 根据实体拿到该实体的所有属性
      *
      * @param clazz 实体
      * @return 返回属性的list集合
      */
     public static List<String> getSuperClassFieldByClass(Class<?> clazz) {

         List<String> list = new ArrayList<String>();

         // 否则，查看父类中是否存在此字段，如果有则返回
         Class<?> superClazz = clazz.getSuperclass();

         Field[] superFields = superClazz.getDeclaredFields();
         for (Field field : superFields) {
             list.add(field.getName());
         }

         // 如果父类没有，则返回空
         return list;
     }

     /**
      * @param clazz       ：对象对应的类
      * @param equalFields ：复合业务主键对应的map
      * @return 查询到的对象
      * @MethodName : getObjByFields
      * @Description :根据复合业务主键查询对象
      */
     private <T> T getObjByFields(Class<T> clazz, LinkedHashMap<Object, Object> equalFields) {

         List<T> list = null;
         if (equalFields.size() != 0) {
             // list=commonBean.findResultListByEqual(clazz, equalFields);
         }

         return list == null || list.size() == 0 ? null : list.get(0);
     }

     /**
      * @param normalFieldMap 普通字段Map
      * @param referFieldMap  引用字段Map
      * @return 组合后的Map
      * @MethodName : combineFields
      * @Description : 组合普通和引用中英文字段Map
      */
     private LinkedHashMap<String, String> combineFields(LinkedHashMap<String, String> normalFieldMap, LinkedHashMap<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> referFieldMap) {

         LinkedHashMap<String, String> combineMap = new LinkedHashMap<String, String>();

         // 如果存在普通字段，则添加之
         if (normalFieldMap != null && normalFieldMap.size() != 0) {
             combineMap.putAll(normalFieldMap);
         }

         // 如果存在引用字段，则添加之
         if (referFieldMap != null && referFieldMap.size() != 0) {

             // 组建引用中英文字段Map
             LinkedHashMap<String, String> simpleReferFieldMap = new LinkedHashMap<String, String>();
             for (Map.Entry<LinkedHashMap<String, Class<?>>, LinkedHashMap<String, String>> entry : referFieldMap.entrySet()) {
                 LinkedHashMap<String, Class<?>> keyMap = entry.getKey();
                 LinkedHashMap<String, String> valueMap = entry.getValue();

                 // 获取引用中文字段名
                 String referField = "";
                 for (Map.Entry<String, Class<?>> keyEntry : keyMap.entrySet()) {
                     referField = keyEntry.getKey();
                     break;
                 }

                 for (Map.Entry<String, String> valueEntry : valueMap.entrySet()) {
                     String enField = valueEntry.getValue();
                     String cnField = valueEntry.getKey();
                     // 拼接英文引用字段
                     String fullEnField = referField + "." + enField;

                     // 放入simpleReferFieldMap
                     simpleReferFieldMap.put(cnField, fullEnField);
                 }

             }

             // 放入combineMap
             combineMap.putAll(simpleReferFieldMap);
         }

         return combineMap;

     }

 }

2.导出工具

 package com.linrain.jcs.test;

 import org.apache.commons.lang.StringUtils;
 import org.apache.poi.hssf.usermodel.HSSFCell;
 import org.apache.poi.hssf.usermodel.HSSFRow;
 import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;

 import javax.servlet.http.HttpServletResponse;
 import java.io.*;
 import java.util.List;
 import java.util.Map;

 /**
  * @Description: 导出excel工具类
  * @CreateDate: 2019/1/24/ 17:18$
  * @Version: 1.0
  */
 public class ExportExcelUtil {
     /**
      * 常用普通文件下载
      * @param response
      * @param fileName
      * @param sheetName
      * @param data
      */
     public static void down(HttpServletResponse response, String fileName, String sheetName, List<Map<String, Object>> data) {
         // 生成提示信息，
         response.setContentType("application/vnd.ms-excel");
         if (StringUtils.isBlank(fileName)) {
             fileName = Long.toString(System.currentTimeMillis());
         }
         OutputStream fOut = null;
         try {
             // 进行转码，使其支持中文文件名
             fOut = response.getOutputStream();
             String codedFileName = new String(fileName.getBytes("gbk"), "iso-8859-1");
             response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
             HSSFWorkbook workbook = new HSSFWorkbook();
             // 产生工作表对象
             HSSFSheet sheet = workbook.createSheet(sheetName);

             for (int i = 0; i < data.size(); i++) {
                 HSSFRow row = sheet.createRow((int) i);
                 Map<String, Object> map = data.get(i);

                 //遍历map中的值
                 int j = 0;
                 for (Object value : map.values()) {
                     HSSFCell cell = row.createCell((int) j);
                     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                     if (value == null) {
                         value = "";
                     }
                     cell.setCellValue(value.toString());
                     j++;
                 }
             }
             workbook.write(fOut);
         } catch (Exception e) {
             e.printStackTrace();
         } finally {
             try {
                 fOut.flush();
                 fOut.close();
             } catch (IOException e) {
             }
         }
     }
 }