C#•7mo ago

✅ How to export data from datagridview into excel file

I've tried like a few ways and cant really make sense of it, if someone can guide me through it it would be nice, thanks in advance 🙂

17 Replies

UnawareModestyOP•7mo ago

I guess note: I use Visual Studio 2022

SpReeD•7mo ago

If you're data is a DataTable or DataSet it only takes a line of code with one of these excel frameworks: https://github.com/ClosedXML/ClosedXML https://www.epplussoftware.com/

UnawareModestyOP•7mo ago

its datagridview

SpReeD•7mo ago

A DataGridView is a control, it holds the data within DataSource

UnawareModestyOP•7mo ago

what is epplus and whats the line of code youre talking bout

SpReeD•7mo ago

As I wrote, an excel framework like ClosedXML It depends on your data, DataSource holds object, which can be anything

UnawareModestyOP•7mo ago

i mean the columns from the table im pulling are string and int but idk what youre trying to explain to me rn

SpReeD•7mo ago

How do you fill your DataGridView?

UnawareModestyOP•7mo ago

with a datareader i guess more specificaly this is the code

            string connectionString12 = null;
            SqlConnection cnn12;
            SqlCommand command12;
            SqlDataReader dataReader12;
            connectionString12 = GlobalConnection.decoyconnection;
            cnn12 = new SqlConnection(connectionString12);
            cnn12.Open();
            string sql12 = $"select ppi.LocationID, pl.Name as LocationName, ppi.ProductID, pp.Name as ProductName, ppi.Quantity from production.ProductInventory ppi\r\njoin production.Product pp\r\non ppi.ProductID = pp.ProductID\r\njoin production.Location pl\r\non pl.LocationID = ppi.LocationID\r\norder by ppi.LocationID";
            command12 = new SqlCommand(sql12, cnn12);
            SqlDataAdapter da12 = new SqlDataAdapter(command12);
            DataTable dt12 = new DataTable();
            da12.Fill(dt12);
            dataGridView1.DataSource = dt12;
            command12.Dispose();
            cnn12.Close();

            string connectionString12 = null;
            SqlConnection cnn12;
            SqlCommand command12;
            SqlDataReader dataReader12;
            connectionString12 = GlobalConnection.decoyconnection;
            cnn12 = new SqlConnection(connectionString12);
            cnn12.Open();
            string sql12 = $"select ppi.LocationID, pl.Name as LocationName, ppi.ProductID, pp.Name as ProductName, ppi.Quantity from production.ProductInventory ppi\r\njoin production.Product pp\r\non ppi.ProductID = pp.ProductID\r\njoin production.Location pl\r\non pl.LocationID = ppi.LocationID\r\norder by ppi.LocationID";
            command12 = new SqlCommand(sql12, cnn12);
            SqlDataAdapter da12 = new SqlDataAdapter(command12);
            DataTable dt12 = new DataTable();
            da12.Fill(dt12);
            dataGridView1.DataSource = dt12;
            command12.Dispose();
            cnn12.Close();

SpReeD•7mo ago

So, it's a DataTable ... A DataTable is an object of representation of a Table, so with both frameworks it's easy to export it to an Excel Table. For instance, with ClosedXML:

XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add((DataTable)dataGridView1.DataSource,"WorksheetName");
wb.SaveAs(myPath);

XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add((DataTable)dataGridView1.DataSource,"WorksheetName");
wb.SaveAs(myPath);

UnawareModestyOP•7mo ago

ok thank you it finally works i guess i need to put a separate thing to be able to change the name of the file everytime cause it kinda doesnt work if you want to put one with a duplicate name

SpReeD•7mo ago

A duplicate file? another Worksheet? An Excel file or Workbook is a container of Worksheets or Tables. However, I encourage you to read the docs for detailed information on each function of ClosedXML: https://docs.closedxml.io/en/latest/

UnawareModestyOP•7mo ago

well the path for mypath requires at teh end a name for the created file, example "c::\users\decoyuser\desktop\name_of_file.xlsx"

SpReeD•7mo ago

Indeed

UnawareModestyOP•7mo ago

thats kidna an easy solve tho to check and be able to create names for the files ill be sure to read the doc thanks for the help :peepohappy:

SpReeD•7mo ago

I guess what you describe is a rolling filename, yes you need another function for that; since it has nothing to do with Excel or DataTable etc.

UnawareModestyOP•7mo ago

yeye i might read on that thank you tho :NPCNurseThumbsUp:

Gaming

Programming

✅ How to export data from datagridview into excel file