Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post May 29, 2010 06:57 PM by murali0808
Dec 11, 2007 05:41 PM|LINK
Is there a way to import data in an excel sheet and have this data populate an asp input form (i.e. - textboxes, etc...)? I am NOT interested in importing excel data directly into a sql database, the reason being I want the user to verify the data before
it goes into the database.
Presently, the user populates an existing worksheet then inputs this data into an asp form. These steps are redunant but for tracking purposes, but I can not get rid of using excel. Any guidance on this issue would be appreciated.
Dec 11, 2007 05:59 PM|LINK
You can use a datareader to read the Excel values in, no different than if the values come from a database. you can then populate your form controls while reading through the datareader.
Dec 13, 2007 05:28 AM|LINK
Based on my understanding, you want to get data from Excel files directly and display the data on the web page. If I have misunderstood you, please feel free to let me know.
To achieve this, you can use OLE flexiblly. On the other hand, you can also use Microsoft.Office.Interop.Excel. There is a sample as below you can try.
public DataSet GetExcel(string fileName)
// creat a Application object
oXL = new ApplicationClass();
// get WorkBook object
oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// get WorkSheet object
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets;
System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
DataSet ds = new DataSet();
StringBuilder sb = new StringBuilder();
int jValue = oSheet.UsedRange.Cells.Columns.Count;
int iValue = oSheet.UsedRange.Cells.Rows.Count;
// get data columns
for (int j = 1; j <= jValue; j++)
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
//string colString = sb.ToString().Trim();
//string colArray = colString.Split(':');
// get data in cell
for (int i = 1; i <= iValue; i++)
dr = ds.Tables["dtExcel"].NewRow();
for (int j = 1; j <= jValue; j++)
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
string strValue = oRng.Text.ToString();
dr["column" + j] = strValue;
catch (Exception ex)
Label1.Text = "Error: ";
Label1.Text += ex.Message.ToString();
You can call this method like this:
DataSet ds = GetExcel("c:\\abcd.xls");
GridView1.DataSource = ds;
Dec 13, 2007 05:52 AM|LINK
You can read data from Excel in the same way as you do from the database like sql server using ODBC. Go thru this link on knowing how to use ODBC
You will be able to use all the normal stuff like the datasets and datareaders and get the data from the excel file.
Dec 17, 2007 09:35 PM|LINK
Thank you for your reply, I took your advise and I am using a datareader to find my values and populate my controls. I have an excel sheet (c"\asp.xls") with a Range named "First". The named range only contains one cell value, I am able to get this value
and popluate my Textbox "Textbox1" as shown below.
Here is my question. Since I want to find the values of multiple ranges within this worksheet -named "Sheet1", how can I accomplish this?
Ideally, I want to select the entire the sheet then tie mulitple textboxes to several ranges within the same worksheet. I have tried setting: SQLString= "Select FROM [First]" but I am stuck at setting the textbox.text to the ranges? Any help would be appreciated.
1 <%@ Page Language="VB" %>
2 <%@ Import Namespace= "System.Data.OleDb" %>
4 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6 <script runat="server">
7 Dim DbConnection As OleDbConnection
8 Dim DbCommand As OleDbCommand
9 Dim DbReader As OleDbDataReader
10 Dim SQLString As String
12 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
13 DbConnection = New OleDbConnection( _
14 "Provider= Microsoft.Jet.OleDb.4.0;" & _
15 "Data Source = c:\asp.xls;" & _
16 "Extended Properties='Excel 8.0;HDR=NO;IMEX=1'")
20 SQLString = "SELECT * FROM [First]"
22 DbCommand = New OleDbCommand(SQLString, DbConnection)
24 DbReader = DbCommand.ExecuteReader()
28 TextBox1.Text = DbReader.Item(0).ToString
34 End Sub
37 <html xmlns="http://www.w3.org/1999/xhtml" >
38 <head runat="server">
39 <title>Untitled Page</title>
42 <form id="form1" runat="server">
44 <asp:TextBox ID="TextBox1" runat="server" Style="z-index:
100; left: 23px; position: absolute;
45 top: 47px"></asp:TextBox>
Dec 18, 2007 12:30 AM|LINK
If you want to get the data from Sheet of Excel, you can try my codes provided above. It can define the Sheet Object which you can get the data by.
Hope this can help.
Dec 18, 2007 03:03 PM|LINK
Newbie Questions: Using the Interop method you mentioned earlier and I download the Interop.Excel.dll file:
1. Will I need to do upload the dll file to the server when this application goes live?
2. If so, how?
3. If this file is uploaded to the server, will the end user need this .dll file?
Dec 19, 2007 12:23 AM|LINK
Q1: Yes, but you needn't download this dll file which is in the Visual Stadio. You can add it as reference.
Q2: There are several steps as below.
1. In Visual Stadio, right-click your web site in the Solution Explorer.
2. Choose "Add Reference".
3. In ".NET" tab, please choose "Microsoft.Office.Interop.Excel". And click "OK".
4. Please add "using Microsoft.Office.Interop.Excel;" into your page using Excel Object.
After that, you can achieve using Excel Object and client needn't download this dll file which is just on the server.
Q3: The end users needn't the dll file which can be added in your website.
Hope this help you.
Dec 19, 2007 03:04 PM|LINK
Thank you very much for sharing your knowlege on this issue.
I attempted to add the reference in visual Web Developer 2005 but under the .NET tab I did not see "Microsoft.Office.Interop.Excel" listing. I went into the COM Tab and added the following items (I am not sure if this helps?):
Micrsoft Office 11.0 Object Library
Microsoft Excel 8.0 Object Library
After this process, I attempted to import the namespace but the only option I have is
I think it has something to do with the fact I am using an older version of Excel (97 SR-2)?
Is there a web reference I could use? or other option?
Thank you again.
Dec 20, 2007 01:05 AM|LINK
If you add Microsoft Excel 8.0 Object Library, it will add Office.Core.dll and
Office.Interop.Excel.dll as references. If the Office.Interop.Excel.dll is not added,
as far as I know, it's associated with the Microsoft Office edition you installed.
You have to install Office Excel 2003 or above.
To download Office.Interop.Excel.dll, you can check the below:
Download URL: http://www.dll4you.com/download/get.php?file=microsoft.office.interop.excel.dll&site=13&ver=11.0.5530&com=Microsoft%20Corporation&des=Microsoft.Office.Interop.Excel
It's not free.(I'm sorry I haven't found free link.)