//Excel文本导入到本地数据库 具体看下面代码
public class Response implements Serializable {//工具类 private boolean success = true; private Object result = null; private String error = ""; } 
public Response importExcel(HttpServletRequest request, @RequestParam("excelFile") MultipartFile excelFile) throws Exception {
//excelFile 文件名称 Response response = new Response(); BaseUser baseUser = (BaseUser) request.getSession().getAttribute("user"); List<List<List<String>>> data = ExcelUtil.readXlsxNotBlank(excelFile);//该工具类下面会帖出来 Map<String, String> params = null; List<Map<String, String>> lists = new ArrayList<Map<String, String>>(); String jsonString = null; Integer customer_cclstore_index = null;//店号 String customer_ccl_store_code = null; Integer customer_ccl_store_index_name = null;//门店名称 String customer_ccl_store_name = null; Integer customer_ccl_merchant_index_name = null;//商户名称 String customer_ccl_merchant_name = null; Integer customer_ccl_store_index_province_name = null;//省 String customer_ccl_store_province_name= null; Integer customer_ccl_store_index_city_name = null;//市 String customer_ccl_store_city_name = null; Integer customer_ccl_store_index_address = null;//地址 String customer_ccl_store_address = null; Integer customer_ccl_store_index_contacts = null;//联系人 String customer_ccl_store_contacts = null; Integer customer_ccl_store_index_mobile = null;//联系电话 String customer_ccl_store_mobile = null; Integer customer_ccl_store_index_email = null;//邮箱 String customer_ccl_store_email = null; int count = 0; try{//开始遍历表格
for (List<List<String>> result : data) { if (result.size() < 1) { continue; } for (int i = 0; i < result.size(); i++) { List<String> list = result.get(i);//获取到每一行 if (i == 0 || i==1 ) {//这里的意思是只遍历标题和表头名称（如上表格），并且表头名称和我们定义的必须一致否则则报空指针异常 for (int j = 0; j < list.size(); j++) {//获取到每一行的每一个单元格的内容 if (list.get(j) != null && "店号".equals(list.get(j).toString())) { customer_cclstore_index = j; } else if (list.get(j) != null && "门店名称".equals(list.get(j).toString())) { customer_ccl_store_index_name = j; } else if (list.get(j) != null && "商户".equals(list.get(j).toString())) { customer_ccl_merchant_index_name = j; } else if (list.get(j) != null && "省".equals(list.get(j).toString())) { customer_ccl_store_index_province_name = j; } else if (list.get(j) != null && "市".equals(list.get(j).toString())) { customer_ccl_store_index_city_name = j; } else if (list.get(j) != null && "地址".equals(list.get(j).toString())) { customer_ccl_store_index_address = j; }else if (list.get(j) != null && "联系人".equals(list.get(j).toString())) { customer_ccl_store_index_contacts = j; }else if (list.get(j) != null && "联系电话".equals(list.get(j).toString())) { customer_ccl_store_index_mobile = j; }else if (list.get(j) != null && "邮箱".equals(list.get(j).toString())) { customer_ccl_store_index_email = j; } } } else { params = new HashMap<String, String>();//获取到每一行的每一个单元格的内容，这里做了非空判断
customer_ccl_store_code = list.get(customer_cclstore_index) == null ? "" : list.get(customer_cclstore_index).toString();customer_ccl_store_name = list.get(customer_ccl_store_index_name) == null ? "" : list.get(customer_ccl_store_index_name).toString();customer_ccl_merchant_name = list.get(customer_ccl_merchant_index_name) == null ? "" : list.get(customer_ccl_merchant_index_name).toString();customer_ccl_store_province_name = list.get(customer_ccl_store_index_province_name) == null ? "" : list.get(customer_ccl_store_index_province_name).toString();customer_ccl_store_city_name = list.get(customer_ccl_store_index_city_name) == null ? "" : list.get(customer_ccl_store_index_city_name).toString();customer_ccl_store_address = list.get(customer_ccl_store_index_address) == null ? "" : list.get(customer_ccl_store_index_address).toString();customer_ccl_store_contacts = list.get(customer_ccl_store_index_contacts) == null ? "" : list.get(customer_ccl_store_index_contacts).toString();customer_ccl_store_mobile = list.get(customer_ccl_store_index_mobile) == null ? "" : list.get(customer_ccl_store_index_mobile).toString();customer_ccl_store_email = list.get(customer_ccl_store_index_email) == null ? "" : list.get(customer_ccl_store_index_email).toString();params.put("customer_ccl_store_code", customer_ccl_store_code);params.put("customer_ccl_store_name", customer_ccl_store_name);params.put("customer_ccl_merchant_name", customer_ccl_merchant_name);params.put("customer_ccl_store_province_name", customer_ccl_store_province_name);params.put("customer_ccl_store_city_name", customer_ccl_store_city_name);params.put("customer_ccl_store_address", customer_ccl_store_address);params.put("customer_ccl_store_contacts", customer_ccl_store_contacts);params.put("customer_ccl_store_mobile", customer_ccl_store_mobile);params.put("customer_ccl_store_email", customer_ccl_store_email);lists.add(params);//循环找到每一个值放到list集合} } } }catch (Exception e){ e.getMessage ();response.setError ( "文件格式错误！请重新选择！" );response.setSuccess ( false );return response;}jsonString = JSONArray.fromObject(lists).toString();List<Map<String, String>> lists = JSONArray.fromObject(jsonString);
//具体业务代码存到数据库}//工具类
ExcelUtil.readXlsxNotBlank
public static List<List<List<String>>> readXlsxNotBlank(MultipartFile excelFile) throws IOException {
String originFileName = excelFile.getOriginalFilename(); if(!originFileName.endsWith(".xlsx") || StringUtils.isEmpty(originFileName)){ throw new NullPointerException("文件格式错误"); } XSSFWorkbook xssfWorkbook = new XSSFWorkbook(excelFile.getInputStream()); List<List<List<String>>> data = new ArrayList<>(); //循环每一页，并处理当前页 for(XSSFSheet xssfSheet : xssfWorkbook) { if(xssfSheet == null) { continue; } List<List<String>> result = new ArrayList<List<String>>(); //处理当前页，循环读取每一行 for(int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum); if (StringUtils.isEmpty(xssfRow)) { continue; } int minColIx = xssfRow.getFirstCellNum(); int maxColIx = xssfRow.getLastCellNum(); List<String> rowList = new ArrayList<String>(); // 遍历这行，获取处理每个cell元素 int ia=minColIx; for(int colIx = minColIx; colIx < maxColIx; colIx++) { XSSFCell cell = xssfRow.getCell(colIx); if(cell != null){ cell.setCellType(XSSFCell.CELL_TYPE_STRING); } if (cell == null||cell.toString()==null||cell.toString().trim().length()==0) ia++; rowList.add(cell == null?null:cell.toString()); } if(ia!=maxColIx) result.add(rowList); } data.add(result); } return data; }//========================================================================================================导出
public Response exportList(HttpServletRequest request , HttpServletResponse responses ,String merchant_id){
Response response = new Response(); response = crmStore.exportList(merchant_id); //首先根据自己得需求查询出需要的数据（查询数据） ExportCrmDaos ex = new ExportCrmDaos();//工具类 if(response.getResult () != null){ //导出方法 ex.exportExcel ( responses, (List<Map<String, Object>>) response.getResult() ); }else{ Response errResponse = new Response(); errResponse.setSuccess(false); errResponse.setError("查询失败"); responses.setContentType("application/json; charset=utf-8"); PrintWriter out = null; try{ out = responses.getWriter(); out.println( JSONObject.fromObject(errResponse).toString()); }catch(Exception e){ log.error(e.getMessage()); }finally { if(out!=null){ out.close(); } } } return response; }
public Response exportList(String merchant_id){
Response response = new Response (); List<Map<String,Object>> fault_list = new ArrayList<Map<String,Object>>(); StringBuffer sql = new StringBuffer(); List<Object> params_fault = new ArrayList<Object>(); List<Map<String,Object>> findeListByName = jdbcTemplate.queryForList ( " select id,name as nameMerchant from customer_ccl_merchant where id= ?" ,new Object[]{merchant_id} ); sql.append ( "select s.*,m.name as merchant_name from customer_ccl_store s join customer_ccl_merchant m on s.merchant_id = m.id where 1=1 \n" ); if(merchant_id != null && !merchant_id.equals ( "" ) ){ sql.append(" and m.name = ? "); params_fault.add(findeListByName.get ( 0 ).get ( "nameMerchant" )); System.err.println ( "####################################"+findeListByName.get ( 0 ).get ( "nameMerchant" ) ); fault_list = jdbcTemplate.queryForList(sql.toString(),params_fault.toArray()); }else{ fault_list = jdbcTemplate.queryForList(sql.toString()); } response.setResult(fault_list); return response; }
//工具类
public class ExportCrmDaos {
Logger log = Logger.getLogger(this.getClass()); /* Integer no_index_ = 0; String no_title_ = "序号";*/ Integer no_index = 0; String no_title = "店号"; //store_code Integer fault_no_index = 1; String fault_no_title = "门店名称";//store_name Integer merchant_index = 2; String merchant_title = "商户"; //merchant_name Integer store_index = 3; String store_title = "省";//province_name Integer province_index = 4; String province_title = "市";//city_name Integer city_index = 5; String city_title = "地址";//address Integer address_index = 6; String address_title = "联系人";//contacts Integer fault_describe_index = 7; String fault_describe_title = "联系电话";//mobile Integer model_index = 8; String model_title = "邮箱";//email public void exportExcel(HttpServletResponse httpResponse, List<Map<String,Object>> fault_list) { // System.err.println ( "共"+fault_list+"数据" ); //设置下载信息 String file_name = DateUtil.format(new Date ())+".xlsx"; httpResponse.setContentType("application/force-download"); httpResponse.addHeader("Content-Disposition", "attachment;fileName=" + file_name); //创建下载对象 try{ XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(); //创建标题 合并单元格 CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 9); sheet.addMergedRegion(titleRegion); Row titleRow = sheet.createRow(0); titleRow.createCell(0).setCellValue("门店记录"); //创建表头 Row lableRow = sheet.createRow(1); // lableRow.createCell(no_index_).setCellValue(no_title_); lableRow.createCell(no_index).setCellValue(no_title); lableRow.createCell(fault_no_index).setCellValue(fault_no_title); lableRow.createCell(merchant_index).setCellValue(merchant_title); lableRow.createCell(store_index).setCellValue(store_title); lableRow.createCell(province_index).setCellValue(province_title); lableRow.createCell(city_index).setCellValue(city_title); lableRow.createCell(address_index).setCellValue(address_title); lableRow.createCell(fault_describe_index).setCellValue(fault_describe_title); lableRow.createCell(model_index).setCellValue(model_title); //填写内容 int now_row_index = 2; //int row_no = 1; for(Map<String,Object> fault_info : fault_list){ Row dataRow = sheet.createRow(now_row_index); // dataRow.createCell(no_index_).setCellValue(row_no);merchant_name dataRow.createCell(no_index).setCellValue( ObjectUtils.isEmpty(fault_info.get("code"))?"":fault_info.get("code").toString()); dataRow.createCell(fault_no_index).setCellValue( ObjectUtils.isEmpty(fault_info.get("name"))?"":fault_info.get("name").toString()); dataRow.createCell(merchant_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("merchant_name"))?"":fault_info.get("merchant_name").toString()); dataRow.createCell(store_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("province_name"))?"":fault_info.get("province_name").toString()); dataRow.createCell(province_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("city_name"))?"":fault_info.get("city_name").toString()); dataRow.createCell(city_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("address"))?"":fault_info.get("address").toString()); dataRow.createCell(address_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("contacts"))?"":fault_info.get("contacts").toString()); dataRow.createCell(fault_describe_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("mobile"))?"":fault_info.get("mobile").toString()); dataRow.createCell(model_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("email"))?"":fault_info.get("email").toString()); now_row_index+=1; //row_no++; } httpResponse.setContentType("application/octet-stream;charset=UTF-8"); httpResponse.setHeader("Content-Disposition", "attachment;filename="+ System.currentTimeMillis()+".xlsx"); httpResponse.addHeader("Pargam", "no-cache"); httpResponse.addHeader("Cache-Control", "no-cache"); //输出 OutputStream outputStream = httpResponse.getOutputStream(); workbook.write(outputStream); }catch (Exception e1){ e1.printStackTrace(); Response errResponse = new Response(); errResponse.setSuccess(false); errResponse.setError("查询失败"); httpResponse.setContentType("application/json; charset=utf-8"); PrintWriter out = null; try{ out = httpResponse.getWriter(); out.println( JSONObject.fromObject(errResponse).toString()); }catch(Exception e2){ log.error(e2.getMessage()); }finally { if(out!=null){ out.close(); } } } }}//*****************************************************************************************************************************************导出
导出对应的前端JS
function xxxx(){
location.href = 'storeContent/exportStoreModeAction.action?export_time_start='+export_time_start+'&export_time_end='+export_time_end;
}
假如我想导出一下的格式
数据一对多怎么导出？？
控制层： dao.exportExcel(httpResponse, (List<Map<String, Object>>) response.getResult());//调用导出工具类代码
Dao层查出来的数据
public Response getListForExport(String merchant_id, String create_time_start, String create_time_end) {
Response response = new Response(); List<Map<String,Object>> fault_list = new ArrayList<Map<String,Object>>(); StringBuffer sql_fault = new StringBuffer(); List<Object> params_fault = new ArrayList<Object>(); sql_fault.append("SELECT f.*,m.`name` AS merchant_name,s.`name` AS store_name,s.province_name,city_name,address "); sql_fault.append("FROM hw_fault f "); sql_fault.append(" left JOIN customer_ccl_merchant m ON f.merchant_id = m.id "); sql_fault.append(" left JOIN customer_ccl_store s ON f.store_id = s.id "); sql_fault.append("WHERE 1 = 1 "); if(!StringUtils.isEmpty(merchant_id)){ sql_fault.append(" and f.merchant_id = ? "); params_fault.add(merchant_id); } if(!StringUtils.isEmpty(create_time_start)){ sql_fault.append(" and f.create_time >= ? "); params_fault.add(DateUtil.getDateFormat(create_time_start,"yyyy-MM-dd HH:mm").getTime()); } if(!StringUtils.isEmpty(create_time_end)){ sql_fault.append(" and f.create_time <= ? "); params_fault.add(DateUtil.getDateFormat(create_time_end,"yyyy-MM-dd HH:mm").getTime()); } fault_list = jdbcTemplate.queryForList(sql_fault.toString(),params_fault.toArray()); StringBuffer sql_worklist = new StringBuffer(); sql_worklist.append("SELECT w.*,a.`name` AS aftersale_name "); sql_worklist.append("FROM hw_worklist w "); sql_worklist.append(" left JOIN customer_aftersale a ON w.aftersale_id = a.id "); sql_worklist.append("WHERE w.fault_id = ? "); for(Map<String,Object> fault_info : fault_list){ List<Map<String,Object>> worklist_list = new ArrayList<Map<String,Object>>(); worklist_list = jdbcTemplate.queryForList(sql_worklist.toString(),new Object[]{ fault_info.get("id").toString()}); //一对多或者一对一 fault_info.put("worklist_list",worklist_list); } response.setResult(fault_list); return response; }工具类：
@Repository
public class ExportFaultDao { Logger log = Logger.getLogger(this.getClass()); Integer no_index = 0; String no_title = "序号"; Integer fault_no_index = 1; String fault_no_title = "故障号"; Integer merchant_index = 2; String merchant_title = "所属商户"; Integer store_index = 3; String store_title = "所属门店"; Integer province_index = 4; String province_title = "省"; Integer city_index = 5; String city_title = "市"; Integer address_index = 6; String address_title = "地址"; Integer fault_describe_index = 7; String fault_describe_title = "故障描述"; Integer model_index = 8; String model_title = "型号"; Integer fault_state_index =9; String fault_state_title= "当前状态"; Integer create_time_index = 10; String create_time_title = "上报时间"; /*Integer aftersale_name_index = 11; String aftersale_name_title ="指派工程师";*/ Integer end_time_index = 11; String end_time_title ="解决时间"; Integer workList_no_index = 12; String workList_no_title = "工单号"; Integer aftersale_index = 13; String aftersale_title = "工程师"; Integer aftersale_create_time_index = 14; String aftersale_create_time_title = "指派时间"; Integer aftersale_acceptance_time_index = 15; String aftersale_acceptance_time_title = "受理时间"; Integer aftersale_service_start_time_index = 16; String aftersale_service_start_time_title = "上门时间"; Integer aftersale_service_end_time_index = 17; String aftersale_service_end_time_title = "完成时间"; Integer aftersale_end_type_index = 18; String aftersale_end_type_title = "服务结果"; Integer aftersale_fault_type_index = 19; String aftersale_fault_type_title = "故障类型"; Integer aftersale_measures_index = 20; String aftersale_measures_title = "解决措施"; Integer aftersale_sparepart_index = 21; String aftersale_sparepart_title = "备件"; public void exportExcel(HttpServletResponse httpResponse, List<Map<String,Object>> fault_list) {//List<Map<String,Object>> fault_list 查出来的参数 //设置下载信息 String file_name = DateUtil.format(new Date())+".xlsx"; httpResponse.setContentType("application/force-download"); httpResponse.addHeader("Content-Disposition", "attachment;fileName=" + file_name); //创建下载对象 try{ XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(); //创建标题 合并单元格 CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 19);//合并表头，这四个参数分别表示：开始行数，最后行数，开始列数，结束列数 sheet.addMergedRegion(titleRegion); Row titleRow = sheet.createRow(0); titleRow.createCell(0).setCellValue("创捷故障报修登记表");//表头的名字 //创建表头标题 Row lableRow = sheet.createRow(1); lableRow.createCell(no_index).setCellValue(no_title); lableRow.createCell(fault_no_index).setCellValue(fault_no_title); lableRow.createCell(merchant_index).setCellValue(merchant_title); lableRow.createCell(store_index).setCellValue(store_title); lableRow.createCell(province_index).setCellValue(province_title); lableRow.createCell(city_index).setCellValue(city_title); lableRow.createCell(address_index).setCellValue(address_title); lableRow.createCell(fault_describe_index).setCellValue(fault_describe_title); lableRow.createCell(model_index).setCellValue(model_title); lableRow.createCell(fault_state_index).setCellValue(fault_state_title);// lableRow.createCell(create_time_index).setCellValue(create_time_title);// lableRow.createCell(end_time_index).setCellValue(end_time_title); lableRow.createCell(workList_no_index).setCellValue(workList_no_title); lableRow.createCell(aftersale_index).setCellValue(aftersale_title); lableRow.createCell(aftersale_create_time_index).setCellValue(aftersale_create_time_title); lableRow.createCell(aftersale_acceptance_time_index).setCellValue(aftersale_acceptance_time_title); lableRow.createCell(aftersale_service_start_time_index).setCellValue(aftersale_service_start_time_title); lableRow.createCell(aftersale_service_end_time_index).setCellValue(aftersale_service_end_time_title); lableRow.createCell(aftersale_end_type_index).setCellValue(aftersale_end_type_title); lableRow.createCell(aftersale_fault_type_index).setCellValue(aftersale_fault_type_title); lableRow.createCell(aftersale_measures_index).setCellValue(aftersale_measures_title); lableRow.createCell(aftersale_sparepart_index).setCellValue(aftersale_sparepart_title); //填写内容 int now_row_index = 2; int row_no = 1; for(Map<String,Object> fault_info : fault_list){ List<Map<String,Object>> worklist_list = (List<Map<String, Object>>) fault_info.get("worklist_list"); //检查是否需要合并单元格 if(worklist_list.size()>1){ for(int i = 0;i<12;i++){ CellRangeAddress dataRegion = new CellRangeAddress(now_row_index, now_row_index+worklist_list.size()-1, i, i); sheet.addMergedRegion(dataRegion); } }//给每一个单元格赋值，循环一次赋值一行
Row dataRow = sheet.createRow(now_row_index); dataRow.createCell(no_index).setCellValue(row_no); dataRow.createCell(fault_no_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("fault_no"))?"":fault_info.get("fault_no").toString()); dataRow.createCell(merchant_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("merchant_name"))?"":fault_info.get("merchant_name").toString()); dataRow.createCell(store_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("store_name"))?"":fault_info.get("store_name").toString()); dataRow.createCell(province_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("province_name"))?"":fault_info.get("province_name").toString()); dataRow.createCell(city_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("city_name"))?"":fault_info.get("city_name").toString()); dataRow.createCell(address_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("address"))?"":fault_info.get("address").toString()); dataRow.createCell(fault_describe_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("fault_describe"))?"":fault_info.get("fault_describe").toString()); dataRow.createCell(model_index).setCellValue(ObjectUtils.isEmpty(fault_info.get("model"))?"":fault_info.get("model").toString()); String type_fault_state = fault_info.get("fault_state").toString (); String typeStrings ; if(!ObjectUtils.isEmpty ( type_fault_state )){ if(type_fault_state.equals ( "0" )){ typeStrings = "未指派"; }else if(type_fault_state.equals ( "1" )){ typeStrings = "未受理"; }else if(type_fault_state.equals ( "2" )){ typeStrings = "已受理"; }else if(type_fault_state.equals ( "3" )){ typeStrings = "未解决"; }else if(type_fault_state.equals ( "4" )){ typeStrings = "已解决"; }else{ typeStrings = "不详"; } }else{ typeStrings = ""; } dataRow.createCell(fault_state_index).setCellValue(typeStrings); dataRow.createCell(create_time_index).setCellValue(DateUtil.getDate(new Date(Long.parseLong(fault_info.get("create_time").toString())),"yyyy-MM-dd HH:mm:ss")); String times = String.valueOf ( fault_info.get("end_time") ); if(times == null || times.equals ( "" ) || times.equals ( "null" )){ // dataRow.createCell(end_time_index).setCellValue(times+"");//解决时间 }else{ Date date = new Date ( ); date.setTime ( Long.valueOf ( times ) ); SimpleDateFormat SDF = new SimpleDateFormat ( "yyyy-MM-dd HH:mm:ss" ); dataRow.createCell(end_time_index).setCellValue(SDF.format ( date ));//解决时间 }
//以上先查询出上面表格红色的字体，下面在查询黄色的内容也就是一对一或者一对多并且合并单元格
for(int i = 0;i<worklist_list.size();i++){ Map<String,Object> worklist_info = worklist_list.get(i); Row subDataRow; if(i==0){ subDataRow = dataRow; }else{ subDataRow = sheet.createRow(now_row_index+i); } subDataRow.createCell(workList_no_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("workList_no"))?"":worklist_info.get("workList_no").toString()); subDataRow.createCell(aftersale_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("aftersale_name"))?"":worklist_info.get("aftersale_name").toString()); subDataRow.createCell(aftersale_create_time_index).setCellValue(DateUtil.getDate(new Date(Long.parseLong(worklist_info.get("create_time").toString())),"yyyy-MM-dd HH:mm:ss")); subDataRow.createCell(aftersale_acceptance_time_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("acceptance_time"))?"":DateUtil.getDate(new Date(Long.parseLong(worklist_info.get("acceptance_time").toString())),"yyyy-MM-dd HH:mm:ss")); subDataRow.createCell(aftersale_service_start_time_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("service_start_time"))?"":DateUtil.getDate(new Date(Long.parseLong(worklist_info.get("service_start_time").toString())),"yyyy-MM-dd HH:mm:ss")); subDataRow.createCell(aftersale_service_end_time_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("service_end_time"))?"":DateUtil.getDate(new Date(Long.parseLong(worklist_info.get("service_end_time").toString())),"yyyy-MM-dd HH:mm:ss")); subDataRow.createCell(aftersale_end_type_index).setCellValue(ObjectUtils.isEmpty(worklist_info.get("end_type"))?"":(worklist_info.get("end_type").toString().equals("1")?"已解决":"未解决")); subDataRow.createCell(aftersale_fault_type_index).setCellValue(worklist_info.get("fault_type")==null?"":worklist_info.get("fault_type").toString()); subDataRow.createCell(aftersale_measures_index).setCellValue(worklist_info.get("measures")==null?"":worklist_info.get("measures").toString()); subDataRow.createCell(aftersale_sparepart_index).setCellValue(worklist_info.get("sparepart")==null?"":worklist_info.get("sparepart").toString()); } if(worklist_list.size() == 0){//这个判断必须有如果一对一 则不需要合并单元格再当前表格行数+1即可 now_row_index +=1; }else{ now_row_index+=worklist_list.size();//如以上表格内容是1对多 必须加上查询出来的多个条数的内容，如当前是52行 查询出了3条数据（一对多），则下次循环必须是+3行而不是+1 } row_no++; } httpResponse.setContentType("application/octet-stream;charset=UTF-8"); httpResponse.setHeader("Content-Disposition", "attachment;filename="+ System.currentTimeMillis()+".xlsx"); httpResponse.addHeader("Pargam", "no-cache"); httpResponse.addHeader("Cache-Control", "no-cache"); //输出 OutputStream outputStream = httpResponse.getOutputStream(); workbook.write(outputStream); }catch (Exception e1){ e1.printStackTrace(); Response errResponse = new Response(); errResponse.setSuccess(false); errResponse.setError("查询失败"); httpResponse.setContentType("application/json; charset=utf-8"); PrintWriter out = null; try{ out = httpResponse.getWriter(); out.println(JSONObject.fromObject(errResponse).toString()); }catch(Exception e2){ log.error(e2.getMessage()); }finally { if(out!=null){ out.close(); } } } }}