jsp导出excel mysql_jsp怎么导出MySQL数据到excel，求详细代码，我这里是用jxl.jar包的...

展开全部

我这有段java代码是将Mysql中的数据查询出来后写入到excel中的，利用的也是jxl.jar包，代码如下，62616964757a686964616fe78988e69d8331333335336433可供参考：

package common;

import java.io.File;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.Properties;

import java.util.TimeZone;

import jxl.Workbook;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

public class PointUseCount {

public Connection getConnection() {

String url = "jdbc:mysql://127.0.0.1:3306/member";

String userName = "root";

String password = "root";

Connection con = null;

try {

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

con = DriverManager.getConnection(url, userName, password);

} catch (SQLException sw) {

System.out.println("connection error...");

}

if (null == con) {

for (int i = 0; i < 10; i++) {

try {

Thread.sleep(2000);

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

con = DriverManager.getConnection(url, userName, password);

} catch (Exception e) {

}

if (null != con) {

break;

}

}

if (null != con) {

return con;

} else {

return null;

}

} else {

return con;

}

}

public String pointsUseCount(String sql,String month) {

Connection conn = null;

Statement cstmt = null;

ResultSet rs = null;

Integer customerId = null;

Integer tempPoints = null;

Integer confirmPoints = null;

PointUseCount cusCamPoints = new PointUseCount();

conn = cusCamPoints.getConnection();

Integer count = 0;

String nowDate = getJapanDate().substring(0,10);

Integer nowMonth = Integer.valueOf(nowDate.substring(5,7));

Integer day = Integer.valueOf(nowDate.substring(8,10));

if (null != conn) {

try {

//String name = nowDate+"("+ month +"月).xls";

String name = null;

name = nowMonth +"月"+day+"日.xls";

WritableWorkbook book = Workbook.createWorkbook(new File("D:\\user\\local\\pointExcel\\"+name));//本地测试使用

//WritableWorkbook book = Workbook.createWorkbook(new File("/usr/local/pointExcel/"+name));//AWS上开发使用

// 设置第一个输入参数的值为point过期的截止即当前日本时间，第二个值为输出结果

cstmt = conn.createStatement();

rs = cstmt.executeQuery(sql);

// 生成名为“第一页”的工作表，参数0表示这是第一页

WritableSheet sheet=null;//创建sheet

while(rs.next()){

if(count/65536 == 0 && count%65536 == 0){

sheet = book.createSheet((count/65536 + 1) + " " + name,0);

}else if(count/65536 == 1 && count%65536 == 0){

sheet = book.createSheet((count/65536 + 1) + " " + name,1);

count = 0;

}else if (count/65536 == 2 && count%65536 == 0) {

sheet = book.createSheet((count/65536 + 1) + " " + name,2);

count = 0;

}

customerId = Integer.valueOf(rs.getString(1));

tempPoints = rs.getInt(2);

confirmPoints = rs.getInt(3);

// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)

// Label cusId=new Label(0,count,customerId);

jxl.write.Number cusId = new jxl.write.Number(0,count,customerId);

sheet.addCell(cusId);

//单元格位置是第二列，第一行

jxl.write.Number tempPoint = new jxl.write.Number(1,count,tempPoints);

sheet.addCell(tempPoint);

// 单元格位置是第三列，第一行

jxl.write.Number confirmPoint = new jxl.write.Number(2,count,confirmPoints);

sheet.addCell(confirmPoint);

count ++;

}

book.write();//写入数据

book.close(); //关闭文件

return "successed";

} catch (Exception e) {

System.out.println("error ... "+e.toString());

return "failed";

}

} else {

System.out.println("pointsUseCount connection Mysql error...");

return "failed";

}

}

public static String getJapanDate() {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

sdf.setTimeZone(TimeZone.getTimeZone("Japan"));

//System.out.println("Japan time successed");

return sdf.format(new Date());

}

public Properties getProperties() {

InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("sendmail.properties");

Properties p = new Properties();

try {

p.load(inputStream);

} catch (IOException e) {

System.out.println(e.toString());

}

return p;

}

public static void main(String[] args) {

PointUseCount checkConf = new PointUseCount();

Properties props = checkConf.getProperties();

String sql1 = "sql查询语句";

try {

String startDate = getJapanDate();

Integer nowMonth = Integer.valueOf(startDate.substring(5,7));

System.out.println("startDate: " + startDate);

sql1 = new String(sql1.getBytes("ISO-8859-1"),"utf-8");

String check = checkConf.pointsUseCount(sql1,"1");

System.out.println("Jan PointUseCount: " + check);

String endDate = getJapanDate();

System.out.println("endDate: " + endDate);

System.out.println("---------------------------------------------------------");

} catch (Exception e) {

System.out.println(e.toString());

}

System.exit(0);

}

}

本回答由电脑网络分类达人 李孝忠推荐

已赞过

已踩过<

你对这个回答的评价是？

评论

收起