This is what I need to do, in a nutshell:
Generate Excel spreadsheet files (programmatically).
Store these .xlsx files in a location where they can be accessed by users later. These files need to be "on the Internet/in the cloud", since most legitimate downloaders are not internal users and thus are not on our company network)
Make these files available to the user so that they can download them via a link on a web page.
This is what I've done so far:
Step 1, the programmatic generation of the .xlsx files, is complete. Currently, though, these are saved to a local folder on the machine on which the app runs (a Winforms app that allows the user to select the criteria that is needed to generate the reports using the correct data).
As for Steps 2 and 3, I've created a Web API (C#, ASP.NET) project that presents a page based on routing logic (the type of report, the company for whom the report was generated, as well as parameters for the dates the report convers). I am rendering the appropriate page, but the download link I'm generating via HTML is not working. The problem is apparently that the location in the web project (the App_Data folder) where I'm saving the generated .xlsx files is not accessible, or not being represented correctly.
I've been trying "everything" to get the download link to work, as can be seen here, on StackOverflow, but I wonder if I need to take a complete step back to consider trying something fundamentally different, since I feel like I'm banging my head against the wall with this approach.
As of now, I'm only testing very simple spreadsheet files that the Web API app dynamically generates (not the ones generated by the Winform app). I could either:
- Use the same code in the Web API app that I'm using in the Winforms app to generate these .xlsx files.
- Send the .xlsx files from the Winforms app to the Web API app - but I don't know how to do that. Where can I send them? Surely not directly to the Web API app's App_Data folder, but how/where?
The way I've got the REST methods working now is I have a GET method for the REST url that looks for the corresponding generated file; if it already exists (the file has previously been generated), it renders that to a page. If the file does not yet exist, the GET method calls the POST method, which generates the file; then, the file does exist, and that newly generated file is rendered on a new page:
[RoutePrefix("api/fillrate")]
public class FillRateController : ApiController
{
[Route("{unit}/{begindate}")]
// URL would be something like "http://localhost:52194/api/fillrate/Gramps/201509"
public HttpResponseMessage Get(string unit, string begindate)
{
string _unit = unit;
string _begindate = begindate;
string appDataFolder =
HttpContext.Current.Server.MapPath("~/App_Data/");
string fillRatefillRateByDistByCustFilename =
string.Format("fillRateByDistByCust_{0}_{1}.html", _unit, _begindate);
string fillRateFullPath = Path.Combine(appDataFolder,
fillRatefillRateByDistByCustFilename);
// If report has been generated, open it from its file (in AppData
folder);
// otherwise, generate it now; in this way, any date range for this
report can be requested
if (!File.Exists(fillRateFullPath))
{
Post(unit, begindate);
}
var HtmlToDisplay = File.ReadAllText(fillRateFullPath);
return new HttpResponseMessage()
{
Content = new StringContent(
HtmlToDisplay,
Encoding.UTF8,
"text/html"
)
};
}
[Route("{unit}/{begindate}")]
[HttpPost]
public void Post(string unit, string begindate)
{
string _unit = unit;
string _begindate = String.Format("{0}01",
HyphenizeYYYYMM(begindate));
string _enddate = GetEndOfMonthFor(_begindate);
string appDataFolder =
HttpContext.Current.Server.MapPath("~/App_Data/");
DataTable dtFillRateResults = null;
List<FillRateByDistributorByCustomer>
_fillRateByDistributorByCustomerList = null;
List<FillRateByDCByLocation> _fillRateByDCByLocationList = null;
List<string> _members = SQLDBHelper.GetMemberStringList();
try
{
foreach (String _memberId in _members)
{
dtFillRateResults =
SQLDBHelper.ExecuteSQLReturnDataTable(
. . .
);
. . .
// Create minimalistic spreadsheet file for now
var fillRateByDistByCustHtmlStr =
ConvertFillRateByDistributorByCustomer(_fillRateByDistributorByCustomerList,
unit, begindate);
string fillRatefillRateByDistByCustFilename =
string.Format("fillRateByDistByCust_{0}_{1}.html", _unit, begindate);
string fullFillRateByDistByCustPath =
Path.Combine(appDataFolder, fillRatefillRateByDistByCustFilename);
File.WriteAllText(fullFillRateByDistByCustPath,
fillRateByDistByCustHtmlStr);
}
catch (Exception ex)
{
. . .
}
}
These rendered pages have a download link where the .xlsx file can be downloaded from (if only it worked, rather than feigning ignorance of the App_Data folder).
So is this a sensible design/approach, or is something else indicated? Surely this is not a unique requirement that has never been solved before, so what is the "preferred method" or canonical approach to doing this?
UPDATE
What I plan on doing, and am in the middle of implementing, is this:
1) Store the files in a database (from the Winforms app)
2) In the ASP.NET Web API app, generate a set of links on a page, one for each saved report
3) Those links will each have as an href yet another REST address, which will respond by reading the file from the database and downloading it