Export to Excel - Drawing a frame

Business Central/NAV Developers Forum
1

Hi everyone,

I am trying to export some data to excel…

The customer wants each row to be surrounded by a frame…and the whole data to be surronded by a bold frame, just like the picture below…

Does anyone knows how to do this???

4048.file.JPG
4048.file.JPG1024×737 16.4 KB

2

I assume that you mean the Borders - not frames?

I had this problem a few years ago and it wasn’t really easy to find any information about this, but analyzing a lot of Visual Basic code examples from microsoft.com made me find the solution. So there might be an easier way, but this one worked for me.

When you do it then you need to think about how Excel works. Here you can set the top, left, right and bottom borders individually.

First you need to know these enum values used to set the indivdual border types and line types:

xlDot := -4118;
xlMedium := -4138;
xlThin := 2;
XlEdgeBottom := 9;
XlContinuous := 1;

The below code is setting the top border to a normal thin border.

xlBorderIndex := xlBorderIndex::xlEdgeTop;
XlWrkSht.Range(xlColID + xlRowID).Borders.Item(xlBorderIndex).LineStyle := XlContinuous;
XlWrkSht.Range(xlColID + xlRowID).Borders.Item(xlBorderIndex).Weight := xlThin;
3

I am not really sure I understand this…

Do I have to work on each border separately???

and what about the numeric values you mentionned???Where do I use them???

4

If you are using Automation for exporting data to excel then use this

xlWorkSheet.Range(‘A’ + FORMAT(ColNo)).VerticalAlignment:=-4108;

xlWorkSheet.Range(‘A’ + FORMAT(ColNo))._BorderAround(1,2,-4105);

5

Hi Poppins,

That’s the only way I got it to work, but you make it sounds so difficult, you just create one function to set it and then set at once.

I defined all numeric enum values as variables and assigned them in the beginning of the function. Even the one to define which of the four borders it is (defined as an option with these values: None,xlDiagonalDown,xlDiagonalUp,xlEdgeLeft,xlEdgeTop,xlEdgeBottom,xlEdgeRight,xlInsideVertical,xlInsideHorizontal).

Personally the way I have done it, is to extend the functionality already found in the Excel Buffer table. I have attached the modified table I created. Take a look at function CreateSheet, this should inspire you enough to do it. (The fob file is delivered AS-IS and with no guarantees or promises only for your inspiration).
excel.fob (168 KB)

6

Thanks a lot for sharing, Eric…

I’ll try it [:)]

7

Hi Ernst, I tried the fob file you attached…

Infact, the table doesn’t compile, the following message error is displayed: TableData 65012 doesn’t exist.

May be there is another object you created and didn’t include in the fob???

8

Hi Poppins,

As I said then the attached file was only for your inspiration. It was not a “ready to use” solution. There are many other objects in the Extended Excel Buffer solution, which I’m not going to share here. I’m selling these enhancements to my clients. [:)]

9

OK, I understand [:)]