public ExcelExtensionReponse Test(string fileName, string[] validationSet, string[] regexSet, string[] columnLengths)
{
ExcelExtensionReponse excelExtensionReponse = new ExcelExtensionReponse();
string JSONString = string.Empty;
IExcelDataReader excelReader;
try
{
FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
if (fileName.Contains("xlsx"))
{
excelReader = ExcelReaderFactory.CreateReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
excelReader.Close();
List <string> getValidationErrors;
bool getDesiredColumnExistence = ColExistence(result, validationSet, out getValidationErrors);
if (getDesiredColumnExistence)
{
for (int i = 0; i < result.Tables[0].Columns.Count; i++)
{
bool regexStatus = regexValdiation(result.Tables[0].DefaultView.ToTable(false, result.Tables[0].Columns[i].ColumnName), regexSet[i], Int32.Parse(columnLengths[i]));
if (!regexStatus)
{
throw new Exception("'" + result.Tables[0].Columns[i].ColumnName + "' column has identified invalid data");
}
}
foreach (string dataColumn in validationSet)
{
if (DataExtensions.HasNull(result.Tables[0].DefaultView.ToTable(true, dataColumn)))
{
throw new Exception("'" + dataColumn + "' column has identified invalid data");
}
}
var regexItem = new Regex("[^0-9a-zA-Z]+");
for (int i = 0; i < result.Tables[0].Columns.Count; i++)
{
if (regexItem.IsMatch(result.Tables[0].Columns[i].ColumnName.ToString()))
{
result.Tables[0].Columns[i].ColumnName = "S_" + Regex.Replace(result.Tables[0].Columns[i].ColumnName, @"[^0-9a-zA-Z]+", "");
}
}
result.AcceptChanges();
JSONString = JsonConvert.SerializeObject(result.Tables[0]);
excelExtensionReponse.ExcelExtensionReponseData = JSONString;
excelExtensionReponse.success = true;
}
else
{
excelExtensionReponse.exception = new ArgumentException("Required Columns " + JsonConvert.SerializeObject(getValidationErrors) + " are not found");
}
}
catch (Exception ex)
{
excelExtensionReponse.exception = ex;
}
return(excelExtensionReponse);
}
//public 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;
//}
//public 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;
//}
public ExcelExtensionReponse Test(string fileName)
{
ExcelExtensionReponse excelExtensionReponse = new ExcelExtensionReponse();
string JSONString = string.Empty;
IExcelDataReader excelReader;
try
{
FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
if (fileName.Contains("xlsx"))
{
excelReader = ExcelReaderFactory.CreateReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
excelReader.Close();
bool getDesiredColumnExistence = result.Tables[0].Columns.Contains("package no");
if (getDesiredColumnExistence)
{
var regexItem = new Regex("[^0-9a-zA-Z]+");
for (int i = 0; i < result.Tables[0].Columns.Count; i++)
{
if (regexItem.IsMatch(result.Tables[0].Columns[i].ColumnName.ToString()))
{
result.Tables[0].Columns[i].ColumnName = "S_" + Regex.Replace(result.Tables[0].Columns[i].ColumnName, @"[^0-9a-zA-Z]+", "");
}
}
result.AcceptChanges();
JSONString = JsonConvert.SerializeObject(result.Tables[0]);
excelExtensionReponse.ExcelExtensionReponseData = JSONString;
excelExtensionReponse.success = true;
}
else
{
excelExtensionReponse.exception = new ArgumentException("Required Column 'package no' is not found");
}
}
catch (Exception ex)
{
excelExtensionReponse.exception = ex;
}
return(excelExtensionReponse);
}