Excel互操作性定界选项不起作用 [英] Excel interoperability delimiting option not working
问题描述
我在代码中使用excel互操作性,并且要将以下定界的excel数据导出到sql表中.
MAS | 201012211616360001 | Q-0263014640218 067 0423 |限量版| SS实心POLO || 6110202075 | 100%棉#### ||| Q086 | CASUAL TOPS CUT& SEWN S | CH |儿童| CUT& SEWN | CASUAL TOPS | 0423 | 067 | 0263014640218 | 59024316 |||||||||||| N || 1 | EA | 1 | 0 | 1 | 1 | CM | 0 | KG
代码如下
Hi,
I am using excel interoperability in my code and i want to export the below delimited excel data into sql table.
MAS|201012211616360001|Q-0263014640218 067 0423|LIMITED TOO|SS SOLID POLO||6110202075|100% COTTON####|||Q086|CASUAL TOPS CUT & SEWN S|CH|CHILDREN|CUT & SEWN|CASUAL TOPS|0423|067|0263014640218|59024316||||||||||N|||1|EA|1|0|1|1|CM|0|KG
The code is as follow
string path = txtFile.Text;
DirectoryInfo dir = new DirectoryInfo(path);
FileInfo[] FileList = dir.GetFiles("*.*", SearchOption.AllDirectories);
foreach (FileInfo FI in FileList)
{
using (SqlConnection Connection = ConnectionManager.GetConnection())
{
Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(FI.FullName, 0, true, 1, "","",false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "|", true, false, 0, true,false, false);
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
int rowIndex = 1;
int colIndex1 = 1;
int index = 0;
a: while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
{
此时,value2将显示整个行,其不基于"|"
进行定界
请帮助
At this point the value2 is showing the entire row, its not delimiting based on "|"
Please help
推荐答案
由于您使用自己的定界符,因此format参数应该为6而不是1.请参见下面的代码
Since you are using your own delimiter, format parameter should be 6 and not 1. Refer to the code below
ExcelObj.Workbooks.Open(FI.FullName, 0, true, 6, "","",false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "|", true, false, 0, true,false, false);
这篇关于Excel互操作性定界选项不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!