java、jquery导出excel、word、pdf

Eden-li

于 2024-09-25 14:01:36 发布

阅读量205

点赞数 2

文章标签： java jquery excel

本文链接：https://blog.csdn.net/qq_38657010/article/details/140928951

版权

一、导出excel
1、java poi直接导出并下载（带下拉框）
引poi.jar

@RequestMapping("printWageUser")
	@ResponseBody
	public  void printWageUser(HttpServletRequest request,HttpServletResponse response, String depid) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		try {
			
			List<WageUser> list = null;//这里查询数据集合
			// 创建工作表
			Sheet sheet = workbook.createSheet("sheet");
			// 创建标题行
			Row headerRow = sheet.createRow(0);
			String[] titles = {"姓名",
					"身份证号",
					"最高学历-研究生/大学本科/大学专科/中专及一下",
					"备注"};
			//遍历标题行
			for (int i = 0; i < titles.length; i++) {
				String[] split = titles[i].split("-");
				// 创建第0列，其赋值为Cell类
				Cell headerCell1 = headerRow.createCell(i);
				// 通过该类赋值其含义，此为第0列的属性
				headerCell1.setCellValue(split[0]);
				if(split.length>1){
					//设置下拉框
					String[] options = split[1].split("/");
					DataValidationHelper validationHelper = sheet.getDataValidationHelper();
					DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(options);
					CellRangeAddressList addressList = new CellRangeAddressList(1, list.size(), i, i);//第0行，最后一行，第0列，最后一列
					DataValidation validation = validationHelper.createValidation(constraint, addressList);
					sheet.addValidationData(validation);
				}
			}
			//遍历数据
			for (int i = 0; i < list.size(); i++) {
				entity obj = list.get(i);
				//带入其他值
				Row dataRow1 = sheet.createRow(i+1);
				dataRow1.createCell(0).setCellValue(obj.getUsername());
				dataRow1.createCell(1).setCellValue(obj.getIdcard());
				dataRow1.createCell(1).setCellValue(obj.getIdcard());
				dataRow1.createCell(1).setCellValue(obj.getIdcard());
			}
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			workbook.write(baos);
			byte[] excelBytes = baos.toByteArray();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=wage_user_excel.xlsx");//导出文件名称
			response.setContentLength(excelBytes.length);
			ServletOutputStream outputStream = response.getOutputStream();
			outputStream.write(excelBytes, 0, excelBytes.length);
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

2、java poi模版导出
引poi.jar

@RequestMapping("printWageUser")
	@ResponseBody
	public  void printWageUser(HttpServletRequest request,HttpServletResponse response, String depid) {
		try {
			List<WageUser> list = null;//查询数据结果集
			String ioconfigFilePath = request.getSession().getServletContext().getRealPath("/")+"WEB-INF/dbioxml/wage_user_template.xls";//模版地址
			// 创建模板文件
			File templateFile = new File(ioconfigFilePath);
			// 读取模板文件
			Workbook workbook = new HSSFWorkbook(new FileInputStream(templateFile));
			Sheet sheet = workbook.getSheetAt(1);
			// 填充数据
			for (int i = 0; i < list.size(); i++) {
				Entity obj = list.get(i);
				//带入其他值
				Row dataRow1 = sheet.createRow(i+2);//空2行
				dataRow1.createCell(0).setCellValue(obj.getUsername());
				dataRow1.createCell(1).setCellValue(obj.getIdcard());
			}
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			workbook.write(baos);
			byte[] excelBytes = baos.toByteArray();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=wage_user_excel.xlsx");//文件名称
			response.setContentLength(excelBytes.length);
			ServletOutputStream outputStream = response.getOutputStream();
			outputStream.write(excelBytes, 0, excelBytes.length);
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

3、前端导出excel

//tableid-需要导出的表格ID；sheetName-文件名称
function downloadExcel(tableid, sheetName) {

        var uri = 'data:application/vnd.ms-excel;base64,';
        var template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"' +
            'xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="UTF-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>'
            + '<x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>'
            + '</x:ExcelWorkbook></xml><![endif]-->' +
            ' <style type="text/css">' +
            'table th{' +
            'background-color:#EFEFEF;' +
            'border: 0.1em solid #000000;' +
            'font-size: 15px;' +
            'height: 40px;' +
            '}' +
            'table td {' +
            'border: 0.1em solid #000000;' +
            //            'width: 250px;' +
            'height: 40px;' +
            ' text-align: center;' +
            'background-color:#FFFFFF;' +
            'color: #000000;' +
            'font-size: 14px;' +
            ' }' +
            '</style>' +
            '</head><body ><table class="excelTable">{table}</table></body></html>';

        $("input[name='inputData']").each(function(){
            var val = $(this).val()
            $(this).parent().html(val)
        })
        $("select[name='selectData']").each(function(){
            var val = $(this).val()
            $(this).parent().html(val)
        })
        if (!tableid.nodeType) tableid = document.getElementById(tableid);
        var ctx = {worksheet: sheetName || 'Worksheet', table: tableid.innerHTML};

        var href = uri + base64(format(template, ctx));

        //sheetName = $("title").html() +'-'+ $(".jsMainNav li[class^='active']").text() +'-'+ $(".userName").text() +'-'+ formatDateTime(new Date());
        if (sheetName == undefined || sheetName == '' || sheetName == null) {
            //文件名为空
            sheetName = $("title").html() + $(".jsMainNav li[class^='active']").text() + $(".userName").text() + formatDateTime(new Date());
        }

        // 模拟a点击，解决文件名
        var TAG_A_ID = "a2947547615766754";
        $("a#" + TAG_A_ID).remove();
        $("body").append(
            $("<a>").css("display", "none").attr("id", TAG_A_ID).attr("href", href).attr("download", sheetName + ".xls").append(
                $("<p>").text(" ")
            )
        );
        $("a#" + TAG_A_ID + ">p").trigger("click");
        //删除A标签
        $("#a2947547615766754").remove();
        //window.location.reload()
    }
    var base64 = function (s) {
        return window.btoa(unescape(encodeURIComponent(s)));
    };
    var format = function (s, c) {
        return s.replace(/{(\w+)}/g,
            function (m, p) {
                return c[p];
            });
    }

二、导出word
1、Java导出word（直接套用方式）

待定

2、Java导出word（freemarker方式）

待定

3、前端导出word
（1）、在页面html和css画出需要导出的word样式
（2）、引入js： jquery.wordexport.js

https://github.com/markswindoll/jQuery-Word-Export/blob/master/jquery.wordexport.js

和
FileSaver.js

https://github.com/eligrey/FileSaver.js/blob/master/dist/FileSaver.js

（3）、执行下载函数

$("#"+divId).wordExport("文件名称");

二、导出pdf
1、前端导出pdf
（1）、在页面html和css画出需要导出的样式
（2）、引入js：html2pdf.js

https://cdnjs.cloudflare.com/ajax/libs/html2pdf.js/0.10.1/html2pdf.bundle.min.js

	（3）、执行下载函数

downloadPdf("divid","文件名称","portrait","c4")
function downloadPdf(divId,name,orientation,page) {
    //可以是$("#id或类选择器").html()或val()
    var element = $("#"+divId).html()

    html2pdf().from(element).set({
        margin: 1,
        filename: name+'.pdf',
        html2canvas: {scale: 2},
        //landscape横向portrait纵向
        // jsPDF: {orientation: orientation?orientation:'landscape', unit: 'in', format: orientation?'a4':'a3', compressPDF: false}
        jsPDF: {orientation: orientation, unit: 'in', format: page, compressPDF: false}
    }).save();
}