数据库导出为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…