Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Styling excel using X+...
Finance forum
Suggested answer

Styling excel using X++ in D365FO

(1) ShareShare
ReportReport
Posted on by Huggins Mafigu 457

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

Categories:
Data Integration
Development / Customization / SDK
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 228,971 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');
        }
    }
}

  • Huggins Mafigu Profile Picture
    Huggins Mafigu 457 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

  • Martin Dráb Profile Picture
    Martin Dráb 228,971 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.

  • Martin Dráb Profile Picture
    Martin Dráb 228,971 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.

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

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

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 228,971 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.

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

Replay: Dynamics 365 Community Call ERP Edition | October 9, 2024

Another great gathering of our Community!

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

More Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,665 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,971 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,150

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans