How To Open and Read an Excel Spreadsheet into a ListView in .NET

Mike Gold

6y698.1k42100Article

C#.NET Windows Forms ListView Excel FileExcel SpreadsheetAdding an Excel Reference

42 Comments

Join the conversation! Your thoughts help the community grow.

Mohamed Elqassas9y ago
Good work..............
kalu singh rao10y ago
Nice...
Sharad11y ago
good one...
Anton Bithink13y ago
thanks mike
srilekha15y ago
hi all, I want to upload a excel file and i want to read the excel file details using c#.net can any one say me that i how should i write the code for this.
- knights15y ago
  I think this is not the right place to put your question!
reena kapoor16y ago
how could we do the same thing in vb.net
tee pee17y ago
Thanks Mike
sachu ss17y ago
Can anyone here please suggest how to implement the same using asp .net application? Thanks in advance....
Anitha Joseph18y ago
I tried your code .I'm getting an exception :"Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154. " Can i do this without installing excel on my system?..Is there any way of doing this without installing excel ??
steven staley18y agoedited
I am trying to use the code above to read a xls file. I had to modify the code a bit to get it to work on my computer, like putting Microsoft.Office.Interop before every Excell reference. I also had to use Microsoft.Excell.12 as the reference instead of 9. I dont know if that makes any kind of difference. My problem is that for some reason, the file is not being recognized. I did not use the OpenFileDialog class, as I know the single file I want to open, so instead I just put the file name I wanted to open as the 1st parameter in the OPEN function. Those are the only major differences between what I have and what was posted above. What comes up is a prompt asking me to check the spelling of the file and then asking if I want to quit or continue, quit being closing the program out, and continue being continue on from where I was. The data never shows up or it never finds the file for some reason. I made sure that the file was named correctly and even tried placing it in different places on my computer. I am not dealing with a database at all, just need to read a single excell file. Can someone please help. Thanks.
Seed18y ago
Doesn't this solution require that MS Office be installed on the server? thx, seed
- David Fullerton18y ago
  If I wanted to run the app on a server does the server have to have excel or office installed?
ravi naik18y ago
hi, i have read from excel cell by cell as the excell file is not formatted . then i want to insert the values into sql server2005. please do f\provide some code i am struck with it.. Thanks SAKEE
ravi naik18y ago
hi, i have read from excel cell by cell as the excell file is not formatted . then i want to insert the values into sql server2005. please do f\provide some code i am struck with it.. Thanks SAKEE
Filo Walasky18y ago
Hi, Nice article, very helpful. Now: How can I take the data in the datagrid and display it in a new spreadsheet. Please help!
harsh kapoor18y ago
plz write the code for transfer the data of one excel sheet into the another excel sheet. plz write the runnable code and way to run the code.
Hemant Bais18y ago
i opend the exel file by following code. Dim xlTmp As Excel.Application xlTmp = New Excel.Application Dim wb As Excel.Workbook wb = xlTmp.Workbooks.Open("C:\UnitTest files for import\MapTest.xls") xlTmp.Visible = True 'xlTmp.ActiveWorkbook.ReadOnly wb.Activate() But it not let me save the file by same name
Scott18y ago
This is wonderful, but, I need to insert data into cells. How is this done?
Filo Walasky18y ago
How would I make the listView to not only show the first column but the whole document's data. It currently shows only the first column and it lists the data vertically. I want the listview to show the data exactly the same way you did it in this example.
Filo Walasky18y ago
How would I make the listView to not only show the first column but the whole document's data. It currently shows only the first column and it lists the data vertically. I want the listview to show the data exactly the same way you did it in this example.
Mukesh Wadhwa19y ago
Hi, Can any body help me for writing the code for merging cells. Thanks Mukesh
Mukesh Wadhwa19y ago
Hello sir, This is mukesh here. i tried ur example.its working fine.Thanks,but i have a question in this,if i use the same file name in Save as, it shows a dialog "File already exist...overwrite". I need not to show such dialog box when SaveAs metod of workbook invoked.it should overwrite the file. plz help me and reply on [email protected] Thanks & Regards Mukesh
- karthic mohandhoss18y ago
  // Needs for the Excel Microsoft.Office.Interop.Excel.Application m_objExcel = null; Microsoft.Office.Interop.Excel.Workbooks m_objBooks = null; Microsoft.Office.Interop.Excel._Workbook m_objBook = null; Microsoft.Office.Interop.Excel.Sheets m_objSheets = null; Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null; Microsoft.Office.Interop.Excel.Range m_objRange = null; object m_objOpt = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application(); ExcelObj.Visible = false; // Start a new workbook in Excel. m_objExcel = new Microsoft.Office.Interop.Excel.Application(); m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Microsoft.Office.Interop.Excel._Workbook)(m_objBooks.Add(m_objOpt)); /// Add data to cells of the first worksheet in the new workbook. m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1)); object[] objHeaders = { "ID", "KEY", "DESCRIPTION" }; m_objRange = m_objSheet.get_Range("A1", "C1"); m_objRange.set_Value(m_objOpt, objHeaders); m_objFont = m_objRange.Font; m_objFont.Bold = true; int size = msgObjs.Count; object[,] objData = new Object[size + 2, 3]; int loopcount = 0; //here write the data coding as u like foreach (MsgDes mobj in msgObjs) { objData[loopcount, 0] = mobj.Id; if (mobj.Key != null) objData[loopcount, 1] = mobj.Key; if (mobj.Description != null) objData[loopcount, 2] = mobj.Description; loopcount = loopcount + 1; } m_objRange = m_objSheet.get_Range("A2", Missing.Value); m_objRange = m_objRange.get_Resize(size + 2, 3); m_objRange.set_Value(m_objOpt, objData); // Save the workbook and quit Excel. string path = //get path from the user //if file exists delete the file m_objBook.SaveAs(path + "test.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objBook.Close(false, m_objOpt, m_objOpt); m_objExcel.Quit(); ExcelObj.Quit(); isSuccess = true;
Kamrul Ahsan19y ago
suppose that i have list view. I want to get the datas in an exel file. how can i do.
Raja Nagarajan19y ago
I want to create one more worksheet in Excel from .Net... CAn Anyone help me how to do that?
MIni Pillai19y agoedited
WhenI use this code I am getting an error from my remote client server. IN local server creates no peroblem. then errror I am getting is "Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005. " Can you please help me to solve this problem? My email Id is [email protected]
- karthic mohandhoss18y ago
  http://support.microsoft.com/kb/306023 .if u have doubt i am online at [email protected]
Florian Patzl19y agoedited
Hi, good article. But i'd need to get the comments to the extracted cells, too. Any ideas how to include this in this example? I found other articles about deleting & adding comments, but how to get the comment text into my C# programme? Thanks in advance! [edit] Comment.Shape.AlternativeText returns a string with the comment text.
Indrasis Acharya19y agoedited
I am trying to open a form from a customised menu bar I have created by C#.net. The form is opning but stops responding and the controls over it are not visible. private void MenuItem_Click(Office.CommandBarButton Ctrl, ref Boolean cancelDeafult ) { try { Form2 frm2 = new Form2(); frm2.Show(); } catch (Exception e) { MessageBox.Show(e.Message, e.Source); } Searching for solution.
manu sharma19y ago
Excel.Application is not detected . i am getting error Error 1 The type or namespace name 'Excel' could not be found . i have installed excel 2003 , i have selected MS excel 11.0 in the COM tab
- arghad19y agoedited
  Ok .. here is a working version from the code: using ExcelInterop = Microsoft.Office.Interop.Excel; ExcelInterop.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); ExcelInterop.WorkbookClass workBook = (ExcelInterop.WorkbookClass)excel.Workbooks.Open(Path, 0, true, 5, "", "", true, ExcelInterop.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); ExcelInterop.Sheets sheets = workBook.Worksheets; ExcelInterop.Worksheet worksheet = (ExcelInterop.Worksheet)sheets.get_Item(1); ExcelInterop.Range range = worksheet.get_Range("A1", "A300"); System.Array myvalues = (System.Array)range.Cells.Value2; string[] strArray = ConvertToStringArray(myvalues); for (int i = 1; i < strArray.Length; i++) { Console.WriteLine(strArray[i]); } and of course the method: string[] ConvertToStringArray(System.Array values) { // create a new string array string[] theArray = new string[values.Length]; // loop through the 2-D System.Array and populate the 1-D String Array for (int i = 1; i <= values.Length; i++) { if (values.GetValue(i, 1) == null) continue; theArray[i-1] = (string)values.GetValue(i, 1).ToString(); } return theArray; } Hope this help ;-) Arghad
- Danny Garduce garduce19y ago
  Try to include this on the top of your code using Microsoft.Office;
- Victor Fernandez19y ago
  Hai... Its working Fine.But it should read the First column values only.if I change A1 object and A300 object to 'C' or 'D' it reads the 3 0r 4 th column respectivily.Why it is so...Pls give the soln to read the Excel file full column. Thnx. Victor
Jacobs19y ago
The Interop.Office.dll is not stored in my bin directory. And my code to generate an Excel is also not working. What did i forgot to do/install? Can anaybody help me please?? Thx!
- Mahesh Chand19y ago
  Do you have office installed on your machine? Have you added reference to Excel 9.0 Lib?
Amita Saini19y ago
This artical is very helpful to me to develope an web- application .net which is related to upload a excel file but now i want to store it's contents in SQL server2000 database which create tables dynamically , thanks for ur help
- SangDV SangDV19y ago
  #region Hàm Insert vào CSDL (chua thành công) private void button4_Click(object sender, EventArgs e) { { if (dt != null) // dt là 1 DataTable dùng d? hi?n th? ra DataGrid { int numrows = dt.Rows.Count; // s? dòng trong DataTable for (int i = 0; i < numrows; i++) { try { #region Ð?y d? li?u vào Paramater SqlParameter[] prm = new SqlParameter[5]; prm[0] = new SqlParameter("@Num1", SqlDbType.NVarChar); prm[0].Value = dt.Rows[i][0].ToString(); prm[1] = new SqlParameter("@Num2", SqlDbType.NVarChar); prm[1].Value = dt.Rows[i][1].ToString(); prm[2] = new SqlParameter("@Num3", SqlDbType.NVarChar); prm[2].Value = dt.Rows[i][2].ToString(); prm[3] = new SqlParameter("@Num4", SqlDbType.NVarChar); prm[3].Value = dt.Rows[i][3].ToString(); prm[4] = new SqlParameter("@Num5", SqlDbType.NVarChar); prm[4].Value = dt.Rows[i][4].ToString(); string strSql = "SP_Test"; DataSet ds = Util.GetDataSet(strSql, prm); #endregion } catch { MessageBox.Show("Không thành công!"); } } } MessageBox.Show("Thành công!"); } } #endregion
- ravi naik18y ago
  Hi i want to read data from excel file ,using C#.net 2005 and storing it in sql server 2005. i cant use sqlbulkcopy as my excel file is not formatted. i have to read cell by cell.. and store it in database. please do provide some code Thanks Sakee
- ravi naik18y ago
  Hi ,i want to read excel file from c# and then store it into sql server 2005. i cant use bulkcopy as excel file is not formated. i have to read cell by cell . please gice some code and and i am new to c#.. Thanks Sakeee
vivek pawar20y ago
hi, i opened excel file in my c# application in readonly mode with open(.......) method and visible = false; and after that if open same excel file seperately in window my application throws exception
Prabhu BN20y ago
How to delete first Row from an Excel sheet using C#.net. For my project its very important. I am New for DotNet. So i don't know what to do. Please Help. Please mail me: [email protected]

Introduction

The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. You can also refer to my previous article on the topic: Real-time Stock Quotes in Excel using .NET for more information on accessing Excel via .NET. This article was written in response to a question asking How do I open an excel file and read it using .NET?

Figure 1 - Excel Spreadsheet read into a ListView

The first step is to create a reference in your project to Excel 9.0 Objects Library. This is done by right mouse clicking on the References folder in the Solution Explorer and choose Add Reference. This brings up the Tab Dialog below. Choose the COM Tab and pick Microsoft Excel 9.0 Objects Library.

Figure 2 - Adding an Excel Reference

This action puts an Interop.Excel.dll and Interop.Office.dll into your bin directory so you can manipulate excel.

Now we can declare our Excel Application Object and the compiler will recognize it:

private Excel.Application ExcelObj = null;

Excel is launched and an Application reference is obtained in the constructor of our form. First an Excel Application object is constructed. Then we check to make sure Excel was actually started. If it was, we have a valid application object and we can now use it to open a file:

public Form1()
{
// Initialize the Windows Components
InitializeComponent();
ExcelObj = new Excel.Application();
// See if the Excel Application Object was successfully constructed
if (ExcelObj == null)
{
MessageBox.Show("ERROR: EXCEL couldn't be started!");
System.Windows.Forms.Application.Exit();
}
// Make the Application Visible
ExcelObj.Visible = true;
}

The code for opening the Excel file is shown below. The code uses the OpenFileDialog component to get the path name for the Excel file. The Excel file is opened using the WorkBooks collections' Open method. This method takes 15 parameters with the following definition.

Function Open(Filename As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMenuRecentlyUsed]) As Workbook

We really are only interested in the FileName, but have added the other default parameters for your reference. There is also an OpenText method in Workbooks for opening tab or comma delimited text files.