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