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

Notifications

Announcements

No record found.

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 vinaytak 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 237,978 Most Valuable Professional on at

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

  • vinaytak Profile Picture
    vinaytak 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 237,978 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.

  • vinaytak Profile Picture
    vinaytak 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 237,978 Most Valuable Professional on at

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

  • vinaytak Profile Picture
    vinaytak 371 on at

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

  • vinaytak Profile Picture
    vinaytak 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 237,978 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.

  • vinaytak Profile Picture
    vinaytak 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

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 > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans