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