How read all Excel file row into mysql database by .NET

[minhtran]

Hello everyone!
I have a project as to read the Excel file and load into mySQL database, but my coding is to read only 100 first rows, how can we read all rows from .XLS sheet. Please, anyone can help me. Thanks a lot in advance. my coding as below:
for (int row = 1; row <= document.WorkBook.Worksheets[0].LastRow; row++)

{

// And column in first Worksheet
for (int col = 0; col <= document.WorkBook.Worksheets[0].LastColumn; col++)
{
// Append cell value to StringBuilder
builder.AppendFormat("{0}\t", document.WorkBook.Worksheets[0].Cell(row, col).Value );
//


}

builder.AppendLine();
}



// Save StringBuilder to file
File.WriteAllText("C:\\MINH.txt", builder.ToString());

[SammyB]

Assuming that you have data in the first row,
document.WorkBook.Worksheets[0].UsedRange.Rows.Count
will give you the last row. HTH --Sam

[minhtran]

Thanks Sam, But still no work coz I used ByteCount.xls has no UsedRange, any other help please

[SammyB]

Thanks Sam, But still no work coz I used ByteCount.xls has no UsedRange, any other help please

Every worksheet has a UsedRange. Post all of your code that reads the workbook and I'll fix it. Here is an example of C# Excel code. http://support.microsoft.com/kb/306683 It does different things, but shows how to do automation in C#.

[minhtran]

Thanks a lot SAM, hers is my code Please help me
protected sub DumpExcelFile(string strFilePath)
{
// Create new XLSDocument from SimpleReport.xls file
XLSDocument document = new XLSDocument(strFilePath);

// Create new StringBuilder
StringBuilder builder = new StringBuilder();


// For each row
for (int row = 1; row <= document.WorkBook.Worksheets[0].LastRow; row++)

{


// And column in first Worksheet
for (int col = 0; col <= document.WorkBook.Worksheets[0].LastColumn; col++)
{
// Append cell value to StringBuilder
builder.AppendFormat("{0}\t", document.WorkBook.Worksheets[0].Cell(row, col).Value );
//


}

builder.AppendLine();
}



// Save StringBuilder to file
File.WriteAllText("C:\\MINH.txt", builder.ToString());
}

[SammyB]

It will be Monday before I have a computer setup to do Excel with Visual Studio. Look at the link that I gave you and try to make your code look like that. Also look at this thread: http://www.thescripts.com/forum/post2587984-2.html. Notice that in C# you cannot string stuff together like document.WorkBook.Worksheets[0].Cell(row, col). You have to cast each item into an object: first get an Excel Application object, then get a Workbooks collection, then get a Workbook via Open (don't forget the oMissing's), then get the Worksheets collection, then get the Worksheet, then get the UsedRange. Let me know on Monday how you are doing. --Sam