Code stage error while converting excel to html

[HongJooChoi]

Hi, Community

We're testing the code stage to convert the Excel to HTML, and then to Image. The code itself works fine when it's run in the Visual Studio but it cases a compilor error when it's run in Blue Prism as below:

Compiler error at line 9: 'Range' does not contain a definition for 'ToHtml' and no accessible extension method 'ToHtml' accepting a first argument of type 'Range' could be found (are you missing a using directive or an assembly reference?)

When tested line by line, it seems like the error is coming from " string html = range.ToHtml();". 

Can anyone help us out to figure out the root cause and how to fix it?

The full code is shown below:

-------------------------------

using System;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Drawing;

namespace ExcelToHtmlAndImage
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create Excel Instance
            Application excel = new Application();
            excel.Visible = false;

            // Open Excel file
            Workbook workbook = excel.Workbooks.Open("C:\\test\\sample.xlsx");

            // Select the sheet to be converted
            Workshet worksheet = (Worksheet)workbook.Sheets["Sheet1"];

            // Select the range 
            Range range = worksheet.Range["A1:E10"];

            // Convert to HTML
            string html = range.ToHtml();

            // Save as HTML file
            File.WriteAllText("C:\\test\\sample.html", html);

            // Convert to Image
            Image image = range.ToImage();

            // Save Image file
            image.Save("C:\\test\\sample.jpg", System.Drawing.Imaging.ImageFormat.Jpeg);

            // Close Excel workbook
            workbook.Close(false);

            // Close Excel Instance
            excel.Quit();
        }
    }
}

-------------------------------

I believe there's no problem in the configuration.

Best regards

------------------------------
HongJoo Choi
------------------------------

[michaeloneil]

Hi @HongJooChoi 

Is there any reason you cant open the file in excel and do save as for the conversion? i'm assuming you want to save as a single worksheet from the file?

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[devneetmohanty07 · Accepted solution]

Hi @HongJooChoi 

The error you are facing seems to be a correct error as there is no function called as either .ToHtml() or .ToImage() associated with the Microsoft.Office.Interop.Excel.Range object. You can refer to the official MSDN page for the same as well. My best guess is that someone might have implemented a custom function for this which you have not referenced in your code while trying to implement the same on Blue Prism side.

However, based on your requirements, I have written a code which can help you to achieve the same results at your end. You can refer to the below steps for more details:

1) First create a new business object and have the following external references and namespace imports added:

2) Now, create a new action named: 'Convert Excel To HTML & Image' action with the following input arguments:

• In_ExcelFilePath: The file path for the excel spreadsheet where the range needs to be converted to HTML and JPEG format.
  
  
• In_ExcelSheetName: The sheet name within the excel spreadsheet where the range needs to be converted to HTML and JPEG format.
  
  
• In_ExcelRange: The range of the data that needs to be converted to HTML and JPEG format.
  
  
• In_HTMLFilePath: The file path for the output HTML file that needs to be generated.
  
  
• In_ImageFilePath: The file path for the output image file that needs to be generated in JPEG format.

3) Now, you can use the below code in the code stage with the input arguments:

// Create Excel Instance
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;

// Open Excel file
Workbook workbook = excel.Workbooks.Open(In_ExcelFilePath);

// Select the sheet to be converted
Worksheet worksheet = (Worksheet)workbook.Sheets[In_ExcelSheetName];

// Select the range
Range range = worksheet.Range[In_ExcelRange];

// Copy the range to the clipboard
range.Copy();

// Create a new HTML format object
System.Windows.Forms.DataObject htmlData = new System.Windows.Forms.DataObject();
htmlData.SetData(DataFormats.Html, Clipboard.GetText(TextDataFormat.Html));

// Return the HTML data as a string
string html = htmlData.GetData(DataFormats.Html).ToString();

// Save as HTML file
System.IO.File.WriteAllText(In_HTMLFilePath, html);

// Copy the range to a new worksheet and get its image
Worksheet copySheet = (Worksheet)workbook.Worksheets.Add();
range.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);

// Get the image from the clipboard
Image image = null;
if (Clipboard.ContainsImage())
{
	image = Clipboard.GetImage();
}

copySheet.Delete();

// Save the image to a file
if (image != null)
	{
		image.Save(In_ImageFilePath, System.Drawing.Imaging.ImageFormat.Jpeg);
	}

// Close Excel workbook
workbook.Close(false);

// Close Excel Instance
excel.Quit();

You can now publish the action and check from Process Studio if you want.

Here, for my supplied arguments as below, you can see the sample output generated:

Inputs: 

Outputs:

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[HongJooChoi]

Hi, @devneetmohanty07 

The issue has been resolved.

I really appreciate your help !!

Best regards

------------------------------
HongJoo Choi
------------------------------