c# Excel转换成DataSet

vbloveshllm

于 2024-02-29 13:56:29 发布

阅读量702

点赞数 7

文章标签： c# excel java

本文链接：https://blog.csdn.net/vbloveshllm/article/details/136369392

版权

该代码片段展示了如何将Excel文件(.xls或.xlsx)转换为DataSet，通过使用不同版本的Excel工作簿API读取并处理工作表数据。

摘要由CSDN通过智能技术生成

/// <summary>
/// Excel转换成DataSet（.xlsx/.xls）
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string filePath, out string strMsg)
{
strMsg = "";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string fileType = Path.GetExtension(filePath).ToLower();
string fileName = Path.GetFileName(filePath).ToLower();
try
{
ISheet sheet = null;
int sheetNumber = 0;
// 加载Excel文件
using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
if (fileType == ".xlsx")
{
// 2007版本
XSSFWorkbook workbook = new XSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
//MessageBox.Show("Sheet数据获取失败，原因：" + strMsg);
}
}
}
}
else if (fileType == ".xls")
{
// 2003版本
HSSFWorkbook workbook = new HSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
//MessageBox.Show("Sheet数据获取失败，原因：" + strMsg);
}
}
}
}
}
return ds;
}
catch (Exception ex)
{
strMsg = ex.Message;
return null;
}