private void LoadTemplate_Click(object sender, RoutedEventArgs e)
{
var filePath = string.Empty;
OpenFileDialog openFileDialog = new OpenFileDialog();
if (_migrationContainer.Destination != null)
{
openFileDialog.InitialDirectory = _migrationContainer.Destination.FileDirectory;
}
else
{
openFileDialog.InitialDirectory = "C:\\";
}
openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsm|All files (*.*)|*.*";
openFileDialog.FilterIndex = 2;
openFileDialog.RestoreDirectory = true;
Nullable <bool> result = openFileDialog.ShowDialog();
if (result == true)
{
filePath = openFileDialog.FileName;
AsposeExcel Template = new AsposeExcel(filePath);
LoadFromTemplate(Template);
}
else
{
var msg = new MessageView("Please choose some template file");
}
}
private Dictionary <string, int> LoadColumn(AsposeExcel source, int sourceColumnIndex)
{
Dictionary <string, int> column = new Dictionary <string, int>();
int emptykeyscounter = 0;
for (int i = 0; i < source.RowsCount; i++)
{
var key = source.ReadCell(source.ColumnCaptionRow + 1 + i, sourceColumnIndex).ToLower();
if (!column.ContainsKey(key))
{
column.Add(source.ReadCell(source.ColumnCaptionRow + 1 + i, sourceColumnIndex).ToLower(), i);
}
if (key == string.Empty)
{
emptykeyscounter++;
}
if (emptykeyscounter > 10)
{
break;
}
}
return(column);
}
public ActionResult RenderToExcel3(PubPage fg)
{
string sql = "select * from SystemUser";
var dt = _iSysUserServices.ToDataTable(sql, null);
AsposeExcel.ToExcel(dt);
return(Content(""));
}
//=== Json ===/
public tempJson JsonToAspose() // GET /Master/JsonToAspose
{
// Declarations
tempJson tempjson = new tempJson();
AsposeExcel service = new AsposeExcel();
tempjson = GetAndConvJson(tempjson); // Call tempJson and convert
return(service.AsposeJsontoWB(tempjson)); // Create workbook
}
private void LoadFromTemplate(AsposeExcel template)
{
string sourcefullpath = template.ReadCell(0, 1);
string destinationfullpath = template.ReadCell(1, 1);
string sourceworksheet = template.ReadCell(0, 2);
string destinationworksheet = template.ReadCell(1, 2);
if (File.Exists(sourcefullpath) && File.Exists(destinationfullpath))
{
_migrationContainer.Source = new AsposeExcel(sourcefullpath);
InitSourceExcel();
_migrationContainer.Destination = new AsposeExcel(destinationfullpath);
InitDestinationExcel();
if (_migrationContainer.Source.ChangeWorksheet(sourceworksheet) && _migrationContainer.Destination.ChangeWorksheet(destinationworksheet))
{
_migrationContainer.TemplateAttached = true;
for (int crow = 3; crow < template.RowsCount; crow++)
{
ColumnParser col = new ColumnParser();
col.SourceColumnName = template.ReadCell(crow, 0);
col.SourceColumnIndex = Convert.ToInt16(template.ReadCell(crow, 1));
col.DestinationColumnName = template.ReadCell(crow, 2);
col.DestinationColumnIndex = Convert.ToInt16(template.ReadCell(crow, 3));
col.IsKey = Convert.ToBoolean(template.ReadCell(crow, 4));
col.LookupMatch = Convert.ToBoolean(template.ReadCell(crow, 5));
_migrationContainer.ColumnParsers.Add(col);
}
if (_migrationContainer.ColumnParsers.Count() > 0)
{
var msg = new MessageView("Template has been loaded");
msg.Show();
}
else
{
var msg = new MessageView("Cannot find any column relations");
msg.Show();
}
}
else
{
var msg = new MessageView("Cannot find worksheets");
msg.Show();
}
}
else
{
var msg = new MessageView("Cannot find files. Put them in the same directory as written in excel file");
msg.Show();
}
}
// Example route: https://localhost:5001/Master/FiletoEPPlus
// Under this controller you will create routes to the service for various package examples
#endregion Namespace_Details
#region Aspose
//=== File Uploader ===//
public async Task <IActionResult> FiletoAspose(IFormFile file) // GET /Master/FiletoAspose
{
AsposeExcel service = new AsposeExcel();
if (file == null || file.Length == 0)
{
return(Content("File not selected"));
}
FileInfo fi = new FileInfo(file.FileName);
var data = service.AsposeFiletoWB(fi, file);
return(null);
}
public FileStreamResult DownLoadOutsideFiles(string id)
{
//下载计数
//data_statistics_infoService dataStatisticsService = new data_statistics_infoService();
//int myId = int.Parse(id);
//IQueryable<data_statistics> sSet = dataStatisticsService.LoadEntites(o => o.item_id == myId && o.data_catagory==3);
//if (sSet.Count() != 0)
//{
// var sItem = sSet.First() as data_statistics;
// data_statistics nds = new data_statistics
// {
// id = sItem.id,
// data_catagory = sItem.data_catagory,
// item_id = sItem.item_id,
// browse_quantity = sItem.browse_quantity,
// download_quantity = sItem.download_quantity + 1
// };
// dataStatisticsService.UpdateEntity(nds);
//}
//
if (Session["UserLevel"] != null)
{
int userLevel = Convert.ToInt32(Session["UserLevel"].ToString());
}
var detailInfo = detailService.LoadEntites(d => d.ID == Convert.ToInt32(id)).First();
if (detailInfo != null)
{
if (!string.IsNullOrEmpty(detailInfo.Data_Detail_Script))
{
string strSql = string.Format(detailInfo.Data_Detail_Script);
DataSet ds = commonService.QueryDataSet(strSql);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
string fileName = detailInfo.Data_Detail_Name;
string filePath = Server.MapPath(@"\Content\Files\") + fileName + ".xlsx";
AsposeExcel.OutFileToDisk(ds, fileName, filePath);
return(File(new FileStream(filePath, FileMode.Open), "application/octet-stream", fileName + ".xlsx"));
}
}
}
return(null);
}
/// <summary>
/// 导出所有相关的数据
/// </summary>
public string ExportExcel(string fileName)
{
Common_BLL pg = new Common_BLL();
BasicVariable(); //基本变量赋值
Where(); //执行获取查看范围
if (TableView.IsNullOrEmpty())
{
TableView = GetGridAttr("TableView");
}
string ExcelField = string.Empty;
foreach (XElement e in Node)
{
ExcelField += " " + e.Attribute("Key").Value + " AS [" + e.Attribute("Name").Value + "],";
}
ExcelField = ExcelField.Substring(0, ExcelField.Length - 1);
#region 读取数据
DataTable list_x = null;
DataSet ds = pg.GetData(TableView.IsNullOrEmpty() ? TableName : TableView, ExcelField, SQLWhere, SQLOrder);
if (ds.Tables.Count > 0)
{
list_x = ds.Tables[0];
//Sys_Menu MenuModel = new Sys_MenuBLL().FindByURL(Request.RawUrl);
string ph = AsposeExcel.OutFileToDisk(list_x, fileName, false);
if (ph != "0")
{
return("javascript:" + ph + "," + fileName);
}
else
{
return("javascript:'" + ph + "',''");
}
}
return("javascript:''0'',''");
#endregion
}
private int LastEmptyRow(AsposeExcel destination)
{
return(destination.RowsCount + 1);
}