using System;
using System.Data;
using System.Data.OleDb;
namespace Legendigital.Swim.Common
{
/// <summary>
/// Summary description for Excel.
/// </summary>
public class Excel
{
private const string STRING_CONECTTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 8.0;HDR=Yes;IMEX=1;"";
public Excel()
{
//
// TODO: Add constructor logic here
//
}
public static DataTable GetDataTable(string excelFilePath,string sql)
{
try
{
DataSet ds = new DataSet();
string connstring = string.Format(STRING_CONECTTIONSTRING,excelFilePath);
using(OleDbConnection cnn = new OleDbConnection(connstring))
{
cnn.Open();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql,cnn);
dataAdapter.Fill(ds);
cnn.Close();
return ds.Tables[0];
}
}
finally
{
}
}
public static DataSet FillDataSet(string excelFilePath,string sql,DataSet ds)
{
DataTable dt = GetDataTable(excelFilePath,sql);
foreach(DataRow dr in dt.Rows)
{
try
{
DataRow newdr = ds.Tables[0].NewRow();
foreach(DataColumn dc in dt.Columns)
{
if(ds.Tables[0].Columns.Contains(dc.ColumnName))
{
newdr[dc.ColumnName] = dr[dc.ColumnName];
}
}
ds.Tables[0].Rows.Add(newdr);
}
catch
{
}
}
ds.AcceptChanges();
return ds;
}
}
}