Merge the same content columns in Table + merge the same content columns in Excel ., Tableexcel

Merge the same content columns in Table + merge the same content columns in Excel ., Tableexcel

It is easier to merge columns with the same content in a Table, and there are a lot of code on the Internet.

Plug-ins, calling is still quite useful.

This place has been slightly modified. Sometimes the content in td is the same, but the value in the database is different. For example, different companies have personnel departments, finance departments,

Company A's Finance Department and Company B's Finance Department cannot be merged, so I added A name attribute to td. Used to save the Department ID. The Department IDs of each company in the group are different.

Call Method

$ (Function (){

$ ("# Tbdianbao"). rowspan (1); // merge the 2nd columns,
$ ("# Tbdianbao"). rowspan (2); // merge the 3rd Columns

});

?

12345678910111213141516171819202122232425262728293031323334353637383940414243

(function($) {     $.fn.extend({        // The name used to call the plug-in. The input is the column index 0, 1, 2 ,,,        "rowspan": function(colIdx) {             var that;            var rowspan;            // Traverse rows in the table            $(this).find("tr").each(function(row) {            // Obtain the set of the index column, and filter it with filter to Obtain visible columns, and then traverse them.                $('td:eq(' + colIdx + ')', this).filter(':visible').each(function(col) {                    // The content of the upper and lower columns is the same                     if (that != null && $<span style="color: #ff0000;">(this).attr("name") == $(that).attr("name"))</span> {                         rowspan = $(that).attr("rowSpan");                        // If this is the first time that two columns are merged                        if (rowspan == undefined) {                            $(that).attr("rowSpan", 1);                            rowspan = $(that).attr("rowSpan");                        }                                                 rowspan = Number(rowspan) + 1;                        $(that).attr("rowSpan", rowspan);                        // Hide the column                        $(this).hide();                     }                    else {                        // Save the object in the previous column.                        that = this;                    }                 });            });         }     }); })(jQuery);

We may need to import the generated table to Excel and merge the same columns. We cannot afford to worry about VBA, so we can only judge the merged columns when generating an Excel file.

The columns with the same company ID in the two columns are merged from the excel 3rd row, and the columns with the same department ID in the three columns are merged.

This. margeCol (worksheet, 2, 2, dtExcel, "GongSiID ");
This. margeCol (worksheet, 2, 3, dtExcel, "BuMenId ");

1 /// <summary> 2 /// merge column 3 in Excel /// </summary> 4 /// <param name = "worksheet"> workbook </param> 5 /// <param name = "rno"> the first row of the workbook </param> 6 /// <param name = "cno"> Start column of the workbook </param> 7 // <param name = "dttemp"> bind table </param> 8 // <param name = "colName"> merge columns </param> 9 private void margeCol (Microsoft. office. interop. excel. _ Worksheet worksheet, int rno, int cno, DataTable dttemp, string colName) 10 {11 string dcvalu E = string. Empty; 12 13 int sron = rno; // start line 14 15 foreach (DataRow dr in dttemp. Rows) {16 17 if (dcvalue! = String. empty & dcvalue = dr [colName]. toString () 18 {19 rno = rno + 1; // accumulate 20} 21 else22 {23 dcvalue = dr [colName]. toString (); 24 25 // when the target row is larger than the start row, it indicates that the content of the row is 26 if (rno> sron) {27 worksheet. get_Range (worksheet. cells [sron, cno], worksheet. cells [rno, cno]). merge (worksheet. get_Range (worksheet. cells [sron, cno], worksheet. cells [rno, cno]). mergeCells); 28} 29 30 // after the merge, the starting row moves down a row 31 rno = rno + 1; 32 sron = rno; 33 34} 35} 36 37}