/// <summary>
/// Get filter list of word SaveFileDiaglog and update pdw extension
/// </summary>
/// <param name="isIncludeProntoDoc"></param>
/// <returns></returns>
private string GetFilterString(bool isIncludeProntoDoc)
{
string filterString = "";
// get filter from office
Office.FileDialog fileSave =
Wkl.MainCtrl.CommonCtrl.CommonProfile.App.get_FileDialog(Office.MsoFileDialogType.msoFileDialogSaveAs);
foreach (Office.FileDialogFilter filter in fileSave.Filters)
{
filterString += string.Format(FormatString.FullFilterDialog, filter.Description, filter.Extensions);
}
// add pronto document word filter
if (isIncludeProntoDoc)
{
filterString += string.Format(FormatString.DetailFilterDialog,
Properties.Resources.ipm_DescriptionFile, FileExtension.ProntoExtension);
}
else if (filterString.EndsWith("|"))
{
filterString = filterString.Remove(filterString.Length - 1);
}
return(filterString);
}
/// <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>
/// Setup Filters
/// </summary>
/// <param name="fd"></param>
/// <param name="defaultExt"></param>
/// <param name="fltrs"></param>
/// <returns>Index of initial filter</returns>
static int SetupFilters(
string filter,
string defaultExt,
Microsoft.Office.Core.FileDialog fd)
{
FileDialogFilters fltrs = null;
if (fd != null) // only setup if fd is defined
{
fltrs = fd.Filters;
fltrs.Clear();
}
string[] sa = filter.Split(',');
int sfi = 0;
for (int fi = 0; fi < sa.Length; fi += 2)
{
string desc = sa[fi];
string ext = sa[fi + 1];
if (fltrs != null)
{
fltrs.Add(desc, ext);
}
if (Lex.Contains(ext, defaultExt)) // selected filter
{
sfi = fi / 2 + 1;
}
}
if (sfi == 0 && sa.Length > 0)
{
sfi = 1;
}
if (fltrs != null)
{
fd.FilterIndex = sfi; // set the filter index
}
return(sfi);
}
/// <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;
}
}
}
}
