Need to save the data in excel which is exported from a datacontrol in asp.net page

Posted by Lalitha under ASP.NET on 10/14/2009 | Views : 3062 | Status : [Member] | Replies : 5
hi
i am facing the problem with this
my question is:
I have taken a datacontrol( gridview ) in asp.net page and exported that grid data to excel.
Excel is generated. Now if i modify the data in that excel and click on save, the modified data should reflect in the same asp.net datacontrol which i have exported to excel.
Please help me




Responses

Posted by: Vuyiswamb on: 10/14/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Lalitha

That is impossible. You cant export to Excel and modify that File outsite your application and expect the Grid to reflect the changes. They are not linked in any way. The Only thing that you need to to is to import the excell file again back into the Grid and then you will see your changes.

Thank you for posting at dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Lalitha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lalitha on: 10/14/2009 [Member] Starter

Up
0
Down
thank you very much for sending response to my question.
Can u give me code that how to import from excel

Lalitha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 10/14/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
look at this



SQL SERVER - Import CSV File Into SQL Server Using Bulk Insert - Load Comma Delimited File Into SQL Server

This is very common request recently - How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable


USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO


Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202


Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK

INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO



or copy this and paste it in the Browser ,

http://www.aspsnippets.com/post/2009/06/06/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASPNet.aspx



Thank you for posting at dotnetFunda

Vuyiswa Maseko



Thank you for posting at Dotnetfunda
[Administrator]

Lalitha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lalitha on: 10/15/2009 [Member] Starter

Up
0
Down
hi
i am sorry to disturb u again
i am unable to open the links which u have send me.
Actually in my offices some sites areblocked.
so can u paste the code

Lalitha, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 10/15/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Read and Import Excel Sheet into SQL Server Database in ASP.Net
Author:Mudassar Khan

In one of my previous articles I explained Read and Import Excel Sheet into ASP.Net GridView Control

Here I am explaining how to import Excel Sheet Rows into SQL Server Database table using ADO.Net in an ASP.Net Web Application.



Concept

1. User uploads the Excel File.

2. Based on the extension it is decided whether it is Excel 97 - 2003 or Excel 2007 format.

3. User can select whether the Excel Sheet has header row or not using the Radio Buttons

4. The Excel file is uploaded and then sheets in the Excel workbook are read into a DropDownList.

5. User has to now select the Sheet from the dropdown whose data he wants to import.

6. User has to enter the name of the table which he wants the data to be imported.

7. User presses OK Button and the data is imported into the SQL Server Database table and the user is updated with the status.



Stored Procedures

For this article I have created two stored procedures one to read the Excel 97 - 2003 format and other Excel 2007 format. Though the Microsoft Ace Driver can read both still I have used Jet for Excel 97 - 2003 formats.








Excel 97 - 2003 Format



SET ANSI_NULLS ON


GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE spx_ImportFromExcel03

@SheetName varchar(20),

@FilePath varchar(100),

@HDR varchar(3),

@TableName varchar(50)

AS

BEGIN

DECLARE @SQL nvarchar(1000)



IF OBJECT_ID (@TableName,'U') IS NOT NULL

SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'

ELSE

SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'



SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Data Source='

SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 8.0;HDR='

SET @SQL = @SQL + @HDR + ''''''')...['

SET @SQL = @SQL + @SheetName + ']'

EXEC sp_executesql @SQL

END

GO



Excel 2007 Format

SET ANSI_NULLS ON


GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE spx_ImportFromExcel07

@SheetName varchar(20),

@FilePath varchar(100),

@HDR varchar(3),

@TableName varchar(50)

AS

BEGIN

DECLARE @SQL nvarchar(1000)



IF OBJECT_ID (@TableName,'U') IS NOT NULL

SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'

ELSE

SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'



SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='

SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='

SET @SQL = @SQL + @HDR + ''''''')...['

SET @SQL = @SQL + @SheetName + ']'

EXEC sp_executesql @SQL

END

GO




In the above stored procedures, I have used four input parameters

1.@SheetName - Name of the Excel Sheet to be read.

2.@FilePath - Path of the Excel File

3.@HDR - Indicates whether first row in the excel sheet will be considered as Header row or not.

4.@TableName - The name of the table in which the Excel Sheet data will be transferred if the table is not present it will be created.



When you run the above stored procedure first time you might get the following error message.

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

In order to run the above stored procedure you will need to enable Surface Area Configuration in your SQL Server in the following way

Execute the below four statements one by one in the SQL Server Query Analyzer



sp_configure 'show advanced options', 1




reconfigure





sp_configure 'Ad Hoc Distributed Queries', 1






reconfigure





Also in order to use the Microsoft OLEDB Ace Driver you will need to install the 2007 Office System Driver: Data Connectivity Components which is necessary for Microsoft OLEDB ACE 12.0 driver to work using the link below

2007 Office System Driver: Data Connectivity Components

I faced the following Error while running the OLEDB Ace stored procedure

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 2


Cannot get the column information from OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".



I have written an article on the solutions for it. You can visit it using the link below

The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)"

Front End Design

Below is the markup of the HTML mark of the asp.net web page. There are 2 panels. First with an upload button, Asp.Net FileUpload control and a label to display the status used to upload the Excel File. Second one with label to display the uploaded Excel file name, DropDownList which contain the names of Sheets of the Excel workbook, an ASP.Net RadioButtonList to capture whether the Sheet has header row and finally two buttons one to import the Excel Sheet rows into the SQL Server Database table and other one to cancel.

The message is long, i could not put it all here. Please look the look in a open internet connection.

Thank you for posting at dotnetFunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Lalitha, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response