warz

using System;
using System.IO;

using ExcelInterop = Microsoft.Office.Interop.Excel;

namespace UniDocConverter.FileTypes.Office
{
    public static class Excel
    {
        public static void ToTiff(PrinterDriver printer, String input, String output)
        {
            // Create a Excel's Application object
            ExcelInterop.Application ExcelApp = new ExcelInterop.ApplicationClass();

            Object ReadOnly = true;
            Object Missing = Type.Missing;
            Object Preview = false;
            Object SaveChanges = false;

            // Open the document from a file
            ExcelInterop.Workbook Workbook = ExcelApp.Workbooks.Open(input, 2, ReadOnly, Missing, Missing, Missing, true, Missing, Missing, Missing, Missing, Missing, Missing, Missing, true);

            // Set printer settings for excel files as specified in standards doc
            printer.Orientation = UDC.PageOrientationID.PO_LANDSCAPE;

            // Loop through each worksheet
            foreach (ExcelInterop.Worksheet Worksheet in Workbook.Worksheets)
            {
                Console.WriteLine("Processing worksheet {0} ({1})", Worksheet.Index, Worksheet.Name);

                // Activate this sheet
                Worksheet.Activate();

                // Grab the page setup object
                Microsoft.Office.Interop.Excel.PageSetup PageSetup = Worksheet.PageSetup;

                // Set specific settings
                PageSetup.Zoom = 80;
                PageSetup.PrintTitleRows = "";
                PageSetup.PrintTitleColumns = "";
                PageSetup.Order = Microsoft.Office.Interop.Excel.XlOrder.xlOverThenDown;
                PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperLetter;
                PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                PageSetup.PrintComments = Microsoft.Office.Interop.Excel.XlPrintLocation.xlPrintNoComments;
                PageSetup.BlackAndWhite = true;
                PageSetup.PrintHeadings = true;
                PageSetup.CenterHorizontally = true;
                PageSetup.CenterVertically = true;

                Console.WriteLine(" - Footer: {0}", PageSetup.RightFooter);

                // Force the sheet to be visible
                Worksheet.Visible = Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible;

                // Explicitly set the PrintArea to the UsedRange
                String formula = Worksheet.UsedRange.get_AddressLocal(Worksheet.UsedRange.Rows.Count, Worksheet.UsedRange.Columns.Count,
                                                      Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, null, Worksheet.UsedRange);
                PageSetup.PrintArea = formula;
                Console.WriteLine(" - Used range: {0}", PageSetup.PrintArea);

                // Attempt to force the printer to use 300 dpi quality
                Int32 quality_x = Int32.Parse("" + Worksheet.PageSetup.get_PrintQuality(1));
                Int32 quality_y = Int32.Parse("" + Worksheet.PageSetup.get_PrintQuality(2));
                if ((quality_x != 200) || (quality_y != 200))
                {
                    try
                    {
                        object[] x = new Object[] { 1, 2 };
                        object[] y = new Object[] { 200, 200 };
                        PageSetup.set_PrintQuality(x, y);

                        Console.WriteLine(" - Print quality adjusted from {0}x{1} to {2}x{3}", quality_x, quality_y,
                                                                                            PageSetup.get_PrintQuality(1),
                                                                                            PageSetup.get_PrintQuality(2));
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(" - Unable to adjust print quality: {0}", ex.Message);
                    }
                }

                // Select the used range
                try
                {
                    Worksheet.UsedRange.Select();
                }
                catch (Exception)
                {
                    //Console.WriteLine(" - Caught except while selecting used range on {0}", Worksheet.Name);
                }

                // Try to ungroup any groupings
                try
                {
                    Worksheet.UsedRange.Ungroup();
                }
                catch (Exception)
                {
                    //Console.WriteLine(" - Caught exception while ungrouping items on {0}", Worksheet.Name);
                }

                // Try to unhide all rows and columns
                try
                {
                    Worksheet.UsedRange.EntireColumn.Hidden = false;
                    Worksheet.UsedRange.EntireRow.Hidden = false;
                }
                catch (Exception)
                {
                    //Console.WriteLine(" - Caught exception while unhiding rows and columns on {0}", Worksheet.Name);
                }

                // Try to apply autofit to rows and columns
                try
                {
                    Worksheet.UsedRange.EntireRow.AutoFit();
                    Worksheet.UsedRange.EntireColumn.AutoFit();
                }
                catch (Exception)
                {
                    //Console.WriteLine(" - Caught exception while applying autofit to rows and columns on {0}", Worksheet.Name);
                }

                // Try to apply wrap and disable shrink
                try
                {
                    Worksheet.UsedRange.WrapText = true;
                    Worksheet.UsedRange.ShrinkToFit = false;
                }
                catch (Exception)
                {
                    //Console.WriteLine(" - Caught exception while applying wrap and shrink on {0}", Worksheet.Name);
                }

                // Attempt to replace certain key words within the excel file.
                try
                {
                    ExcelApp.DisplayAlerts = false;
                    if (Worksheet.UsedRange.Find("=Today()", Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing) != null)
                        Worksheet.UsedRange.Replace("=Today()", "", Type.Missing, Type.Missing, false, false, Type.Missing, Type.Missing);

                    if (Worksheet.UsedRange.Find("=Now()", Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing) != null)
                        Worksheet.UsedRange.Replace("=Now()", "", Type.Missing, Type.Missing, false, false, Type.Missing, Type.Missing);

                    if (Worksheet.UsedRange.Find("=CELL(\"filename\")", Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing) != null)
                        Worksheet.UsedRange.Replace("=CELL(\"filename\")", "", Type.Missing, Type.Missing, false, false, Type.Missing, Type.Missing);
                }
                catch (Exception)
                {
                    //Console.WriteLine(" - Caught exception while replacing cell contents on {0}", Worksheet.Name);
                }
            }

            // Send the workbook to the printer driver
            Workbook.PrintOut(Missing, Missing, Missing, Preview, "Universal Document Converter", Missing, Missing, Missing);

            //Close the spreadsheet without saving changes, and close Excel
            Workbook.Close(SaveChanges, Missing, Missing);
            ExcelApp.Quit();
        }
    }
}