我为了实现批量导入多个txt文本到EXCEL的多张Sheet中,写了一下代码:
for (int i=2;i<=FileName.Length+1;i++)
{ newSheet = newExcel.ActiveSheet;
newSheet.QueryTables.Add("TEXT;"+FilePath[i-2],newSheet.get_Range("$A$1"),nothing);
//newSheet.QueryTables[i-1].Name = FileName[i-2];
newSheet.QueryTables[i-1].FieldNames = true;
newSheet.QueryTables[i-1].RowNumbers = false;
newSheet.QueryTables[i-1].FillAdjacentFormulas = false;
newSheet.QueryTables[i-1].PreserveFormatting = true;
newSheet.QueryTables[i-1].RefreshOnFileOpen = false;
newSheet.QueryTables[i-1].RefreshStyle = MSExcel.XlCellInsertionMode.xlInsertDeleteCells;
newSheet.QueryTables[i-1].SavePassword = false;
newSheet.QueryTables[i-1].SaveData = true;
newSheet.QueryTables[i-1].AdjustColumnWidth = true;
newSheet.QueryTables[i-1].RefreshPeriod = 0;
newSheet.QueryTables[i-1].TextFilePromptOnRefresh = false;
newSheet.QueryTables[i-1].TextFilePlatform = 936;
newSheet.QueryTables[i-1].TextFileStartRow = 1;
newSheet.QueryTables[i-1].TextFileParseType = MSExcel.XlTextParsingType.xlDelimited;
newSheet.QueryTables[i-1].TextFileTextQualifier = MSExcel.XlTextQualifier.xlTextQualifierDoubleQuote;
newSheet.QueryTables[i-1].TextFileConsecutiveDelimiter = true;
newSheet.QueryTables[i-1].TextFileTabDelimiter = false;
newSheet.QueryTables[i-1].TextFileSemicolonDelimiter = false;
newSheet.QueryTables[i-1].TextFileCommaDelimiter = false;
newSheet.QueryTables[i-1].TextFileSpaceDelimiter = false;
newSheet.QueryTables[i-1].TextFileOtherDelimiter = "|";
newSheet.QueryTables[i-1].TextFileColumnDataTypes = ColumnDataType;
newSheet.QueryTables[i-1].TextFileTrailingMinusNumbers = true;
newSheet.QueryTables[i-1].Refresh(false);
newSheet.QueryTables[i - 1].Name = FileName[i - 2]; string tmp = newSheet.QueryTables[i - 1].Name;//临时变量,调试用
}
这是其中的一段代码,程序可以运行,只是一直在同一张Sheet里面重复导入第一个文本,并没有像预期那样将多个文本导入多个Sheet。调试中,我设置的tmp变量值一直在变,说明系统有读到其他文件。请各路高人指点迷津~先谢谢啦~!
——————————————————————————————
个人感觉“newSheet = newExcel.ActiveSheet;"可能有点问题,但始终不确定,也不知道怎么改,求助!!!C#,Excel
for (int i=2;i<=FileName.Length+1;i++)
{ newSheet = newExcel.ActiveSheet;
newSheet.QueryTables.Add("TEXT;"+FilePath[i-2],newSheet.get_Range("$A$1"),nothing);
//newSheet.QueryTables[i-1].Name = FileName[i-2];
newSheet.QueryTables[i-1].FieldNames = true;
newSheet.QueryTables[i-1].RowNumbers = false;
newSheet.QueryTables[i-1].FillAdjacentFormulas = false;
newSheet.QueryTables[i-1].PreserveFormatting = true;
newSheet.QueryTables[i-1].RefreshOnFileOpen = false;
newSheet.QueryTables[i-1].RefreshStyle = MSExcel.XlCellInsertionMode.xlInsertDeleteCells;
newSheet.QueryTables[i-1].SavePassword = false;
newSheet.QueryTables[i-1].SaveData = true;
newSheet.QueryTables[i-1].AdjustColumnWidth = true;
newSheet.QueryTables[i-1].RefreshPeriod = 0;
newSheet.QueryTables[i-1].TextFilePromptOnRefresh = false;
newSheet.QueryTables[i-1].TextFilePlatform = 936;
newSheet.QueryTables[i-1].TextFileStartRow = 1;
newSheet.QueryTables[i-1].TextFileParseType = MSExcel.XlTextParsingType.xlDelimited;
newSheet.QueryTables[i-1].TextFileTextQualifier = MSExcel.XlTextQualifier.xlTextQualifierDoubleQuote;
newSheet.QueryTables[i-1].TextFileConsecutiveDelimiter = true;
newSheet.QueryTables[i-1].TextFileTabDelimiter = false;
newSheet.QueryTables[i-1].TextFileSemicolonDelimiter = false;
newSheet.QueryTables[i-1].TextFileCommaDelimiter = false;
newSheet.QueryTables[i-1].TextFileSpaceDelimiter = false;
newSheet.QueryTables[i-1].TextFileOtherDelimiter = "|";
newSheet.QueryTables[i-1].TextFileColumnDataTypes = ColumnDataType;
newSheet.QueryTables[i-1].TextFileTrailingMinusNumbers = true;
newSheet.QueryTables[i-1].Refresh(false);
newSheet.QueryTables[i - 1].Name = FileName[i - 2]; string tmp = newSheet.QueryTables[i - 1].Name;//临时变量,调试用
}
这是其中的一段代码,程序可以运行,只是一直在同一张Sheet里面重复导入第一个文本,并没有像预期那样将多个文本导入多个Sheet。调试中,我设置的tmp变量值一直在变,说明系统有读到其他文件。请各路高人指点迷津~先谢谢啦~!
——————————————————————————————
个人感觉“newSheet = newExcel.ActiveSheet;"可能有点问题,但始终不确定,也不知道怎么改,求助!!!C#,Excel
newSheet = newBook.Worksheets.Add(nothing,newExcel.Worksheets[i-1],1,nothing);
不过编译到第2个循环时,报错:“无效索引。 (异常来自 HRESULT:0x8002000B (DISP_E_BADINDEX))"
这种错误我上网查了下,原因如下:
1.参数字段传值与报表参数不对应(缺少必填项、参数传递过多、参数不对应等)2.dataset模板更新不及时,导致取值与模板不一致3.公式字段报错
不过很奇怪,这个错误在第2重循环时出现。
((Excel.Worksheet)newSheet).Activate();
newSheet = newExcel.ActiveSheet;
for (int i=2;i<=FileName.Length+1;i++)
{ //newSheet = (MSExcel.Worksheet)newExcel.Worksheets;
//newBook = newExcel.ActiveWorkbook;
//newSheet = newBook.Worksheets.Add(nothing,newExcel.Worksheets[i-1],1,nothing);
((MSExcel.Worksheet)newSheet).Activate();
newExcel.Worksheets.Add(nothing, newExcel.ActiveSheet, nothing, nothing);
newSheet.QueryTables.Add("TEXT;"+FilePath[i-2],newSheet.get_Range("$A$1"),nothing);
//newSheet.QueryTables[i-1].Name = FileName[i-2];
newSheet.QueryTables[i-1].FieldNames = true;
newSheet.QueryTables[i-1].RowNumbers = false;
newSheet.QueryTables[i-1].FillAdjacentFormulas = false;
newSheet.QueryTables[i-1].PreserveFormatting = true;
newSheet.QueryTables[i-1].RefreshOnFileOpen = false;
newSheet.QueryTables[i-1].RefreshStyle = MSExcel.XlCellInsertionMode.xlInsertDeleteCells;
newSheet.QueryTables[i-1].SavePassword = false;
newSheet.QueryTables[i-1].SaveData = true;
newSheet.QueryTables[i-1].AdjustColumnWidth = true;
newSheet.QueryTables[i-1].RefreshPeriod = 0;
newSheet.QueryTables[i-1].TextFilePromptOnRefresh = false;
newSheet.QueryTables[i-1].TextFilePlatform = 936;
newSheet.QueryTables[i-1].TextFileStartRow = 1;
newSheet.QueryTables[i-1].TextFileParseType = MSExcel.XlTextParsingType.xlDelimited;
newSheet.QueryTables[i-1].TextFileTextQualifier = MSExcel.XlTextQualifier.xlTextQualifierDoubleQuote;
newSheet.QueryTables[i-1].TextFileConsecutiveDelimiter = true;
newSheet.QueryTables[i-1].TextFileTabDelimiter = false;
newSheet.QueryTables[i-1].TextFileSemicolonDelimiter = false;
newSheet.QueryTables[i-1].TextFileCommaDelimiter = false;
newSheet.QueryTables[i-1].TextFileSpaceDelimiter = false;
newSheet.QueryTables[i-1].TextFileOtherDelimiter = "|";
newSheet.QueryTables[i-1].TextFileColumnDataTypes = ColumnDataType;
newSheet.QueryTables[i-1].TextFileTrailingMinusNumbers = true;
newSheet.QueryTables[i-1].Refresh(false);
newSheet.QueryTables[i - 1].Name = FileName[i - 2]; string tmp = newSheet.QueryTables[i - 1].Name;
//newExcel.ActiveSheet.QueryTables[i-1].Delete();
//newSheet.QueryTables[i - 1].FieldNames = true;
}
((MSExcel.Worksheet)newSheet).Activate();
是不是应该这样
newSheet = newExcel.Worksheets.Add(nothing, newExcel.ActiveSheet, nothing, nothing);
还有你的QueryTable一直这样[i-1]随i递增干什么?每个sheet加一个然后就用它就可以了。
另外,把代码再贴出来,如果有改进的地方,请大家指教!for (int i=2;i<=FileName.Length+1;i++)
{
if (i > 2)
{
newExcel.Worksheets.Add(nothing, newExcel.Worksheets[i-2], nothing, nothing);
}
newSheet = newExcel.Worksheets[i-1];
newSheet.QueryTables.Add("TEXT;"+FilePath[i-2],newSheet.get_Range("$A$1"),nothing); //newSheet.QueryTables[i-1].Name = FileName[i-2];
newSheet.QueryTables[1].FieldNames = true;
newSheet.QueryTables[1].RowNumbers = false;
newSheet.QueryTables[1].FillAdjacentFormulas = false;
newSheet.QueryTables[1].PreserveFormatting = true;
newSheet.QueryTables[1].RefreshOnFileOpen = false;
newSheet.QueryTables[1].RefreshStyle = MSExcel.XlCellInsertionMode.xlInsertDeleteCells;
newSheet.QueryTables[1].SavePassword = false;
newSheet.QueryTables[1].SaveData = true;
newSheet.QueryTables[1].AdjustColumnWidth = true;
newSheet.QueryTables[1].RefreshPeriod = 0;
newSheet.QueryTables[1].TextFilePromptOnRefresh = false;
newSheet.QueryTables[1].TextFilePlatform = 936;
newSheet.QueryTables[1].TextFileStartRow = 1;
newSheet.QueryTables[1].TextFileParseType = MSExcel.XlTextParsingType.xlDelimited;
newSheet.QueryTables[1].TextFileTextQualifier = MSExcel.XlTextQualifier.xlTextQualifierDoubleQuote;
newSheet.QueryTables[1].TextFileConsecutiveDelimiter = true;
newSheet.QueryTables[1].TextFileTabDelimiter = false;
newSheet.QueryTables[1].TextFileSemicolonDelimiter = false;
newSheet.QueryTables[1].TextFileCommaDelimiter = false;
newSheet.QueryTables[1].TextFileSpaceDelimiter = false;
newSheet.QueryTables[1].TextFileOtherDelimiter = "|";
newSheet.QueryTables[1].TextFileColumnDataTypes = ColumnDataType;
newSheet.QueryTables[1].TextFileTrailingMinusNumbers = true;
newSheet.QueryTables[1].Refresh(false);
newSheet.Name = FileName[i - 2]; newSheet.QueryTables[1].Delete();
}这次我没有用activesheet,直接指明哪张sheet。大家帮我看看,没问题傍晚下班前结贴给分~
using System;
using System.Data;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Excel=Microsoft.Office.Interop.Excel;namespace ConsoleApplication3
{
class ExcelObj
{
private Excel.Application ExcelApp = new Excel.Application();//Excel应用程序实例
private Excel.Workbook ExcelWbk;//当前操作的工作簿
private Excel.Worksheet ExcelWst;//当前操作的工作表
public void Open(string path)
{
ExcelWbk = ExcelApp.Workbooks.Open(path);
}
public void ReadAllTXT(string path)
{
DirectoryInfo theFolder = new DirectoryInfo(path);
FileInfo[] files = theFolder.GetFiles(); foreach (FileInfo file in files)
{
if (Path.GetExtension(file.FullName) == ".txt")
{
ExcelWst = ExcelWbk.Worksheets.Add();
ExcelWst.Name = Path.GetFileNameWithoutExtension(file.Name); ExcelWst.QueryTables.Add(@"TEXT;"+ file.FullName, ExcelWst.get_Range("$A$1"));
ExcelWst.QueryTables[1].FieldNames = true;
ExcelWst.QueryTables[1].RowNumbers = false;
ExcelWst.QueryTables[1].FillAdjacentFormulas = false;
ExcelWst.QueryTables[1].PreserveFormatting = true;
ExcelWst.QueryTables[1].RefreshOnFileOpen = false;
ExcelWst.QueryTables[1].RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
ExcelWst.QueryTables[1].SavePassword = false;
ExcelWst.QueryTables[1].SaveData = true;
ExcelWst.QueryTables[1].AdjustColumnWidth = true;
ExcelWst.QueryTables[1].RefreshPeriod = 0;
ExcelWst.QueryTables[1].TextFilePromptOnRefresh = false;
ExcelWst.QueryTables[1].TextFilePlatform = 936;
ExcelWst.QueryTables[1].TextFileStartRow = 1;
ExcelWst.QueryTables[1].TextFileParseType = Excel.XlTextParsingType.xlDelimited;
ExcelWst.QueryTables[1].TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierDoubleQuote;
ExcelWst.QueryTables[1].TextFileConsecutiveDelimiter = true;
ExcelWst.QueryTables[1].TextFileTabDelimiter = false;
ExcelWst.QueryTables[1].TextFileSemicolonDelimiter = false;
ExcelWst.QueryTables[1].TextFileCommaDelimiter = false;
ExcelWst.QueryTables[1].TextFileSpaceDelimiter = false;
ExcelWst.QueryTables[1].TextFileOtherDelimiter = "|";
ExcelWst.QueryTables[1].TextFileColumnDataTypes = new int[]{1};
ExcelWst.QueryTables[1].TextFileTrailingMinusNumbers = true;
ExcelWst.QueryTables[1].Refresh(false); ExcelWst.QueryTables[1].Delete(); }
}
}
public void Close()
{
ExcelWbk.Close(true);
}
}
}感觉要比你的代码规范些
static void Main(string[] args)
{
ExcelObj Excelobj = new ExcelObj();
Excelobj.Open(@"C:\Users\Desktop\test\test.xlsx");
Excelobj.ReadAllTXT(@"C:\Users\Desktop\test\demo");
Excelobj.Close();
}demo文件夹下是所有的txt文件