C#-读写Excel学习笔记
一、C#读写Excel的几种方法
二、C#读写Excel-Microsoft.Office.Interop.Excel
前提是本机须安装office才能运行，且不同的office版本之间可能会有兼容问题，从Nuget下载 Microsoft.Office.Interop.Excel。
三、NPOI方法（推荐、在不安装office的时候也是可以读写的，速度很快）：
1、Excel与DataTable转换：
/**
*┌──────────────────────────────────────────────────────────────┐
*│ 描 述：EXCEL操作类
*│ 作 者：执笔小白
*│ 版 本：1.2
*│ 创建时间：2020-11-26 10:16
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│ 命名空间: Util
*│ 类 名：NPOIHelper
*└──────────────────────────────────────────────────────────────┘
*/
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Threading;
/// <summary>
/// EXCEL操作类
/// </summary>
public class NPOIHelper
{
/// <summary>
/// 读取Excel文件到DataTable
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="sheetTitle">指定sheet标题；不指定时默认获取第1个sheet页</param>
/// <param name="sheetTitle_isContains">sheet标题包含sheetTitle非全等于</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static DataTable ReadExcel(string filePath, string sheetTitle = "", bool sheetTitle_isContains = false)
{
NPOI.SS.UserModel.ISheet sheet = null;
if (filePath.Contains(".xlsx")) // 2007 xlsx
{
#region 初始化信息
XSSFWorkbook hssfworkbook;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
if (string.IsNullOrEmpty(sheetTitle))
sheet = hssfworkbook.GetSheetAt(0);
else
{
if (sheetTitle_isContains)
{
if (hssfworkbook.NumberOfSheets > 0)
{
for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
{
if (hssfworkbook.GetSheetName(i).Contains(sheetTitle))
sheet = hssfworkbook.GetSheetAt(i);
}
}
if (sheet == null)
throw new Exception($"未找到名包含‘{sheetTitle}’的sheet页");
}
else
{
sheet = hssfworkbook.GetSheet(sheetTitle);
if (sheet == null)
throw new Exception($"未找到名为‘{sheetTitle}’的sheet页");
}
}
#endregion 初始化信息
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) // cell数
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
NPOI.SS.UserModel.ICell cell1 = row.GetCell(i + 1);
if (cell == null)
dr[i] = null;
else
{
dr[i] = cell.ToString();
//int rowIndex = cell.RowIndex;
//int cellIndex = cell.ColumnIndex;
//NPOIHelper_Extension.IsMergeCell(cell, out Dimension dimension); // 单元格跨度
//int rowNumber = dimension.RowSpan;
//int cellNumber = dimension.ColumnSpan;
}
}
dt.Rows.Add(dr);
}
return dt;
}
else // 2003 xls
{
#region 初始化信息
HSSFWorkbook hssfworkbook;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
if (string.IsNullOrEmpty(sheetTitle))
sheet = hssfworkbook.GetSheetAt(0);
else
{
if (sheetTitle_isContains)
{
if (hssfworkbook.NumberOfSheets > 0)
{
for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
{
if (hssfworkbook.GetSheetName(i).Contains(sheetTitle))
sheet = hssfworkbook.GetSheetAt(i);
}
}
if (sheet == null)
throw new Exception($"未找到名包含‘{sheetTitle}’的sheet页");
}
else
{
sheet = hssfworkbook.GetSheet(sheetTitle);
if (sheet == null)
throw new Exception($"未找到名为‘{sheetTitle}’的sheet页");
}
}
#endregion 初始化信息
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
dr[i] = null;
else
{
dr[i] = cell.ToString();
//int rowIndex = cell.RowIndex;
//int cellIndex = cell.ColumnIndex;
//NPOIHelper_Extension.IsMergeCell(cell, out Dimension dimension); // 单元格跨度
//int rowNumber = dimension.RowSpan;
//int cellNumber = dimension.ColumnSpan;
}
}
dt.Rows.Add(dr);
}
return dt;
}
}
/// <summary>
/// 将DataTable数据写入新Excel文件并保存到指定路径
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
public static void WriteExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
if (filePath.Contains(".xlsx")) // 2007 xlsx
{
XSSFWorkbook book = new XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
else // 2003 xls
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}
}
/// <summary>
/// 将DataTable数据写入到指定路径的Excel文件 - 使用表头模板
/// </summary>
/// <param name="templatePath">表头模板路径</param>
/// <param name="dt">数据源</param>
/// <param name="filePath">保存路径</param>
/// <param name="firstRowIndex">从第几行开始写入数据；Excel中行的计算方法，从0开始计数，即第一行为0</param>
/// <returns></returns>
public static (bool, string) WriteExcelByHeaderTemplate(string templatePath, DataTable dt, string filePath, int firstRowIndex = 1)
{
if (!string.IsNullOrEmpty(templatePath) && !string.IsNullOrEmpty(filePath)
&& null != dt && dt.Rows.Count > 0)
{
if (templatePath.Contains(".xlsx") && filePath.Contains(".xlsx")) // 2007 xlsx
{
XSSFWorkbook book = new XSSFWorkbook();
using (FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
{
book = new XSSFWorkbook(file);
}
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0);
// 从第n行开始写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + firstRowIndex);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
return (true, $"成功！文件保存路径为{filePath}");
}
else if (templatePath.Contains(".xls") && filePath.Contains(".xls")) // 2003 xls
{
HSSFWorkbook book;
using (FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
{
book = new HSSFWorkbook(file);
}
NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(0); // 找到sheet1
// 从第n行开始写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + firstRowIndex);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
return (true, $"成功！文件保存路径为{filePath}");
}
else
return (false, "表头模板文件类型与目标文件类型必须保持一致，本软件支持‘.xlsx’或‘.xls’！");
}
else
return (false, "请指定清楚‘表头模板路径’、‘保存路径’，并需要保存的数据不可为空！");
}
/// <summary>
/// 创建excel写入头
/// </summary>
/// <param name="fileName"></param>
/// <param name="headData"></param>
public static void CreateExcel(string fileName, IDictionary<int, string> headData)
{
using (FileStream fileWrite = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.Read))
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("加工数据");//获取工作表
IRow row = sheet.CreateRow(0); //创建新行
ICell cell = null;
foreach (KeyValuePair<int, string> keyValue in headData)
{
cell = row.CreateCell(keyValue.Key);
cell.SetCellValue(keyValue.Value);
}
workbook.Write(fileWrite);
workbook.Close();
}
}
private static Mutex mutex = new Mutex();
/// <summary>
/// 向已存在的excel写入数据
/// </summary>
/// <param name="fileName">excel路径</param>
/// <param name="headData">列索引<列索引，单元格值></param>
/// <param name="cellData">列索引<列索引，单元格值></param>
public static string AddExcelData(string fileName, IDictionary<int, string> headData, IDictionary<int, string> cellData)
{
mutex.WaitOne();
string ret = string.Empty;
FileStream fileRead = null;
FileStream fileWrite = null;
try
{
if (!File.Exists(fileName))
CreateExcel(fileName, headData);
IWorkbook workbook = null;
//读
fileRead = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
workbook = new HSSFWorkbook(fileRead);
ISheet sheet = workbook.GetSheetAt(0);
IRow row = sheet.CreateRow(sheet.LastRowNum + 1);
ICell cell = null;
foreach (KeyValuePair<int, string> keyValue in cellData)
{
cell = row.CreateCell(keyValue.Key);
cell.SetCellValue(keyValue.Value);
} //写
fileWrite = new FileStream(fileName, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);
workbook.Write(fileWrite);
workbook.Close();
ret = "写入成功";
}
catch (Exception ex)
{
ret = ex.Message.ToString();
}
finally
{
fileRead?.Close();
fileWrite?.Close();
}
mutex.ReleaseMutex();
return ret;
}
}2、导入Excel修改完再保存（举例为.xls格式）：
// 载入模板文件-xls
string excelTemplate_BaseMaterialTestReport = Path.Combine("目录", "文件名.xls");
if (excelTemplate_BaseMaterialTestReport.Equals(".xls"))
{
stringBuilder.Append("Excel文件格式不为‘.xls’");
return false;
}
HSSFWorkbook hssfworkbook;
using (FileStream file = new(excelTemplate_BaseMaterialTestReport, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); // 找到sheet1
// 写入数据
DateTime dateTime = DateTime.Now;
string dateTime_Date = dateTime.ToString(); // 日期
sheet.GetRow(3).GetCell(8).SetCellValue(dateTime_Date); // 日期
sheet.GetRow(5).GetCell(6).SetCellValue("备注"); // 备注
string targetFileName = "文件保存目标地址"; // 文件名
// 保存Excel到指定文件夹中（xls）
using (System.IO.MemoryStream ms = new())
{
hssfworkbook.Write(ms);
using (FileStream fs = new(targetFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
hssfworkbook = null;
}3、获取单元格是否跨行
/// <summary>
/// EXCEL操作类-扩展方法
/// 代码原文：https://blog.csdn.net/m0_37880556/article/details/127248207
/// </summary>
public static class NPOIHelper_Extension
{
/// <summary>
/// 判断指定行列所在的单元格是否为合并单元格，并且输出该单元格的维度
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="rowIndex">行索引，从0开始</param>
/// <param name="columnIndex">列索引，从0开始</param>
/// <param name="dimension">单元格维度</param>
/// <returns>返回是否为合并单元格的布尔(Boolean)值</returns>
public static bool IsMergeCell(this ISheet sheet, int rowIndex, int columnIndex, out Dimension dimension)
{
dimension = new Dimension
{
DataCell = null,
RowSpan = 1,
ColumnSpan = 1,
FirstRowIndex = rowIndex,
LastRowIndex = rowIndex,
FirstColumnIndex = columnIndex,
LastColumnIndex = columnIndex
};
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
sheet.IsMergedRegion(range);
//这种算法只有当指定行列索引刚好是合并单元格的第一个跨度行第一个跨度列时才能取得合并单元格的跨度
//if (range.FirstRow == rowIndex && range.FirstColumn == columnIndex)
//{
// dimension.DataCell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
// dimension.RowSpan = range.LastRow - range.FirstRow + 1;
// dimension.ColumnSpan = range.LastColumn - range.FirstColumn + 1;
// dimension.FirstRowIndex = range.FirstRow;
// dimension.LastRowIndex = range.LastRow;
// dimension.FirstColumnIndex = range.FirstColumn;
// dimension.LastColumnIndex = range.LastColumn;
// break;
//}
if ((rowIndex >= range.FirstRow && range.LastRow >= rowIndex) && (columnIndex >= range.FirstColumn && range.LastColumn >= columnIndex))
{
dimension.DataCell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
dimension.RowSpan = range.LastRow - range.FirstRow + 1;
dimension.ColumnSpan = range.LastColumn - range.FirstColumn + 1;
dimension.FirstRowIndex = range.FirstRow;
dimension.LastRowIndex = range.LastRow;
dimension.FirstColumnIndex = range.FirstColumn;
dimension.LastColumnIndex = range.LastColumn;
break;
}
}
bool result;
if (rowIndex >= 0 && sheet.LastRowNum > rowIndex)
{
IRow row = sheet.GetRow(rowIndex);
if (columnIndex >= 0 && row.LastCellNum > columnIndex)
{
ICell cell = row.GetCell(columnIndex);
result = cell.IsMergedCell;
if (dimension.DataCell == null)
{
dimension.DataCell = cell;
}
}
else
{
result = false;
}
}
else
{
result = false;
}
return result;
}
/// <summary>
/// 判断指定行列所在的单元格是否为合并单元格，并且输出该单元格的行列跨度
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="rowIndex">行索引，从0开始</param>
/// <param name="columnIndex">列索引，从0开始</param>
/// <param name="rowSpan">行跨度，返回值最小为1，同时表示没有行合并</param>
/// <param name="columnSpan">列跨度，返回值最小为1，同时表示没有列合并</param>
/// <returns>返回是否为合并单元格的布尔(Boolean)值</returns>
public static bool IsMergeCell(this ISheet sheet, int rowIndex, int columnIndex, out int rowSpan, out int columnSpan)
{
Dimension dimension;
bool result = sheet.IsMergeCell(rowIndex, columnIndex, out dimension);
rowSpan = dimension.RowSpan;
columnSpan = dimension.ColumnSpan;
return result;
}
/// <summary>
/// 判断指定单元格是否为合并单元格，并且输出该单元格的维度
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="dimension">单元格维度</param>
/// <returns>返回是否为合并单元格的布尔(Boolean)值</returns>
public static bool IsMergeCell(this ICell cell, out Dimension dimension)
{
return cell.Sheet.IsMergeCell(cell.RowIndex, cell.ColumnIndex, out dimension);
}
/// <summary>
/// 判断指定单元格是否为合并单元格，并且输出该单元格的行列跨度
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="rowSpan">行跨度，返回值最小为1，同时表示没有行合并</param>
/// <param name="columnSpan">列跨度，返回值最小为1，同时表示没有列合并</param>
/// <returns>返回是否为合并单元格的布尔(Boolean)值</returns>
public static bool IsMergeCell(this ICell cell, out int rowSpan, out int columnSpan)
{
return cell.Sheet.IsMergeCell(cell.RowIndex, cell.ColumnIndex, out rowSpan, out columnSpan);
}
/// <summary>
/// 返回上一个跨度行，如果rowIndex为第一行，则返回null
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="rowIndex">行索引，从0开始</param>
/// <param name="columnIndex">列索引，从0开始</param>
/// <returns>返回上一个跨度行</returns>
public static IRow PrevSpanRow(this ISheet sheet, int rowIndex, int columnIndex)
{
return sheet.FuncSheet(rowIndex, columnIndex, (currentDimension, isMerge) =>
{
//上一个单元格维度
Dimension prevDimension;
sheet.IsMergeCell(currentDimension.FirstRowIndex - 1, columnIndex, out prevDimension);
return prevDimension.DataCell.Row;
});
}
/// <summary>
/// 返回下一个跨度行，如果rowIndex为最后一行，则返回null
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="rowIndex">行索引，从0开始</param>
/// <param name="columnIndex">列索引，从0开始</param>
/// <returns>返回下一个跨度行</returns>
public static IRow NextSpanRow(this ISheet sheet, int rowIndex, int columnIndex)
{
return sheet.FuncSheet(rowIndex, columnIndex, (currentDimension, isMerge) =>
isMerge ? sheet.GetRow(currentDimension.FirstRowIndex + currentDimension.RowSpan) : sheet.GetRow(rowIndex));
}
/// <summary>
/// 返回上一个跨度行，如果row为第一行，则返回null
/// </summary>
/// <param name="row">行</param>
/// <returns>返回上一个跨度行</returns>
public static IRow PrevSpanRow(this IRow row)
{
return row.Sheet.PrevSpanRow(row.RowNum, row.FirstCellNum);
}
/// <summary>
/// 返回下一个跨度行，如果row为最后一行，则返回null
/// </summary>
/// <param name="row">行</param>
/// <returns>返回下一个跨度行</returns>
public static IRow NextSpanRow(this IRow row)
{
return row.Sheet.NextSpanRow(row.RowNum, row.FirstCellNum);
}
/// <summary>
/// 返回上一个跨度列，如果columnIndex为第一列，则返回null
/// </summary>
/// <param name="row">行</param>
/// <param name="columnIndex">列索引，从0开始</param>
/// <returns>返回上一个跨度列</returns>
public static ICell PrevSpanCell(this IRow row, int columnIndex)
{
return row.Sheet.FuncSheet(row.RowNum, columnIndex, (currentDimension, isMerge) =>
{
//上一个单元格维度
Dimension prevDimension;
row.Sheet.IsMergeCell(row.RowNum, currentDimension.FirstColumnIndex - 1, out prevDimension);
return prevDimension.DataCell;
});
}
/// <summary>
/// 返回下一个跨度列，如果columnIndex为最后一列，则返回null
/// </summary>
/// <param name="row">行</param>
/// <param name="columnIndex">列索引，从0开始</param>
/// <returns>返回下一个跨度列</returns>
public static ICell NextSpanCell(this IRow row, int columnIndex)
{
return row.Sheet.FuncSheet(row.RowNum, columnIndex, (currentDimension, isMerge) =>
row.GetCell(currentDimension.FirstColumnIndex + currentDimension.ColumnSpan));
}
/// <summary>
/// 返回上一个跨度列，如果cell为第一列，则返回null
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>返回上一个跨度列</returns>
public static ICell PrevSpanCell(this ICell cell)
{
return cell.Row.PrevSpanCell(cell.ColumnIndex);
}
/// <summary>
/// 返回下一个跨度列，如果columnIndex为最后一列，则返回null
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>返回下一个跨度列</returns>
public static ICell NextSpanCell(this ICell cell)
{
return cell.Row.NextSpanCell(cell.ColumnIndex);
}
/// <summary>
/// 返回指定行索引所在的合并单元格(区域)中的第一行(通常是含有数据的行)
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="rowIndex">行索引，从0开始</param>
/// <returns>返回指定列索引所在的合并单元格(区域)中的第一行</returns>
public static IRow GetDataRow(this ISheet sheet, int rowIndex)
{
return sheet.FuncSheet(rowIndex, 0, (currentDimension, isMerge) => sheet.GetRow(currentDimension.FirstRowIndex));
}
/// <summary>
/// 返回指定列索引所在的合并单元格(区域)中的第一行第一列(通常是含有数据的单元格)
/// </summary>
/// <param name="row">行</param>
/// <param name="columnIndex">列索引</param>
/// <returns>返回指定列索引所在的合并单元格(区域)中的第一行第一列</returns>
public static ICell GetDataCell(this IRow row, int columnIndex)
{
return row.Sheet.FuncSheet(row.RowNum, columnIndex, (currentDimension, isMerge) => currentDimension.DataCell);
}
private static T FuncSheet<T>(this ISheet sheet, int rowIndex, int columnIndex, Func<Dimension, bool, T> func)
{
//当前单元格维度
Dimension currentDimension;
//是否为合并单元格
bool isMerge = sheet.IsMergeCell(rowIndex, columnIndex, out currentDimension);
return func(currentDimension, isMerge);
}
/// <summary>
/// 表示单元格的维度，通常用于表达合并单元格的维度
/// </summary>
public struct Dimension
{
/// <summary>
/// 含有数据的单元格(通常表示合并单元格的第一个跨度行第一个跨度列)，该字段可能为null
/// </summary>
public ICell DataCell;
/// <summary>
/// 行跨度(跨越了多少行)
/// </summary>
public int RowSpan;
/// <summary>
/// 列跨度(跨越了多少列)
/// </summary>
public int ColumnSpan;
/// <summary>
/// 合并单元格的起始行索引
/// </summary>
public int FirstRowIndex;
/// <summary>
/// 合并单元格的结束行索引
/// </summary>
public int LastRowIndex;
/// <summary>
/// 合并单元格的起始列索引
/// </summary>
public int FirstColumnIndex;
/// <summary>
/// 合并单元格的结束列索引
/// </summary>
public int LastColumnIndex;
}
}四、附录：
附录-1：C# DataTable转化为自定义DataTable。
附录-2：Winform的Excle展示与编辑控件：DSOFramer：
① C# 如何在winform中嵌入Excel,内嵌Excel,word
② 显示 Word、Excel 等文件(WinForm 窗口中)
本文来自博客园，作者：꧁执笔小白꧂，转载请注明原文链接：https://www.cnblogs.com/qq2806933146xiaobai/p/14191662.html
浙公网安备 33010602011771号