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.WorkBo ok.Worksheets[0].LastRow; row++)

{

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


}

builder.AppendL ine();
}



// Save StringBuilder to file
File.WriteAllTe xt("C:\\MINH.tx t", builder.ToStrin g());

[SammyB]

Assuming that you have data in the first row,
document.WorkBo ok.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(s tring strFilePath)
{
// Create new XLSDocument from SimpleReport.xl s file
XLSDocument document = new XLSDocument(str FilePath);

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


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

{


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


}

builder.AppendL ine();
}



// Save StringBuilder to file
File.WriteAllTe xt("C:\\MINH.tx t", builder.ToStrin g());
}

[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.WorkBo ok.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