使用POI库生成Excel文件

1.项目结构

2.1 Client.java

package com.pojo;

public class Client {
	String id;
	String name;
	String sex;
	String address;
	
	public String getId() {
		return id;
	}
	public void setId(String 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 getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "Client [id=" + id + ", name=" + name + ", sex=" + sex + ", address=" + address + "]";
	}
}

2.2  ClientDao.java

package com.dao;

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

import com.pojo.Client;
import com.utils.DBUtils;

public class ClientDAO {

	public List<Client> getAllClient() {
		Connection conn =null;
		String sql = "Select * from client";
		List clients = new ArrayList();
		try {
			 conn = DBUtils.getConn();
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Client client = new Client();
				client.setId(rs.getString("id"));
				client.setName(rs.getString("name"));
				client.setSex(rs.getString("sex"));
				client.setAddress(rs.getString("address"));
				clients.add(client);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return clients;
	}
}

2.3  LoadExcelServlet.java

package com.controller;

import java.io.IOException;
import java.io.OutputStream;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.pojo.Client;
import com.service.ClientService;

/**
 * Servlet implementation class LoadExcelServlet
 */
@WebServlet("/LoadExcelServlet")
public class LoadExcelServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LoadExcelServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
			// 1 获得数据库clients表数据
			ClientService clientService=new ClientService();
			List<Client> allClient = clientService.getAllClient();
			// 2 写到excel
			HSSFWorkbook wb=new HSSFWorkbook();
			HSSFSheet createSheet = wb.createSheet();
			HSSFRow createRow = createSheet.createRow(0);
			HSSFCell cell0 = createRow.createCell(0);
			HSSFCell cell1 =createRow.createCell(1);
			HSSFCell cell2 =createRow.createCell(2);
			HSSFCell cell3 =createRow.createCell(3);
			cell0.setCellValue("ID");
			cell1.setCellValue("姓名");
			cell2.setCellValue("性别");
			cell3.setCellValue("地址");
			for(int i=0;i<allClient.size();i++) {
				HSSFRow createRow2 = createSheet.createRow(i+1);
				cell0=createRow2.createCell(0);
				cell1=createRow2.createCell(1);
				cell2=createRow2.createCell(2);
				cell3=createRow2.createCell(3);
				Client client = allClient.get(i);
				cell0.setCellValue(client.getId());
				cell1.setCellValue(client.getName());
				cell2.setCellValue(client.getSex());
				cell3.setCellValue(client.getAddress());
			}
			response.setContentType("application/vnd.ms-excel");
			response.addHeader("Content-Disposition", "attachment; filename=client1.xls");
			OutputStream os = response.getOutputStream();
			wb.write(os);
			os.flush();
			os.close();
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

2.4 ClientService.java

package com.service;

import java.util.List;

import com.dao.ClientDAO;
import com.pojo.Client;

public class ClientService {
	ClientDAO dao = new ClientDAO();
	public List<Client> getAllClient(){
		return dao.getAllClient();
	}
}

2.5 DButils.java

package com.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtils {

	private static final String url = "jdbc:mysql://localhost:3306/etc02?characterEncoding=utf-8";
	private static final String user = "root";
	private static final String password = "root";

	// 1 加载驱动
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 2 获取连接
	public static Connection getConn() throws SQLException {
		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}
}

2.6 loadExcel.jsp

<%@ 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>
	<a href="${pageContext.request.contextPath }/LoadExcelServlet">加载excel文件</a>
</body>
</html>