Python Openpyxl xlsx转html_python excel转化html

目录​​​​​​​

Excel转Html

示例

函数

Openpyxl知识点

其他Excel解析库问题

Excel转Html

示例

函数

import openpyxl
 
from openpyxl.styles import Border
from openpyxl.styles import Font
from openpyxl.styles import Side
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill
from openpyxl.cell import MergedCell
 
 
# 模式：adaptive / normal
def transform_xlsx_to_html(m_sheet, m_mode):
    # sheet.min_row的值在后续数据获取过程中会改变，原因不明
    m_min_row = m_sheet.min_row
    m_max_row = m_sheet.max_row
    m_min_col = m_sheet.min_column
    m_max_col = m_sheet.max_column
    if m_min_row == m_max_row == m_min_col == m_max_col and not m_sheet.cell(m_min_row, m_min_col).value:
        return ''
    m_merged_cell_dict = {}  # 用于储存 所有合并单元格的左上单元格对象
    m_width_dict = {}  # 用于储存 所有列的列宽,px
    m_height_dict = {}  # 用于储存 所有列的行高,px
    # 查询行高
    for m_i in range(m_min_row, m_max_row + 1):
        m_height = 13.5
        if m_sheet.row_dimensions[m_i].height:
            m_height = m_sheet.row_dimensions[m_i].height
        m_height_dict[str(m_i)] = m_height
    # 查询列宽
    for m_i in range(m_min_col, m_max_col + 1):
        m_col_name = openpyxl.utils.get_column_letter(m_i)
        m_width = m_sheet.column_dimensions[m_col_name].width
        if m_mode == 'normal':
            m_width = m_sheet.column_dimensions[m_col_name].width * 6
        m_width_dict[m_col_name] = m_width
    # 找出所有合并区域的行高，列宽，向右合并距离，向下合并距离
    for m_merged_range in m_sheet.merged_cells.ranges:
        m_width = 0  # 定义列宽
        m_height = 0  # 定义行高
        for m_i in range(m_merged_range.min_row, m_merged_range.max_row + 1):
            m_height += m_height_dict[str(m_i)]
        for m_i in range(m_merged_range.min_col, m_merged_range.max_col + 1):
            m_col_name = openpyxl.utils.get_column_letter(m_i)
            m_width += m_width_dict[m_col_name]
        m_cell = m_sheet.cell(row=m_merged_range.min_row, column=m_merged_range.min_col)  # 选择合并区域左上单元格
        m_colspan = m_merged_range.max_col - m_merged_range.min_col + 1  # 向右合并长度
        m_rowspan = m_merged_range.max_row - m_merged_range.min_row + 1  # 向下合并长度
        m_merged_cell_dict[m_cell] = (m_height, m_width, m_colspan, m_rowspan)
    # 开始写入数据到table标签
    m_html = '''<table style="border: 1px solid #A9A9A9">'''
    for m_i in range(m_min_row, m_max_row + 1):
        tr = '''<tr>'''
        for m_j in range(m_min_col, m_max_col + 1):
            m_cell = m_sheet.cell(m_i, m_j)
            m_col_name = openpyxl.utils.get_column_letter(m_j)
            # html 字体大小
            m_font_size = str(int(m_cell.font.size) + 3) if m_cell.font.size else 10
            # html 字体加粗
            m_font_weight = '700' if m_cell.font.b else '400'
            # html 边框格式
            m_border = 'border: 1px solid #A9A9A9' if m_cell.value or m_cell.value == 0 else 'border: 0'
            # 水平位置
            m_align_horizontal = 'text-align: {}'.format(m_cell.alignment.horizontal) if m_cell.alignment.horizontal else ''
            # 垂直位置
            m_align_vertical = 'vertical-align: {}'.format(m_cell.alignment.vertical) if m_cell.alignment.vertical else ''
            # 字体颜色 颜色未设置时有时会存在 Values must be of type <class 'str'> 输出
            if m_cell.font.color and 'str' not in str(m_cell.font.color.rgb):
                m_font_color = 'color: #{}'.format(m_cell.font.color.rgb[2:])
            else:
                m_font_color = 'color: #000000'
            # 背景颜色 颜色未设置时有时会存在 Values must be of type <class 'str'> 输出
            if m_cell.fill.fgColor and 'str' not in str(m_cell.fill.fgColor.rgb) and m_cell.fill.fgColor.rgb[2:] != '000000':
                m_fill_color = 'background-color: #{}'.format(m_cell.fill.fgColor.rgb[2:])
            else:
                m_fill_color = 'background-color: #FFFFFF'
            # 单元格格式
            style = f'''{m_fill_color}; {m_border}; {m_font_color};
                        font-style: normal;
                        font-family: 微软雅黑;
                        font-size: {m_font_size}px; 
                        font-weight: {m_font_weight}; 
                        {m_align_horizontal}; {m_align_vertical}; '''
 
            if m_mode == 'normal':
                if m_cell in m_merged_cell_dict.keys():
                    style += f'''height: {m_merged_cell_dict[m_cell][0]}px; width: {m_merged_cell_dict[m_cell][1]}px; '''
                else:
                    style += f'''height: {m_height_dict[str(m_i)]}px; width: {m_width_dict[m_col_name]}px; '''
            else:
                style += f'''word-break: keep-all; word-wrap:break-word; white-space: nowrap'''
 
            text = m_cell.value if m_cell.value or m_cell.value == 0 else ''
            # 文本处理：小数转为百分比展示
            text = '{:.2f}%'.format(text * 100) if text and type(text) == float else text
            # 文本处理：字符串换行展示
            text = text.replace('\n', '<br/>') if text and type(text) == str else text
 
            td = ""
            if m_cell in m_merged_cell_dict.keys():
                td = f'''<td colspan="{m_merged_cell_dict[m_cell][2]}" rowspan="{m_merged_cell_dict[m_cell][3]}" style="{style}">{text}</td>'''
            elif not isinstance(m_cell, MergedCell):
                td = f'''<td style="{style}">{text}</td>'''
            tr = tr + td
        tr = tr + '''</tr>'''
        m_html += tr
    m_html += '''</table>'''
    return m_html

Openpyxl知识点

 示例代码：

import openpyxl
 
from openpyxl.styles import Border
from openpyxl.styles import Font
from openpyxl.styles import Side
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill
from openpyxl.cell import MergedCell
 
# 打开Excel
workbook = openpyxl.load_workbook(filename='D:\Project\test.xlsx')
print(workbook.sheetnames)
 
# 创建删除sheet
workbook.create_sheet('test')
workbook.remove('test')
 
# 行数列数
sheet = workbook.sheetnames[0]
print(sheet.min_row)
print(sheet.max_row)
print(sheet.min_column)
print(sheet.max_column)
# 行高列宽
print(sheet.row_dimensions[1].height )
print(sheet.column_dimensions['A'].width)
 
# 设置行高列宽
sheet.row_dimensions[1].height = 10
sheet.column_dimensions['A'].width = 10
# 获取下标对应的列名（从1开始）
column_name = openpyxl.utils.get_column_letter(1)
 
# 设置不显示网格线
sheet.views.sheetView[0].showGridLines = False
# 合并单元格
sheet.merge_cells(start_row=1, end_row=2, start_column=3, end_column=4)
 
# 设置数字格式
sheet.cell(row=2, column=2, value=100).number_format = '0'
sheet.cell(row=3, column=3, value=100).number_format = '0.00%'
 
# 设置单元格
sheet.cell(row=1, column=1, value='test')
# 设置填充颜色
fill = PatternFill('solid', fgColor='123456')
# 设置字体格式
font = Font(u'微软雅黑', size=16, bold=True, italic=False, strike=False, color='000000')
# 设置文本对齐
alignment = Alignment(wrap_text=True, horizontal='center', vertical='center')
# 方式一
sheet.cell(row=1, column=1, fill=fill, font=font, alignment=alignment, value='test')
# 方式二
sheet.cell(row=1, column=1).value = 'test'
sheet.cell(row=1, column=1).fill = fill
sheet.cell(row=1, column=1).font = font
sheet.cell(row=1, column=1).alignment = alignment
 
# 设置边框 方式一 合并单元格时右侧边框设置会不成功
border = Border()
border.left = Side(border_style='double', color='000000')
border.top = Side(border_style='double', color='000000')
border.right = Side(border_style='double', color='000000')
border.bottom = Side(border_style='double', color='000000')
# 设置边框 方式二 合并单元格时右侧边框设置会不成功
border = Border(left=Side(border_style='double', color='000000'),
                      top=Side(border_style='double', color='000000'),
                      right=Side(border_style='double', color='000000'),
                      bottom=Side(border_style='double', color='000000'))
 
 
# 使用下标索引时从0开始
cell = sheet[0][0]
cell = sheet.cell(row=1, column=1)
# 获取单元格信息
print(cell.value)
print(cell.coordinate)
# 获取字体信息
print(cell.font.size)
print(cell.font.b)
# 获取对齐信息
print(cell.alignment.vertical)
print(cell.alignment.horizontal)
# 判断是否为合并单元格
print(isinstance(cell, MergedCell))
 
# 获取边框信息 未初始化时不可直接获取
left = cell.border.left.style if cell.border.left else ''
top = cell.border.top.style if cell.border.top else ''
right = cell.border.right.style if cell.border.right else ''
bottom = cell.border.bottom.style if cell.border.bottom else ''
# 获取颜色信息 未初始化时不可直接获取
font_color = cell.font.color.rgb if 'str' not in str(cell.font.color.rgb) else ''
fill_color = cell.fill.fgColor.rgb if 'str' not in str(cell.fill.fgColor.rgb) else ''
 
# 获取合并单元格信息
merged_list = sheet.merged_cells.ranges
merged_cell = sheet.merged_cells.ranges[0]
print(merged_cell.min_row)
print(merged_cell.max_row)
print(merged_cell.min_col)
print(merged_cell.max_col)

其他Excel解析库问题

参考资料：

python将excel转html的table标签（含合并单元格）_python xlsx转html_weixin_50702169的博客-CSDN博客