This is the 28th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021
Preface:
Blogger when doing the project requires the database data using DataGridView display, then put the display data export to Excel file, many times we will have a project to do download file button, we need to use Microsoft’s interface, Microsoft. Office. Interop. Excel, We need to import the references to the DataGridView data processing,. Use the Microsoft Office. Interop. Excel offers classes to export the data. The blogger writes down the specific operation steps to teach you to use ！！！！
Once a day, happiness is done
1. Create a form file and design the interface
Bloggers directly use the previous project, you need to be able to see the blogger’s previous articles
2. Import Microsoft. Office. Interop. Excel references
In the Windows project we find the reference, right click add Reference search Excel in the COM type library and find the reference Microsoft Excel 14.0
3. Double-click the generator to process the code
Double – click the button automatically generated trigger function, in the function to write code
3.1 References to namespaces
Adding in the namespace using Excel. = the Microsoft Office. Interop. Excel; This is the namespace.
3.2 Code Analysis
The blogger has commented on each line of code to make it easier to understand, using the file operation.
3.3 Note: the code here can be copied directly
The code of the operation of the blogger to export the file can be directly copied to the button you need to export the paste can be used, the premise is that you finish the previous steps.
3.3.1 Code presentation
The blogger has basically annotated every export file for everyone to read
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 System.Data.SqlClient; using MySql.Data.MySqlClient; using Excel = Microsoft.Office.Interop.Excel; namespace student { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) {} Private void button1_Click(Object Sender, EventArgs e) {string connString = "server=localhost; database=student; uid=root; pwd=88888888; Character Set=utf8;" ; MySqlConnection conn = new MySqlConnection(connString); MySqlCommand comm = new MySqlCommand(); comm.Connection = conn; try { conn.Open(); string sql = "select course_id ,course_name,teacher_naem from T_course"; MySqlDataAdapter da = new MySqlDataAdapter(sql, connString); DataSet ds = new DataSet(); da.Fill(ds,"studens"); dataGridView1.DataSource = ds; dataGridView1.DataMember ="studens"; conn.Close(); } catch (Exception ex) {messagebox.show (ex.Message, "error!" , MessageBoxButtons.OK, MessageBoxIcon.Exclamation); }} // Copy the code from here Private void button2_Click(Object Sender, EventArgs e) {string fileName = "IC00"; String saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); // Instantiate the file object savedialog.defaultext = "XLSX "; / / file. The default extension saveDialog Filter = "Excel file | *. XLSX"; // Gets or sets the current filename filter string that determines the selection that appears in the Save as file Type or File Type boxes of the dialog box. saveDialog.FileName = fileName; saveDialog.ShowDialog(); // Open the save window for you to select the path and set the FileName saveFileName = savedialog.filename; if (saveFileName.IndexOf(":") < 0) return; / / has been ordered to cancel Microsoft. Office. Interop. Excel. Application xlApp = new Microsoft. Office. Interop. Excel. The Application (); If (xlApp == null) {MessageBox.Show(" Unable to create Excel object, your computer may not have Excel installed "); return; } Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; / / Workbooks represents a Microsoft Excel Workbook. Microsoft Office. Interop. Excel. The Workbook Workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Create a new worksheet. The new worksheet will become an active worksheet. Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; Sheet1 // Write the title for (int I = 0; i < dataGridView1.ColumnCount; Cells[1, I + 1] = DatagridView1. Columns[I].HeaderText; Cells[1, I +1] for (int r = 0; r < dataGridView1.Rows.Count; R++) / / here represent data line mark, dataGridView1. Rows. Count Rows {for (int I = 0; i < dataGridView1.ColumnCount; Rows[r].cells [I].rows [r].cells [I].value; Cells[r + 2, I + 1] = Cells[r + 2, I + 1] DataGridView1. Rows [r]. Cells [I] Value for column r line I Value} System. Windows, Forms, Application. DoEvents (); / / updated form} worksheet. Columns. EntireColumn. AutoFit (); Show(fileName + "data saved successfully "," prompt ", messageboxbuttons.ok); If (saveFileName! = "")//saveFileName saveFileName is not empty {try {workbook.saved = true; // Gets or sets a value indicating whether the workbook has changed since it was last saved. Workbook.savecopyas (saveFileName); //fileSaved = true; } catch (Exception ex) {//fileSaved = false; Messagebox.show (" Error while exporting file, file may be being opened! \n" + ex.Message); } } xlApp.Quit(); GC.Collect(); }}}Copy the code4. Effect display
The Excel file will be exported in the path of your choice, and we can process the file.
Conclusion:
The operation is simple, but more or less will have some difficulty, we need to pay attention to add a reference or code might doesn’t work, in addition to the Workbooks to understand children’s shoes, it must be fast, it doesn’t matter if you don’t know about this can go to the Microsoft website, learning c # can see more of the official document, will be a great help to you, Well, it is not easy to pass it on.
