Create Excel Files in Python

create-excel-file-with-chart.py

# create a new XLSX workbook
workbook = Workbook(FileFormatType.XLSX)
# obtaining the reference of the first worksheet
worksheets = workbook.getWorksheets()
sheet = worksheets.get(0)
# adding some sample value to cells
cells = sheet.getCells()
cell = cells.get("A1")
cell.setValue(50)
cell = cells.get("A2")
cell.setValue(100)
cell = cells.get("A3")
cell.setValue(150)
cell = cells.get("B1")
cell.setValue(4)
cell = cells.get("B2")
cell.setValue(20)
cell = cells.get("B3")
cell.setValue(50)
# get charts in worksheet
charts = sheet.getCharts()
# adding a chart to the worksheet
chartIndex = charts.add(ChartType.PYRAMID, 5, 0, 15, 5)
chart = charts.get(chartIndex)
# adding NSeries (chart data source) to the chart ranging from "A1"
# cell to "B3"
serieses = chart.getNSeries()
serieses.add("A1:B3", True)
# write the Excel file 
workbook.save("workbook_with_chart.xlsx")

create-excel-file-with-image.py

# create a new XLSX workbook
workbook = Workbook(FileFormatType.XLSX)
worksheet = workbook.getWorksheets().get(0)
# Insert a string value to a cell
worksheet.getCells().get("C2").setValue("Image")
# set the 4th row height
worksheet.getCells().setRowHeight(3, 150)
# set the C column width
worksheet.getCells().setColumnWidth(3,50)
# add a picture to the D4 cell
index = worksheet.getPictures().add(3, 3, "aspose-cells-for-python.png")
# get the picture object
pic = worksheet.getPictures().get(index)
# save the Excel file 
workbook.save("workbook_with_image.xlsx")

create-excel-file-with-pivot-table.py

# create a new XLSX workbook
workbook = Workbook(FileFormatType.XLSX)
# obtaining the reference of the newly added worksheet
sheetIndex = workbook.getWorksheets().add()
sheet = workbook.getWorksheets().get(sheetIndex)
cells = sheet.getCells()

# setting the value to the cells
cell = cells.get("A1")
cell.setValue("Sport")
cell = cells.get("B1")
cell.setValue("Quarter")
cell = cells.get("C1")
cell.setValue("Sales")

cell = cells.get("A2")
cell.setValue("Golf")
cell = cells.get("A3")
cell.setValue("Golf")
cell = cells.get("A4")
cell.setValue("Tennis")
cell = cells.get("A5")
cell.setValue("Tennis")
cell = cells.get("A6")
cell.setValue("Tennis")
cell = cells.get("A7")
cell.setValue("Tennis")
cell = cells.get("A8")
cell.setValue("Golf")

cell = cells.get("B2")
cell.setValue("Qtr3")
cell = cells.get("B3")
cell.setValue("Qtr4")
cell = cells.get("B4")
cell.setValue("Qtr3")
cell = cells.get("B5")
cell.setValue("Qtr4")
cell = cells.get("B6")
cell.setValue("Qtr3")
cell = cells.get("B7")
cell.setValue("Qtr4")
cell = cells.get("B8")
cell.setValue("Qtr3")

cell = cells.get("C2")
cell.setValue(1500)
cell = cells.get("C3")
cell.setValue(2000)
cell = cells.get("C4")
cell.setValue(600)
cell = cells.get("C5")
cell.setValue(1500)
cell = cells.get("C6")
cell.setValue(4070)
cell = cells.get("C7")
cell.setValue(5000)
cell = cells.get("C8")
cell.setValue(6430)

pivotTables = sheet.getPivotTables()
# adding a PivotTable to the worksheet
index = pivotTables.add("=A1:C8", "E3", "PivotTable2")
# accessing the instance of the newly added PivotTable
pivotTable = pivotTables.get(index)
# unshowing grand totals for rows.
pivotTable.setRowGrand(False)
# dragging the first field to the row area.
pivotTable.addFieldToArea(PivotFieldType.ROW, 0)
# dragging the second field to the column area.
pivotTable.addFieldToArea(PivotFieldType.COLUMN, 1)
# dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 2)

# write the Excel file 
workbook.save("workbook_with_pivot_table.xlsx")

create-excel-file.py

# create a new XLSX workbook
wb = Workbook(FileFormatType.XLSX)
# insert value in the cells
wb.getWorksheets().get(0).getCells().get("A1").putValue("Hello World!")
# save workbook as .xlsx file
wb.save("workbook.xlsx")

update-excel-file.py

# create a new XLSX workbook
wb = Workbook("workbook.xlsx")
# insert value in the cells
wb.getWorksheets().get(0).getCells().get("A1").putValue("Location")
wb.getWorksheets().get(0).getCells().get("B1").putValue("Person")
wb.getWorksheets().get(0).getCells().get("A2").putValue("Home")
wb.getWorksheets().get(0).getCells().get("B2").putValue("abc")
wb.getWorksheets().get(0).getCells().get("A3").putValue("Office")
wb.getWorksheets().get(0).getCells().get("B3").putValue("xyz")
# save workbook as .xlsx file
wb.save("workbook-updated.xlsx")