public static System.Data.DataSet ImportCSVData(string fileName, bool firstRowContainsHeaders)
{
string sql = String.Format(SELECT_SQL, "*", "[" + GetFileName(fileName) + "]");
ExcelProperties properties = new ExcelProperties(firstRowContainsHeaders, true, true);
return(ExcelUtility.Fill(fileName, sql, null, properties));
}
public static System.Data.DataSet ImportExcelData(ImportType importType, string fileName, bool firstRowContainsHeaders, string worksheetName, string[] columns)
{
string selectColumns = string.Empty;
for (int i = 0; i < columns.Length; i++)
{
if (i == 0)
{
selectColumns = columns[i];
}
else
{
selectColumns += "," + columns[i];
}
}
if (selectColumns.Trim() == string.Empty)
{
selectColumns = "*";
}
string sql = String.Format(SELECT_SQL, selectColumns, "[" + worksheetName + "]");
ExcelProperties properties = new ExcelProperties(firstRowContainsHeaders, true, importType == ImportType.CSV);
return(ExcelUtility.Fill(fileName, sql, null, properties));
}
public static System.Data.DataSet ImportExcelData(ImportType importType, string fileName, bool firstRowContainsHeaders, string worksheetName, string startRange, string endRange)
{
string sql = String.Format(SELECT_SQL, "*", "[" + worksheetName + startRange + ":" + endRange + "]");
ExcelProperties properties = new ExcelProperties(firstRowContainsHeaders, true, importType == ImportType.CSV);
return(ExcelUtility.Fill(fileName, sql, null, properties));
}
public static bool Export(DataTable dt, string filepath, string tablename, ExcelProperties properties)
{
if (dt == null)
{
throw new Exception(ExcelResources.DataTableNull);
}
if (string.IsNullOrEmpty(filepath))
{
throw new Exception(ExcelResources.DataSourceEmpty);
}
ExcelHelper exlHelp = new ExcelHelper();
var culture = new CultureInfo("en-US");
bool rtn = exlHelp.Export(dt, filepath, tablename, properties, culture);
return(rtn);
}
public DataSet Fill(string filepath, string query, string[] parameters, ExcelProperties properties)
{
filepath = getCannolizePath(filepath);
//logger.Trace("Fill(string filepath, string query, string[] parameters, ExcelProperties properties)");
if (string.IsNullOrEmpty(filepath))
{
throw new Exception(ExcelResources.DataSourceEmpty);
}
if (string.IsNullOrEmpty(query))
{
throw new Exception(ExcelResources.QueryEmpty);
}
OpenXmlClient con = new OpenXmlClient();
DataSet ds = con.Fill(query, parameters, filepath);
return(ds);
}
public bool Export(DataTable dt, string filepath, string tablename, ExcelProperties properties, CultureInfo culture)
{
filepath = getCannolizePath(filepath);
if (culture == null)
{
culture = new System.Globalization.CultureInfo("en-EN");
}
dt.TableName = tablename;
MemoryStream ms = CreateExcelDocument(dt, culture);
FileStream file = new FileStream(filepath, FileMode.Create, FileAccess.Write);
ms.WriteTo(file);
file.Close();
ms.Close();
return(true);
}
public int ExecuteMultipleQuery(string filepath, string[] query, ExcelProperties properties)
{
filepath = getCannolizePath(filepath);
if (string.IsNullOrEmpty
(filepath))
{
throw new Exception(ExcelResources.DataSourceEmpty);
}
if (query.Length == 0)
{
throw new Exception(ExcelResources.QueryEmpty);
}
OpenXmlClient con = new OpenXmlClient();
con.ExecuteMultipleQuery(filepath, query);
return(1);
}
public static int ExecuteMultipleQuery(string filepath, string[] query, ExcelProperties properties)
{
if (string.IsNullOrEmpty(filepath))
{
throw new Exception(ExcelResources.DataSourceEmpty);
}
if (query == null)
{
throw new Exception(ExcelResources.QueryArrayNull);
}
foreach (string x in query)
{
if (string.IsNullOrEmpty(x))
{
throw new Exception(ExcelResources.QueryArrayInstanceEmpty);
}
}
ExcelHelper exlHelp = new ExcelHelper();
int rtn = exlHelp.ExecuteMultipleQuery(filepath, query, properties);
return(rtn);
}
public static DataSet Fill(string filepath, string query, object[] parameters, string srcTable, ExcelProperties properties)
{
if (string.IsNullOrEmpty(filepath))
{
throw new Exception(ExcelResources.DataSourceEmpty);
}
if (string.IsNullOrEmpty(query))
{
throw new Exception(ExcelResources.QueryEmpty);
}
if (string.IsNullOrEmpty(srcTable))
{
throw new Exception(ExcelResources.SourceTableEmpty);
}
ExcelHelper exlHelp = new ExcelHelper();
string[] param = CastParameters(parameters);
DataSet rtn = exlHelp.FillWithName(filepath, query, param, srcTable, properties);
return(rtn);
}
public static DataTable GetOleDbSchemaTable(string filepath, Guid schema, string[] parameters, ExcelProperties properties)
{
if (string.IsNullOrEmpty(filepath))
{
throw new Exception(ExcelResources.DataSourceEmpty);
}
if (schema == null || string.IsNullOrEmpty(schema.ToString()))
{
throw new Exception(ExcelResources.SchemaEmpty);
}
ExcelHelper exlHelp = new ExcelHelper();
DataTable rtn = exlHelp.GetOleDbSchemaTable(filepath, schema, parameters, properties);
return(rtn);
}
public static int ExecuteNonQuery(string filepath, string query, object[] parameters, ExcelProperties properties)
{
if (string.IsNullOrEmpty(filepath))
{
throw new Exception(ExcelResources.DataSourceEmpty);
}
if (string.IsNullOrEmpty(query))
{
throw new Exception(ExcelResources.QueryEmpty);
}
ExcelHelper exlHelp = new ExcelHelper();
string[] param = CastParameters(parameters);
int rtn = exlHelp.ExecuteNonQuery(filepath, query, param, properties);
return(rtn);
}
public DataTable GetOleDbSchemaTable(string filepath, Guid schema, string[] parameters, ExcelProperties properties)
{
filepath = getCannolizePath(filepath);
if (string.IsNullOrEmpty(filepath))
{
throw new Exception(ExcelResources.DataSourceEmpty);
}
if (schema == null || string.IsNullOrEmpty(schema.ToString()))
{
throw new Exception(ExcelResources.SchemaEmpty);
}
OpenXmlClient openxmlcon = new OpenXmlClient();
DataTable openxmltbl = openxmlcon.GetSchemaTable(filepath);
return(openxmltbl);
}
public DataSet FillWithName(string filepath, string query, string[] parameters, string srcTable, ExcelProperties properties)
{
filepath = getCannolizePath(filepath);
DataSet ds = Fill(filepath, query, parameters, properties);
ds.Tables[0].TableName = srcTable;
ds.AcceptChanges();
return(ds);
}