javaExcel表格导入导出

主要为 poi 和 jxl 依赖&＃xff0c;如下&＃xff1a;

org.apache.poipoi3.17net.sourceforge.jexcelapijxl2.6.12

核心代码

调用方

后台接收前端传递回来的文件地址&＃xff0c;生成字节流&＃xff1a;

public ReturnDTO importUserDetails(String path) {ReturnDTO returnDTO &＃61; new ReturnDTO();try {path &＃61; DirectoryConst.FILE_DIR &＃43; path.replace("/files", ""); // 判断文件是否是Excel(2003、2007)String suffix &＃61; path.substring(path.lastIndexOf("."), path.length());if (".xls".equalsIgnoreCase(suffix) || ".xlsx".equalsIgnoreCase(suffix)) {// 2003后缀或2007后缀List userList &＃61; ExcelUtil.excelToList(new FileInputStream(path),"Sheet1",BkPassportInfoDetailDTO.class,ExcelFieldMap.getFieldMapIn());System.out.println(userList.toString());returnDTO.setResult(commonQueryDAO.importUserDetails(userList));returnDTO.setStatus(StatusConsts.STATUS_SUCCESS);returnDTO.setReturnMsg(StatusConsts.STATUS_SUCCESS_MESSAGE);}else {returnDTO.setStatus(StatusConsts.STATUS_PARAMETER_ERROR);returnDTO.setReturnMsg(StatusConsts.STATUS_PARAMETER_ERROR_MESSAGE);}}catch(Exception e) {e.printStackTrace();returnDTO.setStatus(StatusConsts.STATUS_FAILURE);returnDTO.setReturnMsg(StatusConsts.STATUS_FAILURE_MESSAGE);}return returnDTO;}


ExcelUtil

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
//import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;/*** &＃64;Comments : 导入导出Excel工具类*/public class ExcelUtil {/*** &＃64;param list 数据源* &＃64;param fieldMap 类的英文属性和Excel中的中文列名的对应关系* 如果需要的是引用对象的属性&＃xff0c;则英文属性使用类似于EL表达式的格式* 如&＃xff1a;list中存放的都是student&＃xff0c;student中又有college属性&＃xff0c;而我们需要学院名称&＃xff0c;则可以这样写* fieldMap.put("college.collegeName","学院名称")* &＃64;param sheetName 工作表的名称* &＃64;param sheetSize 每个工作表中记录的最大个数* &＃64;param out 导出流* &＃64;throws ExcelException* &＃64;MethodName : listToExcel* &＃64;Description : 导出Excel&＃xff08;可以导出到本地文件系统&＃xff0c;也可以导出到浏览器&＃xff0c;可自定义工作表大小&＃xff09;*/public static void listToExcel(List list,LinkedHashMap fieldMap,String sheetName,int sheetSize,OutputStream out) throws ExcelException {if (list.size() &＃61;&＃61; 0 || list &＃61;&＃61; null) {throw new ExcelException("数据源中没有任何数据");}if (sheetSize > 65535 || sheetSize <1) {sheetSize &＃61; 65535;}//创建工作簿并发送到OutputStream指定的地方WritableWorkbook wwb;try {wwb &＃61; Workbook.createWorkbook(out);//因为2003的Excel一个工作表最多可以有65536条记录&＃xff0c;除去列头剩下65535条//所以如果记录太多&＃xff0c;需要放到多个工作表中&＃xff0c;其实就是个分页的过程//1.计算一共有多少个工作表double sheetNum &＃61; Math.ceil(list.size() / new Integer(sheetSize).doubleValue());//2.创建相应的工作表&＃xff0c;并向其中填充数据for (int i &＃61; 0; i list.size() - 1 ? list.size() - 1 : (i &＃43; 1) * sheetSize - 1;//填充工作表fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);}}wwb.write();wwb.close();} catch (Exception e) {e.printStackTrace();//如果是ExcelException&＃xff0c;则直接抛出if (e instanceof ExcelException) {throw (ExcelException) e;//否则将其它异常包装成ExcelException再抛出} else {throw new ExcelException("导出Excel失败");}}}/*** &＃64;param list 数据源* &＃64;param fieldMap 类的英文属性和Excel中的中文列名的对应关系* &＃64;param out 导出流* &＃64;throws ExcelException* &＃64;MethodName : listToExcel* &＃64;Description : 导出Excel&＃xff08;可以导出到本地文件系统&＃xff0c;也可以导出到浏览器&＃xff0c;工作表大小为2003支持的最大值&＃xff09;*/public static void listToExcel(List list,LinkedHashMap fieldMap,String sheetName,OutputStream out) throws ExcelException {listToExcel(list, fieldMap, sheetName, 65535, out);}/*** 不去重*/public static List excelToList(InputStream in,String sheetName,Class entityClass,LinkedHashMap fieldMap) throws ExcelException {return excelToList(in, sheetName, entityClass, fieldMap, null);}/*** 去重复字段 需要把重复字段写出来** &＃64;param in &＃xff1a;承载着Excel的输入流* &＃64;param entityClass &＃xff1a;List中对象的类型&＃xff08;Excel中的每一行都要转化为该类型的对象&＃xff09;* &＃64;param fieldMap &＃xff1a;Excel中的中文列头和类的英文属性的对应关系Map* &＃64;param uniqueFields &＃xff1a;指定业务主键组合&＃xff08;即复合主键&＃xff09;&＃xff0c;这些列的组合不能重复* &＃64;return &＃xff1a;List* &＃64;throws ExcelException* &＃64;MethodName : excelToList* &＃64;Description : 将Excel转化为List*/public static List excelToList(InputStream in,String sheetName,Class entityClass,LinkedHashMap fieldMap,String[] uniqueFields) throws ExcelException {//定义要返回的listList resultList &＃61; new ArrayList();try {//根据Excel数据源创建WorkBookWorkbook wb &＃61; Workbook.getWorkbook(in);//获取工作表Sheet sheet &＃61; wb.getSheet(sheetName);//获取工作表的有效行数int realRows &＃61; 0;for (int i &＃61; 0; i excelFieldList &＃61; Arrays.asList(excelFieldNames);for (String cnName : fieldMap.keySet()) {if (!excelFieldList.contains(cnName)) {isExist &＃61; false;break;}}//如果有列名不存在&＃xff0c;则抛出异常&＃xff0c;提示错误if (!isExist) {throw new ExcelException("Excel中缺少必要的字段&＃xff0c;或字段名称有误");}//将列名和列号放入Map中,这样通过列名就可以拿到列号LinkedHashMap colMap &＃61; new LinkedHashMap();for (int i &＃61; 0; i entry : fieldMap.entrySet()) {//获取中文字段名String cnNormalName &＃61; entry.getKey();//获取英文字段名String enNormalName &＃61; entry.getValue();//根据中文字段名获取列号int col &＃61; colMap.get(cnNormalName);//获取当前单元格中的内容String content &＃61; sheet.getCell(col, i).getContents().toString().trim();//给对象赋值setFieldValueByName(enNormalName, content, entity);}resultList.add(entity);}} catch (Exception e) {e.printStackTrace();//如果是ExcelException&＃xff0c;则直接抛出if (e instanceof ExcelException) {throw (ExcelException) e;//否则将其它异常包装成ExcelException再抛出} else {e.printStackTrace();throw new ExcelException("导入Excel失败");}}return resultList;}/*<-------------------------辅助的私有方法----------------------------------------------->*//*** &＃64;param fieldName 字段名* &＃64;param o 对象* &＃64;return 字段值* &＃64;MethodName : getFieldValueByName* &＃64;Description : 根据字段名获取字段值*/private static Object getFieldValueByName(String fieldName, Object o) throws Exception {Object value &＃61; null;Field field &＃61; getFieldByName(fieldName, o.getClass());if (field !&＃61; null) {field.setAccessible(true);value &＃61; field.get(o);} else {throw new ExcelException(o.getClass().getSimpleName() &＃43; "类不存在字段名 " &＃43; fieldName);}return value;}/*** &＃64;param fieldName 字段名* &＃64;param clazz 包含该字段的类* &＃64;return 字段* &＃64;MethodName : getFieldByName* &＃64;Description : 根据字段名获取字段*/private static Field getFieldByName(String fieldName, Class clazz) {//拿到本类的所有字段Field[] selfFields &＃61; clazz.getDeclaredFields();//如果本类中存在该字段&＃xff0c;则返回for (Field field : selfFields) {if (field.getName().equals(fieldName)) {return field;}}//否则&＃xff0c;查看父类中是否存在此字段&＃xff0c;如果有则返回Class superClazz &＃61; clazz.getSuperclass();if (superClazz !&＃61; null && superClazz !&＃61; Object.class) {return getFieldByName(fieldName, superClazz);}//如果本类和父类都没有&＃xff0c;则返回空return null;}/*** &＃64;param fieldNameSequence 带路径的属性名或简单属性名* &＃64;param o 对象* &＃64;return 属性值* &＃64;throws Exception* &＃64;MethodName : getFieldValueByNameSequence* &＃64;Description :* 根据带路径或不带路径的属性名获取属性值* 即接受简单属性名&＃xff0c;如userName等&＃xff0c;又接受带路径的属性名&＃xff0c;如student.department.name等*/private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throws Exception {Object value &＃61; null;//将fieldNameSequence进行拆分String[] attributes &＃61; fieldNameSequence.split("\\.");if (attributes.length &＃61;&＃61; 1) {value &＃61; getFieldValueByName(fieldNameSequence, o);} else {//根据属性名获取属性对象Object fieldObj &＃61; getFieldValueByName(attributes[0], o);String subFieldNameSequence &＃61; fieldNameSequence.substring(fieldNameSequence.indexOf(".") &＃43; 1);value &＃61; getFieldValueByNameSequence(subFieldNameSequence, fieldObj);}return value;}/*** &＃64;param fieldName 字段名* &＃64;param fieldValue 字段值* &＃64;param o 对象* &＃64;MethodName : setFieldValueByName* &＃64;Description : 根据字段名给对象的字段赋值*/private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {Field field &＃61; getFieldByName(fieldName, o.getClass());if (field !&＃61; null) {field.setAccessible(true);//获取字段类型Class fieldType &＃61; field.getType();//根据字段类型给字段赋值if (String.class &＃61;&＃61; fieldType) {field.set(o, String.valueOf(fieldValue));} else if ((Integer.TYPE &＃61;&＃61; fieldType)|| (Integer.class &＃61;&＃61; fieldType)) {field.set(o, Integer.parseInt(fieldValue.toString()));} else if ((Long.TYPE &＃61;&＃61; fieldType)|| (Long.class &＃61;&＃61; fieldType)) {field.set(o, Long.valueOf(fieldValue.toString()));} else if ((Float.TYPE &＃61;&＃61; fieldType)|| (Float.class &＃61;&＃61; fieldType)) {field.set(o, Float.valueOf(fieldValue.toString()));} else if ((Short.TYPE &＃61;&＃61; fieldType)|| (Short.class &＃61;&＃61; fieldType)) {field.set(o, Short.valueOf(fieldValue.toString()));} else if ((Double.TYPE &＃61;&＃61; fieldType)|| (Double.class &＃61;&＃61; fieldType)) {field.set(o, Double.valueOf(fieldValue.toString()));} else if (Character.TYPE &＃61;&＃61; fieldType) {if ((fieldValue !&＃61; null) && (fieldValue.toString().length() > 0)) {field.set(o, Character.valueOf(fieldValue.toString().charAt(0)));}} else if (Date.class &＃61;&＃61; fieldType) {field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));}else if(BigDecimal.class &＃61;&＃61; fieldType){field.set(o, new BigDecimal(fieldValue.toString()));}else {field.set(o, fieldValue);}} else {throw new ExcelException(o.getClass().getSimpleName() &＃43; "类不存在字段名 " &＃43; fieldName);}}/*** &＃64;param ws* &＃64;MethodName : setColumnAutoSize* &＃64;Description : 设置工作表自动列宽和首行加粗*/private static void setColumnAutoSize(WritableSheet ws, int extraWith) {//获取本列的最宽单元格的宽度for (int i &＃61; 0; i void fillSheet(WritableSheet sheet,List list,LinkedHashMap fieldMap,int firstIndex,int lastIndex) throws Exception {//定义存放英文字段名和中文字段名的数组String[] enFields &＃61; new String[fieldMap.size()];String[] cnFields &＃61; new String[fieldMap.size()];//填充数组int count &＃61; 0;for (Map.Entry entry : fieldMap.entrySet()) {enFields[count] &＃61; entry.getKey();cnFields[count] &＃61; entry.getValue();count&＃43;&＃43;;}//填充表头for (int i &＃61; 0; i

ExcelFieldMap映射

表格的标题要和字段一一对应&＃xff0c;这里导入用的是 getFieldMapIn() 方法。

import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;public class ExcelFieldMap {/*** 字段排名列*导出excel* &＃64;return*/public static LinkedHashMap getFieldMapOut() {LinkedHashMap linkedHashMap &＃61; new LinkedHashMap();linkedHashMap.put("phone", "联系电话");linkedHashMap.put("realName", "真实姓名");linkedHashMap.put("department", "机构"); linkedHashMap.put("email", "电子邮箱");linkedHashMap.put("money", "金额");return linkedHashMap;}/*** 字段排序* 导入excel*/public static LinkedHashMap getFieldMapIn() {LinkedHashMap linkedHashMap &＃61; new LinkedHashMap();Iterator iter &＃61; getFieldMapOut().entrySet().iterator();while (iter.hasNext()) {Map.Entry entry &＃61; (Map.Entry) iter.next();String key &＃61; (String) entry.getKey();String val &＃61; (String) entry.getValue();linkedHashMap.put(val,key);}return linkedHashMap;}}

ExcelException

自定义异常&＃xff1a;


/*** 自定义异常*/
public class ExcelException extends Exception{public ExcelException() {// TODO Auto-generated constructor stub}public ExcelException(String message) {super(message);// TODO Auto-generated constructor stub}public ExcelException(Throwable cause) {super(cause);// TODO Auto-generated constructor stub}public ExcelException(String message, Throwable cause) {super(message, cause);// TODO Auto-generated constructor stub}
}

可直接下载源码运行&＃xff1a; https://download.csdn.net/download/she_lock/10603000