Excel 2003 VSTO convert to PDF

Question

I have an excel workbook vsto solution that needs to generate a pdf copy of one of its sheets as output.

I have a license for abcdpdf .net and tried outputting to html, then using abcpdf to convert the html to pdf, but the excel html markup tries to emulate excel with all 4 worksheets with horrible markup. It also messes up the colors (silver background across entire workbook).

Any suggestions?

Here is the code I'm currently using to generate the html file:

FileInfo excelDoc = new FileInfo(Globals.ThisWorkbook.Path + @"\Document.html");

Globals.Sheet2.SaveAs(excelDoc.FullName,
    Excel.XlFileFormat.xlHtml, missing, missing, false, false,
    Excel.XlSaveAsAccessMode.xlNoChange,
    missing, missing, missing);

If I hack away some of the html header tags manually, I can get abcdpf to accept it, but the formatting is a bit off and this solution seems sub optimal.

Thanks in advance.

Answer 1

Solution found: store excel sheet as XPS print out. Import XPS printout into pdf document.

MyImportOperation code adapted from abcpdf XPS sample source code.

    public void SaveSheetToPdf(FileInfo outputPDF)
    {
        FileInfo documentFile = new FileInfo(Globals.ThisWorkbook.Path + @"\tempDoc.xps");
        if (documentFile.Exists)
            documentFile.Delete();

        Globals.Sheet2.PrintOut(1, missing, 1, false, "Microsoft XPS Document Writer", true, false, documentFile.FullName);

        Doc theDoc = new Doc();                

        try
        {
            MyImportOperation importOp = new MyImportOperation(theDoc);
            importOp.Import(documentFile);            
        }
        catch (Exception ex)
        {
            throw new Exception("Error rendering pdf. PDF Source XPS Path: " + investmentPlanXPSPath, ex);
        }

        theDoc.Save(outputPDF.FullName);
    }

    public class MyImportOperation
    {
       private Doc _doc = null;
       private double _margin = 10;
       private int _pagesAdded = 0;

       public MyImportOperation(Doc doc)
       {
           _doc = doc;
       }

    public void Import(string inPath)
    {
        using (XpsImportOperation op = new XpsImportOperation())
        {
            op.ProcessingObject += Processing;
            op.ProcessedObject += Processed;
            op.Import(_doc, inPath);
        }
    }

    public void Processing(object sender, ProcessingObjectEventArgs e)
    {

        if (e.Info.SourceType == ProcessingSourceType.PageContent)
        {       
            _doc.Page = _doc.AddPage();     
            e.Info.Handled = true;
            _pagesAdded++;
        }
    }

    public void Processed(object sender, ProcessedObjectEventArgs e)
    {
        if (e.Successful)
        {
            PixMap pixmap = e.Object as PixMap;
            if (pixmap != null)
                pixmap.Compress();      
        }
    }

}

Answer 2

Another solution is using commercial Excel to PDF .Net library. For example to convert sheet 1 and 3 from excel workbook to PDF you will need this C# code:

SautinSoft.XlsToPdf x = new SautinSoft.XlsToPdf();
x.Sheets.Custom(new int[] { 1, 3 });
x.ConvertFile(@"d:\Workbook.xls", @"d:\Hardcopy.pdf");

The library is not free, but it's not expensive.