关于Excel数据导入和导出的问题

zh_zh_y 2004-07-24 04:37:37
目前我需要实现将数据库的数据导入导出的功能。我以前从没有用过excel，所以对excel操作不是非常清楚，下面提到的导入导出流程都是自己理解。

举例说明：
某个数据表，有大约20几个字段.字段的命名使用的是拼音缩写。

雇员编号 姓名 性别 所属科室 家庭地址 固定电话 移动电话 .........
GYBH XM XBM SSKS JTDZ GDDH YDDH .........省略号代表后面还有若干字段
0001 李四 0 0001 北京 010-****** *******

a.数据导入过程

数据导入流程简介：
1.首先自定义的excel文件的形式，然后提供下载，让用户下载自定义的excel文件；
2.用户下载后，根据实际情况填写相应字段内容，然后通过浏览器导入到数据库服务器中；


我不明白的地方：
1. 数据库表字段使用的拼音缩写，而客户端的excel文件的文件使用拼音缩写，会造成用户使用不便，应该使用中文表头，那么是不是需要字段之间的转换；也就是这个excel文件如何来自定义？导入后，怎样来转换？

2 数据导入时，如何将excel文件里的字段与数据库表中的字段对准，也就是说，能够向Sql 语言那样的方式
insert into table(a1,b1,c1) values('a1','b1','c1'),就是说插入时，能够对应的相应的字段，而不需要一一对应。
需不需要将所有字段顺序的导入？如何来导入了？

b.数据导出
数据导出流程简介：
1.用户先通过选择需要导出的字段；
2.然后通过数据库服务器导出成excel文件，让用户下载。

这又该如何操作？大家给我指点！！恳求那位大侠能够给我提供一个完整的例子.
...全文
480 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
pearl321 2004-07-28
  • 打赏
  • 举报
 回复
我現在想做一個這樣的功能:每個客戶端的使用者都可以自已把excel檔案的數據導入到資料庫,必須把excel文件放在服務器上才可以嗎?
dreamfisher1 2004-07-28
  • 打赏
  • 举报
 回复
服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。

上面这个错误是因为你的EXECL 文件被打开着,关掉那个EXCEL文件再试试.

我碰到的这个错误提示是因为这个原因.

推荐你看看如下的东东:
熟悉SQL SERVER 2000的数据库管理员都知道，其DTS可以进行数据的导入导出，其实，我们也可以使用Transact-SQL语句进行导入导出操作。在Transact-SQL语句中，我们主要使用OpenDataSource函数、OPENROWSET 函数，关于函数的详细说明，请参考SQL联机帮助。利用下述方法，可以十分容易地实现SQL SERVER、ACCESS、EXCEL数据转换，详细说明如下：

一、SQL SERVER 和ACCESS的数据导入导出
常规的数据导入导出：
使用DTS向导迁移你的Access数据到SQL Server，你可以使用这些步骤:
　　○1在SQL SERVER企业管理器中的Tools（工具）菜单上，选择Data Transformation
　　○2Services（数据转换服务），然后选择 czdImport Data（导入数据）。
　　○3在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source，然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。
　　○4在Choose a Destination（选择目标）对话框中，选择Microsoft OLE　DB Prov ider for SQL　Server，选择数据库服务器，然后单击必要的验证方式。
　　○5在Specify Table Copy（指定表格复制）或Query（查询）对话框中，单击Copy tables（复制表格）。
○6在Select Source Tables（选择源表格）对话框中，单击Select All（全部选定）。下一步，完成。

Transact-SQL语句进行导入导出：
1.在SQL SERVER里查询access数据:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名

2.将access导入SQL server
在SQL SERVER 里运行:
SELECT *
INTO newtable
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名

3.将SQL SERVER表里的数据插入到Access表中
在SQL SERVER 里运行：
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名
(列名1,列名2)
select 列名1,列名2 from sql表

实例：
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\db.mdb';'admin';'', Test)
select id,name from Test


INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)
SELECT *
FROM sqltablename

二、SQL SERVER 和EXCEL的数据导入导出

1、在SQL SERVER里查询Excel数据:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

2、将Excel的数据导入SQL server :
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

3、将SQL SERVER中查询到的数据导成一个Excel文件
T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式

实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

在VB6中应用ADO导出EXCEL文件代码：
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"


4、在SQL SERVER里往Excel插入数据:

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

T-SQL代码：
INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test')

总结：利用以上语句，我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！

参考：
http://www.itrain.de/knowhow/sql/transfer/adhoc/
zh_zh_y 2004-07-26
  • 打赏
  • 举报
 回复
以前我没有用过excel，不知道是不是这样建excel文件，我是这么理解的。
zh_zh_y 2004-07-26
  • 打赏
  • 举报
 回复
使用sa登录的。
就是一直报这样的错误，难道我excel表有问题？
A1 ,B1
这里设置为数据库字段
A2，B2及以后我保存需要输入的记录值
zjcxc 元老 2004-07-26
  • 打赏
  • 举报
 回复
权限呢? 用sa登录.

语句是没有问题的. 我一直都这样用.
zjcxc 元老 2004-07-26
  • 打赏
  • 举报
 回复
F:\TEST.xls 必须在SQL服务器上
sheet1 必须有sheet1这个工作表
fenglaotou 2004-07-26
  • 打赏
  • 举报
 回复
学习
zh_zh_y 2004-07-26
  • 打赏
  • 举报
 回复
insert into GY(GYBH,XM)
select A1,B1
from OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=F:\TEST.xls',sheet1$)

我在查询分析器中执行
报错了：

服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
zh_zh_y 2004-07-26
  • 打赏
  • 举报
 回复
INSERT INTO GY(GYBH,XM) --表的列名
SELECT A1,B1 --excel中的列名，可以跟数据库中的不同
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="F:\Test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

TEST.xls就在A1、B1定义了两个字段，GYBH，XM

我在查询分析器中执行
报错了：

服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
zh_zh_y 2004-07-26
  • 打赏
  • 举报
 回复
数据库表GY大约有15个字段，
第一 第二个为GYBH XM

'MICROSOFT.JET.OLEDB.4.0'是否需要象ODBC那样进行设置？建立数据源啊？

zh_zh_y 2004-07-26
  • 打赏
  • 举报
 回复
都是再一个服务器上，只有sheet1这个工作表
zh_zh_y 2004-07-25
  • 打赏
  • 举报
 回复
wanyingsong(豌豆) 兄，导入得存储过程我还没有看，我好好研究研究，但是导入我还是不甚明白。
数据导入时，如何将excel文件里的字段与数据库表中的字段对准，也就是说，能够向Sql 语言那样的方式 insert into table(a1,b1,c1) values('a1','b1','c1'),就是说插入时，能够对应的相应的字段，而不需要一一对应。
需不需要将所有字段顺序的导入？如何来导入了？

我现在要导入数据得表不需要将全部数据导入，只希望导入感兴趣得字段，所以我不明白怎么做？
老宛 2004-07-25
  • 打赏
  • 举报
 回复

从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:

/*===================================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
老宛 2004-07-25
  • 打赏
  • 举报
 回复
好好看看，邹老大的这个存储过程：

/*--数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10--*/

/*--调用示例

p_exporttb @sqlstr='select * from 地区资料'
,@path='c:\',@fname='aa.xls',@sheetname='地区资料'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO

create proc p_exporttb
@sqlstr sysname, --查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250), --文件名
@sheetname varchar(250)='' --要创建的工作表名,默认为文件名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')

--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES'
+';DATABASE='+@sql+'"'

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)

select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@sheetname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

set @sql='drop table ['+@tbname+']'
exec(@sql)
return

lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go
zh_zh_y 2004-07-25
  • 打赏
  • 举报
 回复
看来，我不应该将现在的理解方式也得太清楚了，本来是想告诉大侠们，我现在得理解，请大家讨论。
zh_zh_y 2004-07-25
  • 打赏
  • 举报
 回复
呵呵，我应用在asp.net上
lzymagi 2004-07-25
  • 打赏
  • 举报
 回复
导出数据可以直接用excel来做,导入就用企业管理器.简单..
zh_zh_y 2004-07-25
  • 打赏
  • 举报
 回复
上面得是不是敲错了
insert into 表(字段列表（select 要导入得字段列表 ？
zjcxc 元老 2004-07-25
  • 打赏
  • 举报
 回复
insert into 表(字段列表(
select 要导入的字段列表
from OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
yujohny 2004-07-24
  • 打赏
  • 举报
 回复
这么长，头晕，有时间再细看！
JAVA实现mysql与excel数据互导
不错，得到一些启发，后来在网络上又找了一些学习资源，自己做了个mysql与excel自动导入导出的普通JAVA工具类...在网上找了一些学习资源，自己照着做了一个mysql与excel数据互想导入导出的例子，希望对初学者有所帮助！
Java实现Excel导入和导出，看这一篇就够了(珍藏版)
最近抽了两天时间，把Java实现表格的相关操作进行了封装，本次封装是基于POI的二次开发，最终使用只需要调用一个工具类中的方法，就能满足业务中绝大部门的导入和导出需求。 环境准备 1. Maven 依赖 本次工具类的...
C# Excel数据导入和导出简单方案
C# Excel数据导入和导出简单方案 Excel的数据处理功能非常强大，因此在日常工作中，我们经常需要将...网上关于Excel数据导入和导出的方案比较多，比如使用Excel Interop，但是这种方式比较复杂而且要求运行环境安装Ex
用hutool对Excel数据导入与导出
然后我们想把Excel中的数据一起导入到数据库中在页面中显示。当我们将Excel文件以上面代码对发送post请求时。数据库添加的数据时，对于中文字段数据的...这样子Excel数据导入与导出就完成了。数据就添加到数据库中了。
Excel数据批量导入导出（基础版）
Excel批量导入导出
MS-SQL Server

34,576

社区成员

254,587

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章