将数据库数据导入Excel

1.缂栧啓杩炴帴鏁版嵁搴撳痉JavaBean锛屼唬鐮佸涓?

聽

package db;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

public class ConnDB

{

聽聽聽聽聽聽聽 private聽 String driverName="com.mysql.jdbc.Driver";

聽聽聽聽聽聽聽 private String聽 user="root";

聽聽聽聽聽聽聽 private String聽 password="";

聽聽聽聽聽聽聽 private String url="jdbc:mysql://localhost:3306/test";

聽聽聽聽聽聽聽 PreparedStatement聽 ps=null;

聽聽聽聽Connection conn=null;

聽聽聽聽ResultSet res=null;

聽聽聽聽聽聽聽 public聽 ResultSet getResult(String sql){

聽聽聽聽聽聽聽聽

聽聽聽聽聽聽聽聽try{

聽聽聽聽聽聽聽聽聽Class.forName(driverName);

聽聽聽聽聽聽聽聽聽conn=DriverManager.getConnection(url,user,password);

聽聽聽聽聽聽聽聽聽ps=conn.prepareStatement(sql);

聽聽聽聽聽聽聽聽聽res=ps.executeQuery();

聽聽聽聽聽聽聽聽聽聽聽聽

聽聽聽聽聽聽聽聽聽聽聽

聽聽聽聽聽聽聽聽}

聽聽聽聽聽聽聽聽catch(Exception e){

聽聽聽聽聽聽聽聽聽e.printStackTrace();

聽聽聽聽聽聽聽聽}

聽聽聽聽聽聽聽聽

聽聽聽聽聽聽聽聽 return res;

聽聽聽聽聽聽聽 }

聽聽聽聽聽聽聽

聽聽聽聽聽聽聽 public void close(){

聽聽聽聽聽聽聽聽try{

聽聽聽聽聽聽聽聽if(res!=null){res.close();}

聽聽聽聽聽聽聽聽if(ps!=null){ps.close();}

聽聽聽聽聽聽聽聽if(conn!=null){conn.close();}

聽聽聽聽聽聽聽聽 }

聽聽聽聽聽聽聽聽catch(Exception e){e.printStackTrace();}

聽聽聽聽聽聽聽聽}

聽聽聽聽聽聽聽

聽聽聽聽

}

2.聽聽缂栧啓聽灏嗘暟鎹簱鏁版嵁瀵煎叆Excel鐨凷ervlet锛屼唬鐮佸涓嬄犅犅犅犅犅犅犅犅犅犅?

聽

package servlet;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.sql.Date;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Types;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import jxl.Workbook;

import jxl.write.DateFormat;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import db.ConnDB;

public class ExcelServlet extends HttpServlet {

聽

聽public void doGet(HttpServletRequest request, HttpServletResponse response)

聽throws IOException, ServletException

聽{

聽HttpSession session = request.getSession(true);

聽String fileName = (String)session.getAttribute("fileName");//鍓嶅彴闇?瑕佹妸杩欎簺鍙傛暟鍐欏埌session涓??聽String querySql = (String)session.getAttribute("querySql");

聽聽聽聽聽聽聽 // session.removeAttribute("fileName");

聽// session.removeAttribute("querySql");

聽try

聽{

聽WritableFont arial15font = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD);

聽arial15font.setColour(jxl.format.Colour.LIGHT_BLUE);

聽WritableCellFormat arial15format = new WritableCellFormat (arial15font);

聽arial15format.setAlignment(jxl.format.Alignment.CENTRE);

聽arial15format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

聽arial15format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);

聽File file = new File("output.xls");

聽WritableWorkbook workbook = Workbook.createWorkbook(file); //寤虹珛宸ヤ綔绨?聽WritableSheet sheet = workbook.createSheet("Sheet 1", 0); //寤虹珛sheet

聽sheet.addCell(new Label(0, 0, fileName, arial15format));

聽sheet.setName(fileName);

聽ConnDB db=new ConnDB();

聽ResultSet rs = db.getResult("select *from message");

聽if(rs != null) {

聽WritableFont arial11font = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD);

聽WritableCellFormat arial11format = new WritableCellFormat (arial11font);

聽arial11format.setAlignment(jxl.format.Alignment.CENTRE);聽

聽arial11format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

聽arial11format.setBackground(jxl.format.Colour.RED);

聽int row = 0;

聽int col = 1;

聽// 鍐欒〃澶翠俊鎭?聽ResultSetMetaData rsmd = rs.getMetaData();

聽int []validColumn = new int[rsmd.getColumnCount()];

聽for(int i=0; i<rsmd.getColumnCount(); i++) {

聽String colName = rsmd.getColumnName(i+1);

聽if(colName.indexOf("NextIsURL") != -1) {

聽validColumn[i] = -1;

聽}

聽else if(colName.indexOf("ThisIsCheckBox") != -1){

聽validColumn[i] = -2;

聽}

聽else {

聽sheet.addCell(new Label(row, col, colName, arial11format));

聽validColumn[i] = getStrLen(colName) + 4;

聽//sheet.setColumnView(row, validColumn[i]);

聽row++;

聽}

聽col++;

聽if(row>1) {

聽sheet.mergeCells(0, 0, row-1, 0);

聽}

聽 WritableFont arial9font = new WritableFont(WritableFont.ARIAL, 9);

聽WritableCellFormat arial9format = new WritableCellFormat(arial9font);

聽//arial9format.setAlignment(Alignment.CENTRE);聽

聽arial9format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

聽DateFormat dateFormat = new DateFormat ("yyyy-MM-dd");

聽WritableCellFormat dateCellFormat = new WritableCellFormat(arial9font, dateFormat);

聽//dateCellFormat.setAlignment(Alignment.CENTRE);聽

聽dateCellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

聽DateFormat timeFormat = new DateFormat ("hh:mm:ss");

聽WritableCellFormat timeCellFormat = new WritableCellFormat(arial9font, timeFormat);

聽//timeCellFormat.setAlignment(Alignment.CENTRE);聽

聽timeCellFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

聽// 寰幆鍐欐墍鏈夎褰?

聽while (rs.next()) {

聽row = 0;

聽for(int i=0; i<rsmd.getColumnCount(); i++) {

聽// 濡傛灉鏄摼鎺ュ垪锛屽垯涓嶅啓鍏ユ枃浠朵腑

聽if(validColumn[i] == -1 || validColumn[i] == -2) {

聽continue;

聽}

聽// 璇诲彇姣忎竴鍒楃殑绫诲瀷

聽int columnType = rsmd.getColumnType(i+1);

聽switch (columnType)

聽{

聽case Types.BIT:

聽case Types.BIGINT:

聽case Types.BOOLEAN:

聽case Types.NUMERIC:

聽case Types.REAL:

聽case Types.SMALLINT:

聽case Types.TINYINT:

聽case Types.DECIMAL:

聽case Types.FLOAT:

聽case Types.INTEGER :

聽float number = rs.getFloat(i+1);

聽sheet.addCell(new jxl.write.Number(row, col, number, arial9format));

聽break;

聽case Types.DATE:

聽case Types.TIMESTAMP:

聽Date date = rs.getDate(i+1);

聽if(date == null) {

聽sheet.addCell(new jxl.write.Blank(row, col));

聽}

聽else {

聽sheet.addCell(new jxl.write.DateTime(row, col, date, dateCellFormat ));

聽}

聽break;

聽case Types.TIME:

聽Date time = rs.getDate(i+1);

聽if(time == null) {

聽sheet.addCell(new jxl.write.Blank(row, col));

聽}

聽else {

聽sheet.addCell(new jxl.write.DateTime(row, col, time, timeCellFormat ));

聽}

聽break;

聽default:

聽String str = rs.getString(i+1);

聽if(str == null) {

聽sheet.addCell(new jxl.write.Blank(row, col, arial9format));

聽}

聽else {

聽str = str.trim();

聽sheet.addCell(new Label(row, col, str, arial9format));

聽int len = getStrLen(str);

聽if(len > validColumn[i]) {

聽validColumn[i] = len;

聽}

聽break;

聽}

聽row++;

聽}

聽col++;

聽}

聽row = 0;

聽for(int i=0; i<rsmd.getColumnCount(); i++) {

聽if(validColumn[i] > 0){

聽sheet.setColumnView(row, validColumn[i]);

聽row++;

聽}

聽db.close();

聽workbook.write();

聽workbook.close();

聽response.setContentType("application/octet-stream");

聽fileName = new String(fileName.getBytes("gb2312"), "ISO8859_1");

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

聽int len = (int)file.length();

聽byte []buf = new byte[len];

聽FileInputStream fis = new FileInputStream(file);

聽OutputStream out = response.getOutputStream();

聽len = fis.read(buf);

聽out.write(buf, 0, len);

聽out.flush();

聽fis.close();

聽file.delete();

聽}

聽catch (Exception e) {

聽System.out.println("[Info: ] User canceled - " + e.getMessage());

聽}

聽public int getStrLen(String str) {

聽if(str == null) {

聽return 0;

聽}

聽byte []buf = str.getBytes();

聽return buf.length;

聽}

聽public void doPost(HttpServletRequest request, HttpServletResponse response)

聽throws IOException, ServletException

聽{

聽doGet(request, response);

聽}

}

聽

3.鍦╳eb.xml娣诲姞濡備笅浠ｇ爜

聽

聽聽聽 <description>This is the description of my J2EE component</description>

聽聽聽 <display-name>This is the display name of my J2EE component</display-name>

聽聽聽 <servlet-name>ExcelServlet</servlet-name>

聽聽聽 <servlet-class>servlet.ExcelServlet</servlet-class>

聽 </servlet>

聽

聽<servlet-mapping>

聽聽聽 <servlet-name>ExcelServlet</servlet-name>

聽聽聽 <url-pattern>/excelservlet</url-pattern>

聽 </servlet-mapping>

聽

4.鍦╦sp鏂囦欢鐐瑰嚮鎸夐挳锛屾煡鐪嬫樉绀轰俊鎭紝jsp鏂囦欢浠ｇ爜濡備笅

聽聽聽聽聽

<%@ page language="java" contentType="text/html; charset=GB18030"

聽聽聽 pageEncoding="GB18030"%>

<html>

<head>

<title>Insert title here</title>

</head>

<body>

session.setAttribute("fileName", "璁垮鐣欒█淇℃伅");

session.setAttribute("title", "璁垮鐣欒█");

</form>

</body>

</html>

聽

聽聽聽聽聽聽聽

将数据库数据导入Excel

继续阅读

Android – ListView 中添加按钮，动态删除添加ItemView的操作

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

GridView终极用法(一)

登录plsql 报错 the account is locked --用户被锁

SequoiaDB巨杉数据库C++驱动概述