Click here to Skip to main content
15,799,398 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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;
                                    case DataGridViewContentAlignment.MiddleRight:
                                        align = XLAlignmentHorizontalValues.Right;
                                    case DataGridViewContentAlignment.TopRight:
                                        align = XLAlignmentHorizontalValues.Right;

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

                                        align = XLAlignmentHorizontalValues.Left;

                                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);




What I have tried:

I've tried putting it in different places in vain.
Updated 19-Jun-23 6:23am
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

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:
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:
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))

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

// close excel to release resources

* 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.
Share this answer
LiterallyGutsFromBerserk 20-Jun-23 3:08am    
Thanks for the tips. There may have been some changes in my code, but I'm sure I'll use them when I come back to the formatting part!
Graeme_Grant 20-Jun-23 3:14am    
All good. If this has helped, please mark the question as answered so that others are aware when they read this. Thank you.
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.
Share this answer
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

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