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 = "哈哈" }); }