excel（一）写数据到excel

w_t_y_y

已于 2024-09-26 15:04:42 修改

阅读量738

点赞数

分类专栏：常用技术文章标签： java postman javascript

于 2017-12-25 15:05:19 首次发布

本文链接：https://blog.csdn.net/w_t_y_y/article/details/78892973

版权

常用技术专栏收录该内容

26 篇文章 0 订阅

订阅专栏

一、demo：

1、pom.xml添加依赖：

	<!-- excel start -->
		<dependency>
        <groupId>org.apache.poi</groupId>
         <artifactId>poi</artifactId>
         <version>3.10-FINAL</version>
     </dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.10-FINAL</version>
		</dependency>
		<!-- excel end -->

并添加jxl的jar包:

<!-- jxl -->
<dependency>
	 <groupId>net.sourceforge.jexcelapi</groupId>
	 <artifactId>jxl</artifactId>
	 <version>2.6.12</version>
</dependency>

2、前、后台：

（1）同步提交：form表单直接提交

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>统计表</title>
<script type="text/javascript" src="../assets/jquery.min.js"></script>	
</head>

<script>

function fun() {
    var excelForm = document.getElementById("excelForm");
    excelForm.submit();            

}

</script>
</head>
<body>
<button onclick="fun()">导出</button>
<form id="excelForm"  action="tj" method="post" target="excelIFrame">
        <input type="hidden" name="columns" id="excelData" />
    </form>
    <iframe id="excelIFrame" name="excelIFrame" style="display:none;"></iframe>
</body>
</html>

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


//稿费统计表
	@PostMapping("/tj")
	@ResponseBody
	public Message gftj(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		System.out.println("导出");
		request.setCharacterEncoding("UTF-8");
		OutputStream out = response.getOutputStream();
		String fname = "稿费统计表";
		response.reset();// 清空输出流

		response.setCharacterEncoding("UTF-8");// 设置相应内容的编码格式
		fname = java.net.URLEncoder.encode(fname, "UTF-8");
		response.setHeader("Content-Disposition",
				"attachment;filename=" + new String(fname.getBytes("UTF-8"), "GBK") + ".xls");
		response.setContentType("application/ms-excel");// 定义输出类型
		
		// 获得表头的数据
		 String rows[] = new String []{"标题","单位","作者","栏目","期数","稿酬","签名"};
		try {
			
			
			//获取数据
			Map<String, Object> map = new HashMap<>();
			List<MsgFxb> list = service.Gfcymx(map); // 获得需要的数据
		
			int c=rows.length;//列数
			int r=list.size();//行数
			String data[][]=new String[r][c];//
			for(int i=0;i<r;i++){
				for(int j=0;j<c;j++){
					if(j==0){
						data[i][j]=list.get(i).getXxbt();
					}
					if(j==1){
						data[i][j]=list.get(i).getSbdwmc();
					}
					if(j==2){
						data[i][j]=list.get(i).getZz();
					}
					if(j==3){
						data[i][j]=list.get(i).getLmmc();
					}
					if(j==4){
						data[i][j]=list.get(i).getQs();
					}
					if(j==5){
						data[i][j]=list.get(i).getGc().stripTrailingZeros().toPlainString();
					}
				}
			}			
			// 创建Excel工作薄
			WritableWorkbook workbook = Workbook.createWorkbook(out); // 创建excel文件
			// 添加第一个工作表并设置第一个Sheet的名字
			WritableSheet sheet = workbook.createSheet("grid1", 0); // 创建sheet单元
			Label label;
			for (int i = 0; i < rows.length; i++) {      // 写入表头到sheet中
				
				//Iterator iterator = hm.keySet().iterator();
				label = new Label(i, 0, rows[i]);
				sheet.addCell(label);
			}			
			for (int i =1; i <=data.length; i++) {   // 写入数据到sheet中				
				for (int j = 0; j < rows.length-1; j++) {//最后一列为签名，手写
					System.out.print(data[i-1][j]);
					label = new Label(j, i, data[i-1][j]);//列,行,值					
					sheet.addCell(label);
				}
				System.out.println();
			}
			workbook.write();    //sheet写入到excel中
			// 关闭文件
			workbook.close();
			out.close();
			message.setCode(200);
			message.setMsg("导出成功");
			System.out.println("导出成功");
		} catch (Exception e) {
			message.setCode(300);
			message.setMsg("服务器繁忙");
			e.printStackTrace();
			System.out.println("导出失败");
		}
		return message;
}

（2）异步提交：

 <div>
    <a target="_blank" href="javascript:;" (click)="export()"  class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-icon-left">
                    <span class="ui-button-text ui-clickable">导出</span>
                </a>
 </div>



 export(){   
   location.href="/demo/teacher/admin/export?courseId="+this.courseId+"&&sort="+this.sort+
    "&&desc="+this.desc+"&&tjxb="+this.tjxb+"&&tkxb="+this.tkxb;       
}

@RequestMapping("/export")
	@ResponseBody
	public String getCourseUserInfo(TeacherAdminQueryParam param, Boolean desc,
			Integer sort,HttpServletRequest request,HttpServletResponse response) throws IOException {
		
		if(param ==  null){
			return JSON.toJSONString(ResultJSONUtil.getFailedInstance("参数缺失"));
		}
		if(StringUtils.isEmpty(param.getCourseId())){
			return JSON.toJSONString(ResultJSONUtil.getFailedInstance("参数缺失"));
		}
		request.setCharacterEncoding("UTF-8");
		OutputStream out = response.getOutputStream();
		String fname = "课程详情统计表";
		
		// 清空输出流
		response.reset();
		response.setCharacterEncoding("UTF-8");
		fname = java.net.URLEncoder.encode(fname, "UTF-8");
		response.setHeader("Content-Disposition",
				"attachment;filename=" + new String(fname.getBytes("UTF-8"), "GBK") + ".xls");
		response.setContentType("application/ms-excel");// 定义输出类型
		
		// 定义表头的数据
		 String rows[] = new String []{"序号","用户id","用户姓名","角色","出勤率","作业完成率","同级续报","同科续报","电话号码"};
		try {		
			
			//获取数据
			Map<String, Object> map = new  HashMap<String, Object>();
			List<TeacherAdminCourseDetail> details = teacherAdminCourseDetailService
					.getTeacherAdminCourseDetailsList(param);
			//列数
			int c=rows.length;
			//行数
			int r=details.size();			
			// 创建Excel工作薄
			WritableWorkbook workbook = Workbook.createWorkbook(out); 
			// 添加第一个工作表并设置第一个Sheet的名字
			WritableSheet sheet = workbook.createSheet("grid1", 0); 
			Label label;
			 // 写入表头到sheet中
			for (int i = 0; i < rows.length; i++) {     				
				label = new Label(i, 0, rows[i]);				
				sheet.addCell(label);
			}			
			// 写入数据到sheet中
			for (int i =1; i <=details.size(); i++) {   
				TeacherAdminCourseDetail detail = details.get(i-1);				
				for (int j = 0; j < rows.length; j++) {
					String data="";
					if( j == 0){
						//序号
						data = String.valueOf(i);
					}
					if(j == 1){
						//用户id
						data = detail.getUserId();
					}
					if(j == 2){
						//用户姓名
						data = detail.getUserName();
					}
					//.......
					//列,行,值
					label = new Label(j, i,data);					
					sheet.addCell(label);
				}
				//System.out.println();
			} 
			workbook.write();   
			// 关闭文件
			workbook.close();			
			out.close();
			System.out.println("导出成功");
			return JSON.toJSONString(ResultJSONUtil.getSuccessInstance());
		} catch (Exception e) {
			 e.printStackTrace();
			return JSON.toJSONString(ResultJSONUtil.getFailedInstance("请稍后再试"));
		}
		
}