This project has moved and is read-only. For the latest updates, please go here.

NetOffice Saving Excel Sheet to .PDF

Dec 24, 2014 at 5:01 PM
Edited Jan 13, 2015 at 2:43 PM
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.CSharp;
using System.Runtime.InteropServices;
using System.IO;
using NetOffice.OfficeApi.Enums;
using Excel = NetOffice.ExcelApi;

namespace ConvertSheetsToPDFs
{
    public class Program
    {
        public static void Main(string[] args)
        {
            string filePath1 = @"\\Server\Folder\SomeFile.xls";
            string saveSheet = "";
            DateTime date = DateTime.Today;
            string format = "MM-dd-yy";
            string stringDate = date.ToString(format);
            Excel.Worksheet worksheet;
            Excel.Application excel = new Excel.Application();
            excel.DisplayAlerts = false;
            excel.AskToUpdateLinks = false;
            excel.Visible = false;
            excel.ScreenUpdating = false;
            Excel.Workbook workbook = excel.Workbooks.Open(filePath1);
            for (int j = 0; j < excel.Worksheets.Count; j++)
            {
                worksheet = excel.Worksheets.ElementAt(j) as Excel.Worksheet;
                if (worksheet.Name == "SomeWorkSheet")
                {
                    Excel.Workbook workbook2 = new Excel.Workbook(worksheet);
                    saveSheet = @"\\Server\Folder\ARCHIVES\" + worksheet.Name + " " + stringDate + ".pdf";
                    object sheet = String.Format(saveSheet);
                    workbook2.ExportAsFixedFormat(Excel.Enums.XlFixedFormatType.xlTypePDF, sheet, Excel.Enums.XlFixedFormatQuality.xlQualityStandard, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
            }
            
            string end = Console.ReadLine();
            excel.ActiveWorkbook.Close();
            excel.Quit();
            excel.Dispose();
        }
    }
}
Dec 25, 2014 at 8:02 AM
the easiest/fastest way(in performance) is to use copy&paste.

something like this:
worksheet .Range["A1:G3"].Copy();
word .Selection.Range.PasteSpecial();

you can set various format arguments in the PasteSpecial method:
see documentation: http://msdn.microsoft.com/de-de/library/microsoft.office.interop.word.wdpastedatatype%28v=office.11%29.aspx

*Sebastian
Dec 26, 2014 at 2:17 PM
Edited Dec 29, 2014 at 3:13 PM
N/A
Dec 26, 2014 at 7:27 PM
Edited Dec 29, 2014 at 3:13 PM
N/A
Dec 29, 2014 at 3:13 PM
Edited Dec 30, 2014 at 2:20 PM
So based on my redone code, I keep getting a error on my worksheet.ExportAsFixedFormat. The error I get is An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in NetOffice.dll. What causes that exception?

I believe that is my only issue at this point. I think based on the code I should get a .pdf of each worksheet that I have in my workbook.
Dec 30, 2014 at 10:38 AM
please check the inner exception for error details. (NO wrap all office exceptions in COMException)
btw: you have to use the Word.Document.ExportAsFixedFormat method to save a word document as pdf.

*Sebastian
Dec 30, 2014 at 2:26 PM
Edited Dec 30, 2014 at 2:33 PM
Would I still need to use a .doc to export my worksheet to a .pdf? I thought with worksheet.ExportAsFixedFormat(Excel.Enums.XlFixedFormatType.xlTypePDF, Sheet, Excel.Enums.XlFixedFormatQuality.xlQualityStandard, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); I could take each worksheet I want and directly make it into a .pdf. I tried using the .doc because I didn't see a option to turn a worksheet into a .pdf, so I thought I could instead turn it into a .doc. I finally found what seems to be the way to export a worksheet to a converted .pdf. So I am trying to make it work now.

This is the code I had made with Aspose but I am trying to get off it because the software is way to expensive
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.CSharp;
using System.Runtime.InteropServices;
using Aspose.Pdf;
using Aspose.Cells;

namespace ConvertNewSheetsToPDFs
{
    public class Program
    {
        public static void Main(string[] args)
        {
            //Get the Excel file path
            string filePath = @"\\Server\Folder\NEW SHEETS.xls";
            DateTime date = new DateTime();
            date = DateTime.Today;
            string format = "M-d-yy";
            string stringDate = date.ToString(format);
            //File from its location
            Workbook workbook = new Workbook(filePath);
            //Make all sheets invisible except first worksheet because you have to keep 1 worksheet visible
            for (int i = 1; i < workbook.Worksheets.Count; i++)
            {
                workbook.Worksheets[i].IsVisible = false;
            }
            //Take Pdfs of each sheet
            for (int j = 1; j < workbook.Worksheets.Count; j++)
            {
                //Make all sheets invisible except first worksheet
                workbook.Worksheets[0].IsVisible = true;
                if(j != 1)
                { 
                    workbook.Worksheets[j - 1].IsVisible = false;
                }
                Worksheet ws = workbook.Worksheets[j];
                if (ws.Name.Equals("Some Worksheet")
                {
                    Console.Out.WriteLine(ws.Name);
                    workbook.Worksheets[j].IsVisible = true;
                    workbook.Worksheets[0].IsVisible = false;
                    workbook.Save(@"\\Server\\Folder\\" + ws.Name + " " + stringDate + ".pdf", Aspose.Cells.SaveFormat.Pdf);
                }
            }
        }
    }
}
This code with Apose gives me what I really want. Gives 5 different .pdf with only the data from one worksheet in each.
Jan 13, 2015 at 2:46 PM
Based on what I just updated. Would you say it is what I am wanting. I still get that exception during the export and it doesn't explain why it is giving that exception. This should create a new workbook each time and put in only the desired worksheet into it a print that into a .pdf.