从数据库导出EXCEL。同样添加poi.jar包。
jsp如下:
servlet如下:
阅读(62) | 评论(0) | 转发(0) |
<script type=text/javascript charset=utf-8 src="http://static.bshare.cn/b/buttonLite.js#style=-1&uuid=&pophcol=3&lang=zh"></script> <script type=text/javascript charset=utf-8 src="http://static.bshare.cn/b/bshareC0.js"></script>
jsp如下:
点击(此处)折叠或打开
- <a href="ReportServlet" onclick="return confirm('确认数据导出到E:/下?');">导出数据到Excel</a>
点击(此处)折叠或打开
- package control;
-
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.OutputStream;
- import java.io.PrintWriter;
- import java.sql.Connection;
- import java.sql.ResultSet;
- import java.sql.SQLException;
-
- import javax.servlet.ServletException;
- 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.HSSFRichTextString;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-
- import db.DB;
-
- public class ReportServlet extends HttpServlet {
-
-
- /**
- *
- */
- private static final long serialVersionUID = 1L;
- public void doGet(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- this.doPost(request, response);
- }
- public void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- request.setCharacterEncoding("utf-8");
- //获得要生成Excel数据的数据库中的表名称
- String tableName = request.getParameter("tableName");
-
- // 禁止数据缓存。
- response.setHeader("Pragma", "no-cache");
- response.setHeader("Cache-Control", "no-cache");
- response.setDateHeader("Expires", 0);
- Connection conn = null ;
- DB db = new DB() ;
- conn = db.getDB() ;
- String sql = "select * from test_table" ;
-
- ResultSet rs = null;
- try {
- rs = conn.createStatement().executeQuery(sql);
- } catch (SQLException e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- // 获取总列数
- int CountColumnNum=0;
- try {
- CountColumnNum = rs.getMetaData().getColumnCount();
- } catch (SQLException e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- int i = 1 ;
- // 创建Excel文档
- HSSFWorkbook wb = new HSSFWorkbook() ;
- // sheet 对应一个工作页
- HSSFSheet sheet = wb.createSheet("test_table表中的数据") ;
- HSSFRow firstrow = sheet.createRow(0); //下标为0的行开始
- HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
- String[] names = new String[CountColumnNum];
- names[0] = "ID";
- names[1] = "学号";
- names[2] = "姓名";
-
- for(int j= 0 ;j<CountColumnNum; j++){
- firstcell[j] = firstrow.createCell(j);
- firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
- }
- try {
- while(rs.next())
- {
- // 创建电子表格的一行
- HSSFRow row = sheet.createRow(i) ; // 下标为1的行开始
- for(int j=0;j<CountColumnNum;j++)
- {
- // 在一行内循环
- HSSFCell cell = row.createCell( j) ;
- // 设置表格的编码集,使支持中文
- //
- // 先判断数据库中的数据类型
- // 将结果集里的值放入电子表格中
- try {
- cell.setCellValue(new HSSFRichTextString(rs.getString(j+1))) ;
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- i++ ;
- }
- } catch (SQLException e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- // 创建文件输出流,准备输出电子表格
- OutputStream out = new FileOutputStream("E:\\test.xls") ;
- wb.write(out) ;
- out.close() ;
- System.out.println("数据库导出成功") ;
- try {
- rs.close() ;
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- try {
- conn.close() ;
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- response.sendRedirect("test.jsp");
- }
- public static void main(String[] args)
- {
- try {
- @SuppressWarnings("unused")
- ReportServlet excel = new ReportServlet() ;
- } catch (Exception e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
-
- }
相关热门文章
给主人留下些什么吧!~~
评论热议