public void ImportTextFile(string path, object destination, string tableName, string delimiter, out int rowsCount, out int columnsCount) { string extension = string.Empty; try { string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(path); extension = Path.GetExtension(path).ToLower().Replace(".", string.Empty); if (string.IsNullOrEmpty(tableName)) { tableName = fileNameWithoutExtension; } } catch { throw new ArgumentException($"Wrong path: '{path} argument.'"); } if (extension.ToLower() != "csv") { throw new ArgumentException($"Wrong path: '{path} argument.' Csv format is supported only."); } string connection = $"TEXT;{path}"; Range target = null; if (destination is string && Regex.Matches((string)destination, @"[a-zA-Z]").Count > 0) { target = sheet.Range[destination]; } else if (destination is System.Drawing.Point) { System.Drawing.Point p = (System.Drawing.Point)destination; target = sheet.Cells[p.X, p.Y] as Range; } if (target == null) { target = sheet.Cells[1, 1] as Range; } QueryTable qTable = sheet.QueryTables.Add(connection, target); SetDelimiter(qTable, delimiter); qTable.Name = tableName; qTable.Refresh(); columnsCount = qTable.ResultRange.Columns.Count; rowsCount = qTable.ResultRange.Rows.Count; }
protected override void ImportFileToSheet(string filePath, Worksheet sheet) { // importing text file as a query table QueryTable queryTable = sheet.QueryTables.Add(Connection: "TEXT;" + filePath, Destination: sheet.Range["$A$1"]); queryTable.Name = Path.GetFileName(filePath); 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 = true; queryTable.RefreshPeriod = 0; queryTable.TextFilePromptOnRefresh = false; queryTable.TextFilePlatform = 437; queryTable.TextFileStartRow = 1; queryTable.TextFileParseType = XlTextParsingType.xlDelimited; queryTable.TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote; queryTable.TextFileConsecutiveDelimiter = false; queryTable.TextFileTabDelimiter = false; queryTable.TextFileSemicolonDelimiter = false; queryTable.TextFileCommaDelimiter = true; queryTable.TextFileSpaceDelimiter = false; XlColumnDataType[] columnDataTypes = { XlColumnDataType.xlTextFormat, XlColumnDataType.xlTextFormat, XlColumnDataType.xlTextFormat, XlColumnDataType.xlTextFormat, XlColumnDataType.xlTextFormat, XlColumnDataType.xlTextFormat, XlColumnDataType.xlTextFormat }; queryTable.TextFileColumnDataTypes = columnDataTypes; queryTable.TextFileTrailingMinusNumbers = true; queryTable.Refresh(BackgroundQuery: false); // Rename the worksheet to the file name of the selected data file sheet.Name = Util.Clip("Precinct " + Path.GetFileNameWithoutExtension(filePath), 31); }
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; } }
public static void PollpadImport() { FileDialog fileDialog = ThisAddIn.app.FileDialog[MsoFileDialogType.msoFileDialogFilePicker]; Workbook workbook = ThisAddIn.app.ActiveWorkbook; // When file explorer opens, only display text log files fileDialog.Filters.Clear(); fileDialog.Filters.Add("PollPad files", "*.txt; *.csv"); // OPen the file explorer and allow selection of multiple files fileDialog.AllowMultiSelect = true; fileDialog.Show(); // Prevent showing Excel document updates to improve performance ThisAddIn.app.ScreenUpdating = false; // Loop to process multiple files consecutively for (int i = 1; i <= fileDialog.SelectedItems.Count; i++) { // pulling file path for a specific file string filePath = fileDialog.SelectedItems.Item(i); string fileNameOnly = Util.Clip(Path.GetFileNameWithoutExtension(filePath), 10); // Check for duplicate precincts and delete the duplicate sheets int c = 1; bool skip = false; while (c < workbook.Sheets.Count + 1) { if (workbook.Sheets[c].name == fileNameOnly + " PollPad") { Util.MessageBox(fileNameOnly + " shares the first 10 characters with a current worksheet." + " Please rename the file and import again."); break; } c++; } if (skip) { continue; } // Add an additional sheet and activate it to populate it with DS200 data workbook.Sheets.Add(After: workbook.ActiveSheet); Worksheet sheet = workbook.ActiveSheet; // importing text file as a query table QueryTable queryTable = sheet.QueryTables.Add(Connection: "TEXT;" + filePath, Destination: sheet.Range["$A$1"]); queryTable.Name = "Precinct " + i; 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 = true; queryTable.RefreshPeriod = 0; queryTable.TextFilePromptOnRefresh = false; queryTable.TextFilePlatform = 437; queryTable.TextFileStartRow = 1; queryTable.TextFileParseType = XlTextParsingType.xlDelimited; queryTable.TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote; queryTable.TextFileConsecutiveDelimiter = false; queryTable.TextFileTabDelimiter = true; queryTable.TextFileSemicolonDelimiter = false; queryTable.TextFileCommaDelimiter = true; queryTable.TextFileSpaceDelimiter = false; queryTable.TextFileTrailingMinusNumbers = true; queryTable.Refresh(BackgroundQuery: false); // Rename the worksheet to the file name of the selected data file sheet.Name = fileNameOnly + " PollPad"; } // Allow the excel file to actively update ThisAddIn.app.ScreenUpdating = true; }