excel操作类

public class ExcelOper
    {
        private Application _excelApp = null;
        public ExcelOper()
        {

        }

        #region 读取Excel的内容

        /// <summary>
        /// 获取Excel的内容
        /// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable
        /// 的列标题
        /// </summary>
        /// <returns></returns>
        public static System.Data.DataTable GetExcelTable(string excelPath, string sheetName)
        {
            try
            {
                //打开Excel连接
                string connString = "";
                OleDbConnection conn = OpenExcelEx(excelPath, out connString);

                //读取Excel
                string strSheetName = sheetName + "$";
                string strSql = "select * from [" + strSheetName + "]";
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn);
                DataSet dtSet = new DataSet();
                dataAdapter.Fill(dtSet);
                System.Data.DataTable dtTable = dtSet.Tables[0];
                dataAdapter.Dispose();
                return dtTable;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return null;
            }
        }

        /// <summary>
        /// 获取Excel的内容
        /// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable
        /// 的列标题
        /// </summary>
        /// <returns></returns>
        public static System.Data.DataTable GetExcelTable(string excelPath)
        {
            return GetExcelTable(excelPath, false);
        }

        /// <summary>
        /// 获取Excel的内容
        /// 备注：把Excel读取到DataTable之后，默认把Excel的第一行作为Datatable
        /// 的列标题
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="HasColumn">是否把一行做表头</param>
        /// <returns></returns>
        public static System.Data.DataTable GetExcelTable(string excelPath, bool HasColumn)
        {
            try
            {
                //打开Excel连接
                string connString = "";
                OleDbConnection conn = null;
                if (HasColumn == true)
                {
                    conn = OpenExcelEx(excelPath, out connString); //连接
                }
                else
                {
                    conn = OpenExcel(excelPath, out connString); //连接
                }

                //读取Excel
                //string strSheetName = GetFirstSheetName(connString);//第一页如果改成其他名称，eg “名称”，sheet2，sheet3，则第一页为sheet2，还需要判断是否有内容

                List<string> lstName = GetFirstSheetNames(connString);//对所有图层遍历看是否有内容
                System.Data.DataTable dtTable = null;
                foreach (string strSheetName in lstName)
                {
                    string strSql = "select * from [" + strSheetName + "]";
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn);  //查询
                    DataSet dtSet = new DataSet();
                    dataAdapter.Fill(dtSet);
                    System.Data.DataTable table = dtSet.Tables[0];
                    dataAdapter.Dispose();
                    if (table.Columns.Count > 1)
                    {
                        dtTable = table;
                        break;
                    }
                }

                conn.Close(); //进程就不会被占用
                return dtTable;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return null;
            }
        }


        /// <summary>
        /// 获取Excel的内容
        /// 备注：读取EXCEL从单元格(N,M)读取到单元格(X,Y)，N,M,X,Y由strFilter指定,如：A3:C65535
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="HasColumn">是否把第N行做为列名（表头）</param>
        /// <returns></returns>
        public static System.Data.DataTable GetExcelTable(string excelPath, string strFilter, bool HasColumn)
        {
            try
            {
                //打开Excel连接
                string connString = "";
                OleDbConnection conn = null;
                if (HasColumn == true)
                {
                    conn = OpenExcelEx(excelPath, out connString); //连接
                }
                else
                {
                    conn = OpenExcel(excelPath, out connString); //连接
                }

                //读取Excel
                string strSheetName = GetFirstSheetName(connString) + strFilter;
                string strSql = "select * from [" + strSheetName + "]";
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn);  //查询
                DataSet dtSet = new DataSet();
                dataAdapter.Fill(dtSet);
                System.Data.DataTable dtTable = dtSet.Tables[0];
                dataAdapter.Dispose();
                conn.Close(); //cql 进程就不会被占用
                return dtTable;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return null;
            }
        }
        /// <summary>
        /// 表单以数值开头的处理方式，湖南项目
        /// </summary>
        /// <param name="excelPath"></param>
        /// <param name="HasColumn"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetNumExcelTable(string excelPath,int line, bool HasColumn)
        {
            try
            {
                //打开Excel连接
                string connString = "";
                OleDbConnection conn = null;
                if (HasColumn == true)
                {
                    conn = OpenExcelEx(excelPath, out connString); //连接
                }
                else
                {
                    conn = OpenExcel(excelPath, out connString); //连接
                }

                //读取Excel
                List<string> _strSheetName = GetFirstSheetNames(connString);

                string strSheetName = SelectSheet(_strSheetName);
                string strSql = "select * from [" + strSheetName + "]";
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn);  //查询
                DataSet dtSet = new DataSet();
                dataAdapter.Fill(dtSet);
                System.Data.DataTable dtTable = dtSet.Tables[0];
                dataAdapter.Dispose();
                conn.Close(); //进程就不会被占用
                ClearNullTable(dtTable);
                System.Data.DataTable newDt = dtTable.Clone();
                DataRow row = null;
                for (int i = line-1; i < dtTable.Rows.Count; i++)
                {
                    newDt.Rows.Add(dtTable.Rows[i].ItemArray);
                }
                return newDt;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return null;
            }
        }
        //去除空行空列
        public static void ClearNullTable(System.Data.DataTable table)
        {
            bool bNull = true;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                bNull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString()))
                    {
                        bNull = false;
                    }
                }
                if (bNull)
                {
                    table.Rows.RemoveAt(i);
                    i--;
                }
            }
            for (int j = 0; j < table.Columns.Count; j++)
            {
                bNull = true;
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString()))
                    {
                        bNull = false;
                    }
                }
                if (bNull)
                {
                    table.Columns.RemoveAt(j);
                    j--;
                }
            }
        }


        /// <summary>
        /// 去除列全部为空的行
        /// </summary>
        /// <param name="table"></param>
        public static void ClearNullRows(System.Data.DataTable table)
        {
            bool bNull = true;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                bNull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(table.Rows[i][table.Columns[j]].ToString()))
                    {
                        bNull = false;
                    }
                }
                if (bNull)
                {
                    table.Rows.RemoveAt(i);
                    i--;
                }
            }
        }
        /// <summary>
        /// 获取第一个Sheet页的名称
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private static string GetFirstSheetName(string connectionString)
        {
            using (OleDbConnection connection = new
                       OleDbConnection(connectionString))
            {
                connection.Open();
                System.Data.DataTable schemaTable = connection.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables,
                    new object[] { null, null, null, "TABLE" });
                string tableName = schemaTable.Rows[0][2].ToString().Trim();
                connection.Close();
                return tableName;
            }
        }

        private static List<string> GetFirstSheetNames(string connectionString)
        {
            List<string> lstName = new List<string>();
            using (OleDbConnection connection = new
                       OleDbConnection(connectionString))
            {
                connection.Open();
                System.Data.DataTable schemaTable = connection.GetOleDbSchemaTable(
                    OleDbSchemaGuid.Tables,
                    new object[] { null, null, null, "TABLE" });
                foreach (DataRow row in schemaTable.Rows)
                {
                    string tableName = row[2].ToString().Trim();
                    lstName.Add(tableName);
                }
                connection.Close();//
            }
            return lstName;
        }
        #region 直接打开读取Excel获取第一个页
        //private static string GetFirstSheetNameEx(string filePath)
        //{
        //    Application xApp = null;
        //    Workbook xBook = null;
        //    try
        //    {
        //        xApp = new ApplicationClass();
        //        xBook = xApp.Application.Workbooks.Open(filePath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//新建文件的代码 
        //        string name = ((Worksheet)xBook.Sheets[0]).Name;
        //        return name;
        //    }
        //    catch(Exception ex)
        //    {
        //        LogHelper.Error.Append(ex);
        //    }
        //    finally
        //    {
        //        if (xBook != null)
        //        {
        //            xBook.Close(Type.Missing, Type.Missing, Type.Missing);
        //            xBook = null;
        //            xApp.Workbooks.Close();
        //            xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 
        //            xApp = null;
        //        }
        //    }
        //    return "";
        //}
        #endregion

        #endregion

        /// <summary>
        /// 判断文件是否正在使用
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static bool IsFileInUse(string fileName)
        {
            bool inUse = true;
            if (File.Exists(fileName))
            {
                FileStream fs = null;
                try
                {
                    fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.None);
                    inUse = false;
                }
                catch (Exception ex)
                {
                    LogHelper.Error.Append(ex);
                }
                finally
                {
                    if (fs != null)
                    {
                        fs.Close();
                    }
                }
                return inUse;           //true表示正在使用,false没有使用
            }
            else
            {
                return false;           //文件不存在则一定没有被使用
            }
        }
        public static System.Data.DataTable GetExcelTable(string excelPath, string strFilter, string strSheetName, bool HasColumn)
        {
            try
            {
                //打开Excel连接
                string connString = "";
                OleDbConnection conn = null;
                if (HasColumn == true)
                {
                    conn = OpenExcelEx(excelPath, out connString); //连接
                }
                else
                {
                    conn = OpenExcel(excelPath, out connString); //连接
                }

                //读取Excel
                strSheetName = strSheetName + "$" + strFilter;
                string strSql = "select * from [" + strSheetName + "]";
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSql, conn);  //查询
                DataSet dtSet = new DataSet();
                dataAdapter.Fill(dtSet);
                System.Data.DataTable dtTable = dtSet.Tables[0];
                dataAdapter.Dispose();
                conn.Close(); //进程就不会被占用
                return dtTable;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return null;
            }
        }


        #region 写Excel

        /// <summary>
        /// 创建Excel进程
        /// </summary>
        /// <returns></returns>
        private Application GetExcelApp()
        {
            Application excelApp = new Application();
            excelApp.Application.Workbooks.Add(true);
            _excelApp = excelApp;

            return excelApp;
        }

        /// <summary>
        /// 把DataTable的内容写入Excel
        /// </summary>
        /// <param name="strExcelPath">excel文件的路径</param>
        /// <param name="htDataTable">key：sheetName，value：DataTable</param>
        /// <returns></returns>
        public bool WriteExcel(string strExcelPath, Hashtable htDataTable)
        {
            if (htDataTable == null || htDataTable.Count == 0)
            {
                return false;
            }

            bool writeRst = false;
            try
            {
                if (_excelApp == null)
                {
                    GetExcelApp();
                }

                //依次写入Sheet页
                int countNum = 1;
                foreach (DictionaryEntry de in htDataTable)
                {
                    string sheetName = de.Key.ToString();
                    System.Data.DataTable dtTable = (System.Data.DataTable)de.Value;

                    Worksheet excelSheet = null;
                    if (countNum == 1)
                    {
                        excelSheet = (Worksheet)_excelApp.Worksheets[countNum];
                    }
                    else
                    {
                        excelSheet = (Worksheet)_excelApp.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                    }
                    excelSheet.Name = sheetName;
                    bool sheetRst = dtTable.Columns.Contains("数据路径") ? writeSheet(excelSheet, dtTable, dtTable.Columns["数据路径"].Ordinal) : writeSheet(excelSheet, dtTable);
                    if (!sheetRst)
                    {
                        throw new Exception(sheetName + "创建失败！");
                    }
                    countNum++;
                }

                //保存
                _excelApp.Visible = false;
                _excelApp.DisplayAlerts = false;
                _excelApp.AlertBeforeOverwriting = false;
                _excelApp.ActiveWorkbook.SaveAs(strExcelPath, Type.Missing, null, null, false, false,
                                                        XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                writeRst = true;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                writeRst = false;
            }
            finally
            {
                //关闭Excel进程
                object missing = System.Reflection.Missing.Value;
                _excelApp.ActiveWorkbook.Close(missing, missing, missing);
                _excelApp.Quit();
                _excelApp = null;

                //垃圾回收
                GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
            }

            return writeRst;
        }
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);
        /// <summary>
        /// 销毁Excel
        /// </summary>
        /// <param name="excelApp"></param>
        /// <param name="excelWorkbook"></param>
        /// <param name="excelWorksheet"></param>
        private static void DisposeExcelCOMObject(ref Microsoft.Office.Interop.Excel.Application excelApp, Microsoft.Office.Interop.Excel.Workbook excelWorkbook, Microsoft.Office.Interop.Excel.Worksheet excelWorksheet)
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
            excelWorksheet = null;
            excelWorkbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
            excelWorkbook = null;
            excelApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 

            IntPtr t = new IntPtr(excelApp.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();

            if (null != excelApp)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                excelApp = null;
            }
            System.GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect();
        }
        /// <summary>
        /// 写Sheet页
        /// </summary>
        /// <param name="excelSheet"></param>
        /// <param name="dtTable"></param>
        /// <returns></returns>
        private bool writeSheet(Worksheet excelSheet, System.Data.DataTable dtTable, int Links)
        {
            if (excelSheet == null || dtTable == null)
            {
                return false;
            }

            //列名
            for (int i = 0; i < dtTable.Columns.Count; i++)
            {
                DataColumn dtColumn = dtTable.Columns[i];
                string caption = dtColumn.Caption;
                excelSheet.Cells[1, i + 1] = caption;
            }

            //写入值
            for (int i = 0; i < dtTable.Rows.Count; i++)
            {
                for (int j = 0; j < dtTable.Columns.Count; j++)
                {
                    object objValue = dtTable.Rows[i][j];
                    excelSheet.Cells[2 + i, j + 1] = objValue;
                    if (Links.Equals(j))
                        excelSheet.Hyperlinks.Add(excelSheet.Cells[2 + i, j + 1], objValue.ToString(), Type.Missing, Type.Missing, Type.Missing);
                }
            }

            excelSheet.Columns.AutoFit();
            return true;
        }

        private bool writeSheet(Worksheet excelSheet, System.Data.DataTable dtTable)
        {
            return writeSheet(excelSheet, dtTable, -1);
        }
        #endregion

        #region 打开Excel连接 ADO.Net
        /// <summary>
        /// 打开Excel文件,把一行做表头
        /// 解决03版本和07版本Excel的访问驱动不兼容得问题
        /// </summary>
        /// <param name="excelPath">Excel文件全路径</param>
        /// <param name="strConn">连接字符串</param>
        /// <returns></returns>
        public static OleDbConnection OpenExcelEx(string excelPath, out string strConn)
        {
            OleDbConnection conn = null;

            //先用03版Excel连接方式   
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            if (!OpenExcelConnection(strConn, out conn))
            {
                //在尝试用07的连接方式
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
                if (!OpenExcelConnection(strConn, out conn)) return null;
            }

            return conn;
        }

        /// <summary>
        /// 打开Excel文件
        /// 解决03版本和07版本Excel的访问驱动不兼容得问题
        /// </summary>
        /// <param name="excelPath">Excel文件全路径</param>
        /// <param name="strConn">连接字符串</param>
        /// <returns></returns>
        public static OleDbConnection OpenExcel(string excelPath, out string strConn)
        {
            OleDbConnection conn = null;

            //先用03版Excel连接方式   
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"";
            if (!OpenExcelConnection(strConn, out conn))
            {
                //在尝试用07的连接方式
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
                if (!OpenExcelConnection(strConn, out conn)) return null;
            }

            return conn;
        }

        /// <summary>
        /// 打开Excel文件
        /// </summary>
        /// <param name="connectString">连接字符串</param>
        /// <param name="conn">连接信息</param>
        /// <returns></returns>
        private static bool OpenExcelConnection(string connectString, out OleDbConnection conn)
        {
            conn = new OleDbConnection(connectString);
            try
            {
                conn.Open();
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return false;
            }
        }
        #endregion



        /// <summary>
        ///  Excel中插入值，保存，数值传输
        /// </summary>
        /// <param name="excelpath">保存路径</param>
        /// <param name="sourcepath">excel模板路径</param>
        /// <param name="insertTable"></param>
        /// <returns></returns>
        public static bool InsertExcelTable(string excelpath, string sourcepath, System.Data.DataTable insertTable, ExcelStatPara excelPar)
        {
            Application excelapp = new ApplicationClass();
            Workbook mybook = null;
            try
            {
                #region 不进行提示是否以只读方式打开
                //mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                #endregion

                Worksheet mysheet = null;
                for (int i = 1; i <= mybook.Worksheets.Count; i++)
                {
                    mysheet = (Worksheet)mybook.Worksheets[i];
                    if (mysheet.Name == excelPar.SheetName)
                        break;
                }
                bool bPicture = false;
                if (string.IsNullOrEmpty(excelPar.PicturePath) == false && File.Exists(excelPar.PicturePath))//存在图片
                {
                    bPicture = true;
                }
                //mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[3];
                //mysheet.Columns.AutoFit();

                //excelapp.Cells[excelPar.DateX, excelPar.DateY] = excelPar.StatDate;
                if (excelPar.StatDate != null && string.IsNullOrEmpty(excelPar.StatDate) == false)
                {
                    mysheet.Cells[excelPar.DateX, excelPar.DateY] = excelPar.StatDate;
                }
                int startLine = excelPar.StartLine;
                Range range = null;
                for (int i = startLine; i < insertTable.Rows.Count + startLine; i++) //第一列日期
                {
                    if (bPicture == true)
                    {
                        range = null;
                        range = (Range)mysheet.Rows[i, Missing.Value];
                        range.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);//插入空行
                    }
                    for (int j = 1; j < insertTable.Columns.Count + 1; j++)
                    {
                        //excelapp.Cells[i, j] = insertTable.Rows[i - startLine][j - 1].ToString();//加行了用这个无效
                        mysheet.Cells[i, j] = insertTable.Rows[i - startLine][j - 1].ToString();

                    }
                }
                if (bPicture == true)
                {
                    mysheet.Select(Missing.Value);
                    range = null;
                    string position = excelPar.PicPosition;
                    position = position.Substring(0, 1) + (Convert.ToInt32(position.Substring(1)) + insertTable.Rows.Count).ToString();
                    range = mysheet.get_Range(position, Missing.Value);
                    range.Select();//不选插入图片会乱，先mysheet.Select(),要不会出错
                    Pictures pics = (Pictures)mysheet.Pictures(Missing.Value);
                    pics.Insert(excelPar.PicturePath, Missing.Value);
                }
                mybook.SaveCopyAs(excelpath);
                mybook.Close(false, Missing.Value, false);

                excelapp.DisplayAlerts = false;//很重要，要不删除sheet不成功
                //删除其他标签
                mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                for (int i = 1; i <= mybook.Sheets.Count; i++)
                {
                    mysheet = (Worksheet)mybook.Worksheets[i];
                    if (mysheet.Name != excelPar.SheetName)
                    {
                        mysheet.Delete();
                        i--;
                    }
                }
                excelapp.DisplayAlerts = true;//
                mybook.Save();
                mybook.Close(false, Missing.Value, false);
                excelapp.Workbooks.Close();
                excelapp.Quit();
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return false;
            }
            //finally
            //{
            //    if (mybook != null)
            //        mybook.Close(false, Missing.Value, false);
            //    excelapp.Workbooks.Close();
            //    excelapp.Quit();
            //}
        }
        /// <summary>
        /// 根据模板导出
        /// </summary>
        /// <param name="excelpath">文件输出全路径</param>
        /// <param name="sourcepath">模板文件全路径</param>
        /// <param name="insertTable">需要插入的dataTable</param>
        /// <param name="excelPar">关于excel控制参数，如：从哪一个开始填充数据，sheet的名称</param>
        /// <returns>wcj</returns>
        public static bool InsertExcelTable1(string excelpath, string sourcepath, System.Data.DataTable insertTable, ExcelStatPara excelPar)
        {
            Application excelapp = new ApplicationClass();
            Workbook mybook = null;
            try
            {
                mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Worksheet mysheet = null;
                for (int i = 1; i <= mybook.Worksheets.Count; i++)
                {
                    mysheet = (Worksheet)mybook.Worksheets[i];
                    if (mysheet.Name == excelPar.SheetName)
                        break;
                }
                int startLine = excelPar.StartLine;
                Range range = null;
                for (int i = startLine; i < insertTable.Rows.Count + startLine; i++) //从第6行开始
                {

                    for (int j = 1; j < insertTable.Columns.Count - 1; j++)  //+1 最后的两个字段不在excel中显示
                    {
                        mysheet.Cells[i, j] = insertTable.Rows[i - startLine][j].ToString();

                    }
                }

                mybook.SaveCopyAs(excelpath);
                mybook.Close(false, Missing.Value, false);

                excelapp.DisplayAlerts = false;//很重要，要不删除sheet不成功
                //删除其他sheet标签
                mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                for (int i = 1; i <= mybook.Sheets.Count; i++)
                {
                    mysheet = (Worksheet)mybook.Worksheets[i];
                    if (mysheet.Name != excelPar.SheetName)
                    {
                        mysheet.Delete();
                        i--;
                    }
                }
                excelapp.DisplayAlerts = true;//
                mybook.Save();
                mybook.Close(false, Missing.Value, false);
                excelapp.Workbooks.Close();
                excelapp.Quit();
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return false;
            }
        }
        /// <summary>
        ///  Excel中插入值，保存，数值传输
        /// </summary>
        /// <param name="excelpath">保存路径</param>
        /// <param name="sourcepath">excel模板路径</param>
        /// <param name="insertTable"></param>
        /// <returns></returns>
        public bool InsertExcelTable(string excelpath, string sourcepath, System.Data.DataTable insertTable)
        {
            try
            {

                Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                Microsoft.Office.Interop.Excel.Workbook mybook;

                #region 为了解决在进行汇总表导入过程中，不进行提示是否以只读方式打开,赖鸿祥


                //mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                mybook = excelapp.Workbooks.Open(sourcepath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                #endregion

                Microsoft.Office.Interop.Excel.Worksheet mysheet;
                mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
                mysheet.Columns.AutoFit();
                int sum = 0;
                double dsum = 0.0;
                excelapp.Cells[4, 1] = "合计";
                for (int k = 5; k <= insertTable.Rows.Count + 4; k++) //第一列日期
                {
                    excelapp.Cells[k, 1] = insertTable.Rows[k - 5][0].ToString();
                }
                for (int j = 2; j <= mysheet.UsedRange.Columns.Count; j++)  //数值
                {
                    sum = 0;
                    dsum = 0.0;
                    for (int i = 5; i <= insertTable.Rows.Count + 4; i++)
                    {
                        if (j <= 9)
                        {
                            sum += int.Parse(insertTable.Rows[i - 5][j - 1].ToString());
                        }
                        else
                        {
                            dsum += double.Parse(insertTable.Rows[i - 5][j - 1].ToString());
                        }
                        excelapp.Cells[i, j] = insertTable.Rows[i - 5][j - 1].ToString();
                    }
                    if (j <= 9)
                    {
                        excelapp.Cells[4, j] = sum;
                    }
                    else
                    {
                        excelapp.Cells[4, j] = dsum;
                    }
                }

                mybook.SaveCopyAs(excelpath);
                mybook.Close(false, Missing.Value, false);
                excelapp.Workbooks.Close();
                excelapp.Quit();
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.Error.Append(ex);
                return false;
            }
        }


        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="excelpath"></param>
        /// <param name="excelPar"></param>
        /// <returns></returns>
        public static bool InsertPicture(string excelpath, ExcelStatPara excelPar)
        {
            Application excelapp = new ApplicationClass();
            Workbook mybook;
            mybook = excelapp.Workbooks.Open(excelpath, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Worksheet mysheet = null;
            for (int i = 1; i <= mybook.Worksheets.Count; i++)
            {
                mysheet = (Worksheet)mybook.Worksheets[i];
                if (mysheet.Name == excelPar.SheetName)
                    break;
            }

            Range range = mysheet.get_Range(excelPar.PicPosition, Missing.Value);
            range.Select();
            Pictures pics = (Pictures)mysheet.Pictures(Missing.Value);
            pics.Insert(excelPar.PicturePath, Missing.Value);


            mybook.Save();
            mybook.Close(false, Missing.Value, false);
            excelapp.Workbooks.Close();
            excelapp.Quit();
            return true;
        }

        public static string SelectSheet(List<string> _strSheetName)
        {
            DevExpress.XtraEditors.XtraForm Form = new DevExpress.XtraEditors.XtraForm();
            DevExpress.XtraEditors.ComboBoxEdit cmbSelect = new DevExpress.XtraEditors.ComboBoxEdit();
            cmbSelect.Location = new System.Drawing.Point(29, 28);
            cmbSelect.Size = new System.Drawing.Size(223, 21);

            DevExpress.XtraEditors.BaseButton btnOK = new DevExpress.XtraEditors.BaseButton();
            btnOK.Location = new System.Drawing.Point(205, 56);
            btnOK.Size = new System.Drawing.Size(47, 23);
            btnOK.Text = "选择";
            btnOK.Click += new EventHandler(delegate(object sender, EventArgs e) { Form.Close(); });

            Form.AutoScaleDimensions = new System.Drawing.SizeF(7F, 14F);
            Form.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            Form.ClientSize = new System.Drawing.Size(284, 90);
            Form.Controls.Add(cmbSelect);
            Form.Controls.Add(btnOK);
            Form.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;
            Form.MaximizeBox = false;
            Form.MinimizeBox = false;
            Form.ShowIcon = false;
            Form.ShowInTaskbar = false;
            Form.ControlBox = false;
            Form.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            Form.Text = "选择Sheet页";
            Form.TopMost = true;
            cmbSelect.Properties.Items.AddRange(_strSheetName);
            Form.ShowDialog();

            return cmbSelect.SelectedItem.ToString();
        }

    }

/// <summary>
    /// Excel结构类
    /// </summary>
    public class ExcelStatPara
    {
        public ExcelStatPara()
        {

        }
        string _sheetName = "";  //Sheet名称
        public string SheetName
        {
            get { return _sheetName; }
            set { _sheetName = value; }
        }
        int _dateX;//日期位置第几行 ：I3为(3,9)
        public int DateX
        {
            set { _dateX = value; }
            get { return _dateX; }
        }
        int _dateY;//日期位置第几列
        public int DateY
        {
            set { _dateY = value; }
            get { return _dateY; }
        }

        int _startLine; //表格内容开始行
        public int StartLine
        {
            set { _startLine = value; }
            get { return _startLine; }
        }

        string _title = "";//标题
        public string Title
        {
            set { _title = value; }
            get { return _title; }
        }
        string _statDate = "";//日期
        public string StatDate
        {
            set
            {
                _statDate = value;
            }
            get { return _statDate; }
        }
        string _strFilter = "";//表头范围 eg"A5:J6";
        public string StrFilter
        {
            set { _strFilter = value; }
            get { return _strFilter; }
        }
        System.Data.DataTable _resultTable = null;//查询结果
        public System.Data.DataTable ResultTable
        {
            set
            {
                _resultTable = value;
            }
            get { return _resultTable; }
        }
        string _picturePath = ""; //有图片的话 存储位置
        public string PicturePath
        {
            get { return _picturePath; }
            set { _picturePath = value; }
        }
        string _picPosition = "";//图片位置，eg:A2
        public string PicPosition
        {
            get { return _picPosition; }
            set { _picPosition = value; }
        }

    }