C Implementation of Importing CSV File to Excel Workbook

This article illustrates how C # implements the method of importing CSV files into Excel workbooks. Share it for your reference. The details are as follows:

You must add references to Microsoft. Office. Core in the project: from the. NET tab of the Visual Studio Reference dialog box, and the Microsoft Excel 12.0 Object Library (you can use 14.0 if you want, too, but nothing lower).

The code for C # is as follows:

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();
}

The usage method is as follows:

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

I hope this article is helpful to everyone's C # programming.