CODEWITHSUNDEEP
c#conditional-formattingclosedxml
MoralesJosue
MoralesJosue

Reputation: 199

Conditional Formatting ClosedXML Error

I'm exporting some data from access to excel using CloseXML, Im trying to do a Conditional Formatting with some dates, but I'm getting a NULL EXCEPTION REFERENCE ERROR. Here is what I have.

 using (OleDbConnection connection = new OleDbConnection(conecctionstring))
            {
                string selectquery = "querystring";


                using (OleDbDataAdapter selectCommand = new OleDbDataAdapter(selectquery, connection))
                {
                    DtSet1 = new DataSet();
                    selectCommand.Fill(DtSet2, "Table1");

                    var wb = new XLWorkbook();

                    var saveFileDialog = new SaveFileDialog
                    {
                        Filter = "Excel files|*.xlsx",
                        Title = "Save an Excel File"
                    };

                    wb.Worksheets.Add(DtSet1);

                    wb.Range("C:C").AddConditionalFormat().WhenEqualOrGreaterThan(DateTime.Now.ToOADate()).Fill.SetBackgroundColor(XLColor.Red);

                    saveFileDialog.ShowDialog();

                    if (!String.IsNullOrWhiteSpace(saveFileDialog.FileName))
                        wb.SaveAs(saveFileDialog.FileName);

                }
            }

I'm using C#, OpenXML and Access DB Any help is appreciated.

Upvotes: 1

Views: 3088

Answers (2)

Raidri
Raidri

Reputation: 17560

The problem in your original code is that you use Workbook.Range("C:C") instead of Worksheet.Range(). The workbook itself has no column "C" so this is an invalid range. Working code:

wb.Worksheet(1).Range("C:C").AddConditionalFormat().WhenEqualOrGreaterThan(DateTime.Now.ToOADate()).Fill.SetBackgroundColor(XLColor.Red);

It has nothing to do with loading and saving the file.

Upvotes: 1

MoralesJosue
MoralesJosue

Reputation: 199

The error is that you have to load the file before u can make any modifications on it, such as conditional formatting, and save the file again with the current format.

Here is my solution. Note: added another condition.

 using (OleDbConnection connection = new OleDbConnection(conecctionstring))
            {
                string selectquery = "string query";

                using (OleDbDataAdapter selectCommand = new OleDbDataAdapter(selectquery, connection))
                {
                    DtSet1 = new DataSet();
                    selectCommand.Fill(DtSet1, "Table1");

                    var wb = new XLWorkbook();

                    var saveFileDialog = new SaveFileDialog
                    {
                        Filter = "Excel files|*.xlsx",
                        Title = "Save an Excel File"
                    };

                    wb.Worksheets.Add(DtSet1);

                    saveFileDialog.ShowDialog();


                    if (!String.IsNullOrWhiteSpace(saveFileDialog.FileName))
                    {
                        wb.SaveAs(saveFileDialog.FileName);

                        var workbook = new XLWorkbook(saveFileDialog.FileName);
                        var ws = workbook.Worksheet(1);

                        ws.Range("C2:C100").AddConditionalFormat().WhenEqualOrLessThan(DateTime.Now.ToOADate()).Fill.SetBackgroundColor(XLColor.Red);
                        ws.Range("C2:C100").AddConditionalFormat().WhenGreaterThan(DateTime.Now.ToOADate() + 6).Fill.SetBackgroundColor(XLColor.Orange);
                        workbook.SaveAs(saveFileDialog.FileName);
                   }
                }
            }

Upvotes: 0

Related Questions