java中把SQL数据库中的表导出到excel中.怎么实现
前段时好做了，导入和的，你自己看吧
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= 0; i < 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= 1; i < rowCount; i )
{
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格式导入定单");
}
}
如何将sql数据库表变成excel表格
在SQL数据库的很多应用中，需要将SQL中的某据库或查询的导出到EXCEL中，因Excel更容易对数据处理和方便用户邮箱传输。本次经验归纳了SQL数据表导出到EXCEL中的三种方法：
1、复制粘贴，将SQL查询语句按指定条件导出数据；——适合小量数据。
2、导出完整的SQL数据表为Excel文件；——适合大量数据。
3、定时自动导出指定SQL数据表为Excel文件；——适合大量数据。
其它：DTS数据转换服务导入导出向导或者DTS设计器创建DTS包；
怎么从mysql数据库导出数据成excel表格
输入的T-SQL语句即可：
select * from 某个表 into outfile '文件路径'
比如，把如下的test数据库的一张i_student表导出d盘根目录下的1.xls：
use test
之后，输入：
select * from i_student into outfile 'd:/1.xls'
如何用java将数据库中的表导出到excel
第一步：用POI操作Excel
@Test
public void createXls() throws Exception{
//声明一个工作薄
HSSFWorkbook wb = new HSSFWorkbook();
//声
HSSFSheet sheet = wb.createSheet("第一个表");
//声
HSSFRow row = sheet.createRow(7);
//声明列
HSSFCell cel = row.createCell(3);
//写入数据
cel.setCellValue("你也好");
FileOutputStream fileOut = new FileOutputStream("d:/a/b.xls");
wb.write(fileOut);
fileOut.close();
}
步：导出指定数据库的所有表
分析：
1：某个数数据库有多少表，表名是什么？―――DataBaseMetadate.getMetadate().getTables(null,null,null,new String[]{Table}); - excel的文件名称。
2：对每一个表进行select * 操作。 － 每一个sheet的名称。
3：分析表结构，rs.getMetadate(); ResultSetMedated
4：多个列，列名是什么. － 字段名就是sheet的第一行信息。
5：获取每一行的数据 – 放到sheet第一行以后。
@Test
public void export() throws Exception{
//声明需要导出的数据库
String dbName = "focus";
//声明book
HSSFWorkbook book = new HSSFWorkbook();
//获取Connection,获取db的元数据
Connection con = DataSourceUtils.getConn();
//声明statemen
Statement st = con.createStatement();
//st.execute("use " dbName);
DatabaseMetaData dmd = con.getMetaData();
//获取数据库有多少表
ResultSet rs = dmd.getTables(dbName,dbName,null,new String[]{"TABLE"});
//获取所有表名 － 就是一个sheet
List tables = new ArrayList();
while(rs.next()){
String tableName = rs.getString("TABLE_NAME");
tables.add(tableName);
}
for(String tableName:tables){
HSSFSheet sheet = book.createSheet(tableName);
//声明sql
String sql = "select * from " dbName "." tableName;
//查询数据
rs = st.executeQuery(sql);
//根据查询的结果，分析结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取这个查询有多少行
int cols = rsmd.getColumnCount();
//获取所有列名
//创建第一行
HSSFRow row = sheet.createRow(0);
for(int i=0;i
String colName = rsmd.getColumnName(i 1);
//创建一个新的列
HSSFCell cell = row.createCell(i);
//写入列名
cell.setCellValue(colName);
}
//遍历数据
int index = 1;
while(rs.next()){
row = sheet.createRow(index );
//声明列
for(int i=0;i
String val = rs.getString(i 1);
//声明列
HSSFCell cel = row.createCell(i);
//放数据
cel.setCellValue(val);
}
}
}
con.close();
book.write(new FileOutputStream("d:/a/" dbName ".xls"));
}