How To Use a Loop In Excel File Every Line?

Question

0.00/5 (No votes)

See more:

I have an Excel file with more Columns, but i need column NAME, Cost and Time

I need to loop in my Excel line by line but when i have Column Name/Cost/Time to save value in a variable.

If FileUpload1.HasFile = False Then

        Exit Sub
    Else
        Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
        Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
        'Dim FolderPath As String = ConfigurationManager.AppSettings("Upload")

        Dim FilePath As String = Server.MapPath("~") & "\Upload\" & FileName
        FileUpload1.SaveAs(FilePath)

        Dim existingFile = New FileInfo(FilePath)
        Dim pack As ExcelPackage = New ExcelPackage(existingFile)
        Dim workBook As ExcelWorkbook = pack.Workbook




        If workBook.Worksheets.Count > 0 Then




        End If

    End If

Where is If workBook.Worksheets.Count > 0 Then i need to loop every value from cell. At finally I calculated the average of all times in a box.

Thanks a lot

Posted 22-Oct-15 22:06pm

MoraruVladut

Updated 23-Oct-15 2:29am

Leo Chapiro

v2

Add a Solution

3 solutions

Solution 1

You can use the Cells collection. Worksheets[0].Cells(row, column).

Also, use intellisense to find these things on your own. It's a great way to learn. Just type Worksheets[0]. and you'll see intellisense pop up with the various methods and properties of a worksheet.

Posted 23-Oct-15 1:46am

ZurdoDev

Solution 2

Dim start = worksheet.Dimension.Start
Dim [_end] = worksheet.Dimension.[End]
For row As Integer = start.Row To [_end].Row
	' Row by row...
	For col As Integer = start.Column To [_end].Column
		' ... Cell by cell...
		Dim cellValue As Object = workSheet__1.Cells(row, col).Text
	Next
Next

Source[^]
And our dear Visual Studio has this great feature called 'intellisense'. Do little bit exploration using it.

-KR

Posted 23-Oct-15 2:45am

Krunal Rohit

Updated 23-Oct-15 3:02am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MoraruVladut · Accepted Answer · 2015-10-26T03:26:00

Do While currentWorksheet.Cells(i, 1).Value <> Nothing

Name = currentWorksheet.Cells(i, 1).Value
Try
TimeCompletyDefinited = currentWorksheet.Cells(i, 36).Value
Catch ex As Exception
End Try
If (currentWorksheet.Cells(i, 4).Value) = "Completed" And (currentWorksheet.Cells(i, 8).Value) = "Column Needed" Then

a = a + currentWorksheet.Cells(i, 35).Value
b = b + currentWorksheet.Cells(i, 36).Value
c = c + currentWorksheet.Cells(i, 38).Value

End If
i = i + 1
Loop