public DataTable ReadExcel(string filepath, Func <string, int, Type> callback)
{
using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(0);
IRow header = sheet.GetRow(sheet.FirstRowNum);
List <int> columns = new List <int>();
DataTable dt = new DataTable();
CalculateTool.ReadHeader(header, dt, columns, callback);
CalculateTool.ReadBody(sheet, dt, columns);
return(dt);
}
}
public static void ReadHeader(IRow header, DataTable dt, List <int> columns)
{
for (int i = 0; i < header.LastCellNum; i++)
{
var cellValue = CalculateTool.GetCellValueType(header.GetCell(i));
if (cellValue == null || cellValue.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i));
}
else
{
dt.Columns.Add(new DataColumn(cellValue.ToString()));
}
columns.Add(i);
}
}
public static void ReadHeader(IRow header, DataTable dt, List <int> columns, Func <string, int, Type> callback)
{
for (int i = 0; i < header.LastCellNum; i++)
{
var cellValue = CalculateTool.GetCellValueType(header.GetCell(i));
if (cellValue == null || cellValue.ToString() == string.Empty)
{
var type = callback(cellValue.ToString(), i);
dt.Columns.Add(new DataColumn("Columns" + i, type));
}
else
{
var type = callback(cellValue.ToString(), i);
dt.Columns.Add(new DataColumn(cellValue.ToString(), type));
}
columns.Add(i);
}
}
public void WriteTableNoTitle(DataTable dt, string filepath)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet(dt.TableName == "" ? FILE_NAME : dt.TableName);
CalculateTool.WriteBody(sheet, dt);
using (MemoryStream ms = new MemoryStream())
{
xssfworkbook.Write(ms);
var buf = ms.ToArray();
using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
}
public override DataTable ReadExcel(string filepath)
{
if (string.IsNullOrEmpty(filepath))
{
throw new ArgumentNullException(nameof(filepath));
}
using (FileStream fs = new FileStream(_filepath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = WorkbookFactory.Create(fs);
DataTable dt = new DataTable();
ISheet sheet = workbook.GetSheetAt(0);
IRow header = sheet.GetRow(sheet.FirstRowNum);
List <int> columns = new List <int>();
CalculateTool.ReadHeader(header, dt, columns);
CalculateTool.ReadBody(sheet, dt, columns);
return(dt);
}
}
public override void WriteTable(DataTable dt, string filepath)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet(dt.TableName == "" ? FILE_NAME : dt.TableName);
IRow header = sheet.CreateRow(0);
CalculateTool.WriteHeader(header, dt);
CalculateTool.WriteBody(sheet, dt);
using (MemoryStream stream = new MemoryStream())
{
hssfworkbook.Write(stream);
var buf = stream.ToArray();
using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
}
public static void ReadBody(ISheet sheet, DataTable dt, List <int> columns)
{
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = CalculateTool.GetCellValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
