Exemplo n.º 1
0
        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();
        }
Exemplo n.º 2
0
        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();
        }
Exemplo n.º 3
0
        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();
        }