mysql中sql数据怎么导出,怎么从mysql数据库导出数据成excel表格 | 如何将数据库导为excel表格...

java中把SQL数据库中的表导出到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= 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"));

}