基于POI的Excel导入导出工具ExcelUtil

亦凉 2022-03-24 15:54 448阅读 0赞

关于excel的操作工具网上实在太多，但有两方面原因值得自己写一个：1）简单的导入导出功能的案例大同小异，自己写一个也浪费不了多少时间，2）成熟的git工具类功能过于臃肿，我仅仅需要个导入导出你让我依赖你一个完整的package?

一、核心工具类代码：

package com.roboadvisor.platform.util.excelUtil;
    
    import java.io.ByteArrayInputStream;
    import java.io.ByteArrayOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.Set;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.formula.functions.T;
    import org.apache.poi.ss.usermodel.CellType;
    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.util.IOUtils;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    import com.roboadvisor.platform.entity.excelEntity.ExcelFiled;
    
    /**
     * excel解析工具
     * @author fujian
     *
     */
    public class ExcelUtil {
    
    	/**
    	 * 根据clazz定义的column解析excel文件
    	 * @param excel
    	 * @param clazz
    	 */
    	
    	public static <T> List<T> parse(MultipartFile excel, Class<T> clazz) {
    		
    		try {
    			File tmpFile = File.createTempFile(excel.getOriginalFilename().substring(0, excel.getOriginalFilename().lastIndexOf(".")), excel.getOriginalFilename().substring(excel.getOriginalFilename().lastIndexOf(".")+1));
    			excel.transferTo(tmpFile);
    			Workbook workbook = getWorkBook(tmpFile);
    			Sheet sheet = workbook.getSheetAt(0);
    			return parseSheet(sheet, clazz);
    			
    			
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		return null;
    	}
    	
    	private static Workbook getWorkBook(File file) {
    		FileInputStream fis = null;
    		try {
    			fis = new FileInputStream(file);
    			if(file.getName().endsWith("xls")) {
    				return new HSSFWorkbook(fis);
    			}
    			if(file.getName().endsWith("xlsx")) {
    				return new XSSFWorkbook(fis);
    			}
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			IOUtils.closeQuietly(fis);
    		}
    		return null;
    	}
    	
    	private static <T> List<T> parseSheet(Sheet sheet, Class<T> clazz) {
    		List<T> result = new ArrayList<>();
    		Map<String, Integer> field2ColNum = new HashMap<>();
    		Field[] fields = clazz.getDeclaredFields();
    		for(Field field : fields) {
    			if(!field.isAccessible()) {
    				field.setAccessible(true);
    			}
    			ExcelFiled anno = field.getDeclaredAnnotation(ExcelFiled.class);
    			if(null != anno) {
    				field2ColNum.put(field.getName(), anno.columnNum());
    			}
    		}
    		
    		for(int rowNum =1; rowNum<sheet.getLastRowNum(); rowNum++) {
    			Row row = sheet.getRow(rowNum);
    			try {
    				T object = clazz.newInstance();
    				for(String filedName : field2ColNum.keySet()) {
    					int cellNum = field2ColNum.get(filedName);
    					Method method = clazz.getDeclaredMethod("set"+filedName.replaceAll("^\\w", String.valueOf(filedName.charAt(0)).toUpperCase()), String.class);
    					if(CellType.STRING != row.getCell(cellNum).getCellType()) {
    						method.invoke(object, String.valueOf(row.getCell(cellNum).getNumericCellValue()).replaceAll("\\.\\d+", ""));
    					} else {
    						method.invoke(object, row.getCell(cellNum).getStringCellValue());
    					}
    				}
    				result.add(object);
    			} catch (Exception e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    			
    		}
    		return result;
    	}
    	
    	/**
    	 * 将list中的数据写入excel
    	 * @param list
    	 * @return
    	 */
    	public static <T> InputStream exportExcel(List<T> list) {
    		InputStream excel = null;
    		if(list.isEmpty()) {
    			return excel;
    		}
    		
    		Workbook workbook = new XSSFWorkbook();
    		Sheet sheet = workbook.createSheet();
    		Row headerRow = sheet.createRow(0);
    		Class<?> clazz = list.get(0).getClass();
    		
    		Map<String, Integer> field2ColNum = new HashMap<>();
    		Field[] fields = clazz.getDeclaredFields();
    		for(Field field : fields) {
    			if(!field.isAccessible()) {
    				field.setAccessible(true);
    			}
    			ExcelFiled anno = field.getDeclaredAnnotation(ExcelFiled.class);
    			if(null != anno) {
    				field2ColNum.put(field.getName(), anno.columnNum());
    				headerRow.createCell(anno.columnNum()).setCellValue(anno.columnName());
    			}
    		}
    		Set<String> fileds = field2ColNum.keySet();
    		for(int i=0; i<list.size(); i++) {
    			Row row = sheet.createRow(i+1);
    			for(String field : fileds) {
    				try {
    					Method method = clazz.getDeclaredMethod("get"+field.replaceAll("^\\w", String.valueOf(field.charAt(0)).toUpperCase()));
    					String object = String.valueOf(method.invoke(list.get(i)));
    					row.createCell(field2ColNum.get(field)).setCellValue(object);
    				} catch (Exception e) {
    					e.printStackTrace();
    				}
    			}
    		}
    		
    		for(int i=0; i<fields.length; i++) {
    			sheet.autoSizeColumn(i);
    		}
    		
    		try {
    			ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    			workbook.write(outputStream);
    			excel = new ByteArrayInputStream(outputStream.toByteArray());
    			workbook.close();
    			return excel;
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return excel;
    	}
    }

二、与excel中内容对应的实体类上需添加的注解如下：

package com.roboadvisor.platform.entity.excelEntity;
    
    import lombok.Getter;
    import lombok.Setter;
    
    @Setter
    @Getter
    public class ExcelEntity {
    
    	
    	private String poolId;
    	@ExcelFiled(columnName="基金代码", columnNum=0)
    	private String securityId;
    	@ExcelFiled(columnName="基金名称", columnNum=1)
    	private String secName;
    	@ExcelFiled(columnName="产品类型", columnNum=2)
    	private String secType;
    	
    }

columnName为列名称，中文名称  
columnNum为列数，0-based

三、service层调用excel工具类

package com.roboadvisor.platform.service;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.List;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.commons.compress.utils.IOUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.web.multipart.MultipartFile;
    import com.roboadvisor.platform.entity.excelEntity.ExcelEntity;
    import com.roboadvisor.platform.mapper.FileImportMapper;
    import com.roboadvisor.platform.util.excelUtil.ExcelUtil;
    import com.roboadvisor.platform.util.resultEntity.ResultEntity;
    import com.roboadvisor.platform.util.resultEntity.ResultStatus;
    
    @Service
    public class FileImportService {
    
    	@Autowired
    	private FileImportMapper mapper;
    	
    	/**
    	 * excel内容导入db
    	 * @param excel
    	 * @return
    	 */
    	public ResultEntity<String> importExcel(MultipartFile excel) {
    		
    		List<ExcelEntity> entities = ExcelUtil.parse(excel, ExcelEntity.class);
    		
    		mapper.insertList(entities);
    		
    		return new ResultEntity<>(ResultStatus.OK);
    		
    	}
    	/**
    	 * db内容写入excel
    	 * @param entities
    	 * @throws UnsupportedEncodingException 
    	 */
    	public void exportExcel(HttpServletResponse response) throws UnsupportedEncodingException {
    		
    		List<ExcelEntity> list = mapper.getFromDB();
    		
    		InputStream in = ExcelUtil.exportExcel(list);
    		response.reset();
    		response.setContentType("multipart/form-data");
    		response.setCharacterEncoding("UTF-8");
    		String fielname = URLEncoder.encode("export-excel.xlsx", "UTF-8");
    		response.setHeader("Content-Disposition", "attachment; filename=" + fielname);
    		byte[] buffer = new byte[1024]; 
    		int length;
    		try {
    			while((length = in.read(buffer)) >0) {
    				response.getOutputStream().write(buffer, 0, length);
    			}
    		} catch (IOException e) {
    			e.printStackTrace();
    		} finally {
    			IOUtils.closeQuietly(in);
    		}	
    	}	 
    }