摘要：
导出execl网上一大堆，最近遇到将数据导出到已有的execl模板中，具体到某行列，动态加载数据。
导出execl网上一大堆，最近遇到将数据导出到已有的execl模板中，具体到某行列，动态加载数据。
添加Microsoft.Office.Interop.Excel 引用
1 /// <summary> 2 ///DataGridView 导出到execl模板中 3 /// </summary> 4 /// <param name="fileName">execl模板路径</param> 5 /// <param name="dgv">数据源</param> 6 /// <param name="index">多种模板情况下 通过判断不同的模板 不需要可删</param> 7 /// <param name="C_AGENCYNAME">exec文件名 不需要可删</param> 8 /// <returns></returns> 9 public string mainaa(string fileName, DataGridView dgv, int index, stringC_AGENCYNAME) 10 { 11 12 //string fileName = @"d:/模板文件.xls"; 13 string savePath = ""; 14 Microsoft.Office.Interop.Excel.Application app = null; 15 Microsoft.Office.Interop.Excel.Workbooks wbs = null; 16 Microsoft.Office.Interop.Excel.Workbook wb = null; 17 Microsoft.Office.Interop.Excel.Worksheet s = null; 18 19 //用与计算不同模板的合计 20 float countsg = 0; 21 float countsh = 0; 22 float countzh = 0; 23 float countfw = 0; 24 float countws = 0; 25 try 26 { 27 app = newMicrosoft.Office.Interop.Excel.Application(); 28 app.DisplayAlerts = false; 29 wbs =app.Workbooks; 30 //读取模板 31 wb =wbs.Open(fileName, Type.Missing, Type.Missing, Type.Missing, 32 Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 33 Type.Missing, Type.Missing, Type.Missing, Type.Missing, 34 Type.Missing, Type.Missing, Type.Missing); 35 s = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1]; 36 Microsoft.Office.Interop.Excel.Range rangeMonth; 37 //假设用模板1 中是从第18行开始写，写入6列 38 if (index == 1) 39 { 40 //从第1行开始 41 rangeMonth = s.get_Range("A1"); 42 //设置7,8,9,17行 四行将要写如的数据 43 rangeMonth.Cells[7, 1] = "主题：" + C_AGENCYNAME + "手续费确认函"; 44 rangeMonth.Cells[8, 1] = "统计日期：" + DateTime.Now.Year.ToString() + "年" + DateTime.Now.Month.ToString() + "月"; 45 rangeMonth.Cells[9, 1] = C_AGENCYNAME + ":"; 46 rangeMonth.Cells[17, 2] =C_AGENCYNAME; 47 //设置模板1 之后的表格数据 从第19行开始写 48 rangeMonth = s.get_Range("A19"); 49 50 } 51 else if (index == 2) 52 { 53 rangeMonth = s.get_Range("A3"); 54 } 55 else 56 { 57 rangeMonth = s.get_Range("A1"); 58 rangeMonth.Cells[6, 1] = "收件人：" +C_AGENCYNAME; 59 rangeMonth.Cells[11, 1] = "主题：" + C_AGENCYNAME + "信诚基金尾随佣金确认函"; 60 rangeMonth.Cells[13, 1] = C_AGENCYNAME + ":"; 61 rangeMonth = s.get_Range("A19"); 62 63 } 64 65 System.Windows.Forms.Clipboard.SetDataObject(dgv.Columns[0].HeaderText); 66 67 //生成字段名称 68 int row = 0; 69 for (int i = 0; i < dgv.ColumnCount; i++) 70 { 71 rangeMonth.Cells[1 , i + 1 + row] =dgv.Columns[i].HeaderText; 72 if(index==3) 73 { 74 row += 2; 75 } 76 } 77 //填充数据 78 for (int i = 0; i < dgv.RowCount - 1; i++) 79 {//控制行 80 81 //基金手续费 合计 累加 82 if (index == 1) 83 { 84 countsg += float.Parse(dgv[2, i].Value.ToString()); 85 countsh += float.Parse(dgv[3, i].Value.ToString()); 86 countzh += float.Parse(dgv[4, i].Value.ToString()); 87 countfw += float.Parse(dgv[5, i].Value.ToString()); 88 } 89 for (int j = 0; j < dgv.ColumnCount; j++)//控制列 90 { 91 92 if (dgv[j, i].ValueType == typeof(string)) 93 { 94 rangeMonth.Cells[i + 2, j + 1] = "'" +dgv[j, i].Value.ToString(); 95 } 96 else 97 { 98 99 int rows = 0; 100 if(index==3) 101 { 102 rows = 2; 103 } 104 rangeMonth.Cells[i + 2, j + 1 + rows] =dgv[j, i].Value.ToString(); 105 106 } 107 //count += float.Parse(dgv[4, j + 1].Value.ToString()); 108 109 } 110 if (index == 3) 111 { 112 countws += float.Parse(dgv[1, i].Value.ToString()); 113 } 114 } 115 rangeMonth.Cells[dgv.RowCount + 1, 1] = "合计"; 116 if (index == 1)//基金手续费合计 117 { 118 rangeMonth.Cells[dgv.RowCount + 1, 3] =countsg.ToString(); 119 rangeMonth.Cells[dgv.RowCount + 1, 4] =countsh.ToString(); 120 rangeMonth.Cells[dgv.RowCount + 1, 5] =countzh.ToString(); 121 rangeMonth.Cells[dgv.RowCount + 1, 6] =countfw.ToString(); 122 rangeMonth.EntireColumn.AutoFit(); //自动调整列宽 123 rangeMonth.EntireRow.AutoFit(); //自动设置行高 124 } 125 if (index == 3) 126 { 127 rangeMonth.Cells[dgv.RowCount + 1,4] =countws.ToString(); 128 rangeMonth.Cells[dgv.RowCount +2, 4] = "公司"; 129 rangeMonth.Cells[dgv.RowCount +3, 4] = DateTime.Now.Year.ToString()+"年"+DateTime.Now.Month.ToString()+"月"+DateTime.Now.Day.ToString()+"日"; 130 } 131 //循环最后一列添加背景颜色 132 133 introwNum; 134 if (index == 3) 135 { 136 rowNum = 4; 137 } 138 else 139 { 140 rowNum =dgv.ColumnCount; 141 } 142 for (int i = 1; i <= rowNum; i++) 143 { 144 rangeMonth.Cells[dgv.RowCount + 1, i].Interior.Color = Color.FromArgb(220, 230, 241); 145 rangeMonth.Cells[dgv.RowCount + 1, i].RowHeight = 20.25; 146 rangeMonth.Cells[dgv.RowCount + 1, i].Font.Name = "宋体";//设置字体 147 rangeMonth.Cells[dgv.RowCount + 1, i].Font.Size = 16;//字体大小 148 rangeMonth.Cells[dgv.RowCount + 1, i].Font.Bold = true;//加粗显示 149 } 150 s.Paste(rangeMonth, false); 151 152 153 } 154 catch(Exception ex) 155 { 156 157 throwex; 158 } 159 finally 160 { 161 try 162 { 163 164 //E:CodeFEEFEEExeclAndPdfExecl手续费模板.xlsx 165 int num =fileName.Length; 166 string[] execlnamestr = fileName.Split('\'); 167 string ExeclName = DateTime.Now.Year.ToString() + "年" +DateTime.Now.Month.ToString() 168 + "月" + C_AGENCYNAME + execlnamestr[execlnamestr.Length - 1]; 169 //获取路径 170 string s1 =System.AppDomain.CurrentDomain.BaseDirectory; 171 string q = s1.Substring(0, s1.Length - 15); 172 //设置存放execl 的路径 173 savePath = System.IO.Path.Combine(q, "ExeclAndPdf\SaveExecl\" +ExeclName).ToString(); 174 wb.SaveAs(savePath); 175 if (wb != null) 176 wb.Close(true, Type.Missing, false); 177 if (wbs != null) 178 wbs.Close(); 179 if (app != null) 180 app.Quit(); 181 wb = null; 182 wbs = null; 183 app = null; 184 } 185 catch 186 { 187 } 188 189 GC.Collect(); 190 #region 强行杀死最近打开的Excel进程 191 System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); 192 System.DateTime startTime = newDateTime(); 193 int m, killID = 0; 194 for (m = 0; m < excelProc.Length; m++) 195 { 196 if (startTime <excelProc[m].StartTime) 197 { 198 startTime =excelProc[m].StartTime; 199 killID =m; 200 } 201 } 202 if (excelProc[killID].HasExited == false) 203 { 204 excelProc[killID].Kill(); 205 } 206 #endregion 207 } 208 returnsavePath; 209 }