* * * Developers Info   * * *
This site is to assist developers, detailing every thing that has to be coded:-

A Brief Description of who I am :-

Call me Basit, I developed the CompuByte Point of Sale Software with over a  million lines of code, (Yeh.. Bloated stuff ! ! !) - all written and compiled in PowerBasic - Basic is what I do well J

I will be the co-coordinator and Will ensure that the code is managed/debugged and tested to the specifications outlined . .

You can ask me all the difficult questions ! ! ! I am on MSN as ceo@compubyte.co.za or available by mail on the same addy . .

A Brief Description of what is needed:-

There are many programs that easily convert an csv file to Excel, and Excel itself can use a csv file directly, what we require is a program that would take all csv files from a directly specified and than convert it to Excel . The created Excel file must be formatted fully based on the contents of the csv file it converted. The csv file has imbedded commands which assist in formatting.

 Example of such a Sample csv file:-

 "<Font=bold>","<Header=CompuByte Report>","<Date>","<Page>"

"<Font=Italic>","Transactions for Esquire","","","","<picS=\programs\esquire.jpeg>"

"<columns>", "<format ######.##>"

“date”,”Description”,”value1”,”value2”

“2004/11/25”,”invoice”,”100”,”10”

“2004/10/25”,”invoice”,”200”,”20”

“<Total>”,” “,”<D2:D3>”,”<E2:E3>”

“2004/11/25”,”invoice”,”300”,”30”

,"<picM=\jpegs\pic1.jpeg>"

“2004/11/20,”invoice”,”400”,”40”

,"<picS=\jpegs\pic2.jpeg>"

2005/01/06”,”invoice”,”500”,”50”

“<Total>”,” “,”<D2:DE>”,”<E3:EE>”

 

Step 1:

The program you will code will read this file or similar files (csv) files (program must be able to run from a command line or be shelled to and search in a specified directory for csv files) and immediate without using excel display all the items in columns, the program will than allow you to define the contents on each column.  So the user can for example say column one is a date in the format yyyy-mm-dd , column 3 and 4 is numeric in the format ######.##.  After this the user will indicate how the columns will be sorted, example by date, then by document, then by value2. Please note any item with the left hand sign and right hand sign i.e < and > will remain in the same row and not be sorted see Row 4 and Row 8 in sample.

Functions

Header must appear on Top of Each page printed if <Header=> exist

Footer must appear on Bottom of Each page printed if <Footer=> exist

Font formatted if <font> exist , if not use a default fault

Date inserted if <date> exist.

Page numbering auto if <Page> exist. (Format Page 1 of 4 etc.)

Column formatting, that is creating borders etc is activated when <column> is found.

Number formatting is when <format ###> exist after <column>

Pictures formated as picS (small size) picM (double the small size) PicL (double M).

 

Step 2:

Once the user defines each column (certain columns could be ignored or deleted) this information is stored in a configuration file which will have the same name but the extension of .cxl. The reason why you have a configuration file is so that when the next time the user loads the same file or similar files (based on the 1st 3 letters of the name of the file) and a similar configuration exists the columns will be formatted accordingly and a resulting excel file created immediately.

 

Step 3:

If you look at the sample csv file the first row shows you what is the header name of each column ,  in row 4 and the last row you have items with < and > , these items indicate that that row is not to be sorted and that a calculation must happen in that row based on what is indicated , example in row 4 the program will calculate the value of row d2+d3 and insert it in that cell, also note when it does this calculation it must not add the values calculated in any row that has a formulae in it already. So as per the example csv file the total will ignore row 4 which also has a calculation. If a cell has (like in sample)  :<D2:DE>  the “E” means add from cell D2 to the end i.e D7,  ignoring row D4.

 

Step 4:

This will display the columns now fully formatted as configured, with lines above below and sides of each cell. The user than can choose to print out the data to the configured printer, the user must have the option to portrait or landscape print out, as well as the very important option of fit to page or pages (similar to Excel’s preview and print page). The chosen options will also be stored into the same configuration file detailed in Step 2:

 

Step 5

The file will be saved in the uses documents folder as an Excel file, which can be opened by any version of excel with all the formatted details created using Step 2 to Step 4. A file with the extension .def be created if other formatting is done using the same file, this file can be used to auto format the next time a similar csv file is loaded.

Sample of Excel file Created if the user does not choose to sort:-  (Headers/pics excluded only columns)

 

Date

Document

Value 1

Value 2

2004/11/25

Invoice

100.00

10.00

2004/10/25

Invoice

200.00

20.00

 

 

300.00

30.00

2004/11/25

Invoice

300.00

30.00

2004/11/20

Invoice

400.00

40.00

2005/01/06

Invoice

500.00

50.00

Totals

 

1500.00

150.00

 

Sample of Excel file Created if the user does choose to sort as per Step 1:- (all formatting and pictures shown)

 

CompuByte Report Dated : 19-12-2004 Page 1 of 1
     
     
Transactions for Esquire :-  
     
     
     
DateDocumentValue 1Value 2 
10/25/2004Invoice20020 
11/20/2004Invoice40040
  60060
11/25/2004Invoice10010 
11/25/2004Invoice30030
1/6/2005Invoice50050 
Totals 1500150 
     
     

Note how the headers are bolded and the total columns bolded, pictures corrected formatted this must be the same in the created xls file.

 

Some notes :-

This program should be independent of Excel being installed on the system, however the created file must be loadable by excel (any newer versions)

 

To get an idea of some coders who attempted to convert and excel file visit :-

http://www.softinterface.com/Convert-Text-To-Excel/Documentation/Default.htm

http://www.winsite.com/bin/Info?5165

 

Conclusion:-

This software could be written in .Net 2003 - usable on a windows machine, the coder can use any design layout as long as it’s simple, attractive and allows the functionality described from step 1 to 5. More embedded functions (items that have < > in them) can be created as you wish to make formatting easier .

Writing code for this is challenging and indeed niche . . .

Have fun ! ! !

 

 

 

 

 

 

 


Questions or problems regarding this web site should be directed to md@mandla.co.za
Copyright © 2004 CompuByte. All rights reserved.
Last modified: 10/05/04.