C# Excel Interop convert excel file to pdf file is not properly converting

Question

I am making an application that can handle input excel files and save them as a new file, after that they need to be converted to a pdf. Lastly they are attached to an email and sent away. The issue I'm running into is that the pdf is not properly converted. I end up with 2 pages each containing a vertical half of the page aswell as the page being twice as long.

This is a preview:

What I want is more like this:

I used an online converter to get this image.

Lastly this is the code I am using to import a layout file, edit data on the file, save it and then export it as a pdf.

        Application oExcel = new Application();
        Workbook oBook = oExcel.Workbooks.Open(string.Format("{0}\\ATH-ICT\\Waspbane Bestellingen\\Resources\\Layout.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.ProgramFilesX86)));

        Worksheet oSheet = (Worksheet)oBook.Worksheets[1];

        oSheet.Range["D3"].Value = customer.Name;
        oSheet.Range["D4"].Value = customer.Address;
        oSheet.Range["D5"].Value = string.Format("{0} {1}", customer.Postal, customer.City);
        oSheet.Range["J3"].Value = DateTime.Now.Year + orderid.ToString("D4");
        oSheet.Range["J5"].Value = DateTime.Now.ToString("dd/MM/yyyy");

        int productNumber = 11;
        foreach (Order order in customer.Orders)
        {
            oSheet.Range["B" + productNumber].Value = order.Amount;
            oSheet.Range["D" + productNumber].Value = "st";
            oSheet.Range["E" + productNumber].Value = order.Product.Name;
            oSheet.Range["I" + productNumber].Formula = string.Format("={0}/{1}", order.Product.Price, 1.21);
            oSheet.Range["J" + productNumber].Formula = "=PRODUCT(B11,I11)";
            oSheet.Range["K" + productNumber].Value = 1;
                
            productNumber += 2;
        }

        oSheet.Range["J36"].Value = shipping;
        oSheet.Range["A43"].Value = string.Format("Betaald met {0}", method);

        oBook.SaveAs(string.Format("{0}\\Factuur {1}{2:D4}.xlsx", savelocation, DateTime.Now.Year, orderid));

        oBook.Close();
        oBook = null;
        oExcel.Quit();
        oExcel = null;

        oExcel = new Application();
        oBook = oExcel.Workbooks.Open(string.Format("{0}\\Factuur {1}{2:D4}.xlsx", savelocation, DateTime.Now.Year, orderid));
        oSheet = (Worksheet)oExcel.Worksheets[1];
        oSheet.PageSetup.FitToPagesWide = 1;
        oSheet.PageSetup.FitToPagesTall = 1;
        oSheet.PageSetup.Zoom = false;
        
        oBook.ExportAsFixedFormat(
            XlFixedFormatType.xlTypePDF,
            string.Format("{0}\\Factuur {1}{2:D4}.pdf", savelocation, DateTime.Now.Year, orderid),
            XlFixedFormatQuality.xlQualityStandard, true, false, Type.Missing, Type.Missing, false
        );

        oBook.Close();
        oBook = null;
        oExcel.Quit();
        oExcel = null;

        Marshal.ReleaseComObject(oSheet);

        GC.Collect();
        GC.WaitForPendingFinalizers();

At the end of the script I also have a few lines regarding the garbage collector and the marshal to try and get rid of the left behind process in task manager but it doesn't seem to be working.

Also, this code is not the most efficiënt. I just want the basic thing working.

EDIT:

In case you want to reproduce my example with the same excel file I now provided a download link down below to the exact excel file that I use:

https://drive.google.com/open?id=0B6hUijkN5GM3QmZ3YkdNd1l0ejg

Order might matter; every answer I've seen related to this issue they set `PageSetup.Zoom = false;` *before* setting `FitToPagesWide/Tall`. I'm no VSTO or COM guru but I could see that being your problem if the property setter checks the zoom property when it's set rather than in some later call. — Quantic, Jul 12 '16 at 18:52
I have been testing with your file and your code and cannot reproduce the problem, it always saves a pdf of 1 page for me. Note that I replaced all occurrences of `order`, `customer`, `shipping`, `orderid` etc.. with random strings or numbers. Also I've been testing with Excel 2010. — Quantic, Jul 12 '16 at 20:28
As for "the garbage collector and the marshal to try and get rid of the left behind process in task manager but it doesn't seem to be working", you have to close every single COM object, and you are making many more objects than you realize; pretty much every dot operator `.` creates a new object. Read [here](http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects). E.g., `Workbook oBook = oExcel.Workbooks.Open` created a `Workbooks` COM object that you are not cleaning up. You will have to research if `.Range` and `.PageSetup` creates COM objects. — Quantic, Jul 12 '16 at 20:36
@Quantic Honestly I don't see why the excel version would matter because I created this code once before but in VB. Back then it worked fine. But both versions run on Windows 10 and Excel 2013. Second, thanks for the insight with the COM objects. I didn't realize this. Thank you. — Thodor12, Jul 12 '16 at 20:47

score 0 · Answer 1 · answered Jul 13 '16 at 12:29

Unfortunately I didn't get it to work the way you normally do it using the Excel Interop but I found a great alternative.

I used Spire.XLS to get it working. It is a free service (up to 5 sheets and 150 rows per excel sheet) that can do everything and even more then the Excel Interop.

http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html

C# Excel Interop convert excel file to pdf file is not properly converting

1 Answers1