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