public void SaveExcel(Project _Project)
{
Console.WriteLine(" 保存数据至Excel中......");
string strPath = @"Xls\";
if (!Directory.Exists(strPath))
{
Directory.CreateDirectory(strPath);
}
strPath = strPath + _Project.ProjectPresell + "_" + _Project.ProjectID + "_" + _Project.ProjectName + ".xls"; //保存的路径和文件名
Stopwatch watch = new Stopwatch();
watch.Start();
ExcelWorkbook wbook = new ExcelWorkbook();
wbook.SetDefaultFont("Arial", 10);
ExcelWorksheet wsheet;
ExcelCellStyle style;
wbook.CreateSheet("DB_ESTATE");
wsheet = wbook.GetSheet("DB_ESTATE");
style = wbook.CreateStyle();
style.Pattern = EnumFill.Solid;
style.PatternForeColour = EnumColours.Grey25;
style.Font.Size = 11;
style.Font.Bold = true;
string[] arrESTATE_Title = { "物业ID", "城市", "行政区", "行政区英文名称", "片区", "片区英文名称", "物业名称", "物业名称拼音索引", "物业名称英文名称",
"物业别名", "物业地址", "物业类型及规模", "环线位置", "周围环境", "楼盘概况", "占地面积", "总建筑面积", "土地使用分区",
"容积率", "绿化率", "项目规划", "建筑类型", "建筑设计", "园林设计", "车位数量", "目标客户群", "物业管理方式",
"核心卖点", "项目自身设施", "项目优劣势", "周边配套"};
for (int o = 0; o < arrESTATE_Title.Length; o++)
{
wsheet.AddCell((ushort)(o + 1), 1, arrESTATE_Title[o], style);
}
DB_ESTATE dbEstate = _Project.Estate;
wsheet.AddCell(1, 2, dbEstate.WYID);
wsheet.AddCell(2, 2, dbEstate.CS);
wsheet.AddCell(3, 2, dbEstate.XZQ);
wsheet.AddCell(5, 2, dbEstate.XZQ);
wsheet.AddCell(7, 2, dbEstate.WYMC);
wsheet.AddCell(11, 2, dbEstate.WYDZ);
wsheet.AddCell(16, 2, dbEstate.ZDMJ);
wsheet.AddCell(17, 2, dbEstate.ZJZMJ);
wbook.CreateSheet("DB_BLOCK");
wsheet = wbook.GetSheet("DB_BLOCK");
style = wbook.CreateStyle();
style.Pattern = EnumFill.Solid;
style.PatternForeColour = EnumColours.Grey25;
style.Font.Size = 11;
style.Font.Bold = true;
string[] arrBLOCK_Title = { "物业ID", "大楼ID", "期数", "期数英文名称", "楼栋中文名称", "楼栋英文名称",
"楼栋别名1", "楼栋别名2", "座落位置", "楼栋结构", "竣工日期", "总单元数目", "建筑总面积", "用途(L1)", "用途(L2)",
"车位类型", "户外车位", "有盖车位", "电梯数量", "扶梯数量", "售楼书号", "物业管理费", "物业管理费币值", "管理公司", "发展商", "单栋大楼名称地址",
"楼盘简述", "路/街/里/弄", "路/街/里/弄（英文）", "街号由", "街号由尾码", "街号至", "街号至尾码", "县", "镇","村", "房屋所有权证号",
"土地使用证证号", "地块编号", "土地使用权起始日", "土地使用权终止日", "土地使用权条款", "地上层数", "地下层数", "区域类别", "周围环境", "发展趋势", "楼栋概况", "楼梯及设施",
"宗地号(报告相关)", "使用条款(报告相关)", "竣工日期(报告相关)", "市场可售性备注"};
for (int o = 0; o < arrBLOCK_Title.Length; o++)
{
wsheet.AddCell((ushort)(o + 1), 1, arrBLOCK_Title[o], style);
}
ushort iRow = 1;
foreach(DB_BLOCK block in _Project.BlockList.ToArray()){
iRow++;
wsheet.AddCell(1, iRow, block.WYID);
wsheet.AddCell(2, iRow, block.DLID);
wsheet.AddCell(5, iRow, block.LDZWMC);
wsheet.AddCell(11, iRow, block.JGRQ);
wsheet.AddCell(25, iRow, block.FZS);
}
wbook.CreateSheet("DB_FLAT");
wsheet = wbook.GetSheet("DB_FLAT");
style = wbook.CreateStyle();
style.Pattern = EnumFill.Solid;
style.PatternForeColour = EnumColours.Grey25;
style.Font.Size = 10;
style.Font.Bold = true;
string[] strDBFLAT_Title = { "大楼ID", "层数列(数字)", "层数名", "单元", "单元名称", "建筑面积(平方米)", "套内面积(平方米)", "天井(平方米)",
"露台(平方米)", "平台(平方米)", "天台(平方米)", "花园(平方米)", "阳台(平方米)", "窗台(平方米)",
"阁楼(平方米)", "储物室(平方米)", "空调室(平方米)", "用途(L1)", "用途(L2)", "房", "厅", "座向",
"单元结构", "备注", "法定用途", "合并情况"};
for (int o = 0; o < strDBFLAT_Title.Length; o++)
{
wsheet.AddCell((ushort)(o + 1), 1, strDBFLAT_Title[o], style);
}
iRow = 1;
foreach (DB_FLAT flat in _Project.FlatList.ToArray())
{
iRow++;
wsheet.AddCell(1, iRow, flat.DLID);
wsheet.AddCell(2, iRow, flat.CSL);
wsheet.AddCell(3, iRow, flat.CSM);
wsheet.AddCell(4, iRow, flat.CSL + flat.DY);
wsheet.AddCell(5, iRow, flat.DY);
wsheet.AddCell(6, iRow, flat.JZMJ);
wsheet.AddCell(7, iRow, flat.TNMJ);
wsheet.AddCell(18, iRow, flat.YTL1);
wsheet.AddCell(20, iRow, flat.F);
wsheet.AddCell(21, iRow, flat.T);
}
wbook.Save(strPath);
watch.Stop();
}
static void SaveFileXls(string strYear, string strMonth, string strDay)
{
Console.WriteLine("");
Console.WriteLine("开始查询" + strYear + "年" + strMonth + "月" + strDay + "日的出售数据");
List<EstateSell> listEstateSell = new List<EstateSell>();
Stopwatch watch = new Stopwatch();
watch.Start();
ExcelWorkbook wbook;
ExcelWorksheet wsheet;
ExcelCellStyle style;
//style.BottomLineStyle = EnumLineStyle.Thick;
//style.TopLineStyle = EnumLineStyle.Medium;
DateTime dt = System.DateTime.Now;
wbook = new ExcelWorkbook();
wbook.SetDefaultFont("Arial", 10);
wbook.CreateSheet("今日出售数据");
wsheet = wbook.GetSheet("今日出售数据");
wbook.SetActiveSheet = "今日出售数据";
style = wbook.CreateStyle();
style.Pattern = EnumFill.Solid;
style.PatternForeColour = EnumColours.Grey25;
style.Font.Size = 11;
style.Font.Bold = true;
string[] strDBBP_Title = { "标题", "区域", "楼盘", "户型", "面积(㎡)", "成交价(万)", "单价(元/㎡)", "时间" };
for (int o = 0; o < strDBBP_Title.Length; o++)
{
wsheet.AddCell((ushort)(o + 1), 1, strDBBP_Title[o], style);
}
int intRow = 1;
int intMaxPage = 1000;
int intPage = 0;
int intOffset = 0;
int intError = 0;
bool blState = true;
do
{
intPage++;
intOffset = (intPage - 1) * 24;
//intOffset = 1080;
try
{
//if (intPage == 1)
//{
// string strMaxPage = GetNoHtml(GetstrCenter(strHtml, "共<span class=\"zi_333333_12\">", "</span>"));
// intMaxPage = int.Parse(Math.Ceiling(int.Parse(strMaxPage) / 24.0).ToString());
//}
//Console.WriteLine("intMaxPage : " + intMaxPage);
Console.Write(" 加载第 " + intPage + " 页数据");
string s = GetXmlHttp(string.Format(strUrl, intOffset.ToString()));
List<EstateSell> listTemp = GetarrEstateSell(s);
DateTime inputSJ = DateTime.Parse(strYear + "-" + strMonth + "-" + strDay);
for (int o = 0; o < listTemp.Count; o++)
{
if (listTemp[o] != null)
{
DateTime searchSJ = DateTime.Parse(listTemp[o].SJ);
if (inputSJ == searchSJ)
{
listEstateSell.Add(listTemp[o]);
}
else if (searchSJ < inputSJ)
{
blState = false;
break;
}
}
else
break;
}
Console.WriteLine("成功。");
intError = 0;
}
catch (Exception ex)
{
Console.WriteLine("失败。错误提示：" + ex.Message);
intError++;
if (intError < 3)
{
Console.WriteLine(" 等待10秒后重新加载...");
Thread.Sleep(10000);
intPage--;
}
else
{
Console.WriteLine(" 连续加载3次失败，跳过当前页。");
}
}
}
while (intPage < intMaxPage & blState);
foreach (EstateSell es in listEstateSell)
{
intRow++;
ushort intX = (ushort)intRow;
wsheet.AddCell(1, intX, es.BT);
wsheet.AddCell(2, intX, es.QY);
wsheet.AddCell(3, intX, es.LP);
wsheet.AddCell(4, intX, es.HX);
wsheet.AddCell(5, intX, es.MJ);
wsheet.AddCell(6, intX, es.CJJ);
wsheet.AddCell(7, intX, es.DJ);
wsheet.AddCell(8, intX, es.SJ);
}
string strPath = @"Xls\";
if (!Directory.Exists(strPath))
{
Directory.CreateDirectory(strPath);
}
strPath = strPath + "出售数据" + strYear + "-" + strMonth + "-" + strDay + ".xls"; //保存的路径和文件名
Console.WriteLine("");
Console.WriteLine("" + strYear + "年" + strMonth + "月" + strDay + "日的数据已经产生。");
wbook.Save(strPath);
watch.Stop();
}
static void SaveFileXls(string strYear, string strMonth)
{
Console.WriteLine("");
Console.WriteLine("开始查询" + strYear + "年" + strMonth + "月数据");
string strHtml = "";
Estate[] arrEstate;
Stopwatch watch = new Stopwatch();
watch.Start();
ExcelWorkbook wbook;
ExcelWorksheet wsheet;
ExcelCellStyle style;
//style.BottomLineStyle = EnumLineStyle.Thick;
//style.TopLineStyle = EnumLineStyle.Medium;
DateTime dt = System.DateTime.Now;
wbook = new ExcelWorkbook();
wbook.SetDefaultFont("Arial", 10);
wbook.CreateSheet("详细成交数据");
wsheet = wbook.GetSheet("详细成交数据");
wbook.SetActiveSheet = "详细成交数据";
style = wbook.CreateStyle();
style.Pattern = EnumFill.Solid;
style.PatternForeColour = EnumColours.Grey25;
style.Font.Size = 11;
style.Font.Bold = true;
string[] strDBBP_Title = { "区域", "物业地址", "栋苑", "面积(㎡)", "成交价(万)", "单价(元/㎡)", "成交日期" };
for (int o = 0; o < strDBBP_Title.Length; o++)
{
wsheet.AddCell((ushort)(o + 1), 1, strDBBP_Title[o], style);
}
int intRow = 1;
int intMaxPage = 5;
int intPage = 0;
int intOffset = 0;
do
{
intPage++;
intOffset = (intPage - 1) * 20;
strHtml = GetXmlHttp(string.Format(strUrl, strYear, strMonth, intOffset.ToString()));
arrEstate = GetEstate(strHtml);
for (int o = 0; o < arrEstate.Length; o++)
{
if (arrEstate[o] != null)
{
intRow++;
ushort intX = (ushort)intRow;
wsheet.AddCell(1, intX, arrEstate[o].QY);
wsheet.AddCell(2, intX, arrEstate[o].WYDZ);
wsheet.AddCell(3, intX, arrEstate[o].DY);
wsheet.AddCell(4, intX, arrEstate[o].MJ);
wsheet.AddCell(5, intX, arrEstate[o].CJJ);
wsheet.AddCell(6, intX, arrEstate[o].DJ);
wsheet.AddCell(7, intX, arrEstate[o].CJRQ);
}
else
break;
}
if (intPage == 1)
{
string strMaxPage = GetNoHtml(GetstrCenter(strHtml, "pageJump_Start", "pageJump_End"));
strMaxPage = GetstrCenter(strMaxPage, "个主题 第", "页");
intMaxPage = int.Parse(strMaxPage.Split('/')[1]);
}
}
while (intPage < intMaxPage);
string strPath = @"Xls\";
if (!Directory.Exists(strPath))
{
Directory.CreateDirectory(strPath);
}
strPath = strPath + "成交数据" + strYear + "_" + strMonth + ".xls"; //保存的路径和文件名
Console.WriteLine("" + strYear + "年" + strMonth + "月数据已经产生。");
wbook.Save(strPath);
watch.Stop();
}