To mark the title of the dview and export the Excel file, use the datagridviewexcel command.

To mark the title of the dview and export the Excel file, use the datagridviewexcel command.
Recently, I learned winform and was assigned a small task before the National Day. I decided to record it, so as to deepen my impression.

This is a function for marking and exporting the import ticket.

1. Select an excel file

2. Define Fields
Date and time the call duration of the recipient's number. Call type
3. Click the datagridview title to display the defined fields in the drop-down menu.
4. Mark the defined field Column
5. Save the definition field data to the datatable
6 Export datatable

As required, design the interface step by step. You need a dview and two buttons, one for import and one for export. I have added a Label and TextBox to indicate the file path.

First, define several global variables in the class, which will be used in the following code.

1 int colIndex; // the index of the clicked cell column 2 int rowIndex; // the index of the clicked cell Row 3 Dictionary <int, string> dic = new Dictionary <int, string> (); // store the excel Title 4 List <string> list = new List <string> (); // store the labeled Title 5 DataTable dt; // imported table6 string filename = ""; // Excel file nameView Code Step 1: Import Excel preview. I found a piece of code on the Internet to import the datagridview dview using excel, as shown below:1 private DataTable ExcelToDataTable (string path) 2 {3 4 FileStream fs = File. openRead (path); // open the. xls file 5 6 HSSFWorkbook wk = new HSSFWorkbook (fs); // write the data in the xls file to the 7 8 var sheet = wk in the wk file. getSheetAt (0); // extract the first sheet 9 var headerRow = sheet. getRow (0); // extract the first line of the sheet 10 var cellCount = headerRow. lastCellNum; // extract the last column of the row 11 DataTable table = new DataTable (); 12 // Add a column 13 for (int I = headerRow. firstCellN Um; I <cellCount; I ++) 14 {15 DataColumn col = new DataColumn (headerRow. getCell (I ). stringCellValue); 16 table. columns. add (col); 17} 18 // get the number of sheet rows 19 var rowCount = sheet. lastRowNum; 20 // cyclically write table21 for (int I = (sheet. firstRowNum + 1); I <rowCount; I ++) 22 {23 var row = sheet. getRow (I); 24 DataRow datarow = table. newRow (); 25 for (int j = row. firstCellNum; j <cellCount; j ++) 26 {27 if (row . GetCell (j )! = Null) 28 {29 datarow [j] = row. getCell (j ). toString (); 30} 31} 32 table. rows. add (datarow); 33} 34 wk = null; 35 sheet = null; 36 return table; 37}Import Method

This is an import method. Double-click the import button and add the following code to the event:

1 private void btnImport_Click (object sender, EventArgs e) 2 {3 OpenFileDialog ofd = new OpenFileDialog (); 4 ofd. initialDirectory = Environment. getFolderPath (Environment. specialFolder. desktop); 5 ofd. filter = "Excel Files (*. xls) | *. xls "; 6 if (ofd. showDialog ()! = DialogResult. OK) 7 {return;} 8 filename = ofd. fileName; 9 textBox1.Text = ofd. fileName; 10 dt = ExcelToDataTable (filename); 11 maid = dt; // ExcelToDataTable (ofd. fileName); 12 13 int ColCount = maid. count; 14 // save all the Excel titles to dic 15 for (int I = 0; I <ColCount; I ++) 16 {17 dic. add (I, dataGridView1.Columns [I]. headerText); 18} 19}Import Preview

To help me put the Excel document I tested on the desktop, I saved the Excel title to the dictionary and used it below.

The first step is basically implemented here. Click the import button, select an excel file, and display it on the datagridview.

 

Step 2 and Step 3: The required style is like this:

Click the datagridview title. A drop-down menu is displayed, showing the defined fields. Therefore, add a ContextMenuStrip control and add fields one by one.

You need to click the title pop-up drop-down menu. I used the CellCilck event of the dview. The code in this event is very simple. Just pop up contexmenustrip and then add a judgment on whether the row is the title line.

Step 4 of View Code is complex. The general idea is that the options of the drop-down menu are fixed. e. itemClicked. text can get the menu you choose, so I use the switch case statement to judge.

First, click the title of the datagridview column, and select a menu. The title of this column is changed to e. itemClicked, and mark this column, so I wrote a markup method to indicate the operation and style after marking.

1 private void Mark (string item) 2 {3 dt. columns [colIndex]. columnName = item; 4 dataGridView1.Columns [colIndex]. headerText = item; 5 maid [colIndex]. defaultCellStyle. backColor = Color. lightSteelBlue; 6 maid = false; 7 maid [colIndex]. headerCell. style. backColor = Color. lightSlateGray; 8}Mark

Because of the issues related to matching, for example, the columns in date format cannot be marked as "Call type" or "call time", so some regular expressions are added and the regular expressions have never been used before, it is also learned on the Internet and has a poor write performance.

I wrote another method after judgment:

1 private void MatchItem (string match, string str, string item) 2 {3 Match m = Regex. match (match, str); 4 if (m. success) 5 {6 Mark (item); 7 // if this column is marked, no item is added to list 8 foreach (var I in list) 9 {10 if (item = I) 11 return; 12} 13 list. add (item); 14} 15 else16 {17 contextMenuStrip1.Hide (); 18 MessageBox. show ("This column is not" + item + "! "); 19} 20}Format

For the contextmenustrip_itemClicked event, you only need to call the MatchItem method in each case.

1 private void contextmenustripincluitemclicked (object sender, ToolStripItemClickedEventArgs e) 2 {3 // dt = ExcelToDataTable (filename); 4 // obtain the Title 5 string item = e. clickedItem. text; 6 // obtain the content used for judgment. 7 var BeMatch = maid [rowIndex + 1]. cells [colIndex]. value. toString (); 8 // whether the current column already exists in the table 9 int I = 0; 10 foreach (DataColumn dataCol in dt. columns) 11 {12 if (I! = ColIndex & dataCol. columnName = item) 13 {14 contextMenuStrip1.Hide (); 15 var index = I + 1; 16 MessageBox. show ("This table" + index + "column" + dataCol. columnName + ", cannot mark"); 17 return; 18} 19 I ++; 20} 21 22 switch (item) 23 {24 case "Call type ": 25 {26 string strType = @ "[\ u4e00-\ u9fbb]"; 27 MatchItem (BeMatch, strType, item); 28} 29 break; 30 case "recipient's number ": 31 {32 string strNum = @ "0? [1] + [1, 358] + \ d {9} "; 33 MatchItem (BeMatch, strNum, item); 34} 35 break; 36 case" date ": 37 {38 string strDate = @ "^ 2 \ d {7} $"; 39 MatchItem (BeMatch, strDate, item); 40} 41 break; 42 case "time ": 43 {44 string strTime = @ "^ [0-2] \ d {1} [0-5] \ d {1} [0-5] \ d {1} $ "; 45 MatchItem (BeMatch, strTime, item); 46} 47 break; 48 case "Call duration": 49 {50 string strOften = @ "\ d"; 51 MatchItem (BeMatch, strOften, item); 52} 53 break; 54 case "Unset": 55 {56 // traverse the original title to get the column to unset, remove dt57 foreach (var kv in dic) 58 {59 if (kv. key = colIndex) 60 {61 list. remove (maid [colIndex]. headerText); 62 // list. insert (colIndex, kv. value); 63 item = kv. value; 64 dt. columns [colIndex]. columnName = item; 65} 66} 67 // restore title and style 68 maid [colIndex]. defaultCellStyle. backColor = Color. white; 69 dataGridView1.Columns [colIndex]. headerCell. style. backColor = DefaultBackColor; 70} 71 break; 72} 73}Step 5 of the markup column: Save the labeled field data to the able, I found a method on the internet, attached link: http://www.jb51.net/article/80620.htm 1 private void btnExport_Click (object sender, EventArgs e) 2 {3 // Add the tagged column to table 4 DataTable table = dt. defaultView. toTable (false, list. toArray (); 5 6 try 7 {8 SaveFileDialog sfd = new SaveFileDialog (); 9 // sfd. fileName = "test guide .xls"; 10 sfd. filter = "Excel Files (*. xls) | *. xls "; 11 sfd. fileName = "test export"; 12 if (sfd. showDialog () = DialogResult. OK) 13 {14 filename = sfd. fileName; 15 DataToExcel (table, filename, "phone", false); 16} 17} 18 catch (Exception ex) 19 {20 MessageBox. show (ex. message); 21}Export the marked Column

The running result is as follows:

 

This is basically the end. There is no major problem after running, and some bugs may not be tested yet. Sorry for the first time I wrote a blog. You are welcome to provide comments and suggestions. Thank you for your support!