从asp.net读取Excel工作表数据 [英] Reading Excel sheet data from asp.net
问题描述
我有一个包含以下记录的Excel工作表，这计划进行72个月，这意味着Excel工作表包含74列，包括前两列.
PROD_CODE PLANT_CODE 201107 201108 201109 201110 201111 201112 201201
1222 INAA 34 44 45 44 34 454 4455
1425 INAC 67 788 787 78 887 888 6767
我的SQL Server表包含4列，如下所示:
1-PROD_CODE
2-PLANT_CODE
3-YEAR_MONTH
4-预测
如何读取存储在SQL Server中的Excel工作表，如下所示:
PROD_CODE PLANT_CODE YEAR_MONTH FORECAST
1222 INAA 201107 34
1222 INAA 201108 44
请有人尽快帮助.
I have an Excel sheet which contains records as follows, this is planning for 72 months, this means that the Excel sheet contains 74 columns including the first two columns.
PROD_CODE PLANT_CODE 201107 201108 201109 201110 201111 201112 201201
1222 INAA 34 44 45 44 34 454 4455
1425 INAC 67 788 787 78 887 888 6767
My SQL Server table contains 4 columns as follows:
1-PROD_CODE
2-PLANT_CODE
3-YEAR_MONTH
4-FORECAST
How can I read the Excel sheet in store into the SQL Server as follows:
PROD_CODE PLANT_CODE YEAR_MONTH FORECAST
1222 INAA 201107 34
1222 INAA 201108 44
Please someone help asap.
推荐答案
您可以使用OpenRowset函数打开excel并选择其内容，就像从表中选择一样.
You can use the OpenRowset function to open the excel and select its content as if you were selecting from a table.
INSERT INTO YourTable (Col1, Col2, ........)
SELECT Col1, Col2, ......
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\YourExcel.xls', 'select * from [Sheet1
')
这篇关于从asp.net读取Excel工作表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
