关于jxl 导入导出excel到数据库

字符串本身就是一个对象，而且 无论是你所说的对象还是字符串，都要去查询，在while（rs.next()）中，你可以把查询出来的结果集，封装到list集合中，然后把这个集合传给一个工具类，这个工具类专门实现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

---