Home » Microsoft TechnologiesRSS

Reading Excel From C#

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:9226766e-a1d5-400c-8d3c-8ffc566bec50@discussions.microsoft.com...
> 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
>
 

64 Answers Found

 

Answer 1

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, newobject[] { null, null, null, "TABLE" });

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

DataSet dsExcel = newDataSet();

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 = newstring[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 installedhttp://sourceforge.net/projects/koogra/ - Reads from .xls using COM automation ; requires Excel to be installedhttp://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 +=
newCommandButtonEvents_ClickEventHandler(btnClick_Click);

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

objectFindControl(stringname, Excel.Worksheetsheet)
{
Excel.
OLEObjecttheObject;
try
{
theObject = (Excel.
OLEObject)sheet.OLEObjects(name);
returntheObject.Object;
}
catch
{
// Returns null if the control is not found.
}
returnnull;
}

 

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 = newOleDbCommand("Select * from [Sheet1$];");

OleDbConnection myConnection = newOleDbConnection(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(myReaderIdea.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 36

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 = newCultureInfo("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 37

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 38

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 39

learning
 

Answer 40

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


 

Answer 41

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 = newstring[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 42

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

Answer 43

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 45

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 46

Read and write excel  using C# Hope this helps
 

Answer 47

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 49

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

Answer 50

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 51

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

Later

Brian
 

Answer 52

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 53

@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 54

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 55

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

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

 

Answer 56

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 57

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 58

Thanks Farooqi , its works in my case.

 

 

Answer 59

thanks..it worked fine for me.
 
 

Answer 61

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 62

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

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

 

Answer 63

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 64

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!

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter