Write a list in a excel where each element is in a seperate cell-CodePudding

I have a data set of images and using a function I compute the hash value for each of the images, now I have 128 digit hash value and the result is like this:

[0, 1, 1, 0, 1, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 0, 0, 1, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0]
[0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 0, 0, 1, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0]
[0, 1, 1, 0, 1, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 1, 1, 0, 0, 0, 1, 1, 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0, 1, 0, 0]
...

Now I would like to write this result in an excel sheet (each digit in a separate cell) and the last cell for each row be the name of each image (for example "ukbench00000.jpg" for the first image, "ukbench00001.jpg" for the second image and so on),

this is the code that I wrote with the help of @Shagy G:

from openpyxl import Workbook

book = Workbook()
sheet = book.active

folder_dir = "C:.../ExLets"

row = 1
for image in os.listdir(folder_dir):
    
    binary_hash = compute_binary_hash(image)
    
    _len = len(binary_hash)
    
    for i in range(1, _len):
        cell = sheet.cell(row=row, column=i)
        cell.value = binary_hash[i]
    
    cell = sheet.cell(row=row, column=_len 1)
    cell.value = image
    row  = 1
    column  = 1

book.save('C:.../ExLets2.xlsx')

but I'm still missing the first digit of the binary_hash because the range starts at 1 (and not at 0 ) to respect the rule that row or column values must be at least 1.

Do you have any suggestions about how to write ALL the digits of binary_hash in the excel sheet?

CodePudding user response：

Using pandas, you can open and edit by inserting your array in each cell of the row, if you need to create excel first, run the second code

import pandas as pd

df = pd.read_excel(f'YOUR_EXCEL.xlsx')
cols = df.columns

image_name = "ukbench00000.jpg"
binary_hash.append(image_name)

df.loc[len(df)] = binary_hash

df = df.set_index(f'{cols[0]}')
df.to_excel(f'YOUR_EXCEL.xlsx')

CREATE EXCEL:

import pandas as pd

d = {}
df = pd.DataFrame(data = d,columns = [x for x in range(0, 128)])
df.to_excel(f'YOUR_EXCEL.xlsx')

CodePudding user response：

number_format = workbook.add_format({'num_format': '0'})

row = 1
for image in os.listdir(folder_dir):
    binary_hash = compute_binary_hash(image)
    _len = len(binary_hash)
    for i in range(_len):
        worksheet.write(row, i, binary_hash[i], number_format)
    worksheet.write(row, _len   1, image)
    row  = 1

from openpyxl import Workbook
book = Workbook()
sheet = book.active

row = 1
for image in os.listdir(folder_dir):
    binary_hash = compute_binary_hash(image)
    _len = len(binary_hash)
    for i in range(_len):
        cell = sheet.cell(row=row, column=5)
        cell.value = binary_hash[i]
    cell = sheet.cell(row=row, column=_len   1)
    cell.value = image
    row  = 1

book.save('images.xlsx')