利用pandas去掉excel表格中换行符

Question

#显示第12行到14行报错

#import xlrd as xd
import xlwt
import pandas as pd
import numpy as np
import xlrd

df = pd.read_excel("E:\Excl程序\my.xlsx")##打开excel文件
io = r'E:\Excl程序\my.xlsx'
data = pd.read_excel(io,sheet_name='Sheet1')##设置读取sheet

d = []
for r in range(sheet.nrows): # 将表中数据按行逐步添加到列表中，最后转换为list结构
for c in range(sheet.ncols):
d.append(sheet.cell_value(r, c))

print(d)

l = [x.strip() for x in d if x.strip() != '']

print(l)

f = xlwt.Workbook() # 创建baidu工作薄zhi
sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True) # 创建sheet
j = 0
for i in l:
sheet1.write(j, 0, i) # 循环写入dao 竖着写zhuan
j = j + 1
f.save('text1.xls') # 保存文shu件

Answer 1

xlrd读取不了xlsx文件，使用openpyxl：

import xlwt
import pandas as pd
import numpy as np
import openpyxl
io = r't0105.xlsx'
df = pd.read_excel(io)  # 打开excel文件
wb=openpyxl.load_workbook(io)
ws=wb.active
d = []
for r in range(1,ws.max_row+1):  # 将表中数据按行逐步添加到列表中，最后转换为list结构
    for c in range(1,ws.max_column+1):
        d.append(str(ws.cell(r, c).value))

print(d)
l = [x.strip() for x in d if x.strip() != '']
print(l)
f = xlwt.Workbook()  # 创建baidu工作薄zhi
sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True)  # 创建sheet
j = 0
for i in l:
    sheet1.write(j, 0, i)  # 循环写入dao 竖着写zhuan
    j = j + 1
f.save('test1.xls')  # 保存文shu件