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} 加载成功!!");
}
}
}
}