Sometime there are requirements where we are required to open CSV file in excel.
if it a manual process then there is now issue. MS Excel provide features using which you can open CSV by default.
Excel also provides facility to open non csv files(Delimited Text Files). To use this feature
go to
Note: I using MS EXCEL 2010
Data Ribbon - > Select - > "From Text" Option
Select text file you want to open and follow the wizard.
Above feature also allow you to open UTF-8 format files.
How to achieve the same functionality using C#.NET?
Some will open the file and read it line by line and convert UTF-8 format.
This will take lot of time when you have large file.
Parameters:
xlSheet: reference to Excel worksheet object. The worksheet needs to active worksheet
Filpath: Path to selected Text or CSV File
public void Importcsv(ref Excel.Worksheet xlSheet,string FilePath)
{
Excel.Range ranage = xlSheet.get_Range("$A$1", Type.Missing);
Excel.QueryTable QT = xlSheet.QueryTables.Add("TEXT;" + FilePath, ranage);
QT.Name = Path.GetFileNameWithoutExtension(FilePath);
QT.FieldNames = true;
QT.RowNumbers = true;
QT.FillAdjacentFormulas = false;
QT.PreserveFormatting = true;
QT.RefreshOnFileOpen = false;
QT.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
QT.SavePassword = false;
QT.SaveData = true;
QT.AdjustColumnWidth = true;
QT.RefreshPeriod = 0;
QT.TextFilePromptOnRefresh = false;
QT.TextFilePlatform = 65001;
QT.TextFileStartRow = 1;
QT.TextFileParseType = Excel.XlTextParsingType.xlDelimited;
QT.TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierDoubleQuote;
QT.TextFileConsecutiveDelimiter = false;
QT.TextFileTabDelimiter = false;
QT.TextFileSemicolonDelimiter = false;
QT.TextFileCommaDelimiter = true;
QT.TextFileSpaceDelimiter = false;
QT.TextFileDecimalSeparator = ",";
QT.TextFileThousandsSeparator = ".";
// QT.TextFileColumnDataTypes =
The above property defines data type of each column based on the file you have selected the data type is specified based on index like 1, 2 like wise
xlSheet.QueryTables[1].Destination.EntireColumn.AutoFit();
xlSheet.QueryTables[1].Refresh(false);
xlSheet.QueryTables[1].Delete();
xlSheet.Name = Path.GetFileNameWithoutExtension(FilePath);
}
