python3处理xls并flask显示

前言：

工作中有需求对xls文件进行生成和显示，这里就将对应的xls文件的处理，读取和flask展示代码罗列出来，方便大家使用：

需要的模块，这里需要注意版本号，如果直接安装使用报错，就指定如下版本安装：

pip install xlrd==1.2.0
pip install xlwt==1.3.0

文件读取：

首先要对xls文件进行读取，读取代码如下：

def ReadExelFile(ProjectName,ScanTime):
    ProjectDir = JarReportDir + ".xls"
    fixexel = []
    nofixexel = []
    rows = 0

    workbook = xlrd.open_workbook(ProjectDir)
    table = workbook.sheets()[0]

    for row in range(0,table.nrows):
        rows = row
        if table.row_values(row)[0] == "":
            rows = rows + 1
            break
        fixexel.append(table.row_values(row))

    for row in range(rows,table.nrows):
        rows = row
        if table.row_values(row)[0] == "No Fix Version:":
            continue
        if table.row_values(row)[0] == "":
            rows = rows + 1
            break
        nofixexel.append(table.row_values(row))

    return fixexel,nofixexel,falseexel,errorexel

Falsk展示：

fask处理代码：

@app.route('/ViewExelReport', methods=['GET'])
def ViewExelReport():
    try:
        cookie = request.cookies.get("cookie")
        user = engine.CheckCookie(cookie)
        if user == "":
            return render_template("login.html")
        ProjectName = request.args.get('name')
        BackArray = engine.GetProjectName()
        ScanTime = request.args.get('time')
        fixexel,nofixexel,falseexel,errorexel = engine.ReadExelFile(ProjectName, ScanTime)
        return render_template("viewexel.html", n_projectname = BackArray, n_getname=ProjectName, n_fixexel=fixexel, n_nofixexel=nofixexel, n_user=user)
    except Exception as e:
        return render_template('error-500.html')

 html代码如下：

                     <div class="col-md-12">
                        <div class="white-box">
                          <h2 class="header-title">待修复版本</h2>
                            <div class="table-responsive">
                              <table class="table table-hover">
                                <thead>
                                  <tr>
                                    <th>Jar名称</th>
                                    <th>Maven版本</th>
                                  </tr>
                                </thead>
                                <tbody>

                                  {% for i in range(1, n_fixexel|length ) %}
                                    <tr>
                                      <td><font color="black">{{ n_fixexel[i][0][:-4] }}</font></td>
                                      <td><font color="DarkOrange">{{ n_fixexel[i][2][10:] }}</font></td>
                                      <td><font color="red">{{ n_fixexel[i][3] }}</font></td>
                                      <td><font color="red">{{ n_fixexel[i][4] }}</font></td>
                                      <td><font color="blue">{{ n_fixexel[i][5] }}</font></td>
                                      <td><font color="blue">{{ n_fixexel[i][6] }}</font></td>
                                      <td>
                                        <a href="{{ n_fixexel[i][7] }}">
                                          <span class="btn btn-pink">
                                            <i class="fa fa-download"></i>
                                            <span>进入下载页面</span>
                                          </span>
                                        </a>
                                      </td>
                                      <td>
                                        <a href="{{ n_fixexel[i][8] }}">
                                          <span class="btn btn-purple">
                                            <i class="fa fa-play"></i>
                                            <span>查看详情</span>
                                          </span>
                                        </a>
                                      </td>

                                    </tr>
                                  {% endfor %}

                                </tbody>
                              </table>
                            </div>

                        </div>
                       </div>

展示如下：

 

文件生成：

 生成代码如下：

    def WriteNewXlsx(self):
        file_name = time.strftime("%Y%m%d", time.localtime())
        jar_xlsx = xlwt.Workbook(encoding="utf-8", style_compression=0)
        sheet1 = jar_xlsx.add_sheet('OutPut', cell_overwrite_ok=True)

        for i in range(0,len(self.Project)):
            sheet1.write(0,i,self.Project[i])

        numrow = 1
        ## 对数据分类 成功
        for i in range(0, len(self.DependencyJarName)):
            if self.SearchState[i] == '0':
                sheet1.write(numrow, 0, self.DependencyJarName[i])
                sheet1.write(numrow, 1, self.VulnerabilityIdJar[i])
                sheet1.write(numrow, 2, self.PackageJar[i])
                sheet1.write(numrow, 3, self.HighestSeverity[i])
                sheet1.write(numrow, 4, self.CVECount[i])
                sheet1.write(numrow, 5, self.NewestId[i])
                sheet1.write(numrow, 6, self.FixVulnerabilityId[i])
                sheet1.write(numrow, 7, self.JarDownloadUrl[i])
                sheet1.write(numrow, 8, self.InforUrl[i])
                sheet1.write(numrow, 9, self.JarFileDir[i])
                sheet1.write(numrow, 10, self.FindJarNum[i])
                numrow = numrow + 1

        jar_xlsx.save( self.xlsxsavedir +'\\'+ file_name +'.xls')
        self._signal.emit( file_name +'.xls' + '保存完成\n')
        QApplication.processEvents()

使用上述代码即可将对应的内容写入对应的xls文件列中

代码很简单，但是其中也遇到了很多坑，这里放出来方便大家的使用，让大家使用中少遇一些坑。