Silverlight使用Com组件操作Excel的封装类（1）

我就是我 2022-04-22 07:00 151阅读 0赞

首先需要引用Microsoft.CSharp.dll  和 System.Core.dll。

然后需要脱离浏览器才能运行调试

判断“是否脱离浏览器”和“提升信任”的方法

if (!Application.Current.IsRunningOutOfBrowser)
                {
                    LSWindow.Alert("Excel导入功能必须在脱离浏览器环境下运行！");
                    return;
                }
    
                if (!Application.Current.HasElevatedPermissions)
                {
                    LSWindow.Alert("用户的应用信任权限不足，不可访问用户本地资源！");
                    return;
                }

整个代码如下：

LSOOBXlsFactory.cs

namespace Longshine.SLLib.LSOOBExcel
    {
        public class LSOOBXlsFactory
        {
            private static LSOOBXlsFactory instance = null;
    
            private LSOOBXlsFactory()
            {
                instance = new LSOOBXlsFactory();
            }
    
            public static LSOOBXlsApplication CreateExcelApplication()
            {
                return new LSOOBXlsApplication();
            }
        }
    }

LSOOBXlsApplication.cs

using System.Runtime.InteropServices.Automation;
    using System.Reflection;
    using System;
    using System.IO;
    using System.Runtime.InteropServices;
    
    namespace Longshine.SLLib.LSOOBExcel
    {
        public class LSOOBXlsApplication
        {
            dynamic excel = null;
    
            public LSOOBXlsApplication()
            {
                excel = AutomationFactory.CreateObject("Excel.Application");
            }
    
            /// <summary>
            /// 是否打开Excel程序
            /// </summary>
            public bool IsOpen
            {
                get
                {
                    return excel.Visible;
                }
                set
                {
                    excel.Visible = value;
                }
            }
    
            /// <summary>
            /// 打开Excel工作薄
            /// </summary>
            /// <param name="fileName">Excel文件全路径</param>
            /// <param name="isOpenApplication">是否要打开Excel程序</param>
            /// <returns></returns>
            public LSOOBXlsWorkbook OpenWorkbook(FileStream fs, string extension)
            {
                string tempPath = LSOOBXlsHelper.GetSpecialFolderPath(Environment.SpecialFolder.MyDocuments);
                string fileName = LSOOBXlsHelper.CopyFileToTemp(fs, tempPath, extension);
    
                LSOOBXlsWorkbook workBook = new LSOOBXlsWorkbook();
                workBook.WorkBook = excel.Workbooks.Open(fileName,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value,
                    Missing.Value
                    );
    
                return workBook;
            }
    
    
            /// <summary>
            /// 关闭Excel
            /// </summary>
            public void Close()
            {
                if (excel != null)
                {
                    excel.Workbooks.Close();
                    excel.Quit();
                    excel = null;
                    GC.Collect();
                }
            }
    
            /// <summary>
            /// 设置是否Alert
            /// </summary>
            public bool DisplayAlerts
            {
                get
                {
                    return excel.DisplayAlerts;
                }
                set
                {
                    excel.DisplayAlerts = value;
                }
            }
    
        }
    }

LSOOBXlsWorkbook.cs

using System;
    using System.Collections.Generic;
    
    namespace Longshine.SLLib.LSOOBExcel
    {
        public class LSOOBXlsWorkbook
        {
            private dynamic workBook = null;
            private List<LSOOBXlsWorkSheet> workSheets = null;
    
            public LSOOBXlsWorkbook()
            {
    
            }
    
            public dynamic WorkBook
            {
                get
                {
                    return workBook;
                }
                set
                {
                    if (value != null)
                    {
                        workBook = value;
    
                        workSheets = new List<LSOOBXlsWorkSheet>();
                        int sheetCount = WorkSheetsCount;
                        for (int i = 1; i <= sheetCount; i++)
                        {
                            LSOOBXlsWorkSheet sheet = new LSOOBXlsWorkSheet();
                            sheet.WorkSheet = workBook.Worksheets[i];
                            workSheets.Add(sheet);
                        }
    
                    }
                }
            }
    
            /// <summary>
            /// 获得当前活动的工作页
            /// </summary>
            public LSOOBXlsWorkSheet ActiveSheet
            {
                get
                {
                    LSOOBXlsWorkSheet currentSheet = new LSOOBXlsWorkSheet();
                    currentSheet.WorkSheet = workBook.ActiveSheet;
                    return currentSheet;
                }
            }
    
            /// <summary>
            /// 获得指定工作页
            /// </summary>
            /// <param name="?"></param>
            /// <returns></returns>
            public LSOOBXlsWorkSheet GetSheet(string sheetName)
            {
                LSOOBXlsWorkSheet currentSheet = null;
                foreach (var s in workSheets)
                {
                    if (sheetName == s.Name)
                        currentSheet = s;
                }
    
                return currentSheet;
            }
    
            public LSOOBXlsWorkSheet GetSheet(int sheetIndex)
            {
                LSOOBXlsWorkSheet currentSheet = new LSOOBXlsWorkSheet();
                currentSheet.WorkSheet = workBook.Worksheets(sheetIndex);
                return currentSheet;
            }
    
            /// <summary>
            /// 获得工作页列表
            /// </summary>
            public List<LSOOBXlsWorkSheet> WorkSheets
            {
                get
                {
                    return workSheets;
                }
            }
    
            /// <summary>
            /// 获得工作页的个数
            /// </summary>
            public int WorkSheetsCount
            {
                get
                {
                    return workBook.Worksheets.Count;
                }
            }
    
            /// <summary>
            /// 增加工作页
            /// </summary>
            /// <param name="sheetName"></param>
            /// <returns></returns>
            public LSOOBXlsWorkSheet AddSheet(string sheetName)
            {
                dynamic newSheet = workBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                newSheet.Name = sheetName;
                LSOOBXlsWorkSheet sheet = new LSOOBXlsWorkSheet();
                sheet.WorkSheet = newSheet;
                workSheets.Add(sheet);
    
                return sheet;
            }
    
            /// <summary>
            /// 删除工作页
            /// </summary>
            /// <param name="sheetName"></param>
            public void RemoveSheet(string sheetName)
            {
                // GetSheet(sheetName).Delete();
            }
    
            /// <summary>
            /// 保存Excel
            /// </summary>
            public void Save()
            {
                workBook.Save();
            }
    
            /// <summary>
            /// 关闭Excel
            /// </summary>
            public void Close()
            {
                workBook.Close(Type.Missing, Type.Missing, Type.Missing);
            }
        }
    }

LSOOBXlsWorkSheet.cs

namespace Longshine.SLLib.LSOOBExcel
    {
        public class LSOOBXlsWorkSheet
        {
            dynamic workSheet = null;
    
            public LSOOBXlsWorkSheet()
            {
            }
    
            public dynamic WorkSheet
            {
                get
                {
                    return workSheet;
                }
                set
                {
                    if (value != null)
                        workSheet = value;
                }
            }
    
            /// <summary>
            /// 设置或获取工作页的名称
            /// </summary>
            public string Name
            {
                get
                {
                    return workSheet.Name;
                }
                set
                {
                    if (!string.IsNullOrEmpty(value))
                        workSheet.Name = value;
                }
            }
    
            public void Delete()
            {
                workSheet.Delete();
            }
    
            /// <summary>
            /// 激活该工作页
            /// </summary>
            public void Activate()
            {
                workSheet.Activate();
            }
    
            /// <summary>
            /// 获取指定的单元格
            /// </summary>
            /// <param name="row"></param>
            /// <param name="column"></param>
            /// <returns></returns>
            public LSOOBXlsCell GetCell(int row, int column)
            {
                LSOOBXlsCell cell = new LSOOBXlsCell();
                dynamic c = workSheet.Cells(row, column);
                cell.Cell = c;
    
                return cell;
            }
    
            /// <summary>
            /// 获取指定的区域
            /// </summary>
            /// <param name="range"></param>
            /// <returns></returns>
            public LSOOBXlsRange GetRange(string range)
            {
                LSOOBXlsRange rang = new LSOOBXlsRange();
                dynamic r = workSheet.Range(range);
                rang.Range = r;
    
                return rang;
            }
    
            /// <summary>
            /// 获取指定的区域
            /// </summary>
            /// <param name="fromCell">开始指定的单元格</param>
            /// <param name="toCell">结束指定的单元格</param>
            /// <returns></returns>
            public LSOOBXlsRange GetRange(string fromCell, string toCell)
            {
                return GetRange(fromCell + ":" + toCell);
            }
    
            /// <summary>
            /// 获取使用中的区域
            /// </summary>
            /// <returns></returns>
            public LSOOBXlsRange GetUsedRange()
            {
                LSOOBXlsRange rang = new LSOOBXlsRange();
                dynamic r = workSheet.UsedRange;
                rang.Range = r;
    
                return rang;
            }
    
            /// <summary>
            /// 获取指定的列
            /// </summary>
            /// <param name="columnName"></param>
            /// <returns></returns>
            public LSOOBXlsColumn GetColumn(string columnName)
            {
                LSOOBXlsColumn column = new LSOOBXlsColumn();
                dynamic c = workSheet.Columns(columnName);
                column.Column = c;
    
                return column;
            }
    
            /// <summary>
            /// 插入一行
            /// </summary>
            /// <param name="rowIndex"></param>
            public void InsertRow(int rowIndex)
            {
                workSheet.Rows[rowIndex].Insert();
            }
            /// <summary>
            /// 插入一列
            /// </summary>
            /// <param name="columnIndex"></param>
            public void InsertColumn(int columnIndex)
            {
                workSheet.Columns[columnIndex].Insert();
            }
            /// <summary>
            /// 删除一行
            /// </summary>
            /// <param name="rowIndex"></param>
            public void DeleteRow(int rowIndex)
            {
                workSheet.Rows[rowIndex].Delete();
            }
            /// <summary>
            /// 删除一列
            /// </summary>
            /// <param name="columnIndex"></param>
            public void DeleteColumn(int columnIndex)
            {
                workSheet.Columns[columnIndex].Delete();
            }
        }
    }

LSOOBXlsCell.cs

namespace Longshine.SLLib.LSOOBExcel
    {
        public class LSOOBXlsCell
        {
            dynamic cell = null;
    
            public LSOOBXlsCell()
            {
            }
    
            public dynamic Cell
            {
                get
                {
                    return cell;
                }
                set
                {
                    if (value != null)
                        cell = value;
                }
            }
            /// <summary>
            /// 值
            /// </summary>
            public object Value
            {
                get
                {
                    return cell.Value;
                }
                set
                {
                    if (value != null)
                        cell.Value = value;
                }
            }
            /// <summary>
            /// 列宽
            /// </summary>
            public int ColumnWidth
            {
                get
                {
                    return cell.ColumnWidth;
                }
                set
                {
                    cell.ColumnWidth = value;
                }
            }
    
            /// <summary>
            /// 是否加粗
            /// </summary>
            public bool FontBold
            {
                get
                {
                    return cell.Font.Bold;
                }
                set
                {
                    cell.Font.Bold = value;
                }
            }
    
            public int FontSize
            {
                get
                {
                    return cell.Font.Size;
                }
                set
                {
                    cell.Font.Size = value;
                }
            }
    
        }
    }