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

最新推荐文章于 2022-02-25 18:19:43 发布

原创最新推荐文章于 2022-02-25 18:19:43 发布 · 196 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Excel #Silverlight #工作 #浏览器 #C

Silverlight 4 专栏收录该内容

6 篇文章

订阅专栏

本文提供了一套用于操作Excel文件的API接口实现，包括创建、打开、读取、编辑及保存等功能。通过这些API，开发者能够在脱离浏览器环境下高效地进行Excel文件处理。

首先需要引用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;
            }
        }

    }
}