excel使用openpyxl

#!/usr/bin/env python
# encoding: utf-8
import re
import openpyxl
'''
excel使用openpyxl
pip install openpyxl
https://blog.csdn.net/weixin_41546513/article/details/109555832
'''

# 去除HTML标签，截取20个字符
def parse_html(html):
    pattern = re.compile(r'<[^>]+>', re.S)
    return pattern.sub('', html)[0:30].replace("&nbsp;", "")


def trans_html(str):
    return str.replace('"', '\\"').replace("'", "\\'").replace('”', '\\”').replace('“', '\\“')


# INSERT INTO `bim_information_article_details`(`information_head`,`information_content`,`abstractDoc`, `dics_id`, `create_user`, `create_time`,`update_time`,`cover`,`disabled_status`) VALUES ();
def parse_excel():
    file_end = open('news.sql', 'w', encoding='utf-8')
    workbook = openpyxl.load_workbook('news.xlsx')
    worksheet = workbook.get_sheet_by_name('news')
    row_num = 0
    for row in worksheet.rows:
        row_num = row_num + 1
        if row_num == 1:
            continue
        # if row_num > 2:
        #     break
        # 一行
        rowstr = "INSERT INTO `bim_information_article_details`(`information_head`,`information_content`,`abstractDoc`, `dics_id`, `create_user`, `create_time`,`update_time`,`cover`,`disabled_status`) VALUES ('"
        cell_num = 0
        for cell in row:
            cell_num = cell_num + 1
            # 标题
            if cell_num == 16:
                # rowstr = rowstr + str(cell.value) + "@@@"
                rowstr = rowstr + trans_html(str(cell.value)) + "','"
            # 内容
            if cell_num == 25:
                # rowstr = rowstr + str(cell.value) + "@@@"
                rowstr = rowstr + trans_html(str(cell.value)) + "','"
                rowstr = rowstr + trans_html(parse_html(str(cell.value))) + "','"
            # 字典
            if cell_num == 41:
                value = cell.value
                # rowstr = rowstr + str(value) + "@@@"
                # 软件新闻
                if value == 665:
                    rowstr = rowstr + str(39) + "','"
                # 公司新闻
                if value == 666:
                    rowstr = rowstr + str(40) + "','"
                # 综合新闻
                if value == 667:
                    rowstr = rowstr + str(47) + "','"
                # 政策法规
                if value == 668:
                    rowstr = rowstr + str(48) + "','"
                # 市场活动
                if value == 669:
                    rowstr = rowstr + str(49) + "','"
            # 时间
            if cell_num == 46:
                # rowstr = rowstr + str(cell.value) + "$$$"
                rowstr = rowstr + str(32) + "','"  # xfxadmin
                rowstr = rowstr + str(cell.value) + "','"
                rowstr = rowstr + str(cell.value) + "','"
                rowstr = rowstr + "','"
                rowstr = rowstr + "0'"
        file_end.write(rowstr)
        file_end.write(");\n")


if __name__ == '__main__':
    parse_excel()