from pyexcel_xls import save_data data = {"Data1": [[123, 45, 56], [23, 34, 35]]} save_data('D:\Shubham\Python\programs\excelDemo1.xls', data) from pyexcel_xls import read_data data1 = read_data('D:\Shubham\Python\programs\excelDemo1.xls') print(data1)
#To save data in an excel from pyexcel_xls import save_data data = {"sheet1": [['sno', 'name', 'address'], [1, 'abc', 'india']]} save_data("demo.xls", data) #To read the data from an excel from pyexcel_xls import read_data data = read_data("demo.xls") print(data) #Working with xlsxwriter import xlsxwriter workbook = xlsxwriter.Workbook('demo.xls') worksheet = workbook.add_worksheet() worksheet.write("A1", 'test data') worksheet.close()
# 2) Read excel,Open excel, get sheets from workbook, getting sheets from the sheets # getting rows and columns from the sheets from pyexcel_xls import save_data, read_data data = { "sheet 1": [[1, 2, 3], [4, 5, 6]], "sheet 2": [[2, 3, 'Arun'], ['python', 'data']] } save_data('E:\\trashcode\\aasd.xlsx', data) rdata = read_data('E:\\trashcode\\aasd.xlsx') print(rdata) import xlsxwriter workbook = xlsxwriter.Workbook('test.xlsx') worksheet = workbook.add_worksheet() worksheet.write('A2', 'Test data') workbook.close()
# 2) Read excel,Open excel, get sheets from workbook, getting sheets from the sheets # getting rows and columns from the sheets from pyexcel_xls import save_data, read_data data = { "sheet 1": [[1, 2, 3], [4, 5, 6]], "sheet 2": [[2, 3, 'Arun'], ['python', 'data']] } save_data("myxls.xls", data) rdata = read_data("myxls.xls") print(rdata) import xlsxwriter workbook = xlsxwriter.Workbook('test.xlsx') worksheet = workbook.add_worksheet() worksheet.write('A2', 'Test data') workbook.close()
''' python project to build an SEO tool to input data in an excel sheet and find related tags in the HTML content of a website, thus printing the tags onto the python shell ''' from pyexcel_xls import read_data from bs4 import BeautifulSoup from urllib.request import urlopen import xlsxwriter name = input("Enter the excelsheet name(with .xlsx ext.): ") exBook = xlsxwriter.Workbook( name) #create an excel workbook and sheet for website name exSheet = exBook.add_worksheet() #add the data according to the project data = read_data(name) try: for sheetname, values in data.items(): urls = values[0] tags = values[1:6] url = urls[0] print(url) print(tags) print('-' * 20) hyperLink = urlopen(url) html = hyperLink.read().decode('utf-8') Soup = BeautifulSoup(html, 'html.parser') #bs4 to display the html content meta = Soup.find_all('meta') desc = Soup.find(attrs={'name': 'description'})
from pyexcel_xls import save_data from pyexcel_xls import read_data from bs4 import BeautifulSoup import urllib.request import xlsxwriter import json d = read_data("Book1.xlsx") s = json.dumps(d) obj = json.loads(s) #for k in obj: # print(obj[k][0][0]) #urlNames=dict() wb = xlsxwriter.Workbook('Book2.xlsx') for k in obj: worksheet = wb.add_worksheet(str(k)) worksheet.write("A1", obj[k][0][0]) worksheet.write("A3", "WORDS") worksheet.write("B3", "COUNT") chart = wb.add_chart({'type': 'column'}) req = urllib.request.Request( str(obj[k][0][0]), data=None, headers={ 'User-Agent': 'Mozilla/5.0 (Windows NT 6.2; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36' }) f = urllib.request.urlopen(req) s = f.read().decode('utf-8') soup = BeautifulSoup('https://kerala.gov.in/web/guest/gallery',
sheet1.write('A1', 'javatpoint.com') sheet1.write('A2', 'https://www.javatpoint.com/java-tutorial') sheet1.write('A3', 'java') sheet1.write('A4', 'sql') sheet1.write('A5', 'tutorial') sheet2 = workbook.add_worksheet("second") sheet2.write('A1', 'tutorialspoint.com') sheet2.write( 'A2', 'https://www.tutorialspoint.com/python/python_data_structure.htm') sheet2.write('A3', 'python') sheet2.write('A4', 'data') sheet2.write('A5', 'programming') ##################################### d = read_data("Urlbook.xlsx") L1 = [] for w in d['first']: L1.append("".join(map(str, w))) L2 = [] for w in d['second']: L2.append("".join(map(str, w))) print(L1, "\n") print(L2, "\n") print("Reading url from excel file:") print(L1[1]) print(L2[1], "\n") #####################################
def ionsuppression(chrom, trans, window=10, save=False, data=data): # import data try: df_trans = data[trans] except: df_trans = pd.read_excel(trans) data[trans] = df_trans if chrom.split(".")[-1] == 'xls': from pyexcel_xls import read_data else: from pyexcel_xlsx import read_data try: df_chrom = data[chrom] except: df_chrom = read_data(chrom) data[chrom] = df_chrom # pci transition, retention time df_trans['Transition'] = [ re.sub("^ *", "", i) for i in df_trans['Transition'] ] # pci_name = re.sub("[(]PCI[)]","",df_trans.iloc[['(PCI)' in i for i in df_trans.iloc[:,0]],0][0]) pci_trans = df_trans.loc[['(PCI)' in i for i in df_trans.iloc[:, 0]], 'Transition'][0] while True: rand = random.sample(list(df_chrom.keys()), 1) if pci_trans in df_chrom[rand[0]][0][0]: df = np.vstack(df_chrom[rand[0]][2:]) break df = df[df[:, 1] < 2, :] iv = pd.Series([df[i + 1, 1] - df[i, 1] for i in range(df.shape[0] - 1)]) iv = round(iv.mode()[0], 4) argmin = df[:, 2].argmin() rt = np.arange(df[argmin, 1] - window * iv, df[argmin, 1] + (window + 1) * iv, iv) # filter chromatography result = np.array( [interpolate2(df, rt, pci_trans) for df in df_chrom.values()]) result = result[result.nonzero()] mat_chrom = np.vstack(list(zip(*result))[0]) name = list(zip(*result))[1] # data assembly datafile = [re.sub('.*[(]', "", re.sub('.*[) ]', "", i)) for i in name] df_is = pd.DataFrame(mat_chrom) df_is.columns = rt df_is.index = pd.Series(datafile).unique() # Save if save == True: try: df_is.to_excel('{}.xlsx'.format(asksaveasfilename())) except: "Cancelled" return df_is
def quantify(chrom, trans, save=False): # import data try: df_trans = data[trans] except: df_trans = pd.read_excel(trans) data[trans] = df_trans if chrom.split(".")[-1] == 'xls': from pyexcel_xls import read_data else: from pyexcel_xlsx import read_data try: df_chrom = data[chrom] except: df_chrom = read_data(chrom) data[chrom] = df_chrom # pci transition, retention time df_trans['Transition'] = [ re.sub("^ *", "", i) for i in df_trans['Transition'] ] # pci_name = re.sub("[(]PCI[)]","",df_trans.iloc[['(PCI)' in i for i in df_trans.iloc[:,0]],0][0]) pci_trans = df_trans.loc[['(PCI)' in i for i in df_trans.iloc[:, 0]], 'Transition'][0] df_trans = df_trans.set_index(['Transition']) rt_min = min(df_trans['RT.s'].dropna()) rt_max = max(df_trans['RT.e'].dropna()) rand = random.sample(list(df_chrom.keys()), 1) df = np.vstack(df_chrom[rand[0]][2:]) df = df[(np.searchsorted(df[:, 1], rt_min, side='right') - 1):(np.searchsorted(df[:, 1], rt_max) + 1), :] iv = pd.Series([df[i + 1, 1] - df[i, 1] for i in range(df.shape[0] - 1)]) iv = round(iv.mode()[0], 4) rt = np.arange(df[0, 1], df[-1, 1] + iv, iv) # filter chromatography result = [interpolate1(df, rt) for df in df_chrom.values()] mat_chrom = np.vstack(list(zip(*result))[0]).transpose() name = list(zip(*result))[1] # Calculate ratio datafile = [re.sub('.*[(]', "", i) for i in name] trans = pd.Series([re.sub('[)].*', "", i) for i in datafile]) datafile = [re.sub('.*[) ]', "", i) for i in datafile] pci_index = trans == pci_trans mat_pci = mat_chrom[:, pci_index] for i in range(len(df_trans.index) - 1): mat_pci = np.hstack([mat_pci, mat_chrom[:, pci_index]]) mat_chrom = mat_chrom / mat_pci # Peak computing dict_range = dict() for i in df_trans.index: if i == pci_trans: dict_range[i] = list(range(len(rt))) else: dict_range[i] = [ j for j, k in enumerate(rt) if k > df_trans.loc[i, 'RT.s'] and k < df_trans.loc[i, 'RT.e'] ] mat_chrom = np.array( [sumif(mat_chrom, i, trans, dict_range) for i in range(len(datafile))]) # data assembly datafile = pd.Series(datafile).unique() trans = trans.unique() peak = mat_chrom.reshape(len(trans), len(datafile)).transpose() peak = pd.DataFrame(peak) peak.index = datafile peak.columns = trans # Save if save == True: try: peak.to_excel('{}.xlsx'.format(asksaveasfilename())) except: "Cancelled" return peak
#key modules py -m pip install pyexcel-xls #key modules py -m pip install beautifulsoup4 #key modules py -m pip install xlsxwriter from pyexcel_xls import save_data from pyexcel_xls import read_data from urllib import * from bs4 import BeautifulSoup import urllib.request import xlsxwriter import json import socket data = read_data("input.xls") ss = json.dumps(data) object = json.loads(ss) urlNames = dict() wb = xlsxwriter.Workbook('output_scrap.xlsx') h1 = wb.add_format({'bold': True, 'font_color': 'red'}) h2 = wb.add_format({'bold': True, 'font_color': 'blue'}) h3 = wb.add_format({'bold': True, 'font_color': 'green'}) for o in object: ws = wb.add_worksheet(str(o)) ws.write("A1", object[o][0][0], h1) ws.write("A3", "WORDS", h2) ws.write("B3", "COUNT", h2) chart = wb.add_chart({'type': 'column'}) req = urllib.request.Request( str(object[o][0][0]), data=None,
# -*- coding: utf-8 -*- """ Spyder Editor This is a temporary script file. """ # pip install pyexcel_xls from pyexcel_xls import save_data, read_data data = { "sheet 1": [[1, 2, 3], [4, 5, 6], [4, 5, 6]], "sheet 2": [[2, 3, 'pavan'], ['python', 'data']] } save_data("myxls123.xls", data) read_data = read_data("myxls123.xls") print(read_data)
from pyexcel_xls import save_data data = {"EmpId": [[101, 102, 103], [65, 47, 789]]} save_data("d:\\emp.xls", data) from pyexcel_xls import read_data data1 = read_data('d:\\emp.xls') print(data1)
from pyexcel_xls import save_data data = {"sheet1": [[10, 40, 50], [75, 85, 74]]} save_data("demo_excel.xls", data) from pyexcel_xls import read_data data = read_data("demo_excel.xls") print(data)
# Add a series of data to the Chart. chart1.add_series({ 'name': '=Sheet1!$C$1', 'categories': '=Sheet1!$B$2:$B$6', 'values': '=Sheet1!$C$2:$C$6', }) # Add a chart title and some axis labels. chart1.set_title({'name': 'Analysis of Density of Keywords'}) chart1.set_x_axis({'name': 'Keywords ---------->'}) chart1.set_y_axis({'name': 'Densities ---------->'}) # Set an Excel chart style. Colors with white outline and shadow. chart1.set_style(27) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D5', chart1, {'x_offset': 25, 'y_offset': 10}) workbook.close() print('''\nSccessfull analysis of URL \nYou can open file - 'Density_analysis' in your (D:) drive ''') from pyexcel_xls import read_data data = read_data("D:\\Density_analysis.xlsx") print(data) else: print('\n________*INVALID URL*________')
def readExcel(self, filepath): self.filePath = filepath from pyexcel_xls import read_data data = read_data(filePath) return data
worksheet.write('A1', 'url') worksheet.write('B1', 'Count') row = 1 col = 0 for url, count in d.items(): worksheet.write(row, col, url) worksheet.write(row, col + 1, count) row += 1 # Write a total using a formula. worksheet.write(row, 0, 'Total') worksheet.write(row, 1, '=SUM(B2:B5)') workbook.close() data = read_data("finalreport.xlsx") print(data) chartsheet = workbook.add_chartsheet() chart = workbook.add_chart({'type': 'bar'}) # Configure the chart. chartsheet.set_chart(chart) chart.add_series({'values': '=Sheet1!$A$2:$A$7'}) chart.add_series({'values': '=Sheet1!$B$2:$B$7'}) # Insert the chart into the worksheet. worksheet.insert_chart('A13', chart) workbook.close()
d = {} workbook = xlsxwriter.Workbook("urlbook.xlsx") worksheet1 = workbook.add_worksheet("first") worksheet1.write('A1', 'javatpoint.com') worksheet1.write('A2', 'https://www.javatpoint.com/java-tutorial') worksheet1.write('A3', 'java') worksheet1.write('A4', 'SQL') worksheet1.write('A5', 'C++') worksheet2 = workbook.add_worksheet("second") worksheet2.write('A1', 'tutorialspoint.com') worksheet2.write( 'A2', 'https://www.tutorialspoint.com/python/python_data_structure.htm') worksheet2.write('A3', 'Python') worksheet2.write('A4', 'Data') worksheet2.write('A5', 'programming') d = read_data("urlbook.xlsx") print("Reading URL from excel file") #print(d['first']) #print(d['second']) L1 = [] for w in d['first']: L1.append("".join(map(str, w))) #print(L1) print(L1[1]) #worksheet 1 L2 = [] for w in d['second']: L2.append("".join(map(str, w))) #print(L2) print(L2[1]) #worksheet 2
#Reading XLS file using pyexcel from pyexcel_xls import read_data data = read_data("book.xls") print(data)