using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;

namespace Common
{
    class Tool
    {     /// <summary>
          /// 读excel，转换为DataTable
          /// </summary>
          /// <param name="path">excel存放的路径</param>
          /// <param name="sheetName">工作簿名称</param>
          /// <returns></returns>
        public static System.Data.DataTable ReadExcelToTable(string path, string sheetName)
        {
            try
            {
                bool IsCompatible = GetIsCompatible(path);
                string connstring;
                if (IsCompatible)
                {
                    connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; //兼容模式
                }
                else
                {
                    connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                }
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    System.Data.DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字                   
                    DataSet set = new DataSet();
                    string sql = string.Format("SELECT * FROM [{0}]", sheetName + "$"); //查询字符串
                    OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                    ada.Fill(set, sheetName);
                    return set.Tables[sheetName];
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 判断是否为兼容模式
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool GetIsCompatible(string filePath)
        {
            string ext = Path.GetExtension(filePath);
            return new[] { ".xls", ".xlt" }.Count(e => e.Equals(ext, StringComparison.OrdinalIgnoreCase)) > 0;
        }
        public static string ExportDataTableToExcel(System.Data.DataTable dataTable, string filePath)
        {
            Application app;

            _Workbook wb;

            _Worksheet ws;

            object misValue = System.Reflection.Missing.Value;

            app = new Application();

            wb = app.Workbooks.Add(misValue);

            ws = (_Worksheet)wb.ActiveSheet;

            int rowIndex = 1;

            int colIndex = 0;

            //取得标题  
            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;

                app.Cells[1, colIndex] = col.ColumnName;
            }

            //取得表格中的数据  
            foreach (DataRow row in dataTable.Rows)
            {
                rowIndex++;

                colIndex = 0;

                foreach (DataColumn col in dataTable.Columns)
                {
                    colIndex++;
                    app.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();
                    //设置表格内容居中对齐  
                    //workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                }
            }
            app.Visible = true;
            wb.SaveAs(filePath, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            dataTable = null;

            wb.Close(true, misValue, misValue);

            app.Quit();

            PublicMethod.Kill(app);//调用kill当前excel进程  

            releaseObject(ws);

            releaseObject(wb);

            releaseObject(app);

            if (!File.Exists(filePath))
            {
                return null;
            }
            return filePath;
        }
        public static bool ExportDataTableToExcel2(System.Data.DataTable dataTable, string filePath)
        {
            if (File.Exists(filePath))
            {
                System.Windows.Forms.MessageBox.Show(filePath + "已存在！", "提示");
                return false;
            }
            #region 初始化Excel表
            Excel.Application app = new Excel.Application();
            object MissingValue = Type.Missing;
            Excel.Workbook wb = app.Workbooks.Add(true);
            Excel.Worksheet ws = null;
            app.DisplayAlerts = false;
            app.AlertBeforeOverwriting = false;
            ws = (Excel.Worksheet)wb.Sheets["Sheet1"];
            #endregion

            #region 填充数据
            Object[,] dataArray = new Object[1 + dataTable.Rows.Count, dataTable.Columns.Count];
            for (int i = 0; i < dataTable.Columns.Count; i++)//填写列名
            {
                dataArray[0, i] = dataTable.Columns[i].ColumnName;

                for (int j = 0; j < dataTable.Rows.Count; j++)//填入数据
                {
                    dataArray[j + 1, i] = dataTable.Rows[j][i].ToString();
                }
            }
            #endregion

            Excel.Range range = ws.Range[ws.Cells[1, 1], ws.Cells[1 + dataTable.Rows.Count, dataTable.Columns.Count]];
            range.Value2 = dataArray;
            range.EntireColumn.AutoFit(); //自动设置列宽
            range.EntireRow.AutoFit(); //自动设置行高
            wb.Saved = true;
            // wb.SaveCopyAs(filePath);//保存
            wb.SaveAs(filePath);
            app.Quit();//关闭进程
            app = null;
            wb = null;
            ws = null;
            GC.Collect();

            if (File.Exists(filePath))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 释放COM组件对象
        /// </summary>
        /// <param name="obj"></param>
        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }
        /// <summary>
        /// 关闭进程的内部类
        /// </summary>
        public class PublicMethod
        {
            [DllImport("User32.dll", CharSet = CharSet.Auto)]

            public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

            public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
            {
                //如果外层没有try catch方法这个地方需要抛异常。
                IntPtr t = new IntPtr(excel.Hwnd);

                int k = 0;

                GetWindowThreadProcessId(t, out k);

                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);

                p.Kill();
            }
        }
    }
}