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#程序设计有所帮助。