private void LoadDataFromExcelWind(string fileLoc, string sheetname, ref List <APData> list, string stationID, int year, int month)
{
String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileLoc + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
try
{
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
// Create connection object by using the preceding connection string.
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect = new OleDbCommand("Select * from [" + sheetname + "$]", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");
// Bind data to DataGrid control.
// dataGridView1.DataSource = objDataset1.Tables[0].DefaultView;
//List<ApplicationQualification> list = new List<ApplicationQualification>();
var s = objDataset1.Tables[0].AsEnumerable();
foreach (var a in s)
{
//ApplicationQualification ap = new ApplicationQualification();
if (!string.IsNullOrEmpty(a.ItemArray[0].ToString().Trim()))
{
int tryInt;
if (int.TryParse(a.ItemArray[0].ToString().Trim(), out tryInt))
{
for (int i = 1, j = 1; i <= 24; i++, j++)
{
APData l = new APData();
l.Year = year;
l.Month = month;
l.Day = int.Parse(a.ItemArray[0].ToString().Trim());
l.Hour = i;
l.DataVal2 = a.ItemArray[j].ToString().Trim();
l.stationID = stationID;
float val;
if (float.TryParse(a.ItemArray[(++j)].ToString().Trim(), out val))
{
l.DataVal = val;
}
else
{
l.DataVal = null;
}
list.Add(l);
}
int day = int.Parse(a.ItemArray[0].ToString().Trim());
APDataCombined cl = Dailylist.Where(x => x.Year == year && x.Month == month && x.Day == day && x.stationID == stationID).FirstOrDefault();
bool isNew = false;
if (cl == null)
{
cl = new APDataCombined();
cl.Year = year;
cl.Month = month;
cl.Day = day;
cl.stationID = stationID;
isNew = true;
}
float val49;
if (float.TryParse(a.ItemArray[49].ToString().Trim(), out val49))
{
cl.WindSpeedVal = val49;
}
else
{
cl.WindSpeedVal = null;
}
if (isNew)
{
Dailylist.Add(cl);
}
}
}
}
// Clean up objects.
objConn.Close();
}
catch (Exception ex)
{
File.AppendAllText("log.txt", Environment.NewLine + "Exception: " + DateTime.Now.ToString() + "#sheetname: " + sheetname + "#stationID: " + stationID + "#year: " + year + "#month" + month + "#Exception Text: " + ex.Message);
objConn.Close();
}
//WriteToExcelApplicantQualification(list);
}
private void LoadDataFromExcel(string fileLoc, string sheetname, ref List <APData> list, string stationID, int year, int month)
{
String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileLoc + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
try
{
// Open connection with the database.
objConn.Open();
// The code to follow uses a SQL SELECT command to display the data from the worksheet.
// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect = new OleDbCommand("Select * from [" + sheetname + "$]", objConn);
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;
// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();
// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");
// Bind data to DataGrid control.
// dataGridView1.DataSource = objDataset1.Tables[0].DefaultView;
//List<ApplicationQualification> list = new List<ApplicationQualification>();
var s = objDataset1.Tables[0].AsEnumerable();
foreach (var a in s)
{
//ApplicationQualification ap = new ApplicationQualification();
if (!string.IsNullOrEmpty(a.ItemArray[0].ToString().Trim()))
{
int tryInt;
if (int.TryParse(a.ItemArray[0].ToString().Trim(), out tryInt))
{
for (int i = 1; i <= 24; i++)
{
APData l = new APData();
l.Year = year;
l.Month = month;
l.Day = int.Parse(a.ItemArray[0].ToString().Trim());
l.Hour = i;
float val;
if (float.TryParse(a.ItemArray[i].ToString().Trim(), out val))
{
l.DataVal = val;
}
else
{
l.DataVal = null;
}
l.stationID = stationID;
list.Add(l);
}
int day = int.Parse(a.ItemArray[0].ToString().Trim());
APDataCombined cl = Dailylist.Where(x => x.Year == year && x.Month == month && x.Day == day && x.stationID == stationID).FirstOrDefault();
bool isNew = false;
if (cl == null)
{
cl = new APDataCombined();
cl.Year = year;
cl.Month = month;
cl.Day = day;
cl.stationID = stationID;
isNew = true;
}
float?nulableVal = null;
float val25;
if (float.TryParse(a.ItemArray[25].ToString().Trim(), out val25))
{
nulableVal = val25;
}
switch (sheetname)
{
case "CO Hourly": { cl.COVal = nulableVal; break; }
case "CH4 Hourly": { cl.CH4Val = nulableVal; break; }
case "NmHC Hourly": { cl.NmHCVal = nulableVal; break; }
case "THC Hourly": { cl.THCVal = nulableVal; break; }
case "O3 Hourly": { cl.O3Val = nulableVal; break; }
case "PM10 Hourly": { cl.PM10Val = nulableVal; break; }
case "SO2 Hourly": { cl.SO2Val = nulableVal; break; }
case "NOx Hourly": { cl.NOxVal = nulableVal; break; }
case "NO Hourly": { cl.NOVal = nulableVal; break; }
case "NO2 Hourly": { cl.NO2Val = nulableVal; break; }
case "TotalAPI Hourly": { cl.APIVal = nulableVal; break; }
case "AmbientTemp Hourly": { cl.AmbTempVal = nulableVal; break; }
case "Humidity Hourly": { cl.HumidityVal = nulableVal; break; }
}
if (isNew)
{
Dailylist.Add(cl);
}
}
}
}
// Clean up objects.
objConn.Close();
}
catch (Exception ex)
{
objConn.Close();
File.AppendAllText("log.txt", Environment.NewLine + "Exception: " + DateTime.Now.ToString() + "#sheetname: " + sheetname + "#stationID: " + stationID + "#year: " + year + "#month" + month + "#Exception Text: " + ex.Message);
}
//WriteToExcelApplicantQualification(list);
}