C＃ - 具有国际设置的Excel数字格式化问题 [英] C# - Excel Number Formatting Issue with International settings
问题描述
我正在尝试使用c＃3.5写入Excel 2003电子表格。但是，我无法使其在不同的国家/地区设置正常运行。国家设置是英文或德文。这两个设置具有不同的十进制和千位设置。一切都正常，除非用户在选项屏幕的国际设置中更改了十进制数和千位分隔符。任何人都可以帮助，因为我觉得我不能再看到木头的树木了，而且缺少一些明显的东西。
I am trying to write to an Excel 2003 spreadsheet using c# 3.5. However I am unable to get this to function correctly across different country settings. The country settings are either English or German. These two settings have different decimal and thousands settings. Everything works fine unless a user has changed the decimal and thousands separators in the International settings of the Options screen. Can anybody help as I feel I can no longer see the wood for the trees and am missing something obvious.
总结：
从访问数据库检索的数据。
由c＃应用程序读取并写入Excel电子表格。
Data retrieved from an access database. Read by c# application and written to an Excel spreadsheet.
Excel版本2003
机器是英文或德文。
在Excel中的国际设置选项中可能会更改小数和千位分隔符 - 这是发生问题的地方。
Excel Version 2003 Machines are either English or German. It is possible that decimal and thousands separators have been changed within the International Settings options in Excel -- This is where the problem occurs.
观察行为： br>
具有默认选项的英语设置 - >国际 - 如预期
德语设置与默认选项 - >国际 - 按预期
Observed behaviour:
English setup with default Options --> International - as expected
German setup with default Options --> International- as expected
具有小数分隔符的英文设置更改为，，千位分隔符设置为。和系统分隔符未选中 - >国际 - Excel数据不正确。查看星号行。
English setup with decimal separator changed to ",", thousands separator set to "." and System Separators unticked in Options --> International - Excel data incorrect. See asterisked rows.
数据Excel
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 111650
2567007.0833 25.670.070.833 **
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 111650
2567007.0833 25.670.070.833**
我已经附带了示例代码来演示此行为。如果有人能告诉我我在做错什么，那将是非常感激的。
I have attcahed sample code to demonstrate this behaviour. If anybody can show me what I am doing wrong, it would be much appreciated.
要运行此代码示例，只需创建一个新的Windows窗体应用程序，并将以下代码发布到Form1.cs中。您还需要添加对Microsoft.Office.Interop.Excel的引用。
To run this code sample, just create a new Windows forms application and post the code below into Form1.cs. You will also need to add a reference to Microsoft.Office.Interop.Excel.
非常感谢
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;
}
}
}
推荐答案
我没有运行你的代码，只是扫描它...
I haven't run your code, just scanned it...
第一个潜在的问题：你正在检查UseSystemSeparators，然后设置DecimalSeparaor和ThousandsSeparator。 / p>
First potential Problem: You are checking UseSystemSeparators and then setting the DecimalSeparaor and ThousandsSeparator.
if (app.UseSystemSeparators == false)
{
app.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
app.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
}
然后紧接着打开SystemSeparator，所以上面的代码不是做任何事情，因为你打开系统分隔符。
Then right after, you are turning on SystemSeparators, so the code above is not doing anything since you are turning the system seperators on.
app.UseSystemSeparators = true;
第二个潜在问题/建议：
设置单元格值时，将其设置为double值而不是字符串，让Excel格式化您的数字。
Second potential problem/suggestion: When setting the cell value, set it as a double value instead of string, let Excel format the number for you.
这篇关于C＃ - 具有国际设置的Excel数字格式化问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
