Convert the data from excel to html

Kunal_Jain · November 7, 2024, 6:33am

Hi,
I have an excel file that contains some columns
I need to save the data from the file to an html file.
The columns are Responsibilites and Requirements.
And the file name will be saved based on the value from Code.
Here I have attached the resulting sheet. The format on how the result sheet will look like
2650.html (32 Bytes)
Here is the file from where we need to save the data
Book1.xlsx (9.6 KB)

naveen.s · November 7, 2024, 6:39am

@Kunal_Jain ,

Try below:
To save data from an Excel file to an HTML file in UiPath, you’ll need to perform several steps. Below is a high-level outline and a sample workflow that you can follow, along with a template for how the HTML output will be structured.

Steps to Follow in UiPath:

Read the Excel File:
- Use the “Excel Application Scope” to open the Excel file.
- Use the “Read Range” activity to read the data from your Excel sheet into a DataTable.
Process the Data:
- Access the DataTable to extract the relevant columns (Responsibilities, Requirements, and Code).
Create HTML Content:
- Construct the HTML string based on the data extracted from the DataTable. You will need to loop through each row of the DataTable to build the HTML structure.
Save HTML File:
- Use the “Write Text File” activity to save the resulting HTML string to a file. The name of the file will be based on the value from the “Code” column.

Sample HTML Format

Here’s a simple example of how the resulting HTML might look:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Job Responsibilities and Requirements</title>
</head>
<body>
    <h1>Job Title: [Job Title]</h1>
    <h2>Responsibilities</h2>
    <ul>
        <li>Responsibility 1</li>
        <li>Responsibility 2</li>
        <li>Responsibility 3</li>
    </ul>
    <h2>Requirements</h2>
    <ul>
        <li>Requirement 1</li>
        <li>Requirement 2</li>
        <li>Requirement 3</li>
    </ul>
</body>
</html>

UiPath Workflow Example

Below is a step-by-step guide on how to implement this:

Excel Application Scope
- File Path: path\to\your\excel.xlsx
- Output: DataTable dt

For Each Row in DataTable (dt)

Inside this loop:

Get values:
- responsibilities = row("Responsibilities").ToString
- requirements = row("Requirements").ToString
- code = row("Code").ToString

Create an HTML string:

htmlContent = "<!DOCTYPE html>" + Environment.NewLine +
              "<html lang='en'>" + Environment.NewLine +
              "<head>" + Environment.NewLine +
              "    <meta charset='UTF-8'>" + Environment.NewLine +
              "    <title>Job Responsibilities and Requirements - " + code + "</title>" + Environment.NewLine +
              "</head>" + Environment.NewLine +
              "<body>" + Environment.NewLine +
              "    <h1>Job Title: " + code + "</h1>" + Environment.NewLine +
              "    <h2>Responsibilities</h2>" + Environment.NewLine +
              "    <ul>"
For Each responsibility In responsibilities.Split(";"c)
    htmlContent += "<li>" + responsibility.Trim + "</li>"
Next
htmlContent += "    </ul>" + Environment.NewLine +
              "    <h2>Requirements</h2>" + Environment.NewLine +
              "    <ul>"
For Each requirement In requirements.Split(";"c)
    htmlContent += "<li>" + requirement.Trim + "</li>"
Next
htmlContent += "    </ul>" + Environment.NewLine +
              "</body>" + Environment.NewLine +
              "</html>"

Write Text File
- File Name: path\to\your\output\folder\" + code + ".html"
- File Content: htmlContent

Notes:

Make sure to replace the placeholders like path\to\your\excel.xlsx and path\to\your\output\folder\ with actual paths.
Adjust the delimiter used in the split function if your responsibilities and requirements are stored in a different format.
Ensure that the “Code” is unique for naming your HTML files to avoid overwrites.

Kunal_Jain · November 7, 2024, 6:55am

Hi @naveen.s
Thank you for your reply.
I just need to confirm that html content should also be inside the for each loop right?

naveen.s · November 7, 2024, 6:59am

@Kunal_Jain ,

Yes, you are correct! The HTML content should indeed be built inside the For Each Row loop. As you iterate through each row of the DataTable, you will append the actual data for “Responsibilities” and “Requirements” to the htmlContent.

Here’s how the structure should look conceptually:

Code Structure Example

' Initialize the HTML content before the loop
htmlContent = "<html><body><table border='1'><tr><th>Responsibilities</th><th>Requirements</th></tr>"

' Start For Each Row loop
For Each row In dataTable
{
    ' Append data from the current row to htmlContent
    htmlContent += "<tr><td>" + row("Responsibilities").ToString() + "</td><td>" + row("Requirements").ToString() + "</td></tr>"
}

' After the loop, close the HTML tags
htmlContent += "</table></body></html>"

Complete Workflow Steps

Putting everything together, the steps would look like this:

Excel Application Scope to open your Excel file (Book1.xlsx):
- Read Range to read data into dataTable.

Initialize HTML Content (before the loop):

htmlContent = "<html><body><table border='1'><tr><th>Responsibilities</th><th>Requirements</th></tr>"

For Each Row in dataTable:

Inside the loop, append to htmlContent:

htmlContent += "<tr><td>" + row("Responsibilities").ToString() + "</td><td>" + row("Requirements").ToString() + "</td></tr>"

Close HTML Tags (after the loop):

htmlContent += "</table></body></html>"

Get Filename based on “Code” value:

fileName = dataTable.Rows(0)("Code").ToString() + ".html" ' Modify index as needed

Write Text File to save htmlContent:
- Use the Write Text File activity to save the content:
```
Path: fileName, Content: htmlContent
```

Important Considerations

Data Validation: Ensure that the “Responsibilities”, “Requirements”, and “Code” columns exist in the DataTable. You may want to add checks to handle cases where these values may be null or missing.
Path for the HTML File: When naming the output HTML file, ensure you’re providing the correct path depending on where you want the file to be saved.

Kunal_Jain · November 7, 2024, 7:16am

Hi @naveen.s
I am not able to get the desired result.
Here I have attached the result sheet that we are getting after running the code provided.
2254.html (576 Bytes)
The result sheet that I need is also added here.
2650.html (32 Bytes)
(Please open the sheet in notepad)
Can you share the code if possible in a zip file format please?
That could be a great help.
Thanks!!

naveen.s · November 7, 2024, 7:17am

@Kunal_Jain ,

I cant upload any data from my client machine, try with the logic

Kunal_Jain · November 7, 2024, 7:43am

Hi @naveen.s
I think I am making some mistake while coding it is not giving me the correct output.

Kunal_Jain · November 7, 2024, 9:19am

@supermanPunch @Anil_G , @ppr , @Parvathy , @AJ_Ask , @ushu , @Gokul001 , @Palaniyappan , @Sudharsan_Ka
Can we get any solution for the above issue please?

Sanjay_Bhat · November 7, 2024, 12:17pm

Hi @Kunal_Jain

check the xaml!
testforforum.zip (11.5 KB)
Hope this helps!

Kunal_Jain · November 7, 2024, 1:21pm

Thanks for your help!!

system · November 10, 2024, 1:21pm

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
how to conver Excel data to Html Activities excel , question	3	777	March 11, 2022
Open html file using Excel Application Automation Starter excel , studio , question , html	11	2805	September 19, 2022
Convert excel data to html Studio	4	3397	July 18, 2020
How to convert Excel file to HTML Activities excel , activities , question , convert	8	2822	July 11, 2022
Excel Data to HTML format Help	7	2720	March 15, 2019

Convert the data from excel to html

Steps to Follow in UiPath:

Sample HTML Format

UiPath Workflow Example

Notes:

Code Structure Example

Complete Workflow Steps

Important Considerations

Related topics