How can put a condition in loop?

Question

0.00/5 (No votes)

See more:

I am a newbie and a self taught anyway

I am planning to export my data from my datagridview into excel using loop but what i want is when when the loop reach the set condition the other data should put into another row of cells in excel

here is the example when i export my data into excel

   |___A__|___B_____|____C___|__D____|___E___|_________F___________|
_
1     I      want      to      go      out    inside my house
_
2     He     want      to      go      out    From his School
_
3
_
4
_

But what I want is like this in my excel

   |___A ____|___B___|___C___|___D_____________|____E____|__F__|
_
1     I        want   
_
2     He       want    
_
3
_
4     to         go      out     inside my house
_
5     to         go      out      From his School
_

here is my code Can you please help me.

VB

Private Sub ExportingEdited()
       If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
           Exit Sub
       End If

       Dim columnCollection As DataGridViewColumnCollection = DataGridView1.Columns
       Dim currentVisibleColumn As DataGridViewColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
       Dim lastColumnExported As DataGridViewColumn = currentVisibleColumn
       Dim visibleColumnCount As Integer = columnCollection.GetColumnCount(DataGridViewElementStates.Visible)

       Dim xlApp As Microsoft.Office.Interop.Excel.Application
       Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
       Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
       Dim misValue As Object = System.Reflection.Missing.Value


       xlApp = New Microsoft.Office.Interop.Excel.Application With {.Visible = True}
       xlWorkBook = xlApp.Workbooks.Add(xlPath)
       xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Microsoft.Office.Interop.Excel.Worksheet)

       For r = 0 To DataGridView1.Rows.Count - 1
           'Reset values'
           currentVisibleColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
           lastColumnExported = currentVisibleColumn
           For c = 2 To visibleColumnCount + 1
               Dim value = DataGridView1.Rows(r).Cells(currentVisibleColumn.Index).Value
               If value IsNot vbNullString Then
                   xlWorkSheet.Cells(r + 58, c) = value.ToString()
               End If
               currentVisibleColumn = columnCollection.GetNextColumn(lastColumnExported, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
               lastColumnExported = currentVisibleColumn
           Next
       Next

       xlWorkSheet.SaveAs(CType(xlPath, String))
       xlWorkBook.Close()
       xlApp.Quit()

       releaseObject(xlApp)
       releaseObject(xlWorkBook)
       releaseObject(xlWorkSheet)

       Dim p As ProcessStartInfo = New ProcessStartInfo(CType(xlPath, String))
       Process.Start(p)

   End Sub

What I have tried:

I tried this

VB

Private Sub ExportingEditedspecificrow()
      If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
          Exit Sub
      End If

      Dim columnCollection As DataGridViewColumnCollection = DataGridView1.Columns
      Dim currentVisibleColumn As DataGridViewColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
      Dim lastColumnExported As DataGridViewColumn = currentVisibleColumn
      Dim visibleColumnCount As Integer = columnCollection.GetColumnCount(DataGridViewElementStates.Visible)

      Dim xlApp As Microsoft.Office.Interop.Excel.Application
      Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
      Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
      Dim misValue As Object = System.Reflection.Missing.Value


      xlApp = New Microsoft.Office.Interop.Excel.Application With {.Visible = True}
      xlWorkBook = xlApp.Workbooks.Add(xlPath)
      xlWorkSheet = CType(xlWorkBook.Sheets("sheet2"), Microsoft.Office.Interop.Excel.Worksheet)

      For r = 0 To DataGridView1.Rows.Count - 1
          'Reset values'
          currentVisibleColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
          lastColumnExported = currentVisibleColumn
          For c = 2 To visibleColumnCount + 1
              Dim value = DataGridView1.Rows(r).Cells(currentVisibleColumn.Index).Value
              If value IsNot vbNullString Then
                  xlWorkSheet.Cells(r + 58, c) = value.ToString()
                  If c = 7 Then
                      For a = 0 To DataGridView1.Rows.Count - 1
                          'Reset values'
                          currentVisibleColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
                          lastColumnExported = currentVisibleColumn
                          For b = 2 To visibleColumnCount + 1
                              value = DataGridView1.Rows(a).Cells(currentVisibleColumn.Index).Value
                              If value IsNot vbNullString Then
                                  xlWorkSheet.Cells(a + 67, b) = value.ToString()
                              End If
                          Next
                      Next
                  End If


              End If
              currentVisibleColumn = columnCollection.GetNextColumn(lastColumnExported, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
              lastColumnExported = currentVisibleColumn
          Next
      Next
      xlWorkSheet.SaveAs(CType(xlPath, String))
      xlWorkBook.Close()
      xlApp.Quit()

      releaseObject(xlApp)
      releaseObject(xlWorkBook)
      releaseObject(xlWorkSheet)

      Dim p As ProcessStartInfo = New ProcessStartInfo(CType(xlPath, String))
      Process.Start(p)

  End Sub

but what happen is the result became like this

  |___A__|___B_____|____C___|__D____|___E___|_________F___________|
_
1     I      want      to      go      out    inside my house
_
2     He     want      to      go      out    From his School
_
3
_
4     I      I        I       I        I        I
_
5     I      I        I       I        I        I
_

Posted 2-Sep-18 23:32pm

Member 13890537

Updated 3-Sep-18 23:53pm

Patrice T

v5

Add a Solution

Comments

Richard MacCutchan 3-Sep-18 7:22am

And? What happened?

Member 13890537 3-Sep-18 21:22pm

Hello Mr. Richard And Goodmorning
I already update the post. Please can you help me

Richard MacCutchan 4-Sep-18 3:49am

Row 1: put the first two words of the first data row.
Row 2: put the first two words of the second data row.
Row 3: nothing
Row 4: put the last words of the first data row.
Row 5: put the last words of the second data row.

So, if row number is less than 3 copy word 1 to column 1, and word 2 to column 2.
If row equals 3, do nothing
If row is greater than 3 copy words 3 to N to columns 1 to N-2

Member 13890537 4-Sep-18 20:28pm

Goodmorning and have a healthy Day Mr.
Thank you for this but how can I do that?
can you give me example of code?

Richard MacCutchan 5-Sep-18 3:41am

Not really, because I am not completely sure what actual problem you are trying to solve. Since you have this data in a DataGridView, it must come from some other source, so maybe it would be easier to send it to Excel from its original source.

Member 13890537 5-Sep-18 3:48am

the Data is came from my database access that I created and then the data loads into my datagridview when my form loads. then I created a button that will export the data from my datagridview to excel.

Richard MacCutchan 5-Sep-18 4:14am

So use the original data, split each line into the parts you want and put in the relevant cells of the excel sheet.

1 solution

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)

Dominic Burford · Answer 1 · 2018-09-03T23:54:00

Solution 1

Surely it would be better to get the source data into the correct format, then all your code needs to do it output the data into Excel. Messing around with the data after the fact is over complicating things and error prone.

Posted 3-Sep-18 23:54pm

Dominic Burford

Comments

Member 13890537 4-Sep-18 20:32pm

Hello Mr. Dominic Burford Goodmorning and Healthy Day
what do you mean of the correct format?

Dominic Burford 5-Sep-18 0:32am

Get the data in your datagridview to exactly replicate how you want the data to look once exported into Excel. This way you won't have to manipulate it in code.

Member 13890537 5-Sep-18 2:33am

Thank you for the response Mr.
It will be great if you can give a example

So I can understand more and others newbie too

We will Greatly Appreciate it

Thanks in advance Mr.

Dominic Burford 5-Sep-18 2:37am

How do you currently populate your datagridview? This is where you need to make your changes. Populate this to mirror your required Excel output.

Member 13890537 5-Sep-18 2:44am

I'll post the code here Mr.

This is on how I populate my Datagridview. How do I do the mirror thing?
Kindly have patience on me Mr. Burford, I just want to learn..

Public Sub DatagridviewLoad()
Dim dbcommand As String
'Dim dv As New DataView(dt)

dbcommand = "SELECT * FROM EMPLOYEES_DATA"

da = New OleDbDataAdapter(dbcommand, con)
dt = New DataTable
da.Fill(dt)

DataGridView1.DataSource = dt
DataGridView1.Columns(0).Visible = False
DataGridView1.Columns(1).Visible = False
DataGridView1.Columns(2).Visible = False
DataGridView1.Columns(3).Visible = False
End Sub

Dominic Burford 5-Sep-18 3:05am

What I'm suggesting is that your function for populating the datagridview needs to output its data so that it matches the required output you need for Excel.

Member 13890537 5-Sep-18 3:28am

I can really feel your effort on answering my question, I appreciated it Mr. Burford
Now I Understand your point but I dont know how to do that is there any links or article that can help me to do that? but it will be great if you
Can show us on how to do that an example please?

Thanks