C# 下载excel模板,并将数据导进模板excel

           这段时间事情得比较多,各式各样的都有,就拿将数据汇出excel来说吧,有直接汇出的,有需要按格式汇出的,还有需要设置颜色汇出。。。一堆,今天我就讲讲说怎样从ftp下载excel，然后再将数据汇到改excel

         首先,需要去服务器放一个excel模板，这个模板的格式按照自己的需求去调,下图我是随便命名的~~~

        

现在来看代码部分，首先是新建一个操作ftp类,我命名为DownLoad，代码如下：

 public class DownLoad
    {
        string ftpURI;
        string ftpUserID;
        string ftpPassword;
        string ftpServerIP;
         string ftpRemotePath;


        /// <summary>
        /// 连接FTP
        /// </summary>
        /// <param name="FtpServerIP">FTP连接地址</param>
        /// <param name="FtpRemotePath">指定FTP连接成功后的当前目录, 如果不指定即默认为根目录</param>
        /// <param name="FtpUserID">用户名</param>
        /// <param name="FtpPassword">密码</param>
        public DownLoad(string FtpServerIP, string FtpRemotePath, string FtpUserID, string FtpPassword)
        {
            ftpServerIP = FtpServerIP;
            ftpRemotePath = FtpRemotePath;
            ftpUserID = FtpUserID;
            ftpPassword = FtpPassword;
            ftpURI = "ftp://" + ftpServerIP + "/";
        }


        /// <summary>
        /// 下载
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="fileName"></param>
        public string Download()
        {
            FtpWebRequest reqFTP;
            string downFile = "";
            try
            {
                // FileStream outputStream = new FileStream(filePath + "\\" + fileName, FileMode.Create);
                DateTime DT = System.DateTime.Now;
                if (Directory.Exists("D:\\EXCEL") == false)//如果不存在就创建file文件夹
                {
                    Directory.CreateDirectory(@"D:\EXCEL");
                }

                downFile = @"D:\EXCEL\" + "model.xls";
                FileStream outputStream = new FileStream(downFile, FileMode.Create);


                reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://地址"));
                reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
                reqFTP.UseBinary = true;
                reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpURI + "model.xls"));
                FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
                Stream ftpStream = response.GetResponseStream();
                long cl = response.ContentLength;
                int bufferSize = 2048;
                int readCount;
                byte[] buffer = new byte[bufferSize];
                readCount = ftpStream.Read(buffer, 0, bufferSize);
                while (readCount > 0)
                {
                    outputStream.Write(buffer, 0, readCount);
                    readCount = ftpStream.Read(buffer, 0, bufferSize);
                }
                ftpStream.Close();
                outputStream.Close();
                response.Close();

            }
            catch (Exception ex)
            {
                throw new Exception("lo_ExXls Fail,Message is 11 " + ex.Message);
            }
            return downFile;
        }
    }

下载该excel

try
            {
                if (File.Exists(Environment.CurrentDirectory + @"\EXCEL\model.xls") == false)
                {
                    Console.ReadLine();
                    DownLoad fw = new DownLoad("MECLEDAAP1", "", "用戶名", "密碼"); 
                    MessageBox.Show(fw.Download());
                }
            }
            catch (Exception ex)
            {
                throw new Exception("lo_ExXls Fail,Message is 11 " + ex.Message);
             }

点击汇出按钮

#region Event 匯¡Ñ出DX表i格a
        private void butExcel_Click(object sender, RoutedEventArgs e)
        {
                 System.Windows.Forms.SaveFileDialog saveExcel = new System.Windows.Forms.SaveFileDialog();
            saveExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007 Files(*.xlsx)|*.xlsx|All Files(*.*)|(*.*)";


            saveExcel.RestoreDirectory = true;
            string fileName = "";
            saveExcel.FileName = fileName;
            string ls_FileName = "";
            if (saveExcel.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                ls_FileName = saveExcel.FileName;
                SetExexl(ls_FileName);
                MessageBox.Show("導出Execl成功!", "導出", MessageBoxButton.OK, MessageBoxImage.Information);
            }
        }

 //數據放入execl
        public void SetExexl(string ls_FileName)
        {

            try
            {

                //需要添加 Microsoft.Office.Interop.Excel引用 
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
                app.UserControl = true;
                Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
                Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add("D:\\EXCEL\\model.xls"); //加载模板
                Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
                Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); //第一个工作薄。
                if (worksheet == null)
                    return;  //工作薄中没有工作表.

                #region execl內容填充
              
                try
                {
                    for (int i = 1; i <= lo_DTbEmp.Rows.Count ; i++)
                    {
                        int row_ =  5 + i;  //Excel模板上表头和标题行占了2行,根据实际模板需要修改;
                        int dt_row = i - 1; //dataTable的行是从0开始的。 
                        worksheet.Cells[row_, 1] = lo_DTbEmp.Rows[dt_row]["EmpNo"].ToString();
                        worksheet.Cells[row_, 2] = lo_DTbEmp.Rows[dt_row]["EmpName"].ToString();
                        worksheet.Cells[row_, 3] = lo_DTbEmp.Rows[dt_row]["Deptname"].ToString();
                        worksheet.Cells[row_, 4] = lo_DTbEmp.Rows[dt_row]["PostName"].ToString();
                        worksheet.Cells[row_, 5] = lo_DTbEmp.Rows[dt_row]["InDate"].ToString();
                        worksheet.Cells[row_, 6] = lo_DTbEmp.Rows[dt_row]["ColSize"].ToString();                  
    
                    }

                    //调整Excel的样式。
                    worksheet.Columns.AutoFit(); //自动调整列宽。



                    workbook.SaveAs(ls_FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
                        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                }
                catch (Exception lo_ExXls)
                {
                    throw new Exception("lo_ExXls Fail,Message is 11 " + lo_ExXls.Message, lo_ExXls);

                }
                finally
                {

                    #region 資源釋放
                    wb.Close(false, Missing.Value, Missing.Value);
                    NAR(wb);
                    NAR(range);
                    excel.Quit();
                    NAR(excel);
                    System.GC.Collect();

                    #endregion
                }
                #endregion

            }
            catch (Exception lo_ExcelEx)
            {
                throw new Exception("lo_ExcelEx Fail,Message is " + lo_ExcelEx.Message, lo_ExcelEx);
            }


        }


        //强制释放一个对象
        private static void NAR(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);//强制释放一个对象
            }
            catch { }
            finally
            {
                o = null;
            }
        }