web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Styling excel using X+...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Styling excel using X++ in D365FO

(1) ShareShare
ReportReport
Posted on by Huggins Mafigu 507

I'm using the EPPlus API to export my data to excel. What I want to achieve is to have a Wrap text in a cells in the first row of my excel and sizing of cells. I've tried with the following code but its not working. Any leads on how I can achieve that. Below is my code


System.IO.Stream workbookStream = new System.IO.MemoryStream();


System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();

using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
{
var currentRow = 1;
var worksheets = package.get_Workbook().get_Worksheets();
var worksheet = worksheets.Add("Sheet1");
//worksheet.Protection.IsProtected = true;
var cells = worksheet.get_Cells();
var cell = cells.get_Item(currentRow,1);
cell.AutoFitColumns();
Style.ExcelBorderStyle styleBorder; //= cell.Style.WrapText;


styleBorder = cell.Style.Wraptext;

}

I know I could have used Office Integration for exporting data to excel but it does not give me the desired formatted spreadsheet.

This is my desired output

Excelupload.PNG

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,697 Most Valuable Professional on at
    RE: Styling excel using X++ in D365FO

    This seems to be a question about how to use EPPlus library, not about D365FO, therefore this isn't the best place to ask.

    Anyway, you can do that by setting WrapText property of a style to true.

  • Huggins Mafigu Profile Picture
    Huggins Mafigu 507 on at
    RE: Styling excel using X++ in D365FO

    Setting it to true will give an error of "Wraptext is a readonly property"

  • Martin Dráb Profile Picture
    Martin Dráb 237,697 Most Valuable Professional on at
    RE: Styling excel using X++ in D365FO

    Can you show your complete code, please? Your brief reply doesn't make clear which object you tried to change.

    Use Insert > Insert Code (in the rich-formatting view) to paste source code.

  • Martin Dráb Profile Picture
    Martin Dráb 237,697 Most Valuable Professional on at
    RE: Styling excel using X++ in D365FO

    Here is a link to an EPPlus tutorial page demonstrating WrapText: https://riptutorial.com/epplus/example/26411/text-alignment-and-word-wrap.

  • Huggins Mafigu Profile Picture
    Huggins Mafigu 507 on at
    RE: Styling excel using X++ in D365FO

     HcmLabourInvoiceTrans hlabourInvoiceTrans ;
HcmLabourInvoiceTransLines labourInvoiceTransLines;

DocuFileSaveResult saveResult = DocuFileSave::promptForSaveLocation(strFmt('Upload %1  %2  5M001', mthName(DateTimeUtil::month(today())), DateTimeUtil::year(today())), "xlsx", null, "Export to excel");
           

            if (saveResult && saveResult.parmAction() != DocuFileSaveAction::Cancel)
            {
                saveResult.parmOpenParameters('web=1');
                saveResult.parmOpenInNewWindow(false);

                System.IO.Stream workbookStream = new System.IO.MemoryStream();
              
             
                System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
            
                using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
                {
                    var currentRow = 1;
                    var worksheets = package.get_Workbook().get_Worksheets();
                    var worksheet = worksheets.Add("Sheet1");
                    //worksheet.Protection.IsProtected = true;
                    var cells = worksheet.get_Cells();
                    var cell = cells.get_Item(currentRow,1);
                    cell.AutoFitColumns();
                    Style.ExcelBorderStyle styleBorder; //= cell.Style.WrapText;
                    
                    worksheet.Column(1).Width = 25;
                    worksheet.Column(1).Style.WrapText = true; //This is giving error Invalid token'='
                    
                    styleBorder = cell.Style.Wraptext;
                    Style.Border borderStyle = cell.Style.Border;
                    borderStyle.BorderAround(ExcelBorderStyle::Thin, System.Drawing.Color::Black);

                    Style.ExcelFont style = cell.Style.Font;
                    //style.Bold = true;
                    style.Size = 11;
                    style.Name  = "Trebuchet MS";
                    cell.set_Value("MIM CC");
                    cell = null;

                    
                    for (hlabourInvoiceTrans = HcmLabourInvoiceTrans_ds.getFirst(true) ? HcmLabourInvoiceTrans_ds.getFirst(true) : hlabourInvoiceTrans; hlabourInvoiceTrans; hlabourInvoiceTrans = HcmLabourInvoiceTrans_ds.getNext())
                    {
                        currentRow   ;
                        cell = null;

                        cell = cells.get_Item(currentRow, 1);
                        cell.set_Value(hlabourInvoiceTrans.CostCenter);
                        cell.AutoFitColumns();
                        Style.ExcelFont st1 = cell.Style.Font;
                        st1.Size = 11;
                        st1.Name  = "Calibri";
                        cell = null;
                    }

                    package.Save();

                }

                memoryStream.Seek(0, System.IO.SeekOrigin::Begin);

                DocuFileSave::processSaveResult(memoryStream, saveResult);

    This is my whole code

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,697 Most Valuable Professional on at
    RE: Styling excel using X++ in D365FO

    Your code contains a couple of things that aren't related to the problem in question, therefore let me provide a cleaner example. It's a runnable class that everybody can copy and execute; it has other dependencies besides EPPlus.

    You'll get an Excel sheet with a hundred 'a' characters inside a text-wrapped cell.

    using OfficeOpenXml;

class TextWrapDemo
{
    public static void main(Args _args)
    {
        using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
        {
            using (var package = new ExcelPackage(stream))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
            
                Style.ExcelStyle style = worksheet.Column(1).Style;
                style.WrapText = true;
            
                ExcelRange cell = worksheet.Cells.get_Item(1,1);
                cell.Value = strRep('a', 100);
            
                package.Save();
            }
            File::SendFileToUser(stream, 'data.xlsx');
        }
    }
}

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 683 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 563 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 398 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans