前言：
最近PM来了一个需求，简单来说就是在录入数据时一条一条插入到系统显得非常麻烦，让我实现一个直接通过excel导入的方法一次性录入所有数据。网上关于excel导入导出的例子很多，但大多相互借鉴。经过思考，认为一百个客户在录入excel的时候，就会有一百个格式版本，所以在实现这个功能之前，所以要统一excel的格式。于是提供了一个通用excel模版的下载功能。当所有客户用模版录入好数据再上传到系统，后端对excel进行解析，然后再持久化到数据库。
概述：
此工具类的几大特点
1、基本导入导出
2、提供excel模版自动生成及下载功能
3、创建模版过程简单通用，只需要在实体类上进行注解
4、springMVC框架
5、模版可以生成下拉框选择列
废话不多说，上代码......
一、引入poi相关依赖（及spring上传文件相关配置，不再解释）
<!-- apache poi start -->
<poi.version>3.14</poi.version>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency> <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>${poi.version}</version>
</dependency> <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!-- apache poi end -->
二、excel导入导出工具类的实现
/**
* @Description
* @author zhaomin E-mail:min.zhao@mljr.com
* @date 创建时间：2017年2月14日 下午2:13:30
* @version 1.0
*/
public class ImportExcelUtil {
final static String notnullerror = "请填入第{0}行的{1},{2}不能为空";
final static String errormsg = "第{0}行的{1}数据导入错误"; /**
* 导入Excel
*
* @param clazz
* @param xls
* @return
* @throws Exception
*/
@SuppressWarnings("rawtypes")
public static List importExcel(Class<?> clazz, InputStream xls) throws Exception {
try {
// 取得Excel
HSSFWorkbook wb = new HSSFWorkbook(xls);
HSSFSheet sheet = wb.getSheetAt(0);
Field[] fields = clazz.getDeclaredFields();
List<Field> fieldList = new ArrayList<Field>(fields.length);
for (Field field : fields) {
if (field.isAnnotationPresent(ModelProp.class)) {
ModelProp modelProp = field.getAnnotation(ModelProp.class);
if (modelProp.colIndex() != -1) {
fieldList.add(field);
}
}
}
EmployeeDTO employee = new EmployeeDTO();
// 行循环
List<ImportModel> modelList = new ArrayList<ImportModel>(sheet.getPhysicalNumberOfRows() * 2);
for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
// 数据模型
ImportModel model = (ImportModel) clazz.newInstance();
int nullCount = 0;
Exception nullError = null;
for (Field field : fieldList) {
ModelProp modelProp = field.getAnnotation(ModelProp.class);
HSSFCell cell = sheet.getRow(i).getCell(modelProp.colIndex());
try {
if (cell == null || cell.toString().length() == 0) {
nullCount++;
if (!modelProp.nullable()) {
nullError = new Exception(StringUtil.format(notnullerror,
new String[] { "" + (1 + i), modelProp.name(), modelProp.name() })); }
} else if (field.getType().equals(Date.class)) {
if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(), new Date(parseDate(parseString(cell))));
} else {
BeanUtils.setProperty(model, field.getName(),
new Date(cell.getDateCellValue().getTime())); }
} else if (field.getType().equals(Timestamp.class)) {
if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(),
new Timestamp(parseDate(parseString(cell))));
} else {
BeanUtils.setProperty(model, field.getName(),
new Timestamp(cell.getDateCellValue().getTime()));
} } else if (field.getType().equals(java.sql.Date.class)) {
if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(),
new java.sql.Date(parseDate(parseString(cell))));
} else {
BeanUtils.setProperty(model, field.getName(),
new java.sql.Date(cell.getDateCellValue().getTime()));
}
} else if (field.getType().equals(java.lang.Integer.class)) {
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(), (int) cell.getNumericCellValue());
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(), Integer.parseInt(parseString(cell)));
}
} else if (field.getType().equals(java.math.BigDecimal.class)) {
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(),
new BigDecimal(cell.getNumericCellValue()));
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(), new BigDecimal(parseString(cell)));
}
} else {
if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(),
new BigDecimal(cell.getNumericCellValue()));
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
BeanUtils.setProperty(model, field.getName(), parseString(cell));
}
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception(StringUtil.format(errormsg, new String[] { "" + (1 + i), modelProp.name() })
+ "," + e.getMessage());
}
}
if (nullCount == fieldList.size()) {
break;
}
if (nullError != null) {
throw nullError;
}
modelList.add(model);
}
return modelList; } finally {
xls.close();
}
} private final static int colsizeN = 630;
private final static int colsizeM = 1000; /**
* 下载Excel模版
*
* @param clazz
* @param map
* @param rowSize
* @return
*/
public static InputStream excelModelbyClass(Class<?> clazz, Map<Integer, String[]> map, Integer rowSize) {
try {
if (!clazz.isAnnotationPresent(ModelTitle.class)) {
throw new Exception("请在此类型中加上ModelTitle注解");
}
if (rowSize == null) {
rowSize = 1000;
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
/**
* 设置标题样式
*/
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeight((short) 400);
titleStyle.setFont(font);
HSSFCell titleCell = sheet.createRow(0).createCell(0); // 创建第一行，并在该行创建单元格，设置内容，做为标题行
/**
* 获取标题
*/
ModelTitle modelTitle = clazz.getAnnotation(ModelTitle.class);
titleCell.setCellValue(new HSSFRichTextString(modelTitle.name()));
titleCell.setCellStyle(titleStyle); Field[] fields = clazz.getDeclaredFields();
HSSFRow headRow = sheet.createRow(1);
int colSzie = 0;
/**
* 设置表头样式
*/
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headFont = wb.createFont();
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setFontHeight((short) 240);
headStyle.setFont(headFont);
List<Integer> cells = new ArrayList<Integer>(); for (Field field : fields) {
if (field.isAnnotationPresent(ModelProp.class)) {
ModelProp modelProp = field.getAnnotation(ModelProp.class);
if (modelProp.colIndex() == -1)
continue;
cells.add(modelProp.colIndex());
HSSFCell cell = headRow.createCell(modelProp.colIndex());
cell.setCellValue(new HSSFRichTextString(modelProp.name()));
cell.setCellStyle(headStyle);
colSzie++;
sheet.autoSizeColumn((short) modelProp.colIndex());
sheet.setColumnWidth(modelProp.colIndex(), modelProp.name().length() * colsizeN + colsizeM); // 设置列为下拉框格式
if (map != null && map.get(new Integer(modelProp.colIndex())) != null) {
DVConstraint constraint = DVConstraint
.createExplicitListConstraint(map.get(modelProp.colIndex()));
CellRangeAddressList regions = new CellRangeAddressList(2, rowSize, modelProp.colIndex(),
modelProp.colIndex());
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidation);
}
}
}
HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFDataFormat format = wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
for (int i = 2; i < rowSize; i++) {
HSSFRow row = sheet.createRow(i);
for (Integer integer : cells) {
HSSFCell cell = row.createCell(integer);
cell.setCellStyle(cellStyle);
}
}
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSzie - 1));
if (map != null) {
for (Integer colIndex : map.keySet()) {
DVConstraint constraint = DVConstraint.createExplicitListConstraint(map.get(colIndex));
CellRangeAddressList regions = new CellRangeAddressList(2, 1000, colIndex, colIndex);
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidation);
}
} ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
} byte[] b = os.toByteArray(); ByteArrayInputStream in = new ByteArrayInputStream(b);
return in;
} catch (Exception e) {
e.printStackTrace();
return null;
}
} private static String parseString(HSSFCell cell) {
return String.valueOf(cell).trim();
} private static long parseDate(String dateString) throws ParseException {
if (dateString.indexOf("/") == 4) {
return new SimpleDateFormat("yyyy/MM/dd").parse(dateString).getTime();
} else if (dateString.indexOf("-") == 4) {
return new SimpleDateFormat("yyyy-MM-dd").parse(dateString).getTime();
} else if (dateString.indexOf("年") == 4) {
return new SimpleDateFormat("yyyy年MM月dd").parse(dateString).getTime();
} else if (dateString.length() == 8) {
return new SimpleDateFormat("yyyyMMdd").parse(dateString).getTime();
} else {
return new Date().getTime();
}
} }
三、自定义spring注解
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ModelProp{
public String name();
public int colIndex() default -1;
public boolean nullable() default true;
public String interfaceXmlName() default "";
}
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface ModelTitle{
public String name();
}
四、定义实体类父类
public class ImportModel {
}
五、定义实体类
@ModelTitle(name="人员列表")
public class EmployeeDTO extends ImportModel implements Serializable { private static final long serialVersionUID = -3434719712955859295L; private Long id;
@ModelProp(name = "电话", colIndex = 1, nullable = false)
private String telephone; @ModelProp(name = "名称", colIndex = 0, nullable = false)
private String name; @ModelProp(name = "性别", colIndex = 2, nullable = false)
private Integer sex;
}
六、定义controller
@RestController
@RequestMapping("/api/excelOpera")
public class ImportEmployeeController extends BaseController { private static Logger logger = LoggerFactory.getLogger(ImportEmployeeController.class);
/**
* 导入excel表
* @version 1.0
* @since 1.0
*/
@RequestMapping(path = "/importEmployee", method = RequestMethod.POST)
public RespMsg uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
try{
List<EmployeeDTO> employeeDTOList = ImportExcelUtil.importExcel(EmployeeDTO.class, file.getInputStream());
//可做持久化操作，现只打印观察
for(EmployeeDTO employeeDTO : employeeDTOList){
logger.info("name=" + employeeDTO.getName() + ",telephone=" + employeeDTO.getTelephone()+",sex=" + employeeDTO.getSex());
}
}catch(Exception e){
logger.error(e.getMessage());
}
return null;
}
/**
* 导出excel模版
* @version 1.0
* @since 1.0
*/
@RequestMapping(path = "/downloadEmployeeModel", method = RequestMethod.GET)
public RespMsg downloadEmployeeModel(HttpServletResponse response) {
try{
response.setContentType("application/xls");
response.addHeader("Content-Disposition", "attachment;filename="+new String(("eeelist").getBytes("UTF-8"),"iso-8859-1")+".xls");
Map<Integer,String[]> paramMap = new HashMap<Integer,String[]>();
//excel第三行为下拉选择框
paramMap.put(2, new String[]{"man","women"});
BufferedInputStream input = new BufferedInputStream(ImportExcelUtil.excelModelbyClass(EmployeeDTO.class, paramMap, null));
byte buffBytes[] = new byte[1024];
OutputStream os = response.getOutputStream();
int read = 0;
while ((read = input.read(buffBytes)) != -1) {
os.write(buffBytes, 0, read);
}
os.flush();
os.close();
input.close();
return success("下载成功！");
}catch(Exception e){
logger.error("downloadEmployeeModel() catch Exception ",e);
return fail("下载失败！");
}
} }
至此全部工具类的实现已经完成，可以请求访问检验一下结果。
下载下来的excel模版
填写数据上传
后台控制台打印输出结果
检验结果能够达到预期效果且能通用，是不是很简单呢，欢迎大神们提出意见，小女子感谢了。