Java实现Excel下载功能-- Web 项目

项目演示效果(项目jar包和源码在文章最后,需要的同学可以下载)

在浏览器中输入localhost/downloadExcel,我这里修改了tomcat80端口

点击查看所有用户,显示学生信息(项目jar包和源码在文章最后,需要的同学可以下载)

点击导出Excel,弹出保存框(项目jar包和源码在文章最后,需要的同学可以下载)

打开保存的Excel表(项目jar包和源码在文章最后,需要的同学可以下载)

具体代码:(项目jar包和源码在文章最后,需要的同学可以下载)

创建数据表student(项目jar包和源码在文章最后,需要的同学可以下载)

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


INSERT INTO `student` VALUES ('1', '张三', '男', '13');
INSERT INTO `student` VALUES ('2', '李四', '女', '14');
INSERT INTO `student` VALUES ('3', '王五', '男', '15');
INSERT INTO `student` VALUES ('4', '赵六', '女', '16');

 

1.创建一个web项目downloadExcel,项目目录如下:(项目jar包和源码在文章最后,需要的同学可以下载)

 

1.在util下创建DBconn.java文件,配置数据库连接信息(项目jar包和源码在文章最后,需要的同学可以下载)

package com.util;

import java.sql.*;

public class DBconn {
	static String url = "jdbc:mysql://localhost:3306/user?useunicuee=true& characterEncoding=utf8";
	static String username = "root";
	static String password = "root";
	static Connection conn = null;
	static ResultSet rs = null;
	static PreparedStatement ps = null;

	public static void init() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			System.out.println("init [SQL驱动程序初始化失败!]");
			e.printStackTrace();
		}
	}

	public static ResultSet selectSql(String sql) {
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
		} catch (SQLException e) {
			System.out.println("sql数据库查询异常");
			e.printStackTrace();
		}
		return rs;
	}

	public static void closeConn() {
		try {
			conn.close();
		} catch (SQLException e) {
			System.out.println("sql数据库关闭异常");
			e.printStackTrace();
		}
	}
}

2.在filter目录下创建EncodingFilter.java文件,配置过滤器(项目jar包和源码在文章最后,需要的同学可以下载)

package com.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class EncodingFilter implements Filter {

	public EncodingFilter() {
		System.out.println("过滤器构造");
	}

	public void destroy() {
		System.out.println("过滤器销毁");
	}

	public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
			throws IOException, ServletException {
		request.setCharacterEncoding("utf-8"); // 将编码改为utf-8
		response.setContentType("text/html;charset=utf-8");
		chain.doFilter(request, response);
	}

	public void init(FilterConfig arg0) throws ServletException {
		System.out.println("过滤器初始化");
	}

}

3.在entity目录下写入学生实体类Student.java(项目jar包和源码在文章最后,需要的同学可以下载)

package com.entity;

public class Student {

	private int id;
	private String name;
	private String sex;
	private String age;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getAge() {
		return age;
	}

	public void setAge(String age) {
		this.age = age;
	}

}

4.StudentServlet.java(项目jar包和源码在文章最后,需要的同学可以下载)

package com.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dao.StudentDao;
import com.dao.StudentDaoImpl;
import com.entity.Student;

@SuppressWarnings("serial")
public class StudentServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		StudentDao ud = new StudentDaoImpl();
		List<Student> userAll = ud.getUserAll();
		request.setAttribute("userAll", userAll);
		request.getRequestDispatcher("/student.jsp").forward(request, response);
	}
}

5.StudentDao.java(项目jar包和源码在文章最后,需要的同学可以下载)

package com.dao;

import java.util.List;

import com.entity.Student;

public interface StudentDao {

	List<Student> getUserAll();
}

6.StudentDaoImpl(项目jar包和源码在文章最后,需要的同学可以下载)

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.entity.Student;
import com.util.DBconn;

public class StudentDaoImpl implements StudentDao {

	@Override
	public List<Student> getUserAll() {
		List<Student> list = new ArrayList<Student>();
		try {
			DBconn.init();
			ResultSet resultSet = DBconn.selectSql("select * from student");
			while (resultSet.next()) {
				Student user = new Student();
				user.setId(resultSet.getInt("id"));
				user.setName(resultSet.getString("name"));
				user.setSex(resultSet.getString("sex"));
				user.setAge(resultSet.getString("age"));
				list.add(user);
			}
			DBconn.closeConn();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}

7.重点,excel下载ExcelServlet.java(项目jar包和源码在文章最后,需要的同学可以下载)

package com.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dao.StudentDao;
import com.dao.StudentDaoImpl;
import com.entity.Student;
import com.util.ExcelUtils;

@SuppressWarnings("serial")
public class ExcelServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		StudentDao userDao = new StudentDaoImpl();
		// 获取User数据
		List<Student> maps = userDao.getUserAll();
		try {
			EXCEL(maps, response);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void EXCEL(List<Student> maps, HttpServletResponse response) throws Exception {
		int selectCount = maps.size();
		String fileName = "学生信息表"; // 文件名
		String sheetName = "学生信息表1"; // 下标
		String titleName = "学生信息表2"; // 第一列标题
		String[] columnName = { "姓名", "性别", "年龄" }; // 标题行数据
		int columnNumber = columnName.length; // 行数
		int[] columnWidth = new int[columnNumber]; // 设置各列列宽
		for (int i = 0; i < columnNumber; i++) {
			columnWidth[i] = 20;
		}
		String[][] dataList = new String[selectCount][columnNumber]; // 查询的数据库数据
		for (int i = 0; i < maps.size(); i++) {
			String splits = maps.get(i).getName() + "," + maps.get(i).getSex() + "," + maps.get(i).getAge();
			String[] split = splits.split(",");
			dataList[i] = split;
		}
		String[] columnName2 = { "学生总数" };
		String summary[] = { maps.size() + "" };
		new ExcelUtils().export(sheetName, titleName, fileName, columnNumber, columnWidth, columnName, dataList,
				summary, columnName2, response);
	}
}

8.Excel下载的工具类ExcelUtils.java(项目jar包和源码在文章最后,需要的同学可以下载)

package com.util;

import java.io.ByteArrayOutputStream;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
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;

/**
 * 
 * @author win-10
 *
 */
public class ExcelUtils {

	/**
	 * 
	 * @param sheetName
	 *            下标
	 * @param titleName
	 *            第一列标题
	 * @param fileName
	 *            文件名
	 * @param columnNumber
	 *            行数
	 * @param columnWidth
	 *            格列列宽
	 * @param columnName
	 *            标题行数据
	 * @param dataList
	 *            数据库数据
	 * @param columnName2
	 * @return
	 * @throws Exception
	 */
	public void export(String sheetName, String titleName, String fileName, int columnNumber, int[] columnWidth,
			String[] columnName, String[][] dataList, String[] summary, String[] columnName2,
			HttpServletResponse response) throws Exception {
		if (columnNumber == columnWidth.length && columnWidth.length == columnName.length) {
			// 创建Excel工作薄
			@SuppressWarnings("resource")
			HSSFWorkbook book = new HSSFWorkbook();
			// 在Excel工作薄中建一张工作表
			HSSFSheet sheet = book.createSheet(sheetName);
			// 设置单元格格式(文本)
			// HSSFCellStyle cellStyle = book.createCellStyle();
			// 单独设置每列的宽
			for (int i = 0; i < columnNumber; i++) {
				for (int j = 0; j <= i; j++) {
					if (i == j) {
						sheet.setColumnWidth(i, columnWidth[j] * 256);
					}
				}
			}
			// // 创建第0行 也就是标题
			// HSSFRow row1 = sheet.createRow((int) 0);
			// row1.setHeightInPoints(25);// 设备标题的高度
			// // 第三步创建标题的单元格样式style2以及字体样式headerFont1
			// HSSFCellStyle style2 = book.createCellStyle();
			// HSSFFont headerFont1 = book.createFont(); // 创建字体样式
			// headerFont1.setFontName("黑体"); // 设置字体类型
			// headerFont1.setFontHeightInPoints((short) 20); // 设置字体大小
			// style2.setFont(headerFont1); // 为标题样式设置字体样式
			// HSSFCell cell1 = row1.createCell(0);// 创建标题第一列
			// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNumber
			// - 1)); // 合并列标题
			// cell1.setCellValue(titleName); // 设置值标题
			// cell1.setCellStyle(style2); // 设置标题样式

			// 创建第1行 也就是表头
			HSSFRow row = sheet.createRow((int) 0);
			row.setHeightInPoints(15);// 设置表头高度
			// 第四步,创建表头单元格样式 以及表头的字体样式
			HSSFCellStyle style = book.createCellStyle();
			style.setWrapText(true);// 设置自动换行
			HSSFFont headerFont = book.createFont(); // 创建字体样式
			headerFont.setFontName("黑体"); // 设置字体类型
			headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
			style.setFont(headerFont); // 为标题样式设置字体样式
			// 第四.一步,创建表头的列
			for (int i = 0; i < columnNumber; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellValue(columnName[i]);
				cell.setCellStyle(style);
			}
			// 循环导出数据到excel中
			for (int i = 0; i < dataList.length; i++) {
				row = sheet.createRow((int) i + 1);
				HSSFCell datacell = null;
				for (int j = 0; j < columnNumber; j++) {
					datacell = row.createCell(j);
					datacell.setCellValue(dataList[i][j]);
				}
			}
			// 第5步,汇总列
			HSSFRow row2 = sheet.createRow(dataList.length + 1);
			row2.setHeightInPoints(15);// 设置表头高度
			for (int i = 0; i < columnName2.length; i++) {
				row2.createCell(i).setCellValue(columnName2[i]);
			}
			// 第6步,汇总数据
			HSSFRow row3 = sheet.createRow(dataList.length + 2);
			row3.setHeightInPoints(15);// 设置表头高度
			for (int i = 0; i < summary.length; i++) {
				row3.createCell(i).setCellValue(summary[i]);
			}
			try {
				response.setContentType("application/octet-stream;charset=utf-8");
				response.setHeader("Content-Disposition",
						"attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1") + ".xls");
				book.write(response.getOutputStream());
				// 如果转化为字节流:
				ByteArrayOutputStream os = new ByteArrayOutputStream();
				book.write(os);
				byte[] bytes = os.toByteArray();
				// 获取响应报文输出流对象
				ServletOutputStream out = response.getOutputStream();
				// 输出
				out.write(bytes);
				out.flush();
				out.close();
			} catch (Exception e) {
				System.out.println("导出失败");
			}
		} else {
			System.out.println("列数目,长度,名称三个数组长度要一致");
		}
	}

}

 

 

9.配置web.xml(项目jar包和源码在文章最后,需要的同学可以下载)

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
	id="WebApp_ID" version="3.1">
	<display-name>Demo</display-name>
	<filter><!--过滤器配置 -->
		<filter-name>EncodingFilter</filter-name>
		<filter-class>com.filter.EncodingFilter</filter-class>
	</filter>
	<filter-mapping>
		<filter-name>EncodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

	<servlet><!--servlet类路径配置 -->
		<servlet-name>ExcelServlet</servlet-name>
		<servlet-class>com.servlet.ExcelServlet</servlet-class>
	</servlet>

	<servlet-mapping><!--servlet类映射配置 -->
		<servlet-name>ExcelServlet</servlet-name>
		<url-pattern>/ExcelServlet</url-pattern>
	</servlet-mapping>
	<servlet><!--servlet类路径配置 -->
		<servlet-name>StudentServlet</servlet-name>
		<servlet-class>com.servlet.StudentServlet</servlet-class>
	</servlet>

	<servlet-mapping><!--servlet类映射配置 -->
		<servlet-name>StudentServlet</servlet-name>
		<url-pattern>/StudentServlet</url-pattern>
	</servlet-mapping>

	<welcome-file-list><!--默认首页地址 -->
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>
</web-app>

10.index.jsp页面(项目jar包和源码在文章最后,需要的同学可以下载)

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>查看所有用户页面</title>
</head>

<body>
	<a href="StudentServlet">查看所有用户</a>
</body>
</html>

11.student.jsp页面(项目jar包和源码在文章最后,需要的同学可以下载)

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>所有用户页面</title>
</head>

<body>
	<div>
		<a href="ExcelServlet">导出Excel表格</a>
	</div>
	<table width="600" border="1" cellpadding="0">
		<tr>
			<th>ID</th>
			<th>姓名</th>
			<th>性别</th>
			<th>年龄</th>
		</tr>
		<c:forEach var="U" items="${userAll}">
			<tr>
				<td><input type="text" value="${U.id}" name="id"></td>
				<td><input type="text" value="${U.name}" name="name"></td>
				<td><input type="text" value="${U.sex}" name="sex"></td>
				<td><input type="text" value="${U.age}" name="age"></td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

项目源码:下载链接:https://pan.baidu.com/s/1997vi0p3dMLyEziSxEfb4w 密码:15ux

项目jar包:下载链接:https://pan.baidu.com/s/1ALli5bUws9Pz_JmjO6iwzg  密码:uw33

 

  • 3
    点赞
  • 2
    评论
  • 9
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页

打赏作者

Java小白的成长

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值