公共模块引入:
import openpyxl,sys
from openpyxl.utils import get_column_letter
项目一:
创建程序,从命令行接受数字N,在一个Excel电子表格中创建一个N*N的乘法表执行方式:>>>python3 ExcelProjects.py 6
def test1(N):
wb = openpyxl.Workbook()
sheet = wb.active
for rowNum in range(2,N+2):
for columnNum in range(2,N+2):
cn = get_column_letter(columnNum)
sheet[cn+'1'] = columnNum-1
sheet['A'+str(rowNum)] = rowNum-1
#print(sheet[cn+'1'].value,sheet['A'+str(rowNum)].value)
sheet[cn +str(rowNum)] = sheet[cn+'1'].value*sheet['A'+str(rowNum)].value
wb.save('/Users/wangxiaowei/Desktop/PythonCode/documents/multiplicationTable.xlsx')
项目二:
在表格第N行插入M个空行
def test2(N,M,FILENAME):
filename = '/Users/wangxiaowei/Desktop/PythonCode/documents/'
wb = openpyxl.load_workbook(filename + FILENAME)
sheet = wb.active
wb1 = openpyxl.Workbook()
sheet1 = wb1.active
co = get_column_letter(sheet.max_column) + str(sheet.max_row)
for rowOfCell in sheet['A1':co]:
for cellObj in rowOfCell:
if cellObj.row >= N :
sheet1.cell(row = cellObj.row + M,column = cellObj.column).value = cellObj.value
else:
sheet1[cellObj.coordinate] = cellObj.value
wb1.save(filename + 'simpleCopy.xlsx')
项目三:
编写一个程序,读入几个文本文件的内容,并将这些内容插入一个电子表格,每行写入一行文本;第一个文本文件中的行将写入列A中的单元格,第二个文本文件中的行将写入列B中的单元格,以此类推
def test3(textFileList):
#新建一个Excel表格
wb = openpyxl.Workbook()
sheet = wb.active
for textFileNum in range(0,len(textFileList)):
textPath = '/Users/wangxiaowei/Desktop/PythonCode/documents/'
textFile = open(textPath+str(textFileList[textFileNum]),'r')
# textFile.readlines()只第一次读取有数据,所以需要先存储
textLists = textFile.readlines()
for fileLine in range(1,len(textLists)+1):
tt = get_column_letter(textFileNum+1) + str(fileLine)
sheet[tt].value = textLists[fileLine-1].strip()
wb.save(textPath+'textExcel.xlsx')
textFile.close()
项目四:
与test3功能相反
def test4(excelName):
excelPath = '/Users/wangxiaowei/Desktop/PythonCode/documents/'
wb = openpyxl.load_workbook(excelPath + excelName)
sheet = wb.active
#有多少列就生成多少个文本文件
for excelColumn in range(0,sheet.max_column):
textFile = open(excelPath+'excelText{}.txt'.format(excelColumn+1),'w')
# sheet.columns返回生成器,故需要用list(sheet.columns)
for lineNum in list(sheet.columns)[excelColumn]:
#每一列的行数不同时,输出空单元格
if lineNum.value == None:
textFile.write('\n')
continue
textFile.write(str(lineNum.value))
textFile.write('\n')
textFile.close()
def main():
#test1(int(sys.argv[1]))
#test2(int(sys.argv[1]),int(sys.argv[2]),sys.argv[3])
#test3(sys.argv[1:])
test4('textExcel.xlsx')
if __name__ == '__main__':
main()