WinForm使用Aspose.cells利用模板导出Excel

Aspose是一个处理文件格式的组件,可对Words、Excel等工具进行操作,实现代码数据导出。在这里我介绍以及提供Aspose对Excel(Aspose.Cells)导出的工具类。

1.首先介绍Aspose.Cells的基础知识：

A.Excel模板变量的定义(详细可在官网查看详细:http://www.aspose.com/docs/display/cellsnet/Using+Smart+Markers#UsingSmartMarkers-skip):

(1)普通变量格式:&=$name

(2)datatable导出格式(会自动添加多行):&=dtname.columnnae.(注:在column可添加别的标识,numeric:转化为数字,如果你在Excel中写好了公式计算,最好加上否则结果会计算错,noadd:导出的时候不添加新行,只是把数据放到Excel表格中,也就是说样式都是模板的样式,未添加时,样式则为Excel普通样式,skip:n 跳过n行,一般可以与分组结合起来使用,标识不同的分组,group:normal,分组,有多种样式,normal为普通样式,merge,repeat为另外两种样式,还有其他ascending,horizontal,shift ,copystyle等，由于没用到所以不是很了解,不满足你的需求，可以到官网查看)

(3)等

(4)这里是模板的例子:

B.直接上工具类代码(代码以及插件dll可在这里Aspose.Cells工具类下载,密码:zre7):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using Aspose.Cells;


namespace TestNamespace
{
    public class TemplateExcelHelper
    {
        #region 根据Excel模板导出 string DataTable Dictionary<String,Object>
        /*
         * templatePath 模板路径(一般在bin/debug目录下)
         * dtSource 数据源(datatable数据源,表名必须与模板中的对象对应)
         * dictSource 附加信息 
         */
        public static void TemplateExportExcel(string templatePath, DataTable dtSource, Dictionary<String,Object> dictSource)
        {
            SaveFileDialog sf = new SaveFileDialog();
            sf.Filter = "Excel files (*.xls)|*.xls";
            string path = System.IO.Path.Combine(Application.StartupPath, templatePath);
            WorkbookDesigner designer = new WorkbookDesigner();
            try
            {
                designer.Open(path);
            }
            catch (Exception ex)
            {
                if (sf.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        ExcelHelper.ExportExcel(dtSource, sf.FileName);//找不到制定模板时,按标准导出导出模板
                        MessageBox.Show("保存成功", "提示");
                    }
                    catch (Exception ex2)
                    {
                        MessageBox.Show(ex2.Message, "异常");
                    }
                }
                return;
            }
//设置数据源
            designer.SetDataSource(dtSource);
            if (dictSource != null && dictSource.Count > 0) {
                foreach (var keyValuePair in dictSource) {
                    designer.SetDataSource(keyValuePair.Key, keyValuePair.Value);
                }
            }
            designer.Process();
            if (sf.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    string saveFile = System.IO.Path.Combine(Application.StartupPath, sf.FileName);
                    designer.Save(saveFile);
                    MessageBox.Show("导出成功", "提示");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "异常");
                }
            }
        }
        #endregion


        #region 根据Excel模板导出 string DataGridView Dictionary<String,Object>
        /*
         * templatePath 模板路径(一般在bin/debug目录下)
         * datagridView 数据源(Datatable或DatagridView数据源信息)
         * dictSource 附加信息 
         */
        public static void TemplateExportExcel(string templatePath, DataGridView datagridView,string tablename, Dictionary<String, Object> dictSource)

        {
            TemplateExportExcel(templatePath, GetDgvToTable(datagridView,tablename), dictSource);
        }
        #endregion


        #region datagridview数据源转化为datatable
        public static DataTable GetDgvToTable(DataGridView dgv,string tablename)
        {
            DataTable dt = new DataTable();
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
                dt.Columns.Add(dc);
            }
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                }
                dt.Rows.Add(dr);
            }
            dt.TableName = tablename;
            return dt;
        }
        #endregion


        /// <summary>
        /// 导出Excel,拓展
        /// </summary>
        /// <param name="templatePath">模板路径</param>
        /// <param name="ds">数据集</param>
        /// <param name="dictSource">附加信息数据源,添加fileNames导出多个文件</param>
        /// <param name="isDivsion">是否将附加信息分割,true:导出多个Excel,false:只导出一个Excel</param>
        #region 
        public static void TemplateExportExcel(string templatePath, DataSet ds,Dictionary<String, Object> dictSource,bool isDivsion,string tableName)

        {
            SaveFileDialog sf = new SaveFileDialog();
            sf.Filter = "Excel files (*.xls)|*.xls";
            string path = System.IO.Path.Combine(Application.StartupPath, templatePath);
            WorkbookDesigner designer = new WorkbookDesigner();
            
            //获取保存多个文件时的后缀
            string[] fileNameSuffix = null;
            if (isDivsion)
            {
                if (dictSource.ContainsKey("fileNames"))//存在文件名属性集合时
                {
                    fileNameSuffix = (string[])dictSource["fileNames"];
                }
                else
                { //不存在时,自定义文件名
                    fileNameSuffix = new string[ds.Tables.Count];
                    for (int i = 0; i < fileNameSuffix.Length; i++)
                    {
                        fileNameSuffix[i] = "_" + i;
                    }
                }
            }
            try
            {
                designer.Open(path);
            }
            catch (Exception ex)
            {
                if (sf.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        ///导出一个Excel,默认为第一个表格
                        if (!isDivsion)
                        {
                            ExcelHelper.ExportExcel(ds.Tables[0], sf.FileName);
                            MessageBox.Show("导出成功", "提示");
                        }
                        else {
                            for (int i = 0; i < ds.Tables.Count;i++ )
                            {
                                ExcelHelper.ExportExcel(ds.Tables[i], sf.FileName.Insert(sf.FileName.LastIndexOf("."), fileNameSuffix[i]));
                            }
                            MessageBox.Show("导出成功","提示");
                        }
                    }
                    catch (Exception ex2)
                    {
                        MessageBox.Show(ex2.Message, "异常");
                    }
                }
                return;
            }
            if (sf.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if (!isDivsion)
                    {
                        if (ds != null)
                        {
                            designer.SetDataSource(ds);
                        }
                        designer.Process(); 
                        string saveFile = System.IO.Path.Combine(Application.StartupPath, sf.FileName);
                        designer.Save(saveFile);
                        MessageBox.Show("导出成功", "提示");
                    }
                    else {
                        if (ds != null)
                        {
                            for (int i = 0; i < ds.Tables.Count; i++)
                            {
                                DataTable table = ds.Tables[i].Copy();
                                table.TableName = tableName;
                                string saveFile = System.IO.Path.Combine(Application.StartupPath,sf.FileName.Insert(sf.FileName.LastIndexOf("."),fileNameSuffix[i]));

                                TempleExportExcelNormal(templatePath, table, dictSource, saveFile);
                            }
                        }
                        MessageBox.Show("导出成功", "提示");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "异常");
                }
            }
        }
        #endregion
    }

        /// <summary>
        ///  update 2018/7/18 yzy
        /// </summary>
    public static void TempleExportExcelNormal(string templatePath, DataTable dt, Dictionary<String, Object> dictSource, string saveFile)
        {
            try
            {
                string path = System.IO.Path.Combine(Application.StartupPath, templatePath);
                WorkbookDesigner designer = new WorkbookDesigner();
                designer.Open(path);
                if (dictSource != null && dictSource.Count > 0)
                {
                    foreach (var keyValuePair in dictSource)
                    {
                        designer.SetDataSource(keyValuePair.Key, keyValuePair.Value);
                    }
                }
                dt.Columns.Add("组id");
                int pages = 1;
                int count = dt.Rows.Count;
                int cutCount = count - 25;
                if (count > 25)
                {
                    if (cutCount % 50 == 0)
                    {
                        pages = cutCount / 50 + 1;
                    }
                    else
                    {
                        pages = cutCount / 50 + 2;
                    }
                }
                int id = 0 ;
                for (int i = 0; i < count; i++) {
                    if (i == 25)
                    {
                        id++;
                    }
                    else if ((count - 25) % 50 == 0 && i>25)
                    {
                        id++;
                    }
                    dt.Rows[i]["组id"] = id;    
                }
                designer.SetDataSource(dt);
                designer.Process();
                designer.Save(saveFile);
            }
            catch (Exception ex) {
                MessageBox.Show(ex.Message,"异常");
            }
        }
    }
}