spire.XLS Excel转html,[C#] 使用Spire.XLS 将excel转DataTable

///

/// Excel帮助类

///

public class ExcelHelpers

{

///

/// 将Excel以文件流转换DataTable

///

/// 是否有表头

/// 文件路径

/// 文件簿索引

public static DataTable ExcelToDataTableFormPath(bool hasTitle = true, string path = "", int tableindex = 0)

{

//新建Workbook

Workbook workbook = new Workbook();

//将当前路径下的文件内容读取到workbook对象里面

workbook.LoadFromFile(path);

//得到第一个Sheet页

Worksheet sheet = workbook.Worksheets[tableindex];

return SheetToDataTable(hasTitle, sheet);

}

///

/// 将Excel以文件流转换DataTable

///

/// 是否有表头

/// 文件流

/// 文件簿索引

public static DataTable ExcelToDataTableFormStream(bool hasTitle = true, Stream stream = null,int tableindex = 0)

{

//新建Workbook

Workbook workbook = new Workbook();

//将文件流内容读取到workbook对象里面

workbook.LoadFromStream(stream);

//得到第一个Sheet页

Worksheet sheet = workbook.Worksheets[tableindex];

return SheetToDataTable(hasTitle,sheet);

}

private static DataTable SheetToDataTable(bool hasTitle,Worksheet sheet)

{

int iRowCount = sheet.Rows.Length;

int iColCount = sheet.Columns.Length;

DataTable dt = new DataTable();

//生成列头

for (int i = 0; i < iColCount; i++)

{

var name = "column" + i;

if (hasTitle)

{

var txt = sheet.Range[1, i + 1].Text;

if (!string.IsNullOrEmpty(txt)) name = txt;

}

while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。

dt.Columns.Add(new DataColumn(name, typeof(string)));

}

//生成行数据

int rowIdx = hasTitle ? 2 : 1;

for (int iRow = rowIdx; iRow <= iRowCount; iRow++)

{

DataRow dr = dt.NewRow();

for (int iCol = 1; iCol <= iColCount; iCol++)

{

dr[iCol - 1] = sheet.Range[iRow, iCol].Text;

}

dt.Rows.Add(dr);

}

return dt;

}

}