Module having subroutines of 100 MB ???

The Application Programming Interface and the OASIS Open Document Format

Module having subroutines of 100 MB ???

Postby shambhagwat » Tue Jul 16, 2013 12:06 pm

I am a FoxPro Programmer

We were using MS Office but now switching to OpenOffice

We send 52 data entry forms to various institutions. On receiving it back we just consolidate all forms separately with just copy and paste. Then we publish it for the use of public. The columns in each form varies from 10 to 195 but rows are always less than 30.

There are so many data entry validations, which is done by micros in 52 modules and working fine.

Now we found that some institutions change the format. If they add/delete row or column, it is reported by our data entry validations module But if they change font height or cell borders or change the status of merged cells then the final print out gives us a unpleasant results.

Changes are made by others for some meaningful intention. For example
1) The FontHeight is increased slightly for readability and then widening the column becomes necessary.
2) Further while correcting the data entry errors, user generally hit the delete button. The pop up dialog box if checked for “All” (Default) then all formatting is set to default for that cell.

We therefore developed one micro which create a text file, in which all text and formatting of our prescribed forms is stored as a subroutine.

When this generated sub is copied to a module and after its execution the formatting etc. is restored.
The problem is, such automatically generated subroutine becomes vary large. For some forms 2MB subroutine is created.

We also intend to add a menu such as “Restore formatting” with sub menu for each form, so that other institutions (the ultimate users) will be in position to format it properly before printing.

Is there any suggession for an alternative or for optimization of code.

Attaching file “Formatting of Forms.ods” The GetInfo sub in module1 generates temp.txt as a subroutine. Just change the path of the temp.txt in sub main.

In module2 the generated subroutine “Info46B” is pasted from the temp.txt. When this sub is called, formatting is done keeping the data as it is.

There will be auto generated 52 subs in module2 which will be about 100 MB.
I am in doubt whether I am on right track.
Formatting of Forms.ods
(72.83 KiB) Downloaded 174 times
Appache OpenOffice 3.4 on Window XP SP3
Posts: 4
Joined: Sun Jun 17, 2012 7:21 am

Re: Module having subroutines of 100 MB ???

Postby FJCC » Tue Jul 16, 2013 4:29 pm

I haven't looked at this in detail but two possible methods come to mind.
1. Use cell styles instead of direct formatting. Instead of code like
Code: Select all   Expand viewCollapse view
        With .GetCellByPosition(177,18)
            .CharWeight = 100
            .CharFontName = "Arial"
            .CharHeight = 7.5
            .HoriJustify = 3
            oBorder.OuterLineWidth = 0
            .TopBorder = oBorder
            oBorder.OuterLineWidth = 35
            .LeftBorder = oBorder
            oBorder.OuterLineWidth = 35
            .RightBorder = oBorder
            oBorder.OuterLineWidth = 0
            .BottomBorder = oBorder

you could have
Code: Select all   Expand viewCollapse view
With .GetCellByPosition(177,18)
.CellStyle = "MyCellStyle_01"

where you have previously defined a cell style called MyCellStyle_01 that has all of the desired attributes. You would have to define several styles but then your code would be much shorter.
2. What if you had a second, hidden and protected, sheet with all the correct formatting in the file? You could then simply copy the DataArray from the used area of the first sheet into the second sheet.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Posts: 8302
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Module having subroutines of 100 MB ???

Postby shambhagwat » Wed Jul 17, 2013 8:56 am

FJCC, Thank You very much

Just explaining the cituation

We are Calling information once in a year from various institutions for last 6 decades and this practise will be continued forever.

Changed time may require changes in the forms and such changes will be of two types.
1. Primary Changes : Adding/Deleting a Column/Row or Additional data validation clauses or a new form. Such types of changes will be dealt with every year. All such changes are documented well in advance. So easy to look after.
2. Secondary Changes: But about formatting it is difficult to find out whether it has been changed or not for the current year. The concern authority is free to change it on fly without any documentation about it. All these formatting is for printing purpose only.

We created 52 sheets containing one form in each sheet and 52 separate modules for data validation (one module for each sheet) to take care of Primary Changes. Every year we will have to update and modify these modules as per requirement.

But we want to find out one time solution for the Secondary changes otherwise every year we will have to find out the changes made in the formatting. To solve the problem auto generated subroutine concept was applied where the current status of the formatting is automatically captuered.

Ok. Now for your suggessions.

As per your second suggession, We will have to create additional 52 hidden and protected sheets for formatting purpose and every year we will have to update these additional sheets manually.

I think, your first suggession is acceptable.

Accordingly, each cell will be compared with the predefined styles. If the CellStyle is mached, one line of code will be generated as per your suggestion.

If there is new style, code for defining new style can be generated automatically.

I will try and let you know.
Appache OpenOffice 3.4 on Window XP SP3
Posts: 4
Joined: Sun Jun 17, 2012 7:21 am

Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest