private static List <OrginalDataField> ReadHtmlExcel(string filePath, out int numColumn)
{
numColumn = 0;
string content = File.ReadAllText(filePath);
int firstIndex = content.IndexOf("<tr>");
int lastIndex = content.LastIndexOf("</tr>");
if (firstIndex == -1 || lastIndex == -1)
{
return(null);
}
string tableContent = content.Substring(firstIndex, lastIndex - firstIndex + 5);
string[] lines = tableContent.Split(new string[] { "<tr>" }, StringSplitOptions.RemoveEmptyEntries);
List <OrginalDataField> listData = new List <OrginalDataField>();
for (int i = 1; i < lines.Length; i++)
{
string line = lines[i];
firstIndex = line.IndexOf("<td");
lastIndex = line.LastIndexOf("</td>");
string lineContent = line.Substring(firstIndex, lastIndex - firstIndex + 5);
string[] cols = lineContent.Split(new string[] { "</td><td " }, StringSplitOptions.None);
numColumn = cols.Length;
if (numColumn >= numFieldOfImportFile)
{
for (int j = 0; j < cols.Length; j++)
{
string value = cols[j];
firstIndex = value.LastIndexOf("\">") + 2;
value = value.Substring(firstIndex);
cols[j] = value;
}
OrginalDataField rowData = new OrginalDataField();
rowData.LineNumber = i + 1;
rowData.ID = cols[0];
rowData.FirstName = cols[1];
rowData.LastName = cols[2];
rowData.OrganizationName = cols[3];
rowData.Website = cols[4];
rowData.PhoneNumber = cols[5];
rowData.Email = cols[6];
rowData.PollinatorType = cols[7];
rowData.OrganizationDescription = cols[8];
rowData.PollinatorSize = cols[9];
rowData.Address = cols[10];
rowData.City = cols[11];
rowData.State = cols[12];
rowData.ZIP = cols[13];
rowData.Country = cols[14];
rowData.Geolocation = cols[15].Replace("</td>", "");
listData.Add(rowData);
}
}
return(listData);
}
private static List <OrginalDataField> ReadDataFromExcelUsingClosedXML(string filePath, out int numColumn)
{
var objWorkbook = new XLWorkbook(filePath);
var objWorksheet = objWorkbook.Worksheets.First();
var objFullRange = objWorksheet.RangeUsed();
var objUsedRange = objWorksheet.Range(2, 1, objFullRange.RangeAddress.LastAddress.RowNumber,
objFullRange.RangeAddress.LastAddress.ColumnNumber);
numColumn = objUsedRange.ColumnCount();
List <OrginalDataField> listData = new List <OrginalDataField>();
if (numColumn != numFieldOfImportFile)
{
return(listData);
}
int lineNumber = 1;
foreach (var objRow in objUsedRange.RowsUsed())
{
OrginalDataField rowData = new OrginalDataField();
lineNumber++;
rowData.LineNumber = lineNumber;
rowData.ID = GetString(objRow.Cell(1).Value);
rowData.FirstName = GetString(objRow.Cell(2).Value);
rowData.LastName = GetString(objRow.Cell(3).Value);
rowData.OrganizationName = GetString(objRow.Cell(4).Value);
rowData.Website = GetString(objRow.Cell(5).Value);
rowData.PhoneNumber = GetString(objRow.Cell(6).Value);
rowData.Email = GetString(objRow.Cell(7).Value);
rowData.PollinatorType = GetString(objRow.Cell(8).Value);
rowData.OrganizationDescription = GetString(objRow.Cell(9).Value);
rowData.PollinatorSize = GetString(objRow.Cell(10).Value);
rowData.Address = GetString(objRow.Cell(11).Value);
rowData.City = GetString(objRow.Cell(12).Value);
rowData.State = GetString(objRow.Cell(13).Value);
rowData.ZIP = GetString(objRow.Cell(14).Value);
rowData.Country = GetString(objRow.Cell(15).Value);
rowData.Geolocation = GetString(objRow.Cell(16).Value);
listData.Add(rowData);
}
return(listData);
}
/// <summary>
/// Get Data From LineString
/// </summary>
/// <param name="lineContent"></param>
/// <param name="lineNumber"></param>
private void GetDataFromLineString(OrginalDataField orginalDataField, List<DataItem> listPType, List<DataItem> listPSize, List<DataItem> listCountry)
{
try
{
//parse line
//string[] valueOffields = ImportExportUltility.GetCsvRecord(lineContent);//input line and return value of fields of that line
//set value field
string errorDescriptionRow = string.Empty;
string errorDescriptionField = string.Empty;
bool fieldOK = true;
bool rowOK = true;
string itemID = string.Empty;
//create a new datarow
ImportExportFields dataRow = new ImportExportFields();
dataRow.LineNumber = orginalDataField.LineNumber;//0
dataRow.Premium = memberLevel;
dataRow.isApproved = 1;
//ID
if (!string.IsNullOrEmpty(orginalDataField.ID))
{
fieldOK = CheckValidateField(orginalDataField.ID, "ID", "Guid", false, 0, -1, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.UserId = new Guid(orginalDataField.ID);//1
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
}
//FirstName
fieldOK = CheckValidateField(orginalDataField.FirstName, "First Name", null, true, 0, 100, @"[^{}]*", null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.FirstName = orginalDataField.FirstName;//csv field 1
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//LastName
fieldOK = CheckValidateField(orginalDataField.LastName, "Last Name", null, false, 0, 60, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.LastName = orginalDataField.LastName;//csv field 2
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//csv field OrganizationName
fieldOK = CheckValidateField(orginalDataField.OrganizationName, "Organization Name", null, true, 0, 100, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.OrganizationName = orginalDataField.OrganizationName;//csv field 3
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//Website
fieldOK = CheckValidateField(orginalDataField.Website, "Website", null, false, 0, 255, @"(http(s)?://)?([\w-]+\.)+[\w-]+(/[\w- ./?%&=]*)?", null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.Website = orginalDataField.Website;//csv field 4
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//PhoneNumber
fieldOK = CheckValidateField(orginalDataField.PhoneNumber, "Phone Number", null, false, 0, 24, @"([()\d+-.\s])*", null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.PhoneNumber = orginalDataField.PhoneNumber;//csv field 5
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//Email
fieldOK = CheckValidateField(orginalDataField.Email, "Email", null, false, 0, 256, @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*", null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.Email = orginalDataField.Email;//csv field 6
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//PollinatorType
fieldOK = CheckValidateField(orginalDataField.PollinatorType, "Type of Pollinator", null, false, 0, 30, null, listPType, out itemID, out errorDescriptionField);
if (fieldOK)
{
dataRow.PollinatorTypeName = orginalDataField.PollinatorType;//csv field 7
if (!string.IsNullOrEmpty(itemID))
dataRow.PollinatorType = Int32.Parse(itemID);
else
dataRow.PollinatorType = 0;
}
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//OrganizationDescription
fieldOK = CheckValidateField(orginalDataField.OrganizationDescription, "Organization Description", null, false, 0, -1, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.OrganizationDescription = orginalDataField.OrganizationDescription;//csv field 8
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//PollinatorSize
fieldOK = CheckValidateField(orginalDataField.PollinatorSize, "Size of Pollinator Location", null, false, 0, 60, null, listPSize, out itemID, out errorDescriptionField);
if (fieldOK)
{
dataRow.PollinatorSizeName = orginalDataField.PollinatorSize;//csv field 9
if (!string.IsNullOrEmpty(itemID))
dataRow.PollinatorSize = Int32.Parse(itemID);
else
dataRow.PollinatorSize = 9;//Other
}
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//check Address(Street, City, State)
if (string.IsNullOrEmpty(orginalDataField.Address) && string.IsNullOrEmpty(orginalDataField.City) && string.IsNullOrEmpty(orginalDataField.State))
{
rowOK = false;
errorDescriptionField += string.Format("<br> - {0}", "Address (or City/State) is required");
errorDescriptionRow += errorDescriptionField;
}
else
{
//Address
fieldOK = CheckValidateField(orginalDataField.Address, "Address", null, false, 0, 100, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.LandscapeStreet = orginalDataField.Address;//csv field 10
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//City
fieldOK = CheckValidateField(orginalDataField.City, "City", null, false, 0, 50, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.LandscapeCity = orginalDataField.City;//csv field 11
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//State
fieldOK = CheckValidateField(orginalDataField.State, "State", null, false, 0, 30, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.LandscapeState = orginalDataField.State;//csv field 12
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
}
//ZIP
fieldOK = CheckValidateField(orginalDataField.ZIP, "ZIP", null, false, 0, 15, @"[A-Za-z0-9\s-]*", null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.LandscapeZipcode = orginalDataField.ZIP;//csv field 13
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//Country
fieldOK = CheckValidateField(orginalDataField.Country, "Country", null, false, 0, 100, null, listCountry, out itemID, out errorDescriptionField);
if (fieldOK)
{
dataRow.LandscapeCountryName = orginalDataField.Country;//csv field 14
dataRow.LandscapeCountry = itemID;
}
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//Geolocation(lat;lng)
if (!string.IsNullOrEmpty(orginalDataField.Geolocation) && orginalDataField.Geolocation.Contains(';'))
{
dataRow.Geolocation = orginalDataField.Geolocation;
string[] geolocation = orginalDataField.Geolocation.Split(new string[] { ";" }, StringSplitOptions.None);
if (geolocation.Length == 2)
{
string latitude = geolocation[0].Trim();
string longitude = geolocation[1].Trim();
//latitude
fieldOK = CheckValidateField(latitude, "Latitude of Geolocation", "decimal", true, 0, 18, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.Latitude = decimal.Parse(latitude);
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
//Longitude
fieldOK = CheckValidateField(latitude, "Longitude of Geolocation", "decimal", true, 0, 18, null, null, out itemID, out errorDescriptionField);
if (fieldOK)
dataRow.Longitude = decimal.Parse(longitude);
else
{
rowOK = false;
errorDescriptionRow += errorDescriptionField;
}
}
}
//row is OK-> add to list OK
if (rowOK)
listRowOK.Add(dataRow);//add to list row OK
else//row is failed -> add to list
{
OrginalDataField dataRowFailed = new OrginalDataField();
dataRowFailed.LineNumber = orginalDataField.LineNumber;//0
dataRowFailed.ErrorDescription = errorDescriptionRow.Substring(4);//error
dataRowFailed.ID = orginalDataField.ID;
dataRowFailed.FirstName = orginalDataField.FirstName;
dataRowFailed.LastName = orginalDataField.LastName;
dataRowFailed.OrganizationName = orginalDataField.OrganizationName;
dataRowFailed.Website = orginalDataField.Website;
dataRowFailed.PhoneNumber = orginalDataField.PhoneNumber;
dataRowFailed.Email = orginalDataField.Email;
dataRowFailed.PollinatorType = orginalDataField.PollinatorType;
dataRowFailed.OrganizationDescription = orginalDataField.OrganizationDescription;
dataRowFailed.PollinatorSize = orginalDataField.PollinatorSize;
dataRowFailed.Address = orginalDataField.Address;
dataRowFailed.City = orginalDataField.City;
dataRowFailed.State = orginalDataField.State;
dataRowFailed.ZIP = orginalDataField.ZIP;
dataRowFailed.Country = orginalDataField.Country;
dataRowFailed.Geolocation = orginalDataField.Geolocation;
//add to list failed rows
listRowFailed.Add(dataRowFailed);
}
}
catch (Exception ex)
{
lblErrorMess.Text = ex.Message;
Pollinator.Common.Logger.Error("Error occured at " + typeof(Admin_ImportData).Name + " GetDataFromLineString(). ", ex);
}
}
private static List<OrginalDataField> ReadHtmlExcel(string filePath, out int numColumn)
{
numColumn = 0;
string content = File.ReadAllText(filePath);
int firstIndex = content.IndexOf("<tr>");
int lastIndex = content.LastIndexOf("</tr>");
if (firstIndex == -1 || lastIndex == -1)
{
return null;
}
string tableContent = content.Substring(firstIndex, lastIndex - firstIndex + 5);
string[] lines = tableContent.Split(new string[] { "<tr>" }, StringSplitOptions.RemoveEmptyEntries);
List<OrginalDataField> listData = new List<OrginalDataField>();
for (int i = 1; i < lines.Length; i++)
{
string line = lines[i];
firstIndex = line.IndexOf("<td");
lastIndex = line.LastIndexOf("</td>");
string lineContent = line.Substring(firstIndex, lastIndex - firstIndex + 5);
string[] cols = lineContent.Split(new string[] { "</td><td " }, StringSplitOptions.None);
numColumn = cols.Length;
if (numColumn >= numFieldOfImportFile)
{
for (int j = 0; j < cols.Length; j++)
{
string value = cols[j];
firstIndex = value.LastIndexOf("\">") + 2;
value = value.Substring(firstIndex);
cols[j] = value;
}
OrginalDataField rowData = new OrginalDataField();
rowData.LineNumber = i + 1;
rowData.ID = cols[0];
rowData.FirstName = cols[1];
rowData.LastName = cols[2];
rowData.OrganizationName = cols[3];
rowData.Website = cols[4];
rowData.PhoneNumber = cols[5];
rowData.Email = cols[6];
rowData.PollinatorType = cols[7];
rowData.OrganizationDescription = cols[8];
rowData.PollinatorSize = cols[9];
rowData.Address = cols[10];
rowData.City = cols[11];
rowData.State = cols[12];
rowData.ZIP = cols[13];
rowData.Country = cols[14];
rowData.Geolocation = cols[15].Replace("</td>", "");
listData.Add(rowData);
}
}
return listData;
}
private static List<OrginalDataField> ReadDataFromExcelUsingClosedXML(string filePath, out int numColumn)
{
var objWorkbook = new XLWorkbook(filePath);
var objWorksheet = objWorkbook.Worksheets.First();
var objFullRange = objWorksheet.RangeUsed();
var objUsedRange = objWorksheet.Range(2, 1, objFullRange.RangeAddress.LastAddress.RowNumber,
objFullRange.RangeAddress.LastAddress.ColumnNumber);
numColumn = objUsedRange.ColumnCount();
List<OrginalDataField> listData = new List<OrginalDataField>();
if (numColumn != numFieldOfImportFile)
{
return listData;
}
int lineNumber = 1;
foreach (var objRow in objUsedRange.RowsUsed())
{
OrginalDataField rowData = new OrginalDataField();
lineNumber++;
rowData.LineNumber = lineNumber;
rowData.ID = GetString(objRow.Cell(1).Value);
rowData.FirstName = GetString(objRow.Cell(2).Value);
rowData.LastName = GetString(objRow.Cell(3).Value);
rowData.OrganizationName = GetString(objRow.Cell(4).Value);
rowData.Website = GetString(objRow.Cell(5).Value);
rowData.PhoneNumber = GetString(objRow.Cell(6).Value);
rowData.Email = GetString(objRow.Cell(7).Value);
rowData.PollinatorType = GetString(objRow.Cell(8).Value);
rowData.OrganizationDescription = GetString(objRow.Cell(9).Value);
rowData.PollinatorSize = GetString(objRow.Cell(10).Value);
rowData.Address = GetString(objRow.Cell(11).Value);
rowData.City = GetString(objRow.Cell(12).Value);
rowData.State = GetString(objRow.Cell(13).Value);
rowData.ZIP = GetString(objRow.Cell(14).Value);
rowData.Country = GetString(objRow.Cell(15).Value);
rowData.Geolocation = GetString(objRow.Cell(16).Value);
listData.Add(rowData);
}
return listData;
}
private static List<OrginalDataField> ReadBinaryExcel(string filePath, FileInfo file, out int numColumn)
{
OleDbConnection oledbConn;
if (file.Extension == ".xls")
{
oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
}
else //if (file.Extension == ".xlsx")
{
oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
}
try
{
oledbConn.Open();
}
catch (Exception ex)
{
numColumn = 0;
Pollinator.Common.Logger.Error("Error occured at " + typeof(ImportExportUltility).Name + " ReadBinaryExcel().:", ex);
return null;
}
DataTable dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string firstSheetName = "";
if (dt != null && dt.Rows.Count > 0)
{
firstSheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
dt = new DataTable();
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + firstSheetName + "]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(dt);
oledbConn.Close();
numColumn = dt.Columns.Count;
List<OrginalDataField> listData = new List<OrginalDataField>();
for (int i = 0; i < dt.Rows.Count; i++)
{
OrginalDataField rowData = new OrginalDataField();
rowData.LineNumber = i + 2;
rowData.ID = GetString(dt.Rows[i][0]);
rowData.FirstName = GetString(dt.Rows[i][1]);
rowData.LastName = GetString(dt.Rows[i][2]);
rowData.OrganizationName = GetString(dt.Rows[i][3]);
rowData.Website = GetString(dt.Rows[i][4]);
rowData.PhoneNumber = GetString(dt.Rows[i][5]);
rowData.Email = GetString(dt.Rows[i][6]);
rowData.PollinatorType = GetString(dt.Rows[i][7]);
rowData.OrganizationDescription = GetString(dt.Rows[i][8]);
rowData.PollinatorSize = GetString(dt.Rows[i][9]);
rowData.Address = GetString(dt.Rows[i][10]);
rowData.City = GetString(dt.Rows[i][11]);
rowData.State = GetString(dt.Rows[i][12]);
rowData.ZIP = GetString(dt.Rows[i][13]);
rowData.Country = GetString(dt.Rows[i][14]);
rowData.Geolocation = GetString(dt.Rows[i][15]);
listData.Add(rowData);
}
return listData;
}
public static List<OrginalDataField> ReadCsv(string filePath, out int numFieldOfCurrentFile)
{
numFieldOfCurrentFile = 0;
string content = File.ReadAllText(filePath);// Convert content to list string
Regex csvSplitLine = new Regex("\r\n(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
string[] lines = csvSplitLine.Split(content);
List<OrginalDataField> listData = new List<OrginalDataField>();
//store data to list
OrginalDataField orginalDataField;
int rowNo = 0;
foreach (var line in lines)
{
//not get line 1(header line)
if (rowNo == 0)
{
numFieldOfCurrentFile = ImportExportUltility.GetCsvRecord(line).Length;//get num of column
if (numFieldOfCurrentFile != numFieldOfImportFile)
break;
rowNo++;
continue;
}
//not add line is empty
if (string.IsNullOrEmpty(line))
{
rowNo++;
continue;
}
//add data records
orginalDataField = new OrginalDataField();
orginalDataField.LineNumber = rowNo + 1;
string[] valueOfFields = ImportExportUltility.GetCsvRecord(line.ToString());
//not add line is empty
if (string.IsNullOrEmpty(valueOfFields[0]) && string.IsNullOrEmpty(valueOfFields[1]) && string.IsNullOrEmpty(valueOfFields[2])
&& string.IsNullOrEmpty(valueOfFields[3]) && string.IsNullOrEmpty(valueOfFields[4]) && string.IsNullOrEmpty(valueOfFields[5])
&& string.IsNullOrEmpty(valueOfFields[6]) && string.IsNullOrEmpty(valueOfFields[7]) && string.IsNullOrEmpty(valueOfFields[8])
&& string.IsNullOrEmpty(valueOfFields[9]) && string.IsNullOrEmpty(valueOfFields[10]) && string.IsNullOrEmpty(valueOfFields[11])
&& string.IsNullOrEmpty(valueOfFields[12]) && string.IsNullOrEmpty(valueOfFields[13]) && string.IsNullOrEmpty(valueOfFields[14])
&& string.IsNullOrEmpty(valueOfFields[15]))
{
rowNo++;
continue;
}
orginalDataField.ID = valueOfFields[0].ToString().Trim();
orginalDataField.FirstName = valueOfFields[1].ToString().Trim();
orginalDataField.LastName = valueOfFields[2].ToString().Trim();
orginalDataField.OrganizationName = valueOfFields[3].ToString().Trim();
orginalDataField.Website = valueOfFields[4].ToString().Trim();
orginalDataField.PhoneNumber = valueOfFields[5].ToString().Trim();
orginalDataField.Email = valueOfFields[6].ToString().Trim();
orginalDataField.PollinatorType = valueOfFields[7].ToString().Trim();
orginalDataField.OrganizationDescription = valueOfFields[8].ToString().Trim();
orginalDataField.PollinatorSize = valueOfFields[9].ToString().Trim();
orginalDataField.Address = valueOfFields[10].ToString().Trim();
orginalDataField.City = valueOfFields[11].ToString().Trim();
orginalDataField.State = valueOfFields[12].ToString().Trim();
orginalDataField.ZIP = valueOfFields[13].ToString().Trim();
orginalDataField.Country = valueOfFields[14].ToString().Trim();
orginalDataField.Geolocation = valueOfFields[15].ToString().Trim();
//add to list
listData.Add(orginalDataField);
//asc 1 unit
rowNo++;
}
return listData;
}
private static List <OrginalDataField> ReadBinaryExcel(string filePath, FileInfo file, out int numColumn)
{
OleDbConnection oledbConn;
if (file.Extension == ".xls")
{
oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
}
else //if (file.Extension == ".xlsx")
{
oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
}
try
{
oledbConn.Open();
}
catch (Exception ex)
{
numColumn = 0;
Pollinator.Common.Logger.Error("Error occured at " + typeof(ImportExportUltility).Name + " ReadBinaryExcel().:", ex);
return(null);
}
DataTable dt = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string firstSheetName = "";
if (dt != null && dt.Rows.Count > 0)
{
firstSheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
dt = new DataTable();
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + firstSheetName + "]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(dt);
oledbConn.Close();
numColumn = dt.Columns.Count;
List <OrginalDataField> listData = new List <OrginalDataField>();
for (int i = 0; i < dt.Rows.Count; i++)
{
OrginalDataField rowData = new OrginalDataField();
rowData.LineNumber = i + 2;
rowData.ID = GetString(dt.Rows[i][0]);
rowData.FirstName = GetString(dt.Rows[i][1]);
rowData.LastName = GetString(dt.Rows[i][2]);
rowData.OrganizationName = GetString(dt.Rows[i][3]);
rowData.Website = GetString(dt.Rows[i][4]);
rowData.PhoneNumber = GetString(dt.Rows[i][5]);
rowData.Email = GetString(dt.Rows[i][6]);
rowData.PollinatorType = GetString(dt.Rows[i][7]);
rowData.OrganizationDescription = GetString(dt.Rows[i][8]);
rowData.PollinatorSize = GetString(dt.Rows[i][9]);
rowData.Address = GetString(dt.Rows[i][10]);
rowData.City = GetString(dt.Rows[i][11]);
rowData.State = GetString(dt.Rows[i][12]);
rowData.ZIP = GetString(dt.Rows[i][13]);
rowData.Country = GetString(dt.Rows[i][14]);
rowData.Geolocation = GetString(dt.Rows[i][15]);
listData.Add(rowData);
}
return(listData);
}
public static List <OrginalDataField> ReadCsv(string filePath, out int numFieldOfCurrentFile)
{
numFieldOfCurrentFile = 0;
string content = File.ReadAllText(filePath);// Convert content to list string
Regex csvSplitLine = new Regex("\r\n(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
string[] lines = csvSplitLine.Split(content);
List <OrginalDataField> listData = new List <OrginalDataField>();
//store data to list
OrginalDataField orginalDataField;
int rowNo = 0;
foreach (var line in lines)
{
//not get line 1(header line)
if (rowNo == 0)
{
numFieldOfCurrentFile = ImportExportUltility.GetCsvRecord(line).Length;//get num of column
if (numFieldOfCurrentFile != numFieldOfImportFile)
{
break;
}
rowNo++;
continue;
}
//not add line is empty
if (string.IsNullOrEmpty(line))
{
rowNo++;
continue;
}
//add data records
orginalDataField = new OrginalDataField();
orginalDataField.LineNumber = rowNo + 1;
string[] valueOfFields = ImportExportUltility.GetCsvRecord(line.ToString());
//not add line is empty
if (string.IsNullOrEmpty(valueOfFields[0]) && string.IsNullOrEmpty(valueOfFields[1]) && string.IsNullOrEmpty(valueOfFields[2]) &&
string.IsNullOrEmpty(valueOfFields[3]) && string.IsNullOrEmpty(valueOfFields[4]) && string.IsNullOrEmpty(valueOfFields[5]) &&
string.IsNullOrEmpty(valueOfFields[6]) && string.IsNullOrEmpty(valueOfFields[7]) && string.IsNullOrEmpty(valueOfFields[8]) &&
string.IsNullOrEmpty(valueOfFields[9]) && string.IsNullOrEmpty(valueOfFields[10]) && string.IsNullOrEmpty(valueOfFields[11]) &&
string.IsNullOrEmpty(valueOfFields[12]) && string.IsNullOrEmpty(valueOfFields[13]) && string.IsNullOrEmpty(valueOfFields[14]) &&
string.IsNullOrEmpty(valueOfFields[15]))
{
rowNo++;
continue;
}
orginalDataField.ID = valueOfFields[0].ToString().Trim();
orginalDataField.FirstName = valueOfFields[1].ToString().Trim();
orginalDataField.LastName = valueOfFields[2].ToString().Trim();
orginalDataField.OrganizationName = valueOfFields[3].ToString().Trim();
orginalDataField.Website = valueOfFields[4].ToString().Trim();
orginalDataField.PhoneNumber = valueOfFields[5].ToString().Trim();
orginalDataField.Email = valueOfFields[6].ToString().Trim();
orginalDataField.PollinatorType = valueOfFields[7].ToString().Trim();
orginalDataField.OrganizationDescription = valueOfFields[8].ToString().Trim();
orginalDataField.PollinatorSize = valueOfFields[9].ToString().Trim();
orginalDataField.Address = valueOfFields[10].ToString().Trim();
orginalDataField.City = valueOfFields[11].ToString().Trim();
orginalDataField.State = valueOfFields[12].ToString().Trim();
orginalDataField.ZIP = valueOfFields[13].ToString().Trim();
orginalDataField.Country = valueOfFields[14].ToString().Trim();
orginalDataField.Geolocation = valueOfFields[15].ToString().Trim();
//add to list
listData.Add(orginalDataField);
//asc 1 unit
rowNo++;
}
return(listData);
}
