Processing Excel Data Using Pyexcel

Question

Here's a Python question:

Hello I'm making a web app, its receives the data from a spreadsheet(.csv) turning them into integers from. Evaluating those values, returning those values and writing that data to the 4'th column of the sheet, for each row. As you can see in my code:

import fileinput
import csv
import pyexcel as pe
records = pe.iget_records(file_name="test.xlxs")






cho = raw_input("\nStart Forecaster on file?:<1/0>")

if cho == 1:

  for record in records:
     rem = record[i,0]
     sold1 = record[i,1]
     sold2 = record[i,2]

     rem = int(rem)
     sold1 = int(sold1)
     sold2 = int(sold2)
     result = forecast(rem,sold1,sold2)
     record[i,4] = result
  print "Forecast Complete! Please check the file!"


else:
  quit()  




def calculate(rem,sold1,sold2):

   result = ((l+t)/2)*3
   return result





def forecast(rem,sold1,sold2):

    if (rmn == 0 and sold1 == 0 and sold2 ==0): #All ZERO
          return 15
    elif (rmn == 0 and sold1 == 0 and sold2 < 10): #ALL FOR ONE PRODUCT VALUE
          return sold2*3
    elif (rmn == 0 and sold1 < 10 and sold2 ==0):
          return sold1*3
    elif (rmn < 10 and sold1 == 0 and sold2 == 0):
          return rmn*3
     #END FOR ONE PRODUCT VALUE
    elif (rmn>= 10 and  sold1>=10 and sold2>=10):

          if((rmn/3)>=(sold1+10) or (rmn/3)>=(sold1+10)):
              return 0
          else:
              return calculate(rmn,sold1,sold2)-rmn
    elif (rmn<10 and sold1<10 and sold2<10):
          return calculate(rmn,sold1,sold2)
    elif (rmn == 0 and sold1>=10 and sold2>=10):
          return calculate(rmn,sold1,sold2)
    else:
          return sold1

... There were no errors but it didnt have any effect on the csv file. Any ideas? Also at print "Forecast Complete! Please check the file!" .. when i run the program it doesn't get there which means there has to be something wrong with the looping? I'm figuring it out right now. But I want to ask for help as well.

Original file:

1  2  3
1  2  3
1  2  3

What I wanted to happen:

1  2  3  result(digits)
1  2  3  result(digits)
1  2  3  result(digits)

I don't use the library specifically, but it doesn't look like you're writing anything to the file anywhere. records = pe.iget_records(file_name="test.xlxs") is just pulling the file contents into Python, where you manipulate it and then throw the results away. Have a look at the tutorial on writing/saving changes. — roganjosh
– roganjosh, Commented Apr 13, 2017 at 15:54
I tried records.save_as("test.xlsx") but still it doesn't work, how do you see my loop? is there something wrong with it? — dekt
– dekt, Commented Apr 13, 2017 at 16:24
I don't have time to play with the library at the moment and no test data. However, you should verify with a print of the data before saving that you have actually calculated the things you think you have. — roganjosh
– roganjosh, Commented Apr 13, 2017 at 16:50
may i suggest the pandas,openpyxl,or xlsxwriter modules instead of pyexcel? — MattR
– MattR, Commented Apr 13, 2017 at 20:50

chfw · Accepted Answer · 2017-04-19 10:39:23Z

Short answer

pyexcel.iget_records returns a list of dictionary, and is suitable for data with a header row. 'records.save_as' will not work because the returned data structure is a standard Python list, which naturally not has save_as function.

pyexcel.Sheet instances would have a save_as function, but 'pyexcel.get_sheet' should be used in your code. Or pyexcel.save_as, the module level function could save an array into a file. See the example here.

Sample solution

>>> import pyexcel as  p
>>> sheet = p.get_sheet(file_name='test.xlsx') # pip install pyexcel-xlsx
>>> sheet
Sheet 1:
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
>>> for row in sheet:
...      print row
...
[1, 2, 3]
[1, 2, 3]
[1, 2, 3]
>>> results = []
>>> for row in sheet:
...     results.append(sum(row)) # <- do your own forcast here
...
>>> results
[6, 6, 6]
>>> sheet.column += results
>>> sheet
Sheet 1:
+---+---+---+---+
| 1 | 2 | 3 | 6 |
+---+---+---+---+
| 1 | 2 | 3 | 6 |
+---+---+---+---+
| 1 | 2 | 3 | 6 |
+---+---+---+---+
>>> sheet.save_as('new.csv')

Long answer

pyexcel helps you get python data structure in one liner. There are 4 functions: get_array, get_dict, get_records and get_book_dict. Two streaming functions are provided to handle bigger data size: iget_array and iget_records. It is assumed that the developer needs the data for analysis only.

pyexcel provides two custom functions to help you manipulate the data: get_sheet and get_book. The former one returns pyexcel.Sheet and the latter returns pyexcel.Book. It is assumed that the developer needs to manipulate the rows, colums and sheets, then save the sheets/books back to an excel file.

With that said, however, the generic python data structure could easily be stored as an excel file. Here are three save functions at module level: save_as and save_book_as. Here is an example on how to save an array into an xls file.

Comparing with pandas, pyexcel is a light-weight, easy-to-install, simple-to-understand and component-based excel data processing package. However, it does not aim to replace pandas but to provide an alternative to data analysis tasks that are less complex.

Comparing with openpyxl and xlsxwriter, pyexcel lets you focus on data, instead of file formats, where you could reuse your code to handle ods, xls and csv file formats without code changes.

Collectives™ on Stack Overflow

Processing Excel Data Using Pyexcel

1 Answer 1

Short answer

Sample solution

Long answer

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Short answer

Sample solution

Long answer

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related