private void DataBaseToExcel()
{
//List<Status> statuses = db.StatusDb.ToList();
//List<User> users = db.UserDb.ToList();
//List<Hazards> hazards = db.HarzardsDb.ToList();
List<StatusL> statusLs = db.StatusLDb.ToList();
User user = new User();
Status status = new Status();
Hazards hazard = new Hazards();
StatusL statusL = new StatusL();
PropertyInfo[] userInfo = user.GetType().GetProperties();
PropertyInfo[] statusInfo = status.GetType().GetProperties();
PropertyInfo[] hazardInfo = hazard.GetType().GetProperties();
PropertyInfo[] statusLInfo = statusL.GetType().GetProperties();
object oOpt = System.Reflection.Missing.Value; //for optional arguments
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oWBs = oXL.Workbooks;
Microsoft.Office.Interop.Excel.Workbook oWB = oWBs.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[1];
//outputRows is a List<List<object>>
int numberOfRows = statusLs.Count(); //users.Count;
int numberOfColumns = statusLInfo.Count();//userInfo.Count();
Microsoft.Office.Interop.Excel.Range oRng;
for (int i = 0; i < numberOfColumns; i++)
{
oSheet.Cells[1, i + 1] = statusLInfo[i].Name;
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, i + 1];
oRng.Interior.ColorIndex = 15;
}
int row = 0;
foreach (var newstatus in statusLs)
{
if (row <= 12000)
{
row++;
continue;
}
for (int col = 0; col < numberOfColumns; col++)
{
try
{
if (!statusLInfo[col].Name.Equals("User"))
{
oSheet.Cells[row + 2, col + 1] = statusLInfo[col].GetValue(newstatus, null);
}
}
catch
{
oSheet.Cells[row + 2, col + 1] = "null";
}
}
row++;
}
oXL.Visible = true;
}
private void LoadPoiTimeLineReadJson(dynamic json, float lat, float lon)
{
try
{
if (json.IsDefined("statuses"))
{
foreach (var status in json.statuses)
{
var isInStatusDb = db.StatusDb.Find(status.id);
if (isInStatusDb == null)
{
Status statusEntity = new Status();
statusEntity.CreatedAt = status.created_at;
statusEntity.ID = status.id;
statusEntity.Text = status.text;
if (statusEntity.Text.IndexOf("此微博已被作者删除") <= -1 && statusEntity.Text.IndexOf("此微博已被删除") <= -1)
{
User userEntity = new User();
userEntity.ID = status.user.id;
userEntity.IDStr = status.user.idstr;
userEntity.Name = status.user.name;
userEntity.Province = status.user.province;
userEntity.City = status.user.city;
userEntity.Location = status.user.location;
userEntity.ProfileImageUrl = status.user.profile_image_url;
userEntity.Gender = status.user.gender;
userEntity.CreatedAt = status.user.created_at;
var isInUserDb = db.UserDb.Find(status.user.id);
if (isInUserDb == null)
{
db.UserDb.Add(userEntity);
db.SaveChanges();
}
statusEntity.Source = status.source;
if (status.IsDefined("thumbnail_pic"))
{
statusEntity.ThumbnailPictureUrl = status.thumbnail_pic;
statusEntity.MiddleSizePictureUrl = status.bmiddle_pic;
statusEntity.OriginalPictureUrl = status.original_pic;
}
if (status.geo != null)
{
statusEntity.Long = (float)status.geo.coordinates[1];
statusEntity.Lat = (float)status.geo.coordinates[0];
}
else
{
statusEntity.Long = lon;
statusEntity.Lat = lat;
}
statusEntity.RepostsCount = int.Parse(status.reposts_count);
statusEntity.CommentsCount = int.Parse(status.comments_count);
statusEntity.AttitudeCount = int.Parse(status.attitudes_count);
statusEntity.UserID = userEntity.ID;
db.StatusDb.Add(statusEntity);
db.SaveChanges();
}
}
}
}
}
finally
{
}
}
/// <summary>
/// 根据id调用Status/Show
/// </summary>
/// <param name="statusid"></param>
private void LoadStatusShow(string statusid)
{
if (Sina == null)
{
Sina = new Client(OAUTH);
}
dynamic json = Sina.API.Dynamic.Statuses.Show(statusid);
try
{
if (json != null)
{
var isInHarzardsDb = db.HarzardsDb.Find(json.id);
if (isInHarzardsDb == null)
{
Hazards hazardsEntity = new Hazards();
hazardsEntity.CreatedAt = json.created_at;
hazardsEntity.ID = json.id;
hazardsEntity.Text = json.text;
if (hazardsEntity.Text.IndexOf("此微博已被作者删除") <= -1 && hazardsEntity.Text.IndexOf("此微博已被删除") <= -1)
{
User userEntity = new User();
userEntity.ID = json.user.id;
userEntity.IDStr = json.user.idstr;
userEntity.Name = json.user.name;
userEntity.Province = json.user.province;
userEntity.City = json.user.city;
userEntity.Location = json.user.location;
userEntity.ProfileImageUrl = json.user.profile_image_url;
userEntity.Gender = json.user.gender;
userEntity.CreatedAt = json.user.created_at;
var isInUserDb = db.UserDb.Find(json.user.id);
if (isInUserDb == null)
{
db.UserDb.Add(userEntity);
db.SaveChanges();
}
hazardsEntity.Source = json.source;
if (json.IsDefined("thumbnail_pic"))
{
hazardsEntity.ThumbnailPictureUrl = json.thumbnail_pic;
hazardsEntity.MiddleSizePictureUrl = json.bmiddle_pic;
hazardsEntity.OriginalPictureUrl = json.original_pic;
}
if (json.geo != null)
{
hazardsEntity.Long = (float)json.geo.coordinates[1];
hazardsEntity.Lat = (float)json.geo.coordinates[0];
}
//else
//{
// statusEntity.Long = 0f;
// statusEntity.Lat = 0f;
//}
hazardsEntity.RepostsCount = int.Parse(json.reposts_count);
hazardsEntity.CommentsCount = int.Parse(json.comments_count);
hazardsEntity.AttitudeCount = int.Parse(json.attitudes_count);
hazardsEntity.UserID = userEntity.ID;
db.HarzardsDb.Add(hazardsEntity);
db.SaveChanges();
}
}
}
}
finally
{
}
}
private void LoadNearbyUserReadJson(dynamic json)
{
try
{
if (json.IsDefined("users"))
{
foreach (var user in json.users)
{
var isInUserDb = db.UserDb.Find(user.id);
if (isInUserDb == null)
{
User userEntity = new User();
userEntity.ID = user.id;
userEntity.IDStr = user.idstr;
userEntity.Name = user.name;
userEntity.Province = user.province;
userEntity.City = user.city;
userEntity.Location = user.location;
userEntity.ProfileImageUrl = user.profile_image_url;
userEntity.Gender = user.gender;
userEntity.CreatedAt = user.created_at;
db.UserDb.Add(userEntity);
db.SaveChanges();
if (user.IsDefined("status"))
{
var isInStatusDb = db.StatusDb.Find(user.status.id);
if (isInStatusDb == null)
{
Status statusEntity = new Status();
statusEntity.CreatedAt = user.status.created_at;
statusEntity.ID = user.status.id;
statusEntity.Text = user.status.text;
if (statusEntity.Text.IndexOf("此微博已被作者删除") <= -1)
{
statusEntity.Source = user.status.source;
if (user.status.IsDefined("thumbnail_pic"))
{
statusEntity.ThumbnailPictureUrl = user.status.thumbnail_pic;
statusEntity.MiddleSizePictureUrl = user.status.bmiddle_pic;
statusEntity.OriginalPictureUrl = user.status.original_pic;
}
statusEntity.Long = float.Parse(user.lon);//
statusEntity.Lat = float.Parse(user.lat);
statusEntity.RepostsCount = int.Parse(user.status.reposts_count);
statusEntity.CommentsCount = int.Parse(user.status.comments_count);
statusEntity.AttitudeCount = int.Parse(user.status.attitudes_count);
statusEntity.User = userEntity;
db.StatusDb.Add(statusEntity);
db.SaveChanges();
}
}
}
}
}
}
}
finally
{
}
}