excel导出servlet

最新推荐文章于 2023-06-05 10:23:29 发布

猿原缘

最新推荐文章于 2023-06-05 10:23:29 发布

阅读量347

点赞数

本文链接：https://blog.csdn.net/csdn_12345678910/article/details/80727595

版权

首先导入jar包

c3p0-0.9.2.1.jar

commons-fileupload-1.2.2.jar

mchange-commons-java-0.2.3.4.jar

mysql-connector-java-5.0.5

lombok-1.16.20.jar

poi-3.17.jar

ueditor-mini.jar

web.xml文件：

<?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>excel_export_servlet</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
</web-app>

c3p0-config.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>


<named-config name="mysql">

<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="jdbcUrl">jdbc:mysql://192.168.26.3:3306/two?useUnicode=true&characterEncoding=utf8
</property>

<property name="user">root</property>

<property name="password">root</property>

<property name="initialPoolSize">3</property>

<property name="maxIdleTime">60</property>

<property name="maxPoolSize">100</property>

<property name="minPoolSize">5</property>

<property name="maxStatements">200</property>
</named-config>
</c3p0-config>

实体类：

package com.xy.excel.bean;

import lombok.Data;

@Data
public class User {

private Integer uid;
private String uname;
private String uaddress;
public User(Integer uid, String uname, String uaddress) {
super();
this.uid = uid;
this.uname = uname;
this.uaddress = uaddress;
}
public User(Integer uid) {
super();
this.uid = uid;
}

}

持久层dao：

接口：

package com.xy.excel.dao;

import java.util.List;

import com.xy.excel.bean.User;

public interface UserDao {

List<User>query();
}

实现类：

package com.xy.excel.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.xy.excel.bean.User;
import com.xy.excel.dao.UserDao;
import com.xy.excel.utils.DBUtils;

public class UserDaoImpl implements UserDao{

@Override
public List<User> query() {
List<User> list = new ArrayList<>();
Connection conn= DBUtils.getConnection();
String sql="select * from users ";
PreparedStatement pstmt=DBUtils.getPreparedStatement(conn, sql,null);
ResultSet rs=DBUtils.getResultSet(pstmt);
try {
if(rs.next()){
int uid = rs.getInt("uid");
String uname = rs.getString("uname");
String uaddress = rs.getString("uaddress");
User u = new User(uid, uname, uaddress);
list.add(u);
}
} catch (Exception e) {
e.printStackTrace();
}
DBUtils.close(rs);
DBUtils.close(pstmt);
DBUtils.close(conn);
return list;
}

}

控制层servlet：

package com.xy.excel.sevlet;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
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;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import com.xy.excel.bean.User;
import com.xy.excel.dao.UserDao;
import com.xy.excel.dao.impl.UserDaoImpl;

/**
* 访问路径：http://localhost:8080/excel_export_servlet/index.jsp
* Servlet implementation class UserServlet
*/
@WebServlet("/UserServlet")
public class UserServlet extends HttpServlet {
private UserDao userDao = new UserDaoImpl();
private static final long serialVersionUID = 1L;

/**
* @see HttpServlet#HttpServlet()
*/
public UserServlet() {
super();
// TODO Auto-generated constructor stub
}

/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}

/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//准备要导出来的数据
List<User> user = userDao.query();

try {
exportUserExcel(response,user);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 提取头部公共的样式
*/
private HSSFCellStyle createTitleStyle(HSSFWorkbook workbook, int fontSize){
HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中

//创建字体样式
HSSFFont font = workbook.createFont();

font.setFontHeightInPoints((short)fontSize);

font.setBold(true);//设置字体样式如加粗，斜体等

font.setFontName("华文行楷");

style.setFont(font);

return style;
}
/**
* 提取小标题公共的样式
* @param workbook
* @param fontSize
* @return
*/
private HSSFCellStyle createSecondTitleStyle(HSSFWorkbook workbook, int fontSize) {
HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中

// 创建一个字体样式

HSSFFont font = workbook.createFont();

font.setFontHeightInPoints((short)fontSize);

font.setBold(true);

style.setFont(font);

return style;
}
/**

* 提取表格头部的样式

*

* @param workbook

* @param fontSize

* @return

*/

private HSSFCellStyle createDataHeaderStyle(HSSFWorkbook workbook, int fontSize) {

HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中

// 创建一个字体样式

HSSFFont font = workbook.createFont();

font.setFontHeightInPoints((short)fontSize);

font.setBold(true);

style.setFont(font);

return style;

}

/**

* 提取表格数据的样式

*

* @param workbook

* @param fontSize

* @return

*/

private HSSFCellStyle createDataStyle(HSSFWorkbook workbook, int fontSize) {

HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中

// 创建一个字体样式

HSSFFont font = workbook.createFont();

font.setFontHeightInPoints((short)fontSize);

style.setFont(font);

return style;

}

/**
* 创建导出的方法
* @param response
* @param users
* @throws Exception
*/
public void exportUserExcel(HttpServletResponse response,List<User> users) throws Exception{
//声明文件名

String fileName="用户数据.xls";

//处理文件名

try {

fileName=URLEncoder.encode(fileName, "utf-8");

} catch (Exception e) {

e.printStackTrace();

}

//处理文件类型

response.setContentType("application/x-download");

// 设置下载的文件名

response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

//创建一个工作表

HSSFWorkbook workbook = new HSSFWorkbook();//是一个空的，没有sheet的excel

//创建sheet并取名

HSSFSheet sheet = workbook.createSheet("用户数据");

//设置整个Excel表格的列的默认宽度

sheet.setDefaultColumnWidth((short) 30);

//开始写数据

int row=0;

//写第一行数据

HSSFRow titleRow = sheet.createRow(row);

//在第一行里面创建列

HSSFCell titleCell = titleRow.createCell(0);

//创建合并的对象

/**

* 参数1:开始行参数2：结束行参数3 开始列参数4 ：结束列的下标

*/

CellRangeAddress region=new CellRangeAddress(row, row, 0, 2);

//合并

sheet.addMergedRegion(region);

//设置合并后的列的的数据

titleCell.setCellValue("用户数据");

//从公共方法里面提取样式

HSSFCellStyle titleStyle = this.createTitleStyle(workbook, 25);

//把样式应该到Cell里面

titleCell.setCellStyle(titleStyle);





//第二行

row++;
HSSFRow secondTitleRow = sheet.createRow(row);

//在第二行里面创建列

HSSFCell secondCell = secondTitleRow.createCell(0);

CellRangeAddress region2=new CellRangeAddress(row, row, 0, 2);

//合并

sheet.addMergedRegion(region2);

//设置合并后的列的的数据

secondCell.setCellValue("总数:"+users.size()+"条，导出时间:"+new Date().toLocaleString());

//从公共方法里面提取样式

HSSFCellStyle secondTitleStyle = this.createSecondTitleStyle(workbook, 14);

//把样式应该到Cell里面

secondCell.setCellStyle(secondTitleStyle);





String[] headers={"用户ID","用户姓名","用户地址"};

//第三行是头部数据

row++;

HSSFRow rowHeader = sheet.createRow(row);

HSSFCellStyle dataHeaderStyle = this.createDataHeaderStyle(workbook, 12);

for (int i = 0; i < headers.length; i++) {

HSSFCell cell = rowHeader.createCell(i);

cell.setCellValue(headers[i]);

cell.setCellStyle(dataHeaderStyle);

}

//第四行到最后都是用户数据

HSSFCellStyle dataStyle = this.createDataStyle(workbook, 12);

for (int i = 0; i < users.size(); i++) {

row++;

User user = users.get(i);

HSSFRow rowData = sheet.createRow(row);

String [] data={user.getUid()+"",user.getUname(),user.getUaddress()};

for (int j = 0; j < data.length; j++) {

HSSFCell cell = rowData.createCell(j);

cell.setCellValue(data[j]);

cell.setCellStyle(dataStyle);

}

}

//把它写出去

workbook.write(response.getOutputStream());


}

}

页面，只是测试，很简单的一个a标签

<%@ 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>Insert title here</title>
</head>
<body>
<h1>
<a href="UserServlet">导出所有数据</a>
</h1>
</body>
</html>

启动tomcat后访问：访问路径：http://localhost:8080/excel_export_servlet/index.jsp

猿原缘

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
excel导出servlet

首先导入jar包c3p0-0.9.2.1.jarcommons-fileupload-1.2.2.jarmchange-commons-java-0.2.3.4.jarmysql-connector-java-5.0.5lombok-1.16.20.jarpoi-3.17.jarueditor-mini.jarweb.xml文件：&lt;?xml version="1.0" encoding="U...
复制链接

扫一扫