xlsxwriter having problems when inserting 8-bit values with ‘ symbol

  excel, pandas, python, python-3.x, xlsxwriter

My aim is to compare a table from a htm file with a table from a xlsx file and i done it all by converting to a dataframe using python. Its all working correctly and could display the correct value from xlsx file but when I try to copy the file from xlsx file to a new xlsx file which i convert the information to a table with the name and values as column, it gives me an error. It could show the correct value when i use print(data[y].values[z,1]) but when i want to put it into the excel file im getting an error with worksheet.write_string(row,col,data[y].values[z,1]). I had tried first convert the value to a string by value=str(data[y].values[z,1]) and the print(value) then i only put the value variable into the xlsx file by worksheet.write_string(row,col,value) but everything i get from the output file is nan for the value. The name could be shows in characters but the value could not shown out. Is it because my value is a 8-bit value and the value 8’h0 and contains the symbol ' so it could not be done by the library? If it is a yes, then how can I solve this problem?
This is the output file:
enter image description here
This is what i get with print(data[y].values[z,1]):
enter image description here
This is my source code:

import pandas as pd
import numpy as np
htm = pd.read_html('HAS.htm')[5]
xlsx = pd.ExcelFile('MTL_SOCSouth_PCH_and_IOE_Security_Parameters.xlsm')

import xlsxwriter
workbook = xlsxwriter.Workbook('Output01.xlsx')
worksheet = workbook.add_worksheet()
sheets=xlsx.sheet_names

#remove unwanted sheet
sheets.pop(0);
sheets.pop(0);
sheets.pop(0);
sheets.pop(-1);
sheets.pop(-1);
sheets.pop(-1);
sheets.pop(-1);

#create an array to store data for each sheet
data=[0]*(len(sheets))

#insert each sheet into array
for x in range(len(sheets)):
    data[x]=xlsx.parse(sheets[x],header=4,usecols='B,AM')
    data[x]=pd.DataFrame(data[x])

#initialize to first row
row = 0

#loop from first row from htm file to last row
for x in range(len(htm.index)):
    chapter=(htm.values[x,3])
    chapter=chapter[:chapter.find(": ")]
    chapter=chapter.split("Chapter ",maxsplit=1)[-1]

    #if the chapter is equal to 37 then proceed, ignore if not 37
    if(chapter=='37'):
        col = 0
        source=htm.values[x,0]
        source=source[:source.find("[")]
        print(source)
        for y in range((len(sheets))):
            for z in range(len(data[y].index)):
                target=data[y].values[z,0]
                targetname=str(target)
                worksheet.write(row,col,targetname)
                if source==target:
                    col += 1
                    print(sheets[y])
                    worksheet.write(row,col,sheets[y])
                    col += 1
                    print(data[y].values[z,1])
                    worksheet.write_string(row,col,data[y].values[z,1])
        row += 1

workbook.close()

Source: Python-3x Questions

LEAVE A COMMENT