C#实现导入CSV文件到Excel工作簿的方法

发布时间：2020-12-15 05:51:31 所属栏目：百科来源：网络整理

导读：本篇章节讲解C#实现导入CSV文件到Excel工作簿的方法。供大家参考研究。具体如下：你必须在项目中添加对 Microsoft.Office.Core 的引用：from the .NET tab of the Visual Studio Add Reference dialog box,and the Microsoft Excel 12.0 Object Lib

本篇章节讲解C#实现导入CSV文件到Excel工作簿的方法。分享给大家供大家参考。具体如下：

你必须在项目中添加对 Microsoft.Office.Core 的引用：from the .NET tab of the Visual Studio Add Reference dialog box,and the Microsoft Excel 12.0 Object Library (you can use 14.0 if you want,too,but nothing lower).

C#代码如下：

using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
/// <summary>
/// Takes a CSV file and sucks it into the specified worksheet of this workbook at the specified range
/// </summary>
/// <param name="importFileName">Specifies the full path to the .CSV file to import</param>
/// <param name="destinationSheet">Excel.Worksheet object corresponding to the destination worksheet.</param>
/// <param name="destinationRange">Excel.Range object specifying the destination cell(s)</param>
/// <param name="columnDataTypes">Column data type specifier array. For the QueryTable.TextFileColumnDataTypes property.</param>
/// <param name="autoFitColumns">Specifies whether to do an AutoFit on all imported columns.</param>
public void ImportCSV(string importFileName,Excel.Worksheet destinationSheet,Excel.Range destinationRange,int[] columnDataTypes,bool autoFitColumns)
{
  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("Importing data...");
  destinationSheet.QueryTables[1].Refresh(false);
  if (autoFitColumns==true)
    destinationSheet.QueryTables[1].Destination.EntireColumn.AutoFit();
  // cleanup
  this.ActiveSheet.QueryTables[1].Delete();
}

使用方法如下：

myOwnWorkbookClass.ImportCSV(
   @"C:MyStuffMyFile.CSV",(Excel.Worksheet)(MyWorkbook.Worksheets[1]),(Excel.Range)(((Excel.Worksheet)MyWorkbook.Worksheets[1]).get_Range("$A$7")),new int[] { 2,2,2 },true);

希望本文所述对大家的C#程序设计有所帮助。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!