This year, it is 2019. Next year, 2020 will be added. We would like to be able to grab whatever the new column header is.
Based on your comment, I came up with this code.
First is sample data followed by the code I came up with
However, when I click to export data to excel, I get a message that object reference is not set to the instance of the object.
It is working good now.
I forgot to initialize the form with this:
InitializeComponent();
TransactionID Amount AmountOwed FullName Gender phone EmailAddress Address EventYear enelope_number Description Reg_Fee 2019
8 120.00 0.00 John J Doe Male 443-393-2022 jdoe@att.net 33333 ankara dr 2019-01-11 00:00:00.000 EV20199 Annual Dues 35.00 20.00
9 120.00 0.00 Carole Smith Male 443-111-2010 csmith@yahoo.com 342 Angel Str Atlanta, CA 20330 2019-01-12 00:00:00.000 EV5995 Annual Dues 35.00 60.00
10 120.00 70.00 James Ajukwara Male 678-222-1033 JAjukwara@yahoo.com 435 Williams Dr., Marietta, GA 30023 2019-01-13 00:00:00.000 EV4888 Annual Dues 35.00 50.00
namespace APOMembers
{
public partial class Form2 : Form
{
//private DataGridView dataGridView1 = new DataGridView();
private BindingSource bindingSource1 = new BindingSource();
private SqlDataAdapter dataAdapter = new SqlDataAdapter();
// Initialize the form.
public Form2()
{
dataGridView1.Dock = DockStyle.Fill;
//reloadButton.Text = "reload";
//submitButton.Text = "submit";
//reloadButton.Click += new System.EventHandler(reloadButton_Click);
//submitButton.Click += new System.EventHandler(submitButton_Click);
FlowLayoutPanel panel = new FlowLayoutPanel();
panel.Dock = DockStyle.Top;
panel.AutoSize = true;
//panel.Controls.AddRange(new Control[] { reloadButton, submitButton });
this.Controls.AddRange(new Control[] { dataGridView1, panel });
this.Load += new System.EventHandler(Form2_Load);
}
private void Form2_Load(object sender, System.EventArgs e)
{
// Bind the DataGridView to the BindingSource
// and load the data from the database.
dataGridView1.DataSource = bindingSource1;
GetData("select * from SummaryTable");
}
private void reloadButton_Click(object sender, System.EventArgs e)
{
// Reload the data from the database.
GetData(dataAdapter.SelectCommand.CommandText);
}
private void submitButton_Click(object sender, System.EventArgs e)
{
// Update the database with the user's changes.
dataAdapter.Update((DataTable)bindingSource1.DataSource);
}
private void GetData(string selectCommand)
{
try
{
// Specify a connection string. Replace the given value with a
// valid connection string for a Northwind SQL Server sample
// database accessible to your system.
String connectionString = ConfigurationManager.ConnectionStrings["Members"].ConnectionString;
// Create a new data adapter based on the specified query.
dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
// Create a command builder to generate SQL update, insert, and
// delete commands based on selectCommand. These are used to
// update the database.
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
// Resize the DataGridView columns to fit the newly loaded content.
dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
catch (SqlException)
{
MessageBox.Show("There is an issue with connection string.");
}
}
private void btnExportExcel_Click(object sender, EventArgs e)
{
//create datetime placeholder
DateTime curdate = DateTime.Now;
//Creating DataTable
DataTable dt = new DataTable();
//Adding the Columns
foreach (DataGridViewColumn column in dataGridView1.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
//Adding the Rows
foreach (DataGridViewRow row in dataGridView1.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in row.Cells)
{
dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
}
}
//Exporting to Excel
string folderPath = "C:\\Excel\\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Summary");
wb.SaveAs(folderPath + curdate + "APOReport.xlsx");
MessageBox.Show("Data Successfully exported to Excel file"
}
}
}
}
Thank you very much for the great feedback