Automating a manual process

[lukica]

Hi guys, 

I am looking to automate the process of filling the same excel and word documents. Here's the deal: as a consultant a part of my job is to fill in the same forms over and over again for different clients. So for each client there needs to be a set of different excel and word files created with their personal info filled in. I would like to create a program that would fill in all different documents with the same input that I type in. for example: It will be much easier to enter the name one time and then have the program insert it where needed instead of me doing it 5 times. The problem is I don't know which language to do it in. I am sure the code that does this exists, just don't know where to look. Any suggestions and tips would be highly appreciated. 

Thank you!

[Ghideon]

1 hour ago, lukica said:

I am looking to automate the process of filling the same excel and word documents.

 

1 hour ago, lukica said:

I am sure the code that does this exists, just don't know where to look. Any suggestions and tips would be highly appreciated. 

Hello. A quick answer, your scenario sounds like a task for RPA*, Robotic Process Automation. Typically such frameworks have some means to interact with office programs such spreadsheet programs. No or limited amount of coding may be needed; it is often more like recording and modifying macros. You typically teach the software how to manipulate the GUI of the office program. In cases with a singel user and where no central orchestration is required I would look at a community edition** of some of the commercial vendor's product. There are also or open source alternatives. Personally I would start looking from the RPA/Macro/automation perspective rather than from software development/programming language perspective in the described situation. 

 

*) https://en.wikipedia.org/wiki/Robotic_process_automation

**) Often free for personal or limited commercial use. I'm not going to promote some specific vendor or product here; that would require more information and some evaluation.

[Sensei]

In Open Office Spreadsheet you can copy one cell to other cell.

e.g. suppose so we have something in cell A1. Fill cell A2 with data "=A1". Whenever you change content of A1, A2 will be automatically updated. Excel should have something similar too. Instead of copying you can also do math operation. Sumation. etc.

 

Open Office Spreadsheet accepts CSV file format, which is plain text but with information about spreadsheet structure. You can modify it by simple script. Columns are separated by coma or other character e.g.

0,1,2

3,4,5

Will make two rows, three columns with filled data.

So you can make template in CSV with special tags e.g.

%FIRST%,%SURNAME%,%DATE%

Then in the script language of your choice use string replacement function to change tags to real values or values read from external file etc.

e.g. in PHP it will be:

$output = str_replace( "%FIRST%", "John", $input );

$output = str_replace( "%SURNAME%", "Doe", $output );

$output = str_replace( "%DATE%", "2021-04-20", $output );

 

What are you doing with your excel and docs? Print? Mail? Save on disk? 

In some cases you could use HTML (/JS/CSS/jQuery/AJAX) instead of Excel and Doc. And work entirely in Web browser window. It has many advantages like portability (PC, iMac, mobile and tablet supported since the beginning). HTML has form tags.

https://www.w3schools.com/html/html_forms.asp

You can use jQuery and AJAX and PHP for validation of data, sending filled e-mail and storing ready document in local database.

https://www.w3schools.com/jquery/jquery_intro.asp

If you want to try this route first download XAMPP. It is local HTTP Apache server with PHP and MySQL database installed and ready to use.

https://www.apachefriends.org

 

Edited April 20, 2021 by Sensei

[fredreload]

I would go with C# and NPOI. Back then when we had to export a document we go (ex):

worksheet ws = new worksheet()

irow, icell, etc to export the headers and contents from the datatable.

Works pretty well if you are already using Visual Studio and building apps with it.