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); } }
public void ReadExcel() { using (ExcelHelper excel = new ExcelHelper("abc.xlsx")) { excel.Hdr = "YES"; DataTable dt = excel.ReadTable("Sheet1"); Console.WriteLine(dt.Columns[0].ToString()); } }
//创建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; }
// 得到对手的详细销售信息 /// <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)); }
//创建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; }
//创建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; }
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); }
//检查该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(); }