Java实现导出excel

卑微远

已于 2022-09-02 18:00:30 修改

阅读量2.4k

点赞数

分类专栏：后端文章标签： Java实现导出excel

于 2022-01-25 15:42:06 首次发布

本文链接：https://blog.csdn.net/Marshall_Ma/article/details/122686286

版权

后端专栏收录该内容

3 篇文章 0 订阅

订阅专栏

这里介绍两种常用方式，一种是通过Workbook导出excel，另一种则是直接通过JSP导出。
方法一：通过Workbook导出excel
Workbook 对象是 Workbooks 集合的成员。 Workbooks 集合包含 Microsoft Excel 中当前打开的所有 Workbook 对象。

ByteArrayOutputStream baos = (ByteArrayOutputStream) doCreateBook(list, map);
ServletOutputStream out = response.getOutputStream();
String fileName = MiscUtils.timestampToString(new Date(), "yyyy-MM-d") + "记录单" + ".xls";//excel名称
response.setHeader("Cache-Control", "max-age=0");
response.setHeader("Content-Disposition","attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setContentLength(baos.size());
out.write(baos.toByteArray());
out.flush();
out.close();

封装doCreateBook方法

public OutputStream doCreateBook(Collection result, Map map) throws IOException {
	OutputStream os = new ByteArrayOutputStream();
	//输出流
	jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);
	//创建一个工作表对象
	jxl.write.WritableSheet ws = wwb.createSheet("交易历史", 0);

	try {
		ws.setColumnView(0, 10); // 设置列的宽度
		ws.setColumnView(1, 20);
		ws.setColumnView(2, 20);
		//label里面的样式
		jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TAHOMA, 20, WritableFont.BOLD, true);
		jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
		wcfF.setAlignment(Alignment.CENTRE);
    
		ws.mergeCells(0, 0, 10, 0);//标题title合并前10列
		String title = "交易明细";
		jxl.write.Label labelTitle = new jxl.write.Label(0, 0, map.get("whDesc") + title, wcfF);
		ws.addCell(labelTitle);//设置表头

		jxl.write.Label labelColes = null;
		jxl.write.WritableFont wfs = new jxl.write.WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false);
		jxl.write.WritableCellFormat wfColes = new jxl.write.WritableCellFormat(wfs);
		wfColes.setAlignment(Alignment.CENTRE);

		labelColes = new jxl.write.Label(1, 3, "库房:" + map.get("whDesc"), wfColes);
		ws.addCell(labelColes);

		jxl.write.Label labelCols = null;
		jxl.write.WritableCellFormat wfCols = new jxl.write.WritableCellFormat();
		wfCols.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		//设置列名
		labelCols = new jxl.write.Label(0, 5, "序号", wfCols);
		ws.addCell(labelCols);
		labelCols = new jxl.write.Label(1, 5, "物料", wfCols);
		ws.addCell(labelCols);
		labelCols = new jxl.write.Label(2, 5, "物料描述", wfCols);
		ws.addCell(labelCols);

		Iterator it = result.iterator();
		jxl.write.Number numberValue = null;
		jxl.write.Label stringValue = null;
		int i = 6;//从第i+1行开始输出
		int count = 0;
		double currentQtyDouble = 0;
		System.out.println("size===" + result.size());
		while (it.hasNext()) {
			Map material = (Map) it.next();
			count++;
			numberValue = new jxl.write.Number(0, i, count, wfCols);
			ws.addCell(numberValue);
			stringValue = new jxl.write.Label(1, i, (String) material.get("instanceId"), wfCols);
			ws.addCell(stringValue);
			stringValue = new jxl.write.Label(2, i, (String) material.get("instanceDesc"), wfCols);
			ws.addCell(stringValue);
			i++;
		}
		//写入信息
		wwb.write();
		wwb.close();

	} catch (Exception d) {
		log.error("doCreateBook", d);
	}
	return os;
}

方法二：在JSP输出需要导出的信息
在page指令中设置contentType值，指明需要导出文件格式；
contentType=“application/msexcel” 输出Excel;
contentType=“application/msword” 输出Word。

<%@ page language="java" pageEncoding="utf-8"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ page contentType="application/msexcel" %>  
<%-- 
Word只需要把contentType="application/msexcel"改为contentType="application/msword" 
--%>
<%   
  //独立打开excel软件   
  response.setHeader("Content-disposition","attachment; filename=MyExcel.xls");   
  
//嵌套在ie里打开excel   
  
//response.setHeader("Content-disposition","inline; filename=MyExcel.xls");   
  
//response.setHeader("Content-disposition","inline; filename=wwww.doc");   
%>  
<html>  
<head>  
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="UTF-8">
<title>测试导出Excel和Word</title>  
</head>  
<body>  
<table width="600" border="1" align="center">  
	<tr>  
	  <td colspan="6" align="center"><h2>用户列表</h2></td>  
	</tr>
	<tr>
	    <td>ID</td>
	    <td>用户组</td>
	    <td>用户名</td>
	    <td>昵称</td>
	    <td>用户状态</td>
	    <td>创建时间</td>
	</tr> 
	<c:forEach items="${downlodeUserInfo}" var="user">
	  	<tr>
	   		<td class="userid">${user.userId }</td>
	   		<td class="groupid">${user.groupId }</td>
	   		<td class="usercode">${user.userCode }</td>
	   		<td class="nickname">${user.nickName }</td>
	   		<td class="userstate">
	   			${user.userState==1?"启用":"禁用"}
	   		</td>
	   		<td class="createtime">${fn:substring(user.createTime,0,16)}</td>
		</tr>
	</c:forEach> 
</table>  
</body>  
</html>