using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
namespace cltStammdatenImporterGUI
{
public class ExcelFile
{
public static DataSet DataSetFromExcelFile(string sExcelFilename, string sExcelRange = "")
{
DataSet ds = new DataSet();
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sExcelFilename + ";Extended Properties='Excel 12.0 XML;HDR=NO;IMEX=1;';";
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
// Get all Sheets in Excel File
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
foreach (DataRow dr in dtSheet.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$"))
continue;
// Get all rows from the Sheet. sExcelRange must be in the form A1:C10 or sim or just empty
cmd.CommandText = "SELECT * FROM [" + sheetName + sExcelRange + "]";
DataTable dt = new DataTable();
dt.TableName = sheetName;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
ds.Tables.Add(dt);
}
cmd = null;
conn.Close();
}
return ds;
}
}
}
No comments:
Post a Comment
Your comment will be visible after approval.