The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Downloading or Extracting excel files, Downloading file or Extracting data from Excel file stored in other we
Rayx
post Jan 29 2014, 12:03 AM
Post #1





Group: Members
Posts: 3
Joined: 28-January 14
Member No.: 20,292



Hi,

I have equivalent to "No knowledge" in HTML or Java script, hence taken shelter here to find a solution to the problem confronting for last 9 months. I desperately need your help. The problem discussed below:

Problem:
Everyday morning I download 32 fresh excel files from an intranet site, then open each file (workbooks) and copy data from 2 different sheets in each excel file (workbooks) to make a master excel file. This manual process takes hours to complete, afterwards I prepare dashboard with the same data in excel, which makes the excel file heavier and slower to work.

Solution Needed:
I want to create a webpage which will solve all the above problems, mentioned in steps below:-
1. Download 32 excel file, selecting different options in 3 different the drop-downs (one is country lists, the 2nd is date and the 3rd is Job Received/Running/Job Done)
2. Extract data from each file and save in a 3 different excel sheets (received,running,done accordingly) in one workbook
3. In the webpage I need some buttons to show the count of works are done and received yesterday and count of jobs running till today and time since the job is running
4. Finally a graph to show the counts of received, done and running from last week till today

I will be obliged if my request is heard and extended a hand of help.

Thanks and regards.
rolleyes.gif

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Jan 29 2014, 12:59 AM
Post #2


This is My Life
*******

Group: Members
Posts: 1,128
Joined: 24-August 06
From: t-dot
Member No.: 16



There is a php library called PHPExcel which can read excel spreadsheets and turn them into xml or json.

Then you could again using php combine the xml into a single file.

Then it would be relatively easy to turn this into a webpage presentation.

i worked on a project like this last year, I would think it would take a few days/weeks of programming depending on how familiar you are with php/xml objects/json.

If you want to keep each days files for later retrieval you might put the xml or json into a database for retrieval upon request.

===
Just reviewed some of the code i used.
We actually converted the excel to csv using the PHPExcel library.
Used PHP to convert that to xml, then to a simpleXML object.
From there you can read that and create what you need.


This post has been edited by jimlongo: Jan 29 2014, 01:18 AM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Rayx
post Jan 29 2014, 01:48 AM
Post #3





Group: Members
Posts: 3
Joined: 28-January 14
Member No.: 20,292



Thank you so very much for a quick response.

I have no knowledge in PHP or XML or JSON, so it would be too difficult for me to develop this project without your assistance. Please guide me from where and how should i start and which one of 3 (PHP/XML/JSON) will be easier for me to understand without much effort. It would be of a great help if you can give me a small example of the problem discussed and guide me how to start the process step by step.

Appreciate your help.

Thank you.

This post has been edited by Rayx: Jan 29 2014, 01:48 AM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Jan 29 2014, 09:49 AM
Post #4


This is My Life
*******

Group: Members
Posts: 1,128
Joined: 24-August 06
From: t-dot
Member No.: 16



This would be impossible without programming knowledge.
You need to know PHP.

I wish I could direct you how to do this without much effort . . . but effort or money are the only ways to solve a problem this non-trivial.

The people I did this for were a financial institution, they could afford it.
What is your purpose?

This post has been edited by jimlongo: Jan 29 2014, 09:52 AM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Rayx
post Jan 29 2014, 11:30 PM
Post #5





Group: Members
Posts: 3
Joined: 28-January 14
Member No.: 20,292



The purpose of building this application is to minimize the extra labor I give to complete a job everyday and the other intention is also to learn the subject for a job on the same field. It would be very helpful if you could guide me how to start and what and which chapter of subject to study to complete it myself. I have programming knowledge and basic knowledge in HTML and I can understand 50% of PHP coding but need your guidance on how to proceed.
1. How to strart?
2. How to connect to the intranet?
3. How to log in to the page?
4. How to access the excel files stored in the page?
5. How to download the files?
6. How to save the files in 3 different sub-folders in a folder?
7. How to open each files from each sub-folder and copy the content to an assigned sheet in another excel master workbook?
8. How to close each files opened from sub-folder?
9. How to replace "xxx" values in one column to "yyy" from 3 sheets in master workbook?
10. How to do the count, calculate time and date difference, percentage calculations etc.?

your guidance will be much helpful.

Thank you.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
jimlongo
post Jan 30 2014, 12:15 AM
Post #6


This is My Life
*******

Group: Members
Posts: 1,128
Joined: 24-August 06
From: t-dot
Member No.: 16



QUOTE(Rayx @ Jan 29 2014, 11:30 PM) *

The purpose of building this application is to minimize the extra labor I give to complete a job everyday and the other intention is also to learn the subject for a job on the same field. It would be very helpful if you could guide me how to start and what and which chapter of subject to study to complete it myself. I have programming knowledge and basic knowledge in HTML and I can understand 50% of PHP coding but need your guidance on how to proceed.
1. How to strart?
2. How to connect to the intranet?
3. How to log in to the page?
4. How to access the excel files stored in the page?
5. How to download the files?

The first part of the job is to read the files. Where are the files? Let's say for sake of argument they are on a server and the php scripts are on the same server. You can use php's DirectoryIterator function to walk through the directory and open each file to be converted into csv format by the PHPExcel library. The files get converted to csv, probably into the same folder the excel folders were in, or it could be another folder.

QUOTE

6. How to save the files in 3 different sub-folders in a folder?
7. How to open each files from each sub-folder and copy the content to an assigned sheet in another excel master workbook?
8. How to close each files opened from sub-folder?

Now you walk through the directory again using DirectoryIterator and fopen() and fgetcsv() to read the csv and convert that into XML using DomDocument() Eventually you'll now have changed all the csv to XML


QUOTE

9. How to replace "xxx" values in one column to "yyy" from 3 sheets in master workbook?
10. How to do the count, calculate time and date difference, percentage calculations etc.?

This will be the hard part, you have to convert the XML to SimpleXML objects and use array manipulation to pull the data you want, and do the calculations. So hard to say without knowing the data.


QUOTE

your guidance will be much helpful.
Thank you.


I would ask about this on StackOverflow, it's a more appropriate place for this kind of programming question and you'll find a lot more knowledgeable people than me.

If you're working on this take it a step at a time.
1. Iterate through the files with DirectoryIterator and foreach loops
2. Learn how to convert the files with PHPExcel
3. Learn how to open files for reading with fgetcsv
4. Learn how to create XML from the csv
5. Learn how to create SimpleXML objects
6. Learn how to use multidimensional arrays and find specific data in the SimpleXML object
7. Learn how to manipulate the data
8. Figure out where to store it (database) and in what form. JSON might come in handy at this point.
9. Learn how to get it from the database and display it on a webpage.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
micagordon
post Jul 22 2015, 02:51 AM
Post #7





Group: Members
Posts: 2
Joined: 5-February 15
Member No.: 22,152



Thanks, jimlongo
I'm beginner here, and my work have some similar request with the Rayx.
Thank you for giving ideas on the issue.


--------------------
Best regards
Mica Gordon
Barcode


This post has been edited by micagordon: Jul 22 2015, 02:52 AM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 28th March 2024 - 11:10 AM