Excel文件下载

赖皮蛇化真龙

于 2021-12-05 13:26:33 发布

阅读量90

点赞数

文章标签： html java

本文链接：https://blog.csdn.net/rrgef/article/details/121728234

版权

前端代码示例：

<!DOCTYPE html
    PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>管理</title>
    <#include "/common/global_css.ftl">
        <link href="${request.getContextPath()}/css/jbox/jbox_blue.css" rel="stylesheet" type="text/css" id="jbox" />
        <link href="${request.getContextPath()}/css/datatable/datatable.css" rel="stylesheet" type="text/css" />
        <link href="${request.getContextPath()}/css/chosen/chosen.css" rel="stylesheet" type="text/css" />
</head>


<body>
    <#include "/common/top.ftl">

        <div class="container">
            <#include "/common/left.ftl">
                <div id="main">
                    <div class="position"><a>基础数据管理</a> > 列表</div>
                    <#-- 企业员工员工学习统计 -->
                        <div class="header header_no_martop"
                            style="margin-bottom: 30px; height: 500px;background-color: rgb(252, 251, 251);">
                            <p style="font-size: 18px;margin-bottom: 15px;">企业员工学习统计</p>
                            <form class="search_c" id="staffStatistics">
                                <input type="hidden" id="staffId" value="${staffId}" />
                                        <a href="#" style="border-radius: 4px;
										width: 120px;
										height: 32px;
										line-height: 30px;
										text-align: center;
										border: none;
										color: #FFF;
										cursor: pointer; 
										background: #34B8DF;
										float: none;
    									display: inline-block;" href='javascript:;' style="background-size: 18px 18px;" onclick="exportExcel()">
                                            导出Excel表格</a>
                            </form>
                            <div style="width:700px;height:380px;margin-top:20px;" id="staffStatistic">
                            </div>
                        </div>
                                    <i class="search_btn" id="searchBtn"></i>
                                    <i class="reset_btn" id="searchResetBtn"></i>
                                </form>
        </div>
        <#include "/common/bottom.ftl">
            <script type="text/javascript" src="${request.getContextPath()}/js/datatable/jquery.dataTables.js"></script>
            <script type="text/javascript" src="${request.getContextPath()}/js/utils/jbox/jquery.jBox.src.js"></script>
            <script type="text/javascript"
                src="${request.getContextPath()}/js/utils/jbox/jquery.jBox-zh-CN.js"></script>
            <script type="text/javascript" src="${request.getContextPath()}/js/rbcList.js"></script>
            <script type="text/javascript" src="${request.getContextPath()}/js/utils/strUtils/dateUtil.js"></script>
            <script type="text/javascript"
                src="${request.getContextPath()}/js/utils/chosen/chosen.jquery.min.js"></script>
            <script type="text/javascript" src="${request.getContextPath()}/js/echarts.min.js"></script>
            <script type="text/javascript">
                //导出Excel表格
                function exportExcel() {

                    var submit = function (v, h, f) {
                        if (v == 'ok') {
                            var url = encodeURI("${request.getContextPath()}/studytime/LearnStatistic/staffInfoAll");
                            window.open(url);
                            $.jBox.tip('导出成功！');
                        } else if (v == 'cancel') {

                        }
                        return true;
                    };
                    jBox.confirm("确定导出<font color='red'><b>用户学习信息</b></font>的数据？", "提示", submit);
                }
            </script>
</body>

</html>

后端代码示例(根据前端的访问路径，映射到后端的Controller层)：

	/**初始化staff管理页面*/
	@RequestMapping(value = {"/staffInfoAll"})
	@ResponseBody
	public void staffStatisticInfo(Model model, HttpServletRequest request  ,HttpServletResponse response) {
		System.out.println("执行");
		Long partnerId =  (Long) request.getSession().getAttribute("partnerId");
		Partner partner = pertnerService.load(partnerId);
		
		List<Staff> staffList = staffService.selectAll(partnerId);
		List<CWUserstatisticalVO> userstatisticalVO =getStaffInfo(staffList);
		List<Long> listIds =new ArrayList<Long>();
		for (Staff staff : staffList) {
			listIds.add(staff.getId());
		}
		String ids ="(";
		ids += Joiner.on(",").join(listIds);
		ids+=")";

		List<CwUserStudyTime> learnStatistic = studyTimeService.staffStatisticInfo(ids);
		List<CWUserstatisticalVO> staffAll = getStaffAll(userstatisticalVO,learnStatistic,new CWUserstatisticalVOById());

		String createExcel = ExportExcel.createExcel(partner.getName(), staffAll,response);

	}

然后找到具体调用的静态方法路径：

package com.nuocai.modules.studytime.controller;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Random;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import com.nuocai.modules.studytime.model.CWUserstatisticalVO;

public class ExportExcel {

	public static String createExcel(String name,List<CWUserstatisticalVO> staffAll,HttpServletResponse response) {
		Date date = new Date();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		String filePath = name+"内部员工学习统计_"+sdf.format(date)+"_"+Math.abs(new Random().nextLong())+".xlsx";
		try {
//			// 获取桌面路径
//			// FileSystemView fsv = FileSystemView.getFileSystemView();
//			// String desktop = fsv.getHomeDirectory().getPath();
//
//
//			File file = new File(filePath);
//
//			if (!file.getParentFile().exists()) {
//				boolean result = file.getParentFile().mkdirs();
//				if (!result) {
//					System.out.println("创建失败");
//				}
//			}
//
//			OutputStream outputStream = new FileOutputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet(name+"内部员工学习统计");

			HSSFRow row0 = sheet.createRow(0);
			HSSFRow row1 = sheet.createRow(1);
			row0.createCell(0).setCellValue(name+"内部员工学习统计");

			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.setHeightInPoints(20); // 设置行的高度
			row0.setHeightInPoints(30); // 设置行的高度
			sheet.setColumnWidth(0, 100 * 50);
			sheet.setColumnWidth(1, 100 * 50);
			sheet.setColumnWidth(2, 100 * 50);
			sheet.setColumnWidth(3, 100 * 50);
			sheet.setColumnWidth(4, 100 * 50);
			sheet.setColumnWidth(5, 100 * 50);


			//合并的单元格样式
			HSSFCellStyle boderStyle = workbook.createCellStyle();
			//垂直居中
			boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			//设置一个边框
			boderStyle.setBorderTop(HSSFBorderFormatting.BORDER_THICK);
			// 合并单元格：参数：起始行, 终止行, 起始列, 终止列
			CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 5);
			sheet.addMergedRegion(cra);


			int index =2;
			for (int i = 0; i < staffAll.size(); i++) {

				if( staffAll.get(i).getStaffInfo().size() == 0) {
					HSSFRow row2 = sheet.createRow(index);
					row2.createCell(0).setCellValue(staffAll.get(i).getStaffName());
					row2.createCell(1).setCellValue("暂时没有观看课程");
					row2.createCell(2).setCellValue("暂时没有观看视频");
					row2.createCell(3).setCellValue(0);
					row2.createCell(4).setCellValue(0);
					row2.createCell(5).setCellValue("");
					index++;
				}else {
					for (int j = 0; j < staffAll.get(i).getStaffInfo().size(); j++) {
						HSSFRow row2 = sheet.createRow(index);
						row2.createCell(0).setCellValue(staffAll.get(i).getStaffName());
						row2.createCell(1).setCellValue(staffAll.get(i).getStaffInfo().get(j).getCourseName());
						row2.createCell(2).setCellValue(staffAll.get(i).getStaffInfo().get(j).getVideoName());
						row2.createCell(3).setCellValue(staffAll.get(i).getStaffInfo().get(j).getStudyTime());
						row2.createCell(4).setCellValue(staffAll.get(i).getStaffInfo().get(j).getEffectiveStudyTime());
						row2.createCell(5).setCellValue(sdf.format(staffAll.get(i).getStaffInfo().get(j).getLastTime()));
						index++;

					}
				}

			}



//		HSSFCellStyle cellStyle = workbook.createCellStyle();
//		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		HSSFCell cell0 = row0.createCell(0);
//		cell0.setCellStyle(cellStyle);

			HSSFFont font = workbook.createFont();
			font.setFontName("黑体");
			font.setFontHeightInPoints((short) 16);// 设置字体大小

			HSSFFont font2 = workbook.createFont();
			font2.setFontName("仿宋_GB2312");
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
			font2.setFontHeightInPoints((short) 12);


			OutputStream os  = response.getOutputStream();
			response.setContentType("APPLICATION/OCTET-STREAM");
			String title = new String(filePath.getBytes("gb2312"),"iso-8859-1");
			response.setHeader( "Content-Disposition" ,"attachment;filename=\"" + title  + "\"" );


			workbook.write(os);

			os.flush();

			return filePath;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}

赖皮蛇化真龙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Excel文件下载

前端代码示例：<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head> <meta http-equiv="Content-Type" content="te
复制链接

扫一扫