C#实现导入CSV文件到Excel工作簿的方法
本文实例讲述了C#实现导入CSV文件到Excel工作簿的方法。分享给大家供大家参考。具体如下：
你必须在项目中添加对Microsoft.Office.Core的引用：fromthe.NETtaboftheVisualStudioAddReferencedialogbox,andtheMicrosoftExcel12.0ObjectLibrary(youcanuse14.0ifyouwant,too,butnothinglower).
C#代码如下：
usingMicrosoft.Office.Interop.Excel;
usingMicrosoft.Office.Core;
///<summary>
///TakesaCSVfileandsucksitintothespecifiedworksheetofthisworkbookatthespecifiedrange
///</summary>
///<paramname="importFileName">Specifiesthefullpathtothe.CSVfiletoimport</param>
///<paramname="destinationSheet">Excel.Worksheetobjectcorrespondingtothedestinationworksheet.</param>
///<paramname="destinationRange">Excel.Rangeobjectspecifyingthedestinationcell(s)</param>
///<paramname="columnDataTypes">Columndatatypespecifierarray.FortheQueryTable.TextFileColumnDataTypesproperty.</param>
///<paramname="autoFitColumns">SpecifieswhethertodoanAutoFitonallimportedcolumns.</param>
publicvoidImportCSV(stringimportFileName,Excel.WorksheetdestinationSheet,
Excel.RangedestinationRange,int[]columnDataTypes,boolautoFitColumns)
{
destinationSheet.QueryTables.Add(
"TEXT;"+Path.GetFullPath(importFileName),
destinationRange,Type.Missing);
destinationSheet.QueryTables[1].Name=Path.GetFileNameWithoutExtension(importFileName);
destinationSheet.QueryTables[1].FieldNames=true;
destinationSheet.QueryTables[1].RowNumbers=false;
destinationSheet.QueryTables[1].FillAdjacentFormulas=false;
destinationSheet.QueryTables[1].PreserveFormatting=true;
destinationSheet.QueryTables[1].RefreshOnFileOpen=false;
destinationSheet.QueryTables[1].RefreshStyle=XlCellInsertionMode.xlInsertDeleteCells;
destinationSheet.QueryTables[1].SavePassword=false;
destinationSheet.QueryTables[1].SaveData=true;
destinationSheet.QueryTables[1].AdjustColumnWidth=true;
destinationSheet.QueryTables[1].RefreshPeriod=0;
destinationSheet.QueryTables[1].TextFilePromptOnRefresh=false;
destinationSheet.QueryTables[1].TextFilePlatform=437;
destinationSheet.QueryTables[1].TextFileStartRow=1;
destinationSheet.QueryTables[1].TextFileParseType=XlTextParsingType.xlDelimited;
destinationSheet.QueryTables[1].TextFileTextQualifier=XlTextQualifier.xlTextQualifierDoubleQuote;
destinationSheet.QueryTables[1].TextFileConsecutiveDelimiter=false;
destinationSheet.QueryTables[1].TextFileTabDelimiter=false;
destinationSheet.QueryTables[1].TextFileSemicolonDelimiter=false;
destinationSheet.QueryTables[1].TextFileCommaDelimiter=true;
destinationSheet.QueryTables[1].TextFileSpaceDelimiter=false;
destinationSheet.QueryTables[1].TextFileColumnDataTypes=columnDataTypes;
Logger.GetInstance().WriteLog("Importingdata...");
destinationSheet.QueryTables[1].Refresh(false);
if(autoFitColumns==true)
destinationSheet.QueryTables[1].Destination.EntireColumn.AutoFit();
//cleanup
this.ActiveSheet.QueryTables[1].Delete();
}
使用方法如下：
myOwnWorkbookClass.ImportCSV(
@"C:\MyStuff\MyFile.CSV",
(Excel.Worksheet)(MyWorkbook.Worksheets[1]),
(Excel.Range)(((Excel.Worksheet)MyWorkbook.Worksheets[1]).get_Range("$A$7")),
newint[]{2,2,2,2,2},true);
希望本文所述对大家的C#程序设计有所帮助。