easyexcel-1.1.2-beta5.jar实现Excel数据导入及根据模板Excel导出数据（Jfinal框架）

1、接到了一个老项目的项目改造，增加房屋普查信息的功能，主要是下载Excel模板，然后在模板填写数据，在导入。还一个是根据模板写入数据下载到本地。
2、老版本的玩意网上都不好找咋玩。

1、标题第一步看看easyexcel-1.1.2-beta5.jar这个玩意依赖哪些库，统统下载导入进来

maven库给你贴上，你想咋下载就咋下载

https://mvnrepository.com/

2、下面上正菜

/**
* 这个用来实现前端上传数据Excel文件后台读取http流获取数据入库
* 我这里使用的是jfinal框架	
*/
public void importData() {
	UploadFile uploadFile = getFile("excelFile");
	SurveyDataListener surveyDataListener = newSurveyDataListener(getCurrentUser().getStr("USER_NO"));
		BufferedInputStream fis = null;
		try {
			fis = new BufferedInputStream(new FileInputStream(uploadFile.getFile()));
		} catch (FileNotFoundException e) {
			throw new ServiceException("文件流获取异常！");
		}
	EasyExcelFactory.readBySax(fis,new com.alibaba.excel.metadata.Sheet(1, 1),
	surveyDataListener);
}

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.goodcol.util.AppUtils;
import com.goodcol.util.LoggerUtil;
import com.goodcol.util.log.Logger;
import com.goodcol.util.log.LoggerConsole;
import com.jfinal.plugin.activerecord.Db;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @Description 处理建筑普查信息excel文件监听器
 * @Author shaoqi
 * @Date 2021/10/12
 */
public class SurveyDataListener extends AnalysisEventListener<List<String>> {
    public LoggerConsole log = Logger.getLogger(); // 记录日志用
    private List<Object[]> list = new ArrayList<Object[]>();
    private final int fileSize = 96;//字段数量
    private final int maxLength = 20;//读取20列入库一次
    private String userNo;
    public SurveyDataListener(String user){
        this.userNo = user;
    }
    @Override
    public void invoke(List<String> buildSruveyModel, AnalysisContext analysisContext) {
        if(list.size() > maxLength){
            int sum = saveData(list);
            list.clear();
        }
        List<String> temp = new ArrayList<>();
        temp.add(AppUtils.getStringSeq());
        temp.addAll(1, buildSruveyModel);
        list.add(temp.toArray(new Object[fileSize]));
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        int sum = saveData(list);
        log.info("[建筑普查信息excel导入数据]成功数量：" + sum);
        list.clear();
    }

    public int saveData(List<Object[]> params){
        if(params != null && params.size() > 0){
            Object[][] args = new Object[params.size()][fileSize];
            for (int i=0,size=params.size(); i<size; i++){
                args[i] = params.get(i);
            }
            String sql = "insert into (字段) values (?)";
            int[] result = Db.batch(sql, args, params.size());
            int sum = Arrays.stream(result).sum();
            return sum;
        }
        return 0;
    }
}

3、下载模板

	public void downLoadTemplate(){
		final String srcRelativePath = "template/";
		final String finalTemplateName = "data_import.xlsx";
		try {
			String filename = null;
			log.info("charset:" + Charset.defaultCharset().name());
			filename = new String(finalTemplateName.getBytes(), Charset.defaultCharset().name());

			String resourcePath = srcRelativePath + filename;
			URL url = getClass().getClassLoader().getResource(resourcePath);
			log.info("resourcePath:" + resourcePath);
			File srcFile = new File(url.toURI());
			renderFile(srcFile);
		} catch (UnsupportedEncodingException e1) {
			throw new ServiceException("模板获取异常，异常编码");
		} catch (URISyntaxException e) {
			throw new ServiceException("下载导入模板失败，原因：【" + e.getMessage() + "】", e);
		}
	}

4、导出Excel数据

我这里使用的是 jxl.jar 操作Excel表格

public void exportExcelData(){
		String fwmc = getPara("fwmc");
		String selectSql = "select * from t_aqjd_pcxx where 1=1 ";

		List<String> strList = new ArrayList<String>();
		if (AppUtils.StringUtil(fwmc) != null) {
			selectSql += " and fwmc  like  ?";
			strList.add("%" + fwmc +"%" );
		}
		List<Record> list = Db.find(selectSql, strList.toArray());
		if(list != null && list.size() > 0){
			final String srcRelativePath = "template/";
			final String finalTemplateName = "既有建筑清查情况汇总表.xlsx";
			HttpServletResponse response = getResponse();
			response.setCharacterEncoding("UTF-8");
			response.setContentType("application/octet-stream;charset=UTF-8");
			try {
				String filename = new String(finalTemplateName.getBytes(), Charset.defaultCharset().name());
				String resourcePath = srcRelativePath + filename;
				log.info("resourcePath:" + resourcePath);
				URL url = getClass().getClassLoader().getResource(resourcePath);
				File srcFile = new File(url.toURI());
				jxl.Workbook book= jxl.Workbook.getWorkbook(new FileInputStream(srcFile));
				response.addHeader("Content-Disposition", "attachment;fileName=" + new String(finalTemplateName.getBytes("utf-8"), "iso-8859-1"));
				WritableWorkbook wwb = Workbook.createWorkbook(response.getOutputStream(), book);
				WritableSheet sheet = wwb.getSheet(0);

				for(int a=0,aSize=list.size(); a<aSize; a++){
					Record one = list.get(a);
					Label labelTag = new Label(0, a+6, String.valueOf(a+1));//44列
					sheet.addCell(labelTag);
					Object[] values = one.getColumnValues();
					String[] fileds = new String[]{"FWMC","FWDZ","JDSQ","FWCQR","CQDJ","JZYT","SFBHXJZ","SFBHXJZ","SFBHXJZ","SFBHXJZ","SFBHXJZ","SFZYSJJZ",
					"JZMJ","JZGD","DSCS","DXCS","TS","JZSJ","JGLX","LMLX","WMLX","WQBWXS","SFCYJGZ","YWMXLHQXBXD","KZJGSJ","YWWYGL","XQGZSJ","YWGZKJ","YWGZKJ","YWGZKJ_SJDW","YWGZKJ_SGDW",
					"FWJZLX","YDXK","GHXK","SGXK","JGYS","YTBG","XFYS","GSDJ","TZHYXK","JGAQPCJL","WJJL","WFWGSPJL"};
					for(int b=0; b<fileds.length; b++){
						if(b == 6){//是否保护性建筑
							Label label7 = new Label(7, a+6, "");
							Label label8 = new Label(8, a+6, "");
							Label label9 = new Label(9, a+6, "");
							Label label10 = new Label(10, a+6, "");
							Label label11 = new Label(11, a+6, "");
							if(fileds[6] != null){
								String build = one.getStr(fileds[6]);
								if(build == null){

								}else if(build.equals("否")){
									label7.setString(build);
								}else if(!build.equals("")){
									switch (build){
										case "全国重点文保建筑":
											label8.setString("是");
											break;
										case "省级文保建筑":
											label9.setString("是");
											break;
										case "市县级文保建筑":
											label10.setString("是");
											break;
										case "历史建筑":
											label11.setString("是");
											break;
									}
								}
								sheet.addCell(label7);
								sheet.addCell(label8);
								sheet.addCell(label9);
								sheet.addCell(label10);
								sheet.addCell(label11);
								b = 11;
							}
						}else if(b == 27){//有无改建扩建
							Label label29 = new Label(28, a+6, "");
							Label label30 = new Label(29, a+6, "");
							String extendBuild = one.getStr(fileds[28]);
							if(extendBuild != null){
								if(extendBuild.equals("有")){
									label29.setString("有");
								}else if(extendBuild.equals("无")){
									label30.setString("无");
								}
							}
							sheet.addCell(label29);
							sheet.addCell(label30);
							b = 29;
						}
						Label label = new Label(b+1, a+6, one.getStr(fileds[b]));//44列
						sheet.addCell(label);
					}
				}
				wwb.write();
				wwb.close();
				renderNull();
			} catch (UnsupportedEncodingException | URISyntaxException | FileNotFoundException e) {
				throw new ServiceException("[房屋普查信息]导出Excel数据：模板文件读取异常！");
			} catch (BiffException | IOException | WriteException e){
				throw new ServiceException("[房屋普查信息]导出Excel数据：导出数据Excel数据异常！");
			}
		}else{
			renderSuccessJsonMsg("没有符合条件的数据需要导出！");
		}
	}

打完了下班吧