winform excel导入--自带office.interop.excel方式

用npoi方式，遇到一个问题，有的excel用加密软件(盖章，只读等)生成的解析不了，所以换成自带的方式，可以解决。

需要引用系统自带Microsoft.office.interop.excel

public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string f = @"D:kintest.xlsx";
            CreateExcelFile(f);
            WriteToExcel(f, "file2", "编号2", "姓名2");

        }
        private void CreateExcelFile(string FileName)
        {
            if (File.Exists(FileName))
            {
                File.Delete(FileName);
            }
            //create
            object Nothing = System.Reflection.Missing.Value;
            var app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(Nothing);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[1];
            worksheet.Name = "Work";
            //headline
            worksheet.Cells[1, 1] = "员工编号";
            worksheet.Cells[1, 2] = "姓名";
            worksheet.Cells[1, 3] = "A卡";
            worksheet.Cells[1, 4] = "B卡";
            worksheet.Cells[1, 5] = "开通餐卡";
            worksheet.Cells[1, 6] = "备注";

            worksheet.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
            workBook.Close(false, Type.Missing, Type.Missing);
            app.Quit();
            KillExcel.Kill(new IntPtr(app.Hwnd));

            app = null;
        }
        private void WriteToExcel(string excelName, string filename, string findString, string replaceString)
        {
            //open
            object Nothing = System.Reflection.Missing.Value;
            var app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            Microsoft.Office.Interop.Excel.Workbook mybook = app.Workbooks.Open(excelName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
            Microsoft.Office.Interop.Excel.Worksheet mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
            mysheet.Activate();
            //get activate sheet max row count
            int maxrow = mysheet.UsedRange.Rows.Count + 1;
            mysheet.Cells[maxrow, 1] = filename;
            mysheet.Cells[maxrow, 2] = findString;
            mysheet.Cells[maxrow, 3] = replaceString;
            mybook.Save();
            mybook.Close(false, Type.Missing, Type.Missing);
            mybook = null;
            //quit excel app
            app.Quit();
            KillExcel.Kill(new IntPtr(app.Hwnd));

            app = null;
        }

        private void OpenExcel(string strFileName)
        {
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//lauch excel application
            if (excel == null)
            {
                //Response.Write("<script>alert('Can't access excel')</script>");
            }
            else
            {
                excel.Visible = false; excel.UserControl = true;
                // 以只读的形式打开EXCEL文件
                Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, false, missing, missing, missing,
                 missing, missing, missing, true, missing, missing, missing, missing, missing);
                //取得第一个工作薄
                //Worksheet ws = (Worksheet)wb.Worksheets.get_Item(2);
                Worksheet ws = (Worksheet)excel.ActiveSheet;

                //取得总记录行数   (包括标题列)
                int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
                //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数


                //取得数据范围区域 (不包括标题列) 
                Range rng1 = ws.Cells.get_Range("B2", "B" + rowsint);   //item

                Range rng2 = ws.Cells.get_Range("E2", "E" + rowsint); //Customer
                object[,] arryItem = (object[,])rng1.Value2;   //get range's value
                object[,] arryCus = (object[,])rng2.Value2;
                //将新值赋给一个数组
                string[,] arry = new string[rowsint - 1, 2];
                for (int i = 1; i <= rowsint - 1; i++)
                {
                    //Item_Code列
                    //arry[i - 1, 0] = arryItem[i, 1].ToString();
                    //Customer_Name列
                    //arry[i - 1, 1] = arryCus[i, 1].ToString();

                    string i1 = arryItem[i, 1] == null ? "" : arryItem[i, 1].ToString();
                    string i2 = arryCus[i, 1] == null ? "" : arryCus[i, 1].ToString();
                    Console.WriteLine(i1 + "--" + i2);
                }
                //Response.Write(arry[0, 0] + " / " + arry[0, 1] + "#" + arry[rowsint - 2, 0] + " / " + arry[rowsint - 2, 1]);
                Console.WriteLine("wb.ReadOnly--" + wb.ReadOnly);
                wb.Close();
            }
   
            excel.DisplayAlerts = false;
            excel.Quit();
            KillExcel.Kill(new IntPtr(excel.Hwnd));
     
            excel = null;
            ////Process[] procs = Process.GetProcessesByName("excel");

            ////foreach (Process pro in procs)
            ////{
            ////    pro.Kill();//没有更好的方法,只有杀掉进程
            ////}
    
            
        }

        //选择excel
        private void button3_Click(object sender, EventArgs e)
        {
            OpenFileDialog f = new OpenFileDialog();
            f.Multiselect = true;
            f.ShowDialog();
            string[] filenames = f.FileNames;

            if (filenames.Length > 0)
            {
                this.label1.Text = filenames[0];
                string strFile = System.IO.Path.GetFileName(filenames[0]);
                System.Data.DataTable dt = GetDataBySQLNew("McsDW", "select 1 from [McsDW].[dbo].[UserGateWayNumber] where ExcelFileName='" + strFile + "'");
                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {
                        MessageBox.Show("数据库里已经导入名称为 " + strFile + " 的excel表，请核对！");
                        this.label1.Text = "";
                    }
                }
            }
        }

        //对比数据
        private void button2_Click(object sender, EventArgs e)
        {
            if (this.label1.Text.Length < 1)
            {
                MessageBox.Show("请先选择excel文件！");
                return;
            }
            string sheetIndex = texBoxSheet.Text;
            int sheetInt = 1;
            int.TryParse(sheetIndex, out sheetInt);
            if (sheetInt < 1)
            {
                MessageBox.Show("sheet序号请填写整数！");
                return;
            }

            ReadFromExcelFile(this.label1.Text, sheetInt);
        }

        private void ReadFromExcelFile(string filePath, int sheetInt)
        {
            string extension = System.IO.Path.GetExtension(filePath);
            try
            {
                object missing = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//lauch excel application
                if (excel == null)
                {
                    //Response.Write("<script>alert('Can't access excel')</script>");
                }
                else
                {
                    excel.Visible = false; excel.UserControl = true;
                    // 以只读的形式打开EXCEL文件
                    Workbook wb = excel.Application.Workbooks.Open(filePath, missing, false, missing, missing, missing,
                     missing, missing, missing, true, missing, missing, missing, missing, missing);
                    //取得第一个工作薄
                    //Worksheet ws = (Worksheet)wb.Worksheets.get_Item(2);
                    Worksheet ws = (Worksheet)excel.ActiveSheet;

                    //取得总记录行数   (包括标题列)
                    int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
                    //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数

                    //取得数据范围区域 (不包括标题列) 
                    Range rngxuhao = ws.Cells.get_Range("A3", "A" + rowsint);
                    Range rngdanwei = ws.Cells.get_Range("B3", "B" + rowsint);
                    Range rngbumen = ws.Cells.get_Range("C3", "C" + rowsint);
                    Range rngbianhao = ws.Cells.get_Range("D3", "D" + rowsint);
                    Range rngname = ws.Cells.get_Range("E3", "E" + rowsint);
                    Range rngAka = ws.Cells.get_Range("F3", "F" + rowsint);
                    Range rngBka = ws.Cells.get_Range("G3", "G" + rowsint);
                    Range rngkai = ws.Cells.get_Range("H3", "H" + rowsint);

                    object[,] arryxuhao = (object[,])rngxuhao.Value2;   //get range's value
                    object[,] arrydanwei = (object[,])rngdanwei.Value2;
                    object[,] arrybumen = (object[,])rngbumen.Value2;
                    object[,] arrybianhao = (object[,])rngbianhao.Value2;
                    object[,] arryname = (object[,])rngname.Value2;
                    object[,] arryAka = (object[,])rngAka.Value2;
                    object[,] arryBka = (object[,])rngBka.Value2;
                    object[,] arrykai = (object[,])rngkai.Value2;

                    //将新值赋给一个数组
                    string[,] arry = new string[rowsint - 1, 2];
                    string userCount = "";
                    Dictionary<string, List<string>> userDic = new Dictionary<string, List<string>>();
                    for (int i = 1; i <= rowsint - 1; i++)
                    {
                        //Item_Code列
                        //arry[i - 1, 0] = arryItem[i, 1].ToString();
                        //Customer_Name列
                        //arry[i - 1, 1] = arryCus[i, 1].ToString();

                        string iA = arryxuhao[i, 1] == null ? "" : arryxuhao[i, 1].ToString();
                        string iB = arrydanwei[i, 1] == null ? "" : arrydanwei[i, 1].ToString();
                        string iC = arrybumen[i, 1] == null ? "" : arrybumen[i, 1].ToString();
                        string iD = arrybianhao[i, 1] == null ? "" : arrybianhao[i, 1].ToString();
                        string iE = arryname[i, 1] == null ? "" : arryname[i, 1].ToString();
                        string iF = arryAka[i, 1] == null ? "" : arryAka[i, 1].ToString();
                        string iG = arryBka[i, 1] == null ? "" : arryBka[i, 1].ToString();
                        string iH = arrykai[i, 1] == null ? "" : arrykai[i, 1].ToString();

                        if (iA.Contains("计数"))
                        {
                            int uxuhaoInt = 0;
                            int.TryParse(iH, out uxuhaoInt);

                            userCount = uxuhaoInt.ToString();
                        }
                        if (iA.Equals("") || iA.Equals("序号"))
                        {
                            continue;
                        }
                        if (iD.Equals(""))
                        {
                            continue;
                        }
                        if (iA.Contains("计数") || iA.Contains("负责人") || iA.Contains("开通餐卡"))
                        {
                            break;
                        }

                        List<string> ulist = new List<string>();
                        ulist.Add(iB);
                        ulist.Add(iC);
                        ulist.Add(iE);
                        ulist.Add(iH);
                        ulist.Add(iF);
                        ulist.Add(iG);
                        userDic.Add(iD, ulist);

                        Console.WriteLine(iA + "--" + iB + "--" + iC + "--" + iD + "--" + iE + "--" + iF + "--" + iG + "--" + iH);
                    }
                    //Response.Write(arry[0, 0] + " / " + arry[0, 1] + "#" + arry[rowsint - 2, 0] + " / " + arry[rowsint - 2, 1]);
                    Console.WriteLine("wb.ReadOnly--" + wb.ReadOnly);
                    wb.Close();

                    if (userCount.Equals("") && userCount.Equals("0"))
                        userCount = "没有计数";
                    DoUser(userDic, filePath, userCount, sheetInt);
                }
                
                excel.DisplayAlerts = false;
                excel.Quit();
                KillExcel.Kill(new IntPtr(excel.Hwnd));

                excel = null;
            }

            catch (Exception e)
            {
                //只在Debug模式下才输出
                Console.WriteLine(e.Message);
                MessageBox.Show(e.Message);
            }
        }

        private void DoUser(Dictionary<string, List<string>> userDic, string filePath, string userCount, int sheetInt)
        {
            if (userDic.Count < 1)
                return;
            string namev = "";
            string tip = "";
            System.Data.DataTable dtNew = new System.Data.DataTable();
            try
            {
                //
                System.Data.DataTable dt = GetDataBySQLNew("McsDW", "select * from UserViewAll_DW ");

                dtNew.Columns.Add("员工编号", Type.GetType("System.String"));
                dtNew.Columns.Add("姓名", Type.GetType("System.String"));
                dtNew.Columns.Add("A卡", Type.GetType("System.String"));
                dtNew.Columns.Add("B卡", Type.GetType("System.String"));
                dtNew.Columns.Add("开通餐卡", Type.GetType("System.String"));
                dtNew.Columns.Add("备注", Type.GetType("System.String"));

                string sql = "", sqlabnull = "";
                string remark = "";
                int passCount = 0;
                System.Data.DataTable dtNewABNull = dtNew.Clone();

                foreach (var d in userDic)
                {
                    //Console.Write(d.Key+"----"+d.Value);
                    //Console.WriteLine("
");
                    //namev = d.Value;
                    remark = "";
                    bool isExcel = false;
                    DataRow[] rows = dt.Select("PostionLevel='" + d.Key + "'");
                    //if (d.Value[3].Trim().Equals("否"))
                    //    continue;
                    //开通餐卡为空，默认为不开通
                    if (d.Value[3].Trim().Equals(""))
                    {
                        //Console.WriteLine(d.Key + "----" + d.Value + "----开通餐卡为空");
                        //isExcel = true;
                        //remark = "开通餐卡为空!";
                        continue;
                    }
                    //开通餐卡，填的不是A,B
                    if (!(d.Value[3].Trim().ToUpper().Equals("A") || d.Value[3].Trim().ToUpper().Equals("B")))
                    {
                        isExcel = true;
                        remark += "开通餐卡项填写错误，填的不是A,B";
                    }
                    //如果开通A卡但是A卡为空,B卡不为空，也提示
                    else if (d.Value[3].Trim().ToUpper().Equals("A") && d.Value[4].Trim().Equals("") && !d.Value[5].Trim().Equals(""))
                    {
                        isExcel = true;
                        remark += "需要开通A卡但是A卡为空，B卡不为空，请填B卡";
                    }
                    //如果开通B卡但是B卡为空，A卡不为空，也提示
                    else if (d.Value[3].Trim().ToUpper().Equals("B") && d.Value[5].Trim().Equals("") && !d.Value[4].Trim().Equals(""))
                    {
                        isExcel = true;
                        remark += "需要开通B卡但是B卡为空，A卡不为空，请填A卡";
                    }
                    else if (rows == null)
                    {
                        isExcel = true;
                        remark += "数据库为空";
                    }
                    else if (rows.Length < 1)
                    {
                        isExcel = true;
                        remark += "数据库找不到该用户";
                    }
                    else
                    {
                        //判断用户名，中文名字
                        if (!(d.Value[2].Trim().Equals(rows[0]["UserDisplayName"].ToString().Trim())))
                        {
                            isExcel = true;
                            remark += "数据库里该用户名不匹配:" + rows[0]["UserDisplayName"].ToString();
                        }
                        //如果开通A卡，判断A卡和数据库是否匹配
                        if (d.Value[3].Trim().ToUpper().Equals("A") && !(d.Value[4].Trim().Equals(rows[0]["GatewayNumber"].ToString().Trim())))
                        {
                            isExcel = true;
                            remark += "开通A卡,但数据库里该用户A卡不匹配:" + rows[0]["GatewayNumber"].ToString();
                        }
                        //如果开通B卡，判断B卡和数据库是否匹配
                        if (d.Value[3].Trim().ToUpper().Equals("B") && !(d.Value[5].Trim().Equals(rows[0]["GatewayNumber2"].ToString().Trim())))
                        {
                            isExcel = true;
                            remark += "开通B卡,但数据库里该用户B卡不匹配:" + rows[0]["GatewayNumber2"].ToString();
                        }
                        //string kastr = GetKa(rows[0]["GatewayNumber2"], rows[0]["GatewayNumber"]);
                        //if (kastr.Equals("") || kastr.Equals("没有卡号"))
                        //{
                        //    isExcel = true;
                        //    remark += "数据库里该用户卡号有误:" + kastr;
                        //}
                    }

                    string Gender = GetGender(d.Key);
                    //如果A卡和B卡都为空，也提示
                    if (d.Value[4].Trim().Equals("") && d.Value[5].Trim().Equals(""))
                    {
                        string abnull = "A,B卡都为空，请尽快启动办卡流程";
                        dtNewABNull.Rows.Add(new object[] { d.Key, d.Value[2], d.Value[4], d.Value[5], d.Value[3], abnull });

                        sqlabnull += string.Format("  insert into UserGateWayNumberABNull(UnitName,DepartmentName,PostionLevel,UserName,GatewayNumber,ExcelFileName,AorBcard,Remark,Gender) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
                            d.Value[0], d.Value[1], d.Key, d.Value[2], "", System.IO.Path.GetFileName(filePath), d.Value[3], abnull, Gender);
                        passCount++;
                        continue;
                    }

                    if (isExcel)
                    {
                        DataRow r = dtNew.NewRow();
                        r["员工编号"] = d.Key;
                        r["姓名"] = d.Value[2];
                        r["A卡"] = d.Value[4];
                        r["B卡"] = d.Value[5];
                        r["开通餐卡"] = d.Value[3];
                        r["备注"] = remark;

                        dtNew.Rows.Add(r);
                    }
                    else
                    {
                        string gNum = "";
                        if (d.Value[3].Trim().ToUpper().Equals("A"))
                            gNum = d.Value[4];
                        else
                            gNum = d.Value[5];

                        sql += string.Format("  insert into UserGateWayNumber(UnitName,DepartmentName,PostionLevel,UserName,GatewayNumber,ExcelFileName,AorBcard,Gender) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
                            d.Value[0], d.Value[1], d.Key, d.Value[2], gNum, System.IO.Path.GetFileName(filePath), d.Value[3], Gender);
                        passCount++;
                    }
                }
                
                int ucount = 0;
                int.TryParse(userCount, out ucount);
                if (dtNew.Rows.Count > 0)
                {
                    //DataRow r = dtNew.NewRow();
                    //r["员工编号"] = filePath;
                    //r["姓名"] = "";
                    //r["A卡"] = "";
                    //r["B卡"] = "";
                    //r["开通餐卡"] = "";
                    //r["备注"] = "";

                    //dtNew.Rows.Add(r);
                    //加入AB都为空的用户
                    foreach (DataRow ro in dtNewABNull.Rows)
                    {
                        dtNew.Rows.Add(ro.ItemArray);
                    }
                    //加入excel名
                    dtNew.Rows.Add(new object[] { filePath, "", "", "", "", "" });

                    DataTableToExcel2(dtNew);
                    tip = "总共处理了" + userDic.Count + "条数据，其中异常的有" + (dtNew.Rows.Count - 1).ToString() + "条。请查看D:/食堂餐卡统计.xlsx";
                }
                else if (ucount != passCount && !userCount.Equals("没有计数"))
                {
                    tip = "总共处理了" + userDic.Count + "条数据，其中合格数据有" + passCount + "条， excel表中计数的有" + ucount + "条。数据不匹配，请核查！";
                }
                else
                {
                    GetDataBySQLNew("McsDW", sql + sqlabnull);
                    tip = "总共处理了" + userDic.Count + "条数据，其中合格数据有" + (passCount - dtNewABNull.Rows.Count).ToString() + "条，数据已经录入到[McsDW].[dbo].[UserGateWayNumber]表中, AB卡都为空但是需要开通卡的用户数据有" + dtNewABNull.Rows.Count + "条，此数据录入到表UserGateWayNumberABNull";
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "--" + namev);
            }

            Console.Write("总共" + userDic.Count);
            MessageBox.Show(tip);
        }
        private string GetGender(string pl)
        {
            string sql = "select Gender from [McsDW].[dbo].[UserViewAll_DW] where PostionLevel='" + pl + "'";
            System.Data.DataTable dt = GetDataBySQLNew("McsDW", sql);
            if (dt == null || dt.Rows.Count < 1)
                return "";
            return dt.Rows[0][0] == null ? "" : dt.Rows[0][0].ToString();
        }
        private bool DataTableToExcel2(System.Data.DataTable dt)
        {
            bool result = false;

            string f = @"D:食堂餐卡统计.xlsx";
            CreateExcelFile(f);
            WriteToExcel(f, dt);

            return result;
        }
        private void WriteToExcel(string excelName, System.Data.DataTable dt)
        {
            //open
            object Nothing = System.Reflection.Missing.Value;
            var app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            Microsoft.Office.Interop.Excel.Workbook mybook = app.Workbooks.Open(excelName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
            Microsoft.Office.Interop.Excel.Worksheet mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
            mysheet.Activate();
            //get activate sheet max row count
            int maxrow = mysheet.UsedRange.Rows.Count + 1;
            for (int i = maxrow; i < dt.Rows.Count+maxrow; i++)
            {
                mysheet.Cells[i, 1] = dt.Rows[i-maxrow][0];
                mysheet.Cells[i, 2] = dt.Rows[i - maxrow][1];
                mysheet.Cells[i, 3] = dt.Rows[i - maxrow][2];
                mysheet.Cells[i, 4] = dt.Rows[i - maxrow][3];
                mysheet.Cells[i, 5] = dt.Rows[i - maxrow][4];
                mysheet.Cells[i, 6] = dt.Rows[i - maxrow][5];
            }
            
            mybook.Save();
            mybook.Close(false, Type.Missing, Type.Missing);
            mybook = null;
            //quit excel app
            app.Quit();
            KillExcel.Kill(new IntPtr(app.Hwnd));

            app = null;
        }
    
        private System.Data.DataTable GetDataBySQLNew(string database, string sql)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            string strServer = "Server=127.0.0.0; DataBase=McsDW; user id=sqluser; password=Pass@word123;";
            if (database.Equals("McsDW"))
            {
                strServer = "Server=127.0.0.0; DataBase=McsDW; user id=sqluser; password=Pass@word123;";
            }
            else if (database.Equals("McsSP"))
            {
                strServer = "Server=127.0.0.0; DataBase=McsSP; user id=sqluser; password=Pass@word123;";
            }
            using (System.Data.SqlClient.SqlConnection SqlCnn = new System.Data.SqlClient.SqlConnection(strServer))
            {
                SqlCnn.Open();
                System.Data.SqlClient.SqlDataAdapter SqlDa = new System.Data.SqlClient.SqlDataAdapter(sql, SqlCnn);
                DataSet DS = new DataSet();
                SqlDa.Fill(DS);
                SqlDa.Dispose();
                SqlCnn.Close();
                if (DS == null || DS.Tables == null || DS.Tables.Count < 1)
                    return null;
                dt = DS.Tables[0];
            }
            return dt;
        }

这种方式的话，导入一个excel，进程里就多了一个excel进程，必须kill掉，找到进程ID再kill

public class KillExcel
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);


        /// <summary>
        /// 强制关闭当前Excel进程
        /// </summary>
        public static void Kill(IntPtr intPtr)
        {
            try
            {
                Process[] ps = Process.GetProcesses();
                int ExcelID = 0;
                GetWindowThreadProcessId(intPtr, out ExcelID); //得到本进程唯一标志k   
                foreach (Process p in ps)
                {
                    if (p.ProcessName.ToLower().Equals("excel"))
                    {
                        if (p.Id == ExcelID)
                        {
                            p.Kill();
                        }
                    }
                }
            }
            catch
            {
                //不做任何处理   
            }
        }
    }