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
> 







                                            

                            						

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.

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

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.

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);
}
}

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.

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!)

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

can any body give any solution for the above query please

Thanks-Aparna