Setting culture for CSV files


#1

Hi,

I am using Aspose.Cells for creating CSV files. Is there any way to set the culture for the CSV file as US so that when the user opens the CSV file in excel he sees the format according to US locale irrespective of his locale settings. PFA my code snippet for doing the same but I am not able to achieve the proper result

Blockquote

        string FileName = @"C:\Users\abc\Downloads\input.xlsx";
        string destFileName = @"C:\Users\abc\Downloads\outputaspose5.csv";
        List<string> LstReportObject = new List<string>();
        LstReportObject.Add("Date");
        LstReportObject.Add("Spend in USD");
        // //Load your source workbook
        var culture = new System.Globalization.CultureInfo("en-US");
        Workbook workbook1 = new Workbook(FileName);

       MemoryStream msFormatting = new MemoryStream();
       workbook1.Save(msFormatting, SaveFormat.Xlsx);
       TxtLoadOptions olts = new TxtLoadOptions();
        // olts.LanguageCode = CountryCode.Default;
        //olts.Region = CountryCode.Default;
        olts.CultureInfo = culture;
        Workbook workbook = new Workbook(msFormatting,olts);

        Worksheet ws = workbook.Worksheets[0];
        //  //ws.Cells[0,0].Value = "SEP=    ";
        // MemoryStream msNew = new MemoryStream();
        // //0-byte array
        
        TxtSaveOptions opts = new TxtSaveOptions(SaveFormat.CSV);
        opts.Encoding = Encoding.Unicode;
         opts.QuoteType = TxtValueQuoteType.Always;

        opts.Separator = ';';
        
        byte[] workbookData = new byte[0];
        string[] columnName = new string[2];

        string[] columnHeader = new string[2];

        for (int i = 0; i < 2; i++)
        {

            columnName[i] = CellsHelper.ColumnIndexToName(i) + 4;

            Cell cell = ws.Cells[columnName[i]];

            columnHeader[i] = cell.Value.ToString();



        }

        foreach (var item in LstReportObject)
        {

            for (int i = 0; i < columnHeader.Length; i++)
            {

                if (columnHeader[i] == item)
                {



                    if (item == "Spend in USD")
                    {

                        StyleFlag NumberFlag = new StyleFlag();

                        NumberFlag.NumberFormat = true;

                        Style NumberStyle = workbook.CreateStyle();

                        NumberStyle.Custom = "€#,##0.00";

                        ws.Cells.Columns[i].ApplyStyle(NumberStyle, NumberFlag);

                    }
                    break;
                }
                }
        }

        workbook.Save(destFileName,opts);
    }

}

}

excelforaspose.zip (60.2 KB)


#2

@an1,

Thanks for your query.

Currently there is no such option available in Aspose.Cells which can be used to create CSV file along with a culture. You may please try creating such CSV using Excel and share the steps with us to perform this task. We will analyze the output CSV file created by Excel and provide our feedback.


#3

I found a solution to the above problem

Blockquote
Workbook workbook = new Workbook(ms);
workbook.Settings.Region = CountryCode.USA;

works perfectly fine


#4

@an1,

Good to know that your issue is sorted out by setting the region. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.