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

Announcements

Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Freeze in Excel file u...
Microsoft Dynamics AX (Archived)

Freeze in Excel file using x++

(0) ShareShare
ReportReport
Posted on by CU02060633-1 371

Hello everybody,

I have a requirement on creating an Excel file from X++.

I have to freeze rows and columns.

I've searched a way to do this with SysExcel classes and Excel COM object and i didn't find it.

Please can anyone give me advices ?

I'm working with AX 2012 R2.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 239,932 Most Valuable Professional on at

    It can be done by Window.FreezePanes property. Here is an example in X++.

  • CU02060633-1 Profile Picture
    CU02060633-1 371 on at

    Thanx @Martin Dráb..for the reply... ut how to use in the code of the excel export

  • Martin Dráb Profile Picture
    Martin Dráb 239,932 Most Valuable Professional on at

    What do you mean by "the code of the excel export"? I said you wanted to "create an Excel file from X++" and you correctly mentioned SysExcel / COM, so I gave you a solution for it.

    If you mean that you want SSRS to generate an Excel reports in some specific way, you have no control over it - you could merely suggest a new feature to the SSRS team. But you could let SSRS to generate the report as usual and then use X++ to modify it.

  • CU02060633-1 Profile Picture
    CU02060633-1 371 on at

    @Martin Dráb ..i wriiten a job to export the excel template i prepare..and job and excel both are working fine..when my excel open i want to freeze the 1st and 2nd row...means user scroll down to fill th data in that the freeze pane will show

  • Martin Dráb Profile Picture
    Martin Dráb 239,932 Most Valuable Professional on at

    All right, that's what I addressed in my first reply. What doesn't work for you?

  • CU02060633-1 Profile Picture
    CU02060633-1 371 on at

    @Martin Dráb..you said use " Window.FreezePanes" but in the code how to write this

  • CU02060633-1 Profile Picture
    CU02060633-1 371 on at

    @Martin Dráb....

    SysExcelApplication     sysExcelApplication;

    COM comWindow, comApplication;

    sysExcelApplication = SysExcelApplication::construct();

    ...

    comApplication  = sysExcelApplication.comObject();

    comWindow       = comApplication.activeWindow();

    comWindow.splitcolumn(1);

    comWindow.splitRow(row);

    comWindow.freezePanes(true);

    i am using this also but eeror is comming that

    "COM object not initialized."

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    Most likely you do not have the correct version of Excel installed on the computer where your client is running, if the constructor does not return the COM object. If you are on a Terminal server, that has to have an Office installation available.

    Check the AX System Requirements guide to see what major and minor version, with which cumulative update is supporting what Office version.

  • Martin Dráb Profile Picture
    Martin Dráb 239,932 Most Valuable Professional on at

    Please show us your full code (include the part you've replaced with ...) and tell is which line is throwing the error, i.e. which COM object isn't initialized. You must realize that we can't help you if you don't give us enough information.

  • CU02060633-1 Profile Picture
    CU02060633-1 371 on at

    @Martin Dráb this is the code ...

    static void AMY_Exceltemplates()

    {

       #AviFiles

       ComVariant                                      CellValue;

       counter                                         sno,col,row,setcount;

       int                                             black = WinAPI::RGB2int(0,0,0);

       KOTAExcelReporter                               KOTAExcelReporter = new KOTAExcelReporter();

       str                                             a,b;

        SysExcelApplication                             sysExcelApplication;

        COM                                            comWindow, comApplication,comSheet;

       SysOperationProgress   sysOperationProgress = new SysOperationProgress(1);

       setPrefix("Extracting Data");

       sysOperationProgress.setAnimation(#aviTransfer);

       sysOperationProgress.setCaption("Opening Template");

       sysOperationProgress.setText("Processing...");

       sysOperationProgress.update(true);

        //for the freeze on the row

           sysExcelApplication = SysExcelApplication::construct();

           comApplication  = sysExcelApplication.comObject();

           comWindow       = comApplication.activeWindow();

           comWindow.splitcolumn(1);

           comWindow.splitRow(row);

           comWindow.freezePanes(true);

       //ennded the code for freeze

       KOTAExcelReporter.xlInsert(types::String, 1, 3, strfmt(" Quality Report "));

       KOTAExcelReporter.xlFormatCell("C1",15,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 2, 1, "Branch:");

       KOTAExcelReporter.xlFormatCell("A2",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 2, 5, "Date:");

       KOTAExcelReporter.xlFormatCell("E2",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 2, 7, "From:");

       KOTAExcelReporter.xlFormatCell("G2",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 2, 10, "To:");

       KOTAExcelReporter.xlFormatCell("J2",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 4, 1, "No.");

       KOTAExcelReporter.xlFormatCell("A4",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 4, 2, "Kitchen");

       KOTAExcelReporter.xlFormatCell("B4",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 4, 3, "Bad");

       KOTAExcelReporter.xlFormatCell("C4",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 4, 4, "Good");

       KOTAExcelReporter.xlFormatCell("D4",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 4, 4, "Excellent");

       KOTAExcelReporter.xlFormatCell("E4",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 4, 5, "Comment");

       KOTAExcelReporter.xlFormatCell("F4",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 5, 1, "1");

       KOTAExcelReporter.xlFormatCell("A5",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 5, 2, "Floors");

       KOTAExcelReporter.xlInsert(types::String, 6, 1, "2");

       KOTAExcelReporter.xlFormatCell("A6",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 6, 2, "Tables");

       KOTAExcelReporter.xlInsert(types::String, 7, 1, "3");

       KOTAExcelReporter.xlFormatCell("A7",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 7, 2, "Fridges");

       KOTAExcelReporter.xlInsert(types::String, 8, 1, "4");

       KOTAExcelReporter.xlFormatCell("A8",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 8, 2, "Store");

       KOTAExcelReporter.xlInsert(types::String, 9, 1, "Comment for kitchen");

       KOTAExcelReporter.xlFormatCell("A9",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 11, 1, "No.");

       KOTAExcelReporter.xlFormatCell("A11",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 11, 2, "Temprature");

       KOTAExcelReporter.xlFormatCell("B11",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 11, 3, "Bad");

       KOTAExcelReporter.xlFormatCell("C11",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 11, 4, "Good");

       KOTAExcelReporter.xlFormatCell("D11",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 11, 5, "Excellent");

       KOTAExcelReporter.xlFormatCell("E11",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 11, 6, "Comment");

       KOTAExcelReporter.xlFormatCell("F11",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 12, 1, "1");

       KOTAExcelReporter.xlFormatCell("A12",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 12, 2, "Floors");

       KOTAExcelReporter.xlInsert(types::String, 13, 1, "2");

       KOTAExcelReporter.xlFormatCell("A13",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 13, 2, "Fridges");

       KOTAExcelReporter.xlInsert(types::String, 14, 1, "3");

       KOTAExcelReporter.xlFormatCell("A14",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 14, 2, "Marie Milk");

       KOTAExcelReporter.xlInsert(types::String, 15, 1, "4");

       KOTAExcelReporter.xlFormatCell("A15",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 15, 2, "Frying");

       KOTAExcelReporter.xlInsert(types::String, 16, 1, "Comment for temprature");

       KOTAExcelReporter.xlFormatCell("A16",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 18, 1, "No.");

       KOTAExcelReporter.xlFormatCell("A18",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 18, 2, "Hall");

       KOTAExcelReporter.xlFormatCell("B18",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 18, 3, "Bad");

       KOTAExcelReporter.xlFormatCell("C18",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 18, 4, "Good");

       KOTAExcelReporter.xlFormatCell("D18",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 18, 5, "Excellent");

       KOTAExcelReporter.xlFormatCell("E18",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 18, 6, "Comment");

       KOTAExcelReporter.xlFormatCell("F18",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 19, 1, "1");

       KOTAExcelReporter.xlFormatCell("A19",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 19, 2, "Placemats");

       KOTAExcelReporter.xlInsert(types::String, 20, 1, "2");

       KOTAExcelReporter.xlFormatCell("A20",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 20, 2, "Glass");

       KOTAExcelReporter.xlInsert(types::String, 21, 1, "3");

       KOTAExcelReporter.xlFormatCell("A21",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 21, 2, "Silver");

       KOTAExcelReporter.xlInsert(types::String, 22, 1, "4");

       KOTAExcelReporter.xlFormatCell("A22",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 22, 2, "Tables");

       KOTAExcelReporter.xlInsert(types::String, 23, 1, "5");

       KOTAExcelReporter.xlFormatCell("A23",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 23, 2, "Floors");

       KOTAExcelReporter.xlInsert(types::String, 24, 1, "Comment for Hall");

       KOTAExcelReporter.xlFormatCell("A24",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 26, 1, "No.");

       KOTAExcelReporter.xlFormatCell("A26",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 26, 2, "General Cleanliness & Hygenie");

       KOTAExcelReporter.xlFormatCell("B26",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 26, 3, "Bad");

       KOTAExcelReporter.xlFormatCell("C26",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 26, 4, "Good");

       KOTAExcelReporter.xlFormatCell("D26",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 26, 5, "Excellent");

       KOTAExcelReporter.xlFormatCell("E26",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 26, 6, "Comment");

       KOTAExcelReporter.xlFormatCell("F26",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 27, 1, "1");

       KOTAExcelReporter.xlFormatCell("A27",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 27, 2, "WC Singles");

       KOTAExcelReporter.xlInsert(types::String, 28, 1, "2");

       KOTAExcelReporter.xlFormatCell("A28",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 28, 2, "WC Families");

       KOTAExcelReporter.xlInsert(types::String, 29, 1, "3");

       KOTAExcelReporter.xlFormatCell("A29",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 29, 2, "Outside the Restaurant");

       KOTAExcelReporter.xlInsert(types::String, 30, 1, "4");

       KOTAExcelReporter.xlFormatCell("A30",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 30, 2, "Service Providers");

       KOTAExcelReporter.xlInsert(types::String, 31, 1, "5");

       KOTAExcelReporter.xlFormatCell("A31",10,true,false,false,1,false,false,"Segoe UI",black);

       KOTAExcelReporter.xlInsert(types::String, 31, 2, "Kitchen Workers");

       KOTAExcelReporter.xlInsert(types::String, 32, 1, "Comment for General Cleanliness & Hygenie");

       KOTAExcelReporter.xlFormatCell("A32",10,true,false,false,1,false,false,"Segoe UI",black);

       col = 6;

       row = 2;

       KOTAExcelReporter.xlInsert(types::String, row, 6, strfmt("%1",systemDateGet()));

       a = time2str(timeNow() ,0,0);

       KOTAExcelReporter.xlInsert(types::String, row, 8, strfmt("%1",a));

       b = time2str(timeNow()+60*60,0,0);

       KOTAExcelReporter.xlInsert(types::String, row,11, strfmt("%1",b));

       KOTAExcelReporter.xlShow();

      }

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

News and Announcements

Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Sanhthosh.Kumar.K Profile Picture

Sanhthosh.Kumar.K 2

#2
Raed Salah Bzour Profile Picture

Raed Salah Bzour 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans