public bool Export()
{
OleDbConnection dbCon = DatabaseConnection.GetConnection();
dbCon.Open();
DatabaseCore dc = new DatabaseCore(dbCon);
Dictionary<string, string> query = new Dictionary<string,string>();
query.Add("Year",_dt.Year.ToString());
query.Add("Month",_dt.Month.ToString());
query.Add("Day",_dt.Day.ToString());
System.Data.DataTable ivDt = dc.SelectData("dbo_IVTable", query);
System.Data.DataTable mDt = dc.SelectData("MeteorologicalData", query);
dbCon.Close();
if (ivDt.Rows.Count == 0 && mDt.Rows.Count == 0)
return false;
DataView dv = ivDt.DefaultView;
dv.Sort = "[Hour] ASC, [Minute] ASC, [Second] ASC";
ivDt = dv.ToTable();
dv = mDt.DefaultView;
mDt = dv.ToTable();
string path = System.Environment.CurrentDirectory + "\\Excel\\";
Application excel = new Application();
Workbooks wbks = excel.Workbooks;
Workbook wb = wbks.Add(path + "mb.xlsx");
int row = 2;
foreach(DataRow dr in mDt.Rows)
{
Worksheet wsh = wb.Sheets[3];
int col = 1;
wsh.Cells[row, col++] = dr["Hour"] + ":" + dr["Minute"] + ":" + dr["Second"];
wsh.Cells[row, col++] = dr["WindSpeed(m/s)"];
wsh.Cells[row, col++] = dr["AirTemperayure"];
wsh.Cells[row, col++] = dr["Rasiation(W/m2)"];
wsh.Cells[row, col++] = dr["WindDirection"];
wsh.Cells[row, col++] = dr["Humidity(%RH)"];
string str = "[Hour] = " + dr["Hour"] + " and " + "[Minute] = " + dr["Minute"];
DataRow[] result = ivDt.Select(str);
if (result.Length != 0)
wsh.Cells[row, col++] = result[0]["Component1Temperature"];
else
col++;
wsh.Cells[row, col++] = dr["Component2Temperature"];
wsh.Cells[row, col++] = dr["Component3Temperature"];
wsh.Cells[row, col++] = dr["Component4Temperature"];
wsh.Cells[row, col++] = dr["Component5Temperature"];
wsh.Cells[row, col++] = dr["Component6Temperature"];
row++;
}
int sheet1Row = 4;
int sheet2Row = 3;
foreach (DataRow dr in ivDt.Rows)
{
int sheet;
int col;
GetLocation((int)dr["ComponentId"], (int)dr["Azimuth"], (int)dr["Obliquity"], out sheet, out col);
Worksheet wsh = wb.Sheets[sheet];
if(sheet == 1)
{
wsh.Cells[sheet1Row, 1] = dr["Hour"] + ":" + dr["Minute"] + ":" + dr["Second"];
wsh.Cells[sheet1Row, col++] = dr["OpenCircuitVoltage"];
wsh.Cells[sheet1Row, col++] = dr["ShortCircuitCurrent"];
wsh.Cells[sheet1Row, col++] = dr["MaxPowerVoltage"];
wsh.Cells[sheet1Row, col++] = dr["MaxPowerCurrent"];
wsh.Cells[sheet1Row, col++] = dr["MaxPower"];
++sheet1Row;
}
if (sheet == 2)
{
wsh.Cells[sheet2Row, 1] = dr["Hour"] + ":" + dr["Minute"] + ":" + dr["Second"];
wsh.Cells[sheet2Row, col++] = dr["OpenCircuitVoltage"];
wsh.Cells[sheet2Row, col++] = dr["ShortCircuitCurrent"];
wsh.Cells[sheet2Row, col++] = dr["MaxPowerVoltage"];
wsh.Cells[sheet2Row, col++] = dr["MaxPowerCurrent"];
wsh.Cells[sheet2Row, col++] = dr["MaxPower"];
++sheet2Row;
}
}
wb.SaveAs(path + _dt.Year + "年" + _dt.Month + "月" + _dt.Day + "日.xlsx");
wb.Close();
wbks.Close();
return true;
}
/// <summary>
/// 获得指定的气象数据表数据
/// </summary>
/// <param name="latitude">纬度</param>
/// <param name="longitude">经度</param>
/// <param name="partName">类别名</param>
/// <param name="tableName">表名</param>
/// <returns>指定的气象数据表数据</returns>
public DataTable GetTableData(double latitude, double longitude, string partName, string tableName)
{
double nearestLatitude;
double nearestLongitude;
GetNearestCoordinate(latitude, longitude, out nearestLatitude, out nearestLongitude);
DatabaseCore dc = new DatabaseCore(_sqlCon);
string partID;
string tableID;
DataTable dataTable;
Dictionary<string, string> queryTerms = new Dictionary<string, string>();
queryTerms.Add("PartName", partName);
DataTable dt = dc.SelectData("dbo.Parts", queryTerms);
if (dt.Rows.Count != 1)
throw new Exception("the result of select is abnormal");
partID = dt.Rows[0]["PartID"].ToString();
queryTerms.Clear();
queryTerms.Add("PartID", partID);
queryTerms.Add("TableName", tableName);
dt = dc.SelectData("dbo.Tables", queryTerms);
if (dt.Rows.Count != 1)
throw new Exception("the result of select is abnormal");
tableID = dt.Rows[0]["TableID"].ToString();
queryTerms.Clear();
queryTerms.Add("TableID", tableID);
queryTerms.Add("Lat", nearestLatitude.ToString());
queryTerms.Add("Lon", nearestLongitude.ToString());
dt = dc.SelectData("dbo.Lines", queryTerms);
if (dt.Rows.Count <= 0)
throw new Exception("the result of select is abnormal");
dataTable = dt;
return dataTable;
}
/// <summary>
/// 获得属于类别partName的所有数据表名
/// </summary>
/// <param name="partName">类别名</param>
/// <returns>属于类别partName的所有数据表名</returns>
public List<string> GetTableNames(string partName)
{
DatabaseCore dc = new DatabaseCore(_sqlCon);
Dictionary<string,string> dict = new Dictionary<string,string>();
dict.Add("PartName",partName);
DataTable dt = dc.SelectData("dbo.Parts",dict);
string partID = dt.Rows[0]["PartID"].ToString();
dict.Clear();
dict.Add("PartID", partID);
dt = dc.SelectData("dbo.Tables", dict);
List<string> result = new List<string>();
foreach (DataRow dr in dt.Rows)
{
result.Add(dr["TableName"].ToString());
}
return result;
}
/// <summary>
/// 获得所有类别名
/// </summary>
/// <returns>所有类别名</returns>
public List<string> GetPartNames()
{
DatabaseCore dc = new DatabaseCore(_sqlCon);
DataTable dt = dc.SelectData("dbo.Parts", null);
List<string> result = new List<string>();
foreach (DataRow dr in dt.Rows)
{
result.Add(dr["PartName"].ToString());
}
return result;
}