Click here to Skip to main content
14,929,864 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Basically what I need to do is, I have a lot of data that takes up 1 cell per row loaded into a datagridview, and I want to pivot it, and export it to an excel file. So what I have is a vertical list, and I would like to make it horizontal, but I only need the cells to be filled until getting to column 'L', and when the data gets to it, then it starts again at the next row, and repeating this until dgv has no more data to fill in to the excel.

What I have tried:

So far what I have is:

    private void button1_Click(object sender, EventArgs e)
            Excel.Application xlApp = new Excel.Application();
            Excel.Worksheet xlsht = new Excel.Worksheet();
            Microsoft.Office.Interop.Excel.Workbook xlwb;
            xlApp.Visible = true;
            string path = @"myfilepath";
            xlwb = xlApp.Workbooks.Open(path);
            xlsht = xlwb.Worksheets["Tabelle1"];
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)

                for (int j = 1; j < 12; j++)
                    xlsht.Cells[i + 1, j] =               dataGridView1.Rows[i].Cells[0].Value.ToString();

The problem with this is the following: it fills the cells with the same value, until getting to the column 'L' (11th column, that's why I have 12 in the nested for loop), and then it starts again in the next row, but again, repeating the next value(to clarify, the value in the first row is 1, so it repeats 1 until getting to the 11th column.Then it starts again with the next value, for example 2, and does the same thing).
How could I fix this?
Thanks in advance!
Updated 22-Feb-21 2:10am

1 solution

Try this:
int excelrow = 1;
for (int i = 0; i < dataGridView1.Rows.Count - 1; ) // do not increment i here
    for (int j = 1; j < 12; j++)
        xlsht.Cells[excelrow, j] = dataGridView1.Rows[i].Cells[0].Value.ToString();
        i++; // increment i here to go to the next row of the DataGridView
adetroxx12 22-Feb-21 8:13am
It's interresting, because it goes to L, with the right values, but diagonally. I've no clue what might be the issue
Richard MacCutchan 22-Feb-21 8:27am
But in your version every column in the Excel sheet is set with the same value from the DataGridView: Rows[i].Cells[0]. That is why you need to increment i in the inner loop to move to the next row of the grid.
adetroxx12 22-Feb-21 8:35am
I tried the code you provided, and that's when it went down diagonally. The problem is the outer for loop's i=0, and when the inner loop runs j's new value will be 2, and because of this: xlsht.Cells[i+1, j] i's new value will be 2 as well, after the incrementation. If I understand it correctly, the first time it runs, j's value is 1, and i's is 1 as well, and that's correct, but the next time it runs their values will continue incrementing in the same pace, thus they will be equal.
Richard MacCutchan 22-Feb-21 9:04am
Sorry, my mistake, please see my updated solution.
adetroxx12 22-Feb-21 8:19am
For clarification; the first value is at 1,1, and the second one, that should be 1,2 is in 2,2 and so on
adetroxx12 22-Feb-21 8:55am
Thank you Sir! Now it works, the only thing is, it leaves out a few rows, but I can manage that. You were very helpful!
Richard MacCutchan 22-Feb-21 9:16am
My solution was still incorrect; check it now

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900