I have created a dataset
and store 5 datatables
in it. Each datatable contains fix number of columns and rows. One of the columns is a checkbox column
. I would like to loop through these 5 datatables
and retrieve only the checkbox values. When the form loads, the checkbox values are empty and up to user to check or uncheck. Then user press button4
to export the checkbox values to excel.
The thing is when I press button4
, all the checkbox values change to empty (those user checked become unchecked) and the error specified cast is not valid at line foreach (bool item in row.ItemArray)
occurred. For example below, I only include 2 out of the 5 datatables (datagridview1 & datagridview2) so that it won’t be too lengthy. I am very new to these datasets, database etc logic so hope to get some help.
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private DataSet GetDataSet()
{
DataSet ds = new DataSet();
DataTable dt1 = new DataTable();
dt1.Columns.Add("Items", typeof(string));
dt1.Columns.Add("Status", typeof(bool));
dt1.Rows.Add("hello");
dt1.Rows.Add("hello");
ds.Tables.Add(dt1);
datagridview1.DataSource = dt1;
datagridview1.AllowUserToAddRows = false;
DataTable dt2 = new DataTable();
dt2.Columns.Add("Items", typeof(string));
dt2.Columns.Add("Status", typeof(bool));
dt2.Rows.Add("bye");
dt2.Rows.Add("bye");
ds.Tables.Add(dt2);
datagridview2.DataSource = dt2;
datagridview2.AllowUserToAddRows = false;
return ds;
}
private void Form1_Load(object sender, EventArgs e)
{
Getdataset();
}
private void ExportDataSetToExcel(DataSet ds, string strPath)
{
strPath = @"C:\\Trial.xlsx";
Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook oWB;
Excel._Worksheet oSheet;
oWB = oXL.Workbooks.Open(strPath);
oSheet = (Excel.Worksheet)oXL.Worksheets.Item[1];
//loop each row in each datatable and retrieve checkbox values
foreach (DataTable table in ds.Tables)
{
foreach (DataRow row in table.Rows)
{
foreach (bool item in row.ItemArray)
{
oSheet.Cells[1, 1] = item.ToString();
}
}
}
oXL.Visible = true;
oXL.UserControl = true;
oWB.Save();
}
private void button4_Click(object sender, EventArgs e)
{
string filepath = "C:\\Trial.xlsx";
DataSet dsdata = Getdataset();
ExportDataSetToExcel(dsdata, filepath);
}
Aucun commentaire:
Enregistrer un commentaire