一、导出数据
public void exportTableInfo(HttpServletResponse response, String tableName) throws Exception{
try{
String sql = "select * from "+ tableName +"";
if("t_base_station_info".equals(tableName)){
List<TBaseStationInfo> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<TBaseStationInfo>(TBaseStationInfo.class));
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("基站信息表导出", "CAD数据归集日志表导出"), TBaseStationInfo.class, list);
//使用流将excel写入到指定的位置
//FileOutputStream outputStream = new FileOutputStream("D:/基站信息表导出.xlsx");//指定写出的位置
//workbook.write(outputStream);//将数据输出
//关闭流
//workbook.close();
//outputStream.close();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
String fileName ="基站信息表导出";
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
workbook.close();
}else{
throw new Exception("此模型表不存在，请联系管理员");
}
}catch (Exception e){
throw new Exception("导出失败,请联系管理员");
}
}
二、下载导入模板
public void exportByTemplate(HttpServletResponse response,String tableName) throws IOException {
try {
if("t_base_station_info".equals(tableName)){
List<TBaseStationInfo> list = new ArrayList<>();
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("基站信息表导出模板", "CAD数据归集日志表导出模板"), TBaseStationInfo.class, list);
//使用流将excel写入到指定的位置
//FileOutputStream outputStream = new FileOutputStream("D:/基站信息表导出模板.xlsx");//指定写出的位置
//workbook.write(outputStream);//将数据输出
//关闭流
//workbook.close();
//outputStream.close();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
String fileName ="基站信息表导出";
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
workbook.close();
}else {
throw new Exception("此模型表不存在，请联系管理员");
}
} catch (Exception e) {
throw new IOException("导出失败,请联系管理员");
}
}
三、导入数据
public AjaxResult importTableTemplate(@RequestPart("file") MultipartFile file,String tableName) throws Exception{
try{
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(1);
// params.setSheetNum(1);
if("t_base_station_info".equals(tableName)){
List<TBaseStationInfo> list = ExcelImportUtil.importExcel(file.getInputStream(),TBaseStationInfo.class, params);
for(TBaseStationInfo tBaseStationInfo : list){
dataMaintenanceTableMapper.tBaseStationInfoInsert(tBaseStationInfo);
}
}else{
return AjaxResult.error("此模型表不存在！请联系管理员");
}
}catch (Exception e){
throw new Exception("导入失败，请联系管理员");
}
return AjaxResult.success("导入成功");
}
