/// <summary>
/// 文件参数
/// </summary>
/// <param name="fileClass">文件类型</param>
/// <param name="folderIfFile">文件还是文件夹</param>
public FileUtilities(string fileClass, string folderIfFile)
{
Dictionary <string, string> valuePairs = null;
// 非空判断
if (string.IsNullOrWhiteSpace(fileClass) || string.IsNullOrWhiteSpace(folderIfFile))
{
valuePairs = new Dictionary <string, string>();
valuePairs.Add("fileClass", fileClass);
valuePairs.Add("folderIfFile", folderIfFile);
LogUtilities.IsNullErrorLog(valuePairs);
}
FileClass = fileClass;
FolderIfFile = folderIfFile;
}
/// <summary>
/// Excel 转换为 Datatable
/// </summary>
/// <param name="fileUrl">Excel文件路径</param>
/// <param name="sheetNum">工作表索引</param>
/// <param name="isFirstRowColumn">首行为列</param>
/// <returns></returns>
public DataTable ExcelToDataTable(string fileUrl, int sheetNum = 0, bool isFirstRowColumn = true)
{
Dictionary <string, string> valuePairs = null;
if (string.IsNullOrWhiteSpace(fileUrl))
{
valuePairs = new Dictionary <string, string>();
valuePairs.Add("fileUrl", fileUrl);
LogUtilities.IsNullErrorLog(valuePairs);
}
IWorkbook workbook = null;
ISheet sheet = null;
DataTable myTable = new DataTable();
try
{
using (var fs = new FileStream(fileUrl, FileMode.Open, FileAccess.Read))
{
if (fileUrl.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
else if (fileUrl.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
sheet = workbook.GetSheetAt(sheetNum);
}
//工作表不能为空
if (sheet == null)
{
string str = "";
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
str += workbook.GetSheetAt(i).SheetName + ",";
}
str = workbook.NumberOfSheets + str;
throw new Exception($"sheet不能为空！参数：{sheetNum} 工作簿信息：{str}");
}
//Excel最大列数
int MaxColumnNum = 0;
for (int i = 0; i < sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
if (row.LastCellNum > MaxColumnNum)
{
MaxColumnNum = row.LastCellNum;
}
}
//Excel行数
int MaxRowNum = sheet.LastRowNum;
//table新增列
for (int i = 0; i < MaxColumnNum; ++i)
{
//首行为列
if (isFirstRowColumn)
{
bool addEmptyCell = true;//是否添加空列
ICell cell = sheet.GetRow(0).GetCell(i);
if (cell != null)
{
//table列赋值
string cellValue = "";//列名
if (cell.CellType == CellType.Numeric)
{
cellValue = cell.NumericCellValue.ToString();
}
else
{
cellValue = cell.StringCellValue;
}
if (!string.IsNullOrWhiteSpace(cellValue))
{
//列数据为Excel的数据
addEmptyCell = false;
myTable.Columns.Add(new DataColumn(cellValue));
}
}
if (addEmptyCell)
{
myTable.Columns.Add(new DataColumn(""));//列数据为空
}
}
else
{
myTable.Columns.Add(new DataColumn(i + ""));
}
}
//起始行
int startRow = 0;
if (isFirstRowColumn)
{
startRow = 1;
}
//DataTable赋值
for (int i = startRow; i <= MaxRowNum; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
DataRow NewRow = myTable.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
{
ICell cell = row.GetCell(j);
string value = "";
if (cell != null)
{
//table行赋值
if (cell.CellType == CellType.Numeric)
{
value = cell.NumericCellValue.ToString();
if ((j == 0) && ((i == 6) || (i == 12)))
{
//特殊的几个单元格 转换为 日期格式
value = ToDateTimeValue(cell.NumericCellValue.ToString());
}
}
else
{
//row.GetCell(j).SetCellType(CellType.String);
value = cell.StringCellValue;
}
}
NewRow[j] = value;
}
myTable.Rows.Add(NewRow);
}
return(myTable);
}
catch (Exception ex)
{
throw ex;
}
}
