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();
}