Euro symbol not being displayed properly in csv files


#1

Hi,
I am applying custom formatting “€#,##0.00” . But when the csv file is opened the euro symbol is replaced by €. I even tried specifying the encoding but still, it is not working

        string FileName = @"C:\Users\xyz\Desktop\input.xlsx";

        string destFileName = @"C:\Users\xyz\Desktop\output.csv";

        List<string> LstReportObject = new List<string>();
        LstReportObject.Add("Date");
        LstReportObject.Add("Spend in USD");



        //Load your source workbook

        Workbook workbook1 = new Workbook(FileName);
        
        MemoryStream msFormatting = workbook1.SaveToStream();
        Workbook workbook = new Workbook(msFormatting);
        
        Worksheet ws = workbook.Worksheets[0];
        //  ws.Cells[0,0].Value = "SEP=;" ;

        //0-byte array
        TxtSaveOptions opts = new TxtSaveOptions();
        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;

                }



            }

        }
        //Text save options. You can use any type of separator



        //opts.Encoding = Encoding.GetEncoding("ISO - 8859 - 1");
        //Copy each worksheet data in text format inside workbook data array

        for (int idx = 0; idx < workbook.Worksheets.Count; idx++)

        {

            //Save the active worksheet into text format

            MemoryStream ms = new MemoryStream();

            workbook.Worksheets.ActiveSheetIndex = idx;

            workbook.Save(ms, opts);



            //Save the worksheet data into sheet data array

            ms.Position = 0;

            byte[] sheetData = ms.ToArray();

            string input = "SEP=     ";
            byte[] prependArray = Encoding.ASCII.GetBytes(input);

            //Combine this worksheet data into workbook data arra

            byte[] combinedArray = new byte[workbookData.Length + sheetData.Length + prependArray.Length];

            Array.Copy(prependArray, 0, combinedArray, 0, prependArray.Length);

            Array.Copy(workbookData, 0, combinedArray, prependArray.Length, workbookData.Length);

            Array.Copy(sheetData, 0, combinedArray, prependArray.Length + workbookData.Length, sheetData.Length);



            workbookData = combinedArray;

        }



        //Save entire workbook data into file

        File.WriteAllBytes(destFileName, workbookData);

    }
}

}

PFA the input excel files and output csv fileexcelforaspose.zip (100.5 KB)


#2

@an1,

Thanks for the sample code and template file.

I did evaluate your issue a bit. The issue is due to the fact that you are combining workbook data via array copy at the end. Even if you could open your output file into notepad, you will clearly see those euro symbols are attached correctly with data/values. In short, the issue is not linked with Aspose.Cells APIs. If I skip your last part and simply/directly convert the file to CSV file format after specifying the numbers formatting and other attributes, it works fine, see the following sample code which renders the CSV file fine (when opened into MS Excel):
e.g
sample code:

string FileName = "E:\\test2\\excelforaspose\\input.xlsx";

            List<string> LstReportObject = new List<string>();
            LstReportObject.Add("Date");
            LstReportObject.Add("Spend in USD");



            //Load your source workbook

            Workbook workbook1 = new Workbook(FileName);

            MemoryStream msFormatting = new MemoryStream();
            workbook1.Save(msFormatting, SaveFormat.Xlsx);
            Workbook workbook = new Workbook(msFormatting);

            Worksheet ws = workbook.Worksheets[0];
            //  ws.Cells[0,0].Value = "SEP=;" ;

            //0-byte array
            TxtSaveOptions opts = new TxtSaveOptions();
            opts.Encoding = Encoding.Unicode;
            opts.QuoteType = TxtValueQuoteType.Minimum;

            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("E:\\test2\\excelforaspose\\out1.csv", opts);

#3

Thank you for looking into the issue promptly. I did try removing the last part of my code and saving the workbook directly but still the issue persists , the euro symbols are still not appearing properly


#4

@an1,

I tried my sample code (above) to produce out1.csv file using v18.11.x (latest version/fix - please try it if you are not using it), it works fine and output file is fine tuned (attached).
files1.zip (23.5 KB)


#5

Thank You it works now :slight_smile:


#6

@an1,

Good to know that your issue is sorted out. 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.