将csv导入到xlsx python [英] import csv to xlsx python

问题描述

我正在尝试将csv文件中的某些数据放入存在的excel文件中. 我存在的excel文件包含图像，而xlrd无法获取图像. 我尝试使用xlsxwriter，但是它不能追加到现有的xslx. 我发现的唯一解决方案是使用openpyxl.

I'm trying to put some data from a csv file to exist excel file. my exist excel file contains images and xlrd cannot get images. I try to use xlsxwriter but it cannot append to existing xslx. the only solution I've found is to use openpyxl.

import openpyxl
xfile = openpyxl.load_workbook('my_exist_file')
sheet = xfile.get_sheet_by_name('Sheet1')
with open("my_csv", 'rb') as f:
    reader = csv.reader(f)
    for r, row in enumerate(reader):
        for c, col in enumerate(row):
           -here is my problem- 

如何将csv数据(即表)写入存在的xslx中的特定位置?我希望我的桌子从K2单元格开始.

how can I write the csv data (that is a table) to a specific location in the exist xslx? I want that my table will start at K2 cell.

谢谢！

推荐答案

读取CSV

使用 pandas.read_csv 提取信息

reading the CSV

using pandas.read_csv to extract the information

import pandas as pd
df = pd.read_csv(my_filename)

您可能需要指定的选项

• sep:使用哪个分隔符
• 编码
• 标题:第一行是标签行吗?
• index_col:是索引的第一列

灵感来自: https://stackoverflow.com/a/20221655/1562285 查看 pandas.to_excel 文档以了解其他信息可能的选项

inspired by: https://stackoverflow.com/a/20221655/1562285 check the pandas.to_excel documentation for other possible options

book = load_workbook(old_filename)
sheet_name = 'Sheet1'
with pd.ExcelWriter(new_filename, engine='openpyxl')  as writer:

    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

    df.to_excel(writer, sheet_name=sheet_name, startrow=1, startcol=10, engine='openpyxl')

startrow和startcol表示要在工作表中的何处粘贴数据.

The startrow and startcol say where in the worksheet you want to paste your data.

此方法可能会覆盖此工作表上的先前内容.如果是这样，您将不得不遍历DataFrame的列和行，并将它们半手动地添加到工作表中

This method might overwrite the previous content on this worksheet. If it does, you will have to loop over the columns and rows of the DataFrame and add them semi-manually to the worksheet

如果您要在外部插入图片，则可以使用文档

If you have the images to insert somewhere externally you can use the code from the documentation

    from openpyxl.drawing.image import Image
    ws = book['sheet_name_for_images']
    ws['A1'] = 'You should see three logos below'
    img = Image('logo.png')

    # add to worksheet and anchor next to cells
    ws.add_image(img, 'A1')

我没有对此进行测试，您可能需要在writer.sheets = ...

I did not test this, and you might need to insert this code before the writer.sheets = ...

这篇关于将csv导入到xlsx python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！