C#C
C#3y ago
Pit

Is there a way to retain the formatting of a worksheet when copy + pasting programatically?

We're trying to abandon the use of excel macro to generate the emails at work

C#
using System;
using System.IO;
using OfficeOpenXml;
using Outlook = Microsoft.Office.Interop.Outlook;

class Program
{
    static void Main()
    {
        // Set the path to your Excel file
        string excelFilePath = "YourExcelFile.xlsx";

        // Load Excel package using EPPlus
        using (var package = new ExcelPackage(new FileInfo(excelFilePath)))
        {
            // Assuming the worksheet is named "Sheet1"
            var worksheet = package.Workbook.Worksheets["Sheet1"];

            // Specify the range you want to copy (e.g., A1 to C10)
            var range = worksheet.Cells["A1:C10"];

            // Copy the range to a 2D array
            object[,] data = range.Value as object[,];

            // Create a StringBuilder to build the HTML table
            var htmlBuilder = new System.Text.StringBuilder();

            // Build the HTML table
            htmlBuilder.Append("<table border='1'>");
            for (int row = 0; row < data.GetLength(0); row++)
            {
                htmlBuilder.Append("<tr>");
                for (int col = 0; col < data.GetLength(1); col++)
                {
                    htmlBuilder.Append("<td>");
                    htmlBuilder.Append(data[row, col]);
                    htmlBuilder.Append("</td>");
                }
                htmlBuilder.Append("</tr>");
            }
            htmlBuilder.Append("</table>");

            // Get the HTML string
            string htmlTable = htmlBuilder.ToString();

            // Create an Outlook application object
            var outlookApp = new Outlook.Application();

            // Create a new mail item
            var mailItem = outlookApp.CreateItem(Outlook.OlItemType.olMailItem) as Outlook.MailItem;

            // Set the subject and body of the email
            mailItem.Subject = "Excel Data";
            mailItem.HTMLBody = $"<p>Here is the Excel data:</p>{htmlTable}";

            // Display the email
            mailItem.Display();

            // Uncomment the line below if you want to send the email immediately
            // mailItem.Send();
        }
    }
}


This is the structure I'm working with ^


C#
var range = worksheet.Cells["A1:C10"];


I would like to copy and paste 'range' in the body of the email keeping the original formatting
Was this page helpful?