Hello,
I'm trying to save data as excel from datagridview from another panel, but first I have to retrieve all from Mysql database.
private void btn_Retrieve_all_Click(object sender, EventArgs e)
{
string conn2 = "server=192.168.193.10;port=3306;Database=database;uid=root;pwd=123;CharSet=utf8mb4;";
string query2 = "SELECT * FROM equipments";
MySqlConnection connection2 = new MySqlConnection(conn2);
MySqlDataAdapter adapter2 = new MySqlDataAdapter(query2, connection2);
DataSet ds = new DataSet();
adapter2.Fill(ds, "equipments");
dataGridView1.DataSource = ds.Tables["equipments"];
frm1.saveAsToolStripMenuItem.Enabled = true;
frm1.excelToolStripMenuItem.Enabled = true;
frm1.pDFToolStripMenuItem.Enabled = true;
Delete_btn.Enabled = true;
Save_btn.Enabled = true;
Print_btn.Enabled = true;
}
When i retrieve all I'd like to save this data to excel:
private void excelToolStripMenuItem_Click(object sender, EventArgs e)
{
saFDialog1.InitialDirectory = "C";
saFDialog1.Title = "Save as Excel file";
saFDialog1.FileName = "";
saFDialog1.Filter = "Excel Files (2007)|*.xlsx|Excel Files(.CSV)|*.csv|Excel files (2003)|*.xls";
if(saFDialog1.ShowDialog()!= DialogResult.Cancel)
{
Microsoft.Office.Interop.Excel.Application xcelApp = new Microsoft.Office.Interop.Excel.Application();
xcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 0; i < frm2.dataGridView1.Columns.Count + 1; i++)
{
xcelApp.Cells[1, i] = frm2.dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < frm2.dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < frm2.dataGridView1.Columns.Count; j++)
{
xcelApp.Cells[i + 2, j + 1] = frm2.dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
xcelApp.ActiveWorkbook.SaveCopyAs(saFDialog1.FileName.ToString());
xcelApp.ActiveWorkbook.Saved = true;
xcelApp.Visible = true;
}
However i added line code to declare opened form because datagridview is in another form (this case panel):
ITDeviceInfo frm2 = (ITDeviceInfo)Application.OpenForms["ITDeviceInfo"];
When I launched this application written in C# .NET and MySQL database I retrieved all then I chose [save] -> [Excel]. When i confirmed the place to save this excel file then appears this fail information:
Quote:
System.NullReferenceException: "Object reference not set to an instance of an object.
And indicates at code line:
for (int i = 0; i < frm2.dataGridView1.Columns.Count + 1; i++)
I have a question, what should I change at this code which saves the excel file from datagridview? Any Ideas? Thx for any help.
What I have tried:
In below are code sources:
Menu.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace IT_equipment_program
{
public partial class Menu : Form
{
public Menu()
{
InitializeComponent();
}
ITDeviceInfo frm2 = (ITDeviceInfo)Application.OpenForms["ITDeviceInfo"];
private void iTDeviceInfoMNGToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenForm<ITDeviceInfo>();
}
private void OpenForm<Myform>() where Myform : Form, new()
{
Form formular;
formular = panelForm.Controls.OfType<Myform>().FirstOrDefault();
if (formular == null)
{
formular = new Myform();
formular.TopLevel = false;
formular.FormBorderStyle = FormBorderStyle.None;
formular.Dock = DockStyle.Fill;
panelForm.Controls.Add(formular);
panelForm.Tag = formular;
formular.Show();
formular.BringToFront();
}
else
{
formular.BringToFront();
}
}
private void Menu_FormClosed(object sender, FormClosedEventArgs e)
{
Application.Exit();
}
private void excelToolStripMenuItem_Click(object sender, EventArgs e)
{
saFDialog1.InitialDirectory = "C";
saFDialog1.Title = "Save as Excel file";
saFDialog1.FileName = "";
saFDialog1.Filter = "Excel Files (2007)|*.xlsx|Excel Files(.CSV)|*.csv|Excel files (2003)|*.xls";
if(saFDialog1.ShowDialog()!= DialogResult.Cancel)
{
Microsoft.Office.Interop.Excel.Application xcelApp = new Microsoft.Office.Interop.Excel.Application();
xcelApp.Application.Workbooks.Add(Type.Missing);
for (int i = 0; i < frm2.dataGridView1.Columns.Count + 1; i++)
{
xcelApp.Cells[1, i] = frm2.dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < frm2.dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < frm2.dataGridView1.Columns.Count; j++)
{
xcelApp.Cells[i + 2, j + 1] = frm2.dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
xcelApp.ActiveWorkbook.SaveCopyAs(saFDialog1.FileName.ToString());
xcelApp.ActiveWorkbook.Saved = true;
xcelApp.Visible = true;
}
}
}
}
ITDeviceInfo.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DGVPrinterHelper;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using Font = System.Drawing.Font;
using System.Text.RegularExpressions;
using System.Diagnostics;
using MySql.Data.MySqlClient;
namespace IT_equipment_program
{
public partial class ITDeviceInfo : Form
{
MySqlConnection connection;
MySqlCommand command;
MySqlDataReader dr;
public ITDeviceInfo()
{
InitializeComponent();
}
Menu frm1 = (Menu)Application.OpenForms["Menu"];
private void Exit_btn_Click(object sender, EventArgs e)
{
this.Close();
frm1.saveAsToolStripMenuItem.Enabled = false;
frm1.excelToolStripMenuItem.Enabled = false;
frm1.pDFToolStripMenuItem.Enabled = false;
}
private void btn_Retrieve_all_Click(object sender, EventArgs e)
{
string conn2 = "server=192.168.193.10;port=3306;Database=database;uid=root;pwd=123;CharSet=utf8mb4;";
string query2 = "SELECT * FROM equipments";
MySqlConnection connection2 = new MySqlConnection(conn2);
MySqlDataAdapter adapter2 = new MySqlDataAdapter(query2, connection2);
DataSet ds = new DataSet();
adapter2.Fill(ds, "equipments");
dataGridView1.DataSource = ds.Tables["equipments"];
frm1.saveAsToolStripMenuItem.Enabled = true;
frm1.excelToolStripMenuItem.Enabled = true;
frm1.pDFToolStripMenuItem.Enabled = true;
Delete_btn.Enabled = true;
Save_btn.Enabled = true;
Print_btn.Enabled = true;
}
}
}