python with excel

[jackjindtnt]

import openpyxl ---> import lib import os ----> import lib cwd=os.getcwd() os.chdir('path') os.list('.') wb = openpyxl.load_workbook('file_path') type(wb) --- > type of excel file wb.get_sheet_names() --> get sheet names of that excel file sheet1=wb.get_sheet_by_name( ' sheet_name ' ) type(sheet1) sheet1.title sheet1.max_row sheet1.max_column top_sheet= wb.active --- > sheet that's on top when the workbook is open in Excel wb.create_sheet(title='sheet_name') wb.create_sheet(index=1, title= 'At index 1' ) --> create a sheet is at index 1 on workbook of Excel wb.save(new_excel) --> save a new excel file wb.remove_sheet('sheet_name')

---

Read and Write data at cell level

sheet1['Cell'].value a=sheet1['Cell'] a.row a.column c=sheet1.cell(row=2,column=3) c.value lista=[] for i in range(1,sheet.max_column+1): e=sheet,cell(row=1,column=i) print (e.value,end=' ') lista.append(e.value)

for item in lista: print (item)

for i in range(0,len(lista)): e=sheet1.cell(row=2,column=i+3) e.value=lista[i]

for j in range(1,sheet.max_row+1): e=sheet1.cell(row=j,column=1) print (e.value)

listab=[] for i in range(1,sheet.max_row+1) for j in range(1,sheet.max_column+1) e=sheet.cell(row=i,column=j) print (e.value) listab[r-1].append(e.value) e.value=listab[i-1][j-1]

Open a new text file and write the contents of countyData to it.

print('Writing results...') resultFile = open('census2010.py', 'w') resultFile.write('allData = ' + pprint.pformat(countyData)) resultFile.close() print('Done.')

Loop through the rows and update the prices.

for rowNum in range(2, sheet.max_row): # skip the first row ❷ produceName = sheet.cell(row=rowNum, column=1).value ❸ if produceName in PRICE_UPDATES: sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]

❹ wb.save('updatedProduceSales.xlsx')

Setting the Font Style of Cells from openpyxl.styles import Font

import openpyxl from openpyxl.styles import Font wb = openpyxl.Workbook() sheet = wb.get_sheet_by_name('Sheet') ❶ >>> italic24Font = Font(size=24, italic=True) ❷ >>> sheet['A1'].font = italic24Font sheet['A1'] = 'Hello world!' wb.save('styled.xlsx')

Font Objects

import openpyxl from openpyxl.styles import Font wb = openpyxl.Workbook() sheet = wb.get_sheet_by_name('Sheet')

fontObj1 = Font(name='Times New Roman', bold=True) sheet['A1'].font = fontObj1 sheet['A1'] = 'Bold Times New Roman'

fontObj2 = Font(size=24, italic=True) sheet['B3'].font = fontObj2 sheet['B3'] = '24 pt Italic'

wb.save('styles.xlsx')

Formulas

import openpyxl wb = openpyxl.Workbook() sheet = wb.active sheet['A1'] = 200 sheet['A2'] = 300 sheet['A3'] = '=SUM(A1:A2)' wb.save('writeFormula.xlsx')

Setting Row Height and Column Width

import openpyxl wb = openpyxl.Workbook() sheet = wb.active sheet['A1'] = 'Tall row' sheet['B2'] = 'Wide column' sheet.row_dimensions[1].height = 70 sheet.column_dimensions['B'].width = 20 wb.save('dimensions.xlsx')

Merging and Unmerging Cells

import openpyxl wb = openpyxl.Workbook() sheet = wb.active sheet.merge_cells('A1:D3') sheet['A1'] = 'Twelve cells merged together.' sheet.merge_cells('C5:D5') sheet['C5'] = 'Two merged cells.' wb.save('merged.xlsx')

import openpyxl wb = openpyxl.load_workbook('merged.xlsx') sheet = wb.active sheet.unmerge_cells('A1:D3') sheet.unmerge_cells('C5:D5') wb.save('merged.xlsx')

Freeze Panes

import openpyxl wb = openpyxl.load_workbook('produceSales.xlsx') sheet = wb.active sheet.freeze_panes = 'A2' wb.save('freezeExample.xlsx')

Charts

import openpyxl wb = openpyxl.Workbook() sheet = wb.active for i in range(1, 11): # create some data in column A sheet['A' + str(i)] = i

refObj = openpyxl.chart.Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)

seriesObj = openpyxl.chart.Series(refObj, title='First series')

chartObj = openpyxl.chart.BarChart() chartObj.title = 'My Chart' chartObj.append(seriesObj) sheet.add_chart(chartObj, 'C5') wb.save('sampleChart.xlsx')

github.com/yanGT