WPF 根据Excel模版导出数据到Excel

WPF中 根据Excel模版导出数据到Excel

前台.xaml文件：

<Window x:Class="Export.MainWindow"        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"        Title="MainWindow" Height="350" Width="525">    <Grid>        <Button Content="Export" Height="44" HorizontalAlignment="Left" Margin="193,107,0,0" Name="button1" VerticalAlignment="Top" Width="100" Click="button1_Click" />        <TextBox Height="23" HorizontalAlignment="Left" Margin="24,22,0,0" Name="textBox1" VerticalAlignment="Top" Width="322" />        <Button Content="Choose Stencil File" Height="23" HorizontalAlignment="Left" Margin="360,22,0,0" Name="button2" VerticalAlignment="Top" Width="131" Click="button2_Click" />    </Grid></Window>

后台.cs文件：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Windows;using System.Windows.Controls;using System.Windows.Data;using System.Windows.Documents;using System.Windows.Input;using System.Windows.Media;using System.Windows.Media.Imaging;using System.Windows.Navigation;using System.Windows.Shapes;using Microsoft.Office.Interop;using Microsoft.Office.Interop.Excel;using System.Data;namespace Export{    /// <summary>    /// MainWindow.xaml 的交互逻辑    /// </summary>    public partial class MainWindow : System.Windows.Window    {        public MainWindow()        {            InitializeComponent();        }        private void button2_Click(object sender, RoutedEventArgs e)        {            var openFileDialog = new Microsoft.Win32.OpenFileDialog()            {                Filter = "Excel Files (*.xls,*.xlsx)|*.xls;*.xlsx"            };            var result = openFileDialog.ShowDialog();            if (result == true)            {                textBox1.Text = openFileDialog.FileName;            }         }        private void button1_Click(object sender, RoutedEventArgs e)        {            string filePath = textBox1.Text;            if (filePath == "")            {                MessageBox.Show("请选择模版。");                return;            }            //首先模拟建立将要导出的数据，这些数据都存于DataTable中              System.Data.DataTable dt = new System.Data.DataTable();            dt.Columns.Add("Name", typeof(string));            dt.Columns.Add("Gender", typeof(string));            dt.Columns.Add("Age", typeof(string));            dt.Columns.Add("Email", typeof(string));            dt.Columns.Add("Phone", typeof(string));            dt.Columns.Add("Adress", typeof(string));              DataRow row = dt.NewRow();              row["Name"] = "mzl";            row["Gender"] = "Male";              row["Age"] = "25";            row["Email"] = "[e-mail]";            row["Phone"] = "12536885669";            row["Adress"] = "sfgagdgdfadg";             dt.Rows.Add(row);              row = dt.NewRow();            row["Name"] = "mzl2";            row["Gender"] = "Female";            row["Age"] = "23";            row["Email"] = "[e-mail]";            row["Phone"] = "88654877";            row["Adress"] = "tyuutyj";             dt.Rows.Add(row);             Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();            object objMissing = System.Reflection.Missing.Value;            //读取简历模板            Workbook xlTplWorkbook = excelApp.Workbooks.Open(textBox1.Text, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);            //Workbook excelWB = excelApp.Workbooks.Add(System.Type.Missing);    //创建工作簿（WorkBook：即Excel文件主体本身）              Worksheet excelWS = (Worksheet)xlTplWorkbook.Worksheets[1];   //创建工作表（即Excel里的子表sheet） 1表示在子表sheet1里进行数据导出              //excelWS.Cells.NumberFormat = "@";     //  如果数据中存在数字类型 可以让它变文本格式显示              //将数据导入到工作表的单元格            for (int i = 0; i < dt.Rows.Count; i++)              {                  excelWS.Cells[i + 2, 1] = dt.Rows[i]["Name"].ToString();   //Excel单元格第一个从索引1开始                 excelWS.Cells[i + 2, 2] = dt.Rows[i]["Gender"].ToString();                excelWS.Cells[i + 2, 3] = dt.Rows[i]["Age"].ToString();                excelWS.Cells[i + 2, 4] = dt.Rows[i]["Email"].ToString();                excelWS.Cells[i + 2, 5] = dt.Rows[i]["Phone"].ToString();                excelWS.Cells[i + 2, 6] = dt.Rows[i]["Adress"].ToString();            }            //添加图像            string strPhotoPath = "D:\\Export\\Template\\a.jpg";            //添加引用"C://Program Files//common Files//Microsoft Shared//Office14//MSO.DLL"。            excelWS.Shapes.AddPicture(strPhotoPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 566, 4, 180, 103);            xlTplWorkbook.SaveAs("D:\\Export\\ExcelTest.xlsx", CreateBackup:true);  //将其进行保存到指定的路径                xlTplWorkbook.Close();              excelApp.Quit();  //KillAllExcel(excelApp); 释放可能还没释放的进程              KillAllExcel(excelApp);            MessageBox.Show("Export Complete!");       }        #region 释放Excel进程        public bool KillAllExcel(Microsoft.Office.Interop.Excel.Application excelApp)        {            try            {                if (excelApp != null)                {                    excelApp.Quit();                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);                    //释放COM组件，其实就是将其引用计数减1                         //System.Diagnostics.Process theProc;                         foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))                    {                        //先关闭图形窗口。如果关闭失败.有的时候在状态里看不到图形窗口的excel了，                             //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要释放它                             if (theProc.CloseMainWindow() == false)                        {                            theProc.Kill();                        }                    }                    excelApp = null;                    return true;                }            }            catch            {                return false;            }            return true;        }        #endregion      }}

http://blog.csdn.net/sanjiawan/article/details/6818921