用jxl来实现将数据导出成EXECEL表

开始导入jxl.jar包，前面的数据库连接我就不说了

导出表的格式说明

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME ='users' AND XTYPE='U')
DROP TABLE users
GO
create table users (
   userid               int  identity(1,1)   not null,
   qx_id                int                  null,
   dept_id              int                  null,
   username             varchar(50)          not null,
   password             varchar(100)         not null,
   sex                  varchar(50)          default '男',
   users_note           varchar(200)         null,
   userdel_sign         int                  default 0,
   constraint PK_USERS primary key nonclustered (userid)
)

实现导出数据的类

package com.huawei.abstructFactorymode;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.huawei.abstructFactory.abstracFactoryFaces;
import com.huawei.abstructFactory.abstracProduct;

/**
 * ?ù??????????????????????l???????
 *
 * @author Administrator
 *
 */
@SuppressWarnings("unused")
public class Connceshi {
 public static void main(String[] args) {
  abstracFactoryFaces FaxF;
  abstracProduct FaxP;
  FaxF = (abstracFactoryFaces) new mssqlFactory();
  FaxP = ((mssqlFactory) FaxF).Factory();
  // 建立数据库连接
  Connection conn = FaxP.JDBCFacrory();
  try {
   Statement stmp = conn.createStatement();
   ResultSet rstp = stmp.executeQuery("select * from users");
   int nCount = rstp.getMetaData().getColumnCount();
   System.out.println(nCount);
   WritableWorkbook book = Workbook.createWorkbook(new File(
     "commodity.xls"));
   WritableSheet sheet = book.createSheet("用户信息", 0);
   // 生成名为"商品信息"的工作表，参数0表示这是第一页
   int nI = 1;
   // 表字段名
   sheet.addCell(new jxl.write.Label(0, 0, "用户ID"));
   sheet.addCell(new jxl.write.Label(1, 0, "权限ID"));
   sheet.addCell(new jxl.write.Label(2, 0, "部门ID"));
   sheet.addCell(new jxl.write.Label(3, 0, "用户名"));
   sheet.addCell(new jxl.write.Label(4, 0, "密码"));
   sheet.addCell(new jxl.write.Label(5, 0, "性别"));
   sheet.addCell(new jxl.write.Label(6, 0, "扩展字段"));
   sheet.addCell(new jxl.write.Label(7, 0, "删除标志"));
   // 将数据追加
   while (rstp.next()) {
    for (int nJ = 1; nJ <= nCount; nJ++) {
     sheet.addCell(new jxl.write.Label(nJ - 1, nI, rstp
       .getString(nJ)));
    }
    nI++;
   }
   book.write();
   book.close();

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

 }
}

评论

2 楼 jiangyou001 2008-04-25  

为什么会有中文问题

1 楼 JasonMiao 2007-10-11  

int nCount = rstp.getMetaData().getColumnCount();
int nI = 1;
while (rstp.next()) {
    for (int nJ = 1; nJ <= nCount; nJ++) {
     sheet.addCell(new jxl.write.Label(nJ - 1, nI, rstp.getString(nJ)));
    }
    nI++;
}
你这样的处理好啊！我现在用Spring中封装的query来操作ResultSet，统一起来对其他操作很好，现在项目要求导出成excel格式，烦啊！一天都没实现。看来，自己写接口有好也有不好啊。你这文也给了我提示，可能独立操作excel文件比较好。
欢迎交流http://ismiao.blogbus.com/