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