如何通过Tab Interop Excel导入CSV分割 [英] How to Import CSV split by Tab Interop Excel

问题描述

您好，

我通过C＃将CSV文件导入Excel时出现问题。

我有2种不同类型的CSV文件，第一种一个用逗号分隔，导入工作完全正常，第二种类型由tabstops分隔，不起作用。我找不到任何错误，所以我希望你能找到解决方案：



导入逗号文件的代码：

Hello,
I've got a Problem with importing a CSV file to Excel via C#.
I've got 2 different types of CSV files, the first one is delimitered by commas and the import works perfectly fine, the second type is delimitered by tabstops and is not working. I can't find any mistake so I hope you are going to find a solution:

Code for Importing comma file:

// Create new Excel-Application and initialize the Worksheet and the Range //
                Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();

                Microsoft.Office.Interop.Excel.Worksheet xSheet;
                Microsoft.Office.Interop.Excel.Range xRange;

                // Import CSV and split at Comma //
                xl.Workbooks.OpenText(tbDS.Text,
                   false, 3,
                   Excel.XlTextParsingType.xlDelimited,
                   Excel.XlTextQualifier.xlTextQualifierNone,
                   Type.Missing, Type.Missing, Type.Missing, true,
                   Type.Missing, Type.Missing, Type.Missing,
                   Type.Missing, Type.Missing, Type.Missing,
                   Type.Missing, Type.Missing, Type.Missing);

导入选项卡文件代码：

Code for Importing tab file:

string file = Path.GetDirectoryName(tbDS.Text) + "\\Measurements\\" + report + ".csv";
            if (File.Exists(file))
            {
              // Create new Excel-Application and initialize the Worksheet and the Range //
              Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();

              Microsoft.Office.Interop.Excel.Worksheet xSheet;
              Microsoft.Office.Interop.Excel.Range xRange;

              // Import CSV and split at Tab-Stop //
              xl.Workbooks.OpenText(file,
                 false, 3,
                 Excel.XlTextParsingType.xlDelimited,
                 Excel.XlTextQualifier.xlTextQualifierNone,
                 Type.Missing, true, Type.Missing, Type.Missing, 
                 Type.Missing, Type.Missing, Type.Missing,
                 Type.Missing, Type.Missing, Type.Missing, 
                 Type.Missing, Type.Missing, Type.Missing);

第一个将CSV完全拆分，第二个不拆分CSV文件



样本CSV：

逗号：

First one splits the CSV perfectly fine, second one does not split the CSV File

Sample CSV:
comma:

Testplan, Load, RFV1, 1HAR1, RFV2, 1HAR2, RR, LRU, LRL, TUZ(pp), TUZ(har), Total grade, date + time,
434, 6326, 59, 19, 0, 0, 0.249, 1.108, 0.66, 17.3, 0, 1,  7. 05. 2014 08:54:09,
434, 6334, 129, 95, 0, 0, 0.352, 1.449, 0.931, 27.8, 0, 4,  7. 05. 2014 08:54:25,
434, 6301, 41, 23, 0, 0, 0.129, 1.032, 0.631, 14.5, 0, 1,  7. 05. 2014 08:54:40,
434, 6317, 76, 34, 0, 0, 0.248, 1.011, 0.774, 18, 0, 1,  7. 05. 2014 08:54:57,

tab：

tab:

RKS1	RKS2	LKS1	LKS2	HS	SSO	SSU
6	0	2	0	-47	0	-44
6	0	2	0	-49	1	-48
6	0	2	0	-50	2	-54
6	0	2	0	-51	3	-60
6	0	2	0	-52	5	-66
6	0	1	0	-54	5	-70

先谢谢你

Thank you in Advance

推荐答案

我建​​议使用 ADO.NET [ ^ ]而不是Excel OpenText 方法。



请参考这些文章：

从.NET应用程序访问Microsoft Office数据 [< a href =http://msdn.microsoft.com/en-us/library/ms971514.aspxtarget =_ blanktitle =New Window> ^ ]

如何使用Visual C＃2005或Visual C＃.NET将数据传输到Excel工作簿 [ ^ ]

ADO.NET托管提供程序和数据集...... [ ^ ]

读取文本文件（txt，csv，log，tab，fixed length） [ ^ ]

关于文本文件的大量ADO [ ^ ] - 将其视为额外的信息。



我怀疑你的系统中的逗号有特殊含义（例如小数分隔符）。这就是 OpenText 方法失败的原因。请查看我过去的答案，了解如何解决这个问题：阅读文本文件特定列 [< a href =http://www.codeproject.com/Answers/555866/ReadplusTextplusFileplusSpecificplusColumns#answer2target =_ blanktitle =New Window> ^ ] - VB.NET，但这种语言类似到C＃。

I'd suggest to use ADO.NET[^] instead od Excel OpenText method.

Please, refer these articles:
Accessing Microsoft Office Data from .NET Applications[^]
How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET[^]
ADO.NET Managed Providers and DataSet...[^]
Read Text File (txt, csv, log, tab, fixed length)[^]
Much ADO About Text Files[^] - treat it as additional information.

I suspect that comma in your system has special meaning (for example decimal separator). That's why OpenText method failed. Please, see my past answer to find out how to go around it: Read Text File Specific Columns[^] - VB.NET, but this language is similar to C#.

这篇关于如何通过Tab Interop Excel导入CSV分割的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！