如何在C#中将所有Excel工作表导入DataSet
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18006318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to import all the Excel sheets to DataSet in C#
提问by Tarik
I've searched internet for this and couldn't really find a question like it. Everyone was looking for a way to import an individual sheet in the excel file but what I want is to import all the sheets in the file to DataTable's in DataSetwithout knowing the sheet names.
我已经在互联网上搜索过这个，但找不到类似的问题。每个人都在寻找一种在 excel 文件中导入单个工作表的方法，但我想要的是DataTable在DataSet不知道工作表名称的情况下将文件中的所有工作表导入到's in 中。
I've not done much things with Excel before. This a sample and partially working code I've found on the internet and it only parses the given sheet name:
我以前没有用 Excel 做过很多事情。这是我在互联网上找到的一个示例和部分工作代码，它只解析给定的工作表名称：
public static DataSet Parse(string fileName, string workSheetName)
{
string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
string query = string.Format("SELECT * FROM [{0}$]", workSheetName);
DataSet data = new DataSet();
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
adapter.Fill(data);
}
return data;
}
In the code above, as you see, workSheetName should be passed in so the query can know where to look at to import. In my case, I want it to traverse all the sheets no matter what they are named like and import them to individual DataTable's of a DataSet.
在上面的代码中，如您所见，应该传入 workSheetName，以便查询可以知道从何处导入。就我而言，我希望它无论遍历所有的床单什么，他们被命名为喜欢和它们导入到个人DataTable的一个DataSet。
So in essence, the final thing will be a DataSetin which each DataTableholds rows for each sheet in the imported file.
所以本质上，最后的东西将是一个DataSet，其中每个都DataTable包含导入文件中每个工作表的行。
采纳答案by Tarik
This is a code I came up with and it works perfect but I saw someone else already added an answer:
这是我想出的代码，它运行完美，但我看到其他人已经添加了答案：
static DataSet Parse(string fileName)
{
string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
DataSet data = new DataSet();
foreach(var sheetName in GetExcelSheetNames(connectionString))
{
using (OleDbConnection con = new OleDbConnection(connectionString))
{
var dataTable = new DataTable();
string query = string.Format("SELECT * FROM [{0}]", sheetName);
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
adapter.Fill(dataTable);
data.Tables.Add(dataTable);
}
}
return data;
}
static string[] GetExcelSheetNames(string connectionString)
{
OleDbConnection con = null;
DataTable dt = null;
con= new OleDbConnection(connectionString);
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheetNames = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheetNames[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheetNames;
}
回答by Avitus
Because I was bored:
因为我很无聊：
static void Main(string[] args)
{
string filename = @"c:\temp\myfile.xlsx";
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0; " +
"data source='" + filename + "';" +
"Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
myConnection.Open();
DataTable mySheets = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
DataSet ds = new DataSet();
DataTable dt;
for (int i = 0; i <= mySheets.Rows.Count; i++)
{
dt = makeDataTableFromSheetName(filename, mySheets.Rows[i]["TABLE_NAME"].ToString());
ds.Tables.Add(dt);
}
}
private static DataTable makeDataTableFromSheetName(string filename, string sheetName)
{
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0; " +
"data source='" + filename + "';" +
"Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
DataTable dtImport = new DataTable();
System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "$]", myConnection);
myImportCommand.Fill(dtImport);
return dtImport;
}
回答by user951083
The function that was suggested by Avitus is correct but it has logica error, you must rewrite in :
Avitus 建议的函数是正确的，但它有逻辑错误，你必须重写：
DataTable dtImport = new DataTable();
using ( System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0; " +
"data source='" + filename + "';" +
"Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ")){
using ( System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "$]", myConnection))
myImportCommand.Fill(dtImport);
} return dtImport;
this is correct, otherwise you must dispose connection and dataadapter manually.
这是正确的，否则您必须手动配置连接和数据适配器。
回答by Koray
This might not be the best and the fastest one, but its another way (Edit- added elimination of blank cells):
这可能不是最好和最快的，但它是另一种方式（编辑添加消除空白单元格）：
public static DataSet ReadWorkbook(string excelFileName, bool useFirstRowAsColumnName = false)
{
var excel = new Microsoft.Office.Interop.Excel.Application();
var workBook = excel.Workbooks.Open(excelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);//MLHIDE
try
{
System.Data.DataSet ds = new DataSet(excelFileName);
foreach (var sheet0 in workBook.Worksheets)
{
var sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet0;
try
{
var dt = readSheet(sheet, useFirstRowAsColumnName);
if (dt != null)
ds.Tables.Add(dt);
}
finally
{
releaseObject(sheet);
}
}
return ds;
}
finally
{
workBook.Close(true, null, null);
excel.Quit();
releaseObject(workBook);
releaseObject(excel);
}
}
/// <summary>
/// Returns null for empty sheets or if sheet is not found.
/// </summary>
public static DataTable ReadSheet(string excelFileName, string sheetName, bool useFirstRowAsColumnName = false)
{
var excel = new Microsoft.Office.Interop.Excel.Application();
var workBook = excel.Workbooks.Open(excelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);//MLHIDE
try
{
foreach (var sheet0 in workBook.Worksheets)
{
var sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet0;
try
{
if (sheet.Name.Equals_Wildcard(sheetName))
{
var dt = readSheet(sheet, useFirstRowAsColumnName);
if (dt != null)
return dt;
}
}
finally
{
releaseObject(sheet);
}
}
return null;
}
finally
{
workBook.Close(true, null, null);
excel.Quit();
releaseObject(workBook);
releaseObject(excel);
}
}
/// <summary>
/// Returns null for empty sheets
/// </summary>
private static DataTable readSheet(Microsoft.Office.Interop.Excel.Worksheet sheet, bool useFirstRowAsColumnName = false)
{
using (Dece.Common.BeginChangeCurrentCultureBlock_EN_us())
{
var range = sheet.UsedRange;
try
{
object[,] values = (object[,])range.Value2;
int rowCount = values.GetLength(0);
int colCount = values.GetLength(1);
int rowCount0 = rowCount;
int colCount0 = colCount;
#region find row-col count
{
bool ok = false;
for (int row = rowCount; row > 0; row--)
if (!ok)
for (int col = colCount; col > 0; col--)
{
var val = values[row, col];
if ((val != null) && (!System.Convert.ToString(val).IsNullOrEmpty()))
{
rowCount = row;
ok = true;
break;
}
}
else
break;
}
{
bool ok = false;
for (int col = colCount; col > 0; col--)
if (!ok)
for (int row = rowCount; row > 0; row--)
{
var val = values[row, col];
if ((val != null) && (!System.Convert.ToString(val).IsNullOrEmpty()))
{
colCount = col;
ok = true;
break;
}
}
else
break;
}
#endregion
if ((rowCount > 0) && (colCount > 0))
{
var dt = new DataTable(sheet.Name);
dt.BeginLoadData();
try
{
for (int col = 1; col <= colCount; col++)
dt.Columns.Add_RenameIfRequired(useFirstRowAsColumnName ? values[1, col].ToString_NullProof() : col.ToString());
var arr = new object[colCount];
for (int row = useFirstRowAsColumnName ? 1 : 0; row < rowCount; row++)
{
for (int col = 1; col <= colCount; col++)
arr[col - 1] = values[row + 1, col];
dt.Rows.Add(arr);
}
}
finally
{
dt.EndLoadData();
}
return dt;
}
else
return null;
}
finally
{
releaseObject(range);
}
}
}
private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
throw new Exception("Unable to release the Object " + ex.ToString(), ex);//MLHIDE
}
finally
{
GC.Collect();
}
}
