Exemplo n.º 1
0
        public void WriteExcel()
        {
            using (ExcelHelper excel = new ExcelHelper("abcds.xls"))
            {
                excel.Hdr = "YES";
                excel.Imex = "0";
                Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
                tableDefinition.Add("Name", "ntext");
                tableDefinition.Add("Age", "ntext");
                tableDefinition.Add("Class", "ntext");

                excel.WriteTable("Student", tableDefinition);

                StringBuilder sb = new StringBuilder();
            //                //for (int i = 0;i <100; i++)
            //                //{
                    sb.Append(" insert into [Student] (Name,Age,Class) values (");
            ////                }
            //
                    sb.Append("'"); sb.Append("a"); sb.Append("',");
                    sb.Append("'"); sb.Append("b"); sb.Append("',");
                    sb.Append("'"); sb.Append("c"); sb.Append("'");
                    sb.Append(")");
                    Console.WriteLine(sb.ToString());
                    excel.ExecuteCommand(sb.ToString());

            //                excel.ExecuteCommand(sql);
            }
        }
Exemplo n.º 2
0
 public void ReadExcel()
 {
     using (ExcelHelper excel = new ExcelHelper("abc.xlsx"))
     {
         excel.Hdr = "YES";
         DataTable dt = excel.ReadTable("Sheet1");
         Console.WriteLine(dt.Columns[0].ToString());
     }
 }
Exemplo n.º 3
0
        //创建ExcelHelper 该EXCEL是用于存放SKU数据
        private ExcelHelper CreateExcelForBanggoSku(string sheetName)
        {
            FileHelper.CreateDirectory(Resource.SysConfig_Sku);

            string filePath = @"Sku\{0} banggoSku.xls".StringFormat(DateTime.Now.ToString("yyyy-MM-dd"));

            var excel = new ExcelHelper(filePath) { Imex = "0", Hdr = "YES" };

            if (File.Exists(filePath))
            {
                if (SysUtils.CheckTableExsit(excel, sheetName))
                {
                    return excel;
                }
            }
            var dic = new Dictionary<string, string>
                {
                    {"产品地址", "varchar(255)"},
                    {"款号", "Double"},
                    {"售价", "Double"},
                    {"库存","Double"},
                    {"SKU", "text"},
                    {"售完","Double"}
                };

            excel.WriteTable(sheetName, dic);
            return excel;
        }
Exemplo n.º 4
0
        // 得到对手的详细销售信息
        /// <summary>
        /// 得到对手的详细销售信息
        /// </summary>
        /// <param name="item">某个对手HTML结点</param>
        /// <param name="excel">excel</param>
        /// <param name="shellName">工作表名</param>
        /// <returns></returns>
        private void GetRivalDetails(HtmlNode item, ExcelHelper excel, string shellName, DataRow dr)
        {
            #region 得到taobao该对手的价格数据 并添加到excel中

            var salePrice = item.SelectSingleNode("div[@class='row row-focus']/div[1]").InnerText.GetNumberDecimal();
            var postage = item.SelectSingleNode("div[@class='row row-focus']/div[2]").InnerText.GetNumberDecimal();
            var salesVolume = item.SelectSingleNode("div[@class='row']/div[1]").InnerText.GetNumberDecimal();
            var evaluate = item.SelectSingleNode("div[@class='row']/div[2]").InnerText.GetNumberDecimal();
            var url = item.SelectSingleNode("h3[@class='summary']/a").Attributes["href"].Value.Trim();
            var title = XmlHelper.XmlDecode(item.SelectSingleNode("h3[@class='summary']/a/@title").InnerText.Trim());
            var rivalName = item.SelectSingleNode("div[@class='row']/div[@class='col seller']/a").InnerText.Trim();
            var rivalLocation =
                item.SelectSingleNode("div[@class='row']/div[@class='col end loc']").InnerText.Trim();

            dr["标题"] = title;
            dr["总价"] = salePrice + postage;
            dr["价格"] = salePrice;
            dr["邮费"] = postage;
            dr["销量"] = salesVolume;
            dr["评价数"] = evaluate;
            dr["用户名"] = rivalName;
            dr["地点"] = rivalLocation;
            dr["网址"] = url;

            #endregion

            _log.LogInfo(Resource.Log_GetRivalDetailsing.StringFormat(rivalName));

            ////如果有销售情况,就提取他的销售详情
            //if (salesVolume > 0)
            //{
            var saleDetail = SysUtils.GetHtmlDocumentByHttpGet(url);

            #region 获取SKU数据

            dr["SKU"] = "";
            dr["成交记录"] = "";

            sbRivalSkuData.Clear();

            var nodeStock = saleDetail.DocumentNode.SelectSingleNode("//*[@id=\"J_SpanStock\"]");

            if (nodeStock != null)
            {
                sbRivalSkuData.AppendLine("库存:{0};".StringFormat(nodeStock.InnerText));
            }

            var nodeColors = saleDetail.DocumentNode.SelectNodes("//*[@id=\"J_isku\"]/div/dl[2]/dd/ul/li");
            if (nodeColors != null)
            {
                foreach (var nColor in nodeColors)
                {
                    var color =
                        "颜色:{0};".StringFormat(
                            nColor.InnerText.Trim()
                                  .Replace("已选中", "")
                                  .Replace("\\t", "")
                                  .Replace("\\r\\n", "")
                                  .Trim());
                    if (!color.IsNullOrEmpty())
                        sbRivalSkuData.AppendLine(color);
                }
            }

            var nodeSizes = saleDetail.DocumentNode.SelectNodes("//*[@id=\"J_isku\"]/div/dl[1]/dd/ul/li");
            if (nodeSizes != null)
            {
                foreach (var nSize in nodeSizes)
                {
                    var size =
                        "尺码:{0};".StringFormat(
                            nSize.InnerText.Trim()
                                 .Replace("已选中", "")
                                 .Replace("\\t", "")
                                 .Replace("\\r\\n", "")
                                 .Trim());
                    if (!size.IsNullOrEmpty())
                        sbRivalSkuData.AppendLine(size);
                }
            }

            dr["SKU"] = TextHelper.TrimEndLf(sbRivalSkuData.ToString());

            sbRivalSkuData.Clear();

            #endregion

            #region 提取详细销售记录数据

            var buyer = saleDetail.GetElementbyId("J_listBuyerOnView");
            if (buyer == null)
            {
                _log.LogWarning(Resource.Log_UnableGetMallSaleData.StringFormat(rivalName));

                return;
            }
            /*可以提取数量和邮费等信息
             * http://ajax.tbcdn.cn/json/ifq.htm?id=20651779110&sid=820330575&sbn=fe93d967b0bacbda0f41f3eb67d4c0f4&p=1&al=false&ap=1&ss=0&free=0&q=1&ex=0&exs=0&shid=&at=b&ct=1*/

            var saleRecordUrl =
                saleDetail.GetElementbyId("J_listBuyerOnView").Attributes["detail:params"].Value.Replace(
                    ",showBuyerList", "&t={0}&callback=Hub.data.records_reload".StringFormat(DateTime.Now.Ticks));

               /* var saleDetailHtml =
                SysUtils.GetHtmlByHttpGet(saleRecordUrl)
                        .Trim()
                        .Replace("Hub.data.records_reload(", "")
                        .TrimEnd(')');*/

            var saleDetailHtml =
               HttpHelper.GETDataToUrl(saleRecordUrl)
                       .Trim()
                       .Replace("Hub.data.records_reload(", "")
                       .TrimEnd(')');

            if (saleDetailHtml.StartsWith("{html:"))
            {
                JObject jObj = JObject.Parse(saleDetailHtml);

                saleDetail.LoadHtml(jObj.SelectToken("html").Value<string>());

                var nodes =
                    saleDetail.DocumentNode.SelectNodes("/table/tbody/tr");

                if (nodes == null)
                    return;

                var sbContent = new StringBuilder();

                foreach (var node in nodes)
                {
                    sbContent.AppendLine(XmlHelper.XmlDecode(node.InnerText.Trim()));
                }

                dr["成交记录"] = TextHelper.TrimEndLf(sbContent.ToString());
            }

            #endregion

            _log.LogInfo(Resource.Log_GetRivalDetailsSuccess.StringFormat(rivalName));
        }
Exemplo n.º 5
0
        //创建ExcelHelper
        private static ExcelHelper CreateExcelForRivalPrice(string sheetName)
        {
            FileHelper.CreateDirectory("Sku");

            string filePath = @"Sku\{0} 分析淘宝.xls".StringFormat(DateTime.Now.ToString("yyyy-MM-dd"));

            var excel = new ExcelHelper(filePath);
            excel.Imex = "0";
            excel.Hdr = "YES";

            if (File.Exists(filePath))
            {
                if (SysUtils.CheckTableExsit(excel, sheetName))
                {
                    return excel;
                }
            }

            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("款号", "double");
            dic.Add("售价", "double");
            dic.Add("成本价", "double");
            dic.Add("利润", "double");
            dic.Add("价格", "double");
            dic.Add("邮费", "double");
            dic.Add("总价", "double");
            dic.Add("销量", "double");
            dic.Add("评价数", "double");
            dic.Add("用户名", "varchar(255)");
            dic.Add("地点", "varchar(255)");
            dic.Add("标题", "varchar(255)");
            dic.Add("网址", "varchar(255)");
            dic.Add("SKU", "varchar(255)");
            dic.Add("成交记录", "text");

            excel.WriteTable(sheetName, dic);
            return excel;
        }
Exemplo n.º 6
0
        //创建ExcelHelper 该EXCEL是用于存放SKU数据
        private ExcelHelper CreateExcelForSell(string sheetName)
        {
            FileHelper.CreateDirectory("Sell");

            string filePath = @"Sell\{0} 销售.xls".StringFormat(DateTime.Now.ToString("yyyy-MM-dd"));

            var excel = new ExcelHelper(filePath) { Imex = "0", Hdr = "YES" };

            if (File.Exists(filePath))
            {
                if (SysUtils.CheckTableExsit(excel, sheetName))
                {
                    return excel;
                }
            }
            var dic = new Dictionary<string, string>
                {
                    {"订单编号", "varchar(255)"},
                    {"卖出时间", "varchar(255)"},
                    {"货源", "varchar(255)"},
                    {"购买人", "varchar(255)"},
                    {"款号", "Double"},
                    {"颜色", "varchar(255)"},
                    {"尺码", "varchar(255)"},
                    {"商品属性", "varchar(255)"},
                    {"原价", "Double"},
                    {"买家应付邮费", "Double"},
                    {"单件售价", "Double"},
                    {"购买数量", "Double"},
                    {"支出邮费", "Double"},
                    {"销售金额", "Double"},
                    {"付款金额", "Double"},
                    {"退款金额", "Double"},
                    {"利润", "varchar(255)"},
                    {"结帐情况", "varchar(255)"},
                    {"结帐时间", "varchar(255)"},
                    {"购买帐号", "varchar(255)"},
                    {"备注", "varchar(255)"},
                };

            excel.WriteTable(sheetName, dic);
            return excel;
        }
Exemplo n.º 7
0
        private void AddDataRow(dynamic q, DataTable dt, ExcelHelper excel,int repOrder =0,string stuffRemark = null)
        {
            //如果是多个来源,那就要用单独的来源
            var jRemark =
                TextHelper.ToEngInterpunction(
                    stuffRemark ?? TextHelper.ToDBC(q.Remark.ToString().Trim('\'')));
              jRemark = jRemark.Trim('\'');
            if (!VerifyRemark(jRemark))
            {
                _log.LogError("订单号:{0},的来源信息不合法.", q.OrderNo);
                return;
            }

            DataRow dr = dt.NewRow();
            dr["订单编号"] = q.OrderNo;
            dr["卖出时间"] = ObjectExtendMethod.ToDateTime(q.CreateTime).ToString("yyyy/MM/dd");
            dr["款号"] = q.GoodsSn;
            dr["购买人"] = q.UserName;
            dr["商品属性"] = q.Props;
            dr["销售金额"] = q.TotalPrice;
            dr["买家应付邮费"] = q.Postage;
            dr["单件售价"] = q.Price;
            dr["购买数量"] = q.Count;
            //                dr["结帐情况"]
            //                dr["结帐时间"]

            JObject jObj = JObject.Parse(jRemark);
            if (jObj != null)
            {
                var source = jObj.SelectToken("来源");

                if (source != null) dr["货源"] = source.Value<string>();

                var costPrice = jObj.SelectToken("成本价");

                if (repOrder > 0)
                {
                    dr["付款金额"] = costPrice != null ? (object)(decimal.Round(costPrice.Value<decimal>()/repOrder,2)) : 0;
                }
                else
                {
                    dr["付款金额"] = costPrice != null ? (object)costPrice.Value<string>() : 0;
                }

                dr["原价"] = ObjectExtendMethod.GetNumberInt(q.Title.ToString());

                var pastage = jObj.SelectToken("邮费");
                dr["支出邮费"] = pastage != null ? (object) costPrice.Value<string>() : 0;

                var remark = jObj.SelectToken("备注");
                dr["备注"] = remark != null ? (object) remark.Value<string>() : "";

                var refund = jObj.SelectToken("退款金额");
                dr["退款金额"] = refund != null ? (object) refund.Value<string>() : 0;
            }
            else
            {
                dr["付款金额"] = 0;
                dr["原价"] = 0;
                dr["支出邮费"] = 0;
                dr["退款金额"] = 0;
            }

            GetColorAndSize(dr, q.Props.ToString());

            excel.AddNewRow(dr);
        }
Exemplo n.º 8
0
        //检查该EXCEL是否有该表存在
        /// <summary>
        /// 检查该EXCEL是否有该表存在
        /// </summary>
        /// <param name="excel"></param>
        /// <param name="shellName"></param>
        /// <returns></returns>
        public static bool CheckTableExsit(ExcelHelper excel, string shellName)
        {
            DataTable dtSchema = excel.GetSchema();

            return (from DataRow dr in dtSchema.Rows select dr["TABLE_NAME"].ToString()).Select(name => name == shellName).FirstOrDefault();
        }