急请高手,,,如果把数据库的数据导入到excel里?

newleave840617 2004-08-31 11:04:03
各位高手好:
我现在用的是oracle数据库,我要把数据库中的数据通过jsp+bean程序导入成excel文件,有什么好方法?
...全文
215 点赞 收藏 20
写回复
20 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
newleave840617 2004-08-31
请问用路径该怎样用呢?
回复
LittleLandlord 2004-08-31
URL中的DBQ可以用路径的,不一定要配置ODBC数据源
回复
LittleLandlord 2004-08-31
给你一个读Excel文件的例子,参考一下,把oracle读出,写入Excel就OK了吧
package myapp;
import java.awt.*;
import java.sql.*;

//Connect Excel Files
public class connexcel{

public void conn() {
Connection c = null;
Statement stmnt = null;
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
c = DriverManager.getConnection( "jdbc:odbc:commbook", "", "" );
stmnt = c.createStatement();
String query = "SELECT * FROM [Sheet1$] ";
ResultSet rs = stmnt.executeQuery( query );

ResultSetMetaData rsmd = rs.getMetaData();
int columncount = rsmd.getColumnCount();
while( rs.next() ) {
for (int i = 1; i <= columncount; i++) {
if(i>1) //用逗号分隔各列
System.out.print(", ");
String columnValue = rs.getString(i);
System.out.print(columnValue);
}
System.out.println("");
}
}
catch( Exception e ) {
System.err.println( e );
}
finally {
try {
stmnt.close();
c.close();
}
catch( Exception e ) {
System.err.println( e );
}
}
}
}
回复
boykent 2004-08-31
IBM的jxl方式:
package excelfile;
import java.text.DateFormat;
import java.util.*;
import java.io.*;
import jxl.*;

/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2004</p>
* <p>Company: </p>
* @author not attributable
* @version 1.0
*/

public class Jxlcreateexcel {
public Jxlcreateexcel() {
}

public void getExcelFile() {
String tableName = "EXCEL_OUTPUT_TEST_REALDATA_2";
String im = "";
String sid = "oral";
String server = "10.254.100.25";
String whereItem = "rownum <3375";
try {
jxl.write.WritableWorkbook book = Workbook.createWorkbook(new File(
"/home/ljz/filestest/jxlEXCEL_OUTPUT_TEST_REALDATA_2.xls"));
jxl.write.WritableSheet sh = book.createSheet("jxltest", 0);
DBAccess dbaccess = new DBAccess();
dbaccess.setSelectItems(tableName);
dbaccess.setSid(sid);
dbaccess.setServer(server);
dbaccess.setItems(im);
dbaccess.setWhereItem(whereItem);
Hashtable sels = null;
sels = dbaccess.getSelectData();
int hashElement = sels.size();
for (int y = 0; y < hashElement; y++) {
Vector items = (Vector) sels.remove(String.valueOf(y));
for (int x = 0; x < items.size(); x++) {
jxl.write.Label labelCell = new jxl.write.Label(x, y,
(String) items.get(x));
sh.addCell(labelCell);
labelCell = null;
}
}
book.write();
book.close();
}
catch (Exception e) {
e.printStackTrace();
}
}

public static void main(String args[]) {
Date time = new Date();
DateFormat fullDateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG,DateFormat.LONG);
System.out.println("The process start time:"+fullDateFormat.format(time));
Jxlcreateexcel jxlexcle = new Jxlcreateexcel();
try {
jxlexcle.getExcelFile();
}
catch (Exception e) {
e.printStackTrace();
}
Date time1 = new Date();
System.out.println("The process end time:"+fullDateFormat.format(time1));
}

}

这是我写的在linux上面导入excel的方式,windows里面也一样,只不过把生成文件的目录改一下,还有就是你需要下载一个lib,这个就是jxl.jar,(注:DBAccess是我写的一个连接数据库以及从数据库获取数据的一个类 )另外还有一种方式就是poi,我也写了,如果需要的话可以找我 msn: boykent@hotmail.com
回复
awaysrain 2004-08-31
去下poi包吧,里面的文档也很详细的
回复
heartofsword 2004-08-31
这些是用EXCEL做WEB页时自动生成的
回复
wucr 2004-08-31
up
回复
newleave840617 2004-08-31
<link rel=File-List href="./Book1.files/filelist.xml">
<link rel=Edit-Time-Data href="./Book1.files/editdata.mso">
<link rel=OLE-Object-Data href="./Book1.files/oledata.mso">
<link rel=File-List href="./gsdj.files/filelist.xml">
<link rel=Edit-Time-Data href="./gsdj.files/editdata.mso">
<link rel=OLE-Object-Data href="./gsdj.files/oledata.mso">

这些语句是什么意思啊,我能直接拿来用吗?
回复
heartofsword 2004-08-31
<body link=blue vlink=purple class=xl26>

<table x:str border=0 cellpadding=0 cellspacing=0 width=1526 style='border-collapse:
collapse;table-layout:fixed;width:1146pt'>
<col class=xl26 width=197 style='mso-width-source:userset;mso-width-alt:6304;
width:148pt'>
<col class=xl26 width=245 style='mso-width-source:userset;mso-width-alt:7840;
width:184pt'>
<col class=xl26 width=117 style='mso-width-source:userset;mso-width-alt:3744;
width:88pt'>
<col class=xl26 width=101 style='mso-width-source:userset;mso-width-alt:3232;
width:76pt'>
<col class=xl26 width=165 style='mso-width-source:userset;mso-width-alt:5280;
width:124pt'>
<col class=xl26 width=125 style='mso-width-source:userset;mso-width-alt:4000;
width:94pt'>
<col class=xl26 width=72 span=8 style='width:54pt'>
<tr height=53 style='mso-height-source:userset;height:39.95pt'>
<td colspan=6 height=53 class=xl29 width=950 style='height:39.95pt;
width:714pt'>name</td>
<td class=xl26 width=72 style='width:54pt'></td>
<td class=xl26 width=72 style='width:54pt'></td>
<td class=xl26 width=72 style='width:54pt'></td>
<td class=xl26 width=72 style='width:54pt'></td>
<td class=xl26 width=72 style='width:54pt'></td>
<td class=xl26 width=72 style='width:54pt'></td>
<td class=xl26 width=72 style='width:54pt'></td>
<td class=xl26 width=72 style='width:54pt'></td>
</tr>
<tr class=xl28 height=37 style='mso-height-source:userset;height:27.95pt'>
<td height=37 class=xl27 width=197 style='height:27.95pt;width:148pt'>name1</td>
<td class=xl27 width=245 style='border-left:none;width:184pt'>name2</td>
<td class=xl27 width=117 style='border-left:none;width:88pt'>name3</td>
<td class=xl27 width=101 style='border-left:none;width:76pt'>name4</td>
<td class=xl27 width=165 style='border-left:none;width:124pt'>name5</td>
<td class=xl27 width=125 style='border-left:none;width:94pt'>name6</td>
<td colspan=8 class=xl26 style='mso-ignore:colspan'></td>
</tr>

<% if(result.getRows()==1){ %>
<tr><td colspan=8>name</td></tr>
<% } else {
for (int r=1; r<result.getRows(); r++) {
%>


<tr class=xl24 height=37 style='mso-height-source:userset;height:27.95pt'>
<td height=37 class=xl24 width=197 style='height:27.95pt;border-top:none;
width:148pt' x:num><%=result.getCell(0,r)%></td>
<td class=xl24 width=245 style='border-top:none;border-left:none;width:184pt'
x:num><%=result.getCell(1,r)%></td>
<td class=xl25 width=117 style='border-top:none;border-left:none;width:88pt'
x:num><%=result.getCell(2,r)%></td>
<td class=xl24 width=101 style='border-top:none;border-left:none;width:76pt'
x:num><%=result.getCell(3,r)%></td>
<td class=xl25 width=165 style='border-top:none;border-left:none;width:124pt'
x:num><%=result.getCell(4,r)%></td>
<td class=xl30 width=125 style='border-top:none;border-left:none;width:94pt'
x:num><%=result.getCell(5,r)%></td>
<td colspan=8 class=xl26 style='mso-ignore:colspan'></td>
</tr>
<% } %>
<% } %>

<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=197 style='width:148pt'></td>
<td width=245 style='width:184pt'></td>
<td width=117 style='width:88pt'></td>
<td width=101 style='width:76pt'></td>
<td width=165 style='width:124pt'></td>
<td width=125 style='width:94pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
<td width=72 style='width:54pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>
回复
heartofsword 2004-08-31
JSP如下,其中SQL语句在test bean中 Resultobj是一个结果集

<%@ page language="java" contentType="text/html;charset=ISO-8859-1"%>
<%response.setContentType("application/vnd.ms-excel");%>
<%@ page import="java.util.*" %>
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<jsp:useBean id="test" scope="page" class="*.*.test"></jsp:useBean>
<%@ page import="*.Resultobj"%>
String test = request.getParameter("test");
Resultobj result = test.test(test);
<head>
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=GB2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
<link rel=File-List href="./Book1.files/filelist.xml">
<link rel=Edit-Time-Data href="./Book1.files/editdata.mso">
<link rel=OLE-Object-Data href="./Book1.files/oledata.mso">
<head>
<meta http-equiv=Content-Type content="text/html; charset=GB2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
<link rel=File-List href="./gsdj.files/filelist.xml">
<link rel=Edit-Time-Data href="./gsdj.files/editdata.mso">
<link rel=OLE-Object-Data href="./gsdj.files/oledata.mso">
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>HP</o:Author>
<o:LastAuthor>xiaosl</o:LastAuthor>
<o:LastPrinted>2004-02-10T08:41:01Z</o:LastPrinted>
<o:Created>2003-12-27T05:27:45Z</o:Created>
<o:LastSaved>2004-02-10T08:51:43Z</o:LastSaved>
<o:Company>neusoft</o:Company>
<o:Version>9.2812</o:Version>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
<o:DownloadComponents/>
<o:LocationOfComponents HRef="file:\\192.168.71.166\tools\POffice2K_Premium\msowc.cab"/>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{mso-footer-data:"&R&10??&\0022Times New Roman\,????\0022 &N &\0022????\,????\0022??&\0022Times New Roman\,????\0022 &\0022????\,????\0022??&\0022Times New Roman\,????\0022 &P &\0022????\,????\0022??";
margin:1.18in .79in .79in .79in;
mso-header-margin:.51in;
mso-footer-margin:.51in;
mso-page-orientation:landscape;
mso-horizontal-page-align:center;}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:????;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:????;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:12.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:????;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
font-family:·???_GB2312, monospace;
mso-font-charset:134;
text-align:center;
vertical-align:middle;
border:.5pt solid windowtext;
white-space:normal;}
.xl25
{mso-style-parent:style0;
font-family:·???_GB2312, monospace;
mso-font-charset:134;
mso-number-format:"0_\)\;\\\(0\\\)";
text-align:center;
vertical-align:middle;
border:.5pt solid windowtext;
white-space:normal;}
.xl26
{mso-style-parent:style0;
font-family:·???_GB2312, monospace;
mso-font-charset:134;
text-align:center;
vertical-align:middle;
white-space:normal;}
.xl27
{mso-style-parent:style0;
font-family:·???_GB2312, monospace;
mso-font-charset:134;
text-align:center;
vertical-align:middle;
border:.5pt solid windowtext;
background:white;
mso-pattern:auto none;
white-space:normal;}
.xl28
{mso-style-parent:style0;
font-family:·???_GB2312, monospace;
mso-font-charset:134;
text-align:center;
vertical-align:middle;
border-top:1.0pt solid windowtext;
border-right:none;
border-bottom:none;
border-left:none;
white-space:normal;}
.xl29
{mso-style-parent:style0;
font-size:22.0pt;
font-weight:700;
text-align:center;
vertical-align:middle;
white-space:normal;}
.xl30
{mso-style-parent:style0;
color:black;
font-family:·???_GB2312, monospace;
mso-font-charset:134;
mso-number-format:"Long Date";
text-align:center;
vertical-align:middle;
border:.5pt solid windowtext;
background:white;
mso-pattern:auto none;
white-space:normal;}
ruby
{ruby-align:left;}
rt
{color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:????;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-char-type:none;
display:none;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>111</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>559</x:DefaultRowHeight>
<x:Unsynced/>
<x:Print>
<x:ValidPrinterInfo/>
<x:PaperSizeIndex>9</x:PaperSizeIndex>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>600</x:VerticalResolution>
</x:Print>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveRow>2</x:ActiveRow>
<x:ActiveCol>3</x:ActiveCol>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>6555</x:WindowHeight>
<x:WindowWidth>12000</x:WindowWidth>
<x:WindowTopX>0</x:WindowTopX>
<x:WindowTopY>1440</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
<x:ExcelName>
<x:Name>Print_Titles</x:Name>
<x:SheetIndex>1</x:SheetIndex>
<x:Formula>=name</x:Formula>
</x:ExcelName>
</xml><![endif]-->
</head>

回复
heartofsword 2004-08-31
先用excel做出个模板另存成WEB页格式
再将HTML代码复制到JSP中
回复
newleave840617 2004-08-31
是啊,我需要把数据库中的数据通过程序生成一个xls文件。
是不是poi有这个功能啊!
回复
LittleLandlord 2004-08-31
@_@创建xls文件?
回复
newleave840617 2004-08-31
对数据库的操作当然不需要了^_^。我需要生成xls文件。
回复
allenzz 2004-08-31
可以用JDBC将数据读出,然后用APACHE的POI将数据库写入EXCEL文件。

http://jakarta.apache.org/poi/hssf/index.html
回复
UnknowREN 2004-08-31
sorry it is my mistake

i pay no attention on using program
回复
UnknowREN 2004-08-31
不用程序在excel中有个获取外部数据的
你把数据源配置一下,就能够直接导入了
回复
LittleLandlord 2004-08-31
我晕,你是写程序的吗?已经跟你说的够明确的了,连接上2个数据库后,查询、删除、修改数据库还要?从Oracle查询的数据直接写入Excel就行了。
回复
newleave840617 2004-08-31
能否给出一段完整的从数据库导入excel的例子啊,谢谢!
回复
LittleLandlord 2004-08-31
private String url = "jdbc:odbc:driver={Driver do Microsoft Excel(*.xls)};DBQ=D:\\landlord\\Mydoc\\commbook.xls";
回复
相关推荐
发帖
Web 开发
创建于2007-09-28

8.0w+

社区成员

Java Web 开发
申请成为版主
帖子事件
创建了帖子
2004-08-31 11:04
社区公告
暂无公告