C#导出数据至excel模板

开源分享
最近一个客户要做一个将数据直接输出到指定格式的Excel模板中，略施小计，搞定

其中包含了对Excel的增行和删行,打印预览，表头，表体，表尾的控制

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Windows.Forms;

namespace UFIDA.U8.UAP.QW.Common
{
    /// <summary>
    /// Excel操作管理
    /// </summary>
    public class ExcelHandler
    {
        
       #region 按模板导出
        /// <summary>
        /// 导出至模板
        /// </summary>
        /// <param name="type">类型{市场费：0，代理费：1}</param>
        /// <param name="dt">数据源</param>
        /// <returns>失败信息errorMsg</returns>
        public string ExportModel(int type, DataTable dt)
        {
            //选择保存路径
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            if (fbd.ShowDialog() != DialogResult.OK)
                return "";
            string errorMsg = string.Empty;
            string fileName = type == 0 ? "代理费打印模板" : "市场费打印模板";
            string path = Path.GetFullPath(@"Temp" + fileName + ".xlsx");
            string savaPath = fbd.SelectedPath;
            savaPath=savaPath.EndsWith("\")?savaPath:savaPath+"\";
            savaPath += fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            //需要添加 Microsoft.Office.Interop.Excel引用 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false;
                app.UserControl = true;
                Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
                Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(path); //加载模板
                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)
                {
                    errorMsg = "工作薄中没有工作表";
                    return errorMsg;
                }
                int rowIndex = 7;
                //写入数据，Excel索引从1开始。
                //foreach (DataRow row in dt.Rows)
                //{
                if (rowIndex ==7)
                {
                    #region head
                    worksheet.Cells[2, 2] = "客户编码1";//row["产品名称"].ToStr();
                    worksheet.Cells[3, 2] = "客户名称1";//row["产品名称"].ToStr();

                    #endregion

                    #region bottom

                    #endregion
                }

                #region body
                worksheet.Cells[rowIndex, 1] = "产品名称1";//row["产品名称"].ToStr();
                worksheet.Cells[rowIndex, 2] = "规格型号1";//row["规格型号"].ToStr();
                worksheet.Cells[rowIndex, 3] = 1001.0000;//row["结算数量"].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 4] = 1002.0000;//row["销售单价"].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 5] = 1003.0000;//row["销售金额"].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 6] = 1004.0000;//row["结算单价"].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 7] = 1005.0000;//row["差价"].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 8] = 1006.0000;//row["应付费用"].ToStr().ToDouble();
                #endregion

                rowIndex++;
                InsertRows(worksheet, rowIndex);

                //}

                //调整Excel的样式。
                //Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A3", worksheet.Cells[dt.Rows.Count + 2, 8]);
                //rg.Borders.LineStyle = 1; //单元格加边框。
                worksheet.Columns.AutoFit(); //自动调整列宽。
                //Missing 在System.Reflection命名空间下。
                workbook.SaveAs(savaPath, 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);
                //workbook.PrintPreview(true);//打印预览，前提是app.Visible = true,否则看不到;
            }
            catch (Exception ex)
            {
                errorMsg += ex.Message;
            }
            finally
            {
                //最后一定要退出
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                GC.Collect();
            }
            return errorMsg;
        }
        /// <summary>
        /// 在工作表中插入行，并调整其他行以留出空间   
        /// </summary>
        /// <param name="sheet">工作簿</param>
        /// <param name="rowIndex">行索引</param>
        private void InsertRows(Microsoft.Office.Interop.Excel._Worksheet sheet, int rowIndex)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, missing];
            //object   Range.Insert(object   shift,   object   copyorigin);     
            //shift:   Variant类型，可选。指定单元格的调整方式。可以为下列   XlInsertShiftDirection   常量之一：   
            //xlShiftToRight   或   xlShiftDown。如果省略该参数，Microsoft   Excel   将根据区域形状确定调整方式。   
            range.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, missing);
        }

        /// <summary>
        /// 在工作表中删除行   
        /// </summary>
        /// <param name="sheet">工作簿</param>
        /// <param name="rowIndex">行索引</param>
        private void DeleteRows(Microsoft.Office.Interop.Excel.Worksheet sheet, int rowIndex)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, missing];
            range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
        }

        #endregion
    }
}