jxl怎么实现从数据库导出到excel表格

前段时间正好做了，导入和导出的，你自己看吧

/**导出数据为XLS格式

* @param fos

* @param bo

*/

public void writeExcelBo(FileOutputStream fos, java.util.Vector ve)

{

jxl.write.WritableWorkbook wwb

try

{

wwb= Workbook.createWorkbook(fos)

jxl.write.WritableSheet ws= wwb.createSheet("booksheet", 10)

ws.addCell(new jxl.write.Label(0, 1, "书目ID"))

ws.addCell(new jxl.write.Label(1, 1, "ISBN"))

ws.addCell(new jxl.write.Label(2, 1, "定价"))

ws.addCell(new jxl.write.Label(3, 1, "书名"))

ws.addCell(new jxl.write.Label(4, 1, "原书名"))

ws.addCell(new jxl.write.Label(5, 1, "副题名"))

ws.addCell(new jxl.write.Label(6, 1, "著者"))

ws.addCell(new jxl.write.Label(7, 1, "译者"))

ws.addCell(new jxl.write.Label(8, 1, "版次"))

ws.addCell(new jxl.write.Label(9, 1, "出版地"))

ws.addCell(new jxl.write.Label(10, 1, "出版社"))

ws.addCell(new jxl.write.Label(11, 1, "出版日期"))

ws.addCell(new jxl.write.Label(12, 1, "页数"))

ws.addCell(new jxl.write.Label(13, 1, "书高"))

ws.addCell(new jxl.write.Label(14, 1, "装帧"))

ws.addCell(new jxl.write.Label(15, 1, "丛书名"))

ws.addCell(new jxl.write.Label(16, 1, "一般性附注项"))

ws.addCell(new jxl.write.Label(17, 1, "简介"))

ws.addCell(new jxl.write.Label(18, 1, "主题词"))

ws.addCell(new jxl.write.Label(19, 1, "中图法分类"))

ws.addCell(new jxl.write.Label(20, 1, "更新日期"))

ws.addCell(new jxl.write.Label(21, 1, "本数"))

book=new Book[ve.size()]

for (int i= 0i <ve.size()i++)

{

book[i]= (Book)ve.get(i)

ws.addCell(new jxl.write.Label(0, i + 2, "" + book[i].getBookId()))

ws.addCell(new jxl.write.Label(1, i + 2, book[i].getIsbn()))

ws.addCell(new jxl.write.Label(2, i + 2, "" + book[i].getPrice()))

ws.addCell(new jxl.write.Label(3, i + 2, book[i].getBookTitle()))

ws.addCell(new jxl.write.Label(4, i + 2, book[i].getOldFilename()))

ws.addCell(new jxl.write.Label(5, i + 2, book[i].getSubTitle()))

ws.addCell(new jxl.write.Label(6, i + 2, book[i].getWriter()))

ws.addCell(new jxl.write.Label(7, i + 2, book[i].getTranscribe()))

ws.addCell(new jxl.write.Label(8, i + 2, "" + book[i].getVersion()))

ws.addCell(new jxl.write.Label(9, i + 2, book[i].getPublishCity()))

ws.addCell(new jxl.write.Label(10, i + 2, book[i].getPublisher()))

ws.addCell(new jxl.write.Label(11, i + 2, book[i].getPublishDate().toString()))

ws.addCell(new jxl.write.Label(12, i + 2, "" + book[i].getPage()))

ws.addCell(new jxl.write.Label(13, i + 2, "" + book[i].getHight()))

ws.addCell(new jxl.write.Label(14, i + 2, book[i].getInstall()))

ws.addCell(new jxl.write.Label(15, i + 2, book[i].getSeries()))

ws.addCell(new jxl.write.Label(16, i + 2, book[i].getNotes()))

ws.addCell(new jxl.write.Label(17, i + 2, book[i].getPrecisnotes()))

ws.addCell(new jxl.write.Label(18, i + 2, book[i].getSubject()))

ws.addCell(new jxl.write.Label(19, i + 2, book[i].getCls().replaceAll("_", "")))

ws.addCell(new jxl.write.Label(20, i + 2, book[i].getUpdatedate().toString()))

ws.addCell(new jxl.write.Label(21, i + 2, "0"))

}

jxl.write.WritableFont wfc=

new jxl.write.WritableFont(

WritableFont.ARIAL,

255,

WritableFont.BOLD,

false,

UnderlineStyle.NO_UNDERLINE,

jxl.format.Colour.BLACK)

jxl.write.WritableCellFormat wcfFC= new jxl.write.WritableCellFormat(wfc)

ws.addCell(new jxl.write.Label(0, 0, "为保证您提交定单的稳定和正确，导入定单时候请勿更改此表格式（请勿更改书目ID,订购本数自行添加！）"))

wwb.write()

//关闭Excel工作薄对象

wwb.close()

} catch (IOException e)

{} catch (RowsExceededException e)

{} catch (WriteException e)

{}

}

//导入EXCEL

if (f.getName().indexOf(".xls") >0)

{

try

{

fis= new FileInputStream(f)

BookBean bob= new BookBean()

UserBean usb= new UserBean()

jxl.Workbook rwb= Workbook.getWorkbook(fis)

jxl.Sheet sh= rwb.getSheet(0)

int rowCount= sh.getRows()

SimpleDateFormat sdf= new SimpleDateFormat("dd/MM/yyyy")

book= new Book[rowCount - 1]

for (int i= 1i <rowCounti++)

{

book[i - 1]= new Book()

jxl.Cell[] ce= sh.getRow(i)

book[i - 1].setIsbn(ce[0].getContents().toString())

book[i - 1].setSeries(ce[1].getContents().toString())

book[i - 1].setBookTitle(ce[2].getContents().toString())

book[i - 1].setWriter(ce[3].getContents().toString())

book[i - 1].setTranscribe(ce[4].getContents().toString())

book[i - 1].setPublisher(ce[5].getContents().toString())

book[i - 1].setPublishDate(sdf.parse(ce[6].getContents().toString(), new ParsePosition(0)))

book[i-1].setVersion(Integer.parseInt(ce[7].getContents().toString()))

book[i-1].setPage(Integer.parseInt(ce[8].getContents().toString()))

book[i-1].setCls(ce[9].getContents().toString())

book[i-1].setPrecisnotes(ce[10].getContents().toString())

book[i-1].setInstall(ce[11].getContents().toString())

book[i-1].setPrice(Float.parseFloat(ce[12].getContents().toString()))

book[i-1].setUserid(usb.getUser().getUserid())

getVector().addElement(book[i - 1])

}

rwb.close()

fis.close()

} catch (FileNotFoundException e)

{} catch (BiffException e)

{} catch (IOException e)

{} catch (NumberFormatException e)

{

ShowMessage("数据导入失败，请按照本软件要求的EXCEL格式导入定单")

}

}

导出到服务器，详细代码如下：

public String exportMeetingUser() throws NumberFormatException,

ServiceException, IOException, RowsExceededException,

WriteException {

String forward = SUCCESS

// TODO 进行导出 *** 作

// 获取查询条件

String username = this.getParameter("username")

String mobile = this.getParameter("mobile")

String isAdmin = this.getParameter("isAdmin")

String meetingId = this.getParameter("meetingId")

User user = new User()

user.setName(username)

user.setMobile(mobile)

// 把PAGE_SIZE设置很大

pageSize = 1000

Pager<User>pager = userService.findMeetingUserPager(

Long.valueOf(meetingId), user, currentPage, pageSize, isAdmin)

log.debug("meeting id is： {}", meetingId)

List<User>userList = pager.getPageRecords()

String[] title = { "姓名", "手机号码", "职位(通讯录)", "单位", "房间号", "显示房间号", "性别",

"电子邮箱", "城市", "加入通讯录", "显示电话号码", "排序", "职位简称", "显示职位简称" }

// TODO 进行导出的 *** 作

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss")

String exportFileName = sdf.format(new Date())

// 创建文件夹

File uploadFoldPath = new File(fileSystemService.getDocumentRoot()

+ this.USER_EXPORT_FOLDER)

if (!uploadFoldPath.exists()) {

uploadFoldPath.mkdirs()

}

// 判断文件是否存在

String fileSaveName = exportFileName + ".xls"

String dstPath = fileSystemService.getDocumentRoot()

+ this.USER_EXPORT_FOLDER + File.separator + fileSaveName

String from = "WEB"

String documentRoot = fileSystemService.getDocumentRoot()

// DOCUMENT ROOT

getRequest().setAttribute("documentRoot", documentRoot)

getRequest().setAttribute("from", from)

OutputStream os = new FileOutputStream(dstPath)

// 再建完这个文件的时候再建立工作文件

jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os)

int rowIndex = 0

Label label

WritableCellFormat wc = new WritableCellFormat()

wc.setAlignment(Alignment.CENTRE)

//wc.set

WritableSheet sheet = wwb.createSheet("会议用户导出", 0)

label = new Label(2, rowIndex, "此文件为会议云用户导出文件")

sheet.addCell(label)

rowIndex++

// 将列标题循环添加到Label中

for (int i = 0i <title.lengthi++) {

label = new Label(i, rowIndex, title[i])

sheet.addCell(label)

}

rowIndex++

// TODO下面是添加数据

if (userList != null &&userList.size() >0) {

for (User exportUser : userList) {

MeetingMember member = meetingMemberService.selectById(

exportUser.getId(), Long.valueOf(meetingId))

exportUser.setMeetingMember(member)

// "姓名", "手机号码", "职位(通讯录)", "单位", "房间号", "显示房间号", "性别",

// "电子邮箱", "城市", "加入通讯录", "显示电话号码", "排序", "职位简称", "显示职位简称"

// 用户名

label = new Label(0, rowIndex, exportUser.getName())

sheet.addCell(label)

label = new Label(1, rowIndex, exportUser.getMobile())

sheet.addCell(label)

label = new Label(2, rowIndex, exportUser.getMeetingMember()

.getJob())

sheet.addCell(label)

// 单位

label = new Label(3, rowIndex, exportUser.getMeetingMember()

.getDepartment())

sheet.addCell(label)

// 房间号

label = new Label(4, rowIndex, exportUser.getMeetingMember()

.getRoomNumber())

rowIndex++

}

}

wwb.write()

wwb.close()

// 现在开始进行下载 *** 作

MeetingFiles meetingFiles = new MeetingFiles()

meetingFiles.setFilePath(this.USER_EXPORT_FOLDER)

meetingFiles.setFileName(fileSaveName)

meetingFiles.setFileSaveName(fileSaveName)

this.getRequest().setAttribute("meetingFiles", meetingFiles)

return forward

jxl最多支持excel2003，所以单sheet最大的记录数为65536；Office2007单sheet支持的记录数已经超过100W了。

对于大批量数据，我都是每次取65535条记录出来（第一行有表头）放在一个Sheet里面。插入完毕后清空list再去取下一页。

经测试，单文件多Sheet出现内存溢出比多文件单sheet的机率大得太多，服务器的jvm怎么得也要512以上。

---