protected void btnUpload_Click(object sender, EventArgs e) { string fileName = file.PostedFile.FileName; if (string.IsNullOrEmpty(fileName)) { return; } FileInfo fi = new FileInfo(fileName); string[] exts = new string[] { ".xls", ".xlsx" }; if (!exts.ToList().Exists(ext => ext == fi.Extension)) { this.ltlError.Text = "请上传Excel文件。"; } else { //上传文件 fileName = string.Format("{0}Documents\\TempFolder\\{1:ms}_{2}", this.Request.PhysicalApplicationPath, DateTime.Now, fi.Name); AsposeHelper.CheckPath(fileName); file.PostedFile.SaveAs(fileName); string templateFileName = AsposeHelper.GetTemplateFileName(documentID); DataTable dt = AsposeHelper.GetTemplateDataTable(documentID); BatchUploadResponse response = AsposeHelper.ExcelToDataTable(templateFileName, fileName, dt); if (response.IsVerified) { PersistData(response.Data); } ShowResult(response.GetErrors()); } }
protected void Page_Load(object sender, EventArgs e) { string fileName = Request.QueryString["fileID"]; if (!string.IsNullOrEmpty(fileName)) { fileName = string.Format("{0}Documents\\TempFolder\\{1}" , this.Request.PhysicalApplicationPath, HttpUtility.UrlDecode(fileName)); AsposeHelper.DownloadFile(fileName); } }
private void ExportData(List <SPhone_Chat> data, string targetFile) { string documentID = "Xlt101"; List <ChatMessage> allMsgs = new List <ChatMessage>(); // 保存文件 DataTable dt = AsposeHelper.GetTemplateDataTable(documentID); data.ForEach(chat => { allMsgs.AddRange(GetChatDetails(chat)); }); // lst 转 table allMsgs.ForEach(msg => { DataRow dr = dt.NewRow(); dr["ChatID"] = msg.ChatID; dr["Queue"] = msg.Queue; dr["CustomerID"] = msg.CustomerID; dr["CustomerName"] = msg.CustomerName; dr["Date"] = msg.Date; dr["Time"] = msg.Time; dr["NickName"] = msg.NickName; if (msg.Message != null && msg.Message.IndexOf("<img src=\"data:image/") != -1) { dr["Message"] = Tele.Common.Utils.CutStr(Tele.Common.Utils.NoHtml(msg.Message), 1000); } else if (msg.Message.Length > 1000) { dr["Message"] = Tele.Common.Utils.CutStr(Tele.Common.Utils.NoHtml(msg.Message), 1000); } else { dr["Message"] = msg.Message; } // dt.Rows.Add(dr); }); if (Request.QueryString["debug"] != null) { var qs = allMsgs.OrderByDescending(x => x.Message.Length).Take(10); foreach (var item in qs) { Response.Write(item.ChatID + "\r\n"); } return; } string templateFileName = AsposeHelper.GetTemplateFileName(documentID); //string newFileName = string.Format(@"{0}\Documents\TempFolder\{1}.xlsx", this.Request.PhysicalApplicationPath, dt.TableName + DateTime.Now.ToString("-yyMMdd")); BatchDownloadResponse response = AsposeHelper.DataTableToExcel(templateFileName, targetFile, dt); //AsposeHelper.DownloadFile(newFileName); }
/// <summary> /// 下载模板 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnDownloadTemplate_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(documentID)) { throw new Exception("未传入参数:docid !"); } string templateFileName = AsposeHelper.GetTemplateFileName(documentID); if (!string.IsNullOrEmpty(templateFileName)) { AsposeHelper.DownloadFile(templateFileName); } }
private void ExportData(List <SPhone_Chat> data) { string documentID = "Xlt101"; List <ChatMessage> allMsgs = new List <ChatMessage>(); // 保存文件 DataTable dt = AsposeHelper.GetTemplateDataTable(documentID); data.ForEach(chat => { allMsgs.AddRange(GetChatDetails(chat)); }); // lst 转 table allMsgs.ForEach(msg => { DataRow dr = dt.NewRow(); dr["ChatID"] = msg.ChatID; dr["Queue"] = msg.Queue; dr["CustomerID"] = msg.CustomerID; dr["CustomerName"] = msg.CustomerName; dr["Date"] = msg.Date; dr["Time"] = msg.Time; dr["NickName"] = msg.NickName; if (msg.Message != null && msg.Message.IndexOf("<img src=\"data:image/") != -1) { dr["Message"] = Tele.Common.Utils.CutStr(Tele.Common.Utils.NoHtml(msg.Message), 1000);; } else if (msg.Message.Length > 1000) { dr["Message"] = Tele.Common.Utils.CutStr(Tele.Common.Utils.NoHtml(msg.Message), 1000); } else { dr["Message"] = msg.Message; } // dt.Rows.Add(dr); }); string templateFileName = AsposeHelper.GetTemplateFileName(documentID); string newFileName = string.Format(@"{0}\Documents\TempFolder\{1}.xlsx", this.Request.PhysicalApplicationPath, dt.TableName); BatchDownloadResponse response = AsposeHelper.DataTableToExcel(templateFileName, newFileName, dt); AsposeHelper.DownloadFile(newFileName); }
public void ExportCodeSysCodeSetToExcel(string excelPath, string sheetName, IEnumerable <export_code_sys_code_set> lst) { AsposeHelper helper = new AsposeHelper(); Workbook workbook; using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { workbook = new Workbook(fs); } string exportPath = Path.Combine(AppContext.BaseDirectory, $"{Path.GetFileNameWithoutExtension(excelPath)}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"); IList <Style> styles = helper.CheckTest(workbook); Worksheet wshet = workbook.Worksheets[sheetName]; var cellscodesys = wshet.Cells; int j = 0; for (int k = 0; k < cellscodesys.MaxDataRow; k++) { var row = cellscodesys.CheckRow(k); if (row == null || row.FirstCell == null) { continue; } if (row.GetCellOrNull(0).StringValue == "代码系统代码") { continue; } else { var sdsf = row.GetCellOrNull(6).StringValue; if (!string.IsNullOrWhiteSpace(sdsf)) { var item = lst.FirstOrDefault(p => p.code_sys_code.Trim() == sdsf.Trim()); if (item == null) { Serilog.Log.Information("代码系统:{codesys} 未存在对应的代码集合!", sdsf); continue; } ++j; string sheetname = item.code_sys_code.Remove(0, 4); //去除mdm_ 及 hdr_ 等前缀 if (sheetname.Length > 31) { sheetname = sheetname.Substring(0, 31); } var sheet = workbook.Worksheets.Add(sheetname); var cells = sheet.Cells; IList <code_set_entity> codesets = Newtonsoft.Json.JsonConvert.DeserializeObject <IList <code_set_entity> >(item.codeset); #region 代码集合 表头 //第一行 int rowheader = 0; var cel = cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("A"))]; cel.Value = "返回"; //第二行 ++rowheader; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("A"))].Value = "代码系统代码"; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("A"))].SetStyle(styles[0]); cells.Merge(rowheader, CellsHelper.ColumnNameToIndex("B"), 1, 2); cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("B"))].Value = item.code_sys_code; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("B"))].GetMergedRange().SetStyle(styles[2]); //第三行 ++rowheader; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("A"))].Value = "代码系统名称"; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("A"))].SetStyle(styles[0]); cells.Merge(rowheader, CellsHelper.ColumnNameToIndex("B"), 1, 2); cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("B"))].Value = item.code_sys_name; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("B"))].GetMergedRange().SetStyle(styles[2]); //第四行 ++rowheader; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("A"))].Value = "代码"; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("A"))].SetStyle(styles[0]); cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("B"))].Value = "名称"; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("B"))].SetStyle(styles[0]); cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("c"))].Value = "显示名"; cells[CellsHelper.CellIndexToName(rowheader, CellsHelper.ColumnNameToIndex("c"))].SetStyle(styles[0]); #endregion #region 创建链接 //sheetlist.Hyperlinks.Add(CellsHelper.CellIndexToName(j + codesysoffset, CellsHelper.ColumnNameToIndex("C")), 1, 1, $"{sheet.Name}!{cel.Name}"); //查看链接 //sheet.Hyperlinks.Add(cel.Name, 1, 1, $"{sheetlist.Name}!{ CellsHelper.CellIndexToName(j + codesysoffset, CellsHelper.ColumnNameToIndex("C")) }");//返回链接 //P列 增减快捷链接 var cellName = CellsHelper.CellIndexToName(k, CellsHelper.ColumnNameToIndex("P")); cellscodesys[cellName].Value = "查看"; cellscodesys[cellName].Worksheet.Hyperlinks.Add(cellName, cellName, $"{sheet.Name}!{cel.Name}", "", ""); //返回链接 sheet.Hyperlinks.Add(cel.Name, 1, 1, $"{cellscodesys[cellName].Worksheet.Name}!{cellName}");//返回链接 #endregion #region 填充代码集合 codeset int offset = ++rowheader; for (int i = 0; i < codesets.Count; i++) { var code = codesets[i].code; var name = codesets[i].name; var showname = codesets[i].show_name; cells[CellsHelper.CellIndexToName(i + offset, CellsHelper.ColumnNameToIndex("A"))].Value = code; cells[CellsHelper.CellIndexToName(i + offset, CellsHelper.ColumnNameToIndex("A"))].SetStyle(styles[2]); cells[CellsHelper.CellIndexToName(i + offset, CellsHelper.ColumnNameToIndex("B"))].Value = name; cells[CellsHelper.CellIndexToName(i + offset, CellsHelper.ColumnNameToIndex("B"))].SetStyle(styles[2]); cells[CellsHelper.CellIndexToName(i + offset, CellsHelper.ColumnNameToIndex("C"))].Value = showname; cells[CellsHelper.CellIndexToName(i + offset, CellsHelper.ColumnNameToIndex("C"))].SetStyle(styles[2]); } sheet.IsGridlinesVisible = false; #region 设置宽度 cells.SetColumnWidth(0, 16); cells.SetColumnWidth(1, 30); cells.SetColumnWidth(2, 35); //sheet.AutoFitColumns(); #endregion #endregion } } } //sheetlist.IsGridlinesVisible = false; //sheetlist.AutoFitColumns(); workbook.Save(exportPath, SaveFormat.Xlsx); }