C#excel操作辅助类
来源:互联网 发布:中国名门望族排名,知乎 编辑:程序博客网 时间:2024/04/19 13:01
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Diagnostics;
using System.Runtime.InteropServices;
namespace Common
{
/// <summary>
/// Excel辅助类
/// </summary>
public class ExcelHelper
{
#region 数据属性
/// <summary>
/// 当前应用
/// </summary>
public Application Application { get; protected set; }
/// <summary>
/// 当前工作簿
/// </summary>
public Workbook Workbook { get; protected set; }
/// <summary>
/// 当前工作表
/// </summary>
public Worksheet Worksheet { get; protected set; }
/// <summary>
/// 文件路径
/// </summary>
public string FilePath { get; protected set; }
/// <summary>
/// 进程Id
/// </summary>
public int Pid { get; protected set; }
#endregion
#region 构造函数
public ExcelHelper()
{
}
public ExcelHelper(string filePath) : this()
{
Open(filePath);
}
~ExcelHelper()
{
Close();
}
#endregion
#region 公开方法
/// <summary>
/// 根据名字查找工作表
/// </summary>
public Worksheet FindWorksheet(string name)
{
foreach (Worksheet sheet in Workbook.Sheets)
{
if (sheet.Name == name)
{
return sheet;
}
}
return null;
}
/// <summary>
/// 切换当前工作表
/// </summary>
public void ChangeWorksheet(string name)
{
var ws = FindWorksheet(name);
if (ws == null) throw new Exception($"找不到工作表:{name}");
Worksheet = ws;
}
/// <summary>
/// 设置Worksheet中指定单元格的值
/// </summary>
public void SetCellValue(Worksheet worksheet, int row, int col, object value)
{
row++;
col++;
worksheet.Cells[row, col] = value;
}
/// <summary>
/// 设置Worksheet中指定单元格的值
/// </summary>
public void SetCellValue(string sheetName, int row, int col, object value)
{
var ws = FindWorksheet(sheetName);
if (ws == null) throw new Exception($"找不到工作表:{sheetName},是不是写错了?");
SetCellValue(ws, row, col, value);
}
/// <summary>
/// 设置当前Worksheet中指定单元格的值
/// </summary>
public void SetCellValue(int row, int col, object value)
{
if (Worksheet == null) throw new Exception($"当前工作表为空");
SetCellValue(Worksheet, row, col, value);
}
/// <summary>
/// 获取Worksheet中指定单元格的值
/// </summary>
public string GetCellValue(Worksheet worksheet, int row, int col)
{
row++;
col++;
var obj = worksheet.Cells[row, col] as Range;
return obj?.Value?.ToString();
}
/// <summary>
/// 获取Worksheet中指定单元格的值
/// </summary>
public string GetCellValue(string sheetName, int row, int col)
{
var ws = FindWorksheet(sheetName);
if (ws == null) throw new Exception($"找不到工作表:{sheetName},是不是写错了?");
return GetCellValue(ws, row, col);
}
/// <summary>
/// 获取当前Worksheet中指定单元格的值
/// </summary>
public string GetCellValue(int row, int col)
{
if (Worksheet == null) throw new Exception($"当前工作表为空.");
return GetCellValue(Worksheet, row, col);
}
/// <summary>
/// 保存
/// </summary>
public void Save()
{
Workbook.Save();
}
/// <summary>
/// 另存为
/// </summary>
public void SaveAs(string path, XlFileFormat format = XlFileFormat.xlExcel8)
{
if (!path.Contains(":"))
{
path = Path.Combine(Environment.CurrentDirectory, path);
}
Workbook.SaveAs(path, format, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
}
/// <summary>
/// 打开
/// </summary>
public void Open(string filePath)
{
try
{
if (!filePath.Contains(":"))
{
filePath = Path.Combine(Environment.CurrentDirectory, filePath);
}
var oldIds = Process.GetProcessesByName("EXCEL").Select(i => i.Id);
Application = new Application();
Workbook = Application.Workbooks.Add(filePath);
Application.CalculateBeforeSave = true;
Application.AlertBeforeOverwriting = false;
Application.DisplayAlerts = false;
var newIds = Process.GetProcessesByName("EXCEL").Select(i => i.Id);
Pid = newIds.FirstOrDefault(id => !oldIds.Contains(id));
FilePath = filePath;
}
catch (Exception ex)
{
Log.Error(ex);
}
}
/// <summary>
/// 关闭
/// </summary>
public void Close()
{
try
{
if (Workbook != null) Workbook.Close();
if (Application != null)
{
Application.Quit();
Marshal.ReleaseComObject(Application);
GC.Collect();
GC.WaitForPendingFinalizers();
}
if (Pid != 0)
{
var process = Process.GetProcessById(Pid);
process?.Kill();
}
}
catch (Exception e)
{
Log.Error(e);
}
finally
{
Workbook = null;
Application = null;
Pid = 0;
}
}
#endregion
#region 静态方法
/// <summary>
/// 环境检查
/// </summary>
public static bool CheckEnvironment()
{
try
{
var app = new Application();
app.Quit();
Marshal.ReleaseComObject(app);
return true;
}
catch (Exception ex)
{
return false;
}
}
#endregion
}
}
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Diagnostics;
using System.Runtime.InteropServices;
namespace Common
{
/// <summary>
/// Excel辅助类
/// </summary>
public class ExcelHelper
{
#region 数据属性
/// <summary>
/// 当前应用
/// </summary>
public Application Application { get; protected set; }
/// <summary>
/// 当前工作簿
/// </summary>
public Workbook Workbook { get; protected set; }
/// <summary>
/// 当前工作表
/// </summary>
public Worksheet Worksheet { get; protected set; }
/// <summary>
/// 文件路径
/// </summary>
public string FilePath { get; protected set; }
/// <summary>
/// 进程Id
/// </summary>
public int Pid { get; protected set; }
#endregion
#region 构造函数
public ExcelHelper()
{
}
public ExcelHelper(string filePath) : this()
{
Open(filePath);
}
~ExcelHelper()
{
Close();
}
#endregion
#region 公开方法
/// <summary>
/// 根据名字查找工作表
/// </summary>
public Worksheet FindWorksheet(string name)
{
foreach (Worksheet sheet in Workbook.Sheets)
{
if (sheet.Name == name)
{
return sheet;
}
}
return null;
}
/// <summary>
/// 切换当前工作表
/// </summary>
public void ChangeWorksheet(string name)
{
var ws = FindWorksheet(name);
if (ws == null) throw new Exception($"找不到工作表:{name}");
Worksheet = ws;
}
/// <summary>
/// 设置Worksheet中指定单元格的值
/// </summary>
public void SetCellValue(Worksheet worksheet, int row, int col, object value)
{
row++;
col++;
worksheet.Cells[row, col] = value;
}
/// <summary>
/// 设置Worksheet中指定单元格的值
/// </summary>
public void SetCellValue(string sheetName, int row, int col, object value)
{
var ws = FindWorksheet(sheetName);
if (ws == null) throw new Exception($"找不到工作表:{sheetName},是不是写错了?");
SetCellValue(ws, row, col, value);
}
/// <summary>
/// 设置当前Worksheet中指定单元格的值
/// </summary>
public void SetCellValue(int row, int col, object value)
{
if (Worksheet == null) throw new Exception($"当前工作表为空");
SetCellValue(Worksheet, row, col, value);
}
/// <summary>
/// 获取Worksheet中指定单元格的值
/// </summary>
public string GetCellValue(Worksheet worksheet, int row, int col)
{
row++;
col++;
var obj = worksheet.Cells[row, col] as Range;
return obj?.Value?.ToString();
}
/// <summary>
/// 获取Worksheet中指定单元格的值
/// </summary>
public string GetCellValue(string sheetName, int row, int col)
{
var ws = FindWorksheet(sheetName);
if (ws == null) throw new Exception($"找不到工作表:{sheetName},是不是写错了?");
return GetCellValue(ws, row, col);
}
/// <summary>
/// 获取当前Worksheet中指定单元格的值
/// </summary>
public string GetCellValue(int row, int col)
{
if (Worksheet == null) throw new Exception($"当前工作表为空.");
return GetCellValue(Worksheet, row, col);
}
/// <summary>
/// 保存
/// </summary>
public void Save()
{
Workbook.Save();
}
/// <summary>
/// 另存为
/// </summary>
public void SaveAs(string path, XlFileFormat format = XlFileFormat.xlExcel8)
{
if (!path.Contains(":"))
{
path = Path.Combine(Environment.CurrentDirectory, path);
}
Workbook.SaveAs(path, format, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
}
/// <summary>
/// 打开
/// </summary>
public void Open(string filePath)
{
try
{
if (!filePath.Contains(":"))
{
filePath = Path.Combine(Environment.CurrentDirectory, filePath);
}
var oldIds = Process.GetProcessesByName("EXCEL").Select(i => i.Id);
Application = new Application();
Workbook = Application.Workbooks.Add(filePath);
Application.CalculateBeforeSave = true;
Application.AlertBeforeOverwriting = false;
Application.DisplayAlerts = false;
var newIds = Process.GetProcessesByName("EXCEL").Select(i => i.Id);
Pid = newIds.FirstOrDefault(id => !oldIds.Contains(id));
FilePath = filePath;
}
catch (Exception ex)
{
Log.Error(ex);
}
}
/// <summary>
/// 关闭
/// </summary>
public void Close()
{
try
{
if (Workbook != null) Workbook.Close();
if (Application != null)
{
Application.Quit();
Marshal.ReleaseComObject(Application);
GC.Collect();
GC.WaitForPendingFinalizers();
}
if (Pid != 0)
{
var process = Process.GetProcessById(Pid);
process?.Kill();
}
}
catch (Exception e)
{
Log.Error(e);
}
finally
{
Workbook = null;
Application = null;
Pid = 0;
}
}
#endregion
#region 静态方法
/// <summary>
/// 环境检查
/// </summary>
public static bool CheckEnvironment()
{
try
{
var app = new Application();
app.Quit();
Marshal.ReleaseComObject(app);
return true;
}
catch (Exception ex)
{
return false;
}
}
#endregion
}
}
阅读全文
0 0
- C#excel操作辅助类
- C# 操作Excel 辅助功能
- C#Excel操作类
- C#EXCEL 操作类
- C#.net 数据操作--excel 操作类
- C# 操作word辅助类
- C#操作WORD辅助类
- FileHelper-文件操作辅助类
- 数据库JDBC操作辅助类
- GridView导出到excel辅助类
- C#EXCEL 操作类--C#ExcelHelper操作类
- C#EXCEL 操作类--C#ExcelHelper操作类
- C#EXCEL 操作类--C#ExcelHelper操作类
- 操作Excel(C#)
- 操作Excel(C#)
- [转贴]操作Excel(C#)
- 操作Excel(C#)
- 操作Excel(C#)
- Vue 爬坑之路(六)—— 使用 Vuex + axios 发送请求
- 计算机组成原理知识点梳理(一)
- mybatis缓存
- leetcode 448. Find All Numbers Disappeared in an Array
- Kotlin之let,apply,with,run函数区别
- C#excel操作辅助类
- 集合
- 计算机网络-常识1
- Solr6.6.1 在 Tomcat7.0.56 部署,页面提示SolrCore Initialization Failures
- C/C++ 语言中函数参数传递的三种方式
- 【编程题】Java编程题二(10道)
- windows环境下C++实现的多种目录监控和优劣解析
- Move Zeros
- Two