excel manipulation

Python SDK to manipulate Excel files.

Spreadsheets are widely used for storing, manipulating, organizing and presenting data. A spreadsheet can store a small or large set of data in a system of cells, organized into rows and columns. A cell can contain a data value, or a calculation, such as sums or percentages. This means that a spreadsheet can hold many different types of data values as well as various types of calculation on those values.

Aspose.Cells Cloud allows you to create workbooks, add worksheets (spreadsheets), rows and columns and manipulate or present data in any language including .NET, Java, PHP, Ruby, Rails, Python, jQuery and many more. You can use it with any language or platform that supports REST. (Almost all platforms and languages support REST and provide native REST clients to work with REST APIs.)

This post covers creating and populating Microsoft Excel workbooks and worksheets in Python. Check Create an Empty Excel Workbook, Add a new Excel Worksheet and Set Value of a Cell in a Worksheet for examples on how to use it with other languages.

First create an empty workbook file, then add the required worksheets and finally set the values of the cells in the worksheets. the following steps describe the process in detail.

Create Excel Workbook from Scratch

You can use either Python REST or Aspose Cloud SDK for Python to create and populate Excel workbooks. Both REST and SDK examples use Pycurl library to send HTTP request and handle HTTP response so you need to install Pycurl to use these examples.

Using Python REST

You can use the following URI to create a default empty workbook on Aspose Cloud or any supported third party storage.

https://api.aspose.cloud/v3.0/cells/NewWorkbook.xlsx

You can use following optional parameters with the above mentioned URI. All or specific parameters can be used according to your requirement.

  • storage – This parameter can be used to set storage name if you are using a third party storage.
  • folder – This parameter can be used to set the name/path of the folder where new workbook file will be created.

After building URI, you need to go through the following steps:

  1. Set App SID and App Key and sign URI. See section 2 of the following code and Sign URI method for more details.
  2. Send a PUT request to Aspose Cloud service. See section 3 of the following code and process_command method for more details.

Following is the code to create empty workbook.

**from** aspose**.**cloud**.**common **import** *****

**import** json

try**:**

####### Create new workbook #######

####### Section 1 ######

#build URI to create empty workbook

str_uri = ‘https://api.aspose.com/v1.1/cells/NewWorkbook.xlsx'

####### End Section 1 ######

####### Section 2 ######

sepcify App SID

AsposeApp**.**app_sid = ‘77******-1***-4***-a***-80**********’

sepcify App Key

AsposeApp**.**app_key = ‘*******************************’

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

####### End Section 2 ######

####### Section 3 ######

Utils**.process_command(Utils(),** signed_uri**,** ‘PUT’, ‘’, ‘’)

####### End Section 3 ######

Using Python SDK

If you want to use our Python SDK to create new workbook and add worksheets, you can download this SDK from Aspose Cloud SDK for Python. In order to use Python SDK, you need to perform following steps:

  1. Set base product URI, App SID and App Key.
  2. Create object of Workbook class and call create_empty_workbook()

Following is the code to create empty workbook.

**from** aspose**.**cloud**.**common **import** *****

**from** aspose**.**cloud**.**storage **import** *****

**from** aspose**.**cloud**.**cells **import** *****

**import** json

**try****:**

`

sepcify App SID

AsposeApp**.app_sid = ‘77****-1***-4***-a***-80**********’

sepcify App Key

AsposeApp**.**app_key = ‘*******************************’

set base product uri

Product**.**base_product_uri = ‘https://api.aspose.cloud/v3.0/'

workbook = Workbook**(‘NewWorkbook.xlsx’)**

print workbook**.create_empty_workbook()**

`

Add a New Worksheet

Following are the REST and SDK examples.

Using Python REST

You can use the following URI to add a new worksheet to previously created workbook on Aspose Cloud or any supported third party storage.

https://api.aspose.cloud/v3.0/cells/myexcel.xls/worksheets/worksheet1

You can use following optional parameters with the above mentioned URI. All or specific parameters can be used according to your requirement.

  • storage – This parameter can be used to set storage name if you are using a third party storage.
  • folder – This parameter can be used to set the name/path of the folder where new workbook file has been created.

After building URI, you need to go through the following steps:

  1. Sign URI. See section 5 of the following code and Sign URI method for more details.
  2. Send a PUT request to Aspose Cloud service. See section 6 of the following code and process_command method for more details.

Following is the code to add a new worksheet.

####### Add new worksheet #######

####### Section 4 ######

#build URI to add a new worksheet

str_uri **=** https://api.aspose.cloud/v3.0/cells/myexcel.xls/worksheets/worksheet1

`

####### End Section 4 ######

####### Section 5 ######

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

####### End Section 5 ######

####### Section 6 ######

Utils**.process_command(Utils(),** signed_uri**,** ‘PUT’, ‘’, ‘’)

####### End Section 6 ######

`

Using Python SDK

If you want to use our Python SDK, you can download this SDK from Aspose Cloud SDK for Python and call add_worksheet() method as you can see in the following code.

**print** workbook**.**add_worksheet**(**'NewWorksheet'**)**

Set Cell Values

Following are the REST and SDK examples.

Using Python REST

You can use the following URI to set value of a cell to previously created workbook on Aspose Cloud or any supported third party storage.

https://api.aspose.cloud/v3.0/cells/myworkbook.xlsx/worksheets/sheet1/cells/B23

You can use following optional parameters with the above mentioned URI. All or specific parameters can be used according to your requirement.

  • storage – This parameter can be used to set storage name if you are using a third party storage.
  • folder – This parameter can be used to set the name/path of the folder where new workbook file has been created.

After building URI, you need to go through the following steps:

  1. Sign URI. See section 8 of the following code and Sign URI method for more details.
  2. Send a POST request to Aspose Cloud service. See section 9 of the following code and process_command method for more details.

Following is the code to set cell values.

####### Set value of cells #######

####### Section 7 ######

#build URI to set value of a cell

str_uri **=** 'https://api.aspose.cloud/v3.0/cells/myworkbook.xlsx/worksheets/sheet1/cells/B23?value=My%20new%20value'

`

####### End Section 7 ######

####### Section 8 ######

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

####### End Section 8 ######

####### Section 9 ######

Utils**.process_command(Utils(),** signed_uri**,** ‘POST’, ‘’, ‘’)

####### End Section 9 ######

####### Section 10 ######

#build URI to set value of a cell

str_uri = ‘https://api.aspose.cloud/v3.0/cells/myworkbook.xlsx/worksheets/sheet1/cells/B23?value=My%20new%20value'

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

Utils**.process_command(Utils(),** signed_uri**,** ‘POST’, ‘’, ‘’)

####### End Section 10 ######

`

Using Python SDK

If you want to use our Python SDK, you can download this SDK from Aspose Cloud SDK for Python and call add_worksheet() method as you can see in the following code.

worksheet **=** Worksheet**(**'NewWorkbook.xlsx'**,** 'NewWorksheet'**)**

**print** worksheet**.**set_cell_value**(**'B1'**,** 'string'**,** 'This is cell B1'**)**

**print** worksheet**.**set_cell_value**(**'B2'**,** 'string'**,** 'This is cell B2'**)**

Download New Workbook

After creating and populating new workbook, you can download it by following [Download File][9] example.

Complete Python REST Code

Following is the complete REST code after combining above mentioned steps.

**from** aspose**.**cloud**.**common **import** *****

**import** json

**try****:**

####### Create new workbook #######

####### Section 1 ######

`

#build URI to create empty workbook

str_uri = ‘https://api.aspose.cloud/v3.0/cells/myNewBook.xlsx?isWriteOver=false&checkExcelRestriction=true'

####### End Section 1 ######

####### Section 2 ######

sepcify App SID

AsposeApp**.app_sid = ‘77****-1***-4***-a***-80**********’

sepcify App Key

AsposeApp**.**app_key = ‘*******************************’

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

####### End Section 2 ######

####### Section 3 ######

Utils**.process_command(Utils(),** signed_uri**,** ‘PUT’, ‘’, ‘’)

####### End Section 3 ######

####### Add new worksheet #######

####### Section 4 ######

#build URI to add a new worksheet

str_uri = ‘https://api.aspose.cloud/v3.0/cells/myexcel.xls/worksheets/worksheet1?position=1'

####### End Section 4 ######

####### Section 5 ######

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

####### End Section 5 ######

####### Section 6 ######

Utils**.process_command(Utils(),** signed_uri**,** ‘PUT’, ‘’, ‘’)

####### End Section 6 ######

####### Set value of cells #######

####### Section 7 ######

#build URI to set value of a cell

str_uri = '’

####### End Section 7 ######

####### Section 8 ######

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

####### End Section 8 ######

####### Section 9 ######

Utils**.process_command(Utils(),** signed_uri**,** ‘POST’, ‘’, ‘’)

####### End Section 9 ######

####### Section 10 ######

#build URI to set value of a cell

str_uri = ‘https://api.aspose.cloud/v3.0/cells/myworkbook.xlsx/worksheets/sheet1/cells/B23?value=My%20new%20value'

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

Utils**.process_command(Utils(),** signed_uri**,** ‘POST’, ‘’, ‘’)

####### End Section 10 ######

####### Section 11 ######

#build URI to download output file

str_uri = ‘https://api.aspose.cloud/v3.0/cells/storage/file/mhywrokbook.xlsx'

#sign URI

signed_uri = Utils**.sign(Utils(),** str_uri**)**

response_stream = Utils**.process_command(Utils(),signed_uri,** ‘GET’)

Utils**.save_file(Utils(),response_stream,** ‘NewWorkbook.xlsx’)

####### End Section 11 ######

print ‘Done’

except Exception**,** ex**:**

print type**(ex)** # the exception instance

print ex**.**args # arguments stored in .args

print ex**.**errno

print ex**.**strerror

finally**:**

raw_input**(“Press any Key”)**

`

Complete Python SDK Code

Following is the complete SDK code after combining above mentioned steps.

**from** aspose**.**cloud**.**common **import** *****

**from** aspose**.**cloud**.**storage **import** *****

**from** aspose**.**cloud**.**cells **import** *****

**import** json

**try****:**

`

sepcify App SID

AsposeApp**.app_sid = ‘77****-1***-4***-a***-80**********’

sepcify App Key

AsposeApp**.**app_key = ‘*******************************’

set base product uri

Product**.**base_product_uri = ‘https://api.aspose.cloud/v3.0/'

workbook = Workbook**(‘NewWorkbook.xlsx’)**

print workbook**.create_empty_workbook()**

print workbook**.add_worksheet(‘NewWorksheet’)**

worksheet = Worksheet**(‘NewWorkbook.xlsx’,** ‘NewWorksheet’)

print worksheet**.set_cell_value(‘B1’,** ‘string’, ‘This is cell B1’)

print worksheet**.set_cell_value(‘B2’,** ‘string’, ‘This is cell B2’)

initialize Folder object

folder = Folder**()**

download output file

response_stream = folder**.get_file(“NewWorkbook.xlsx”)**

save the stream as local file

Utils**.save_file(Utils(),** response_stream**,** “NewWorkbook.xlsx”)

print ‘Done’

except Exception**,** ex**:**

print type**(ex)** # the exception instance

print ex**.**args # arguments stored in .args

print ex**.**errno

print ex**.**strerror

finally**:**

raw_input**(“Press any Key”)**

`