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:
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.
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:
$("#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:
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:
*
Web Management Tools -> IIS Management Console
*
World Wide Web Services
1- Application Development Features -> .NET Extensibility 3.5, .NET Extensibility 4.7, ASP.NET 3.5, ASP.NET 4.7, CGI, ISAPI Extensions, ISAPI Filters
2- Common HTTP Features -> Default Document, Direct Browsing, HTTP Errors, Static Content
3- Health and Diagnostics -> HTTP Logging
4- Performance Features -> Static Content Compression
5- Security -> Request Filtering
I also gave both the
IIS_IUSERS and the
Administrators a Full Control permissions on the publish folder on which the IIS website is directed.
I also configured the following Component Services for Excel
Component Services -> Computers -> My Computer -> DCOM Config -> Microsoft Excel Application -> Properties
Authentication Level -> None
Security
1- Launch and Activation Permissions -> Customize -> Administrators/IIS_IUSERS have full permissions
2- Access Permissions -> Customize -> Administrators/IIS_IUSERS have full permissions
3- 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!.
What I have tried:
Searching the internet and applying some solution like configure the DCOM component service for the Excel Application, giving a full security permissions for the website publish folder for the IIS_IUSERS/Administrators