/// <summary> /// User selects files to collect data from. /// </summary> /// <param name="xlApp">Excel Application</param> /// <returns>Array of full file path strings.</returns> public static string[] SelectFiles(Excel.Application xlApp) { FileDialog dialog = xlApp.FileDialog[Office.MsoFileDialogType.msoFileDialogOpen]; dialog.AllowMultiSelect = true; dialog.Filters.Add("Excel Files", "*.xlsx", 1); dialog.InitialFileName = string.Format( CultureInfo.CurrentCulture, @"C:\Users\{0}\Documents\BFMetrics\OriginalMonthFiles", Environment.UserName); if (dialog.Show() > 0) { string[] pathArray = new string[dialog.SelectedItems.Count]; for (int i = 1; i < dialog.SelectedItems.Count; i++) { pathArray[i - 1] = dialog.SelectedItems.Item(i); } if (pathArray.Length > 0) { return(pathArray); } throw new ArgumentException($"{pathArray} has a length of zero."); } else { throw new ArgumentException("File selection canceled by user."); } }
private void btn_Browse_Click(object sender, EventArgs e) { Microsoft.Office.Core.FileDialog fileDialog = ppApp.get_FileDialog(MsoFileDialogType.msoFileDialogFolderPicker); fileDialog.InitialFileName = "c:\\Temp\\"; int nres = fileDialog.Show(); if (nres == -1) //ok { FileDialogSelectedItems selectedItems = fileDialog.SelectedItems; string[] selectedFolders = selectedItems.Cast <string>().ToArray(); if (selectedFolders.Length > 0) { selectedFolder = selectedFolders[0]; } string[] fileEntries = Directory.GetFiles(selectedFolder); foreach (string fileName in fileEntries) { if (Path.GetExtension(fileName) == ".pptx") { fileGridView.Rows.Add(Path.GetFileName(fileName)); } } foreach (DataGridViewRow row in fileGridView.Rows) { DataGridViewCheckBoxCell chk = (DataGridViewCheckBoxCell)row.Cells[2]; chk.Value = !(chk.Value == null ? false : (bool)chk.Value); //because chk.Value is initialy null } } this.Width = 395; this.Height = 430; }
/// <summary> /// Prompts user to select the Excel containing latest iClick data. /// </summary> /// <param name="dataFileFullNm">An <code>out</code> parameter to /// capture the name of the selected file.</param> /// <returns> /// <see langword="true"/> if the user selected a file, otherwise /// <see langword="false"/>. /// </returns> /// <remarks> /// If the user does not select a file then the <code>dataFileFullNm</code> /// out parameter will be set to <see cref="string.Empty"/>. /// </remarks> private static bool PromptUserToOpenQuizDataWbk(out string dataFileFullNm) { dataFileFullNm = string.Empty; // ...in case user cxls bool userSelectedWbk = new bool(); Office.FileDialog fd = Globals.ThisWorkbook.Application.get_FileDialog( Office.MsoFileDialogType.msoFileDialogFilePicker); fd.Title = "Latest iClick Results"; fd.AllowMultiSelect = false; fd.Filters.Clear(); fd.Filters.Add("Excel Files", "*.xlsx"); // Handle user selection... if (fd.Show() == -1) // ...-1 == file selected; 0 == user cxled { userSelectedWbk = true; dataFileFullNm = fd.SelectedItems.Item(0); } return(userSelectedWbk); }
/// <summary> /// SaveAs() is entry point for saving SaveFileAs class. /// </summary> void IButtonsaveNewFolder.SaveAs() { Excel.Workbook activeWorkbook = this.xlApp.ActiveWorkbook; Excel.Worksheet newbornWorksheet = activeWorkbook.Worksheets["Newborns_3"]; CheckforHeaders(newbornWorksheet); string defaultSavePath = string.Format( CultureInfo.CurrentCulture, @"C:\Users\{0}\Documents\BFMetrics", Environment.UserName); FileDialog dialog = this.xlApp.FileDialog[Office.MsoFileDialogType.msoFileDialogSaveAs]; dialog.AllowMultiSelect = false; dialog.InitialFileName = defaultSavePath + FileName + GetDateString(newbornWorksheet, GetColumnForDateString(newbornWorksheet)) + ")"; if (dialog.Show() != 0) { string savePath = dialog.SelectedItems.Item(1); activeWorkbook.SaveAs(savePath, 51); } }
/// <summary> /// Prompts user to open XL wbk with latest iClick data. /// </summary> /// <returns> /// Returns name of opened XL workbook (string). /// If user canceled out of FileDialog returns an empty string. /// </returns> public bool PromptUserToOpenQuizDataWbk() { bool userSelectedWbk = new bool(); string testDataWbkNm = string.Empty; Office.FileDialog fd = Globals.ThisWorkbook.Application.get_FileDialog( Office.MsoFileDialogType.msoFileDialogOpen); fd.Title = "Latest iClick Results"; fd.AllowMultiSelect = false; fd.Filters.Clear(); fd.Filters.Add("Excel Files", "*.xlsx"); // Handle user selection... if (fd.Show() == -1) // ...-1 == file opened; 0 == user cxled { userSelectedWbk = true; fd.Execute(); testDataWbkNm = Globals.ThisWorkbook.Application.ActiveWorkbook.Name; _wbkTestData = Globals.ThisWorkbook.Application.Workbooks[testDataWbkNm]; } return(userSelectedWbk); }
/// <summary> /// This will create a temporary file in Unicode text (*.txt) format, overwrite the current loaded file by replaing all tabs with a comma and reload the file. /// </summary> /// <param name="force">To force save the current file as a Unicode CSV. /// When called from the Ribbon items Save/SaveAs, <i>force</i> will be true /// If this parameter is true and the file name extention is not .csv, then a SaveAs dialog will be displayed to choose a .csv file</param> /// <param name="newFile">To show a SaveAs dialog box to select a new file name /// This will be set to true when called from the Ribbon item SaveAs</param> public void SaveAsUnicodeCSV(bool force, bool newFile, bool needReOpen = true) { // _app.ActiveWindow.FreezePanes = false; _app.StatusBar = ""; bool currDispAlert = _app.DisplayAlerts; bool flag = true; int i; string filename = _app.ActiveWorkbook.FullName; GC.Collect(); if (force) //then make sure a csv file name is selected. { if (newFile || !filename.ToLower().EndsWith(".csv")) { Office.FileDialog d = _app.get_FileDialog(Office.MsoFileDialogType.msoFileDialogSaveAs); i = _app.ActiveWorkbook.Name.LastIndexOf("."); if (i >= 0) { d.InitialFileName = _app.ActiveWorkbook.Name.Substring(0, i); } else { d.InitialFileName = _app.ActiveWorkbook.Name; } d.AllowMultiSelect = false; Office.FileDialogFilters f = d.Filters; for (i = 1; i <= f.Count; i++) { if ("*.csv".Equals(f.Item(i).Extensions)) { d.FilterIndex = i; break; } } if (d.Show() == 0) //User cancelled the dialog { flag = false; } else { filename = d.SelectedItems.Item(1); } } if (flag && !filename.ToLower().EndsWith(".csv")) { MessageBox.Show("Please select a CSV file name first"); flag = false; } } if (flag && filename.ToLower().EndsWith(".csv") && (force || _unicodeFiles.Contains(filename))) { if (isInCellEditingMode()) { MessageBox.Show("Please finish editing before saving"); } else { try { //Getting current selection to restore the current cell selection // Excel.Range rng = app.ActiveCell; // int row = rng.Row; // int col = rng.Column; string tempFile = System.IO.Path.GetTempFileName(); var orignalTempFileName = tempFile; var tmpFileName = Path.GetFileNameWithoutExtension(tempFile); tempFile = tempFile.Replace(tmpFileName, Path.GetFileNameWithoutExtension(filename) + "_" + tmpFileName); try { _sFlag = true; //This is to prevent this method getting called again from app_WorkbookBeforeSave event caused by the next SaveAs call using (var sw = new StreamWriter(tempFile, false, Encoding.UTF8)) { Excel.Worksheet worksheet = _app.ActiveSheet; //当前活动sheet Excel.Range usedRange = worksheet.UsedRange; //获取使用的格子二维数组 _lastScrollRow = _app.ActiveWindow.ScrollRow; _lastScrollColumn = _app.ActiveWindow.ScrollColumn; if (usedRange == null) { MessageBox.Show("不可预知的异常"); return; } var rowCount = usedRange.Rows.Count; var columnCount = usedRange.Columns.Count; var arr = new object[rowCount, columnCount]; arr = usedRange.Value; for (int j = 1; j <= rowCount; j++) { for (int jj = 1; jj <= columnCount; jj++) { if (jj != 1) { sw.Write(","); } var o = arr[j, jj]; var value = o != null?o.ToString() : string.Empty; if (value.Contains("\"")) { value = value.Replace("\"", "\"\""); } if (value.Contains(",") || value.Contains("\"") || value.Contains("\n")) { value = $"\"{value}\""; } sw.Write(value); } sw.WriteLine(); } } _openFiles.Remove(filename); _app.ActiveWorkbook.Close(); GC.Collect(); if (new FileInfo(tempFile).Length <= (1024 * 1024)) //If its less than 1MB, load the whole data to memory for character replacement { File.WriteAllText(filename, File.ReadAllText(tempFile, Encoding.Default).Replace("\t", ","), Encoding.UTF8); } else //otherwise read chunks for data (in 10KB chunks) into memory { using (StreamReader sr = new StreamReader(tempFile, Encoding.Default)) using (StreamWriter sw = new StreamWriter(filename, false, Encoding.UTF8)) { char[] buffer = new char[10 * 1024]; //10KB Chunks while (!sr.EndOfStream) { int cnt = sr.ReadBlock(buffer, 0, buffer.Length); for (i = 0; i < cnt; i++) { if (buffer[i] == '\t') { buffer[i] = ','; } } sw.Write(buffer, 0, cnt); } } } } finally { File.Delete(orignalTempFileName); } GC.Collect(); if (!needReOpen) { return; } openFile(filename); _app.StatusBar = "File has been saved as a Unicode CSV"; if (!_unicodeFiles.Contains(filename)) { _unicodeFiles.Add(filename); } _app.ActiveWorkbook.Saved = true; } catch (Exception e) { MessageBox.Show("Error occured while trying to save this file as Unicode CSV: " + e.Message); } finally { _app.DisplayAlerts = currDispAlert; } } } }
/// <summary> /// This will create a temporary file in Unicode text (*.txt) format, overwrite the current loaded file by replaing all tabs with a comma and reload the file. /// </summary> /// <param name="force">To force save the current file as a Unicode CSV. /// When called from the Ribbon items Save/SaveAs, <i>force</i> will be true /// If this parameter is true and the file name extention is not .csv, then a SaveAs dialog will be displayed to choose a .csv file</param> /// <param name="newFile">To show a SaveAs dialog box to select a new file name /// This will be set to true when called from the Ribbon item SaveAs</param> public void SaveAsUnicodeCSV(bool force, bool newFile) { app.StatusBar = ""; bool currDispAlert = app.DisplayAlerts; bool flag = true; int i; string filename = app.ActiveWorkbook.FullName; if (force) //then make sure a csv file name is selected. { if (newFile || !filename.ToLower().EndsWith(".csv")) { Office.FileDialog d = app.get_FileDialog(Office.MsoFileDialogType.msoFileDialogSaveAs); i = app.ActiveWorkbook.Name.LastIndexOf("."); if (i >= 0) { d.InitialFileName = app.ActiveWorkbook.Name.Substring(0, i); } else { d.InitialFileName = app.ActiveWorkbook.Name; } d.AllowMultiSelect = false; Office.FileDialogFilters f = d.Filters; for (i = 1; i <= f.Count; i++) { if ("*.csv".Equals(f.Item(i).Extensions)) { d.FilterIndex = i; break; } } if (d.Show() == 0) //User cancelled the dialog { flag = false; } else { filename = d.SelectedItems.Item(1); } } if (flag && !filename.ToLower().EndsWith(".csv")) { MessageBox.Show("Please select a CSV file name first"); flag = false; } } if (flag && filename.ToLower().EndsWith(".csv") && (force || unicodeFiles.Contains(filename))) { if (isInCellEditingMode()) { MessageBox.Show("Please finish editing before saving"); } else { try { //Getting current selection to restore the current cell selection Excel.Range rng = (Excel.Range)app.ActiveCell; int row = rng.Row; int col = rng.Column; string tempFile = System.IO.Path.GetTempFileName(); try { sFlag = true; //This is to prevent this method getting called again from app_WorkbookBeforeSave event caused by the next SaveAs call app.ActiveWorkbook.SaveAs(tempFile, Excel.XlFileFormat.xlUnicodeText); app.ActiveWorkbook.Close(); if (new FileInfo(tempFile).Length <= (1024 * 1024)) //If its less than 1MB, load the whole data to memory for character replacement { File.WriteAllText(filename, File.ReadAllText(tempFile, UnicodeEncoding.UTF8).Replace("\t", ","), UnicodeEncoding.UTF8); } else //otherwise read chunks for data (in 10KB chunks) into memory { using (StreamReader sr = new StreamReader(tempFile, true)) using (StreamWriter sw = new StreamWriter(filename, false, sr.CurrentEncoding)) { char[] buffer = new char[10 * 1024]; //10KB Chunks while (!sr.EndOfStream) { int cnt = sr.ReadBlock(buffer, 0, buffer.Length); for (i = 0; i < cnt; i++) { if (buffer[i] == '\t') { buffer[i] = ','; } } sw.Write(buffer, 0, cnt); } } } } finally { File.Delete(tempFile); } app.Workbooks.Open(filename, Type.Missing, Type.Missing, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, ","); Excel.Worksheet ws = app.ActiveWorkbook.ActiveSheet; ws.Cells[row, col].Select(); app.StatusBar = "File has been saved as a Unicode CSV"; if (!unicodeFiles.Contains(filename)) { unicodeFiles.Add(filename); } app.ActiveWorkbook.Saved = true; } catch (Exception e) { MessageBox.Show("Error occured while trying to save this file as Unicode CSV: " + e.Message); } finally { app.DisplayAlerts = currDispAlert; } } } }