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 ==  ? "代理费打印模板" : "市场费打印模板";

            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(); //第一个工作薄。

                if (worksheet == null)

                {

                    errorMsg = "工作薄中没有工作表";

                    return errorMsg;

                }

                int rowIndex = ;

                //写入数据，Excel索引从1开始。

                //foreach (DataRow row in dt.Rows)

                //{

                if (rowIndex ==)

                {

                    #region head

                    worksheet.Cells[, ] = "客户编码1";//row["产品名称"].ToStr();

                    worksheet.Cells[, ] = "客户名称1";//row["产品名称"].ToStr();

                    #endregion

                    #region bottom

                    #endregion

                }

                #region body

                worksheet.Cells[rowIndex, ] = "产品名称1";//row["产品名称"].ToStr();

                worksheet.Cells[rowIndex, ] = "规格型号1";//row["规格型号"].ToStr();

                worksheet.Cells[rowIndex, ] = 1001.0000;//row["结算数量"].ToStr().ToDouble();

                worksheet.Cells[rowIndex, ] = 1002.0000;//row["销售单价"].ToStr().ToDouble();

                worksheet.Cells[rowIndex, ] = 1003.0000;//row["销售金额"].ToStr().ToDouble();

                worksheet.Cells[rowIndex, ] = 1004.0000;//row["结算单价"].ToStr().ToDouble();

                worksheet.Cells[rowIndex, ] = 1005.0000;//row["差价"].ToStr().ToDouble();

                worksheet.Cells[rowIndex, ] = 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

    }

}