一、导出数据
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("导入成功");
}
四、多sheet导出excel
@PostMapping(value = "/exportResultsData")
@ApiOperation(value = "导出成果数据", notes = "导出成果数据")
public void exportResultsData(HttpServletResponse response, String tableName, String id) throws Exception {
try {
String sql = "";
String[] idArr = id.split(",");
String[] cadFileId =null;
if ("t_task_info_view".equals(tableName)) {
if (StringUtils.isNotEmpty(id)) {
cadFileId = new String[idArr.length];
String ids = id.replace('\"', '\'');
sql = "select distinct id from res_attachment where rel_resid in (" + ids + ") and file_type='dwg'";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
if (list.isEmpty()) {
throw new Exception("所选任务下未上传CAD图纸");
} else {
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
String id1 = (String) map.get("id");
cadFileId[i] = id1;
}
}
} else {
sql = "select distinct id from res_attachment where file_type='dwg'";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
cadFileId = new String[list.size()];
if (list.isEmpty()) {
throw new Exception("任务下未上传CAD图纸");
} else {
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
String id1 = (String) map.get("id");
cadFileId[i] = id1;
}
}
}
} else if ("t_project_info_view".equals(tableName)) {
if (StringUtils.isNotEmpty(id)) {
String ids = id.replace('\"', '\'');
sql = "select distinct id from t_task_info_view where rel_proj_id in(" + ids + ")";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
cadFileId = new String[list.size()];
if (list.isEmpty()) {
throw new Exception("项目下无任务信息！");
} else {
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
String id1 = (String) map.get("id");
sql = "select distinct id from res_attachment where rel_resid ='" + id1 + "' and file_type='dwg'";
List<Map<String, Object>> list1 = jdbcTemplate.queryForList(sql);
if (list1.isEmpty()) {
cadFileId[i] ="";
} else {
String id2 = (String) list1.get(0).get("id");
cadFileId[i] = id2;
}
}
}
} else {
sql = "select distinct id from t_task_info_view ";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
cadFileId = new String[list.size()];
if (list.isEmpty()) {
throw new Exception("项目下无任务信息！");
} else {
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
String id1 = (String) map.get("id");
sql = "select distinct id from res_attachment where rel_resid ='" + id1 + "' and file_type='dwg'";
List<Map<String, Object>> list1 = jdbcTemplate.queryForList(sql);
if (list1.isEmpty()) {
cadFileId[i] ="";
} else {
String id2 = (String) list1.get(0).get("id");
cadFileId[i] = id2;
}
}
}
}
}
//创建sheet
Workbook workBook = null;
try {
String idStr = "";
if (cadFileId.length > 0) {
for (String str : cadFileId) {
if(str.isEmpty()){
continue;
}else{
idStr += ",'" + str + "'";
}
}
idStr = idStr.substring(1);
}
//创建第一个sheet-基站
sql = "select * from t_base_station_info where cad_file_id in(" + idStr + ")";
List<TBaseStationInfo> tBaseStationInfoList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<TBaseStationInfo>(TBaseStationInfo.class));
// System.err.println(JSONArray.toJSONString(tBaseStationInfoList));
// 创建参数对象（用来设定excel得sheet得内容等信息）
ExportParams tBaseStationInfoParams = new ExportParams();
// 设置sheet得名称
tBaseStationInfoParams.setSheetName("基站");
// 创建sheet1使用得map
Map<String, Object> tBaseStationInfoMap = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
tBaseStationInfoMap.put("title", tBaseStationInfoParams);
// 模版导出对应得实体类型
tBaseStationInfoMap.put("entity", TBaseStationInfo.class);
// sheet中要填充得数据
tBaseStationInfoMap.put("data", tBaseStationInfoList);
//创建第二个sheet-设备
sql = "select * from t_device_list where cad_file_id in(" + idStr + ")";
List<TDeviceList> tDeviceListList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<TDeviceList>(TDeviceList.class));
// System.err.println(JSONArray.toJSONString(tDeviceListList));
ExportParams tDeviceListParams = new ExportParams();
tDeviceListParams.setSheetName("设备");
Map<String, Object> tDeviceListMap = new HashMap<>();
tDeviceListMap.put("title", tDeviceListParams);
tDeviceListMap.put("entity", TDeviceList.class);
tDeviceListMap.put("data", tDeviceListList);
// 将sheet1-2使用的map进行包装
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(tBaseStationInfoMap);
sheetsList.add(tDeviceListMap);
// 执行方法
workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
//设置编码格式
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
//设置内容类型
response.setContentType("application/octet-stream");
//设置头及文件命名。
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("成果数据导出.xlsx", StandardCharsets.UTF_8.name()));
//写出流
workBook.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workBook != null) {
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
} catch (Exception e) {
throw new Exception("获取成果文件数据异常，请联系管理员！");
}
}
excel按照模板导出
/**
* 导出审核报告
*/
@PostMapping(value = "/exportByTem",produces = "application/octet-stream")
@ApiOperation(value = "导入审核报告",notes = "导入审核报告")
public void exportJg(HttpServletResponse response, HttpServletRequest request, String taskid) throws Exception{
/**
*如果是在SSM项目中用以下方式，模板直接放在webapp下
* String path = request.getSession().getServletContext().getRealPath("/")+"template/技工报名表.xls";
* TemplateExportParams params = new TemplateExportParams(path);
*/
if(taskid.contains(",")){
throw new Exception("请选择单条任务进行导出报告！");
}
//取到要导出的模板
TemplateExportParams params = new TemplateExportParams("static/drawing_file_audit_report.xls");
if (null!=params){
//获取附件id
String sql="select id from res_attachment where rel_resid='"+ taskid +"'";
List<Map<String,Object>> idList = jdbcTemplate.queryForList(sql);
String cadFileId="";
if(idList.isEmpty()){
throw new Exception("该任务下未上传cad文件，无法导出审核报告！");
}else {
cadFileId = MapUtils.getString(idList.get(0),"id","");
}
//创建模板map
Map<String, Object> map = new HashMap<String, Object>();
//创建站点数据list
List<Map<String, Object>> listMap = new ArrayList<>();
//创建审核项数据list
List<Map<String, Object>> listCheckMap = new ArrayList<>();
//审核报告站点信息获取
sql="select * from t_base_station_info where cad_file_id='"+cadFileId +"'";
List<Map<String,Object>> stationList = jdbcTemplate.queryForList(sql);
if(stationList.isEmpty()){
throw new Exception("请先进行图纸处理！");
}else{
//创建站点数据map
Map<String,Object> stationMap = stationList.get(0);
Map<String,Object> resultMap = new LinkedHashMap<>();
resultMap.put("bt1","项目名称：");
resultMap.put("value1",MapUtils.getString(stationMap,"project_name",""));
resultMap.put("bt2","设计单位：");
resultMap.put("value2",MapUtils.getString(stationMap,"design_unit",""));
listMap.add(resultMap);
resultMap = new LinkedHashMap<>();
resultMap.put("bt1","站点名称：");
resultMap.put("value1",MapUtils.getString(stationMap,"bs_name",""));
resultMap.put("bt2","站点编码：");
resultMap.put("value2",MapUtils.getString(stationMap,"bs_number",""));
listMap.add(resultMap);
resultMap = new LinkedHashMap<>();
resultMap.put("bt1","站址：");
resultMap.put("value1",MapUtils.getString(stationMap,"bs_address",""));
resultMap.put("bt2","站点类型：");
resultMap.put("value2",MapUtils.getString(stationMap,"construct_type",""));
listMap.add(resultMap);
resultMap = new LinkedHashMap<>();
resultMap.put("bt1","站点产权：");
resultMap.put("value1",MapUtils.getString(stationMap,"bs_property",""));
resultMap.put("bt2","组网方式：");
resultMap.put("value2",MapUtils.getString(stationMap,"networking_mode",""));
listMap.add(resultMap);
resultMap = new LinkedHashMap<>();
resultMap.put("bt1","设计人：");
resultMap.put("value1",MapUtils.getString(stationMap,"design_man",""));
resultMap.put("bt2","审核日期：");
resultMap.put("value2",MapUtils.getString(stationMap,"issue_date",""));
listMap.add(resultMap);
resultMap = new LinkedHashMap<>();
resultMap.put("bt1","是否新增电源设备：");
String value1 = MapUtils.getString(stationMap,"is_adding_power_supplies","");
if("Y".equals(value1)){
value1 = "是";
}else if("N".equals(value1)){
value1 = "否";
}
resultMap.put("value1",value1);
resultMap.put("bt2","是否为极简站：");
String value2 = MapUtils.getString(stationMap,"is_simple_bs","");
if("Y".equals(value2)){
value2 = "是";
}else if("N".equals(value2)){
value2 = "否";
}
resultMap.put("value2",value2);
listMap.add(resultMap);
resultMap = new LinkedHashMap<>();
resultMap.put("bt1","是否需要申报共建共享系统：");
value1 = MapUtils.getString(stationMap,"is_share","");
if("Y".equals(value1)){
value1 = "是";
}else if("N".equals(value1)){
value1 = "否";
}
resultMap.put("value1",value1);
resultMap.put("bt2","是否高风险作业：");
value2 = MapUtils.getString(stationMap,"is_high_risk_operation","");
if("Y".equals(value2)){
value2 = "是";
}else if("N".equals(value2)){
value2 = "否";
}
resultMap.put("value2",value2);
listMap.add(resultMap);
}
//获取审核项
sql ="select * from t_audit_report where cad_file_id = '"+ cadFileId +"' order by sord_no";
List<Map<String,Object>> reportList = jdbcTemplate.queryForList(sql);
if(reportList.isEmpty()){
throw new Exception("请先进行图纸处理！");
}else{
for(Map<String,Object> reportmap : reportList){
//创建审核项map
Map<String,Object> resultCheckMap = new LinkedHashMap<>();
resultCheckMap.put("xh",MapUtils.getString(reportmap,"sord_no",""));
resultCheckMap.put("shx",MapUtils.getString(reportmap,"audit_items",""));
String shjl = MapUtils.getString(reportmap,"is_simple_bs","");
if("1".equals(shjl)){
shjl = "合格";
}else if("2".equals(shjl)){
shjl = "不合格";
}
resultCheckMap.put("shjl",shjl);
resultCheckMap.put("bhgyy",MapUtils.getString(reportmap,"cause_of_nonconformity",""));
listCheckMap.add(resultCheckMap);
}
}
map.put("maplist", listMap);
map.put("checkmaplist", listCheckMap);
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
ServletOutputStream out = null;
try {
//流的形式传输数据
response.setHeader("content-type","application/octet-stream");
//防止中文乱码
response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode("图纸范审核报告.xls","UTF-8"));
out=response.getOutputStream();
workbook.write(out);
}catch (IOException e){
e.printStackTrace();
}finally {
if (null!=out){
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
模板
1569
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
