コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }