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