Excel导入导出

  在后台管理系统中，常遇到Excel导入导出的需求，整理了如下两个工具类。

一、导入

导入工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
package com.demo.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;
import ch.qos.logback.classic.Logger;

public class ExcelToEntityList {

	private Logger log = (Logger) LoggerFactory.getLogger(this.getClass());
	private BeanStorage storage = new BeanStorage();
	private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	private StringBuffer error = new StringBuffer(0);

	/**
	 * 包含headMapping信息已经前端传过来的扩展信息
	 * @param entity  		一行excel内容要转换为何种对象
	 * @param excel	  		excel输入流
	 * @param titleToAttr	key为excel的中文title，value为该中文title对于的entity属性名
	 * @return
	 * @throws IOException
	 * @throws ClassNotFoundException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws InvalidFormatException
	 */
	public  <T> ArrayList<T> transform(Class<?> entity,InputStream excel,Map<String,String> titleToAttr) throws IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, InvalidFormatException{
		ArrayList<T> result = new ArrayList<T>();

		Workbook book = create(excel);
		Sheet sheet = book.getSheetAt(0);
	    int rowCount = sheet.getLastRowNum();
	    if(rowCount < 1){
			return result;
		}
	    //加载标题栏数据,以此和headMapping对应
	    Map<Integer, String> headTitle = loadHeadTitle(sheet);
	    for(int i=1;i<=rowCount;i++){
	    	Row row = sheet.getRow(i);
	    	//空行跳过
	    	if(row==null){
	    		continue;
	    	}
			int cellCount = row.getLastCellNum();
			@SuppressWarnings("unchecked")
			T instance = (T) entity.newInstance();
			int col = 0;
			try {
		    	for(;col<cellCount;col++){
					String cellValue = getCellValue(row.getCell(col));
					if(null!=cellValue){
						this.setEntity(entity, instance,titleToAttr.get(headTitle.get(col)), cellValue);
					}
				}
				result.add(instance);
			} catch (Exception e) {
				//excel.close();
				e.printStackTrace();
				this.error.append("第"+ (i+1) +"行，"+ headTitle.get(col)+"字段，数据错误，跳过！").append("<br>");
				log.error("第"+ (i+1) +"行，"+ headTitle.get(col)+"字段，数据错误，跳过！");
			}
	    }
	    excel.close();
	    return result;
	}

	/**
	 * 加载Excel的标题栏
	 * @param sheet
	 * @return 返回列序号和对于的标题名称Map
	 */
	private Map<Integer,String> loadHeadTitle(Sheet sheet){
		Map<Integer,String> map = new HashMap<Integer, String>();
		Row row = sheet.getRow(0);
		int cellCount= row.getLastCellNum();
		for(int i = 0; i < cellCount; i++){
			String value = row.getCell(i).getStringCellValue();
			if(null == value){
				throw new RuntimeException("Excel导入：标题栏不能为空！");
			}
			map.put(i, value);
		}
		return map;
	}

	/**
	 * 获取表格列的值
	 * @param cell
	 * @return
	 */
	private String getCellValue(Cell cell){
		if(null==cell){return "";}
		String value = null;
		switch (cell.getCellType()){
		case XSSFCell.CELL_TYPE_BOOLEAN:
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case XSSFCell.CELL_TYPE_NUMERIC:
			// 判断当前的cell是否为Date
			if (DateUtil.isCellDateFormatted(cell)){
				value = dateFormat.format(cell.getDateCellValue());
			}else{
				value = String.valueOf((long) cell.getNumericCellValue());
			}
			break;
		case XSSFCell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		case XSSFCell.CELL_TYPE_FORMULA:
			log.debug("不支持函数！");
			break;
		}

		return value;
	}

	private <T> void setEntity(Class<?> clazz, T instance, String pro, String value) throws SecurityException, NoSuchMethodException, Exception{
		String innerPro = null;
		String outterPro = null;
		if (pro.contains(".")){
			String[] pros = pro.split("\\.");
			outterPro = pros[0];
			innerPro = pros[1];
			// 将成员变量的类型存储到仓库中
			storage.storeClass(instance.hashCode() + outterPro, clazz.getDeclaredMethod(this.initGetMethod(outterPro), null).getReturnType());
		}
		String getMethod = this.initGetMethod(outterPro!=null?outterPro:pro);
		Class<?> type = clazz.getDeclaredMethod(getMethod, null).getReturnType();
		Method method = clazz.getMethod(this.initSetMethod(outterPro!=null?outterPro:pro), type);
		if (type == String.class){
			method.invoke(instance, value);
		}else if (type == int.class || type == Integer.class){
			method.invoke(instance, Integer.parseInt("".equals(value) ? "0" : value));
		}else if (type == long.class || type == Long.class){
			method.invoke(instance, Long.parseLong("".equals(value) ? "0" : value));
		}else if (type == float.class || type == Float.class){
			method.invoke(instance, Float.parseFloat("".equals(value) ? "0" : value));
		}else if (type == double.class || type == Double.class){
			method.invoke(instance, Double.parseDouble("".equals(value) ? "0" : value));
		}else if (type == Date.class){
			method.invoke(instance, dateFormat.parse(value));
		}else if (type == boolean.class|| type == Boolean.class){
			method.invoke(instance, Boolean.parseBoolean("".equals(value) ? "false" : value));
		}else if (type == byte.class|| type == Byte.class){
			method.invoke(instance, Byte.parseByte(value));
		}else{
			// 引用类型数据
			Object ins = storage.getInstance(instance.hashCode() + outterPro);
			this.setEntity(ins.getClass(), ins, innerPro, value);
			method.invoke(instance, ins);
		}
	}

	public String initSetMethod(String field)
	{
		return "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
	}

	public String initGetMethod(String field)
	{
		return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);
	}

	/**
	 * @return true 存在错误，false 不存在错误
	 */
	public boolean hasError()
	{
		return error.capacity() > 0;
	}

	public StringBuffer getError()
	{
		return error;
	}
	/**
	 * 存储bean中的bean成员变量
	 */
	private class BeanStorage
	{
		private Map<String, Object> instances = new HashMap<String, Object>();
		public void storeClass(String key, Class<?> clazz) throws Exception{
			if (!instances.containsKey(key)){
				instances.put(key, clazz.newInstance());
			}
		}
		public Object getInstance(String key){
			return instances.get(key);
		}
	}

	//2003、2007兼容处理
	public  Workbook create(InputStream inp) throws IOException,InvalidFormatException {
	    if (!inp.markSupported()) {
	        inp = new PushbackInputStream(inp, 8);
	    }
	    if (POIFSFileSystem.hasPOIFSHeader(inp)) {
	        return new HSSFWorkbook(inp);
	    }
	    if (POIXMLDocument.hasOOXMLHeader(inp)) {
	        return new XSSFWorkbook(OPCPackage.open(inp));
	    }
	    throw new IllegalArgumentException("你的excel版本目前poi解析不了");
	}
}

使用方式

1
2
3
4
5
6
//前端部分提交excel文件与该对应关系到后端
var mapping = {
    "EntityClassName":"com.demo.entity.User",
    "用户编号":"userCode","用户名称":"userName",
    "电话":"phone","地址":"address"
}
1
2
3
4
5
6
7
/*后端部分在servlet中接收excel与excel头与entity属性的对应关系*/
//接收对应关系参数
HashMap<String, String> param = JSON.parseObject(URLDecoder.decode(request.getParameter("mapping"), "utf-8"), HashMap.class);
ExcelToEntityList excel = new ExcelToEntityList();
//把excel文件内容转换为List对象
ArrayList<?> list = excel.transform(Class.forName(param.get("EntityClassName")), request.getPart("file").getInputStream(), param);

二、导出

导入工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
package com.demo.util;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class EntityListToExcel {
	private StringBuffer error = new StringBuffer(0);

	/**
	 * 将实体类列表entityList转换成excel
	 * @param param 包含headMapping信息，key为属性名，value为列名<br>
	 * @param entityList
	 * @param excel
	 * @return
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 * @throws IOException
	 */
	public <T> boolean transform(Map<String, String> param, List<T> entityList,OutputStream excel) throws NoSuchMethodException,
			SecurityException, IllegalAccessException,
			IllegalArgumentException, InvocationTargetException, IOException {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		// 声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置,详见文档
		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
				0, 0, 0, (short) 4, 2, (short) 6, 5));
		// 设置注释内容
		comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		comment.setAuthor("admin");

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		int i = 0;
		List<String> proList = new ArrayList<String>();
		HSSFFont blueFont = workbook.createFont();
		blueFont.setColor(HSSFColor.BLUE.index);
		for (Map.Entry<String, String> entry : param.entrySet()) {
			HSSFCell cell = row.createCell(i);
			HSSFRichTextString text = new HSSFRichTextString(entry.getValue());
			text.applyFont(blueFont);
			cell.setCellValue(text);
			proList.add(entry.getKey());
			i++;
		}
		// 遍历集合数据，产生数据行
		Iterator<T> it = entityList.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			for (i = 0; i < proList.size(); i++) {
				HSSFCell cell = row.createCell(i);
				String propertyName = proList.get(i);
				String textValue = null;
				try {
					textValue = this.getPropertyValue(t, propertyName);
				} catch (Exception e) {
					e.printStackTrace();
					this.error.append("第").append(index+1).append("行，列名：").append(param.get(propertyName)).append("，字段：").append(propertyName).append("，数据错误，跳过！").append("<br>");
				}
				// 利用正则表达式判断textValue是否全部由数字组成
				if (textValue != null) {
					Pattern p = Pattern.compile("^//d+(//.//d+)?$");
					Matcher matcher = p.matcher(textValue);
					if (matcher.matches()) {
						// 是数字当作double处理
						cell.setCellValue(Double.parseDouble(textValue));
					} else {
						HSSFRichTextString richString = new HSSFRichTextString(
								textValue);
						cell.setCellValue(richString);
					}
				}
			}

		}
		workbook.write(excel);
		workbook.close();
		return true;
	}

	/**
	 * 获取实体instance的propertyName属性的值
	 * @param instance
	 * @param propertyName
	 * @return
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 */
	private <T> String getPropertyValue(T instance, String propertyName)
			throws NoSuchMethodException, SecurityException,
			IllegalAccessException, IllegalArgumentException,
			InvocationTargetException {

		String getMethodName = this.initGetMethod(propertyName);
		Class<?> tCls = instance.getClass();
		Method getMethod = null;
		Object value = null;

		getMethod = tCls.getMethod(getMethodName, new Class[] {});
		value = getMethod.invoke(instance, new Object[] {});

		String returnType = getMethod.getReturnType().getName();

		// 判断值的类型后进行强制类型转换
		SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String textValue = null;
		if ("java.util.Date".equals(returnType)) {
			textValue = dateFormat.format(value);
		}else{
			textValue = value.toString();
		}
		return textValue;
	}
	/**
	 * 返回fiel属性的getXXX方法字符串
	 * @param field
	 * @return
	 */
	private String initGetMethod(String field) {
		return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);
	}

	/**
	 * @return true 存在错误，false 不存在错误
	 */
	public boolean hasError() {
		return error.capacity() > 0;
	}

	/**
	 * 获得错误信息
	 * @return
	 */
	public StringBuffer getError() {
		return error;
	}
}

使用方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//Servlet中设置返回类型
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//这里也可以通过前端传过来
LinkedHashMap<String, String> header = new LinkedHashMap<String, String>();
header.put("userCode", "用户编码");
header.put("userName", "用户名");
header.put("phone", "电话");
header.put("address", "地址");

//数据导出为excel
EntityListToExcel excel =  = new EntityListToExcel();
//这里的entityList为要转换为excel行的数据列表
excel.transform(header, entityList, response.getOutputStream());

赏

谢谢你请我吃糖果

扫一扫，分享到微信

微信分享二维码