static void loadExcel(string file, int startrow, int endrow)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = true;
excel.Visible = false;
Excel.Workbook theWorkbook;
Excel.Worksheet worksheet;
theWorkbook = excel.Workbooks.Open(file,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
worksheet = theWorkbook.Worksheets[1];
Excel.Range range;
int succeeded = 0;
int failed = 0;
for (int row = startrow; row <= endrow; row++)
{
GeoPoint locPoint = new GeoPoint();
string cellName;
//check if there is already a house has the same code name if so skip it
cellName = "A" + row.ToString();
range = worksheet.get_Range(cellName, Missing.Value);
string codeName = range.Value2.ToString();
var tId = Guid.Empty;
if (houseService.HouseExistByCodeName(codeName))
{
tId = houseService.FindHouseByCodeName(codeName).Id;
Console.WriteLine("house exists");
}
//check if it has a name if not skip if
cellName = "B" + row.ToString();
range = worksheet.get_Range(cellName, Missing.Value);
string houseName = range.Value2.ToString();
if (string.IsNullOrEmpty(houseName))
{
Console.WriteLine("no house name, skip");
failed++;
continue;
}
//chekck if the location is validte if not skip
cellName = "C" + row.ToString();
string location = worksheet.get_Range(cellName, Missing.Value).Value2.ToString();
try
{
locPoint = HouseService.GetGeoPoint(location);
}
catch
{
failed++;
Console.WriteLine("invalid location, skip");
continue;
}
//all check noe build a house
//user the code name to locate the photo folder
var path = file.Substring(0, file.LastIndexOf(@"\") + 1) + @"photo\" + codeName;
var photos = new List <string>();
try
{
photos = getPhotos(path, codeName).ToList <string>();
}
catch
{
Console.WriteLine("no photo");
}
DateTime year = DateTime.Parse("0001 06 06");
try
{
year = DateTime.Parse(worksheet.get_Range("I" + row.ToString(), Missing.Value).Value2.ToString() + " 06 01");
}
catch
{
//no build year
}
House newHouse = new House
{
CodeName = codeName,
Name = houseName,
Images = photos.Any() ? photos : null,
Cover = photos.Any() ? photos[0] : null,
Location = locPoint,
LocationString = worksheet.get_Range("D" + row.ToString(), Missing.Value).Value2.ToString(),
Abstarct = worksheet.get_Range("E" + row.ToString(), Missing.Value).Value2.ToString(),
Country = worksheet.get_Range("F" + row.ToString(), Missing.Value).Value2.ToString(),
Province = worksheet.get_Range("G" + row.ToString(), Missing.Value).Value2.ToString(),
City = worksheet.get_Range("H" + row.ToString(), Missing.Value).Value2.ToString(),
//BuiltYear = year,
//Condition = worksheet.get_Range("K" + row.ToString(), Missing.Value).Value2.ToString(),
//historica , photo value is not fixed yet
Description = worksheet.get_Range("P" + row.ToString(), Missing.Value).Value2.ToString(),
};
newHouse.AddExtraInformation("houseinfo-condition", worksheet.get_Range("K" + row.ToString(), Missing.Value).Value2.ToString());
newHouse.AddExtraInformation("houseinfo-buildyear", year.Year);
string tagString;
try
{
tagString = worksheet.get_Range("L" + row.ToString(), Missing.Value).Value2.ToString();
newHouse.Tags = tagString.Split(';').ToList <string>();
}
catch (Exception)
{
newHouse.Tags = null;
}
var extra = worksheet.get_Range("Q" + row.ToString(), Missing.Value).Value2;
if (extra != null)
{
string extraStr = extra.ToString();
getExtra(extraStr, newHouse);
}
if (tId == Guid.Empty)
{
houseService.InsertOne(newHouse);
succeeded++;
Console.WriteLine("house added: " + codeName);
}
else
{
newHouse.Id = tId;
houseService.SaveOne(newHouse);
succeeded++;
Console.WriteLine("house updated: " + codeName);
}
}
//clean up
theWorkbook.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(theWorkbook);
excel.Quit();
Marshal.FinalReleaseComObject(excel);
Console.WriteLine("succeeded:" + succeeded);
Console.WriteLine("failed:" + failed);
}
