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