Search code examples
pythonimagecsvxlsx

insert images and data from csv file to xlsx file

hello guys I am new to Python and I have a problem that I hope you can help me with that.

I am using a Raspberry Pi to take photos and then save them to specific file, and also I have a data inside a csv file, what I want to do is to take the images from that folder and save them to specific cell also same for the data. Let's say image no 1 save with data in row1 col1. The problem is, I don't know how many data going to be inside the csv file and how many image, but the number of images always same as number of data inside the csv file. My codes as shown:

import xlsxwriter
import csv

csvFile = open('k1.csv')
csvfileReader = csv.reader(csvFile)

for row in csvfileReader:
workbook = xlsxwriter.Workbook('k.xlsx')
worksheet = workbook.add_worksheet('sheet1')
worksheet.set_column('B:B', 10)
worksheet.set_column('A:A', 15)
worksheet.write('A1',row[0] )
worksheet.write('B1',row[1] )
worksheet.write('C1',row[2] )
worksheet.write('A10',row[0] )
worksheet.write('B10',row[1] )
worksheet.write('C10',row[2] )

worksheet.insert_image('D1', "1.png" , {'x_scale': 0.3, 'y_scale': 0.3})
worksheet.insert_image('D10', "2.png", {'x_scale': 0.3, 'y_scale': 0.3})

workbook.close()

The result I get is shown below: here is my results

I want to know how to insert more images and data to the xlsx file if i have more that 30 images and names(data)

Solution

  • Rather than using A1 notation, you can specify a row and column number (zero indexed) in the .write() function. Adding Python's enumerate() function, it would provide you with a counter rowy that you can use as the row number.

    It assumes that your k1.csv file looks like:

    Image 1,trki,present
Image 2,Tgjkt,present
Image 3,Abcd,not present

    It will read each line from this file and put it into a different row in the XLSX file. In your example you have the rows spaced out every 9th row, so * 9 is multiplied by the row number being used, you can remove this if you want consecutive rows.

    import xlsxwriter
import csv

workbook = xlsxwriter.Workbook('k.xlsx')
worksheet = workbook.add_worksheet('sheet1')

worksheet.set_column('A:A', 15)
worksheet.set_column('B:B', 10)

with open('k1.csv', newline='') as csv_file:
    csv_reader = csv.reader(csv_file)

    for rowy, row in enumerate(csv_reader):
        worksheet.write(rowy * 9, 0, row[0])
        worksheet.write(rowy * 9, 1, row[1] )
        worksheet.write(rowy * 9, 2, row[2] )
        worksheet.insert_image(rowy * 9, 3, "1.png" , {'x_scale': 0.3, 'y_scale': 0.3})

workbook.close()

    If you are still using Python 2.x, change this line: 

    with open('k1.csv', 'rb') as csv_file:

    Note: If the script is run again, it will overwrite the existing k.xlsx with a new file. It will not append new information to the end. So if your CSV file only contains one line, it would appear that it only writes to the first line.

    To append new information each time the script is run, you would need to read the existing file in, determine the last used row number and then write new items from this point onwards. The xlsxwriter library cannot do this, it can only write new files. You would need to use the openpyxl library to do this.