0

I am trying this function using Closed XML as found it to be best way to retain my formatting of datagrid values, however there is an error in the code, the reason why I wanted to use this was cause excel was loosing formatting of numbers when I used the old clipboard copy method of the data.

Does anybody no what the issue may be here. I think may be down to the i+2 when I have i + 1 for the header cells above ?.

The error is this line 

  worksheet.Cell(i + 2, j + 1).Value = dataGridView1.Rows[i].Cells[j].Value.ToString();

An unhandled exception of type 'System.NullReferenceException' occurred in SupplierProductUpdate.exe

Additional information: Object reference not set to an instance of an object.

I do not understand what is going wrong as there is 14 rows of data in the rows section 

public void ExportToExcelWithFormatting(DataGridView dataGridView1,string workSheetTitle,string fileName)
 {         
  if  (fileName!="")
            {

                var workbook = new XLWorkbook();
                var worksheet = workbook.Worksheets.Add(workSheetTitle);
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    worksheet.Cell(1, i + 1).Value = dataGridView1.Columns[i].Name;
                }

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

                        if (worksheet.Cell(i + 2, j + 1).Value.ToString().Length > 0)
                        {
                            XLAlignmentHorizontalValues align;

                            switch (dataGridView1.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 + 2, j + 1).Style.Alignment.Horizontal = align;

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

                            worksheet.Cell(i + 2, j + 1).Style.Font.FontName = dataGridView1.Font.Name;
                            worksheet.Cell(i + 2, j + 1).Style.Font.FontSize = dataGridView1.Font.Size;

                        }
                    }
                }
                worksheet.Columns().AdjustToContents();
                workbook.SaveAs(fileName);
                //MessageBox.Show("Done");
            }
        }
  • I was asking for help in specifics to do with my loop counters not specifically the null reference –  Aug 01 '17 at 09:10
  • The loop counters have nothing to do with the exception, so this is a duplicate. (The `i+2` is correct in the inner loop since there `i` is the row, not the column; the `i+1` from the header is the `j+1` in the inner loop.) – Raidri Aug 01 '17 at 09:31
  • 1
    Is `dataGridView1.Rows[i].Cells[j].Value` equal to `null` for any of the dataGrid's cells? – Francois Botha Aug 02 '17 at 09:49

0 Answers0