C#锁定EXCEL工作表

来源：互联网发布：网络电视不清楚怎么办编辑：程序博客网时间：2024/05/16 06:15

对Excel操作时，由于使用权限的不同，可能对表格的操作权限也不一样。EXCEL提供了保护工作表以及允许编辑单元格功能。相应的在C#中就可以对Excel表格进行操作。

主要用Protect（）方法保护工作表，Worksheet.Protection.AllowEditRanges设置允许编辑的单元格。

下面的代码示例演示如何实现对EXCEL进行保护的操作。

public void CreateExcel()
    {
        //创建一个Excel文件
        Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook excelWorkbook = null;
        Microsoft.Office.Interop.Excel.Worksheet excelSheet = null;
        myExcel.Application.Workbooks.Add(true);

//让Excel文件可见
myExcel.Visible = true;

myExcel.Cells[1, 4] = "普通报表";

        //逐行写入数据
        for (int i = 0; i < 11; i++)
        {
            for (int j = 0; j < 7; j++)
            {
                //以单引号开头，表示该单元格为纯文本
                myExcel.Cells[2 + i, 1 + j] = "'" + i;
            }
        }

        try
        {
            string excelTemp ="c:\\a.xls";

            //excelWorkbook = myExcel.Workbooks[1];
            excelWorkbook = myExcel.ActiveWorkbook;
            excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.ActiveSheet;

            //设定允许操作的单元格
            Microsoft.Office.Interop.Excel.AllowEditRanges ranges = excelSheet.Protection.AllowEditRanges;
            ranges.Add("Information",
                myExcel.Application.get_Range("B2", "B2"),
                Type.Missing);

            //保护工作表
            excelSheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, true, Type.Missing, Type.Missing);

            //Realease the com object
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);
            excelSheet = null;

            //Save the result to a temp path
            excelWorkbook.SaveAs(excelTemp, Excel.XlFileFormat.xlWorkbookNormal,
                                 null, null, false, false,
                                 Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
                                 Type.Missing, Type.Missing,Type.Missing,Type.Missing);
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (excelWorkbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                excelWorkbook = null;
            }
            if (myExcel != null)
            {
                myExcel.Workbooks.Close();
                myExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
                myExcel = null;
            }

            GC.Collect();
        }
    }