Pycharm Excel image import apkg

[XnOrange]

So I have incredibly basic programming skills. I learned some Visual Basic in HS and CSS and HTML in college. My boss and the company programmer are idiots. I came up with a great idea to utilize Anki with our school materials, but the programmer made my idea way too complex for me, and now I’m trying to use tutorials and ChatGPT to help me write a program to extrapolate text and images from an Excel file. I was able to develop an APKG file for import, but there are no images. Only text. What did I do wrong? I’ll post my code soon.

[Keks]

Are you creating an .apkg file to import into anki?

[Danika_Dakika]

Is there a reason not to just import it as text/CSV, based on the Excel file? Text Files - Anki Manual

[XnOrange]

import pandas as pd
from PIL import Image
import base64
from io import BytesIO
import numpy as np  # Import numpy for NaN handling

# Function to extract image from Excel and convert to base64
def extract_image_from_excel(cell):
    if isinstance(cell, str) and cell.startswith("b'"):
        # Handling for base64 encoded image stored as a string in Excel
        encoded_data = cell.split(",")[1].strip("'").encode()
        img = Image.open(BytesIO(base64.b64decode(encoded_data)))
    elif isinstance(cell, float) and np.isnan(cell):
        return None  # Return None if cell is NaN (empty)
    else:
        try:
            img = Image.open(BytesIO(cell))
        except (AttributeError, TypeError, OSError, ValueError):
            return None  # Return None if cell.value does not exist or is not valid

    buffered = BytesIO()
    img.save(buffered, format="PNG")
    return base64.b64encode(buffered.getvalue()).decode('utf-8')

# Input and output file paths
excel_file = r'C:\Users\teachers\PycharmProjects\pythonProject\input.xlsx'  # Replace with your actual path
output_csv = r'C:\Users\teachers\PycharmProjects\pythonProject\output.csv'  # Replace with your actual path

# Read Excel file with specific tab and cell ranges
try:
    df = pd.read_excel(excel_file, sheet_name='1-1-1 Mr. Face (Basic)',
                       usecols='B:D', skiprows=9, nrows=10, engine='openpyxl')
except FileNotFoundError:
    print(f'Error: File not found at {excel_file}. Please check the file path.')
    exit(1)

# Prepare data for CSV output
anki_data = []
for index, row in df.iterrows():
    text = row[1]  # Column B (index 1) has text
    image_base64 = extract_image_from_excel(row[2])  # Column D (index 2) has images

    if image_base64:
        anki_data.append((text, '<img src="data:image/png;base64,{}">'.format(image_base64)))
    else:
        anki_data.append((text, ''))

# Convert to DataFrame
anki_df = pd.DataFrame(anki_data, columns=['Question', 'Image'])

# Save as CSV file
anki_df.to_csv(output_csv, index=False, encoding='utf-8')

print(f'CSV file "{output_csv}" generated successfully.')

[XnOrange]

I tried csv. But it wasn’t working. ChatGPT suggested apkg. So I was trying that. Today I went back to CSV but I’m getting the same frustrating results- no image data when I import. I also click “allow HTML” but nothing

[XnOrange]

There are images in the excel file that I want to import at the same time, but the images are showing up after import. Only text.

[XnOrange]

This is the format I’m working with

[Danika_Dakika]

There are a couple extra steps if you want images to come with the import, but nothing too complicated. Text Files - Anki Manual