3

This is what I need to do, in a nutshell:

  1. Generate Excel spreadsheet files (programmatically).

  2. 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)

  3. 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

  • 1
    Is it possible to add a virtual directory to your web API that maps to network storage? – Dan Pichelman Mar 30 '16 at 16:30
  • 2
    Isn't this just a URL rewriting issue? So that you would need to add an exception to your url routing rules so that app_data is recognised as a path to a directory rather than a method? Since your app is c# I'll assume your also using IIS, for which I don't know the solution, but URL rewriting is where I would look. – Adam Copley Mar 30 '16 at 16:54
  • 2
    What is the app_data folder. That sounds like a windows folder. Is your web server running Windows? Downloading files from a LAMP stack never seemed so hard. – Buttle Butkus Mar 30 '16 at 19:02
  • 1
    The app_data folder is a private folder used in iis to protect a database used by the asp.net site. If you want to allow users to access the files directly it would be best to move them to another folder – Ewan Apr 2 '16 at 9:52
  • 1
    As @Ewan mentioned App_data is your problem, it's to provide file access to the Web App process not and deliberately -not- to external clients (it will block request access to this path). Just create any directory inside your web app for the excel files and it should work. Keep it simple ;-) – Joppe Apr 3 '16 at 13:57
2
+50

While simply uploading the files to a web server and allowing access to them is an option.

There are a couple of potential requirements which you should consider.

1: authentication of the user requesting the file.

2: accessing files by fields other than thier filename.

This kind of thing can steer you towards the approach you seem to be taking. Ie

1: Files are stored on a database or other private file repository.

2: An Api is exposed which handles an incomming request, locates the file, reads it from the store and returns the data.

Having the API in front of the file access gives you a handy place to add authentication, logging, returning lists of files etc.

When deciding what kind of repository to use to store the file, you should consider that you will probably also want to store some meta data along with the file; for which a db is a good option.

Storing the actual file itself in a db may be considered controversial. However, I believe that mssql at least offers some good options here.

You might also consider various cloud based storage options. Amazon S3 for example.

Having an API in front means you can abstract the storage method from the access method.

Also in your particular case the API could generate the file and save it if it has not previously been created. Removing your need for the win forms app

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.