C# winform export to excel

Question

0.00/5 (No votes)

See more:

I am trying to export to Excel, on button click everything seams to go well.
when i open the the file it just shows the header and one row..
has to be quite simple.

C#

<pre>private void btnExcel_Click(object sender, EventArgs e)
        {

            { 
            for (int i = 1; i <= 10; i++)
            {
                DataGridViewRow row =
                    (DataGridViewRow)dataGridView1.RowTemplate.Clone();
                                   
            }
        }

        
        Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

            try
            {

                worksheet = workbook.ActiveSheet;

                worksheet.Name = "ExportedFromDatGrid";

                int cellRowIndex = 1;
                int cellColumnIndex = 1;

                
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                       
                        if (cellRowIndex == 1)
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Columns[j].HeaderText;
                        }
                        else
                        {
                            worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        }
                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }
                
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                saveDialog.FilterIndex = 2;

                if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    workbook.SaveAs(saveDialog.FileName);
                    MessageBox.Show("Export Successful");
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel = null;

            }
        }

What I have tried:

editing the code internet search

Posted 6-Aug-17 7:38am

Member 12349103

Updated 6-Aug-17 8:12am

Add a Solution

Comments

Michael_Davies 6-Aug-17 13:54pm

Not that it is the answer but shouldn't this be dataGridView1.Columns.Count-1?

for (int j = 0; j < dataGridView1.Columns.Count; j++)

Also the inner for loop across the columns will not store the first row of the datagridview as you use the first row setting to set the headers and the data will not store as it is on the else.

Personally i would;

1. store the data starting at row 2 of the sheet.
2. store the header names in the top row, row 1 of the sheet.

	int cellRowIndex = 2;
	int cellColumnIndex;

	for (int i = 0; i < dataGridView1.Rows.Count - 1; i++, cellRowIndex++)
	{
		cellColumnIndex = 1;

		for (int j = 0; j < dataGridView1.Columns.Count - 1; j++, cellColumnIndex++)
		{
			worksheet.Cells[cellRowIndex, cellColumnIndex] = dataGridView1.Rows[i].Cells[j].Value.ToString();
		}
	}

	cellColumnIndex = 1;

	for (int j = 0; j < dataGridView1.Columns.Count - 1; j++, cellColumnIndex++)
	{
		worksheet.Cells[1, cellColumnIndex] = dataGridView1.Columns[j].HeaderText;
	}

2 solutions

Solution 1

Use the debugger to inspect the variables and to see what your code is doing.

There is a tool that allow you to see what your code is doing, its name is debugger. It is also a great learning tool because it show you reality and you can see which expectation match reality.
When you don't understand what your code is doing or why it does what it does, the answer is debugger.
Use the debugger to see what your code is doing. Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.

Debugger - Wikipedia, the free encyclopedia[^]
Debugging C# Code in Visual Studio - YouTube[^]
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't find bugs, it just help you to. When the code don't do what is expected, you are close to a bug.

Posted 6-Aug-17 8:08am

Patrice T

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)

RickZeeland · Accepted Answer · 2017-08-06T08:12:00

Solution 2

See this CodeProject article: DataTable to Excel[^]
and also: Excel Generator with Column Designer[^]
and this one without Excel automation: C#: Read and Write Excel (*.xls and *.xlsx) Files Content without Excel Automation (using NPOI and ADO.NET)[^]

Posted 6-Aug-17 8:12am

RickZeeland

Updated 6-Aug-17 8:19am

v3