I have a following code to display data in datagridview in windows form
CheckBox headerCheckBox = new CheckBox();
private void DisplayDefaulter()
{
con = new SqlDbConnect();
con.SqlQuery(@"select fg.AdmissionNo,SName,FName,fg.Year,fg.ClassID,ClassName,fg.SectionID,SectionName,FPhone,DueDate,Fees,sum(Price) 'Others',ISNULL(Fees,0) + ISNULL(sum(Price),0) 'Recievable',RemBal 'Arrears'
,isnull(( ISNULL(Fees,0) + ISNULL(sum(Price),0)),0) + isnull((RemBal),0) 'NetBal',fg.Month
from tblFeesGenerate as fg left join tblFeesCollection as fc on fg.AdmissionNo=fc.AdmissionNo inner join tblStdReg as sr on fg.AdmissionNo=sr.AdmissionNo
inner join tblDefClass as dc on fg.ClassID=dc.ClassID inner join tblDefSection as ds on fg.SectionID=ds.SectionID
where fc.AdmissionNo is null or fc.AdmissionNo is not null and
fg.Year = @Year and fg.Month=@Month and fg.YearID=@YId and
fc.AdmissionNo not IN(SELECT AdmissionNo FROM tblFeesCollection WHERE FeesMonth = @Month AND Paid >= 0 )
and FeeID IN (SELECT MAX(FeeID) FROM tblFeesCollection group by AdmissionNo)
Group by fg.AdmissionNo,fg.Month,RemBal,SName,FName,fg.Year,fg.ClassID,ClassName,fg.SectionID,SectionName,FPhone,DueDate,Fees order by AdmissionNo");
con.Cmd.Parameters.Add(new SqlParameter("@Year", this.cmbPYear.SelectedItem.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@Month", this.cmbMonth.SelectedItem.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@YId", this.cmbYear.SelectedValue.ToString()));
/* con.Cmd.Parameters.Add(new SqlParameter("@CId", this.cmbClass.SelectedValue.ToString()));
con.Cmd.Parameters.Add(new SqlParameter("@SId", this.cmbSection.SelectedValue.ToString()));*/
paging.SelectCommand = con.Cmd;
sBuilder = new SqlCommandBuilder(paging);
ds = new DataSet();
paging.Fill(ds, "tblFeesCollection");
sTable = ds.Tables["tblFeesCollection"];
dGVStu.DataSource = ds.Tables["tblFeesCollection"].DefaultView;
lblStu.Text = ds.Tables[0].Rows.Count.ToString();
//dGVStu.ReadOnly = true;
Point headerCellLocation = this.dGVStu.GetCellDisplayRectangle(0, -1, true).Location;
headerCheckBox.Location = new Point(headerCellLocation.X + 8, headerCellLocation.Y + 1);
headerCheckBox.BackColor = System.Drawing.Color.White;
headerCheckBox.Size = new Size(18, 18);
headerCheckBox.Click += new EventHandler(HeaderCheckBox_Clicked);
dGVStu.Controls.Add(headerCheckBox);
DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
checkBoxColumn.HeaderText = "";
checkBoxColumn.Width = 30;
checkBoxColumn.Name = "checkBoxColumn";
dGVStu.Columns.Insert(0, checkBoxColumn);
}
in this above the Arrears column has no values so all these cell are blank under Arrears column. Now I want to export these data to excel but it is showing me error
'dataType' argument cannot be null. Parameter name: dataType
and the line inside foreach loop throwing the error is
foreach (DataGridViewColumn column in dGVStu.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
code for export in excel
DataTable dt = new DataTable();
foreach (DataGridViewColumn column in dGVStu.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
foreach (DataGridViewRow row in dGVStu.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in row.Cells)
{
// dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
dt.Rows[dt.Rows.Count - 1][dGVStu.Rows[i].Cells[j - 1].ColumnIndex] = "" ? 0 : Convert.ToDecimal((dGVStu.Rows[i].Cells[j].Value));
}
}
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:C1").Style.Fill.BackgroundColor = XLColor.DarkGreen;
for (int i = 1; i <= dt.Rows.Count; i++)
{
string cellRange = string.Format("A{0}:C{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;
}
}
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);