C # - Excel number formatting error with international settings

I am trying to write to an Excel 2003 spreadsheet using C # 3.5. However, I can't seem to get this to function correctly in different country settings. Country settings - English or German. These two settings have different decimals and thousands of settings. Everything works fine unless the user has changed the decimal and thousands of separators in the international settings of the Options screen. Can anyone help as I feel like I can no longer see the forest for trees and I am missing something obvious.

Summary:

Data retrieved from the access database. Reading with a C # app and writing to an Excel spreadsheet.

Excel 2003 version The machines are either English or German. It is possible that the decimal and thousands separators have been changed in the International Settings in Excel. This is where the problem arises.

Observed Behavior:
Setting in English with default settings → International - as expected
Immediate default setting → International - as expected

Russian setting with decimal separator changed to ",", thousands separator set to "." and system separators are not specified in options -> International - Excel data is incorrect. See the asterisk.

Excel data

3706888.0300 3706888.03
2587033.8000 2587033.8
2081071.1800 2081071.18
9030160.3333 90.301.603.333 **
42470.9842 424.709.842 **
4465546.2800 4465546.28
1436037.3200 1436037.32
111650,0000 11163350
256700

I have a sample code example to demonstrate this behavior. If anyone can show me what I am doing wrong it would be greatly appreciated.

To run this sample code, simply create a new Windows Forms Application and submit the code below to Form1.cs. You will also need to add a link to Microsoft.Office.Interop.Excel.

Many thanks

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Threading; 
using System.Globalization;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public void ExportDTToExcel()
        {

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = true;
            Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet ws = (Worksheet)wb.ActiveSheet;


            string culture = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();//"en-GB";
            CultureInfo ci = new CultureInfo(culture);

            string excelGroupSeparator = app.ThousandsSeparator.ToString();
            string excelDecimalSeparator = app.DecimalSeparator.ToString();
            bool systemseparators = app.UseSystemSeparators  ;
            if (app.UseSystemSeparators == false)
            {
                app.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
                app.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
                //ci.NumberFormat.NumberDecimalSeparator = app.DecimalSeparator;
                //ci.NumberFormat.NumberGroupSeparator = app.ThousandsSeparator;
            }
            //app.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
            //app.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;


            app.UseSystemSeparators = true;


            // Content.   

            try
            {
                SetCellValue("3706888.0300", ws, 0, 0, ci);
                SetCellValue("2587033.8000", ws, 1, 0, ci);
                SetCellValue("2081071.1800", ws, 2, 0, ci);
                SetCellValue("9030160.3333", ws, 3, 0, ci);
                SetCellValue("42470.9842", ws, 4, 0, ci);
                SetCellValue("4465546.2800", ws, 5, 0, ci);
                SetCellValue("1436037.3200", ws, 6, 0, ci);
                SetCellValue("111650.0000", ws, 7, 0, ci);
                SetCellValue("2567007.0833", ws, 8, 0, ci);

            }
            catch (Exception e)
            {


                    MessageBox.Show(e.Message);

            }

            //wb.SaveAs(Filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //wb.Close(false, Type.Missing, false);
            app.DecimalSeparator = excelDecimalSeparator;
            app.ThousandsSeparator = excelGroupSeparator;
            app.UseSystemSeparators = systemseparators;
            //app.Quit();
            Marshal.ReleaseComObject(app);
            Marshal.ReleaseComObject(wb);
            Marshal.ReleaseComObject(ws);
            app = null;
            wb = null;
            ws = null;


        }

        private static void SetCellValue(string data, Worksheet ws,int row, int col, CultureInfo ci)
        {


                double val;
                try
                {
                    val = Convert.ToDouble(data);
                    Console.WriteLine(val);

                }
                catch (Exception e)
                {

                    //Util.Log("Null Value ignored.", LogType.ERROR);
                    return;
                }

                try
                {
                    string s = val.ToString();
                    ws.Cells[row + 2 , col + 1] = s;

                    //Util.Log("S:" + s, LogType.ERROR);
                }
                catch
                {
                    //Util.Log("Null Value ignored.", LogType.ERROR);
                }
            }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            ExportDTToExcel();
            this.Cursor = Cursors.Default;
        }
        }
    }

      

+2


source to share


4 answers


I didn't run your code, just looked at it ...

First potential problem: you check UseSystemSeparators and then set DecimalSeparaor and ThousandsSeparator.

        if (app.UseSystemSeparators == false)
        {
            app.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
            app.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
        }

      



Then immediately you include SystemSeparators, so the code above does nothing as you include system separators.

        app.UseSystemSeparators = true;

      

Second potential problem / suggestion: When you set the value of a cell, set it as a double value instead of a string, let Excel format the number for you.

+4


source


This KB article, and several other KB articles it links to, describe some of the localization issues that can be affected when automating Excel from .NET.



This might help explain your problem.

+2


source


Thanks to Chris and Joe, I finally got the code to work as desired.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Threading; 
using System.Globalization;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public void ExportDTToExcel()
        {

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = true;

            string culture = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();//"en-GB";
            CultureInfo ci = new CultureInfo(culture);

            bool systemseparators = app.UseSystemSeparators  ;
            if (app.UseSystemSeparators == false)
            {

                app.UseSystemSeparators = true;

            }

            // Content.   
            Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet ws = (Worksheet)wb.ActiveSheet;            
            try
            {
                SetCellValue("3706888.0300", ws, 0, 0, ci);
                SetCellValue("2587033.8000", ws, 1, 0, ci);
                SetCellValue("2081071.1800", ws, 2, 0, ci);
                SetCellValue("9030160.3333", ws, 3, 0, ci);
                SetCellValue("42470.9842", ws, 4, 0, ci);
                SetCellValue("4465546.2800", ws, 5, 0, ci);
                SetCellValue("1436037.3200", ws, 6, 0, ci);
                SetCellValue("111650.0000", ws, 7, 0, ci);
                SetCellValue("2567007.0833", ws, 8, 0, ci);

            }
            catch (Exception e)
            {


                    MessageBox.Show(e.Message);

            }

            app.UseSystemSeparators = systemseparators;
            Marshal.ReleaseComObject(app);
            Marshal.ReleaseComObject(wb);
            Marshal.ReleaseComObject(ws);
            app = null;
            wb = null;
            ws = null;
        }

        private static void SetCellValue(string data, Worksheet ws,int row, int col, CultureInfo ci)
        {
                double val;
                try
                {
                    val = Convert.ToDouble(data);
                    Console.WriteLine(val);
                }
                catch (Exception e)
                {

                    //Util.Log("Null Value ignored.", LogType.ERROR);
                    return;
                }

                try
                {
                    string s = val.ToString();
                    ws.Cells[row + 2 , col + 1] = s;

                    //Util.Log("S:" + s, LogType.ERROR);
                }
                catch
                {
                    //Util.Log("Null Value ignored.", LogType.ERROR);
                }
            }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            ExportDTToExcel();
            this.Cursor = Cursors.Default;
        }


    }
}

      

+2


source


Excel used over COM has several limitations that are most important to comply with:

  • Use culture en-US ( LCID error )
  • Call all methods from the same thread (set the correct culture "en-US")

Then you will get rid of most of the sporadic glitches regarding Excel automation and localization.

+2


source







All Articles