1、接到了一个老项目的项目改造,增加房屋普查信息的功能,主要是下载Excel模板,然后在模板填写数据,在导入。还一个是根据模板写入数据下载到本地。
2、老版本的玩意网上都不好找咋玩。
1、标题第一步看看easyexcel-1.1.2-beta5.jar这个玩意依赖哪些库,统统下载导入进来
maven库给你贴上,你想咋下载就咋下载
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("没有符合条件的数据需要导出!");
}
}