在服务器中上传Excel数据 [英] Excel data upload in server

问题描述

您好!!!我想从Excel获取数据并保存到数据库中。问题是，如果任何列中的excel数据单元格在由换行符分隔的单个单元格中包含两个或更多值（''\ n''）。然后我必须生成主键并分别分离值并将值保存在数据库中。



Excel数据这样

<前lang =vb>部门员工
IT A
B
C
D
账户
HouseKeeping E

管理员F

必须是这样的：

部门员工
 IT A 
 IT B 
 IT C 
 IT D 
账户E 
 HouseKeeping E 
管理员F 

注意：没有主键，我们必须在运行时生成主键！



请帮帮我...

先谢谢！ !!!!

解决方案

没有简单的方法可以实现这一点，特别是当两列都包含新行的符号时： \ n 或 CHR（10）。



1.方式是使用VBA

但是你不能在ASP.NET中使用VBA。



2.方法是使用存储过程 [ ^ 使用 CTE 在sql server端进行[] ^ ]

查看示例查询：

  DECLARE   @ names   AS   TABLE （aName  NVARCHAR （ 50 ），belongsTo  NVARCHAR （ 30 ））
 
  INSERT   INTO   @ names （aName，belongsTo）
  SELECT  * 
  FROM （
  SELECT 部门 AS  aName，' 部门'  AS  belongsTo 
  FROM   OPENROWSET （'  Microsoft。 Jet.OLEDB.4.0'，'  Excel 8.0; Database = F：\NewLineInCell.xls'，'  SELECT * FROM [Sheet1 

' ）
WHERE NOT 部门 IS NULL
UNION < span class =code-keyword> ALL
SELECT 员工 AS aName ，' 员工 AS belongsTo
FROM OPENROWSET （' Microsoft.Jet.OLEDB.4.0'，' Excel 8.0;数据库= F：\NewLineInCell.xls'，' SELECT * FROM [Sheet1

'）
WHERE NOT 员工 IS NULL ） AS DT

; WITH CteNames AS
（
SELECT LEFT （aName，CHARINDEX（ CHAR （ 10 ），aName）-1） AS tmpName， RIGHT （aName，LEN（aName）-CHARINDEX（ CHAR （ 10 ），aName） ） AS 剩余，belongsTo
FROM @ names
WHERE CHARINDEX（ CHAR （ 10 ），aName）> 0
UNION 所有
SELECT LEFT （剩余，CHARINDEX（ CHAR （ 10 ），剩余）-1） AS tmpName，< span class =code-keyword> RIGHT （剩余，LEN（剩余）-CHARINDEX（ CHAR （ 10 ），剩余）） AS 剩余，belongsTo
FROM CteNames
WHERE CHARINDEX（ CHAR （ 10 ），剩余）> 0
UNION 所有
SELECT 剩余作为 tmpName， NULL AS 提醒，belongsTo
FROM CteNames
WHERE CHARINDEX（ CHAR （ 10 ），剩余）= 0 - - AND Remainder IS NULL
）
SELECT DISTINCT tmpName，belongsTo
FROM CteNames
UNION ALL
SELECT DISTINCT aName AS tmpName，belongsTo
FROM @ names
WHERE CHARINDEX（ CHAR （ 10 ），aName）= 0
ORDER BY belongsTo，tmpName

以上查询返回：

 tmpName belongsTo 
 ------------------------- 
会计部门
管理部门
 HouseKeeping部门
 IT部门
 A员工
 B员工
 C员工
 D员工
 E员工
 F员工

所有你现在需要做的，是定义一种将数据插入表格的方法。



BTW：我在单元格中添加了新行，使用了键组合： left ALT +输入。

Hi!!! i want to get data from Excel and save into the database. The problem is, if the excel data cell in any of the column, contains two or more values in a single cell splitted by newline(''\n''). Then i have to generate the primary key and separate the values respectively and save the values in the database.

Excel Data Like this

Department     Employees
IT              A
                B
                C
                D
Accounts
HouseKeeping    E

Admin           F 

Need to be like this:

Department  Employees
IT            A
IT            B
IT            C
IT            D
Accounts      E
HouseKeeping  E
Admin         F

Note: There is no primarykey and we have to generate primary key at runtime!

please help me...
Thanks in Advance!!!!!

解决方案

There is no simply way to achieve that, especially when both columns contain signs of new line: \n or CHR(10).

1. way is to use VBA
But you can''t use VBA with ASP.NET.

2. way is to use stored procedure[^] on sql server side with CTE[^]
Have a look at example query:

DECLARE @names AS TABLE (aName NVARCHAR(50), belongsTo NVARCHAR(30))

INSERT INTO @names (aName, belongsTo)
SELECT * 
FROM (
	SELECT Department AS aName, 'Department' AS belongsTo
	FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=F:\NewLineInCell.xls', 'SELECT * FROM [Sheet1

') WHERE NOT Department IS NULL UNION ALL SELECT Employees AS aName, 'Employees' AS belongsTo FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=F:\NewLineInCell.xls', 'SELECT * FROM [Sheet1

') WHERE NOT Employees IS NULL) AS DT ;WITH CteNames AS ( SELECT LEFT(aName, CHARINDEX(CHAR(10),aName) -1) AS tmpName, RIGHT(aName, LEN(aName)-CHARINDEX(CHAR(10),aName)) AS Remainder, belongsTo FROM @names WHERE CHARINDEX(CHAR(10),aName)>0 UNION ALL SELECT LEFT(Remainder, CHARINDEX(CHAR(10),Remainder) -1) AS tmpName, RIGHT(Remainder, LEN(Remainder)-CHARINDEX(CHAR(10),Remainder)) AS Remainder, belongsTo FROM CteNames WHERE CHARINDEX(CHAR(10),Remainder)>0 UNION ALL SELECT Remainder As tmpName, NULL AS Reminder, belongsTo FROM CteNames WHERE CHARINDEX(CHAR(10),Remainder)=0 --AND Remainder IS NULL ) SELECT DISTINCT tmpName, belongsTo FROM CteNames UNION ALL SELECT DISTINCT aName AS tmpName, belongsTo FROM @names WHERE CHARINDEX(CHAR(10),aName)=0 ORDER BY belongsTo, tmpName

Above query returns:

tmpName         belongsTo
-------------------------
Accounts	Department
Admin		Department
HouseKeeping	Department
IT		Department
A		Employees
B		Employees
C		Employees
D		Employees
E		Employees
F		Employees

All what you need to do now, is to define a way to insert data into your tables.

BTW: i have added new line in cell, using combination of keys: left ALT + Enter.

这篇关于在服务器中上传Excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！