// 将excel导入到DataTable中
public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
IWorkbook workbook = null;
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet,返回空
{
return null;
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
DataColumn column = new DataColumn(i.ToString());
data.Columns.Add(column);
}
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
throw;
}
}
获取合并区域信息
private List<CellRangeAddress> GetMergedCellRegion(ISheet sheet)
{
int mergedRegionCellCount = sheet.NumMergedRegions;
var returnList = new List<CellRangeAddress>();
for (int i = 0; i < mergedRegionCellCount; i++)
{
returnList.Add(sheet.GetMergedRegion(i));
}
return returnList;
}
判断单元格是否被合并
/// <summary>
/// 判断单元格是否被合并
/// </summary>
/// <param name="cellIndex">单元格索引</param>
/// <param name="rowIndex">行号</param>
/// <param name="sheet">表sheet</param>
/// <param name="firstRegionRow">合并单元格的第一个行号</param>
/// <returns>是否合并</returns>
public bool IsMergedRegionCell(List<CellRangeAddress> regionLists, int cellIndex, int rowIndex, ISheet sheet, ref int firstRegionRow, ref int lastRegionRow, ref string value)
{
bool isMerged = false;
foreach (var cellRangeAddress in regionLists)
{
for (int i = cellRangeAddress.FirstRow; i <= cellRangeAddress.LastRow; i++)
{
if (rowIndex == i)
{
IRow ir = sheet.GetRow(i);
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
if (ir.GetCell(j) != null)
{
value = ir.GetCell(j).ToString();
}
}
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
if (cellIndex - 1 == j)
{
isMerged = true;
firstRegionRow = cellRangeAddress.FirstRow;
lastRegionRow = cellRangeAddress.LastRow;
//break;
return isMerged;
}
else
{
continue;
}
}
}
else
{
continue;
}
}
}
return isMerged;
}