Excel读写库介绍 infragistics.Documents.Excel

 Infragistics Excel：Infragistics Excel是Infragistics品牌.Net应用程序下操作Excel表格的组件，同样也是托管代码。Infragistics系列控件是一套很好很强大的控件，PDMS程序的界面应该就是这套控件开发的。Infragistics Excel读写Excel速度快，性能较好。

类库下载连接

https://files.cnblogs.com/files/NanShengBlogs/infragistics.Documents.zip?t=1651220350

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Infragistics.Documents.Core;
using Infragistics.Documents.Excel;
using System.Reflection;
using System.Data;
 
namespace infragisticsTest
{
    class Program
    {
        static void Main(string[] args)
        {
            #region//处理依赖的程序集
            string[] requireddllNames = { "infragistics.Documents.Excel.dll", "infragistics.Documents.IO.dll", "infragistics.Documents.Core.dll" };
            var dirinfo = new DirectoryInfo(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location));
            if (dirinfo.GetFiles("*.dll").Count() == 0)
                HandleInfragisticsAssemblyDll(requireddllNames.ToList());
            else
            {
                var dlls = dirinfo.GetFiles("*.dll");
                if (dlls.Count() > 0)
                {
                    var dirDllNames = dlls.Select(c => c.Name);
                    if (!requireddllNames.All(t => dirDllNames.Any(b => b == t))) HandleInfragisticsAssemblyDll(requireddllNames.ToList());
                }
                else
                    HandleInfragisticsAssemblyDll(requireddllNames.ToList());
            }
 
            #endregion
 
            var fn = string.Empty;
            do
            {
                Console.WriteLine("输入文件的全路径:\n");
                fn = Console.ReadLine();
            }
            while (!File.Exists(fn));
 
            AppDomain.CurrentDomain.AssemblyResolve += (s, e)
                => AppDomain.CurrentDomain.GetAssemblies().FirstOrDefault(a => a.GetName().FullName.Split(',')[0] == e.Name.Split(',')[0]);
            //ReadDataFromExcel(fn);
            var ds = new DataSet(Path.GetFileNameWithoutExtension(fn));
            //用流读取xml文件
            using (var sr = new StreamReader(fn, Encoding.Default))
            {
                ds.ReadXml(sr);
                sr.Close();
            }
 
            if (DataSetToExcelDocument(ds, Path.ChangeExtension(fn, ".xlsx")))
                Console.WriteLine("完成!");
            else
                Console.WriteLine("失败!");
            Console.ReadLine();
        }
 
        //创建excel文件并将数据写入到excel文件(xlsx格式,2007)
        static void WriteDateToExcelFile(string extStr = ".xls")
        {
            var wb = new Infragistics.Documents.Excel.Workbook(GetFormat(extStr));
            var ws = wb.Worksheets.Add("tet");
            ws.GetCell("A2", Infragistics.Documents.Excel.CellReferenceMode.A1).Value = "tEST";
            var wbPath = $"{MethodBase.GetCurrentMethod().DeclaringType.Namespace}.{MethodBase.GetCurrentMethod().DeclaringType.Name}.{MethodBase.GetCurrentMethod().Name } {Guid.NewGuid().ToString()}{extStr}";
            using (var fs = new FileStream(wbPath, FileMode.CreateNew))
            {
                wb.Save(fs);
                fs.Close();
            }
        }
 
        static System.Data.DataSet ExcelDocumentToDataSet(string fn)
        {
            System.Data.DataSet ds = new System.Data.DataSet(Path.GetFileNameWithoutExtension(fn));
            if (!File.Exists(fn)) return null;
            using (var fs = new FileStream(fn, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var wb = Workbook.Load(fs);
                foreach (Worksheet ws in wb.Worksheets)
                {
                    System.Data.DataTable dt = new System.Data.DataTable(ws.Name);
                    //创建列
                    for (int i = 0; i < ws.Rows[0].Cells.Count(); i++)
                    {
                        dt.Columns.Add(ws.Rows[0].Cells[i].Value.ToString());
                    }
                    //写入行
                    for (int r = 1; r < ws.Rows.Count(); r++)
                    {
                        var dr = dt.NewRow();
                        var curRow = ws.Rows[r];
                        for (int c = 0; c < curRow.Cells.Count(); c++)
                        {
                            var curCell = curRow.Cells[c];
                            dr[c] = curCell.Value;
                        }
                        dt.Rows.Add(dr);
                    }
                    ds.Tables.Add(dt);
                }
                fs.Close();
            }
            return ds;
        }
 
        /// <summary>
        /// 将DataTable(DataSet)导出到Execl文档
        /// </summary>
        /// <param name="ds">传入一个DataSet</param>
        /// <param name="Outpath">导出路径（可以不加扩展名，不加默认为.xls）</param>
        /// <returns>返回一个Bool类型的值，表示是否导出成功,True表示导出成功，Flase表示导出失败</returns>
        static bool DataSetToExcelDocument(System.Data.DataSet ds, string Outpath)
        {
            bool result = false;
            try
            {
                if (ds == null || ds.Tables == null || ds.Tables.Count == 0 || string.IsNullOrEmpty(Outpath))
                    throw new Exception("输入的DataSet或路径异常");
                WorkbookFormat format = WorkbookFormat.Excel97To2003;
                if (Path.GetExtension(Outpath) != string.Empty) format = GetFormat(Path.GetExtension(Outpath));
                Outpath = Path.ChangeExtension(Outpath, GetExcelFileExtention(format));
                var wb = new Workbook(format);
                int sheetIndex = 0;
                foreach (DataTable dt in ds.Tables)
                {
                    sheetIndex++;
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        Worksheet sheet = wb.Worksheets.Add(string.IsNullOrEmpty(dt.TableName) ? ("sheet" + sheetIndex) : dt.TableName);//创建一个名称为Sheet0的表
                        int rowCount = dt.Rows.Count;//行数
                        int columnCount = dt.Columns.Count;//列数
                                                           //设置列头                      
                        var row = sheet.Rows[0];//excel第一行设为列头
                        for (int c = 0; c < columnCount; c++) row.Cells[c].Value = dt.Columns[c].ColumnName;
                        //设置每行每列的单元格,
                        for (int i = 0; i < rowCount; i++)
                        {
                            row = sheet.Rows[i + 1];
                            //excel第二行开始写入数据
                            for (int j = 0; j < columnCount; j++) row.Cells[j].Value = dt.Rows[i][j].ToString();
                        }
                    }
                }
                //向outPath输出数据
                if (File.Exists(Outpath))
                {
                    do
                        Outpath = Outpath.Replace(GetExcelFileExtention(format), "-New" + GetExcelFileExtention(format));
                    while (File.Exists(Outpath));
                }
                using (FileStream fs = new FileStream(Outpath, FileMode.CreateNew))
                {
                    wb.Save(fs);//向打开的这个xls文件中写入数据
                    result = true;
                    fs.Close();
                }
                return result;
            }
            catch (Exception)
            {
                return false;
            }
        }
 
        static void ReadDataFromExcel(string fn)
        {
            if (!File.Exists(fn))
            {
                Console.WriteLine($"文件不存在\"{fn}\"");
            }
            else
            {
                using (var fs = new FileStream(fn, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    var wb = Workbook.Load(fs);
                    foreach (Worksheet ws in wb.Worksheets)
                    {
                        for (int r = 0; r < ws.Rows.Count(); r++)
                        {
                            var curRow = ws.Rows[r];
                            for (int c = 0; c < curRow.Cells.Count(); c++)
                            {
                                var curCell = curRow.Cells[c];
                                Console.Write($"\t {curCell.ToString()}={curCell.Value},");
                            }
                            Console.WriteLine();
                        }
                    }
                    fs.Close();
                }
            }
            Console.ReadLine();
        }
 
        /// <summary>
        /// 根据excel文件格式返回文件后缀名
        /// </summary>
        /// <param name="format"></param>
        /// <returns>后缀名称,例如.xls/.xlsx</returns>
        public static string GetExcelFileExtention(WorkbookFormat format = WorkbookFormat.Excel97To2003)
        {
            string extName = ".xls";
 
            switch (format)
            {
                case WorkbookFormat.Excel97To2003:
                default:
                    extName = ".xls";
                    break;
                case WorkbookFormat.Excel97To2003Template:
                    extName = ".xlt";
                    break;
                case WorkbookFormat.Excel2007:
                    extName = ".xlsx";
                    break;
                case WorkbookFormat.Excel2007MacroEnabled:
                    extName = ".xlsm";
                    break;
                case WorkbookFormat.Excel2007MacroEnabledTemplate:
                    extName = ".xltm";
                    break;
                case WorkbookFormat.Excel2007Template:
                    extName = ".xltx";
                    break;
            }
            return extName;
        }
 
        /// <summary>
        /// 根据excel文件拓展名称返回excel文件格式
        /// </summary>
        /// <param name="sExtension">后缀名称,例如.xls/.xlsx</param>
        /// <returns></returns>
        public static WorkbookFormat GetFormat(string sExtension)
        {
            switch (sExtension)
            {
                case ".xls":
                    return WorkbookFormat.Excel97To2003;
                case ".xlt":
                    return WorkbookFormat.Excel97To2003Template;
                case ".xlsx":
                    return WorkbookFormat.Excel2007;
                case ".xltx":
                    return WorkbookFormat.Excel2007Template;
                case ".xlsm":
                    return WorkbookFormat.Excel2007MacroEnabled;
                case ".xltm":
                    return WorkbookFormat.Excel2007MacroEnabledTemplate;
                default:
                    return WorkbookFormat.Excel97To2003;
            }
        }
 
        // 1.(new StackTrace()).GetFrame(1) // 0为本身的方法；1为调用方法
        //2.(new StackTrace()).GetFrame(1).GetMethod().Name; // 方法名
        // 3.(new StackTrace()).GetFrame(1).GetMethod().ReflectedType.Name; // 类名
 
        /// <summary>
        /// 加载dll
        /// </summary>
        public static void HandleInfragisticsAssemblyDll(List<string> listDlls)
        {
 
            var avevaMarineDir = string.Empty;
 
            if (Environment.UserName != "sheng.nan")
            {
                //Find Aveva install Directory
                var avevaReg = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\WOW6432Node\AVEVA Solutions Ltd\Marine\12.15");
                if (avevaReg != null) avevaMarineDir = avevaReg.GetValue("Path").ToString();
            }
            if (avevaMarineDir == string.Empty)
                avevaMarineDir = @"\\10.19.80.8\Projects\CP\16.0_software issue & Developmet\MySdkDll";
            var di = new DirectoryInfo(avevaMarineDir);
            foreach (var item in listDlls)
            {
                var dllfn = Path.Combine(avevaMarineDir, item);
                var ass = AppDomain.CurrentDomain.Load(File.ReadAllBytes(dllfn));
                Console.WriteLine($"{dllfn} 加载成功!!");
            }
        }
    }
}