Python脚本错误地删除了.xlsx文件中创建的图表 [英] Python script erroneously erases created chart in .xlsx file

问题描述

我尝试使用Python编写脚本，该脚本从存储在文件夹层次结构中的所有.csv文件中获取一些特定值.这些值将复制到已经创建的目标文件(.xlsx)中的某些特定单元格中.目标文件还具有一些现有的空图表(在单独的工作表中)，这些空图表将使用脚本提供的值进行填充.

I tried to write a script using Python which takes some specific values from all the .csv files stored in a hierarchy of folders. These values are copied at some specific cells in a destination file (.xlsx) which has already been created. The destination file also has some existing empty charts (in separate sheets) which are to be populated with the values provided by the script.

不幸的是，运行脚本后，尽管可以运行，并且我已经在单元格中复制了所需的值，但是由于某种原因，图表消失了.由于我没有处理任何暗示要在脚本中操纵图表的工作，因此我一直无法理解为什么.

Unfortunately, after I run the script, although it works and I have the desired values copied in the cells, for some reason, the charts disappear. I haven't managed to understand why, giving the fact that I didn't work with anything that implied manipulating charts in my script.

看到无法找到这个问题的解决方案，我得出结论，我应该使用自己拥有的值在脚本中实现图表的绘制.但是，我想知道您是否知道为什么会发生这种情况.

Seeing that I couldn't find any solution to this problem, I came to the conclusion that I should implement the plotting of the charts in my script, using the values I have. However, I would like to know if you have any idea why this happens.

下面是我的代码.我不得不提到我是Python的新手. 任何有关该问题或有关代码编写的建议都将不胜感激.

Below is my code. I have to mention that I am new to Python. Any suggestions about the problem or about a better writing of the code would be greatly appreciated.

# -*- coding: utf-8 -*-

import os
import glob
import csv
import openpyxl
from openpyxl import load_workbook

#getting the paths
def get_filepaths(directory):

    file_paths = []  # array that will contain the path for each file

    # going through the folder hierarchy
    for root, directories, files in os.walk(directory):
        for filename in files:
            if filename.endswith('.csv'): 
                filepath = os.path.join(root, filename) #concatenation
                file_paths.append(filepath)  # filling the array
                print filepath
                #print file_paths[0]

    return file_paths  


#extraction of the value of interest from every .csv file   
def read_cell(string, paths):
    with open(paths, 'r') as f:
        reader = csv.reader(f)        
        for n in reader:            
            if string in n:             
                cell_value = n[1]
                return cell_value



#array containing the path extracted for each file    
paths_F = get_filepaths('C:\Dir\mystuff\files')      

#destination folder
dest = r'C:\Dir\mystuff\destination.xlsx'   

#the value of interest 
target = "something"        

#array that will contain the value for each cell    
F30 = [];     

#obtaining the values    
for selection_F in paths_F:         
        if '30cm' in selection_CD:
            val_F30 = read_cell(target, selection_F);      F30.append(val_F30)
            #print val_F30


wb = load_workbook(dest)

#the sheet in which I want to write the values extracted from the files
ws3EV = wb.get_sheet_by_name("3EV")

   cell_E6 = ws10EV.cell( 'E6' ).value = int(F30[0])
cell_E7 = ws10EV.cell( 'E7' ).value = int(F30[1])

推荐答案

我已经能够重新创建您的问题.简单地使用openpyxl加载并保存了现有的xlsx文件后，我的图表就丢失了，没有进行任何更改.

I have been able to recreate your problem. After simply loading and saving an existing xlsx file using openpyxl, my chart was lost without making any changes.

很遗憾，根据 openpyxl的文档:

警告

Openpyxl当前仅支持在工作表中创建图表. 现有工作簿中的图表将丢失.

Openpyxl currently supports chart creation within a worksheet only. Charts in existing workbooks will be lost.

它确实支持创建数量有限的图表:

It does though support the creation of a limited number of charts:

• 条形图
• 折线图
• 散点图
• 饼图

您可以使用它们重新创建所需的图表.

You could possibly recreate the required chart using these.

这篇关于Python脚本错误地删除了.xlsx文件中创建的图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！