java功能实现 -- Excel导入导出

需求

各个项目都有表格导入导出的需求，因此总结一个通用版的功能实现。此前也写过几种导入导出的模板， 最后找到一个较为满意的解决方案。可能也有其他更简洁的方式，但在添加新实体导入导出时不够友好，这里提供一下之前写过的一种简单导出方式。

思路

1.系统提供导入模板，用户先下载该模板进行信息采集；
2.文件服务器提供接口，解析Excel文件为Json格式（见附一）；
3.前端将已解析数据传入后台导入导出接口。

步骤

实体类设置导入导出字段映射

public static Map<String, String> import_filed_interpretation = new LinkedHashMap<String, String>();// 导入字段映射释义
public static Map<String, String> export_filed_interpretation = new LinkedHashMap<String, String>();// 导出字段映射释义

static {
	// 导入字段
	import_filed_interpretation.put("id","编号");
	import_filed_interpretation.put("name","名称");
	import_filed_interpretation.put("code","编码");
	
	// 导出字段
	export_filed_interpretation = import_filed_interpretation;
	export_filed_interpretation.put("describes","描述");
}

API接口代码

@ApiOperation(value = "导入excel", notes = "导入excel")
@RequestMapping(value = "/importexcel", method = RequestMethod.POST)
@JsonView(ModelDto.BaseView.class)
public Object importexcel(@RequestBody String exceldata) {
	return ModelService.importexcel(exceldata);
}

@ApiOperation(value = "导出excel", notes = "导出excel")
@RequestMapping(value = "/exportexcel", method = RequestMethod.GET)
@JsonView(ModelDto.BaseView.class)
public Object exportexcel(HttpServletResponse reponse,
		@ModelAttribute @Validated({ ModelDto.ListValidated.class }) ModelDto ModelDto) {
	return ModelService.exportexcel(reponse, ModelDto);
}

业务实现代码

@Override
public Response importexcel(String exceldata) {
	HttpStatus status = HttpStatus.OK;
	String msg = "操作成功！";
	try {
		JSONArray exceldataArray = JSONArray.fromObject(exceldata);
		if (exceldataArray != null) {
			for (int i = 0; i < exceldataArray.size(); i++) {
				JSONObject excelobject = exceldataArray.getJSONObject(i);
				// 反射机制解析数据
				ModelEntity modelEntity = new ModelEntity();
				// 调用导入excel工具
				modelEntity = ImportExportExcelUtil.importExcel(
					modelDto.import_filed_interpretation,modelEntity, excelobject);
				// 业务代码……
			}
		}
		return new Response(status.value(), msg);
	} catch (Exception e) {
		e.printStackTrace();
		status = HttpStatus.INTERNAL_SERVER_ERROR;
		msg = "操作失败！";
		return new Response(status.value(), msg);
	}
}

@Override
public Response exportexcel(HttpServletResponse response, 	
    BuildEntranceDto buildEntranceDto) {
	HttpStatus status = HttpStatus.OK;
	String msg = "操作成功！";
	try {
		ModelEntity modelEntity = new 
            ModelEntity();
		BeanUtils.copyProperties(modelEntity, buildEntranceDto);
		// 查询数据
		List<ModelEntity> modelEntitys = modelDao.findAll(0, 0, modelEntity);
		// 调用导出工具类
		ImportExportExcelUtil.exportExcel(response, BuildEntranceDto.export_filed_interpretation, 
           	modelEntitys, "模板信息列表");
		return new Response(status.value(), msg);
	} catch (Exception e) {
		e.printStackTrace();
		status = HttpStatus.INTERNAL_SERVER_ERROR;
		msg = "操作失败！";
		return new Response(status.value(), msg);
	}
}

导入导出工具类见附录二

附录一

接口

@PostMapping(value = "/uploadExcel")
	@ResponseBody
	public Object uploadExcel(@RequestParam MultipartFile file) throws Exception {
		HttpStatus status = HttpStatus.OK;
		String msg = "操作成功！";
		JSONArray list = new JSONArray();
		try {
			if (file.isEmpty()) {
				status = HttpStatus.NO_CONTENT;
				msg = "文件不能为空";
			}
			InputStream inputStream = file.getInputStream();
			list = excelImportService.getBankListByExcel(inputStream, file.getOriginalFilename(), 1);
			inputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
			status = HttpStatus.INTERNAL_SERVER_ERROR;
			msg = "操作失败！";
		}
		return new Response(status.value(), msg, list);
	}

业务

    /**
	 * 处理上传的文件
	 * 
	 * @param in
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public JSONArray getBankListByExcel(InputStream in, String fileName, int sheetnumber) throws Exception {
		JSONArray list = new JSONArray();
		// 创建Excel工作薄
		Workbook work = this.getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		if (StringUtils.isEmpty(sheetnumber) || sheetnumber == -1 || sheetnumber == 0) {
			sheetnumber = work.getNumberOfSheets();
		}

		for (int i = 0; i < sheetnumber; i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			List<String> title = new ArrayList<String>();// 存储标题
			for (int j = 0; j <= sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if (row == null /* || row.getFirstCellNum() == j */) {
					continue;
				}
				// 获取首行标题
				if (j == 0) {
					for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
						cell = row.getCell(y);
						if (cell != null) {// 空值判断
							cell.setCellType(CellType.STRING);
							title.add(cell.getStringCellValue().toString());
						}
					}
				} else {
					// 获取内容
					Map<String, String> map = new HashMap<String, String>();
					for (int y = 0; y < title.size(); y++) {
						cell = row.getCell(y);
						if (cell != null) {// 空值判断
							cell.setCellType(CellType.STRING);
							map.put(title.get(y), cell.getStringCellValue().toString());
						} else {// cell为null时赋为空字符串
							map.put(title.get(y), "");
						}
					}
					// 所有为空不加入返回值
					boolean allnotnull = true;
					for (Entry<String, String> m : map.entrySet()) {
						if (!StringUtils.isEmpty(m.getValue())) {
							allnotnull = false;
						}
					}
					if (!allnotnull) {
						list.add(map);
					}
				}
			}
		}
		work.close();
		return list;
	}

附录二

package com.xakj.util;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import org.springframework.util.ReflectionUtils;
import org.springframework.util.StringUtils;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import com.xiaoleilu.hutool.date.DateUtil;

public class ImportExportExcelUtil {

	/**
	 * 导入excel
	 * 
	 * @param import_filed_interpretation
	 * @param obj
	 * @param excelobject
	 */
	public static <T> T importExcel(Map<String, String> import_filed_interpretation, T obj, JSONObject excelobject) {
		try {
			Class<T> clz = (Class<T>) obj.getClass();
			for (Entry<String, String> entry : import_filed_interpretation.entrySet()) {
				Field field = null;
				try {
					field = clz.getDeclaredField(entry.getKey());
					field.setAccessible(true);
					if (field.getGenericType().toString().equals("class java.lang.Integer")) {
						if (Pattern.compile("0|([-]?[1-9][0-9]*)").matcher(excelobject.getString(entry.getValue())).matches()) {
							field.set(obj, Integer.parseInt(excelobject.getString(entry.getValue())));
						} else {
							field.set(obj, 0);
						}
					} else {
						field.set(obj, excelobject.getString(entry.getValue()));
					}
				} catch (Exception e) {
					try {
						Class<T> parent = (Class<T>) clz.getSuperclass();
						if (!parent.getName().toLowerCase().equals("java.lang.object")) {
							field = parent.getDeclaredField(entry.getKey());
							field.setAccessible(true);
							if (field.getGenericType().toString().equals("class java.lang.Integer")) {
								if (Pattern.compile("0|([-]?[1-9][0-9]*)").matcher(excelobject.getString(entry.getValue()))
										.matches()) {
									field.set(obj, Integer.parseInt(excelobject.getString(entry.getValue())));
								} else {
									field.set(obj, 0);
								}
							} else {
								field.set(obj, excelobject.getString(entry.getValue()));
							}
						}
					} catch (Exception e1) {
						// TODO: handle exception
					}
					continue;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}

	/**
	 * 导出excel
	 * 
	 * @param response
	 * @param export_filed_interpretation
	 * @param objList
	 * @param filename
	 */
	public static <T> void exportExcel(HttpServletResponse response, Map<String, String> export_filed_interpretation,
			List<T> data, String filename) {
		try {
			// 过滤掉不需要导出的字段
			List<JSONObject> rows = new ArrayList<JSONObject>();
			if (data != null && data.size() > 0) {
				Class<T> clz = (Class<T>) data.get(0).getClass();
				for (int i = 0; i < data.size(); i++) {
					// 反射机制赋值
					JSONObject obj = new JSONObject();
					for (Entry<String, String> entry : export_filed_interpretation.entrySet()) {
						Field field = null;
						try {
							field = clz.getDeclaredField(entry.getKey());
							field.setAccessible(true);
							PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clz);
							Method getMethod = pd.getReadMethod();// 获得get方法
							Object fieldValue = ReflectionUtils.invokeMethod(getMethod, data.get(i));
							if (StringUtils.isEmpty(fieldValue)) {
								obj.put(entry.getKey(), "");
							} else {
								obj.put(entry.getKey(), fieldValue);
							}
						} catch (Exception e) {
							// e.printStackTrace();
							try {
								Class<T> parent = (Class<T>) clz.getSuperclass();
								field = parent.getDeclaredField(entry.getKey());
								field.setAccessible(true);
								PropertyDescriptor pd = new PropertyDescriptor(field.getName(), parent);
								Method getMethod = pd.getReadMethod();// 获得get方法
								Object fieldValue = ReflectionUtils.invokeMethod(getMethod, data.get(i));
								if (StringUtils.isEmpty(fieldValue)) {
									obj.put(entry.getKey(), "");
								} else {
									obj.put(entry.getKey(), fieldValue);
								}
							} catch (Exception e1) {
								// e1.printStackTrace();
							}
							continue;
						}
					}
					rows.add(obj);
				}
			}
			ExcelWriter writer = ExcelUtil.getWriter();
			// 自定义标题别名
			for (Entry<String, String> entry : export_filed_interpretation.entrySet()) {
				writer.addHeaderAlias(entry.getKey(), entry.getValue());
			}
			writer.write(rows, true);
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ new String(filename.getBytes("GB2312"), "ISO_8859_1") + DateUtil.today() + ".xls");
			ServletOutputStream out = response.getOutputStream();
			// 输出相关数据流
			writer.flush(out, true);
			writer.close();
			IoUtil.close(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}