Exemplo n.º 1
0
 public void ImportCsv(string path, Encoding encoding, Delimiter delimiter, TextQualifier textQualifier)
 {
     excel.ActiveSheet.Name = Path.GetFileNameWithoutExtension(path);
     int[] columnDataTypes = GetColumnDataTypes(path, encoding, delimiter, textQualifier);
     if (columnDataTypes.Length > 0)
     {
         QueryTable queryTable = excel.ActiveSheet.QueryTables.Add("TEXT;" + path, (Range)(excel.ActiveSheet.Range("A1")), Type.Missing);
         queryTable.Name                    = Path.GetFileNameWithoutExtension(path);
         queryTable.FieldNames              = true;
         queryTable.RowNumbers              = false;
         queryTable.FillAdjacentFormulas    = false;
         queryTable.PreserveFormatting      = true;
         queryTable.RefreshOnFileOpen       = false;
         queryTable.RefreshStyle            = XlCellInsertionMode.xlInsertDeleteCells;
         queryTable.SavePassword            = false;
         queryTable.SaveData                = true;
         queryTable.AdjustColumnWidth       = false;
         queryTable.RefreshPeriod           = 0;
         queryTable.TextFilePromptOnRefresh = false;
         try {
             queryTable.TextFilePlatform = encoding.Value.CodePage;
         } catch {
             throw new Exception("Microsoft Excel does not support the specified encoding.");
         }
         queryTable.TextFileStartRow             = 1;
         queryTable.TextFileParseType            = XlTextParsingType.xlDelimited;
         queryTable.TextFileTextQualifier        = textQualifier.ExcelValue;
         queryTable.TextFileConsecutiveDelimiter = false;
         queryTable.TextFileTabDelimiter         = (delimiter.Value == "\t");
         queryTable.TextFileCommaDelimiter       = (delimiter.Value == ",");
         queryTable.TextFileSemicolonDelimiter   = (delimiter.Value == ";");
         queryTable.TextFileSpaceDelimiter       = (delimiter.Value == " ");
         queryTable.TextFileColumnDataTypes      = columnDataTypes;
         queryTable.Refresh(false);
         queryTable.Delete();
         excel.Visible = true;
     }
 }
Exemplo n.º 2
0
        static int[] GetColumnDataTypes(string fileName, Encoding encoding, Delimiter delimiter, TextQualifier textQualifier)
        {
            int columnsCount = 0;
            int linesCount   = 0;

            using (var parser = new TextFieldParser(fileName, encoding.Value.GetEncoding())) {
                parser.Delimiters = new string[] { delimiter.Value };
                parser.HasFieldsEnclosedInQuotes = (!string.IsNullOrEmpty(textQualifier.Value));
                while (!parser.EndOfData)
                {
                    string[] tokens = parser.ReadFields();
                    linesCount++;
                    if (tokens.Length > columnsCount)
                    {
                        columnsCount = tokens.Length;
                    }
                    if (linesCount >= SamplingLines)
                    {
                        break;
                    }
                }
            }
            return(Enumerable.Repeat(2, columnsCount).ToArray());
        }