从数据库导出数据到Excel表(jxl.jar)

December 17, 2023
测试
测试
测试
测试
8 分钟阅读
package com.*****.query; 
import java.io.File;

import java.io.IOException;

import java.io.OutputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement; 
import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory; 
import jxl.*;

import jxl.write.*;

import jxl.write.biff.RowsExceededException; 
/**
* 对Excel表进行操作

*

* @author ********

*

*/

public class ExcelUtil {

private static Log log = LogFactory.getLog(ExcelUtil.class); 
public boolean DB2Excel(ResultSet rs,OutputStream os) {

boolean flag = false;

WritableWorkbook workbook = null;

WritableSheet sheet = null;

Label label = null; 
// 创建Excel表

try {

// workbook = Workbook.createWorkbook(new File(“d:/output.xls”));

workbook = Workbook.createWorkbook(os);

// 创建Excel表中的sheet

sheet = workbook.createSheet(“First Sheet”, 0); 
// 定义表中的数据

// Label label = new Label(0, 0, “k k 中国人*”);

// Label label1 = new Label(0, 1, “A label record”);

// Label label3 = new Label(0, 2, “3.14159”);

// Number number = new Number(1, 1, 3.1459); 
// 向Excel中添加数据 
ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

String colName = null;

int row = 0;

// 添加标题

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

colName = rsmd.getColumnName(i + 1);

label = new Label(i, row, colName);

// log.info(“标题:”+i+”—“+row +”—“+ colName);

sheet.addCell(label);

}

row++;

log.info(“写入标题成功”);

while (rs.next()) {

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

label = new Label(i, row, rs.getString(i+1));

// log.info(“行:”+i+”—“+row +”—“+ rs.getString(i+1));

sheet.addCell(label);

}

row++;

}

log.info(“写入内容成功”); 
// 关闭文件

workbook.write();

workbook.close();

log.info(“数据成功写入Excel”);

flag = true;

} catch (SQLException e) {

log.info(e.getMessage());

} catch (RowsExceededException e) {

log.info(e.getMessage());

} catch (WriteException e) {

log.info(e.getMessage());

} catch (IOException e) {

log.info(e.getMessage());

} finally{

try {

workbook.close();

} catch (Exception e) {

}

}

return flag;

} 
public static void main(String[] args) {

try { 
Class.forName(“net.sourceforge.jtds.jdbc.Driver”);

Connection con = DriverManager

.getConnection(

“jdbc:jtds:sqlserver://localhost:1433/abc”,

“sa”, “sa”);

Statement st = con.createStatement();

ResultSet rs = st

.executeQuery(“select * from abc.dbo.sss”);

// if (!new ExcelUtil().DB2Excel(rs)){

// log.info(“数据写入失败”);

// }

rs.close();

st.close();

con.close(); 
} catch (Exception e) {

e.printStackTrace();

}

}

} 
在servlet中的使用 
// 写入EXCEL

if (action != null &&amp; action.startsWith(“excel.q”)) {

try {

// response.setContentType(“application/vnd.ms-Excel”);

//设置文件输出成Excel文件,在网页上的点击链接,就会下载成report.xls

response.setHeader(“Content-Disposition”, “attachment; filename=report.xls”);

st = con.createStatement();

rs = st.executeQuery(sql);

System.out.println(sql);

OutputStream os = response.getOutputStream(); 
if (!new ExcelUtil().DB2Excel(rs, os)) {

log.info(“Excel写入失败”);

}

os.flush();

os.close();

} catch (Exception e) {

log.info(e.getMessage());

}

}
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/100649.html原文链接:

继续阅读

更多来自我们博客的帖子

如何安装 BuddyPress
由 测试 December 17, 2023
经过差不多一年的开发,BuddyPress 这个基于 WordPress Mu 的 SNS 插件正式版终于发布了。BuddyPress...
阅读更多
Filter如何工作
由 测试 December 17, 2023
在 web.xml...
阅读更多
如何理解CGAffineTransform
由 测试 December 17, 2023
CGAffineTransform A structure for holding an affine transformation matrix. ...
阅读更多