C#导出数据至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
    {
        private static object missing = Type.Missing;

        #region ================导出=================
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="dgv"></param>
        public static void ExportToExcel(System.Windows.Forms.DataGridView dgv)
        {
            ExportToExcel(dgv, 0, dgv.Columns.Count);
        }
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="startColumnIndex">从第几列开始</param>
        public static void ExportToExcel(System.Windows.Forms.DataGridView dgv, int startColumnIndex)
        {
            ExportToExcel(dgv, startColumnIndex, dgv.Columns.Count);
        }
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="startColumnIndex">从第几列开始</param>
        /// <param name="endColumnIndex">到第几列结束</param>
        public static void ExportToExcel(System.Windows.Forms.DataGridView dgv, int startColumnIndex, int endColumnIndex)
        {
            //if (dgv.Rows.Count == 0)
            //    return;
            try
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
                app.UserControl = false;
                Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(missing);
                Microsoft.Office.Interop.Excel.Worksheet ws = app.ActiveWorkbook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
                try
                {
                    ExportToExcel(app, wb, ws, dgv, startColumnIndex, endColumnIndex);
                }
                finally
                {
                    app.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                    GC.Collect();
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("导出失败！\n" + ex.Message, "系统提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning);
            }
        }

        /// <summary>
        /// 导出到excel
        /// </summary>
        /// <param name="app"></param>
        /// <param name="wb"></param>
        /// <param name="ws"></param>
        /// <param name="dgv"></param>
        /// <param name="startColumnIndex">从第几列开始</param>
        /// <param name="startColumnIndex">到第几列结束</param>
        private static void ExportToExcel(Microsoft.Office.Interop.Excel.Application app, Microsoft.Office.Interop.Excel._Workbook wb, Microsoft.Office.Interop.Excel.Worksheet ws, System.Windows.Forms.DataGridView dgv, int startColumnIndex, int endColumnIndex)
        {
            Microsoft.Office.Interop.Excel.Range range;
            int rowCount = dgv.Rows.Count;
            int colCount = 0;
            int colIndex = 0;
            int rowIndex = 0;
            #region 开始到结束

            int startIndex = 0;//开始的列索引
            int endIndex = dgv.Columns.Count;//结束的列索引
            if (endColumnIndex < endIndex)
                endIndex = endColumnIndex;
            if (startIndex < endIndex && startColumnIndex < endIndex)
            {
                startIndex = startColumnIndex;
            }
            #endregion
            for (int i = startIndex; i < endIndex; i++)
            {
                if (!dgv.Columns[i].Visible)
                    continue;
                colCount++;
            }
            //foreach (DataGridViewColumn col in dgv.Columns)
            //{
            //    if (!col.Visible)
            //        continue;
            //    colCount++;
            //}
            #region "Set title"
            String endCellAddress = "";
            if (colCount > 0 && colCount <= 26)
            {
                endCellAddress = ((char)(‘A‘ + colCount)).ToString() + "1";
            }
            else if (colCount > 26 && colCount < 26 * 26)
            {
                int iTemp = (int)Math.Ceiling((double)colCount / (double)26);
                endCellAddress = ((char)(‘A‘ + iTemp)).ToString() + ((char)(‘A‘ + (colCount - 26 * 26))).ToString() + "1";
            }
            else
            {
                throw new Exception("列数太多，操作出错！");
            }
            ws.Cells[1, 1] = "导出数据";
            range = ws.get_Range("A1", endCellAddress);
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            range.Font.Size = 20;
            range.Font.Name = "黑体";
            range.Borders.LineStyle = 1;
            range.Select();
            range.Merge(false);
            //range.Merge(ws.get_Range("A1", endCellAddress));
            #endregion "Set title"

            #region " Set Header Values "
            object[,] colValues = new object[1, colCount + 1];
            colIndex = 0;
            colValues[0, colIndex] = "序号";
            for (int i = startIndex; i < endIndex; i++)
            {
                if (!dgv.Columns[i].Visible)
                    continue;
                colValues[0, colIndex + 1] = dgv.Columns[i].HeaderText;
                colIndex++;
            }
            //foreach (DataGridViewColumn col in dgv.Columns)
            //{
            //    if (!col.Visible)
            //        continue;
            //    colValues[0, colIndex + 1] = col.HeaderText;
            //    colIndex++;
            //}

            range = ws.get_Range(GetExcelCellName(1, 2), GetExcelCellName(colCount + 1, 2));
            range.Value2 = colValues;
            #endregion

            #region " Header Style "
            range.Font.Bold = true;
            range.Font.Name = "Georgia";
            range.Font.Size = 10;
            range.RowHeight = 26;
            range.EntireColumn.AutoFit();
            #endregion

            #region " Set Row Values "
            object[,] rowValues = new object[rowCount, colCount + 1];
            rowIndex = 0;
            foreach (System.Windows.Forms.DataGridViewRow row in dgv.Rows)
            {
                colIndex = 0;
                rowValues[rowIndex, colIndex] = rowIndex + 1;
                foreach (System.Windows.Forms.DataGridViewCell cell in row.Cells)
                {
                    if (cell.ColumnIndex == 0)
                    {
                        colIndex++;
                        continue;
                    }
                    if (row.Cells[colIndex].Value.ToString().IsInt())
                        rowValues[row.Index, colIndex] = string.Format("‘{0}", row.Cells[colIndex].Value);
                    else
                        rowValues[row.Index, colIndex] = row.Cells[colIndex].Value.ToString();
                    colIndex++;
                }
                rowIndex++;
            }

            range = ws.get_Range(GetExcelCellName(1, 3), GetExcelCellName(colCount + 1, rowCount + 2));
            range.Value2 = rowValues;
            #endregion

            #region " Row Style "
            range.Font.Name = "Georgia";
            range.Font.Size = 9;
            range.RowHeight = 18;
            range.EntireColumn.AutoFit();
            range.Borders.ColorIndex = 2;
            #endregion

            #region " Set Borders "
            range = ws.get_Range(GetExcelCellName(1, 1), GetExcelCellName(colCount + 1, rowCount + 2));
            range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            range.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            #endregion
        }

        #region " GetCellName "

        private static string GetExcelCellName(int fColIndex, int fRowIndex)
        {
            if (fColIndex <= 0 || fColIndex > 256)
            {
                throw new Exception("Excel 列索引数值超出范围(1-256)!");
            }
            else if (fColIndex <= 26)
            {
                return GetExcelCellName(fColIndex) + fRowIndex.ToString();
            }
            else
            {
                string retLetter = GetExcelCellName(fColIndex / 26);
                retLetter += GetExcelCellName(fColIndex % 26);
                retLetter += fRowIndex.ToString();
                return retLetter;
            }
        }

        private static string GetExcelCellName(int fColIndex)
        {
            int i = 1;
            foreach (string letter in Enum.GetNames(typeof(ExcelColumnLetters)))
            {
                if (i == fColIndex)
                    return letter;
                i++;
            }
            throw new Exception("Excel 列索引数值超出范围(1-256)!");
        }

        #endregion

        public enum ExcelColumnLetters
        {
            A = 1,
            B = 2,
            C = 3,
            D = 4,
            E = 5,
            F = 6,
            G = 7,
            H = 8,
            I = 9,
            J = 10,
            K = 11,
            L = 12,
            M = 13,
            N = 14,
            O = 15,
            P = 16,
            Q = 17,
            R = 18,
            S = 19,
            T = 20,
            U = 21,
            V = 22,
            W = 23,
            X = 24,
            Y = 25,
            Z = 26
        }
        #endregion

       #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
    }
}