Search code examples
spreadsheetgear

I need a better way to approximate the List Separator in an ASP web app

We host our web forms app on a Windows server.

We utilize SpreadSheetGear to write formulas to cells and allow the user to download the workbook.

We determine the list separator by the Server culture (this is a problem).

We service customers in USA and Latin America.

All this adds up to us providing the wrong List Separator for Latin America machines. The server culture doesn't match the user culture, so SpreadSheetGear throws an Invalid Formula error when we try to .Formula a cell with an improper separator.

I can't find a way to get the separator from the local machine. Accept-Language on the HTTP header passes me multiple languages with weighted values, but this is still guesswork.

My solution was to create a workbook and try to write to a cell on that new workbook. If it fails, the separator is a comma, otherwise, it's a semi-colon.

The concern with this logic is that we call ExcelValueSeparator often, and each time we do, we'd be creating and disposing of a new workbook, worksheet, etc. We're concerned about speed.

I've attached a screenshot of my new logic.

Does anyone here have a better way to accomplish this without guesswork? Are our speed concerns here warranted?

enter image description here

Solution

  • If you have full control of the workbook-creating and formula-creating process on your server (i.e., your end-users from other parts of the globe don't provide potentially culture-sensitive function or formula strings), and if your end-user's are receiving a downloadable Excel file such as XLSX, XLSM or XLS from their browser, then to me it sounds like you can simplify your server-side approach by ensuring you always create the workbook using an "en-US" CultureInfo object and adhere to en-US conventions (such as using a comma as the list separator) when constructing your formulas.

    Reason being is that these CultureInfo-specific aspects of a workbook such as ListSeparator are only relevant at runtime--when the workbook file is actually opened in SpreadsheetGear, or Excel for that matter. Once a workbook is stored on disk (or stream form when sending to an end-user's browser), it's essentially stored in that file in a "locale-neutral" manner. It's not until the file is subsequently opened under a given regional environment (i.e., in Excel they look at the OS regional settings; in SpreadsheetGear we look at the CultureInfo you pass in) that locale-sensitive things like the list separator character (and other things like date formats, currency character, etc.) are dynamically applied to the workbook.

    So in the case of your Latin American customers, the Excel workbook which was "en-US" on the server during runtime, will take on whatever locale conventions they might be running on their machines once they open it in Excel.

    To ensure you use "en-US" when working on a file in SpreadsheetGear (and provide some assurance that you can always use a comma for the ListSeparator), you can either explicitly pass that CultureInfo in, or just don't pass anything in, as that's what is used by default. Example:

    // These are functionally the same.
IWorkbook workbook = Factory.GetWorkbook();
IWorkbook workbook = Factory.GetWorkbook(System.Globalization.CultureInfo.GetCultureInfo("en-US"));