/// <summary> Сохранение документа </summary>
/// <param name="defaultWorksheetIndex"> номер страницы по умолчанию</param>
/// <param name="fileName"> имя файла </param>
/// <param name="xlFileFormat"> формат формируемого файла (см. XlFileFormat Enumeration)</param>
public void SaveAs(string fileName, int defaultWorksheetIndex = 1, XlFileFormat xlFileFormat = XlFileFormat.xlNone)
{
using (var w = GetWorksheet(defaultWorksheetIndex).Activate())
{
_workbook._InvokeMethod("SaveAs", fileName, xlFileFormat == XlFileFormat.xlNone ? Type.Missing : xlFileFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 3 /*XlSaveAsAccessMode.xlExclusive*/);
}
}
void SaveBookAs(Workbook book, XlFileFormat format, string extension)
{
var filename = GetTargetName(book, extension);
WriteVerbose($"Exporting a workbook: {filename}");
book.SaveAs(filename, format, Local: true);
}
public string Save(string filename, bool savePopupFlag = true, string password = "")
{
string fileExt = Path.GetExtension(filename);
if (savePopupFlag)
{
ExcelApplication.DisplayAlerts = true;
xSaveName = ExcelApplication.GetSaveAsFilename(Path.GetFileNameWithoutExtension(filename), "Excel Workbook (*" + fileExt + "), *" + fileExt);
ExcelApplication.DisplayAlerts = false;
}
else
{
if (!File.Exists(filename))
{
xSaveName = filename;
}
else
{
xSaveName = Path.Combine(Path.GetDirectoryName(filename), Path.GetFileNameWithoutExtension(filename) + Path.GetExtension(filename));
}
}
XlFileFormat fileFormat = fileExt switch
{
".xlsx" => XlFileFormat.xlOpenXMLWorkbook,
".xlsm" => XlFileFormat.xlOpenXMLWorkbookMacroEnabled,
".xls" => XlFileFormat.xlExcel8,
".csv" => XlFileFormat.xlCSV,
".txt" => XlFileFormat.xlTextWindows,
_ => XlFileFormat.xlWorkbookDefault,
};
ExcelApplication.DisplayAlerts = false;
Workbook.SaveAs(Filename: xSaveName, FileFormat: fileFormat, CreateBackup: false, Password: password, ConflictResolution: XlSaveConflictResolution.xlLocalSessionChanges);
return(xSaveName);
}
public void RunExample()
{
bool isFailed = false;
string workbookFile = null;
Excel.Application excelApplication = null;
try
{
// start excel and turn off msg boxes
excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = false;
// create a utils instance, not need for but helpful to keep the lines of code low
Excel.Tools.CommonUtils utils = new Excel.Tools.CommonUtils(excelApplication);
// add a new workbook
Excel.Workbook workBook = excelApplication.Workbooks.Add();
// add new global Code Module
VB.VBComponent globalModule = workBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
globalModule.Name = "MyNewCodeModule";
// add a new procedure to the modul
globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello from NetOffice!\" & vbnewline & Param\r\nEnd Sub");
// create a click event trigger for the first worksheet
int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet");
workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\"");
// display info in the worksheet
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets[1];
sheet.Cells[2, 2].Value = "This workbook contains dynamic created VBA Moduls and Event Code";
sheet.Cells[5, 2].Value = "Open the VBA Editor to see the code";
sheet.Cells[8, 2].Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";
// save the book
XlFileFormat fileFormat = GetFileFormat(excelApplication);
workbookFile = utils.File.Combine(HostApplication.RootDirectory, "Example07", Excel.Tools.DocumentFormat.Macros);
workBook.SaveAs(workbookFile, fileFormat);
}
catch (System.Runtime.InteropServices.COMException throwedException)
{
isFailed = true;
HostApplication.ShowErrorDialog("VBA Error", throwedException);
}
finally
{
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
if ((null != workbookFile) && (!isFailed))
{
HostApplication.ShowFinishDialog(null, workbookFile);
}
}
}
public void SaveToFile(DataSet dataSet, string fileName)
{
CheckFileName(fileName);
//XlFileFormat format = XlFileFormat.xlCSVWindows;//导出为CSV，出错，不支持
XlFileFormat format = XlFileFormat.xlWorkbookNormal;//导出为EXCEL
InnerSave(dataSet, format, fileName, false);
}
/// <summary>
/// 이 Worksheet를 다른 이름으로 저장합니다.
/// </summary>
/// <param name="fileName">저장할 파일 이름.</param>
/// <param name="fileFormat">파일 형식.</param>
public void SaveAs(string fileName, XlFileFormat fileFormat)
{
_xlSheet.SaveAs(
fileName, // Filename
fileFormat, // FileFormat
Type.Missing, // Password
Type.Missing, // WriteResPassword
Type.Missing, // ReadOnlyRecommended
Type.Missing, // CreateBackup
Type.Missing, // AddToMru
Type.Missing, // TextCodepage
Type.Missing, // TextVisualLayout
Type.Missing // Local
);
}
/// <summary>
/// Save workbook to new file name (optionally to different format)
/// </summary>
/// <param name="fileName">Full Path Name of file to save</param>
/// <param name="fileFormat">File format desired.</param>
/// <returns></returns>
public bool SaveWorkbookAs(string fileName, XlFileFormat fileFormat = XlFileFormat.xlOpenXMLWorkbook)
{
bool result = false;
try
{
xlWorkbook.SaveAs(fileName, fileFormat);
result = true;
}
catch (Exception ex)
{
LastError = $"Error saving excel file: {ex.Message}";
result = false;
}
return(result);
}
/// <summary>
/// 변경된 모든 사항을 지정된 경로에 지정된 형식으로 저장합니다.
/// </summary>
/// <param name="type">저장할 파일의 형식.</param>
/// <param name="fileName">파일을 저장할 경로.</param>
public void SaveAs(string fileName, XlFileFormat fileFormat)
{
ActiveWorkbook.SaveAs(
fileName, // Filename
fileFormat, // FileFormat
Type.Missing, // Password
Type.Missing, // WriteResPassword
Type.Missing, // ReadOnlyRecommended
Type.Missing, // CreateBackup
XlSaveAsAccessMode.xlNoChange, // AccessMode
Type.Missing, // ConflictResolution
Type.Missing, // AddToMru
Type.Missing, // TextCodepage
Type.Missing, // TextVisualLayout
Type.Missing // Local
);
}
public static bool Convert(string from, string to, XlFileFormat format)
{
if (!IsExcelInstalled())
{
return false;
}
Application app = new Application();
app.DisplayAlerts = false;
Workbook excelWorkbook = app.Workbooks.Open(from);
excelWorkbook.SaveAs(to, format);
excelWorkbook.Close();
app.Quit();
return File.Exists(to);
}
public static bool Convert(string from, string to, XlFileFormat format)
{
if (!IsExcelInstalled())
{
return(false);
}
Application app = new Application();
app.DisplayAlerts = false;
Workbook excelWorkbook = app.Workbooks.Open(from);
excelWorkbook.SaveAs(to, format);
excelWorkbook.Close();
app.Quit();
return(File.Exists(to));
}
/// <summary>
/// 描述:修改方法名，由Save改为SaveAs，另存文件
/// 作者:张怡鹏
/// 创建日期:2009.11.5
/// </summary>
/// <returns></returns>
public bool SaveAs()
{
try
{
// 2012-08-10 张航宇
// 要求FileName包括后缀名，此处不将后缀名去掉
//string strName =m_sFileName.Substring(0,m_sFileName.LastIndexOf(('.')));
XlFileFormat xlFileFormat = new XlFileFormat();
xlFileFormat = m_bOffice2003Ver == true ? XlFileFormat.xlExcel8 : XlFileFormat.xlWorkbookDefault;
m_pWorkBook.SaveAs(m_sFileName, xlFileFormat, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
m_pWorkBook.Close(false, Nothing, Nothing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_pWorkBook);
m_pExcelApp.Quit();
m_pWorkBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_pExcelApp);
m_pExcelApp = null;
GC.Collect();
return(true);
}
catch (Exception exp)
{
Hy.Common.Utility.Log.OperationalLogManager.AppendMessage(exp.ToString());
if (m_pWorkBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_pWorkBook);
}
if (m_pExcelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_pExcelApp);
}
m_pWorkBook = null;
m_pExcelApp = null;
return(false);
}
}
private static void InnerSave(DataSet dataSet, XlFileFormat format, string outputPath, bool deleteOldFile)
{
object missing = Type.Missing;
ApplicationClass excelApp = null;
Workbook excelWorkbook = null;
try
{
if (deleteOldFile && File.Exists(outputPath))
{
File.Delete(outputPath);
}
// Create the Excel Application object
excelApp = new ApplicationClass();
// Create a new Excel Workbook
excelWorkbook = excelApp.Workbooks.Add(missing);
int sheetIndex = 0;
// Copy each DataTable
foreach (System.Data.DataTable dt in dataSet.Tables)
{
// Copy the DataTable to an object array
int rowCount = dt.Rows.Count;
int colsCount = dt.Columns.Count;
string[,] rawData = new string[rowCount + 1, colsCount];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int row = 0; row < rowCount; row++)
{
var dataRow = dt.Rows[row];
for (int col = 0; col < colsCount; col++)
{
rawData[row + 1, col] = dataRow[col].ToString();
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dt.Rows.Count + 1);
var range = excelSheet.get_Range(excelRange, Type.Missing);
range.Value2 = rawData;
// Mark the first row as BOLD
range = ((Range)excelSheet.Rows[1, Type.Missing]);
range.Font.Bold = true;
}
//excelApp.Application.AlertBeforeOverwriting = false;
excelApp.Application.DisplayAlerts = false;
// Save and Close the Workbook
excelWorkbook.SaveAs(
outputPath,
format,
missing, missing, missing, missing,
XlSaveAsAccessMode.xlExclusive,
missing, missing, missing, missing, missing
);
}
finally
{
#region Dispose
if (excelWorkbook != null)
{
excelWorkbook.Close(true, missing, missing);
excelWorkbook = null;
}
// Release the Application object
if (excelApp != null)
{
excelApp.Quit();
//释放进程
IntPtr t = new IntPtr(excelApp.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process.GetProcessById(k).Kill();
excelApp = null;
}
#endregion
}
}
public static new int Convert(String inputFile, String outputFile, Hashtable options)
{
Boolean running = (Boolean)options["noquit"];
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
Boolean nowrite = (Boolean)options["readonly"];
try
{
app = new Microsoft.Office.Interop.Excel.Application()
{
Visible = true,
DisplayAlerts = false,
AskToUpdateLinks = false,
AlertBeforeOverwriting = false,
EnableLargeOperationAlert = false,
Interactive = false,
FeatureInstall = Microsoft.Office.Core.MsoFeatureInstall.msoFeatureInstallNone
};
if ((Boolean)options["hidden"])
{
// Try and at least minimise it
app.WindowState = XlWindowState.xlMinimized;
app.Visible = false;
}
String readPassword = "";
if (!String.IsNullOrEmpty((String)options["password"]))
{
readPassword = (String)options["password"];
}
Object oReadPass = (Object)readPassword;
String writePassword = "";
if (!String.IsNullOrEmpty((String)options["writepassword"]))
{
writePassword = (String)options["writepassword"];
}
Object oWritePass = (Object)writePassword;
// Check for password protection and no password
if (Converter.IsPasswordProtected(inputFile) && String.IsNullOrEmpty(readPassword))
{
Console.WriteLine("Unable to open password protected file");
return((int)ExitCode.PasswordFailure);
}
app.EnableEvents = (bool)options["excel_auto_macros"];
workbooks = app.Workbooks;
workbook = workbooks.Open(inputFile, true, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
// Unable to open workbook
if (workbook == null)
{
return((int)ExitCode.FileOpenFailure);
}
if (app.EnableEvents)
{
workbook.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
}
// Try and avoid xls files raising a dialog
var temporaryStorageDir = Path.GetTempFileName();
File.Delete(temporaryStorageDir);
Directory.CreateDirectory(temporaryStorageDir);
tmpFile = Path.Combine(temporaryStorageDir, Path.GetFileNameWithoutExtension(inputFile) + ".xls");
// Set up the file save format
XlFileFormat fmt = XlFileFormat.xlOpenXMLWorkbook;
if (workbook.HasVBProject)
{
fmt = XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
tmpFile += "m";
}
else
{
tmpFile += "x";
}
// Set up the print quality
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
if ((Boolean)options["screen"])
{
quality = XlFixedFormatQuality.xlQualityMinimum;
}
// Remember - Never use 2 dots with COM objects!
// Using more than one dot leaves wrapper objects left over
var wbWin = workbook.Windows;
var appWin = app.Windows;
if ((Boolean)options["excel_show_formulas"])
{
// Determine whether to show formulas
appWin[1].DisplayFormulas = true;
}
if (wbWin.Count > 0)
{
wbWin[1].Visible = (Boolean)options["hidden"] ? false : true;
Converter.ReleaseCOMObject(wbWin);
}
if (appWin.Count > 0)
{
appWin[1].Visible = (Boolean)options["hidden"] ? false : true;
Converter.ReleaseCOMObject(appWin);
}
// Large excel files may simply not print reliably - if the excel_max_rows
// configuration option is set, then we must close up and forget about
// converting the file. However, if a print area is set in one of the worksheets
// in the document, then assume the author knew what they were doing and
// use the print area.
var max_rows = (int)options[@"excel_max_rows"];
// We may need to loop through all the worksheets in the document
// depending on the options given. If there are maximum row restrictions
// or formulas are being shown, then we need to loop through all the
// worksheets
if (max_rows > 0 || (Boolean)options["excel_show_formulas"] || (Boolean)options["excel_show_headings"])
{
var row_count_check_ok = true;
var found_rows = 0;
var found_worksheet = "";
var worksheets = workbook.Worksheets;
foreach (var ws in worksheets)
{
if ((Boolean)options["excel_show_headings"])
{
var pageSetup = ((Microsoft.Office.Interop.Excel.Worksheet)ws).PageSetup;
pageSetup.PrintHeadings = true;
Converter.ReleaseCOMObject(pageSetup);
}
// If showing formulas, make things auto-fit
if ((Boolean)options["excel_show_formulas"])
{
((Microsoft.Office.Interop.Excel.Worksheet)ws).Activate();
app.ActiveWindow.DisplayFormulas = true;
var cols = ((Microsoft.Office.Interop.Excel.Worksheet)ws).Columns;
cols.AutoFit();
Converter.ReleaseCOMObject(cols);
}
// If there is a maximum row count, make sure we check each worksheet
if (max_rows > 0)
{
// Check for a print area
var page_setup = ((Microsoft.Office.Interop.Excel.Worksheet)ws).PageSetup;
var print_area = page_setup.PrintArea;
Converter.ReleaseCOMObject(page_setup);
if (string.IsNullOrEmpty(print_area))
{
// There is no print area, check that the row count is <= to the
// excel_max_rows value. Note that we can't just take the range last
// row, as this may return a huge value, rather find the last non-blank
// row.
var row_count = 0;
var range = ((Microsoft.Office.Interop.Excel.Worksheet)ws).UsedRange;
if (range != null)
{
var rows = range.Rows;
if (rows != null && rows.Count > max_rows)
{
var cells = range.Cells;
if (cells != null)
{
var cellSearch = cells.Find("*", oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, oMissing, oMissing);
// Make sure we actually get some results, since the worksheet may be totally blank
if (cellSearch != null)
{
row_count = cellSearch.Row;
found_worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)ws).Name;
Converter.ReleaseCOMObject(cellSearch);
}
Converter.ReleaseCOMObject(cells);
}
Converter.ReleaseCOMObject(rows);
}
}
Converter.ReleaseCOMObject(range);
if (row_count > max_rows)
{
// Too many rows on this worksheet - mark the workbook as unprintable
row_count_check_ok = false;
found_rows = row_count;
Converter.ReleaseCOMObject(ws);
break;
}
}
} // End of row check
Converter.ReleaseCOMObject(ws);
}
Converter.ReleaseCOMObject(worksheets);
// Make sure we are not converting a document with too many rows
if (row_count_check_ok == false)
{
throw new Exception(String.Format("Too many rows to process ({0}) on worksheet {1}", found_rows, found_worksheet));
}
}
Boolean includeProps = !(Boolean)options["excludeprops"];
workbook.SaveAs(tmpFile, fmt, Type.Missing, Type.Missing, Type.Missing, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
return((int)ExitCode.Success);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return((int)ExitCode.UnknownError);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
if (!running)
{
if (workbooks != null)
{
workbooks.Close();
}
if (app != null)
{
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}
// Clean all the COM leftovers
Converter.ReleaseCOMObject(workbook);
Converter.ReleaseCOMObject(workbooks);
Converter.ReleaseCOMObject(app);
if (tmpFile != null && File.Exists(tmpFile))
{
System.IO.File.Delete(tmpFile);
// Remove the temporary path to the temp file
Directory.Delete(Path.GetDirectoryName(tmpFile));
}
}
}
public bool ExportExcel(string filename, List <List <string> > data, string sheetname, XlFileFormat fileFormat, List <ExcelColorModel> excelColorEntities)
{
var xlApp = new Application();
var workbooks = xlApp.Workbooks;
var workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
var worksheet = (Worksheet)workbook.Worksheets[1];
worksheet.Name = sheetname;
worksheet.Activate();
var result = true;
if (data != null)
{
for (int i = 1; i <= data.Count; i++)
{
for (int j = 1; j <= data[i - 1].Count; j++)
{
worksheet.Cells[i, j] = data[i - 1][j - 1];
}
}
foreach (var entity in excelColorEntities)
{
Range range = worksheet.Range[worksheet.Cells[entity.RowIndex, entity.ColumnIndex], worksheet.Cells[entity.RowIndex, entity.ColumnIndex]];
range.Interior.Color = entity.XlRgbColor;
}
worksheet.SaveAs(filename, fileFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//worksheet.SaveAs(filename, fileFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing,Type.Missing,Type.Missing);
}
else
{
result = false;
}
workbook.Close();
xlApp.Quit();
return(result);
}
static void TransformSpreadSheet(string excelFile, XlFileFormat format, bool prompt)
{
foreach (var workSheet in GetWorkSheets(excelFile))
{
var sheet = workSheet as Worksheet;
var csvFileName = Path.GetFileNameWithoutExtension(excelFile) + "-" + sheet.Name + ".csv";
SaveWorkSheet(sheet, csvFileName, format, false, prompt);
}
}
static void SaveWorkSheet(Worksheet sheet, string csvFileName, XlFileFormat format, bool quiet, bool prompt)
{
if(!quiet) Console.Error.WriteLine("Saving worksheet {0} to file '{1}'", sheet.Name, csvFileName);
csvFileName = Path.Combine(Environment.CurrentDirectory, csvFileName);
if (File.Exists(csvFileName))
{
if (prompt)
{
Console.Error.Write("The file {0} exists. Replace (Y/n)?", Path.GetFileName(csvFileName));
var input = Console.ReadLine();
if (input == "n") return;
}
File.Delete(csvFileName);
}
sheet.SaveAs(csvFileName, format);
}
/// <summary>
/// Save workbook to new file name (optionally to different format)
/// </summary>
/// <param name="fileName">Full Path Name of file to save</param>
/// <param name="fileFormat">File format desired.</param>
/// <returns></returns>
public bool SaveWorkbookAs(string fileName, XlFileFormat fileFormat = XlFileFormat.xlOpenXMLWorkbook)
{
xlWorkbook.SaveAs(fileName, fileFormat);
return(true);
}
/// <summary>
/// 描述:修改方法名，由Save改为SaveAs，另存文件
/// 作者:张怡鹏
/// 创建日期:2009.11.5
/// </summary>
/// <returns></returns>
public bool SaveAs()
{
try
{
// 2012-08-10 张航宇
// 要求FileName包括后缀名，此处不将后缀名去掉
//string strName =m_sFileName.Substring(0,m_sFileName.LastIndexOf(('.')));
XlFileFormat xlFileFormat = new XlFileFormat();
xlFileFormat = m_bOffice2003Ver == true ? XlFileFormat.xlExcel8 : XlFileFormat.xlWorkbookDefault;
m_pWorkBook.SaveAs(m_sFileName, xlFileFormat, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
m_pWorkBook.Close(false, Nothing, Nothing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_pWorkBook);
m_pExcelApp.Quit();
m_pWorkBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_pExcelApp);
m_pExcelApp = null;
GC.Collect();
return true;
}
catch(Exception exp)
{
Hy.Common.Utility.Log.OperationalLogManager.AppendMessage(exp.ToString());
if (m_pWorkBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_pWorkBook);
}
if (m_pExcelApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_pExcelApp);
}
m_pWorkBook = null;
m_pExcelApp = null;
return false;
}
}
/// <summary>在另一不同文件中保存对工作簿所做的更改。
/// </summary>
/// <param name="FileName">一个表示要保存文件的文件名的字符串。可包含完整路径，如果不指定路径，Microsoft Excel 将文件保存到当前文件夹中。</param>
/// <param name="FileFormat">保存文件时使用的文件格式。要查看有效的选项列表，请参阅 FileFormat 属性。对于现有文件，默认采用上一次指定的文件格式；对于新文件，默认采用当前所用 Excel 版本的格式。</param>
/// <param name="Password">它是一个区分大小写的字符串（最长不超过 15 个字符），用于指定文件的保护密码。</param>
/// <param name="WriteResPassword">一个表示文件写保护密码的字符串。如果文件保存时带有密码，但打开文件时不输入密码，则该文件以只读方式打开。</param>
/// <param name="ReadOnlyRecommended">如果为 True，则在打开文件时显示一条消息，提示该文件以只读方式打开。</param>
/// <param name="CreateBackup">如果为 True，则创建备份文件。</param>
/// <param name="AccessMode">工作簿的访问模式。</param>
/// <param name="ConflictResolution">一个 XlSaveConflictResolution 值，它确定该方法在保存工作簿时如何解决冲突。如果设为 xlUserResolution，则显示冲突解决对话框。如果设为 xlLocalSessionChanges，则自动接受本地用户的更改。如果设为 xlOtherSessionChanges，则自动接受来自其他会话的更改（而不是本地用户的更改）。如果省略此参数，则显示冲突处理对话框。</param>
/// <param name="AddToMru">如果为 True，则将该工作簿添加到最近使用的文件列表中。默认值为 False。</param>
/// <param name="TextCodepage">不在美国英语版的 Microsoft Excel 中使用。</param>
/// <param name="TextVisualLayout">不在美国英语版的 Microsoft Excel 中使用。</param>
/// <param name="Local">如果为 True，则以 Microsoft Excel（包括控制面板设置）的语言保存文件。如果为 False（默认值），则以 Visual Basic for Applications (VBA) （Visual Basic for Applications (VBA)：Microsoft Visual Basic 的宏语言版本，用于编写基于 Microsoft Windows 的应用程序，内置于多个 Microsoft 程序中。） 的语言保存文件，其中 Visual Basic for Applications (VBA) 通常为美国英语版本，除非从中运行 Workbooks.Open 的 VBA 项目是旧的已国际化的 XL5/95 VBA 项目。</param>
public void SaveAs(string FileName,
XlFileFormat? FileFormat = null,
string Password = null,
string WriteResPassword = null,
bool? ReadOnlyRecommended = null,
bool? CreateBackup = null,
XlSaveAsAccessMode? AccessMode = null,
XlSaveConflictResolution? ConflictResolution = null,
bool? AddToMru = null,
object TextCodepage = null,
object TextVisualLayout = null,
bool? Local = null)
{
if (FileName == null || FileName == string.Empty)
throw new ArgumentNullException("The param \"FileName\" can't be a null value");
_objaParameters = new Object[12] {
FileName,
FileFormat == null ? System.Type.Missing : FileFormat,
Password == null ? System.Type.Missing : Password,
WriteResPassword == null ? System.Type.Missing : WriteResPassword,
ReadOnlyRecommended == null ? System.Type.Missing : ReadOnlyRecommended,
CreateBackup == null ? System.Type.Missing : CreateBackup,
AccessMode == null ? System.Type.Missing : AccessMode,
ConflictResolution == null ? System.Type.Missing : ConflictResolution,
AddToMru == null ? System.Type.Missing : AddToMru,
TextCodepage == null ? System.Type.Missing : TextCodepage,
TextVisualLayout == null ? System.Type.Missing : TextVisualLayout,
Local == null ? System.Type.Missing : Local
};
_objWorkbook.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, _objWorkbook, _objaParameters);
}
/// <summary>
/// Save file in .xlsx format
/// </summary>
/// <param name="?"></param>
public static void SaveAs(string fileName)
{
XlFileFormat ff = new XlFileFormat();
if (DebugFileSaveOperation)
{
return; // debug
}
//if (DebugMx.True) // debug
//{
// Save();
// return;
//}
if (LogCalls)
{
DebugLog.Message("ExcelOp SaveAs " + fileName);
}
try
{
ShowExcelApp(true); // make visible before save so visible when reopened later
if (Lex.EndsWith(fileName, ".xlsx")) // Excel 2007+ (version 12+) Workbook (.xlsx) open XML format
{
ff = XlFileFormat.xlOpenXMLWorkbook; // 51 = xlsx
//fileName = Lex.Replace(fileName, ".xlsx", ".xlsm"); // debug
//ff = XlFileFormat.xlOpenXMLWorkbookMacroEnabled; // debug
}
else if (Lex.EndsWith(fileName, ".xlsm")) // Excel Macro-Enabled Workbook (.xlsm) open XML format
{
ff = XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
}
else if (Lex.EndsWith(fileName, ".xlsb")) // Excel Binary Workbook (.xlsx) (Excel 2007, version 12) binary format
{
ff = XlFileFormat.xlExcel12;
}
else if (Lex.EndsWith(fileName, ".xls")) // Excel 97-2003 (version 8-11) Workbook (*.xls) proprietary binary format
{
ff = XlFileFormat.xlExcel8;
}
else
{
ff = XlFileFormat.xlWorkbookDefault; // use default for others
}
XlBook.SaveAs(
Filename: fileName,
FileFormat: ff,
AccessMode: XlSaveAsAccessMode.xlNoChange);
}
catch (Exception ex)
{
throw new Exception("File: " + fileName + "\n\n" + ex.Message);
}
return;
}
/// <summary>将对图表或工作表的更改保存到另一不同文件中。
/// </summary>
/// <param name="Filename">Variant。一个字符串，表示要保存的文件名。可包含完整路径。如果不指定路径，Microsoft Excel 将文件保存到当前文件夹中。</param>
/// <param name="FileFormat">保存文件时使用的文件格式。要查看有效的选项列表，请参阅 FileFormat 属性。对于现有文件，默认采用上一次指定的文件格式；对于新文件，默认采用当前所用 Excel 版本的格式。</param>
/// <param name="Password">它是一个区分大小写的字符串（最长不超过 15 个字符），用于指定文件的保护密码。</param>
/// <param name="WriteResPassword">一个表示文件写保护密码的字符串。如果文件保存时带有密码，但打开文件时不输入密码，则该文件以只读方式打开。</param>
/// <param name="ReadOnlyRecommended">如果为 True，则在打开文件时显示一条消息，提示该文件以只读方式打开。</param>
/// <param name="CreateBackup">如果为 True，则创建备份文件。</param>
/// <param name="AddToMru">如果为 True，则将该工作簿添加到最近使用的文件列表中。默认值是 False。</param>
/// <param name="TextCodepage">不在美国英语版的 Microsoft Excel 中使用。</param>
/// <param name="TextVisualLayout">不在美国英语版的 Microsoft Excel 中使用。</param>
/// <param name="Local">如果为 True，则以 Microsoft Excel（包括控制面板设置）的语言保存文件。如果为 False（默认值），则以 Visual Basic for Applications (VBA) （Visual Basic for Applications (VBA)：Microsoft Visual Basic 的宏语言版本，用于编写基于 Microsoft Windows 的应用程序，内置于多个 Microsoft 程序中。） 的语言保存文件，其中 Visual Basic for Applications (VBA) 通常为美国英语版本，除非从中运行 Workbooks.Open 的 VBA 项目是旧的已国际化的 XL5/95 VBA 项目。</param>
public void SaveAs(string Filename, XlFileFormat? FileFormat = null, string Password = null, string WriteResPassword = null, bool? ReadOnlyRecommended = null, bool? CreateBackup = null, bool? AddToMru = null, object TextCodepage = null, object TextVisualLayout = null, bool? Local = null)
{
_objaParameters = new object[10] {
Filename,
FileFormat == null ? System.Type.Missing : FileFormat,
Password == null ? System.Type.Missing : Password,
WriteResPassword == null ? System.Type.Missing : WriteResPassword,
ReadOnlyRecommended == null ? System.Type.Missing : ReadOnlyRecommended,
CreateBackup == null ? System.Type.Missing : CreateBackup,
AddToMru == null ? System.Type.Missing : AddToMru,
TextCodepage == null ? System.Type.Missing : TextCodepage,
TextVisualLayout == null ? System.Type.Missing : TextVisualLayout,
Local == null ? System.Type.Missing : Local
};
_objChart.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, _objChart, _objaParameters);
}
private static void InnerSave(DataSet dataSet, XlFileFormat format, string outputPath, bool deleteOldFile)
{
object missing = Type.Missing;
ApplicationClass excelApp = null;
Workbook excelWorkbook = null;
try
{
if (deleteOldFile && File.Exists(outputPath))
{
File.Delete(outputPath);
}
// Create the Excel Application object
excelApp = new ApplicationClass();
// Create a new Excel Workbook
excelWorkbook = excelApp.Workbooks.Add(missing);
int sheetIndex = 0;
// Copy each DataTable
foreach (System.Data.DataTable dt in dataSet.Tables)
{
// Copy the DataTable to an object array
int rowCount = dt.Rows.Count;
int colsCount = dt.Columns.Count;
string[,] rawData = new string[rowCount + 1, colsCount];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int row = 0; row < rowCount; row++)
{
var dataRow = dt.Rows[row];
for (int col = 0; col < colsCount; col++)
{
rawData[row + 1, col] = dataRow[col].ToString();
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dt.Rows.Count + 1);
var range = excelSheet.get_Range(excelRange, Type.Missing);
range.Value2 = rawData;
// Mark the first row as BOLD
range = ((Range)excelSheet.Rows[1, Type.Missing]);
range.Font.Bold = true;
}
//excelApp.Application.AlertBeforeOverwriting = false;
excelApp.Application.DisplayAlerts = false;
// Save and Close the Workbook
excelWorkbook.SaveAs(
outputPath,
format,
missing, missing, missing, missing,
XlSaveAsAccessMode.xlExclusive,
missing, missing, missing, missing, missing
);
}
finally
{
#region Dispose
if (excelWorkbook != null)
{
excelWorkbook.Close(true, missing, missing);
excelWorkbook = null;
}
// Release the Application object
if (excelApp != null)
{
excelApp.Quit();
//释放进程
IntPtr t = new IntPtr(excelApp.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process.GetProcessById(k).Kill();
excelApp = null;
}
#endregion
}
}
// Main conversion routine
public static new int Convert(String inputFile, String outputFile, Hashtable options)
{
Boolean running = (Boolean)options["noquit"];
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
System.Object activeSheet = null;
Window activeWindow = null;
Windows wbWin = null;
Hashtable templatePageSetup = new Hashtable();
String tmpFile = null;
object oMissing = System.Reflection.Missing.Value;
Boolean nowrite = (Boolean)options["readonly"];
try
{
// Excel can be very slow to start up, so try to get the COM
// object a few times
int tries = 10;
app = new Microsoft.Office.Interop.Excel.Application();
while (tries > 0)
{
try
{
// Try to set a property on the object
app.ScreenUpdating = false;
}
catch (COMException)
{
// Decrement the number of tries and have a bit of a snooze
tries--;
Thread.Sleep(500);
continue;
}
// Looks ok, so bail out of the loop
break;
}
if (tries == 0)
{
ReleaseCOMObject(app);
return((int)ExitCode.ApplicationError);
}
app.Visible = true;
app.DisplayAlerts = false;
app.AskToUpdateLinks = false;
app.AlertBeforeOverwriting = false;
app.EnableLargeOperationAlert = false;
app.Interactive = false;
app.FeatureInstall = Microsoft.Office.Core.MsoFeatureInstall.msoFeatureInstallNone;
var onlyActiveSheet = (Boolean)options["excel_active_sheet"];
Boolean includeProps = !(Boolean)options["excludeprops"];
Boolean skipRecalculation = (Boolean)options["excel_no_recalculate"];
Boolean showHeadings = (Boolean)options["excel_show_headings"];
Boolean showFormulas = (Boolean)options["excel_show_formulas"];
Boolean isHidden = (Boolean)options["hidden"];
Boolean screenQuality = (Boolean)options["screen"];
Boolean updateLinks = !(Boolean)options["excel_no_link_update"];
int maxRows = (int)options[@"excel_max_rows"];
int worksheetNum = (int)options["excel_worksheet"];
int sheetForConversionIdx = 0;
activeWindow = app.ActiveWindow;
Sheets allSheets = null;
XlFileFormat fmt = XlFileFormat.xlOpenXMLWorkbook;
XlFixedFormatQuality quality = XlFixedFormatQuality.xlQualityStandard;
if (isHidden)
{
// Try and at least minimise it
app.WindowState = XlWindowState.xlMinimized;
app.Visible = false;
}
String readPassword = "";
if (!String.IsNullOrEmpty((String)options["password"]))
{
readPassword = (String)options["password"];
}
Object oReadPass = (Object)readPassword;
String writePassword = "";
if (!String.IsNullOrEmpty((String)options["writepassword"]))
{
writePassword = (String)options["writepassword"];
}
Object oWritePass = (Object)writePassword;
// Check for password protection and no password
if (Converter.IsPasswordProtected(inputFile) && String.IsNullOrEmpty(readPassword))
{
Console.WriteLine("Unable to open password protected file");
return((int)ExitCode.PasswordFailure);
}
app.EnableEvents = (bool)options["excel_auto_macros"];
workbooks = app.Workbooks;
// If we have no write password and we're attempting to open for writing, we might be
// caught out by an unexpected write password
if (writePassword == "" && !nowrite)
{
oWritePass = (Object)"FAKEPASSWORD";
try
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
catch (System.Runtime.InteropServices.COMException)
{
// Attempt to open it in read-only mode
workbook = workbooks.Open(inputFile, updateLinks, true, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
}
else
{
workbook = workbooks.Open(inputFile, updateLinks, nowrite, oMissing, oReadPass, oWritePass, true, oMissing, oMissing, oMissing, oMissing, oMissing, false, oMissing, oMissing);
}
// Add in a delay to let Excel sort itself out
AddCOMDelay(options);
// Unable to open workbook
if (workbook == null)
{
return((int)ExitCode.FileOpenFailure);
}
if (app.EnableEvents)
{
workbook.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
}
// Get any template options
SetPageOptionsFromTemplate(app, workbooks, options, ref templatePageSetup);
// Get the sheets
allSheets = workbook.Sheets;
// Try and avoid xls files raising a dialog
var temporaryStorageDir = Path.GetTempFileName();
File.Delete(temporaryStorageDir);
Directory.CreateDirectory(temporaryStorageDir);
// We will save as xlsb (binary format) since this doesn't raise some errors when processing
tmpFile = Path.Combine(temporaryStorageDir, Path.GetFileNameWithoutExtension(inputFile) + ".xlsb");
fmt = XlFileFormat.xlExcel12;
// Set up the print quality
if (screenQuality)
{
quality = XlFixedFormatQuality.xlQualityMinimum;
}
// If a worksheet has been specified, try and use just the one
if (worksheetNum > 0)
{
// Force us just to use the active sheet
onlyActiveSheet = true;
try
{
if (worksheetNum > allSheets.Count)
{
// Sheet count is too big
return((int)ExitCode.WorksheetNotFound);
}
if (allSheets[worksheetNum] is _Worksheet)
{
((_Worksheet)allSheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Worksheet)allSheets[worksheetNum]).Index;
}
else if (allSheets[worksheetNum] is _Chart)
{
((_Chart)allSheets[worksheetNum]).Activate();
sheetForConversionIdx = ((_Chart)allSheets[worksheetNum]).Index;
}
}
catch (Exception)
{
return((int)ExitCode.WorksheetNotFound);
}
}
if (showFormulas)
{
// Determine whether to show formulas
try
{
activeWindow.DisplayFormulas = true;
}
catch (Exception) { }
}
// Keep the windows hidden
if (isHidden)
{
wbWin = workbook.Windows;
if (null != wbWin)
{
if (wbWin.Count > 0)
{
wbWin[1].Visible = false;
}
}
if (null != activeWindow)
{
activeWindow.Visible = false;
}
}
// Keep track of the active sheet
if (workbook.ActiveSheet != null)
{
activeSheet = workbook.ActiveSheet;
}
// Large excel files may simply not print reliably - if the excel_max_rows
// configuration option is set, then we must close up and forget about
// converting the file. However, if a print area is set in one of the worksheets
// in the document, then assume the author knew what they were doing and
// use the print area.
// We may need to loop through all the worksheets in the document
// depending on the options given. If there are maximum row restrictions
// or formulas are being shown, then we need to loop through all the
// worksheets
if (maxRows > 0 || showFormulas || showHeadings)
{
var row_count_check_ok = true;
var found_rows = 0;
var found_worksheet = "";
// Loop through all the sheets (worksheets and charts)
for (int wsIdx = 1; wsIdx <= allSheets.Count; wsIdx++)
{
var ws = allSheets.Item[wsIdx];
// Skip anything that is not the active sheet
if (onlyActiveSheet)
{
// Have to be careful to treat _Worksheet and _Chart items differently
try
{
int itemIndex = 1;
if (activeSheet is _Worksheet)
{
itemIndex = ((Worksheet)activeSheet).Index;
}
else if (activeSheet is _Chart)
{
itemIndex = ((Microsoft.Office.Interop.Excel.Chart)activeSheet).Index;
}
if (wsIdx != itemIndex)
{
ReleaseCOMObject(ws);
continue;
}
}
catch (Exception)
{
if (ws != null)
{
ReleaseCOMObject(ws);
}
continue;
}
sheetForConversionIdx = wsIdx;
}
if (showHeadings && ws is _Worksheet)
{
PageSetup pageSetup = null;
try
{
pageSetup = ((Worksheet)ws).PageSetup;
pageSetup.PrintHeadings = true;
}
catch (Exception) { }
finally
{
ReleaseCOMObject(pageSetup);
}
}
// If showing formulas, make things auto-fit
if (showFormulas && ws is _Worksheet)
{
Range cols = null;
try
{
((_Worksheet)ws).Activate();
activeWindow.DisplayFormulas = true;
cols = ((Worksheet)ws).Columns;
cols.AutoFit();
}
catch (Exception) { }
finally
{
ReleaseCOMObject(cols);
}
}
// If there is a maximum row count, make sure we check each worksheet
if (maxRows > 0 && ws is _Worksheet)
{
// Check for a print area
var pageSetup = ((Worksheet)ws).PageSetup;
var printArea = pageSetup.PrintArea;
ReleaseCOMObject(pageSetup);
if (string.IsNullOrEmpty(printArea))
{
// There is no print area, check that the row count is <= to the
// excel_max_rows value. Note that we can't just take the range last
// row, as this may return a huge value, rather find the last non-blank
// row.
var row_count = 0;
var range = ((Worksheet)ws).UsedRange;
if (range != null)
{
var rows = range.Rows;
if (rows != null && rows.Count > maxRows)
{
var cells = range.Cells;
if (cells != null)
{
var cellSearch = cells.Find("*", oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, false, oMissing, oMissing);
// Make sure we actually get some results, since the worksheet may be totally blank
if (cellSearch != null)
{
row_count = cellSearch.Row;
found_worksheet = ((Worksheet)ws).Name;
}
ReleaseCOMObject(cellSearch);
}
ReleaseCOMObject(cells);
}
ReleaseCOMObject(rows);
}
ReleaseCOMObject(range);
if (row_count > maxRows)
{
// Too many rows on this worksheet - mark the workbook as unprintable
row_count_check_ok = false;
found_rows = row_count;
Converter.ReleaseCOMObject(ws);
break;
}
}
} // End of row check
Converter.ReleaseCOMObject(ws);
}
// Make sure we are not converting a document with too many rows
if (row_count_check_ok == false)
{
throw new Exception(String.Format("Too many rows to process ({0}) on worksheet {1}", found_rows, found_worksheet));
}
}
// Allow for re-calculation to be skipped
if (skipRecalculation)
{
app.Calculation = XlCalculation.xlCalculationManual;
app.CalculateBeforeSave = false;
}
workbook.SaveAs(tmpFile, fmt, Type.Missing, Type.Missing, Type.Missing, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing);
if (onlyActiveSheet)
{
// Set up a delegate function for times we want to print
PrintDocument printFunc = delegate(string destination, string printer)
{
((Worksheet)activeSheet).PrintOutEx(ActivePrinter: printer, PrintToFile: true, PrToFileName: destination);
};
if (sheetForConversionIdx > 0)
{
activeSheet = allSheets.Item[sheetForConversionIdx];
}
if (activeSheet is _Worksheet)
{
var wps = ((_Worksheet)activeSheet).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
if (String.IsNullOrEmpty((string)options["printer"]))
{
try
{
((Worksheet)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
if (!String.IsNullOrEmpty((string)options["fallback_printer"]))
{
PrintToGhostscript((string)options["fallback_printer"], outputFile, printFunc);
}
else
{
throw;
}
}
}
else
{
PrintToGhostscript((string)options["printer"], outputFile, printFunc);
}
ReleaseCOMObject(wps);
}
else if (activeSheet is _Chart)
{
var wps = ((_Chart)activeSheet).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
((Microsoft.Office.Interop.Excel.Chart)activeSheet).ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
ReleaseCOMObject(wps);
}
else
{
return((int)ExitCode.UnknownError);
}
AddCOMDelay(options);
}
else
{
PrintDocument printFunc = delegate(string destination, string printer)
{
workbook.PrintOutEx(ActivePrinter: printer, PrintToFile: true, PrToFileName: destination);
};
if (HasTemplateOption(options))
{
// Set up the template page setup options on all the worksheets
// in the workbook
var worksheets = workbook.Worksheets;
for (int wsIdx = 1; wsIdx <= worksheets.Count; wsIdx++)
{
var ws = worksheets[wsIdx];
var wps = (ws is _Worksheet) ? ((_Worksheet)ws).PageSetup : ((_Chart)ws).PageSetup;
SetPageSetupProperties(templatePageSetup, wps);
ReleaseCOMObject(wps);
ReleaseCOMObject(ws);
}
ReleaseCOMObject(worksheets);
}
if (String.IsNullOrEmpty((string)options["printer"]))
{
try
{
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,
outputFile, quality, includeProps, false, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
if (!String.IsNullOrEmpty((string)options["fallback_printer"]))
{
PrintToGhostscript((string)options["fallback_printer"], outputFile, printFunc);
}
else
{
throw;
}
}
}
else
{
PrintToGhostscript((string)options["printer"], outputFile, printFunc);
}
}
ReleaseCOMObject(allSheets);
ReleaseCOMObject(fmt);
ReleaseCOMObject(quality);
return((int)ExitCode.Success);
}
catch (COMException ce)
{
if ((uint)ce.ErrorCode == 0x800A03EC)
{
return((int)ExitCode.EmptyWorksheet);
}
else
{
Console.WriteLine(ce.Message);
return((int)ExitCode.UnknownError);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return((int)ExitCode.UnknownError);
}
finally
{
if (workbook != null)
{
ReleaseCOMObject(activeSheet);
ReleaseCOMObject(activeWindow);
ReleaseCOMObject(wbWin);
GC.Collect();
GC.WaitForPendingFinalizers();
// Excel sometimes needs a bit of a delay before we close in order to
// let things get cleaned up
workbook.Saved = true;
CloseExcelWorkbook(workbook);
}
if (!running)
{
if (workbooks != null)
{
workbooks.Close();
}
if (app != null)
{
((Microsoft.Office.Interop.Excel._Application)app).Quit();
}
}
// Clean all the COM leftovers
ReleaseCOMObject(workbook);
ReleaseCOMObject(workbooks);
ReleaseCOMObject(app);
GC.Collect();
GC.WaitForPendingFinalizers();
if (tmpFile != null && File.Exists(tmpFile))
{
System.IO.File.Delete(tmpFile);
// Remove the temporary path to the temp file
Directory.Delete(Path.GetDirectoryName(tmpFile));
}
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
String workbookFullName = FilePath.Get(context);
String newFileName = NewFileName.Get(context);
string newfilePath = NewFilePath.Get(context);
string fileFormat = ToDescriptionString(FileFormatType);
String[] fileFormatTypeStr = FileFormatType.ToString().TrimStart().Split('_');
String extension = String.Concat(".", fileFormatTypeStr[0]);
string newFileNameWithExtension = String.Concat(newFileName, extension);
string workbookName = string.Empty;
dynamic xlWorkbook = null;
bool excelFileVisible = false;
if (true == NeedToOpen)
{
excelFileVisible = true;
}
// Combine 2 path parts.
string workbooknameas = Path.Combine(newfilePath, newFileName);
string workbooknameasWithExtension = Path.Combine(newfilePath, newFileNameWithExtension);
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
workbookName = Path.GetFileName(workbookFullName);
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
xlWorkbook = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
if (File.Exists(workbooknameasWithExtension))
{
if (true == IsOverride)
{
// xlWorkbook.SaveAs(workbooknameas, FileFormatType, Missing.Value, Missing.Value, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value);
XlFileFormat fm = (XlFileFormat)Enum.Parse(typeof(XlFileFormat), fileFormat);
xlWorkbook.SaveAs(workbooknameas, fm, Missing.Value, Missing.Value, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value);
}
else
{
Log.Logger.LogData("The file \"" + newFileName + "\" is already exists in activity Excel_Workbook_SaveAs", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
else
{
// xlWorkbook.SaveAs(workbooknameas, XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value);
//xlWorkbook.SaveAs(workbooknameas, FileFormatType, Missing.Value,Missing.Value, false, false, XlSaveAsAccessMode.xlNoChange,XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value);
XlFileFormat fm = (XlFileFormat)Enum.Parse(typeof(XlFileFormat), fileFormat);
xlWorkbook.SaveAs(workbooknameas, fm, Missing.Value, Missing.Value, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlUserResolution, true, Missing.Value, Missing.Value, Missing.Value);
}
if (true == NeedToClose)
{
xlWorkbook.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
xlWorkbook.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
xlWorkbook.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Workbook_SaveAs", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_Workbook_SaveAs", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
