• 将数据填充到已有的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, string C_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 = new Microsoft.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                 int rowNum;
    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                 throw ex;
    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 = new DateTime();
    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             return savePath;
    209         }
  • 相关阅读:
    nginx中目录转义字符问题
    2022年3月2日最近的状态~
    [esp8266]官方SDK与arduino ROM或Flash布局,Vscode+platformio 如何设置
    什么是并发容器
    Executor, ExecutorService 和 Executors区别与联系
    Spirng和SpringBoot中的Aop优先使用的是JDK动态代理还是Cglib
    建造者模式
    MySQL四大排名函数
    SpringMVC执行流程解析(PS:使用了适配器模式)
    适配器模式
  • 原文地址:https://www.cnblogs.com/li-lun/p/5040084.html
Copyright © 2020-2023  润新知