public TaxiRunningSnapshot[] Read(String file) { Application excel = new Application();//lauch excel application TaxiRunningSnapshot[] result = null; if (excel == null) { Console.Write("Can't access excel!"); } else { excel.Visible = false; excel.UserControl = true; // 以只读的形式打开EXCEL文件 Workbook wb = excel.Application.Workbooks.Open(file, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //取得第一个工作薄 Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1); //取得总记录行数 (包括标题列) int rows = ws.UsedRange.Cells.Rows.Count; //得到行数 //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数 result = new TaxiRunningSnapshot[rows - 1]; //取得数据范围区域 (不包括标题列) Range taxiIdRange = ws.Cells.get_Range("A2", "A" + rows); Range timestampRange = ws.Cells.get_Range("B2", "B" + rows); Range lngsRange = ws.Cells.get_Range("C2", "C" + rows); Range latRange = ws.Cells.get_Range("D2", "D" + rows); Range speedRange = ws.Cells.get_Range("E2", "E" + rows); Range taxiStateRange = ws.Cells.get_Range("G2", "G" + rows); Console.Write("正在读取文件 " + file + ": "); int cursorLeft = Console.CursorLeft, cursorTop = Console.CursorTop; for (int i = 1; i <= rows - 1; i++) { if (i % 100 == 0) { Console.SetCursorPosition(cursorLeft, cursorTop); Console.Write("{0}%", (int)(i / ((double)rows) * 100)); } TaxiRunningSnapshot snapshot = new TaxiRunningSnapshot(); snapshot.TaxiId = (string)((object[, ])taxiIdRange.Value2)[i, 1]; snapshot.Timestamp = DateTime.FromOADate((double)((object[, ])timestampRange.Value2)[i, 1]); snapshot.Location = new Location((double)((object[, ])lngsRange.Value2)[i, 1], (double)((object[, ])latRange.Value2)[i, 1]); snapshot.Speed = (double)((object[, ])speedRange.Value2)[i, 1]; snapshot.TaxiState = (string)((object[, ])taxiStateRange.Value2)[i, 1] == "空车"?TaxiState.Empty:TaxiState.Loaded; result[i - 1] = snapshot; } } excel.Quit(); excel = null; GC.Collect(); return(result); }
public static List <List <TaxiRunningStatistics> > analyse(TaxiRunningSnapshot[] snapshots) { List <List <TaxiRunningStatistics> > result = new List <List <TaxiRunningStatistics> >(); int i = 0; while (i < snapshots.Length) { List <TaxiRunningStatistics> statisticInOneDay = new List <TaxiRunningStatistics>(); DateTime currentDay = snapshots[i].Timestamp.Date, nextDay = currentDay.AddDays(1); TaxiRunningStatistics lastHourLegacy = null; Boolean hasLegacy = false; while (i < snapshots.Length && between(snapshots[i].Timestamp, currentDay, nextDay)) { TaxiRunningStatistics statisticInOneHour = new TaxiRunningStatistics(); DateTime[] floorAndCeiling = GetFloorAndCeiling(snapshots[i].Timestamp); statisticInOneHour.TaxiId = snapshots[i].TaxiId; statisticInOneHour.StartTime = floorAndCeiling[0]; statisticInOneHour.EndTime = floorAndCeiling[1]; Console.WriteLine("开始统计:" + statisticInOneHour.StartTime + "到" + statisticInOneHour.EndTime + "的空驶率..."); if (hasLegacy) { inheritLegacy(statisticInOneHour, lastHourLegacy); hasLegacy = false; } TaxiRunningSnapshot lastSnapshot = null, currentSnapshot = null; while (i < snapshots.Length && between(snapshots[i].Timestamp, statisticInOneHour.StartTime, statisticInOneHour.EndTime)) { currentSnapshot = snapshots[i]; if (lastSnapshot != null) { double duration = currentSnapshot.Timestamp.Subtract(lastSnapshot.Timestamp).TotalSeconds; double distance = DistanceCalculator.Measure(lastSnapshot.Location, currentSnapshot.Location); if (lastSnapshot.TaxiState == TaxiState.Empty) { statisticInOneHour.EmptyRunningDuration += duration; statisticInOneHour.EmptyRunningDistance += distance; } else { statisticInOneHour.LoadedRunningDuration += duration; statisticInOneHour.LoadedRunningDistance += distance; } printMessage(lastSnapshot.TaxiState, duration, distance); } lastSnapshot = currentSnapshot; i++; } if (i < snapshots.Length) { //currentSnapshot为一个新小时的开始,对上一个小时的末尾进行预测 currentSnapshot = snapshots[i]; if (currentSnapshot.Timestamp.Subtract(lastSnapshot.Timestamp).TotalSeconds <= Config.GetPredictableRange()) { Console.WriteLine("正在预估行驶时间和行驶距离..."); lastHourLegacy = new TaxiRunningStatistics(); double extraInDuration = statisticInOneHour.EndTime.Subtract(lastSnapshot.Timestamp).TotalSeconds; double duration = currentSnapshot.Timestamp.Subtract(lastSnapshot.Timestamp).TotalSeconds; double legacyInDuration = duration - extraInDuration; double distance = DistanceCalculator.Measure(lastSnapshot.Location, currentSnapshot.Location); double legacyInDistance = distance * legacyInDuration / duration; double extraInDistance = distance * extraInDuration / duration; if (lastSnapshot.TaxiState == TaxiState.Empty) { statisticInOneHour.EmptyRunningDuration += extraInDuration; statisticInOneHour.EmptyRunningDistance += extraInDistance; lastHourLegacy.EmptyRunningDuration = legacyInDuration; lastHourLegacy.EmptyRunningDistance = legacyInDistance; } else { statisticInOneHour.LoadedRunningDuration += extraInDuration; statisticInOneHour.LoadedRunningDistance += extraInDistance; lastHourLegacy.LoadedRunningDuration = legacyInDuration; lastHourLegacy.LoadedRunningDistance = legacyInDistance; } printMessage(lastSnapshot.TaxiState, extraInDuration, extraInDistance); hasLegacy = true; } } Console.WriteLine(statisticInOneHour.StartTime + "到" + statisticInOneHour.EndTime + "的数据统计完毕:"); Console.WriteLine(statisticInOneHour); Console.WriteLine(); statisticInOneDay.Add(statisticInOneHour); } result.Add(statisticInOneDay); } return(result); }