Reading Excel From C#

Question

Hi

Can anyone help me how to read values in Excel in C# . So that Once I read I can send them to DataBase. My excel file Test.xls in ("C:\")

Thanks
Doss

Answer 1

You can read data from Excel using the JET OLEDB provider. See the URL below 
for the necessary connection string, and a link to a KB article with further 
information ...

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJetExcel

-- 
Brendan Reynolds

 wrote in message 
news:[e-mail]...
> Hi
>
> Can anyone help me how to read values in Excel in C# . So that Once I
> read I can send them to DataBase. My excel file Test.xls in ("C:\")
>
> Thanks
> Doss
> 

Answer 2

I would guess: open Excel from your program, and have Excel read the data.

Search for Visual Studio Tools for Office (VSTO) for the details.

Answer 3

His Doss, Create a reference in your project to Excel Objects Library.  The excel object library can be added in the COM tab of adding reference dialog. I hope the following code in your menu click event method will help you a lot to achieve your need.

  this.openFileDialog1.FileName = "*.xls";
  if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
   {
      Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
         openFileDialog1.FileName, 0, true, 5,
          "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
          0, true); 
     Excel.Sheets sheets = theWorkbook.Worksheets;
     Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
     for (int i = 1; i <= 10; i++)
     {
     Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
     System.Array myvalues = (System.Array)range.Cells.Value;
     string[] strArray = ConvertToStringArray(myvalues);
     }
}

Cheers,
Daya Anand, PSPIndia

Answer 4

If you are using a dynamically generated excel file then you can use the following:

Excel.Sheets sheets = m_Excel.Worksheets; Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1); System.Array myvalues; Excel.Range range = worksheet.get_Range("A1", "E1".ToString()); myvalues = (System.Array)range.Cells.Value;

Thanks

Answer 5

If you don't want to use the Excel COM objects, you can use OleDb. It takes a little setup in your Excel document. Basically, you need to define "named objects" in Excel that are synonymous to tables in a database. The first row of the named object are the column headers. To set up a named object, first select the range of cells (your "table," with the first row being the column headers), then go to menu Insert->Names->Define. Name your object and press "Add." Now you have an object which can be read by ADO.NET.

Now for the C# (this example assumes I have an Excel file at C:\Book1.xls and a named object in this workbook called "MyObject"):

using System.Data; using System.Data.OleDb; ... OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0"); OleDbDataAdapter da = new OleDbDataAdapter("select * from MyObject", con); DataTable dt = new DataTable(); da.Fill(dt);

You can use SQL to query the data in your named object.

Answer 6

When I try to do this, I keep getting "Old format or invalid type library"
I added "Microsoft Excel 10.0" as reference and use the following code

Excel.ApplicationClass excel = new Excel.ApplicationClass();

Excel.Workbook workbook = excl.Workbooks.Open(openFileDialog.FileName.toString(), null, null, null, null, null, null, null, null, null, null, null, null, null, null).
I also don't know what all these parameters should be.

Answer 7

I just started working on an application which performs this action. Feel free to use the following code.

 

//Start up excel to read the excel file in question

Microsoft.Office.Interop.Excel.Application ExcelObj = null;

ExcelObj = new Microsoft.Office.Interop.Excel.Application();

if (ExcelObj == null)

{

 MessageBox.Show("ERROR: EXCEL couldn't be started!");

 System.Windows.Forms.Application.Exit();

}

Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(openFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing);

Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

for(int x = 1; x <= 29; x++)

{

Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A"+x.ToString(), "I" + x.ToString());

System.Array myvalues = (System.Array)range.Cells.get_Value(range.);

string[] strArray = ConvertToStringArray(myvalues);

}

Sorry about the bad formatting, I don't know how to use this forum that well yet.

Answer 8

Hellow,

I'used these things a couple of months ago for an application I made. While trying to adapt the application now I cannot open the Excel file anymore. Does anybody hase any notice of changes in the Excel object?

public ExcelDB()
{
try
{
excel.Open(Application.StartupPath+"\\MAILING.XLS");
excel.Range();
string file = Application.StartupPath+"\\MAILING.XLS";
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+file);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=NO;IMEX=2");
sbConn.Append(Convert.ToChar(34));
cnExcel = new OleDbConnection(sbConn.ToString());
cmdExcel = new OleDbCommand("Select * From Clientlist",cnExcel);
}
catch
{
MessageBox.Show("Ernstige fout, klantenbestand niet gevonden!","Error 03",System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Stop);
}
}
public void FillList()
{
try
{
Cursor.Current = Cursors.WaitCursor;
cnExcel.Open();
drExcel = cmdExcel.ExecuteReader();
while(drExcel.Read())
{
if(drExcel["Name"].ToString() != "")
{
LogicControl.form.clientList.Items.Add(drExcel["Name"].ToString());
string[] listArray = {drExcel["Name"].ToString(),
drExcel["Address"].ToString(),
drExcel["City"].ToString(),
drExcel["Phone"].ToString(),
drExcel["Fax"].ToString(),
drExcel["VAT"].ToString()
};
lvi = new ListViewItem(listArray);
LogicControl.form.klantenList.Items.Add(lvi);
}
}
drExcel.Close();
cnExcel.Close();
Cursor.Current = Cursors.Arrow;
}
catch
{
excel.Quit();
MessageBox.Show("Ernstige fout, foutief klantenbestand!","Error 05",System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Stop);
}
}

Answer 9

Hello There

I'm Using the Same Way but i'm Facing a problem and i hope to find a solution here

objWorkbook = objExcel.Workbooks.Open(MapPath("\" & MyFilePath ))

objWorkbook.Unprotect()

objSheets = objWorkbook.Sheets()

objRange = objSheets.Application.Cells

and i tried to add a row using these tow lines once for the first and the second time i used the second one ..

  'objSheets.Application.Cells.Insert(Excel.XlDirection.xlDown, 1)

  'objRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown)

but a new EXCEPTION appear which says  

"To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet.

Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data.  Then select cell A1 and save your workbook to reset the last cell used.

Or, you can move the data to a new location and try again."

Any one Know how to solve this ?

Please note  i'm using a predisigned Excel "Formatted File " 

Answer 10

I have the same problem, and I'm not using code. I insert one line, and it does fine. When I insert a second line, I get the same message you listed in a modal box ("To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet."). If I save the file, I can then insert the additional line, but then I get the same message again when I try to insert another line. My sheet is large, but not that large; the last line is around 60k, leaving several thousand empty lines.

Looks like Excel is trying to be too smart, and messing up in the process. I have Excel 2003 (with all updates), so it's not something a newer version would fix. Maybe an older version will fix it...

There are no references to the message, that I could find, in Excel documentation or the KB.

Answer 11

Found a reference, worded slightly differently so it wasn't in the original search results. (http://support.microsoft.com/kb/305568)

You probably have formatting the entire length of a column/row, such as a cell border. If you go to the edge of the used area, select the rest of the sheet, and remove the border, you should eliminate the problem.

It's an instant problem caused by selecting a row/column and adding an edge border to it - after that point, you can't insert additional rows more that one at a time.

Microsoft - Please fix the problem. Excel sees this formatting as content, but it is not. (Excel even adds the border back if the row is deleted!)

Answer 12

Hi There,

               I also facing the same problem but it bit of different . I wanted to read the data in Excel Sheet  Eg: If Data is there  for columns A,B,C,D ..

wanted to read the data 'A' and 'B'  'A' and 'C'

after that I wanted to generate the Charts ..

 

any suggestions or solutions.

 

Thanks-Aparna

Answer 13

can any body give any solution for the above query please

Thanks-Aparna

Answer 14

"To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet.

Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data.  Then select cell A1 and save your workbook to reset the last cell used.

Or, you can move the data to a new location and try again."

This usually happens when Excel thinks that there is information in the very last row.  If you hit Ctrl+End, you may find that it shoots all the way down to row 65536 or something like that.  Clear the contents of that entire row or delete it altogether.  You may need to be persistent (this is an Office application, after all).  I had a user with this issue earlier today and I had to clear each cell one at a time and save the document before it would take effect.  Once excel stops believing that there is something in the last rows, you'll be able to insert rows wherever you need.

Answer 15

I ran into this same issue with VB.  Here's what worked for me (from here):

Dim mySelection As Excel.Range
mySelection = ThisApplication.Selection()
mySelection.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown)

Answer 16

Hi,

I have some what a strange requirement. Rather than reading the data from a sheet or a range in excel, i need to get the information regarding named range and based on that i need to filter the data further. For eg: if the range A1: C5 is mapped to a named range as "Amount",  i need to get the absolute range information which is A1:C5 by passing the range "Amount". Is there any way of achieving this programmatically? Currently I am using oledb for reading the data.

Thanks

Jency

Answer 17

It isnt that tough

 

Simply create an object of workbook and worksheet

Specifying the cells(rowindex, columnindex) you can easily bank out the values

Amrit

Answer 18

How can I reference columns in the excel spread sheet to use in an ORDER BY statement or in a WHERE clause?

The code below is what I'm using to  read from the Excel spreadsheet and it works fine. I just need to sort the spread sheet.  The spreadsheet doesn't have any headers

 

DataTable dtExcel = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

string SpreadSheetName = "[" + dtExcel.Rows[0]["TABLE_NAME"].ToString() + "]";

DataSet dsExcel = new DataSet();

objCommand.CommandText = @"SELECT * FROM " + SpreadSheetName;

Answer 19

The only problem here is that you're missing the ConvertToStringArray(myvalues); 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(1, i) == null)
    theArray[i-1] = "";
  else
   theArray[i-1] = (string)values.GetValue(1, i).ToString();
  }

  return theArray;
}

Answer 20

That's okay... you need to replace the null parameters with

System.Reflection.Missing.Value 

in your "using" statement include System.Reflection. You might only need to add this statement once... I've had it work that way, but also have had to use it for all parameters... not sure what the difference is... I'm trying to get this figure out too.

What I'm trying to do is pull out one row at a time from an excel sheet and get the file path from a hyperlink value. I know that there is a hyperlink object that with an address property like so,

myFilePath = range.Hyperlinks(1).Address;

but I keep getting an error on the hyperlink bit... really annoying me.

Answer 21

Excel.Sheets sheets = theWorkbook.Worksheets;

The next runtime error occurs when trying to run your code.
System.InvalidCastException was unhandled
  Message="Unable to cast COM object of type 'System.__ComObject' to class type 'Microsoft.Office.Interop.Excel.WorksheetClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have any interop assembly registered will be wrapped in the __ComObject type. Instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface."
  Source="Test1"
  StackTrace:
       at Test1.ExcelForm.Test(String filename) in D:\Projects\SSMSC\Test\Test1\Form1.cs:line 30
       at Test1.ExcelForm.button1_Click(Object sender, EventArgs e) in D:\Projects\SSMSC\Test\Test1\Form1.cs:line 43
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Test1.Program.Main() in D:\Projects\SSMSC\Test\Test1\Program.cs:line 17
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

Answer 22

when i 'm trying to read date from the cell, it's returning me a double value...can any one help me in this.

Answer 23

There are also some .NET open source projects that offer Excel read/write functionality:

• http://sourceforge.net/projects/nexcel/ - Reads from .xls ; doesn't require Excel to be installed
• http://sourceforge.net/projects/koogra/ - Reads from .xls using COM automation ; requires Excel to be installed
• http://sourceforge.net/projects/smartexcel/ - Writes binary Excel files ; doesn't require Excel to be installed

Answer 24

Hi

I need to insert a button into an excel sheet through C#, and then handle the click event using C# code, and not a macro.

I tried using Excel.Button but could not handle the click event. I also tried using MSForms.CommandButton and could handle the click event if i added the button to an existing Excel sheet, but couldn't add the button through C# code.

Could someone please help?

//Using Excel.Button; Cant find Event Handler for Click event

Excel.Buttons excelButtons;
Excel.Button excelButton;

excelButtons = (Excel.Buttons)excelSheet.Buttons(MIssing.Value);
excelButton = excelButtons.Add(0, 0, 100, 20);

excelButton.Caption = "Hi";

//Using MSForms.CommandButton; Cant find a way to insert the button through C# code

MSForms.CommandButton button;
excelButton = button;

btnClick = (MSForms.CommandButton)FindControl("btnClick");
btnClick.Click += new CommandButtonEvents_ClickEventHandler(btnClick_Click);

object FindControl(string name)
{
return FindControl(name, (Excel.Worksheet)excelBook.ActiveSheet);
}

object FindControl(string name, Excel.Worksheet sheet)
{
Excel.OLEObject theObject;
try
{
theObject = (Excel.OLEObject)sheet.OLEObjects(name);
return theObject.Object;
}
catch
{
// Returns null if the control is not found.
}
return null;
}

 

Answer 25

incase anyone wants to know how to do it in C# 2005 here is my code:

in this example I wanted to read only certain cells so I did not need to use arrays.

DateTime effectiveDate;

float currentRate;

openFileDialog1.FileName = "*.xls";

if (openFileDialog1.ShowDialog() == DialogResult.OK)

{

Microsoft.Office.Interop.Excel.Application excelOBJ = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook theWorkbook = excelOBJ.Workbooks.Open(openFileDialog1.FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

Microsoft.Office.Interop.Excel.Worksheet sheets = (Microsoft.Office.Interop.Excel.Worksheet) theWorkbook.Worksheets[1];

Range myRange = (Range) sheets.Cells[30,10];

currentRate = (float)Convert.ToDouble(myRange.Cells.Value2.ToString());

myRange = (Range)sheets.Cells[30, 2];

effectiveDate = Convert.ToDateTime(myRange.Cells.get_Value(Type.Missing).ToString());

textBox1.AppendText(currentRate.ToString() + " " + effectiveDate.ToShortDateString());

//not sure how many times to do this but 6 seems enough. I don't think excelOBJ == null ever happens even when it quits becasue a loop doesn't work.

if (excelOBJ != null)

{

excelOBJ.Quit();

}

if (excelOBJ != null)

{

excelOBJ.Quit();

}

if (excelOBJ != null)

{

excelOBJ.Quit();

}

if (excelOBJ != null)

{

excelOBJ.Quit();

}

if (excelOBJ != null)

{

excelOBJ.Quit();

}

if (excelOBJ != null)

{

excelOBJ.Quit();

}

}

 

I have noticed that there is no loner a range.cells.value. 

Answer 26

Below is the whole code required for reading the Excel file.

void Read_Excel()

{

// Test.xls is in the C:\

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + C:\Test.xls + ";";

connectionString += "Extended Properties=Excel 8.0;";

// always read from the sheet1.

OleDbCommand myCommand = new OleDbCommand("Select * from [Sheet1$];");

OleDbConnection myConnection = new OleDbConnection(connectionString);

myConnection.Open();

myCommand.Connection = myConnection;

OleDbDataReader myReader = myCommand.ExecuteReader();

while (myReader.Read())

{

// it can read upto 5 columns means A to E. In your case if the requirement is different then change the loop limits a/c to it.

for (int i = 0; i < 5; i++)

{

Response.Write(myReader.ToString() + " ");

}

Response.Write("<br>");

}

myConnection.Close();

}

Answer 27

Very helpful info, thanks guys!

Ke

Answer 28

Did you find a solution to the following problem:

when i 'm trying to read date from the cell, it's returning me a double value...can any one help me in this.

Thanks

Answer 29

I had similar problems few months ago, and I gave up from Excel...
I would recommend you to use ExcelLite component, it has a free version with some size limitations. It is much faster, and very easy to use... And client doesn't need to have Office installed...
You can download this component on:
http://www.gemboxsoftware.com/

Answer 30

 Hello,

I opened the worksheet but couldn't read the values in cells as integer. It requires explicit conversion can I tried convert.toint32 but still error occurs.

 

code is:->

//definition of worksheet is

Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);

int stemp = Convert.ToInt32 ( worksheet.Cells[1, 1]);

 

Regards

Harshal

Answer 31

Hi, did you find out a way to dynamically insert a button in excel and get your c# method to be called when the button is clicked?

Answer 32

Hello,

 

I will refer here only the Web Applications.

It is not possible to perform file I / O operations at Web Applications.

Although you can read Excel files on .csv / .txt format.

You can find an example on my site:

http://www.wwv-it.com/WebServicesNet/ServicesNet.aspx,

category  “Data conversions”,

Service "Convert Excel / Text files to Data Sets / XML files / database tables".

 

I would be glad if you could help with this example.

Do not hesitate to contact me if you need more information or code.

 

Regards

 

Einstein 

 

 

 

 

 

Answer 33

Dear fellows

 

I have problem about which i donot from where i can get help.

 

What is Working:

 

I have made a ASP.NET (C#) application that is reading Data from Excel (cell b cell). That is working fine and i can check it in browser.

 

Problem:

 

Now using this code i made a web part in C# and i want to show it in SharPoint Server 2007 site page.  Now that is not working , thats mean not showing the web part on the page.

 

And when i comment the excel ralated code in web part coding and check it at SharePoint server, web part works.

 

I donot know what to do, Please Help

 

Piece of code is:

 

Excel.Application app = new Excel.Application();

Excel.Workbook wbook = null;

Excel.Worksheet wsheet = null;

Excel.Range range = null;

app.Visible = false;

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

string filepath = inputFile1.Value.ToString();

if (filepath != "")

{

wbook = app.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value);

 

string currentSheet = "Sheet1";

wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item(currentSheet);

range = wsheet.get_Range("B6", "H20");

System.Array myvalues = (System.Array)range.Cells.Value2;

valueArray = ConvertToStringArray(myvalues);

 

if (app != null)

{

app.Workbooks.Close();

app.Quit();

}

app = null;

wsheet = null;

range = null;

Answer 34

Hi.

To use this I need to add the Microsoft Excel refrence to the project, right?

The thing is I dont have microsoft offince on my computer. I use open office which is kind of the same thing. But the problem is that I dont have that refrence in the COM that when I want to add the refrence to my project. Is there any other way to do this or anywhere I can get that reference thing?

thx

Answer 35

Does anybody know to figure out what range of data was used for a chart object? 

 

I like this bit of code posted earlier because it is easy to use:

 

Range myRange = (Range) sheets.Cells[30,10];

currentRate = (float)Convert.ToDouble(myRange.Cells.Value2.ToString());

myRange = (Range)sheets.Cells[30, 2];

 

 

So, my plan is to figure out which cells were used to create a particular chart - i.e. which cells are the x-axis and which cells are the y-axis.  Then just loop through and grab the values to an array.

 

 

Any thought would be great...

 

 

Answer 36

 

learning

Answer 37

I cannot find the function ConvertToStringArray.  What dll do I need to include in my project to be able to use this function?

Answer 38

This is not ready made function, you have to write it yourself, here i can give u example, you can ajust the lengths acooring to your need,

 

string[] ConvertToStringArray(System.Array values)

{

// create a new string array

string[] theArray = new string[values.Length];

int position = 0;

// loop through the 2-D System.Array and populate the 1-D String Array

for (int i = 1; i <16; i++)

{

for (int j = 1; j < 8; j++)

{

if (values.GetValue(i, j) == null)

theArray[position++] = "";

else

{

theArray[position++] = (string)values.GetValue(i, j).ToString();

}

}

}

 

return theArray;

}

 

Cheerz

Answer 39

 

i just asking How u open a excel sheet(Macro enable) in Moss site by button click on Infopath form.

Answer 40

 

Can u suggest how i open this excel sheet by Published infopath form.in moss2207.

 

and where i Save my excel sheet as it's macro enable so not published in moss.

 

but i upload this excel sheet.

 

in waiting ur reply.///

Ankit jain......!!

Answer 41

you can use OLED connection to do this

http://csharp.net-informations.com/excel/csharp-excel-oledb-insert.htm

bla.

Answer 42

hI,

 I tried using this codebut the Response is undefined i tired some ways but could not find an answer.

 Somebody, help me..

Regards,
Kishan K

Answer 43

Read and write excel using C# Hope this helps

Answer 44

hi,

I have an issue while uploading the Excel sheet from the Asp.Net Upload Control.
Here is my scenario.

1) I am using my Asp.Net Upload Control to read the excel sheet data and worksheet names  in the Uploaded Spreadsheet.
2)I am creating the folder in the local machine and saving the uploaded file in Submit button.
3)Then I am reading the data of Excel Spreadsheet from the saved file in local machine using OledbConnection class in C#.

I dont want to save the Uploaded file in the local machine and read it.
Is there any possibility to read the Uploaded Excel File without saving it in Local Machine using Upload file control.

Note : According to my requirement we can’t use the Excel object from office.Excel.Interop dll’s. We should implement with the help of OledbConnection class only.

I am trying this from past 1week but no solution. Can anybody please help me regarding this problem.

Thanks,
Rajkamal.V

---

rajkamal29

Answer 45

find the following link.

http://csharp.net-informations.com/excel/csharp-excel-oledb.htm

http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

suzett.

Answer 46

and if i want to open a xls that is allocated in a sharepoint site??
wich code i shoul use??
THANK YOU

Answer 47

Hi,

you could use GemBox .NET spreadsheet component to read Excel file .

It supports import/export Excel to DataTable within just one method call, and then it is trivial to update database with ADO.NET.

Answer 48

Is the get_Item not 1 based?  I think I read somewhere that it is.

Later

Brian

Answer 49

i was able to read the excel file using ole db reader.
the question is want to read the first excel sheet no matter what its name is...as in if there are 3 sheets in my excel file...sheet1 ,sheet 2,sheet 3....i always want to read sheet 1 or if sheets name is a,b,c i always to read  sheet a.is there a way to read this by default when the program loads ???

i m able to retrieve table names from  the excel sheet but the sheet array returns 2 entries for each excel table. and everytime if i hard code the arrays ..i realize that it returns other tables index .
how do i do this!!

help
!!!

Answer 50

@Vital Parsley : you answer
simple code sample generic excel reader , no matter how many sheets you want to parse

simple coding with simple life :D

using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel ;

 public static string ParseFile(string SourceLocation, string Desitnation, string FileName)
        {

          
            OleDbConnection _con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+SourceLocation+FileName+";Extended Properties=Excel 12.0");
            try
            {
                _con.Open();
                System.Data.DataTable _tab = new System.Data.DataTable();
                System.Data.DataTable _SheetTable = new System.Data.DataTable();
                _SheetTable = _con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                for (int Sheet = 0; Sheet < _SheetTable.Rows.Count; Sheet++)
                {
                    OleDbCommand _com = new OleDbCommand("SELECT * FROM [" + _SheetTable.Rows[Sheet][2].ToString() + "]", _con);
                    OleDbDataAdapter _dap = new OleDbDataAdapter(_com);

                    _dap.Fill(_tab);

                    string _Meesage = string.Empty;
                    for (int x = 0; x < _tab.Rows.Count; x++)
                    {
                        for (int y = 0; y < _tab.Columns.Count; y++)
                        {
                            _Meesage += _tab.Rows[x][y].ToString() + ",";

                        }
                        _Meesage += "\r\n";

                    }
                    string _FileName = _SheetTable.Rows[Sheet][2].ToString();
                    _FileName = _FileName.Replace("$", "");
                    _log.SuccessLoggingFileExcel(_FileName, _Meesage, Desitnation);
                }
                // _log.SuccessLogging("UFL", "Mesage from the Success Logging From the user");  

                return "Successessfully Parsed";

            }
            catch (Exception ex)
            {
                Logging _lo = new Logging();
                _log.LogErrorLog("Exception", ex);
              

            }
        }


Hope this may help.
Make life easy :)

Answer 51

Thanx for ur help.

theres another problem i m facing with my application.

i have 5 columns in my excel file out of which 2 are date columns.User enters date in these columns.In my application i m trying to upload files i got from my client.

whats happening is when i keep the excel file open and try to upload the file the date time columns are treated as some numbers (e.g 39044) instead of showing them as date.(this is as seen in the datatable i use to get excel data.i used a breakpoint to see this data).But if i upload the same file with excel file closed i can see the date column as dates in the datatable ! ! ! Why is this ?

So, to find a solution i right clicked on the cell in excel and chose cell formats,in the general tab the date is shown as a number(e.g 39044 ) and not a date .

in some of the other files i received from my client i saw the date as date (e.g 15/12/2009) and these files were uploaded into the datatable as dates even if the excel file is closed or open.

What exactly is the problem and solution to this ?

Answer 52

YOR CODE HERE DOESNT WORK IF MY FILE SHEET NAME HERE HAS $   IN IT.

_FileName = _FileName.Replace("$", "");

Answer 53

WAT CULD BE DONE IS GET THE LAST INDEX POSITION OF $ AND USING SUBSTRING TO GET STRING BEFORE THAT INDEX POSITION ONE CAN GET THE TABLE NAME.

Answer 54

Here's what finally worked for me:

 

                object misValue = System.Reflection.Missing.Value;
                Excel.Application xlApp = new Excel.ApplicationClass();
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(path,
                    misValue, misValue, misValue, misValue, misValue, misValue,
                    misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);                               
                Excel.Worksheet worksheetOriginal = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                Excel.Range range = worksheetOriginal.get_Range("A1", "O50");
                System.Array myvalues = (System.Array)range.Cells.Value2;

 

Carlos A Merighe.

Answer 55

Thanks Farooqi , its works in my case.

 

Answer 56

thanks..it worked fine for me.

Answer 57

Also check this link for help:

http://prasanjitmandal.blogspot.com/2008/06/read-contents-of-excel-file-20032007.html

---

BR, PM

Answer 58

I use a  best tool for creating an Excel Spreadsheet with C#, it can reading and writing to excel 2007 or excel 2010 from c#.
Spire.XLS

Answer 59

ExcelObj is never declared anywhere.  I think you need this line before ExcelObj can be used:

Excel.Application ExcelObj = new Excel.Application();

Answer 60

using Excel = Microsoft.Office.Interop.Excel;

            private void readExcel()
            {
                //Open File Dialog
                OpenFileDialog openFileDialog1 = new OpenFileDialog();

                //filter file dialog hanya bisa buka file excel(xls)
                openFileDialog1.Filter = "Microsoft Excell Document|*.xls";
                openFileDialog1.Title = "Select Document";

                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                   
                    //Method input data ke grid
                    bool confirm = false;


                    if (AskExistingDataWillBeOverrided() == DialogResult.Yes)
                        confirm = true;
                    else
                        confirm = false;

                    if (confirm)
                    {
                        #region Import data ke Excel
                        Excel.Application _excelApp = new Excel.Application();
                      
                        Excel.Workbook workBook = _excelApp.Workbooks.Open(openFileDialog1.FileName,
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing);

                        int numSheets = workBook.Sheets.Count;
                        //
                        // Iterate through the sheets. They are indexed starting at 1.
                        //
                        for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
                        {

                            Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[sheetNum];

                            Excel.Range excelRange = sheet.UsedRange;
                            object[,] valueArray = (object[,])excelRange.get_Value(
                                Excel.XlRangeValueDataType.xlRangeValueDefault);

                            for (int L = 3; L <= excelRange.Rows.Count; L++)
                            {
                                //Print valueArray[L,1];
                                //Print valueArray[L,2];
                            }
                            //Or Another Method with valueArray Object like "ProcessObjects(valueArray);"
                        }
                        workBook.Close(false, openFileDialog1.FileName, null);
                        Marshal.ReleaseComObject(workBook);
                    }
                }
            }

you can use it for reading an excel workbook with many sheet.

Answer 61

I use VS2008 and build error: namespace "Excel" could not be found.

And then I add "Microsoft.Office.Interop.Excel;" but error.

Please help me!

Answer 62

Hi,

Could you please look into below question-

http://social.msdn.microsoft.com/Forums/en/csharpgeneral/thread/93bd31ee-91fa-40ed-a54c-79ca429ffaae

 

Thanks,Sanket

Answer 63

Hi,

Could anyone look into below question and advise.

http://social.msdn.microsoft.com/Forums/en/csharpgeneral/thread/93bd31ee-91fa-40ed-a54c-79ca429ffaae

Thanks, Sanket

 

Answer 64

from where can i find "excel object library " ?

frustrated :(

Answer 65

How i can get xls file from different places?

my connString is:  <add name="XlsDocumentOpen" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\n00dle\Documents\Visual Studio 2010\Projects\Volia\Volia\myFile.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'" />

---

it is a good day to die

Answer 66

Since this is a big thread and many of us are involved in the topic. I can see that Automating Excel tasks from VS C# is a big concern. 

I'm trying to investigate the topic from some threads of the forum, and the MSDN of MS. I choose to start by using PIAs. 

I'm using C# in Visual Studio 2005. And I want to use Visual Studio 2005 Tools for Microsoft Office System - says VSTO 2005. 

So far, I installed the PIAs and added reference in my new project and tried to implement the code but failed many times:

I read this link from MSDN http://msdn.microsoft.com/en-us/library/wss56bz7(v=VS.80).aspx 

It says that: 

==========

When you create a new document-level project for Excel by using VSTO 2005, you have the option of creating a new Excel Application or Excel Template project. VSTO 2005 automatically creates the following code files in your new Excel project for both document and template projects.

Visual Basic	C#
ThisWorkbook.vb	ThisWorkbook.cs
Sheet1.vb	Sheet1.cs
Sheet2.vb	Sheet2.cs
Sheet3.vb	Sheet3.cs

===========

I also put this line at the beginning of my code "using Excel = Microsoft.Office.Interop.Excel;"

But there is no way I can find the so-called option to create document-level project nor application-level project,  Nor even Excel application or Excel templates,  and those automatically created files (ThisWordbook.cs, Sheet1(2)(3).cs) also cannot be found

I appreciate so much for any help. 

I am new C# and also new to the forum.. if I have any mistake, please correct me

 

Answer 67

Dear guys,

So far, I'm still working on this Excel automation with C#. Compared to the previous post, I've made a few progress. Now, I've been able to find the necessary package to do the task. Including:

- Visual Studio 2005, using C#

- VSTO 2005 SE (the one for Office 2007) => application-level add-ins

- PIAs

With these installations, I now can start a project to work with office. Here it comes with newer problems. My configuration is application-level customization, and I don't know how to do several tasks with it:

- in document-level, there is a syntax to work with workbooks, or sheets like this "Excel.Workbook newWorkbook = this.Application.Workbooks.Add(missing);" or can use "Globals.Sheet1.Range ....." to access Sheet, Workbook. Now I cannot do this, I only have "Globals.ThisAddIn ...".

So, How can I do the same tasks with VSTO 2005 SE? is it possible ? or I will have to install VSTO 2005?? the download webpage from Microsoft says "VSTO 2005 can no longer be downloaded"

Answer 68

Take a look on this code,there are 3 textboxes in excel group called group_name.

int i = 0;

                foreach (Excel.Shape shp in xlWorkSheet.Shapes.Item("group_name").GroupItems)

                {

                    excelOleObject = (Excel.OLEObject)shp.OLEFormat.Object;

                    if (i == 0)//excelOleObject is now excel_textbox2,why not excel_textbox1??,What's the order?

                        cs_txt_1.Text = excelOleObject.Object.GetType().InvokeMember("Text", System.Reflection.BindingFlags.GetProperty, null, excelOleObject.Object, null).ToString();

                    else if (i == 1)

                        cs_txt_2.Text = excelOleObject.Object.GetType().InvokeMember("Text", System.Reflection.BindingFlags.GetProperty, null, excelOleObject.Object, null).ToString();

                    else if (i == 2)

                        cs_txt_3.Text = excelOleObject.Object.GetType().InvokeMember("Text", System.Reflection.BindingFlags.GetProperty, null, excelOleObject.Object, null).ToString();

                    i++;

                }

The question is in which order I get excelTextbox on this way(using foreach loop).I have xl_textbox1,xl_textbox2,xl_textbox3, in group.

How can I set order like this :xl_textbox3,xl_textbox2,xl_textbox1

Answer 69

Hi.. Using this program, How can i open an excel sheet to read it..

Without using Excel object & OleDb

---

Answer 70

Does program work whenever microsoft office has not been installed on the PC, If I use Microsoft.Office.Interop.Excel ?

 

Answer 71

No It does not work..

however if you install the required assemblies, definitely it works

Microsoft recommended assemblies - http://support.microsoft.com/kb/897646

 

 

---

~ Loveson

Answer 72

Have a try Spire.XLS, easy to read and write excel , caculate engine, file i/o directly, performance fast.

Answer 73

You can also use ACE OLEDB data provider.

Please refer : yoursandmyideas blog by Sunil Singhal

Sunil Singhal

http://yoursandmyideas.wordpress.com

Answer 74

Hi.  I have been trying to read a 2010 excel spread sheet using c#.  It also works for spreadsheets saved in 2007 format.  I have enclosed some sample code that works.

     // NEEDED TO USE EXCEL INTEROPERABILITY
using cExcel = Microsoft.Office.Interop.Excel;
    // NEEDED FOR DYNAMIC BINDING
using Microsoft.CSharp.RuntimeBinder;

                .
                .
                .
                .
                .
               
        // Set your file Name
   String lFileName = "c:\\temp\\file.xLSx";
  
        // CREATE EXCEL APPPLICATION
    cExcel.Application cxApp = new cExcel.Application();

        // OPEN WORKBOOK USING FILE NAME
    cExcel.Workbook cxWB = cxApp.Workbooks.Open(lFileName);

        // GET WORKSHEET
    cExcel.Worksheet cxWS = null;

        // LOOP TO FIND THE WORKSHEET WITH THE NAME "SHEETONE"
    foreach (cExcel.Worksheet cxTemp in cxWB.Worksheets)
    {
        if (!("SHEETONE".Equals("cxTemp.Name)))
            System.Runtime.InteropServices.Marshal.ReleaseComObject(cxTemp);
        else
        {
            cxWS = cxTemp;
            break;
        }
    }

    try {
            // NO WORKSHEET FOUND GET OUT
        if (cxWS == null)
        {
            return(false);
        }

            // ACTIVATE WOOKSHEET GET ROW AND COLUMN COUNT OF ALL THE CELLS USED IN SPREADSHEET
        cxWS.Activate();
        cExcel.Range cxRange = cxWS.UsedRange;
        long lRowCount = cxRange.Rows.Count;
        long lColumnCount = cxRange.Columns.Count;


            // PROCESS EACH SPREADSHHET ROW
        String sColumn;
        for  (int i = 1; i <= lRowCount;  i++)
        {
                // PROCESS EACH COLUMN IN EACH ROW
            for (int j = 1; j <= lColumnCount;  j++)
            {
                Object oTemp = cxRange.Cells[i, j].Value;    //  HAD TO DO THIS BECAUSE OF DYNAMIC BINDING TO GET

                                                                                     //  STRING OF THE   VALUE OF THE CELL
                sColumn = oTemp.ToString().Trim();
            }                         
        }
    }
    catch
    {
        throw;
    }
    finally
    {
               // CLEAN UP

       if (!(cxWS == null))
          System.Runtime.InteropServices.Marshal.ReleaseComObject(cxWS);
      cxWB.Close(false, null, null);
      cxApp.Quit();
      System.Runtime.InteropServices.Marshal.ReleaseComObject(cxWB);
      cxWB = null;
      System.Runtime.InteropServices.Marshal.ReleaseComObject(cxApp);
      cxApp = null;
    }


                .
                .
                .
                .
                .
               

// HOPE THIS HELPS

Answer 75

Hello everyone.

My desknbsp;Windows 7 Ultimate 64bits, Visual Studio 2010 Ultimate, Excel 2010.
I have a macro myMacroX() in my PERSONAL.XLSB.
How to CALL this myMacroX() from C # application?
The code below opens my excel (x.xls) file.
How to put the command that calls the myMacroX()?

#region Open the file
            string app = @"C:/Program Files (x86)/Microsoft Office/Office14/EXCEL.exe";
            string param = ExcelFile.xls;
            System.Diagnostics.Process process = System.Diagnostics.Process.Start(app, param);
            #endregion
                                  

---

silvio pontes

Answer 76

Hi,

I tried your above code,

it gives me the following error;

"External table is not in the expected format."for this (da.Fill(dt);)

Can you help me out

thanks in advance

Answer 77

Great share. thanks Mr. AbdulAzizFarooqi.

Answer 78

Hi,

what is "ExcelObj" what is the object type. please explain.

Thanx

thari777