数据库导出为Excel

创建数据库和表：

create database test;
crate table test(
  id int primary key,
  name varchar(10),
  sex varchar(10),
  age int,
  address varchar(20)
);

插入测试数据：

insert into test values(1,'zhangsan1','男',20,'北京');
insert into test values(2,'zhangsan2','女',22,'北京');
insert into test values(3,'zhangsan3','女',20,'北京');
insert into test values(4,'zhangsan4','男',18,'北京');
insert into test values(5,'zhangsan5','男',20,'北京');

Servlet： JavaToExcel.java

import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

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

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class JavaToExcel extends HttpServlet {

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

		//数据库操作 
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		int rows = 1;
		try {
			 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123");
		     pst = con.prepareStatement("select * from test");
		     rs = pst.executeQuery();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		
		//标题 
		String fileName = "my first execl";
		// 获得 OutputStream 最后把生成的excel通过这个写出
	   	OutputStream os = response.getOutputStream();
	
		 response.setContentType("application/ms-excel;charset=gbk");
		 request.setCharacterEncoding("gbk");
		 response.setContentType("text/html;charset=gbk");
		 
		// 设置头
		response.setHeader("Content-Disposition", "attachment;filename="+ fileName + ".xls");

		
		// 标题字体  WritableFont(FontName fn,int ps,BoldStyle bs,Boolean italic) 字体类型  字体大小 字体样式  是否斜体
		WritableFont wfc = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, true);
		WritableCellFormat wcfFC = new WritableCellFormat(wfc);

		// 标题 居中对齐
		try {
			//左右居中
			wcfFC.setAlignment(Alignment.CENTRE);
			//上下居中 
			wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		} catch (WriteException e) {
			e.printStackTrace();
		}
		try {
			// 字段字体
			WritableFont wfc1 = new WritableFont(WritableFont.COURIER, 10, WritableFont.BOLD, true);
			WritableCellFormat wcfFC1 = new WritableCellFormat(wfc1);
			wcfFC1.setAlignment(Alignment.CENTRE); //左右居中 
			wcfFC1.setVerticalAlignment(VerticalAlignment.CENTRE);  //上下居中 

			// 通过OutputStream对象os创建Workbook
			WritableWorkbook wbook = Workbook.createWorkbook(os);
			// 创建sheet
			WritableSheet wsheet = wbook.createSheet("sheet", 0);

			// 加入标题
			wsheet.mergeCells(0, 0, 6, 0); //第一行合并7个单元格  
			wsheet.addCell(new Label(0, 0, "人员信息表", wcfFC));// 写标题 看出坐标是基于 0，0
			
			try{
				while(rs.next()){
						wsheet.addCell(new Label(0,rows,rs.getString(1),wcfFC1));  //列，行 
						wsheet.addCell(new Label(1,rows,rs.getString(2),wcfFC1));
						wsheet.addCell(new Label(2,rows,rs.getString(3),wcfFC1));
						wsheet.addCell(new Label(3,rows,rs.getString(4),wcfFC1));
						wsheet.addCell(new Label(4,rows,rs.getString(5),wcfFC1));
						rows++;
				}
			}catch(Exception e){
				
			}

			// 加入打印时间
			 Date aaa = new Date();
			 SimpleDateFormat aSimpleDateFormat = new
			 java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			 String today = aSimpleDateFormat.format(aaa);
			 wsheet.addCell(new Label(5, rows+1, "打印日期:"));
			 wsheet.addCell(new Label(6, rows+1, today));
		
			// 写入流中
			wbook.write();
			wbook.close();
			os.close();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		this.doGet(request, response);
	}

}

前台jsp页面 只需一个超链接<a>测试

<a href="javaToExcel">导出Excel</a>  <!-- javaToExcel 为我的servlet URL映射路径  在web.xml里面配置有 -->

注：所需jar包 jxl.jar mysql.jar驱动

如果报java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test 错误

可能原因有 mysql驱动必须放在 lib目录下。而不能右键项目 Build Path-->Add External...