1.前台
//导出EXCEL
function export_excle(){
var beginTime = $("#dateBoxBegin").datebox("getValue");
var endTime = $("#dateBoxEnd").datebox("getValue");
$.post(sy.contextPath + '/base/contract-record!doNotNeedSessionAndSecurity_export.sy', {
export_flag:export_flag,beginTime:beginTime,endTime:endTime
}, function(data) {
if(data.success){
$('#dd').dialog('open');
$('#downloadurl').attr("href",sy.contextPath+"/upload/"+data.msg);
}else{
alert("没有查询到相关数据!");
}
}, 'json');
}
2.后台
//台账导出EXCEL
public void doNotNeedSessionAndSecurity_export() throws IOException {
String export_flag = getRequest().getParameter("export_flag");
String beginTime = getRequest().getParameter("beginTime");
String endTime = getRequest().getParameter("endTime");
String sql = "select a.HTBARQ,a.HTDJBH,a.KJYWRMC,a.KJYWRNSRSBH,a.KJYWLX,a.LXR,a.DH,"
+"a.FJMYWMC,a.FJMYWDZ,a.FJMQYNSRSBH,a.FJMQYMC,a.GJ,a.HTXMMC,a.HTH,"
+"a.HTZJK,a.HTQDRQ,a.HTZFCS,a.YZFHBMC,a.YZFJE,a.SDLXDL,a.SDLXXL,"
+"a.YNSEZZS,a.YNSESDS,a.YBSEZZS,a.YBSESDS,a.BZXX,a.KJ_ZZS,a.KJ_SDS,"
+"a.HTZXQSSJ,a.HTZXZZSJ,a.SKRKRQSDS,a.SKRKRQZZS,a.BZMC,"
+"a.KJZZSSL,a.KJSDSSL,b.HL,concat(a.YZFJE*b.HL,'') ZHRMB "
+"from FHMQYDWZFXX a LEFT JOIN BZXX b on a.YZFHBSZ_DM=b.BZSZDM ";
switch (export_flag) {
case "1":
sql += " where a.HTBARQ >= '" + beginTime.replace("-", "")
+ "' and a.HTBARQ <= '" + endTime.replace("-", "")
+ "'";
break;
default:
break;
}
sql += " ORDER BY a.HTBARQ asc";
// 第一步,创建一个webbook,对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet("Sheet1");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
XSSFRow row1 = sheet.createRow((int) 0);
XSSFRow row2 = sheet.createRow((int) 1);
// 第四步,创建单元格,并设置值表头 设置表头居中
/*XSSFCellStyle headerStyle = wb.createCellStyle();
XSSFFont headerFont = wb.createFont(); //标题字体
headerFont.setFontName("宋体");
headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 12);// 字体大小
headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
headerStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFont(headerFont);
sheet.getRow(0).setRowStyle(headerStyle);*/
//sheet.setDefaultColumnWidth(20);//设置列宽
//sheet.setDefaultRowHeight((short)(25*20));//设置行高
short width = 25,height=15*25;
sheet.setDefaultColumnWidth(width);
sheet.setDefaultRowHeight(height);
for(int i=0;i<=37;i++){
if(i<19||i==37){
sheet.addMergedRegion(new CellRangeAddress(0,1,i,i));
}
}
sheet.addMergedRegion(new CellRangeAddress(0,0,19,20));
sheet.addMergedRegion(new CellRangeAddress(0,0,21,24));
sheet.addMergedRegion(new CellRangeAddress(0,0,25,26));
sheet.addMergedRegion(new CellRangeAddress(0,0,27,28));
sheet.addMergedRegion(new CellRangeAddress(0,0,29,30));
sheet.addMergedRegion(new CellRangeAddress(0,0,31,32));
sheet.addMergedRegion(new CellRangeAddress(0,0,33,34));
sheet.addMergedRegion(new CellRangeAddress(0,0,35,36));
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
row1.setRowStyle(style);
row1.createCell(0).setCellValue("序号");
row1.createCell(1).setCellValue("合同备案日期");
row1.createCell(2).setCellValue("扣缴义务人名称");
row1.createCell(3).setCellValue("扣缴义务人纳税人识别号");
row1.createCell(4).setCellValue("扣缴义务类型");
row1.createCell(5).setCellValue("联系人");
row1.createCell(6).setCellValue("电话");
row1.createCell(7).setCellValue("非居民纳税人识别号");
row1.createCell(8).setCellValue("非居民企业名称");
row1.createCell(9).setCellValue("非居民英文企业名称");
row1.createCell(10).setCellValue("非居民英文地址");
row1.createCell(11).setCellValue("国家");
row1.createCell(12).setCellValue("合同项目名称");
row1.createCell(13).setCellValue("合同登记编号");
row1.createCell(14).setCellValue("合同号");
row1.createCell(15).setCellValue("合同签订日期");
row1.createCell(16).setCellValue("合同支付次数");
row1.createCell(17).setCellValue("合同执行起始时间");
row1.createCell(18).setCellValue("合同执行终止时间");
row1.createCell(19).setCellValue("合同总价款");
row1.createCell(20).setCellValue("");
row1.createCell(21).setCellValue("已支付金额(支付备案信息)");
row1.createCell(22).setCellValue("");
row1.createCell(23).setCellValue("");
row1.createCell(24).setCellValue("");
row1.createCell(25).setCellValue("所得类型");
row1.createCell(26).setCellValue("");
row1.createCell(27).setCellValue("应纳税额");
row1.createCell(28).setCellValue("");
row1.createCell(29).setCellValue("应补(退)税额");
row1.createCell(30).setCellValue("");
row1.createCell(31).setCellValue("税款入库日期");
row1.createCell(32).setCellValue("");
row1.createCell(33).setCellValue("扣缴税额");
row1.createCell(34).setCellValue("");
row1.createCell(35).setCellValue("扣缴税率");
row1.createCell(36).setCellValue("");
row1.createCell(37).setCellValue("备注");
row2.createCell(19).setCellValue("币种");
row2.createCell(20).setCellValue("外币金额");
row2.createCell(21).setCellValue("币种");
row2.createCell(22).setCellValue("外币金额");
row2.createCell(23).setCellValue("汇率");
row2.createCell(24).setCellValue("折合人民币");
row2.createCell(25).setCellValue("大类");
row2.createCell(26).setCellValue("明细");
row2.createCell(27).setCellValue("增值税");
row2.createCell(28).setCellValue("所得税");
row2.createCell(29).setCellValue("增值税");
row2.createCell(30).setCellValue("所得税");
row2.createCell(31).setCellValue("增值税");
row2.createCell(32).setCellValue("所得税");
row2.createCell(33).setCellValue("增值税");
row2.createCell(34).setCellValue("所得税");
row2.createCell(35).setCellValue("增值税");
row2.createCell(36).setCellValue("所得税");
List<HashMap> result_list = findDispatchActionServicel.findBySql(sql);
if(result_list!=null&&result_list.size()>0){
for (int j = 0; j < result_list.size(); j++) {
//XSSFRow r = sheet.createRow((int) (j + 2));
Row r = sheet.createRow(j + 2);
r.createCell(0).setCellValue(""+(j + 1));
String HTBARQ = (String) result_list.get(j).get("HTBARQ");
r.createCell(1).setCellValue(HTBARQ);
String KJYWRMC = (String) result_list.get(j).get("KJYWRMC");
r.createCell(2).setCellValue(KJYWRMC);
String KJYWRNSRSBH = (String) result_list.get(j).get("KJYWRNSRSBH");
r.createCell(3).setCellValue(KJYWRNSRSBH);
String KJYWLX = (String) result_list.get(j).get("KJYWLX");
r.createCell(4).setCellValue(KJYWLX);
String LXR = (String) result_list.get(j).get("LXR");
r.createCell(5).setCellValue(LXR);
String DH = (String) result_list.get(j).get("DH");
r.createCell(6).setCellValue(DH);
String FJMQYNSRSBH = (String) result_list.get(j).get("FJMQYNSRSBH");
r.createCell(7).setCellValue(FJMQYNSRSBH);
String FJMQYMC = (String) result_list.get(j).get("FJMQYMC");
r.createCell(8).setCellValue(FJMQYMC);
String FJMYWMC = (String) result_list.get(j).get("FJMYWMC");
r.createCell(9).setCellValue(FJMYWMC);
String FJMYWDZ = (String) result_list.get(j).get("FJMYWDZ");
r.createCell(10).setCellValue(FJMYWDZ);
String GB = (String) result_list.get(j).get("GJ");
r.createCell(11).setCellValue(GB);
String HTXMMC = (String) result_list.get(j).get("HTXMMC");
r.createCell(12).setCellValue(HTXMMC);
String HTDJBH = (String) result_list.get(j).get("HTDJBH");
r.createCell(13).setCellValue(HTDJBH);
String HTH = (String) result_list.get(j).get("HTH");
r.createCell(14).setCellValue(HTH);
String HTQDRQ = (String) result_list.get(j).get("HTQDRQ");
r.createCell(15).setCellValue(HTQDRQ);
String HTZFCS = (String) result_list.get(j).get("HTZFCS");
r.createCell(16).setCellValue(HTZFCS);
String HTZXQSSJ = (String) result_list.get(j).get("HTZXQSSJ");
r.createCell(17).setCellValue(HTZXQSSJ);
String HTZXZZSJ = (String) result_list.get(j).get("HTZXZZSJ");
r.createCell(18).setCellValue(HTZXZZSJ);
String BZMC = (String) result_list.get(j).get("BZMC");
r.createCell(19).setCellValue(BZMC);
String HTZJK = (String) result_list.get(j).get("HTZJK");
r.createCell(20).setCellValue(HTZJK);
String YZFHBMC = (String) result_list.get(j).get("YZFHBMC");
r.createCell(21).setCellValue(YZFHBMC);
String YZFJE = (String) result_list.get(j).get("YZFJE");
r.createCell(22).setCellValue(YZFJE);
String HL = (String) result_list.get(j).get("HL");
r.createCell(23).setCellValue(HL);
String ZHRMB = (String) result_list.get(j).get("ZHRMB");
r.createCell(24).setCellValue(ZHRMB);
String SDLXDL = (String) result_list.get(j).get("SDLXDL");
r.createCell(25).setCellValue(SDLXDL);
String SDLXXL = (String) result_list.get(j).get("SDLXXL");
r.createCell(26).setCellValue(SDLXXL);
String YNSEZZS = (String) result_list.get(j).get("YNSEZZS");
r.createCell(27).setCellValue(YNSEZZS);
String YNSESDS = (String) result_list.get(j).get("YNSESDS");
r.createCell(28).setCellValue(YNSESDS);
String YBSEZZS = (String) result_list.get(j).get("YBSEZZS");
r.createCell(29).setCellValue(YBSEZZS);
String YBSESDS = (String) result_list.get(j).get("YBSESDS");
r.createCell(30).setCellValue(YBSESDS);
String SKRKRQZZS = (String) result_list.get(j).get("SKRKRQZZS");
r.createCell(31).setCellValue(SKRKRQZZS);
String SKRKRQSDS = (String) result_list.get(j).get("SKRKRQSDS");
r.createCell(32).setCellValue(SKRKRQSDS);
String KJZZS = (String) result_list.get(j).get("KJ_ZZS");
r.createCell(33).setCellValue(KJZZS);
String KJSDS = (String) result_list.get(j).get("KJ_SDS");
r.createCell(34).setCellValue(KJSDS);
String KJZZSSL = (String) result_list.get(j).get("KJZZSSL");
r.createCell(35).setCellValue(KJZZSSL);
String KJSDSSL = (String) result_list.get(j).get("KJSDSSL");
r.createCell(36).setCellValue(KJSDSSL);
String BZ = (String) result_list.get(j).get("BZXX");
r.createCell(37).setCellValue(BZ);
}
}
String uploadPath = ServletActionContext.getServletContext()
.getRealPath("upload");
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddhh24mmss");// 设置日期格式
String date = df.format(new Date());// new Date()为获取当前系统时间
String excelName = "";
excelName = date + ".xlsx";
FileOutputStream fout = new FileOutputStream(uploadPath + "/"
+ excelName);
wb.write(fout);
fout.close();
Json json = new Json();
json.setSuccess(true);
json.setMsg(excelName);
writeJson(json);
}