public static void ExportToExcel(DataSet dataSet, string fileName)
{
//export a DataSet to Excel
DialogResult retry = DialogResult.Retry;
while (retry == DialogResult.Retry)
{
try
{
using (ExcelWriter writer = new ExcelWriter(fileName))
{
writer.WriteStartDocument();
foreach (DataTable data in dataSet.Tables)
{
string wsName = data.TableName;
// Write the worksheet contents
writer.WriteStartWorksheet(wsName);
//Write header row
writer.WriteStartRow();
foreach (DataColumn col in data.Columns)
{
writer.WriteExcelUnstyledCell(col.Caption);
}
writer.WriteEndRow();
//write data
foreach (DataRow row in data.Rows)
{
writer.WriteStartRow();
foreach (object o in row.ItemArray)
{
writer.WriteExcelAutoStyledCell(o);
}
writer.WriteEndRow();
}
writer.WriteEndWorksheet();
}
// Close up the document
writer.WriteEndDocument();
writer.Close();
retry = DialogResult.Cancel;
}
}
catch (Exception myException)
{
retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk);
}
}
}
public ActionResult ExportFromCfg()
{
var excelWriter = new ExcelWriter();
Dictionary<string, object> listColumn = new Dictionary<string, object>();
excelWriter.Map("LoginName","登录名");
excelWriter.Map("Name", "姓名");
excelWriter.Map("MobilePhone", "手机号");
excelWriter.Map("Birthday", "生日");
byte[] buffer = excelWriter.Write(OrganizationBusiness.GetUserById(CurrentUser.UserID), new Dictionary<string, ExcelFormatter>() { { "birthday", new ExcelFormatter() { ColumnTrans = EnumColumnTrans.ConvertTime, DropSource = "" } } });
var fileName = "用户信息导入";
if (!Request.ServerVariables["http_user_agent"].ToLower().Contains("firefox"))
fileName = HttpUtility.UrlEncode(fileName);
this.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xlsx");
return File(buffer, "application/ms-excel");
}
private ExcelWriter GetPinLeiKeywordExcelWriter()
{
String exportDir = this.RunPage.GetExportDir();
Dictionary <string, int> resultColumnDic = CommonUtil.InitStringIndexDic(new string[] {
"品类",
"词汇",
"出现次数"
});
string resultFilePath = Path.Combine(exportDir, "万方期刊_品类_行业词汇.xlsx");
Dictionary <string, string> columnFormats = new Dictionary <string, string>();
columnFormats.Add("出现次数", "#0");
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, columnFormats);
return(resultEW);
}
private bool GenerateDetailPageInfo(IListSheet listSheet)
{
bool succeed = true;
string pageSourceDir = this.RunPage.GetDetailSourceFileDir();
Dictionary <string, int> resultColumnDic = new Dictionary <string, int>();
resultColumnDic.Add("商品名称", 0);
resultColumnDic.Add("价格", 1);
resultColumnDic.Add("分类", 2);
resultColumnDic.Add("分类编码", 3);
resultColumnDic.Add("规格", 4);
resultColumnDic.Add("评论数", 5);
resultColumnDic.Add("好评", 6);
resultColumnDic.Add("中评", 7);
resultColumnDic.Add("差评", 8);
resultColumnDic.Add("好评度", 9);
resultColumnDic.Add("url", 10);
resultColumnDic.Add("参考价", 11);
resultColumnDic.Add("商品编码", 12);
resultColumnDic.Add("备注", 13);
resultColumnDic.Add("甜度", 14);
resultColumnDic.Add("城市", 15);
Dictionary <string, string> resultColumnFormat = new Dictionary <string, string>();
resultColumnFormat.Add("价格", "#,##0.00");
resultColumnFormat.Add("评论数", "#,##0");
resultColumnFormat.Add("好评", "#,##0");
resultColumnFormat.Add("中评", "#,##0");
resultColumnFormat.Add("差评", "#,##0");
resultColumnFormat.Add("好评度", "0.00%");
resultColumnFormat.Add("参考价", "#,##0.00");
resultColumnFormat.Add("甜度", "#,##0");
string readDetailDir = this.RunPage.GetReadFileDir();
string exportDir = this.RunPage.GetExportDir();
string resultFilePath = Path.Combine(exportDir, "天天果园商品详情" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, resultColumnFormat);
GenerateDetailPageInfo(listSheet, pageSourceDir, resultEW);
resultEW.SaveToDisk();
return(succeed);
}
public void finish()
{
if (rbRetoreAll.Checked)
{
if (!System.IO.Path.GetExtension(txtPathFile.Text).Equals(".bak"))
{
MessageBox.Show("Vui lòng kiểm tra lại đường dẫn lưu file sao lưu !");
txtPathFile.Text = folderBrowserDialog.SelectedPath + "//backup_" + DateTime.Now.ToFileTime().ToString() + extendFile;
return;
}
ManagerPartient.CustomControl.FormLoad f = new ManagerPartient.CustomControl.FormLoad();
f.TopMost = true;
f.TopLevel = true;
f.Size = new System.Drawing.Size(SystemInformation.VirtualScreen.Width, SystemInformation.VirtualScreen.Height);
f.WindowState = FormWindowState.Maximized;
f.Show();
con = new SqlConnection("Data Source=.\\SQLEXPRESS;Database=Master;data source=.; uid=sa; pwd=123456;");
con.Open();
cmd = new SqlCommand("Backup database nks_db to disk='" + txtPathFile.Text + "'", con);
cmd.ExecuteNonQuery();
con.Close();
f.Close();
DialogResult dialogResult = MessageBoxEX.DialogMessageBox("Thông báo", "Sao lưu dữ liệu thành công", "File sao lưu được lưu ở " + txtPathFile.Text);
if (dialogResult.Equals(DialogResult.OK))
{
this.ParentForm.Close();
}
}
else
{
if (rbInsertData.Checked)
{
if (!System.IO.Path.GetExtension(txtPathFile.Text).Equals(".xls"))
{
MessageBox.Show("Vui lòng kiểm tra lại đường dẫn lưu file sao lưu !");
txtPathFile.Text = folderBrowserDialog.SelectedPath + "//backup_" + DateTime.Now.ToFileTime().ToString() + extendFile;
return;
}
ExcelWriter.WriteExcelFile <tb_patient>(txtPathFile.Text, PatientModelManage.Instance.FindAllPatients(), false);
this.ParentForm.Close();
}
}
}
private void GetAllGoodsInCategory(string locationName, NcpAppElement categoryElement)
{
try
{
hasNoneNewElementTime = 0;
GoodsElementList = new NcpAppElementList();
Size winSize = AppAccess.GetWindowSize();
AppAccess.SwipeDisplayElements(new Point(winSize.Width - 20, winSize.Height - 200),
new Point(winSize.Width - 20, 400),
1000,
10000,
GetCategoryGoodsItems);
//保存到文件
int goodsCount = int.Parse(categoryElement.Attributes["count"]);
string[] goodsColumns = new string[] { "id",
"name",
"price",
"categoryName",
"locationName" };
Dictionary <string, int> goodsColumnDic = CommonUtil.InitStringIndexDic(goodsColumns);
string goodsFilePath = (goodsCount == GoodsElementList.Count ? this.GetCategoryGoodsFilePath(locationName, categoryElement.Id) : this.GetCategoryGoodsFilePath("_Error_" + locationName, categoryElement.Id));
ExcelWriter goodsEW = new ExcelWriter(goodsFilePath, "List", goodsColumnDic, null);
for (int i = 0; i < GoodsElementList.Count; i++)
{
NcpAppElement element = GoodsElementList[i];
string id = element.Id;
string name = element.Name;
string price = element.Attributes["price"];
Dictionary <string, string> row = new Dictionary <string, string>();
row.Add("id", element.Id);
row.Add("name", name);
row.Add("categoryName", categoryElement.Name);
row.Add("price", price);
row.Add("locationName", locationName);
goodsEW.AddRow(row);
}
goodsEW.SaveToDisk();
}
catch (Exception ex)
{
throw new Exception("获取商品信息失败, locationName = " + locationName + ", categoryId = " + categoryElement.Id, ex);
}
}
public override bool BeforeAllGrab()
{
string[] ps = this.Parameters.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
this.GoogleUrlPrefix = ps[0];
this.LinkedinLoginPageUrl = ps[1];
this.LinkedinLoginSucceedCheckUrl = ps[2];
string excelFilePath = this.RunPage.ExcelFilePath;
ExcelReader er = new ExcelReader(excelFilePath, "List");
Dictionary <string, int> columnNameToIndex = CommonUtil.InitStringIndexDic(new string[] {
"detailPageUrl",
"detailPageName",
"cookie",
"grabStatus",
"giveUpGrab",
"loginName",
"loginPassword",
"keyWords",
"公司名称",
"公司名称关键词",
"所属领域",
"备注"
});
ExcelWriter ew = new ExcelWriter(excelFilePath, "List", columnNameToIndex);
var listCount = er.GetRowCount();
for (int i = 0; i < listCount; i++)
{
Dictionary <string, string> listRow = er.GetFieldValues(i);
string keyWords = listRow["keyWords"];
if (keyWords.Contains(" "))
{
keyWords = "\"" + keyWords + "\"";
}
listRow["detailPageUrl"] = this.GoogleUrlPrefix + "/?gws_rd=ssl#q=" + CommonUtil.UrlEncode(keyWords + " inurl:cn.linkedin.com/in/ ");
listRow["detailPageName"] = CommonUtil.UrlEncode(listRow["keyWords"]);
ew.AddRow(listRow);
}
er.Close();
ew.SaveToDisk();
return(true);
}
private bool GetCertNoCompCountList(IListSheet listSheet)
{
String exportDir = this.RunPage.GetExportDir();
Dictionary <string, int> resultColumnDic = new Dictionary <string, int>();
resultColumnDic.Add("certNo", 0);
resultColumnDic.Add("companyCount", 1);
string resultFilePath = Path.Combine(exportDir, "各CertNo对应企业个数.xlsx");
Dictionary <string, string> resultColumnFormat = new Dictionary <string, string>();
resultColumnFormat.Add("companyCount", "#,##0");
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, resultColumnFormat);
string detailPageUrlColumnName = SysConfig.DetailPageUrlFieldName;
for (int i = 0; i < listSheet.RowCount; i++)
{
Dictionary <string, string> row = listSheet.GetRow(i);
bool giveUp = "Y".Equals(row[SysConfig.GiveUpGrabFieldName]);
if (!giveUp)
{
string url = row[detailPageUrlColumnName];
string certNo = row["certNo"];
HtmlAgilityPack.HtmlDocument pageHtmlDoc = this.RunPage.GetLocalHtmlDocument(listSheet, i);
string pageText = pageHtmlDoc.DocumentNode.SelectSingleNode("//form[@class=\"pagingform\"]").NextSibling.NextSibling.InnerText;
int totalStartIndex = pageText.IndexOf("\"$total\":") + 9;
int totalEndIndex = pageText.IndexOf(",", totalStartIndex);
string totalCountStr = pageText.Substring(totalStartIndex, totalEndIndex - totalStartIndex);
int companyCount = int.Parse(totalCountStr);
Dictionary <string, object> f2vs = new Dictionary <string, object>();
f2vs.Add("certNo", certNo);
f2vs.Add("companyCount", companyCount);
resultEW.AddRow(f2vs);
}
}
resultEW.SaveToDisk();
return(true);
}
public override bool AfterAllGrab(IListSheet listSheet)
{
string[] paramters = this.Parameters.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
string nameListFilePath = paramters[0];
string outputFilePath = paramters[1];
int searchPageCount = int.Parse(paramters[2]);
ExcelReader er = new ExcelReader(nameListFilePath, "List");
int rowCount = er.GetRowCount();
Dictionary <string, int> resultColumnDic = CommonUtil.InitStringIndexDic(new string[] {
"detailPageUrl",
"detailPageName",
"cookie",
"grabStatus",
"giveUpGrab",
"keywords",
"pageIndex"
});
ExcelWriter resultEW = new ExcelWriter(outputFilePath, "List", resultColumnDic);
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < searchPageCount; j++)
{
Dictionary <string, string> row = er.GetFieldValues(i);
string keywords = row["keywords"];
string keywordsUrl = CommonUtil.UrlEncode(keywords);
int beginPageIndex = j * 14 + 1;
string pageUrl = "https://cn.bing.com/search?q=" + keywordsUrl + "%20site%3awww.linkedin.com%2fin&qs=n&sp=-1&pq=" + keywordsUrl + "%20site%3awww.linkedin.com%2fin&sc=0-41&sk=&cvid=0EA188BEA7174D4CBD57CDBFDA73B06C&&first=" + beginPageIndex.ToString() + "&FORM=PERE";
row["detailPageUrl"] = pageUrl;
row["detailPageName"] = pageUrl;
row["keywords"] = keywords;
row["pageIndex"] = (j + 1).ToString();
resultEW.AddRow(row);
}
}
resultEW.SaveToDisk();
return(base.AfterAllGrab(listSheet));
}
private void GenerateRelatedItemFile(string fromItemId, string fromItemName, string fromItemTitle, string fromItemUrl, HtmlNodeCollection aNodes)
{
String exportDir = this.RunPage.GetExportDir();
string partDir = CommonUtil.MD5Crypto(fromItemTitle + "_" + fromItemId).Substring(0, 4);
string resultFilePath = Path.Combine(exportDir, partDir + "/易贸商务_百度百科_词条关联_" + CommonUtil.ProcessFileName(fromItemTitle, "_") + "_" + fromItemId + ".xlsx");
if (!File.Exists(resultFilePath))
{
Dictionary <string, int> resultColumnDic = new Dictionary <string, int>();
resultColumnDic.Add("fromItemUrl", 0);
resultColumnDic.Add("fromItemId", 1);
resultColumnDic.Add("fromItemName", 2);
resultColumnDic.Add("fromItemTitle", 3);
resultColumnDic.Add("toItemUrl", 4);
resultColumnDic.Add("toItemId", 5);
resultColumnDic.Add("toItemName", 6);
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, null);
Dictionary <string, bool> itemMaps = new Dictionary <string, bool>();
for (int j = 0; j < aNodes.Count; j++)
{
HtmlNode aNode = aNodes[j];
string toItemUrl = aNode.GetAttributeValue("href", "");
string toItemId = aNode.GetAttributeValue("data-lemmaid", "");
string toItemName = CommonUtil.HtmlDecode(aNode.InnerText).Trim();
if (toItemUrl.StartsWith("/item/") && !itemMaps.ContainsKey(toItemUrl) && this.IsInMainContent(aNode))
{
itemMaps.Add(toItemUrl, true);
string toItemFullUrl = "https://baike.baidu.com" + toItemUrl;
Dictionary <string, string> relatedItemRow = new Dictionary <string, string>();
relatedItemRow.Add("fromItemUrl", fromItemUrl);
relatedItemRow.Add("fromItemId", fromItemId);
relatedItemRow.Add("fromItemName", fromItemName);
relatedItemRow.Add("fromItemTitle", fromItemTitle);
relatedItemRow.Add("toItemUrl", toItemFullUrl);
relatedItemRow.Add("toItemId", toItemId);
relatedItemRow.Add("toItemName", toItemName);
resultEW.AddRow(relatedItemRow);
}
}
resultEW.SaveToDisk();
}
}
public ActionResult CreateExcelReportTemlateDemo()
{
// 1.创建报表模版
string filePhysicalPath = SysContext.Config.ReportTemplateDirectory_Physical + "测试模板.xlsx";
string savePhysicalPath = SysContext.Config.TempDirectory_Physical + SysContext.CommonService.CreateUniqueNameForFile(filePhysicalPath);
using (ExcelWriter excelWriter = new ExcelWriter(filePhysicalPath, "生成报表模版", savePhysicalPath))
{
excelWriter.CreateExcelReportTemlate<ExcelDataViewModel>();
}
// 2.展现Excel文件
return View("~/Views/Shared/Controls/ExcelReportView.cshtml", new ExcelDisplayViewModel
{
Title = "创建Excel报表模版 Demo",
FilePhysicalPath = savePhysicalPath,
SheetName = "生成报表模版"
});
}
public ActionResult AddCommentToExcelDemo()
{
// 1.添加批注
string filePhysicalPath = SysContext.Config.ReportTemplateDirectory_Physical + "测试模板.xlsx";
using (ExcelWriter excelWriter = new ExcelWriter(filePhysicalPath, "哈哈"))
{
excelWriter.AddCommentToCell(3, 1, "数据格式错误", "必须是GUID");
excelWriter.AddCommentToCell(3, 8, "数据格式错误", "必须是数字");
}
// 2.展现Excel文件
return View("~/Views/Shared/Controls/ExcelReportView.cshtml", new ExcelDisplayViewModel
{
Title = "Excel添加批注 Demo",
FilePhysicalPath = filePhysicalPath,
SheetName = "哈哈"
});
}
public void WriteTest()
{
var filename = TestUtility.OutputPath + "write-test.xlsx";
PairedData pd1 = TestUtility.CreatePairedData(3, 10);
ExcelWriter w = new ExcelWriter(filename);
w.Write(pd1, "Sheet1");
ExcelReader r = new ExcelReader(filename);
PairedData pd2 = r.GetPairedData("Sheet1");
Assert.IsTrue(Enumerable.SequenceEqual(pd1.Ordinates, pd2.Ordinates));
Assert.IsTrue(Enumerable.SequenceEqual(pd1.Values[0], pd2.Values[0]));
Assert.IsTrue(Enumerable.SequenceEqual(pd1.Values[1], pd2.Values[1]));
Assert.IsTrue(Enumerable.SequenceEqual(pd1.Values[2], pd2.Values[2]));
File.Delete(filename);
}
public override bool AfterAllGrab(IListSheet listSheet)
{
ExcelWriter ew = this.GetExcelWriter();
for (int i = 0; i < 99; i++)
{
string code = i.ToString().PadLeft(2, '0');
Dictionary <string, string> f2vs = new Dictionary <string, string>();
f2vs.Add("detailPageUrl", "http://jzsc.mohurd.gov.cn/dataservice/query/staff/list?_=" + code);
f2vs.Add("detailPageName", code);
f2vs.Add("cookie", "filter_comp=; JSESSIONID=869A8BA542331894C3EE0830A02BB34F; Hm_lvt_b1b4b9ea61b6f1627192160766a9c55c=1515742554,1516079327,1516091394,1516159353; Hm_lpvt_b1b4b9ea61b6f1627192160766a9c55c=1516160300");
f2vs.Add("formData", "ry_type=&ry_reg_type=&ry_name=®_seal_code=&ry_cardno=&ry_qymc=&complexname=" + code);
ew.AddRow(f2vs);
}
ew.SaveToDisk();
return(true);
}
private ExcelWriter CreateRenWuColumnPropertyListWriter(List <string> propertyColumnNames)
{
String exportDir = this.RunPage.GetExportDir();
string resultFilePath = Path.Combine(exportDir, "百度百科_人物_属性值宽表.xlsx");
Dictionary <string, int> resultColumnDic = new Dictionary <string, int>();
resultColumnDic.Add("name", 0);
resultColumnDic.Add("fullName", 1);
resultColumnDic.Add("url", 2);
for (int i = 0; i < propertyColumnNames.Count; i++)
{
resultColumnDic.Add(propertyColumnNames[i], i + 3);
}
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, null);
return(resultEW);
}
protected FileResult ExportGridToExcelImpl(string gridName)
{
Check.EnsureNotNull(gridName);
// In DHTMLX Grid 4.2 Formulas don't work so PrintFooter true is not very useful, leaving off for now
var generator = new ExcelWriter {
PrintFooter = false
};
var xml = Request.Form["grid_xml"];
Check.EnsureNotNull(xml, "Could not find 'grid_xml' form element in form POST. Is this a properly formed POST request?");
xml = Server.UrlDecode(xml);
xml = xml?.Replace("<![CDATA[$", "<![CDATA["); // RL 7/11/2015 Poor man's hack to remove currency and allow for total rows
xml = BlankRowFixup(xml);
Check.EnsureNotNull(xml);
var stream = generator.Generate(xml);
return(File(stream.ToArray(), generator.ContentType, $"{gridName}-{DateTime.Now.ToStringDateTimeForFileName()}.xlsx"));
}
private ExcelWriter CreateMoreItemWriter(int pageIndex)
{
String exportDir = this.RunPage.GetExportDir();
string resultFilePath = Path.Combine(exportDir, "易贸商务_百度百科_详情页_" + pageIndex.ToString() + ".xlsx");
Dictionary <string, int> resultColumnDic = new Dictionary <string, int>();
resultColumnDic.Add("detailPageUrl", 0);
resultColumnDic.Add("detailPageName", 1);
resultColumnDic.Add("cookie", 2);
resultColumnDic.Add("grabStatus", 3);
resultColumnDic.Add("giveUpGrab", 4);
resultColumnDic.Add("itemId", 5);
resultColumnDic.Add("itemName", 6);
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, null);
return(resultEW);
}
public ActionResult ClearCommentInExcelDemo()
{
// 1.清除批注
string filePhysicalPath = SysContext.Config.ReportTemplateDirectory_Physical + "测试模板.xlsx";
using (ExcelWriter excelWriter = new ExcelWriter(filePhysicalPath, "哈哈"))
{
excelWriter.ClearCommentInCell(3, 8);
}
// 2.展现Excel文件
return(View("~/Views/Shared/Controls/ExcelReportView.cshtml", new ExcelDisplayViewModel
{
Title = "Excel清除批注 Demo",
FilePhysicalPath = filePhysicalPath,
SheetName = "哈哈"
}));
}
private ExcelWriter GetNoneMatchedExcelWriter()
{
String exportDir = this.RunPage.GetExportDir();
Dictionary <string, int> resultColumnDic = CommonUtil.InitStringIndexDic(new string[] {
"FirmID",
"FirmName",
"LastName",
"FirstName",
"MiddleName",
"MatchType"
});
string resultFilePath = Path.Combine(exportDir, "Linkedin自动匹配结果.xlsx");
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic);
return(resultEW);
}
private ExcelWriter GetExcelWriter()
{
String exportDir = this.RunPage.GetExportDir();
Dictionary <string, int> resultColumnDic = new Dictionary <string, int>();
resultColumnDic.Add("detailPageUrl", 0);
resultColumnDic.Add("detailPageName", 1);
resultColumnDic.Add("cookie", 2);
resultColumnDic.Add("grabStatus", 3);
resultColumnDic.Add("giveUpGrab", 4);
resultColumnDic.Add("formData", 5);
string resultFilePath = Path.Combine(exportDir, "项目数据_项目列表页首页.xlsx");
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, null);
return(resultEW);
}
private ExcelWriter CreateResultWriter()
{
String exportDir = this.RunPage.GetExportDir();
string resultFilePath = Path.Combine(exportDir, "成语_YiTang_小类列表页.xlsx");
Dictionary <string, int> resultColumnDic = new Dictionary <string, int>();
resultColumnDic.Add("detailPageUrl", 0);
resultColumnDic.Add("detailPageName", 1);
resultColumnDic.Add("cookie", 2);
resultColumnDic.Add("grabStatus", 3);
resultColumnDic.Add("giveUpGrab", 4);
resultColumnDic.Add("name", 5);
resultColumnDic.Add("pageType", 6);
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, null);
return(resultEW);
}
/// <summary>
/// GetCities
/// </summary>
/// <param name="listSheet"></param>
/// <param name="resultEW"></param>
private void GetQuals(IListSheet listSheet, ExcelWriter resultEW)
{
string[] paramStrs = this.Parameters.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
int qualCount = int.Parse(paramStrs[0]);
int pageIndex = 0;
while (pageIndex * this.QualCountPerPage < qualCount)
{
Dictionary <string, object> f2vs = new Dictionary <string, object>();
f2vs.Add("detailPageUrl", "http://jzsc.mohurd.gov.cn/asite/qualapt/aptData?apt_type=&t=" + pageIndex.ToString());
f2vs.Add("detailPageName", pageIndex.ToString());
f2vs.Add("cookie", "filter_comp=show; JSESSIONID=DC4BC03F99DEDEBEFEE739B680BC5230; Hm_lvt_b1b4b9ea61b6f1627192160766a9c55c=1513578016,1513646440,1514281557,1514350446; Hm_lpvt_b1b4b9ea61b6f1627192160766a9c55c=1514356771");
f2vs.Add("pageIndex", pageIndex);
f2vs.Add("qualCount", qualCount);
resultEW.AddRow(f2vs);
pageIndex++;
}
}
private ExcelWriter GetExcelBaseWriter()
{
String exportDir = this.RunPage.GetExportDir();
Dictionary <string, int> resultColumnDic = CommonUtil.InitStringIndexDic(new string[] {
"url",
"id",
"名字",
"目前工作",
"地区",
"keywords"
});
string resultFilePath = Path.Combine(exportDir, "Linkedin个人基本信息.xlsx");
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic);
return(resultEW);
}
private async void cmdExport_Executed(object sender, EventArgs e)
{
if (folderBrowserDialog.ShowDialog() != DialogResult.OK)
{
return;
}
var folder = folderBrowserDialog.SelectedPath;
string fileName = string.Format("FFP{0}.xlsx", DateTime.Now.ToString("yyyyMMdd"));
string filePath = Path.Combine(folder, fileName);
if (File.Exists(filePath))
{
File.Delete(filePath);
}
var ffpSetId = await GetFFPSetIdAsync();
var service = ServiceFactory.S.GetFFPService();
using (var writer = new ExcelWriter(filePath))
{
writer.CreateSheet(FFP_SCHEMA);
int currentPage = 1;
int itemsPerPage = 100;
while (true)
{
int start = (currentPage - 1) * itemsPerPage + 1;
int end = start + itemsPerPage;
SetRunningStatus(true, string.Format("正在导出第{0}-{1}条数据...", start, end));
var res = await service.GetSqlDataAsync(currentPage, itemsPerPage, ffpSetId);
if (res.Arg.Length == 0)
{
SetRunningStatus(false);
break;
}
List <string> list =
res.Arg.Select(sql => string.Format("INSERT INTO [FFP] VALUES {0};", sql)).ToList();
await writer.InsertAsync(list);
currentPage++;
}
}
}
/// <summary>
/// GetCities
/// </summary>
/// <param name="listSheet"></param>
/// <param name="pageSourceDir"></param>
/// <param name="resultEW"></param>
private void GetProvinces(IListSheet listSheet, string pageSourceDir, ExcelWriter resultEW)
{
string[] paramStrs = this.Parameters.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
string qualListFilePath = paramStrs[0];
ExcelReader qualER = new ExcelReader(qualListFilePath);
List <string> qualCodeList = new List <string>();
int qualCount = qualER.GetRowCount();
for (int i = 0; i < listSheet.RowCount; i++)
{
string pageUrl = listSheet.PageUrlList[i];
Dictionary <string, string> row = listSheet.GetRow(i);
string localFilePath = this.RunPage.GetFilePath(pageUrl, pageSourceDir);
string fileText = FileHelper.GetTextFromFile(localFilePath);
JObject rootJo = JObject.Parse(fileText);
JArray allProvinceObjects = rootJo.SelectToken("json").SelectToken("category").SelectToken("provinces") as JArray;
for (int j = 0; j < allProvinceObjects.Count; j++)
{
JObject provinceObject = allProvinceObjects[j] as JObject;
string regionId = (provinceObject.SelectToken("region_id") as JValue).Value.ToString();
string regionName = (provinceObject.SelectToken("region_name") as JValue).Value.ToString();
string regionFullName = (provinceObject.SelectToken("region_fullname") as JValue).Value.ToString();
for (int k = 0; k < qualCount; k++)
{
Dictionary <string, string> qual = qualER.GetFieldValues(k);
string aptCode = qual["aptCode"];
string aptScope = qual["aptScope"];
Dictionary <string, string> f2vs = new Dictionary <string, string>();
f2vs.Add("detailPageUrl", "http://jzsc.mohurd.gov.cn/dataservice/query/comp/list?regionId=" + regionId + "&aptCode=" + aptCode);
f2vs.Add("detailPageName", regionId + aptCode);
f2vs.Add("cookie", "filter_comp=show; JSESSIONID=DC4BC03F99DEDEBEFEE739B680BC5230; Hm_lvt_b1b4b9ea61b6f1627192160766a9c55c=1513578016,1513646440,1514281557,1514350446; Hm_lpvt_b1b4b9ea61b6f1627192160766a9c55c=1514356771");
f2vs.Add("regionId", regionId);
f2vs.Add("regionName", regionName);
f2vs.Add("regionFullName", regionFullName);
f2vs.Add("aptCode", aptCode);
f2vs.Add("aptScope", aptScope);
resultEW.AddRow(f2vs);
}
}
}
}
public override bool AfterAllGrab(IListSheet listSheet)
{
string[] allParameters = this.Parameters.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
string daoluListFilePath = allParameters[0];
string exportDir = allParameters[1];
CsvReader cr = new CsvReader(daoluListFilePath);
int rowCount = cr.GetRowCount();
int fileIndex = 1;
ExcelWriter resultEW = null;
for (int i = 0; i < rowCount; i++)
{
Dictionary <string, string> row = cr.GetFieldValues(i);
if (i % 500000 == 0)
{
if (resultEW != null)
{
resultEW.SaveToDisk();
}
resultEW = this.GetExcelWriter(exportDir, fileIndex);
fileIndex++;
}
string uid = row["uid"];
Dictionary <string, string> f2vs = new Dictionary <string, string>();
f2vs.Add("detailPageUrl", "http://map.baidu.com/?newmap=1&reqflag=pcmap&biz=1&from=webmap&da_par=direct&pcevaname=pc4.1&qt=ext&uid=" + uid + "&l=18&c=2032&tn=B_NORMAL_MAP&nn=0");
f2vs.Add("detailPageName", uid);
f2vs.Add("uid", uid);
f2vs.Add("title", row["title"]);
f2vs.Add("province", row["province"]);
f2vs.Add("city", row["city"]);
f2vs.Add("address", row["address"]);
f2vs.Add("phoneNumber", row["phoneNumber"]);
f2vs.Add("postcode", row["postcode"]);
f2vs.Add("url", row["url"]);
f2vs.Add("lat", row["lat"]);
f2vs.Add("lng", row["lng"]);
resultEW.AddRow(f2vs);
}
resultEW.SaveToDisk();
return(true);
}
private void GetProvinces(IListSheet listSheet)
{
string pageSourceDir = this.RunPage.GetDetailSourceFileDir();
ExcelWriter ew = this.GetExcelWriter();
for (int i = 0; i < listSheet.RowCount; i++)
{
Dictionary <string, string> row = listSheet.GetRow(i);
bool giveUp = "Y".Equals(row[SysConfig.GiveUpGrabFieldName]);
if (!giveUp)
{
string detailUrl = row["detailPageUrl"];
string year = row["year"];
try
{
HtmlAgilityPack.HtmlDocument htmlDoc = this.RunPage.GetLocalHtmlDocument(listSheet, i, Encoding.GetEncoding("gb2312"));
HtmlNodeCollection provinceNodeList = htmlDoc.DocumentNode.SelectNodes("//tr[@class=\"provincetr\"]/td/a");
for (int j = 0; j < provinceNodeList.Count; j++)
{
HtmlNode provinceNode = provinceNodeList[j];
string hrefValue = provinceNode.GetAttributeValue("href", "");
int codeEndIndex = hrefValue.IndexOf(".");
string code = hrefValue.Substring(0, codeEndIndex);
string name = provinceNode.InnerText.Trim();
Dictionary <string, string> f2vs = new Dictionary <string, string>();
f2vs.Add("detailPageUrl", "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/" + year + "/" + hrefValue);
f2vs.Add("detailPageName", year + "_" + code);
f2vs.Add("year", year);
f2vs.Add("code", code);
f2vs.Add("name", name);
ew.AddRow(f2vs);
}
}
catch (Exception ex)
{
throw ex;
}
}
}
ew.SaveToDisk();
}
public override bool AfterAllGrab(IListSheet listSheet)
{
string filePath = Path.Combine(this.RunPage.GetExportDir(), "京东店铺商品.xlsx");
Dictionary <string, int> columnNameToIndex = CommonUtil.InitStringIndexDic(new string[] {
"detailPageUrl",
"detailPageName",
"cookie",
"grabStatus",
"giveUpGrab",
"name",
"subscribe",
"mark",
"jdSelf",
"keyword"
});
ExcelWriter ew = new ExcelWriter(filePath, "List", columnNameToIndex);
int keywordCount = listSheet.RowCount;
Dictionary <string, string> nameKeywordDic = new Dictionary <string, string>();
for (int i = 0; i < keywordCount; i++)
{
Dictionary <string, string> listRow = listSheet.GetRow(i);
string keyword = listRow["keyword"];
string keywordShopFilePath = this.GetKeywordShopFilePath(keyword);
List <Dictionary <string, string> > shopInfos = this.ReadKeywordShopFromLocalFile(keywordShopFilePath);
foreach (Dictionary <string, string> shopInfo in shopInfos)
{
string name = shopInfo["name"];
string nameKeyword = name + "_" + keyword;
if (!nameKeywordDic.ContainsKey(nameKeyword))
{
nameKeywordDic.Add(name, null);
shopInfo.Add("detailPageUrl", name);
shopInfo.Add("detailPageName", nameKeyword);
ew.AddRow(shopInfo);
}
}
}
ew.SaveToDisk();
return(base.AfterAllGrab(listSheet));
}
private ExcelWriter GetCompanInfoExcelWriter(string destFilePath)
{
Dictionary <string, int> columnDic = CommonUtil.InitStringIndexDic(new string[] {
"Company_Name",
"Page_Company_Name",
"EmployerId",
"ReviewCount",
"JobCount",
"SalaryCount",
"InterViewCount",
"BenefitCount",
"PhotoCount",
"WebSite",
"Headquarters",
"Size",
"Founded",
"Type",
"Industry",
"Revenue",
"Competitors",
"RatingNum",
"RecommenToAFriend",
"ApproveOfCEO",
"CEOName",
"CEORatings"
});
Dictionary <string, string> columnFormats = new Dictionary <string, string>();
columnFormats.Add("ReviewCount", "#0");
columnFormats.Add("JobCount", "#0");
columnFormats.Add("SalaryCount", "#0");
columnFormats.Add("InterViewCount", "#0");
columnFormats.Add("BenefitCount", "#0");
columnFormats.Add("PhotoCount", "#0");
columnFormats.Add("RatingNum", "#0.00");
columnFormats.Add("RecommenToAFriend", "#0.00");
columnFormats.Add("ApproveOfCEO", "#0.00");
columnFormats.Add("CEORatings", "#0");
ExcelWriter ew = new ExcelWriter(destFilePath, "List", columnDic, columnFormats);
return(ew);
}
public HttpResponseBase ExportStatistics(int id)
{
List <VendorEmailStatisticEntry> statisticItems = new List <VendorEmailStatisticEntry>();
_context.VendorStatisticsEntries.Where(x => x.VendorId == id && x.Type == VendorStatisticsEntry.CONTACT_ME_TYPE).ToList()
.ForEach(x => statisticItems.Add(new VendorEmailStatisticEntry(x)));
ExcelWriter writer = new ExcelWriter();
writer.startWorksheet("Statistics");
writer.startRow();
writer.addCell("Person name");
writer.addCell("Person email");
writer.addCell("Facility name");
writer.addCell("Category name");
writer.addCell("Date/time");
writer.endRow();
foreach (VendorEmailStatisticEntry entry in statisticItems)
{
writer.startRow();
writer.addCell(entry.User.Name);
writer.addCell(entry.User.Email);
writer.addCell(entry.User.FacilityName);
writer.addCell(entry.CategoryName);
writer.addCell(entry.OccurredAt);
writer.endRow();
}
writer.endWorksheet();
writer.endWorkbook();
string vendorName = _context.Vendors.Where(x => x.Id == id).First().Name;
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=Statistics for " + vendorName + ".xls");
Response.Charset = "";
Response.ContentType = "application/ms-excel";
Response.Write(writer.getString());
Response.End();
return(Response);
}
/// <summary>
/// 创建Excel文件
/// </summary>
/// <param name="htmlCode"></param>
/// <param name="sheetName"></param>
/// <returns>生成文件的路径</returns>
public string CreateExcel(string htmlCode, string sheetName, string fileName)
{
PageJson result = new PageJson();
string fullFileName = string.Empty;
try
{
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "sheet1";
}
htmlCode = Server.UrlDecode(htmlCode);
sheetName = Server.UrlDecode(sheetName);
fileName = Server.UrlDecode(fileName);
ExcelWriter myExcel = new ExcelWriter(sheetName);
myExcel.WriteData(htmlCode);
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFiles");
fullFileName = myExcel.SaveAsFile(path, fileName);
}
catch
{
result.Success = false;
result.Message = "未知原因导致生成表格失败！";
return(result.ToJsonString());
}
result.Success = true;
result.Message = fullFileName;
string str = result.ToJsonString();
str = Regex.Replace(str, @"\\", "/");
//删除临时生成的文件
ThreadStart deleTempFile = () =>
{
Thread.Sleep(1000 * 30);
System.IO.File.Delete(fullFileName);
};
Thread newThread = new Thread(deleTempFile);
newThread.Start();
return(str);
}
private void ProcessReviewStatistic(string sourceFilePath, string destFilePath)
{
ExcelReader er = new ExcelReader(sourceFilePath, "List-detai_review_new");
int rowCount = er.GetRowCount();
Dictionary <string, Dictionary <string, object> > statisticValue = new Dictionary <string, Dictionary <string, object> >();
for (int i = 0; i < rowCount; i++)
{
Dictionary <string, string> row = er.GetFieldValues(i);
string company = row["Company_Name"];
string city = row["Location"];
string year = row["year"];
string key = company + "_" + city + "_" + year;
if (!statisticValue.ContainsKey(key))
{
Dictionary <string, object> newValue = new Dictionary <string, object>();
newValue.Add("Company", company);
newValue.Add("City", city);
newValue.Add("Year", year);
newValue.Add("Rows", new List <Dictionary <string, string> >());
statisticValue.Add(key, newValue);
}
Dictionary <string, object> value = statisticValue[key];
List <Dictionary <string, string> > rows = (List <Dictionary <string, string> >)value["Rows"];
rows.Add(row);
}
ExcelWriter resultEW = this.GetDestExcelWriter(destFilePath);
foreach (string key in statisticValue.Keys)
{
Dictionary <string, object> value = statisticValue[key];
string company = (string)value["Company"];
string city = (string)value["City"];
string year = (string)value["Year"];
List <Dictionary <string, string> > rows = (List <Dictionary <string, string> >)value["Rows"];
Dictionary <string, object> resultRow = this.GetStatisticValue(key, company, city, year, rows);
resultEW.AddRow(resultRow);
}
resultEW.SaveToDisk();
}
private void GetXiaoLeiFirstPageUrls(IListSheet listSheet)
{
string sourceDir = this.RunPage.GetDetailSourceFileDir();
ExcelWriter resultEW = this.CreateResultWriter();
for (int i = 0; i < listSheet.RowCount; i++)
{
Dictionary <string, string> listRow = listSheet.GetRow(i);
HtmlAgilityPack.HtmlDocument htmlDoc = this.RunPage.GetLocalHtmlDocument(listSheet, i);
HtmlNodeCollection linkNodes = htmlDoc.DocumentNode.SelectNodes("//ul[@class=\"ulLi120 fsc16\"]/li/a");
foreach (HtmlNode linkNode in linkNodes)
{
string linkUrl = linkNode.GetAttributeValue("href", "");
string linkName = CommonUtil.HtmlDecode(linkNode.InnerText).Trim();
string fullLinkUrl = "http://www.yitang.org" + linkUrl;
Dictionary <string, string> resultRow = new Dictionary <string, string>();
resultRow.Add("detailPageUrl", fullLinkUrl);
resultRow.Add("detailPageName", linkName + "_word");
resultRow.Add("giveUpGrab", "Y");
resultRow.Add("name", linkName);
resultRow.Add("pageType", "word");
resultEW.AddRow(resultRow);
}
HtmlNodeCollection moreLinkNodes = htmlDoc.DocumentNode.SelectNodes("//a[@class=\"more\"]");
foreach (HtmlNode moreLinkNode in moreLinkNodes)
{
string moreLinkUrl = moreLinkNode.GetAttributeValue("href", "");
string fullMoreLinkUrl = "http://www.yitang.org" + moreLinkUrl;
Dictionary <string, string> resultRow = new Dictionary <string, string>();
resultRow.Add("detailPageUrl", fullMoreLinkUrl);
resultRow.Add("detailPageName", moreLinkUrl);
resultRow.Add("giveUpGrab", "N");
resultRow.Add("name", moreLinkUrl);
resultRow.Add("pageType", "list");
resultEW.AddRow(resultRow);
}
}
resultEW.SaveToDisk();
}
private void GetSearchPageUrls(IListSheet listSheet)
{
String[] parameters = this.Parameters.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
string exportDir = parameters[0];
Dictionary <string, int> resultColumnDic = CommonUtil.InitStringIndexDic(new string[] {
"detailPageUrl",
"detailPageName",
"cookie",
"grabStatus",
"giveUpGrab",
"keyword",
"品类",
"词类型"
});
string resultFilePath = Path.Combine(exportDir, "万方期刊_专业关键词_搜索页首页.xlsx");
ExcelWriter resultEW = new ExcelWriter(resultFilePath, "List", resultColumnDic, null);
for (int i = 0; i < listSheet.RowCount; i++)
{
Dictionary <string, string> row = listSheet.GetRow(i);
string detailUrl = row["detailPageUrl"];
string keyword = row["keyword"];
string pinLei = row["品类"];
string ciLeiXing = row["词类型"];
bool giveUp = "Y".Equals(row[SysConfig.GiveUpGrabFieldName]);
if (!giveUp)
{
string keywordEncode = CommonUtil.StringToHexString(keyword, Encoding.UTF8);
string detailPageUrl = "http://librarian.wanfangdata.com.cn/SearchResult.aspx?dbhit=wf_qk%3a2466%7cwf_xw%3a188%7cwf_hy%3a87%7cnstl_qk%3a0%7cnstl_hy%3a0&q=%e5%85%b3%e9%94%ae%e8%af%8d%3a(%22" + keywordEncode + "%22)+*+Date%3a2015-2018&db=wf_qk%7cwf_xw%7cwf_hy%7cnstl_qk%7cnstl_hy&p=1";
string detailPageName = row["keyword"] + "_" + row["品类"];
Dictionary <string, string> f2vs = new Dictionary <string, string>();
f2vs.Add("detailPageUrl", detailPageUrl);
f2vs.Add("detailPageName", detailPageName);
f2vs.Add("keyword", row["keyword"]);
f2vs.Add("品类", row["品类"]);
f2vs.Add("词类型", row["词类型"]);
resultEW.AddRow(f2vs);
}
}
resultEW.SaveToDisk();
}
public ActionResult ExportFromProduct(bool test = false, string model = "", string filleName = "产品", string filter = "")
{
JavaScriptSerializer serializer=new JavaScriptSerializer();
Dictionary<string, ExcelFormatter> dic = new Dictionary<string, ExcelFormatter>();
FilterProduct qicProduct = serializer.Deserialize<FilterProduct>(filter);
Dictionary<string, ExcelModel> listColumn = new Dictionary<string, ExcelModel>();
if (string.IsNullOrEmpty(filter))
{
listColumn = GetColumnForJson("product", ref dic, model, test ? "testexport" : "export", CurrentUser.ClientID);
}
else
{
qicProduct = serializer.Deserialize<FilterProduct>(filter);
listColumn = GetColumnForJson("product", ref dic, !string.IsNullOrEmpty(model) ? model : "Item", test ? "testexport" : "export", CurrentUser.ClientID);
}
string ipPath = "";
var excelWriter = new ExcelWriter();
foreach (var key in listColumn)
{
excelWriter.Map(key.Key, key.Value.Title);
}
byte[] buffer;
DataTable dt = new DataTable();
//模版导出
if (test)
{
DataRow dr = dt.NewRow();
foreach (var key in listColumn)
{
DataColumn dc1 = new DataColumn(key.Key, Type.GetType("System.String"));
dt.Columns.Add(dc1);
dr[key.Key] = key.Value.DefaultText;
}
dt.Rows.Add(dr);
}
else
{
int totalCount = 0;
int pageCount = 0;
ipPath= Server.MapPath("~");
dt = new ProductsBusiness().GetProductListDataTable(qicProduct.CategoryID, qicProduct.BeginPrice, qicProduct.EndPrice, qicProduct.Keywords, qicProduct.OrderBy, qicProduct.IsAsc, PageSize, qicProduct.PageIndex, ref totalCount, ref pageCount, CurrentUser.ClientID);
}
buffer = excelWriter.Write(dt, dic, ipPath);
var fileName = filleName + (test ? "导入模版" : "")+DateTime.Now.ToString("yyyyMMdd");
if (!Request.ServerVariables["http_user_agent"].ToLower().Contains("firefox"))
fileName = HttpUtility.UrlEncode(fileName);
this.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xlsx");
return File(buffer, "application/ms-excel");
}
public ActionResult ExportFromCustomer(bool test = false, string model = "", string filleName = "企业客户", string filter="")
{
Dictionary<string, ExcelFormatter> dic = new Dictionary<string, ExcelFormatter>();
FilterCustomer qicCustomer =new FilterCustomer();
Dictionary<string, ExcelModel> listColumn = new Dictionary<string, ExcelModel>();
if (string.IsNullOrEmpty(filter))
{
listColumn = GetColumnForJson("customer", ref dic, model, test ? "testexport" : "export", CurrentUser.ClientID);
}
else
{
JavaScriptSerializer serializer = new JavaScriptSerializer();
qicCustomer=serializer.Deserialize<FilterCustomer>(filter);
listColumn = GetColumnForJson("customer", ref dic, !string.IsNullOrEmpty(model) ? model : qicCustomer.ExcelType == 0 ? "Item" : "OwnItem", test ? "testexport" : "export", CurrentUser.ClientID);
}
var excelWriter = new ExcelWriter();
foreach (var key in listColumn)
{
excelWriter.Map(key.Key, key.Value.Title);
}
byte[] buffer;
DataTable dt = new DataTable();
//模版导出
if (test)
{
DataRow dr = dt.NewRow();
foreach (var key in listColumn)
{
DataColumn dc1 = new DataColumn(key.Key, Type.GetType("System.String"));
dt.Columns.Add(dc1);
if (key.Key == "extent")
{
dr[key.Key] = "下拉框中选择";
}
else if (key.Key == "industryid")
{
dr[key.Key] = "下拉框中选择";
}
else if (key.Key == "province")
{
dr[key.Key] = "下拉框中选择";
}
else
{
dr[key.Key] = key.Value.DefaultText;
}
}
dt.Rows.Add(dr);
}
else
{
int totalCount = 0;
int pageCount = 0;
//客户
dt = CustomBusiness.BaseBusiness.GetCustomersDatable(qicCustomer.SearchType, qicCustomer.Type,
qicCustomer.SourceID, qicCustomer.StageID, qicCustomer.Status, qicCustomer.Mark,
qicCustomer.ActivityID, qicCustomer.UserID, qicCustomer.TeamID, qicCustomer.AgentID,
qicCustomer.BeginTime, qicCustomer.EndTime,
qicCustomer.Keywords, qicCustomer.OrderBy, int.MaxValue, 1, ref totalCount, ref pageCount,
CurrentUser.UserID, CurrentUser.AgentID, CurrentUser.ClientID, qicCustomer.ExcelType);
if (!dt.Columns.Contains("province"))
{
dt.Columns.Add("province", Type.GetType("System.String"));
}
if (!dt.Columns.Contains("citys"))
{
dt.Columns.Add("citys", Type.GetType("System.String"));
}
if (!dt.Columns.Contains("counties"))
{
dt.Columns.Add("counties", Type.GetType("System.String"));
}
foreach (DataRow drRow in dt.Rows)
{
var city= CommonBusiness.GetCityByCode(drRow["CityCode"].ToString());
if (city != null)
{
drRow["province"] = city.Province;
drRow["citys"] = city.City;
drRow["counties"] = city.Counties;
}
}
}
buffer = excelWriter.Write(dt, dic);
var fileName = filleName + (test ? "导入模版" : "") + DateTime.Now.ToString("yyyyMMdd");
if (!Request.ServerVariables["http_user_agent"].ToLower().Contains("firefox"))
fileName = HttpUtility.UrlEncode(fileName);
this.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xlsx");
return File(buffer, "application/ms-excel");
}
public ActionResult ProductImport(HttpPostedFileBase file)
{
string mes = "";
if (file == null) { return Content("请选择要导入的文件"); }
if (file.ContentLength > 2097152)
{
return Content("导入文件超过规定（2M )大小,请修改后再操作.");
}
if (!file.FileName.Contains(".xls") && !file.FileName.Contains(".xlsx"))
{
return Content("文件格式类型不正确");
}
try
{
DataTable dt = ImportExcelToDataTable(file);
if (dt.Columns.Count > 0)
{
///1.获取系统模版列
Dictionary<string, ExcelFormatter> dic=new Dictionary<string, ExcelFormatter>();
Dictionary<string, ExcelModel> listColumn = GetColumnForJson("product", ref dic, "", "import", CurrentUser.ClientID);
;//GetColumnForJson("product", "Item");
///2.上传Excel 与模板中列是否一致 不一致返回提示
foreach (DataColumn dc in dt.Columns)
{
if (default(KeyValuePair<string, ExcelModel>).Equals(listColumn.FirstOrDefault(x => x.Value.Title == dc.ColumnName)))
{
mes += dc.ColumnName + ",";
}
}
if (!string.IsNullOrEmpty(mes))
{
return Content("Excel模版与系统模版不一致,请重新下载模板,编辑后再上传.错误:缺少列 " + mes);
}
///3.开始处理
int k = 1;
var excelWriter = new ExcelWriter();
List<Products> list = new List<Products>();
foreach (DataRow dr in dt.Rows)
{
try
{
Products product=new Products();
excelWriter.GetProductByDataRow(dr, listColumn, product,dic,CurrentUser.ClientID);
product.CreateUserID = CurrentUser.UserID;
product.ClientID = CurrentUser.ClientID;
product.BigUnitID = "";
list.Add(product);
}
catch (Exception ex)
{
mes += k +" 原因:"+ex.Message+ ",";
}
}
try
{
if (list.Count > 0)
{
mes= ExcelImportBusiness.InsertProduct(list);
}
if (!string.IsNullOrEmpty(mes))
{
return Content("部分数据未导入成功,原因如下 ：" + mes);
}
else
{
return Content("操作成功");
}
}
catch (Exception ex)
{
return Content("系统异常:请联系管理员,错误原因" + ex.Message);
}
}
if (!string.IsNullOrEmpty(mes))
{
return Content("部分数据未导入成功,Excel行位置" + mes);
}
}
catch (Exception ex)
{
return Content("系统异常:请联系管理员,错误原因:" + ex.Message.ToString());
}
return Content(mes);
}
public void WriteToFile(Portal portal)
{
IWriter writer = new ExcelWriter();
writer.WriteToFile(portal);
}
public ActionResult ExportFromCfg()
{
var excelWriter = new ExcelWriter();
Dictionary<string, object> listColumn = new Dictionary<string, object>();
excelWriter.Map("Name", "公司名称");
excelWriter.Map("Contcat", "客户名称");
excelWriter.Map("Jobs", "职位");
excelWriter.Map("MobilePhone", "联系电话");
excelWriter.Map("Email", "邮箱");
excelWriter.Map("Province", "省");
excelWriter.Map("City", "市");
excelWriter.Map("District", "区");
excelWriter.Map("Address", "详细地址");
excelWriter.Map("Description", "描述");
byte[] buffer = excelWriter.Write(OrganizationBusiness.GetUserById(CurrentUser.UserID),
new Dictionary<string, ExcelFormatter>()
{
{
"birthday", new ExcelFormatter()
{
ColumnTrans = EnumColumnTrans.ConvertTime,
DropSource = ""
}
}
});
var fileName = "客户信息导入";
if (!Request.ServerVariables["http_user_agent"].ToLower().Contains("firefox"))
fileName = HttpUtility.UrlEncode(fileName);
this.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xlsx");
return File(buffer, "application/ms-excel");
}
public ActionResult CustomerImport(HttpPostedFileBase file, int overType = 0, int type=0)
{
if (file == null) { return Content("请选择要导入的文件"); }
if (file.ContentLength > 2097152)
{
return Content("导入文件超过规定（2M )大小,请修改后再操作.");
}
if (!file.FileName.Contains(".xls") && !file.FileName.Contains(".xlsx")) { return Content("文件格式类型不正确"); }
string mes = "";
try
{
DataTable dt = ImportExcelToDataTable(file);
if (dt.Columns.Count > 0)
{
///1.获取系统模版列
var checkColumn = dt.Columns.Contains("是否企业客户");
Dictionary<string, ExcelFormatter> dic = new Dictionary<string, ExcelFormatter>();
Dictionary<string, ExcelModel> listColumn = GetColumnForJson("customer", ref dic, checkColumn ? "Item" : "OwnItem", "import", CurrentUser.ClientID);
///2.上传Excel 与模板中列是否一致 不一致返回提示
foreach (DataColumn dc in dt.Columns)
{
if (default(KeyValuePair<string, ExcelModel>).Equals(listColumn.FirstOrDefault(x => x.Value.Title == dc.ColumnName)))
{
mes += dc.ColumnName + ",";
}
}
if (!string.IsNullOrEmpty(mes))
{
return Content("Excel模版与系统模版不一致,请重新下载模板,编辑后再上传.错误:缺少列 " + mes);
}
///3.开始处理
int k = 1;
List<CustomerEntity> list = new List<CustomerEntity>();
List<ContactEntity> contactList = new List<ContactEntity>();
var excelWriter = new ExcelWriter();
foreach (DataRow dr in dt.Rows)
{
try
{
if (checkColumn)
{
CustomerEntity customer = new CustomerEntity();
excelWriter.GetProductByDataRow(dr, listColumn, customer, dic, CurrentUser.ClientID);
customer.ClientID = CurrentUser.ClientID;
customer.AgentID = CurrentUser.AgentID;
customer.CreateUserID = CurrentUser.UserID;
customer.CreateTime = DateTime.Now;
customer.CustomerID = Guid.NewGuid().ToString();
customer.CityCode = GetCityCode(dr);
customer.OwnerID = customer.OwnerID.Trim() == "是" ? CurrentUser.UserID : "";
list.Add(customer);
//上面出问题的话请注释以上 使用下面
//list.Add(GetCustomerByDataRow(dr));
}
else
{
ContactEntity contact = new ContactEntity();
excelWriter.GetProductByDataRow(dr, listColumn, contact, dic, CurrentUser.ClientID);
contact.CityCode = GetCityCode(dr);
contact.ClientID = CurrentUser.ClientID;
contact.AgentID = CurrentUser.AgentID;
contact.CreateUserID = CurrentUser.UserID;
contact.OwnerID = CurrentUser.UserID;
contact.CustomerID = "";
contact.CreateTime = DateTime.Now;
contact.Type = 0;
contact.ContactID = Guid.NewGuid().ToString();
contactList.Add(contact);
//contactList.Add( GetContactByDataRow(dr, checkColumn));
}
}
catch (Exception ex)
{
mes += k +" 原因:"+ex.Message+ ",";
}
}
try
{
if (list.Count > 0) ExcelImportBusiness.InsertCustomer(list, type, overType);
if (contactList.Count > 0) ExcelImportBusiness.InsertContact(contactList, type, overType);
}
catch (Exception ex)
{
return Content("系统异常:请联系管理员,错误原因" + ex.Message);
}
}
if (!string.IsNullOrEmpty(mes))
{
return Content("部分数据未导入成功,Excel行位置" + mes);
}
}
catch (Exception ex)
{
return Content("系统异常:请联系管理员,错误原因:" + ex.Message.ToString());
}
return Content("操作成功");
}
public ActionResult ClearCommentInExcelDemo()
{
// 1.清除批注
string filePhysicalPath = SysContext.Config.ReportTemplateDirectory_Physical + "测试模板.xlsx";
using (ExcelWriter excelWriter = new ExcelWriter(filePhysicalPath, "哈哈"))
{
excelWriter.ClearCommentInCell(3, 8);
}
// 2.展现Excel文件
return View("~/Views/Shared/Controls/ExcelReportView.cshtml", new ExcelDisplayViewModel
{
Title = "Excel清除批注 Demo",
FilePhysicalPath = filePhysicalPath,
SheetName = "哈哈"
});
}
