使用宏或 VBA 将科学记数法中的数字转换为 Excel 中的数字格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10074576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert a number in scientific notation to numeric format in Excel using a macro or VBA
提问by Egalitarian
MS Excelhas eaten my head. It is randomly converting numbers into scientific notation format. This causes a problem when I load the file saved in tab delimited format into SQL Server. I know I can provide a format file and do lot of fancy stuff. But let's say I can't.
MS Excel已经吃了我的头。它随机将数字转换为科学记数法格式。当我将以制表符分隔格式保存的文件加载到 SQL Server 中时，这会导致问题。我知道我可以提供格式文件并做很多花哨的事情。但让我们说我不能。
Is there a macro which loops over all the cells and if the number in a cell is in scientific notation format then it converts it to numeric format?
是否有一个循环遍历所有单元格的宏，如果单元格中的数字采用科学记数法格式，那么它将其转换为数字格式？
Say:
说：
Input: spaces signify different cells.
1.00E13 egalitarian
Output after macro:
宏后输出：
10000000000000 egalitarian
I am trying this in Excel 2007.
我正在 Excel 2007 中尝试此操作。
回答by Egalitarian
I wrote a simple C# program to resolve this issue. Hope it's of use.
我写了一个简单的 C# 程序来解决这个问题。希望它有用。
Input:
输入：
Input directory where files reside (assuming files are in .txt format).
输入文件所在的目录（假设文件为 .txt 格式）。
Output:
输出：
Output directory where converted files will be spit out.
将输出转换文件的输出目录。
Delimiter:
分隔符：
Column delimiter.
列分隔符。
The code
编码
using System;
using System.Text.RegularExpressions;
using System.IO;
using System.Text;
using System.Threading;
namespace ConvertToNumber
{
class Program
{
private static string ToLongString(double input)
{
string str = input.ToString().ToUpper();
// If string representation was collapsed from scientific notation, just return it:
if (!str.Contains("E"))
return str;
var positive = true;
if (input < 0)
{
positive = false;
}
string sep = Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator;
char decSeparator = sep.ToCharArray()[0];
string[] exponentParts = str.Split('E');
string[] decimalParts = exponentParts[0].Split(decSeparator);
// Fix missing decimal point:
if (decimalParts.Length == 1)
decimalParts = new string[] { exponentParts[0], "0" };
int exponentValue = int.Parse(exponentParts[1]);
string newNumber = decimalParts[0].Replace("-","").
Replace("+","") + decimalParts[1];
string result;
if (exponentValue > 0)
{
if(positive)
result =
newNumber +
GetZeros(exponentValue - decimalParts[1].Length);
else
result = "-"+
newNumber +
GetZeros(exponentValue - decimalParts[1].Length);
}
else // Negative exponent
{
if(positive)
result =
"0" +
decSeparator +
GetZeros(exponentValue + decimalParts[0].Replace("-", "").
Replace("+", "").Length) + newNumber;
else
result =
"-0" +
decSeparator +
GetZeros(exponentValue + decimalParts[0].Replace("-", "").
Replace("+", "").Length) + newNumber;
result = result.TrimEnd('0');
}
float temp = 0.00F;
if (float.TryParse(result, out temp))
{
return result;
}
throw new Exception();
}
private static string GetZeros(int zeroCount)
{
if (zeroCount < 0)
zeroCount = Math.Abs(zeroCount);
StringBuilder sb = new StringBuilder();
for (int i = 0; i < zeroCount; i++) sb.Append("0");
return sb.ToString();
}
static void Main(string[] args)
{
//Get Input Directory.
Console.WriteLine(@"Enter the Input Directory");
var readLine = Console.ReadLine();
if (readLine == null)
{
Console.WriteLine(@"Enter the input path properly.");
return;
}
var pathToInputDirectory = readLine.Trim();
//Get Output Directory.
Console.WriteLine(@"Enter the Output Directory");
readLine = Console.ReadLine();
if (readLine == null)
{
Console.WriteLine(@"Enter the output path properly.");
return;
}
var pathToOutputDirectory = readLine.Trim();
//Get Delimiter.
Console.WriteLine("Enter the delimiter;");
var columnDelimiter = (char) Console.Read();
//Loop over all files in the directory.
foreach (var inputFileName in Directory.GetFiles(pathToInputDirectory))
{
var outputFileWithouthNumbersInScientificNotation = string.Empty;
Console.WriteLine("Started operation on File : " + inputFileName);
if (File.Exists(inputFileName))
{
// Read the file
using (var file = new StreamReader(inputFileName))
{
string line;
while ((line = file.ReadLine()) != null)
{
String[] columns = line.Split(columnDelimiter);
var duplicateLine = string.Empty;
int lengthOfColumns = columns.Length;
int counter = 1;
foreach (var column in columns)
{
var columnDuplicate = column;
try
{
if (Regex.IsMatch(columnDuplicate.Trim(),
@"^[+-]?[0-9]+(\.[0-9]+)?[E]([+-]?[0-9]+)$",
RegexOptions.IgnoreCase))
{
Console.WriteLine("Regular expression matched for this :" + column);
columnDuplicate = ToLongString(Double.Parse
(column,
System.Globalization.NumberStyles.Float));
Console.WriteLine("Converted this no in scientific notation " +
"" + column + " to this number " +
columnDuplicate);
}
}
catch (Exception)
{
}
duplicateLine = duplicateLine + columnDuplicate;
if (counter != lengthOfColumns)
{
duplicateLine = duplicateLine + columnDelimiter.ToString();
}
counter++;
}
duplicateLine = duplicateLine + Environment.NewLine;
outputFileWithouthNumbersInScientificNotation = outputFileWithouthNumbersInScientificNotation + duplicateLine;
}
file.Close();
}
var outputFilePathWithoutNumbersInScientificNotation
= Path.Combine(pathToOutputDirectory, Path.GetFileName(inputFileName));
//Create the directory if it does not exist.
if (!Directory.Exists(pathToOutputDirectory))
Directory.CreateDirectory(pathToOutputDirectory);
using (var outputFile =
new StreamWriter(outputFilePathWithoutNumbersInScientificNotation))
{
outputFile.Write(outputFileWithouthNumbersInScientificNotation);
outputFile.Close();
}
Console.WriteLine("The transformed file is here :" +
outputFilePathWithoutNumbersInScientificNotation);
}
}
}
}
}
This works fairly well in case of huge files which we are unable to open in MS Excel.
在我们无法在 MS Excel 中打开的大文件的情况下，这非常有效。
回答by Chris
Thanks Peter. I updated your original work to: 1) take in an input file or path 2) only write out a processing statement after every 1000 lines read 3) write the transformed lines to the output file as they are processed so a potentially large string is not kept hanging around 4) added a readkey at the end so that console does not exit automatically while debuggging
谢谢彼得。我将您的原始工作更新为：1) 接收输入文件或路径 2) 仅在每读取 1000 行后写出处理语句 3) 将转换后的行在处理时写入输出文件，因此不会出现潜在的大字符串一直挂着 4) 在最后添加了一个 readkey 以便调试时控制台不会自动退出
using System;
using System.Text.RegularExpressions;
using System.IO;
using System.Text;
using System.Threading;
namespace ConvertScientificToLong
{
class Program
{
private static string ToLongString(double input)
{
string str = input.ToString().ToUpper();
// If string representation was collapsed from scientific notation, just return it:
if (!str.Contains("E"))
return str;
var positive = true;
if (input < 0)
{
positive = false;
}
string sep = Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator;
char decSeparator = sep.ToCharArray()[0];
string[] exponentParts = str.Split('E');
string[] decimalParts = exponentParts[0].Split(decSeparator);
// Fix missing decimal point:
if (decimalParts.Length == 1)
decimalParts = new string[] { exponentParts[0], "0" };
int exponentValue = int.Parse(exponentParts[1]);
string newNumber = decimalParts[0].Replace("-", "").
Replace("+", "") + decimalParts[1];
string result;
if (exponentValue > 0)
{
if (positive)
result =
newNumber +
GetZeros(exponentValue - decimalParts[1].Length);
else
result = "-" +
newNumber +
GetZeros(exponentValue - decimalParts[1].Length);
}
else // Negative exponent
{
if (positive)
result =
"0" +
decSeparator +
GetZeros(exponentValue + decimalParts[0].Replace("-", "").
Replace("+", "").Length) + newNumber;
else
result =
"-0" +
decSeparator +
GetZeros(exponentValue + decimalParts[0].Replace("-", "").
Replace("+", "").Length) + newNumber;
result = result.TrimEnd('0');
}
float temp = 0.00F;
if (float.TryParse(result, out temp))
{
return result;
}
throw new Exception();
}
private static string GetZeros(int zeroCount)
{
if (zeroCount < 0)
zeroCount = Math.Abs(zeroCount);
StringBuilder sb = new StringBuilder();
for (int i = 0; i < zeroCount; i++) sb.Append("0");
return sb.ToString();
}
static void Main(string[] args)
{
//Get Input Directory.
Console.WriteLine(@"Enter the Input Directory or File Path");
var readLine = Console.ReadLine();
if (readLine == null)
{
Console.WriteLine(@"Enter the input path properly.");
return;
}
var pathToInputDirectory = readLine.Trim();
//Get Output Directory.
Console.WriteLine(@"Enter the Output Directory");
readLine = Console.ReadLine();
if (readLine == null)
{
Console.WriteLine(@"Enter the output path properly.");
return;
}
var pathToOutputDirectory = readLine.Trim();
//Get Delimiter.
Console.WriteLine("Enter the delimiter;");
var columnDelimiter = (char)Console.Read();
string[] inputFiles = null;
if (File.Exists(pathToInputDirectory))
{
inputFiles = new String[]{pathToInputDirectory};
}
else
{
inputFiles = Directory.GetFiles(pathToInputDirectory);
}
//Loop over all files in the directory.
foreach (var inputFileName in inputFiles)
{
var outputFileWithouthNumbersInScientificNotation = string.Empty;
Console.WriteLine("Started operation on File : " + inputFileName);
if (File.Exists(inputFileName))
{
string outputFilePathWithoutNumbersInScientificNotation
= Path.Combine(pathToOutputDirectory, Path.GetFileName(inputFileName));
//Create the directory if it does not exist.
if (!Directory.Exists(pathToOutputDirectory))
Directory.CreateDirectory(pathToOutputDirectory);
using (var outputFile =
new StreamWriter(outputFilePathWithoutNumbersInScientificNotation))
{
// Read the file
using (StreamReader file = new StreamReader(inputFileName))
{
string line;
int lineCount = 0;
while ((line = file.ReadLine()) != null)
{
String[] columns = line.Split(columnDelimiter);
var duplicateLine = string.Empty;
int lengthOfColumns = columns.Length;
int counter = 1;
foreach (var column in columns)
{
var columnDuplicate = column;
try
{
if (Regex.IsMatch(columnDuplicate.Trim(),
@"^[+-]?[0-9]+(\.[0-9]+)?[E]([+-]?[0-9]+)$",
RegexOptions.IgnoreCase))
{
//Console.WriteLine("Regular expression matched for this :" + column);
columnDuplicate = ToLongString(Double.Parse
(column,
System.Globalization.NumberStyles.Float));
//Console.WriteLine("Converted this no in scientific notation " +
// "" + column + " to this number " +
// columnDuplicate);
if (lineCount % 1000 == 0)
{
Console.WriteLine(string.Format("processed {0} lines. still going....", lineCount));
}
}
}
catch (Exception)
{
}
duplicateLine = duplicateLine + columnDuplicate;
if (counter != lengthOfColumns)
{
duplicateLine = duplicateLine + columnDelimiter.ToString();
}
counter++;
}
outputFile.WriteLine(duplicateLine);
lineCount++;
}
}
}
Console.WriteLine("The transformed file is here :" +
outputFilePathWithoutNumbersInScientificNotation);
Console.WriteLine(@"Hit any key to exit");
Console.ReadKey();
}
}
}
}
}
回答by Sherrie Taylor
An easier way would be to save it as a .csv file. Then, instead of just opening the file - you go to the Data tab and select "from text" so that you can get the dialogue box. This way you can identify that column with the scientific notation as text to wipe out that format. Import the file and then you can reformat it to number or whatever you want.
更简单的方法是将其另存为 .csv 文件。然后，而不是仅仅打开文件 - 您转到“数据”选项卡并选择“来自文本”，这样您就可以获得对话框。通过这种方式，您可以使用科学记数法将该列标识为文本以消除该格式。导入文件，然后您可以将其重新格式化为数字或任何您想要的格式。
