最新导出备份

    /**
     * 导出台账
     */
    @RequestMapping(params = "ExportXltz")
    @ResponseBody
    public void ExportXltz(HttpServletRequest request, ModelMap modelMap, HttpServletResponse response,
            OutputStream output) throws UnsupportedEncodingException { 
        String invoiceno = request.getParameter("invoiceno");//发票号
        String sendTime_begin = request.getParameter("sendTime_begin");//发送日期 
        String sendTime_end = request.getParameter("sendTime_end");//发送日期
        String orderSupply = request.getParameter("orderSupply");//代理商
        String zdDate_begin = request.getParameter("zdDate_begin");//账单日期
        String zdDate_end = request.getParameter("zdDate_end");//账单日期
        String declaredate_begin = request.getParameter("declaredate_begin");//申报日期开始时间
        String declaredate_end = request.getParameter("declaredate_end");//申报日期结束时间 
        
        
         
        String wheresql = " ";
 
        if(invoiceno!=null && !invoiceno.equals("")) {
            wheresql += " and a.invoice_code='" + invoiceno + "'";
        } 
        if(sendTime_begin!=null && !sendTime_begin.equals("")) {
            wheresql += " and a.send_time >='" + sendTime_begin + "'";
        }
        if(sendTime_end!=null && !sendTime_end.equals("")) {
            wheresql += " and a.send_time <='" + sendTime_end + "'";
        }
        if(orderSupply!=null && !orderSupply.equals("")) {
            wheresql += " and c.order_supplyid='" + orderSupply + "'";
        }
        if(zdDate_begin!=null && !zdDate_begin.equals("")) {
            wheresql += " and c.zdDate >='" + zdDate_begin + "'";
        }
        if(zdDate_end!=null && !zdDate_end.equals("")) {
            wheresql += " and c.zdDate <='" + zdDate_end + "'";
        }
        if(declaredate_begin!=null && !declaredate_begin.equals("")) {
            wheresql += " and b.declaration_data >='" + declaredate_begin + "'";
        }
        if(declaredate_end!=null && !declaredate_end.equals("")) {
            wheresql += " and b.declaration_data <='" + declaredate_end + "'";
        }
        
        String sql = " select * " +
                     "  from dec_order a " +
                     "  left join dec_main b on (b.erp_No = a.invoice_code)" +
                     "    left join (select Invoiceno,order_supply,order_supplyid,zdDate" +
                     "                                from ldc_order_tax " +
                     "                            group by Invoiceno) c on (c.Invoiceno = a.invoice_code)"+
                     wheresql + " order by b.create_date desc ";

        String lujing = request.getSession().getServletContext().getRealPath("/");
        String lujing1 = lujing + "export\template\jkfymx.xlsx";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        InputStream in;
        try {
            in = new FileInputStream(new File(lujing1));
            XSSFWorkbook work = null;
            work = new XSSFWorkbook(in);
            this.ExportXltzOut(request, response, work, maps);
        } catch (Exception e) {

            e.printStackTrace();
        }


    }
    /**
     * 导出 
     */
    public void ExportXltzOut(HttpServletRequest request, HttpServletResponse response, XSSFWorkbook work, List<Map<String, Object>> list) {
        
        
        XSSFSheet sheetAt = work.getSheetAt(0);    
        XSSFCellStyle setBorder = work.createCellStyle();
        setBorder.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框
        setBorder.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        setBorder.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        setBorder.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        setBorder.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
        setBorder.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
        
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); 
         
         Map<String, Object> map = new HashMap<>();
         String invoice_code_old = "";
         int cnt = 0;
         for(int i=0;i<list.size();i++){
             if(i<list.size()){
                  map = list.get(i);
             }
             String invoice_code = String.valueOf(map.get("invoice_code"));
            
             XSSFRow row =  sheetAt.createRow(i+2);
            //物流公司名称
             if(map.get("order_supply") !=null) { 
                 row.createCell(0).setCellValue(String.valueOf(map.get("order_supply")));
                 row.getCell(0).setCellStyle(setBorder);
             }else {
                 row.createCell(0).setCellValue("");  
                 row.getCell(0).setCellStyle(setBorder);       
             }
            //进口发票号码
            
             if(map.get("invoice_code") !=null) { 
                 row.createCell(1).setCellValue(String.valueOf(map.get("invoice_code")));
                 row.getCell(1).setCellStyle(setBorder);
             }else {
                 row.createCell(1).setCellValue("");    
                 row.getCell(1).setCellStyle(setBorder);     
             }
             //到厂日期
             JSONObject jsonb = JSONObject.parseObject(String.valueOf(map.get("content")));
             if(StringUtils.isNotBlank(jsonb.getString("dcdate"))) { 
                 row.createCell(4).setCellValue(jsonb.getString("dcdate"));
                 row.getCell(4).setCellStyle(setBorder);
             }else {
                 row.createCell(4).setCellValue("");    
                 row.getCell(4).setCellStyle(setBorder);     
             }
            //报关单号 
             if(map.get("entry_Id") !=null) { 
                 row.createCell(5).setCellValue(String.valueOf(map.get("entry_Id")));
                 row.getCell(5).setCellStyle(setBorder);
             }else {
                 row.createCell(5).setCellValue("");    
                 row.getCell(5).setCellStyle(setBorder);     
             }
            //贸易方式
             if(StringUtils.isNotBlank(jsonb.getString("tradeMode_value"))) { 
                 row.createCell(6).setCellValue(jsonb.getString("tradeMode_value"));
                 row.getCell(6).setCellStyle(setBorder);
             }else {
                 row.createCell(6).setCellValue("");    
                 row.getCell(6).setCellStyle(setBorder);     
             }
           //申报日期
             if(StringUtils.isNotBlank(jsonb.getString("declarationData"))) { 
                 row.createCell(7).setCellValue(jsonb.getString("declarationData"));
                 row.getCell(7).setCellStyle(setBorder);
             }else {
                 row.createCell(7).setCellValue("");    
                 row.getCell(7).setCellStyle(setBorder);     
             }
              
              String str = String.valueOf(map.get("json_extract"));
              JSONObject json = JSONObject.parseObject(str);
              if(json.containsKey("jsBLorawbNo")){
                  String jsBLorawbNo = json.getString("jsBLorawbNo");/**B/L OR AWB NO*/
                  row.createCell(2).setCellValue(jsBLorawbNo);
                  row.getCell(2).setCellStyle(setBorder); 
              }else{
                  row.createCell(2).setCellValue("");
                  row.getCell(2).setCellStyle(setBorder); 
              } 
              if(json.containsKey("jsEta")){
                  String jsEta = json.getString("jsEta");/**ETA*/
                  row.createCell(3).setCellValue(jsEta);
                  row.getCell(3).setCellStyle(setBorder); 
              }else{
                  row.createCell(3).setCellValue("");
                  row.getCell(3).setCellStyle(setBorder); 
              }
              if(json.containsKey("jsHy")){
                  String jsHy = json.getString("jsHy");/**海运整柜（注明箱型）*/
                  row.createCell(8).setCellValue(jsHy);
                  row.getCell(8).setCellStyle(setBorder); 
              }else{
                  row.createCell(8).setCellValue("");
                  row.getCell(8).setCellStyle(setBorder); 
              }
              if(json.containsKey("jsJzxNum")){
                  String jsJzxNum = json.getString("jsJzxNum");/**集装箱数量*/
                  row.createCell(9).setCellValue(jsJzxNum);
                  row.getCell(9).setCellStyle(setBorder);
              }else{
                  row.createCell(9).setCellValue("");
                  row.getCell(9).setCellStyle(setBorder);
              }
              if(json.containsKey("jsWeight")){
                  String jsWeight = json.getString("jsWeight");/**毛重*/
                  row.createCell(10).setCellValue(jsWeight);
                  row.getCell(10).setCellStyle(setBorder);
              }else{
                  row.createCell(10).setCellValue("");
                  row.getCell(10).setCellStyle(setBorder);
              } 
              if(json.containsKey("jsTj")){
                  String jsTj = json.getString("jsTj");/**体积*/
                  row.createCell(11).setCellValue(jsTj);
                  row.getCell(11).setCellStyle(setBorder);
              }else{
                  row.createCell(11).setCellValue("");
                  row.getCell(11).setCellStyle(setBorder);
              }
              if(json.containsKey("jsSumfy")){
                  String jsSumfy = json.getString("jsSumfy");/**物流费用合计金额*/
                  row.createCell(30).setCellValue(jsSumfy);
                  row.getCell(30).setCellStyle(setBorder);
              }else{
                  row.createCell(30).setCellValue("");
                  row.getCell(30).setCellStyle(setBorder);
              }
              /**关税金额（必须和关税单金额核对）*/
              String tariff = "";//关税
              String addedTax = "";//增值税
              double sumNum = 0;//同发票号关税和
              double sumNum1 = 0;//同发票号增值税和
              if(jsonb.containsKey("decLists")) { 
                  String string = jsonb.getString("decLists"); 
                  
                  JSONArray picArray = JSONArray.parseArray(string);
                  for(int q =0;q<picArray.size();q++){
                      JSONObject json1 = picArray.getJSONObject(q);
                      if(json1.get("tariff")!=null){
                          sumNum += Double.parseDouble(json1.getString("tariff")); //**关税金额（必须和关税单金额核对）*//*
                          sumNum1 += Double.parseDouble(json1.getString("addedTax"));
                    }
                  }
                  
                  
                  row.createCell(31).setCellValue(sumNum+" ");
                  row.getCell(31).setCellStyle(setBorder);
                   
                  row.createCell(32).setCellValue(sumNum1+" ");
                  row.getCell(32).setCellStyle(setBorder);
             
              } 
             
              if(json.containsKey("jsFybm")){
                  String jsFybm = json.getString("jsFybm");/**费用部门*/
                  row.createCell(33).setCellValue(jsFybm);
                  row.getCell(33).setCellStyle(setBorder);
              }else{
                  row.createCell(33).setCellValue("");
                  row.getCell(33).setCellStyle(setBorder);
              }
             
             
              String sql = " select * from ldc_order_tax a where  a.Invoiceno = '"+invoice_code+"' ";
              List<Map<String, Object>> list1 = jdbcTemplate.queryForList(sql);
              for(int j=12;j<31;j++){
                  row.createCell(j).setCellValue(""); 
                }
              for(int k=0;k<list1.size();k++){
                  Map<String, Object> map1 = new HashMap<>();
                  if(k<list1.size()){
                      map1 = list1.get(k);
                  }
                  if(map1.get("order_tax_name")!=null){
                      String order_tax_name = String.valueOf(map1.get("order_tax_name"));
                      String order_tax = String.valueOf(map1.get("order_tax"));
                      if(map1.get("order_tax")!=null){
                          order_tax = String.valueOf(map1.get("order_tax"));       
                      }else{
                          order_tax = "";       
                      }
                      if("报关费".equals(order_tax_name)){ 
                          row.createCell(12).setCellValue(order_tax);       
                      }
                      if("换单费".equals(order_tax_name)) {
                          row.createCell(13).setCellValue(order_tax);
                        }
                        if("查验费".equals(order_tax_name)) {
                            row.createCell(14).setCellValue(order_tax);
                        }
                        if("港杂费".equals(order_tax_name)) {
                            row.createCell(15).setCellValue(order_tax);        
                        }
                        if("THC".equals(order_tax_name)) {
                            row.createCell(16).setCellValue(order_tax);
                        }
                        if("LSSBAFCAF".equals(order_tax_name.trim())) {
                            row.createCell(17).setCellValue(order_tax);            
                        }
                        if("报检费安保费".equals(order_tax_name)) { 
                            row.createCell(18).setCellValue(order_tax);
                        }
                        if("换单代理费".equals(order_tax_name)) {
                            row.createCell(19).setCellValue(order_tax);
                        }
                        if("三检费".equals(order_tax_name)) {
                            row.createCell(20).setCellValue(order_tax);            
                        }
                        if("代垫仓库理货费(港建 理货 搬移 )".equals(order_tax_name)) {                         
                            row.createCell(21).setCellValue(order_tax);                
                        }
                        if("仓储费".equals(order_tax_name)) {
                            row.createCell(22).setCellValue(order_tax);                 
                        }
                        if("木托销毁".equals(order_tax_name)) {
                            row.createCell(23).setCellValue(order_tax);                
                        }
                        if("检验代理费".equals(order_tax_name)) {
                            row.createCell(24).setCellValue(order_tax);    
                        }
                        if("拆箱费".equals(order_tax_name)) {
                            row.createCell(25).setCellValue(order_tax);    
                        }
                        if("运费".equals(order_tax_name)) {
                            row.createCell(26).setCellValue(order_tax);    
                        }
                        if("坏污箱费".equals(order_tax_name)) {
                            row.createCell(27).setCellValue(order_tax);    
                        }
                        if("集装箱超期费".equals(order_tax_name)) {
                            row.createCell(28).setCellValue(order_tax);    
                        }
                        if("其它".equals(order_tax_name)) {
                            row.createCell(29).setCellValue(order_tax);    
                        }
                        if("物流费".equals(order_tax_name)) {
                            row.createCell(30).setCellValue(order_tax);    
                        }
//                        if("关税".equals(order_tax_name)) {
//                           row.createCell(31).setCellValue(order_tax);    
//                        }
//                        if("增值税".equals(order_tax_name)) {
//                           row.createCell(32).setCellValue(order_tax);    
//                        }
                      
                      
                      
                  }
                  
              }
              if(invoice_code.equals(invoice_code_old)){
                  cnt++;
                  if(i==list.size()-1){
                      //当最后一行时看情况合并
                      for(int j=0;j<34;j++){
                          if(j!=4&&j!=5&&j!=6&&j!=7){
                              row.getCell(j).setCellValue("");
                              CellRangeAddress region1 = new CellRangeAddress(i-cnt+2, i+2, (short) j, (short) j);
                              sheetAt.addMergedRegion(region1); 
                          }
                      }
                  }  
                  
              }else{
                  if(cnt>0){
                      //合并单元格
                      for(int j=0;j<34;j++){
                          if(j!=4&&j!=5&&j!=6&&j!=7){
                              row.getCell(j).setCellValue("");
                              CellRangeAddress region1 = new CellRangeAddress(i+2-cnt-1, i-1+2, (short) j, (short) j);
                              sheetAt.addMergedRegion(region1);
                          }
                      }
                  }  
                   cnt=0;
              }
              invoice_code_old=invoice_code;
              for(int j=12;j<31;j++){
                row.getCell(j).setCellStyle(setBorder);
              }
             
         }
         //合计
         XSSFRow row =  sheetAt.createRow(list.size()+3);
         for (int i = 0; i <34; i++) {
             row.createCell(i);
             row.getCell(i).setCellStyle(setBorder); 
         }
         row.createCell(0).setCellValue("合计：");
         row.getCell(0).setCellStyle(setBorder); 

         row.getCell(8).setCellStyle(setBorder); 
         for(int j=12;j<33;j++){
             double sum = 0;
             for(int i=2;i<list.size()+2;i++){
                 String value = sheetAt.getRow(i).getCell(j).getStringCellValue();
                 if(StringUtils.isEmpty(value)){
                     value="0";
                 }
                 sum += Double.parseDouble(value);
             }
             row.createCell(j).setCellValue(sum);
             row.getCell(j).setCellStyle(setBorder); 
         }
        
        //前台反应
        response.reset();  
        response.setCharacterEncoding("UTF-8");  
        response.setContentType("application/vnd.ms-excel");  //保证不乱码  
        try
        {
            Date date=new Date();
            SimpleDateFormat format=new SimpleDateFormat("MMddHHmmss");
            String time="bb"+format.format(date)+".xlsx";
            response.setHeader("Content-Disposition","attachment;" + " filename=" + new String(time.getBytes("utf-8"), "ISO-8859-1"));
           
        }
        catch (UnsupportedEncodingException e1) {        
            e1.printStackTrace();
        }  
        try  
        {  
            ByteArrayOutputStream oss =new  ByteArrayOutputStream(); 
            OutputStream os = response.getOutputStream();
            work.write(oss);  
              
            byte temp[] = oss.toByteArray();  
            ByteArrayInputStream in1 = new ByteArrayInputStream(temp);  
            int n = 0;      
            while ((n = in1.read(temp)) >0) {      
                 os.write(temp, 0, n);      
             }    
            os.flush();  
            os.close();  
        } catch(Exception e){
        
            e.printStackTrace();
        }
        
    }