Export datagridview to excel while keeping the formatting

Question

0.00/5 (No votes)

See more:

I am making a Winform application where I export my datagridview to excel. However, when I do it, it doesn't retain the formatting,despite the existence of a line to do so. Here is my code :

<pre>string fileName;

SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "xls files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveFileDialog1.Title = "To Excel";
saveFileDialog1.FileName = "Contrôle journalier du " + DateTime.Today.ToString("yyyy-MM-dd") + " de " + comboBox1.Text + comboBox2.Text;

if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
    fileName = saveFileDialog1.FileName;
    var workbook = new XLWorkbook();
    var worksheet = workbook.Worksheets.Add(this.Text);
    for (int i = 0; i < dataGridView2.Columns.Count; i++)
         {
              if (dataGridView2.Columns[i].Visible == true)
                        {
                            worksheet.Cell(11, i + 1).Value = dataGridView2.Columns[i].HeaderText;
                        }
                    }

                    for (int i = 0; i < dataGridView2.Rows.Count; i++)
                    {
                        for (int j = 0; j < dataGridView2.Columns.Count; j++)
                        {
                            if ((dataGridView2.Rows[i].Visible == true) && (dataGridView2.Columns[j].Visible == true))
                            {
                                worksheet.Cell(i + 14, j + 1).Value = dataGridView2.Rows[i].Cells[j].Value?.ToString();
                            }
                            if (worksheet.Cell(i + 14, j + 1).Value.ToString().Length > 0)
                            {
                                XLAlignmentHorizontalValues align;

                                switch (dataGridView2.Rows[i].Cells[j].Style.Alignment)
                                {
                                    case DataGridViewContentAlignment.BottomRight:
                                        align = XLAlignmentHorizontalValues.Right;
                                        break;
                                    case DataGridViewContentAlignment.MiddleRight:
                                        align = XLAlignmentHorizontalValues.Right;
                                        break;
                                    case DataGridViewContentAlignment.TopRight:
                                        align = XLAlignmentHorizontalValues.Right;
                                        break;

                                    case DataGridViewContentAlignment.BottomCenter:
                                        align = XLAlignmentHorizontalValues.Center;
                                        break;
                                    case DataGridViewContentAlignment.MiddleCenter:
                                        align = XLAlignmentHorizontalValues.Center;
                                        break;
                                    case DataGridViewContentAlignment.TopCenter:
                                        align = XLAlignmentHorizontalValues.Center;
                                        break;

                                    default:
                                        align = XLAlignmentHorizontalValues.Left;
                                        break;
                                }

                                worksheet.Cell(i + 14, j + 1).Style.Alignment.Horizontal = align;

                                XLColor xlColor = XLColor.FromColor(dataGridView2.Rows[i].Cells[j].Style.BackColor);
                                worksheet.Cell(i + 14, j + 1).AddConditionalFormat().WhenLessThan(1).Fill.SetBackgroundColor(xlColor);

                                

                            }
                        }
                    }



                    worksheet.Columns().AdjustToContents();
                    workbook.SaveAs(fileName);

What I have tried:

I've tried putting it in different places in vain.

Posted 19-Jun-23 0:08am

LiterallyGutsFromBerserk

Updated 19-Jun-23 5:23am

Add a Solution

Comments

Richard MacCutchan 19-Jun-23 6:33am

What alignment value are you setting for each cell, and what result do you see in the spreadsheet? Also, which Excel lib rary are you using?

LiterallyGutsFromBerserk 19-Jun-23 6:42am

I'm setting an alignment similar to the datagridview one. As for the result, I do see my datagridview, but the backcolor is still white despite it being red for certain cells in my datagridview. As for the library, I'm using CLOSEDXML.

Richard MacCutchan 19-Jun-23 6:45am

Sorry, I have not used that library so cannot offer any suggestion.

LiterallyGutsFromBerserk 19-Jun-23 7:06am

thank you anyways

2 solutions

Solution 1

Quote:

C#

worksheet.Cell(i + 14, j + 1).Value = dataGridView2.Rows[i].Cells[j].Value?.ToString();
...
worksheet.Cell(i + 14, j + 1).AddConditionalFormat().WhenLessThan(1).Fill.SetBackgroundColor(xlColor);

You're setting all of the cells to strings. You can't use a numeric comparison to conditionally format a column of strings.

Try removing the ToString call.

Posted 19-Jun-23 1:21am

Richard Deeming

Comments

LiterallyGutsFromBerserk 19-Jun-23 7:26am

Doesn't work unfortunately.

Richard Deeming 19-Jun-23 7:27am

Doesn't work how, precisely?

Have you debugged your code to ensure that the cell values are actually numbers?

Have you checked that the numbers for the rows you're expecting to be highlighted are really less than 1?

LiterallyGutsFromBerserk 19-Jun-23 9:00am

I removed the tostring from everywhere, which meant the value became an object. Now I'm going to try to remove the condition part, seeing as I don't really need it right now and don't quite remmember why the value had to be more than 1

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)

Graeme_Grant · Accepted Answer · 2023-06-19T05:23:00

When having issues like these, it is always a good idea to create a test project and check what is happening. Excel Interop - Microsoft Learn[^] especially.

So, I have created a spreadsheet manually and put a numeric value in a cell. Then I turned macro recording on, applied formatting and resized the row. Then turned off recording and viewed the VBA code generated. It looked like this:

VB

Sub Macro1()
'
' Macro1 Macro
'
    Selection.NumberFormat = "#,##0"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Rows("1:1").RowHeight = 84

End Sub

Okay, now I have a reference, I can now write my code. Here is the C# version of the code above:

C#

using System;
using System.Diagnostics;
using System.IO;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;

// trimmed for readability...

Application excel = new _Excel.Application();

excel.Visible = true; // enable viewing of worksheet


Workbook workbook = excel.Workbooks.Add();
Worksheet worksheet = workbook.Worksheets.Item["Sheet1"];

var range = worksheet.Cells.Range["A1"];

var cell = range.Cells[1,1];

cell.Value = 1234;
cell.NumberFormat = "#,##0";
cell.HorizontalAlignment = _Excel.XlHAlign.xlHAlignCenter;
cell.VerticalAlignment = _Excel.XlVAlign.xlVAlignCenter;
cell.WrapText = false;
cell.Orientation = 0;
cell.AddIndent = false;
cell.IndentLevel = 0;
cell.ShrinkToFit = false;
cell.ReadingOrder = XlReadingOrder.xlContext;
cell.MergeCells = false;

worksheet.Rows["1:1"].RowHeight = 84;

Debugger.Break(); // pause to see the changes

string file = Path.Combine(Environment.CurrentDirectory, "test.xlsx");

// remove old version
if (File.Exists(file))
    File.Delete(file);

// now save the changes
workbook.Close(SaveChanges: true, Filename: file);

// close excel to release resources
excel.Quit();

NOTES:
* For development and debugging, I have turned visibility on so I can see what is happening in the spreadsheet
* I have a breakpoint set so that I can pause execution and check
* Lastly, I release resources to avoid memory leaks

After the app finishes and the spreadsheet is closed, I open it manually and check the results.

Everything looks formatted correctly. Now it is time to update the code in the app. I recommend that you add the noted points to your own code so that you too can check your code.