I am facing a problem that has not been resolved so far and do it over three weeks. I am supposed to create a web application that do the following:
我面临一个迄今尚未解决的问题,并且在三周内完成。我应该创建一个执行以下操作的Web应用程序:
1- Allow the user to select the search criteria from drop down list and click the export button. 2- The button click event calls an API that in its turn fetches the data from the data store based on search criteria, opens an existing Excel template file and fill it with the returned data, and save it with a new name (i.e, original template named abc.xlsx and it will saved as abc[date]-[time].xlsx). 3- Finally download the newly created Excel file.
1-允许用户从下拉列表中选择搜索条件,然后单击导出按钮。 2-按钮单击事件调用API,该API依次根据搜索条件从数据存储中获取数据,打开现有Excel模板文件并使用返回的数据填充它,并使用新名称保存(即原始文件)名为abc.xlsx的模板,它将保存为abc [date] - [time] .xlsx)。 3-最后下载新创建的Excel文件。
I created the application and on the development environment it did the first two steps successfully, when I tired to deploy the application on either my local machine IIS, or on the production server IIS the application did only the first step and part of the second one (i.e., fetching the data from the data store), however, the rest of steps cannot be achieved. Here's my code:
我创建了应用程序并在开发环境中成功完成了前两个步骤,当我厌倦了在我的本地机器IIS或生产服务器IIS上部署应用程序时,应用程序只执行了第一步,而第二步执行了第二步(即,从数据存储中获取数据),然而,其余步骤无法实现。这是我的代码:
$("#dataexporter").click(function () {
if ($("#countrycriteria").val().toString() === "0" || $("#productcriteria").val().toString() === "0") {
$('from').append('<div class="alert alert-warning" role="alert">Please make sure you select all required fields!</div >');
}
else {
$('div[class*="alert-warning"]').remove();
$(this).prop('disabled', true);
$.ajax("api/exportify/export?countrycriteria=" + $("#countrycriteria").val().toString() + "&productcriteria=" + $("#productcriteria").val().toString(), {
type: "GET",
contentType: "application/json; charset=utf-8",
success: function (response, textStatus, jqXHR) {
if (response !== null)
window.location.href = JSON.parse(response).downloadurl
$(this).prop('disabled', false);
},
error: function (jqXHR, textStatus, errorThrown) {
alert('An error occurred ' + errorThrown);
$(this).prop('disabled', false);
}
});
}
});
[RoutePrefix("api/exportify")]
public class ExportController : BaseController
{
private IExporterProvider provider;
private Invoker invoker;
public ExportController()
{
switch (AppConfiguration.ExporterProvider)
{
case "excel":
provider = new ExcelExporterProvider();
break;
default:
throw new NotImplementedException();
}
invoker = new Invoker();
invoker.Commands.Add(provider.GetExporter());
}
private IEnumerable<Analytics> GetData(string countrycriteria, string productcriteria)
{
return helper.SelectByCountryAndProduct(countrycriteria, productcriteria);
}
private Dictionary<string, object> GetResponseContent()
{
string fname = AppConfiguration.SaveAsName;
int lastBackslash = fname.LastIndexOf("\\");
int substringLength = fname.Length - lastBackslash;
string filename = fname.Substring(lastBackslash + 1, substringLength - 1);
return new Dictionary<string, object> {
{ "downloadurl", Path.Combine(AppConfiguration.ServerAddress, AppConfiguration.Temporaryfolder, filename + AppConfiguration.FileExtension) }
};
}
[HttpGet]
[Route("export")]
public IHttpActionResult Export(string countrycriteria, string productcriteria)
{
try
{
List<Analytics> data = (List<Analytics>)GetData(countrycriteria, productcriteria);
if (data.Count > 0)
{
data.ForEach(d => DeterminetheCategory(d));
foreach (var Command in invoker.Commands)
{
Command.ExportedData = data;
}
invoker.Execute();
return Ok(JsonConvert.SerializeObject(GetResponseContent()));
}
else
{
return Content(HttpStatusCode.NoContent, string.Empty);
}
}
catch (Exception e)
{
return Content(HttpStatusCode.InternalServerError, e);
}
}
}
Here's the code that will be ultimately executed when the invoker.Execute(); statement being hit:
这是在invoker.Execute()时最终执行的代码。声明被击中:
public class ExcelExporter
{
protected static void ExportToExcel(IEnumerable<Analytics> data)
{
if (!File.Exists(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, AppConfiguration.Temporaryfolder, AppConfiguration.Filename + AppConfiguration.FileExtension)))
{
throw new FileNotFoundException("File Not Found.\nThe requested analytical.xlsx was not found on the server");
}
Microsoft.Office.Interop.Excel.Application xlsx = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook = null;
Worksheet worksheet = null;
try
{
workbook = xlsx.Workbooks.Open(
Filename: Path.Combine(AppDomain.CurrentDomain.BaseDirectory, AppConfiguration.Temporaryfolder, AppConfiguration.Filename + AppConfiguration.FileExtension),
ReadOnly: false);
worksheet = (Worksheet)workbook.Worksheets[1];
List<Analytics> list = (List<Analytics>)data;
for (int i = 0; i < list.Count; i++)
{
worksheet.Range[string.Format("A{0}", i + 2)].Value = list[i].ProductShare;
worksheet.Range[string.Format("B{0}", i + 2)].Value = list[i].MarketPotential;
worksheet.Range[string.Format("C{0}", i + 2)].Value = list[i].RepresnentativeName;
worksheet.Range[string.Format("D{0}", i + 2)].Value = list[i].DoctorName;
worksheet.Range[string.Format("E{0}", i + 2)].Value = list[i].CustomerCode;
worksheet.Range[string.Format("F{0}", i + 2)].Value = list[i].Specialization;
worksheet.Range[string.Format("G{0}", i + 2)].Value = list[i].ProductName;
worksheet.Range[string.Format("H{0}", i + 2)].Value = list[i].Category;
}
}
catch (Exception e)
{
throw new Exception("Error while processing file", e);
}
finally
{
AppConfiguration.SaveAsName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, AppConfiguration.Temporaryfolder, AppConfiguration.Filename + DateTime.Now.ToString("yyyyMMdd-hhmmss"));
workbook.SaveAs(Filename: AppConfiguration.SaveAsName, FileFormat: XlFileFormat.xlOpenXMLWorkbook);
workbook.Close(SaveChanges: true);
xlsx.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(xlsx);
}
}
}
Here's how the IIS features being selected/installed on either my local machine IIS or the production server IIS:
以下是在本地计算机IIS或生产服务器IIS上选择/安装IIS功能的方法:
Web Management Tools -> IIS Management Console
World Wide Web Services
Application Development Features -> .NET Extensibility 3.5, .NET Extensibility 4.7, ASP.NET 3.5, ASP.NET 4.7, CGI, ISAPI Extensions, ISAPI Filters
Common HTTP Features -> Default Document, Direct Browsing, HTTP Errors, Static Content
Health and Diagnostics -> HTTP Logging
Performance Features -> Static Content Compression
Security -> Request Filtering
I also gave both the IIS_IUSERS/Administrators a full control permissions on the publish folder on which the IIS website is directed.
我还为IIS_IUSERS /管理员提供了IIS网站所针对的发布文件夹的完全控制权限。
I also configured the following Component Services for Excel
我还为Excel配置了以下组件服务
Component Services -> Computers -> My Computer -> DCOM Config -> Microsoft
Excel Application -> Properties
Authentication Level -> None
Security
Launch and Activation Permissions** -> Customize ->
Administrators/IIS_IUSERS have full permissions
Access Permissions -> Customize -> Administrators/IIS_IUSERS have full
permissions
Configure Permissions -> Customize -> Administrators/IIS_IUSERS have full
permissions
I am still having problems starting at opening the existing Excel template file template which blocking the rest of steps of the application from being executed as I have to open the file, fill it with the data, save it with a new name, then download the newly created file!.
我仍然在打开现有的Excel模板文件模板时遇到问题,该模板阻止执行应用程序的其余步骤,因为我必须打开文件,用数据填充它,用新名称保存,然后下载新创建的文件!
1 个解决方案
#1
0
The problem has been solved all what I need is to set the Component Services -> Computers -> My Computer -> DCOM Config -> Microsoft Excel Application -> Properties -> Identity to Interactive User and decorate the class that uses the COM component, ExcelExporter, with [ComVisible(true)] attribute
问题已解决所有我需要的是设置组件服务 - >计算机 - >我的电脑 - > DCOM配置 - > Microsoft Excel应用程序 - >属性 - >身份到交互式用户并装饰使用COM组件的类, ExcelExporter,具有[ComVisible(true)]属性
#1
0
The problem has been solved all what I need is to set the Component Services -> Computers -> My Computer -> DCOM Config -> Microsoft Excel Application -> Properties -> Identity to Interactive User and decorate the class that uses the COM component, ExcelExporter, with [ComVisible(true)] attribute
问题已解决所有我需要的是设置组件服务 - >计算机 - >我的电脑 - > DCOM配置 - > Microsoft Excel应用程序 - >属性 - >身份到交互式用户并装饰使用COM组件的类, ExcelExporter,具有[ComVisible(true)]属性