Export DataGridView with grand Total to Excel file using ClosedXml in Windows Application

Last Reply 6 months ago By dharmendr

Posted 6 months ago

I displayed the data in the datagridview with checkbox.It contains 5 rows. sceenshoot is attached.

https://imgur.com/P3cl7yt

Now I want to export this data in excel with Total in the last row.

it is working fine. but there is an issue. it is exporting 4 rows instead of 5 row. Why this is missing one row?? where is the error and how to fix it??

for this purpose, i wrote the following code 

        private void mSExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                decimal Fee = 0;
                decimal Others = 0;
                decimal Recievable = 0;
                decimal Arrears = 0;
                decimal NetBal = 0;
                
                DataTable dt = new DataTable();
                foreach (DataGridViewColumn column in dGVStu.Columns)
                {
                    dt.Columns.Add(column.HeaderText); //, column.ValueType
                }
                foreach (DataGridViewRow row in dGVStu.Rows)
                {
                    if (dGVStu.Rows.Count - 1 > row.Index)
                    {
                        dt.Rows.Add();
                        foreach (DataGridViewCell cell in row.Cells)
                        {
                            dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
                        }
                    }
                }

                string folderPath = @"C:\Reports\";
                if (!Directory.Exists(folderPath))
                {
                    Directory.CreateDirectory(folderPath);
                }
                using (XLWorkbook wb = new XLWorkbook())
                {
                    wb.Worksheets.Add(dt, "DefaulterStudents");
                    wb.Worksheet(1).Cells("A1:R1").Style.Fill.BackgroundColor = XLColor.DarkGreen;
                    for (int i = 1; i <= dt.Rows.Count; i++)
                    {
                        Fee += Convert.ToDecimal(dGVStu.Rows[i].Cells[14].Value);
                        Others += Convert.ToDecimal(dGVStu.Rows[i].Cells[15].Value);
                        Recievable += Convert.ToDecimal(dGVStu.Rows[i].Cells[16].Value);
                        //Arrears += Convert.ToDecimal(dGVStu.Rows[i].Cells[15].Value);
                        Arrears += Convert.ToDecimal(dGVStu.Rows[i].Cells[17].Value == DBNull.Value ? "0" : dGVStu.Rows[i].Cells[17].Value.ToString());
                        NetBal += Convert.ToDecimal(dGVStu.Rows[i].Cells[18].Value);

                      
                        string cellRange = string.Format("A{0}:R{0}", i + 1);
                        if (i % 2 != 0)
                        {
                            wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.GreenYellow;
                        }
                        else
                        {
                            wb.Worksheet(1).Cells(cellRange).Style.Fill.BackgroundColor = XLColor.Yellow;
                        }

                    }
                    string Fe = "O" + (dt.Rows.Count + 2) + ":O" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Fe).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Fe).Value = Fee;

                    string Oth = "P" + (dt.Rows.Count + 2) + ":P" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Oth).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Oth).Value = Others;

                    string Reci = "Q" + (dt.Rows.Count + 2) + ":Q" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Reci).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Reci).Value = Recievable;

                    string Arr = "R" + (dt.Rows.Count + 2) + ":R" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Arr).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Arr).Value = Arrears;

                    string Net = "S" + (dt.Rows.Count + 2) + ":S" + (dt.Rows.Count + 2);
                    wb.Worksheet(1).Cells(Net).Style.Font.Bold = true;
                    wb.Worksheet(1).Cells(Net).Value = NetBal;                   

                    wb.Worksheet(1).Columns().AdjustToContents();
                    wb.SaveAs(folderPath + "DefaulterStudents- " + DateTime.Now.ToShortDateString() + ".xlsx");
                }
                MessageBox.Show("Defaulter Student's Data Exported Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

 

This question does not have replies that have been liked.