Good day. I am writing in despair.

For several days I have been trying to master at least something (openpyxl, xlsxwriter, xlrd-xlwt ...) that will help me accomplish the following, seemingly simple algorithm:

  1. Open Excel file.
  2. Find a cell in the defined column that matches the pre-defined word (in Russian).
  3. Copy the entire row along with this cell and all the data in this row (row).
  4. Create a new Excel file and write it all there (all the rows in which that predetermined word was found).

I tried exactly each of the modules. In some terrible troubles with receiving data from the string, others do not perceive the Russian language in the cells, others refuse to work at all ...

Here is the last failed example using win32com:

import win32com.client Excel = win32com.client.Dispatch("Excel.Application") text = 'Блендер' counter = 2 def write(val, pos): wb = Excel.Workbooks.Add() ws = wb.ActiveSheet i = 1 for rec in val: ws.Cells(pos,i).value = rec i = i + 1 wb.SaveAs('test.xlsx') wb.Close() Excel.Quit() def search(): wb = Excel.Workbooks.Open(u'C:/Users/User/Desktop/excel.xlsx') sheet = wb.ActiveSheet srch = [r[0].value for r in sheet.Range("B2:B13")] for items in srch: if text in items: global counter print ('Found') found = sheet.Range("A%s:D%s" % (counter,counter)).Value print (found) write(found,counter) counter += 1 search()

Surely this code is as imperfect as possible. But let it be there at least over99 crutches, if only it worked, but it doesn’t work even with this - at the sight of a Russian text it falls into hysterics and shouts “OLE error NONE NONE”. And even without Russian characters, one FIG records only the first cell.

I will be infinitely glad to any help.

1 answer 1

UPDATE:

 In [155]: text = 'Смеситель' In [156]: df[df.ix[:,1].str.lower().str.contains(text.lower())] Out[156]: № товара Наименование товара Цена товара Страна производитель 5 6 Смеситель VITEK 5000 РОССИЯ 9 10 Смеситель Rivex 11000 СЛОВЕНИЯ 11 12 Смеситель Tragefar 7000 РОССИЯ

Here is the solution using the Pandas module:

 import pandas as pd text = 'blender' df = pd.read_excel(r'D:\temp\.data\excel.xlsx') df[df.ix[:,1].str.lower().str.contains(text.lower())].to_excel('d:/temp/result.xlsx', index=False)

PS for Python 2.x you should use unicode_literals or decode a line:

 from __future__ import unicode_literals

or

 df[df.ix[:,1].str.lower().str.contains(text.decode('utf-8').lower())]

Demo (Python 3.5):

 In [133]: import pandas as pd In [134]: text = 'blender' In [135]: fn = r'D:\temp\.data\excel.xlsx' In [136]: df = pd.read_excel(fn) In [137]: df Out[137]: № товара Наименование товара Цена товара Страна производитель 0 1 Кондиционер VITEK 22000 КИТАЙ 1 2 Мультиварка Tefal 8000 СЛОВЕНИЯ 2 3 Blender BEKO 4000 ЯПОНИЯ 3 4 Микроволновая печь Redmond 3800 КИТАЙ 4 5 Сплит-система BEKO 25000 КИТАЙ 5 6 Смеситель VITEK 5000 РОССИЯ 6 7 Blender Messimo 9000 США 7 8 Мультиварка Redmond 3000 РОССИЯ 8 9 Микроволновая печь Smithson 19000 КИТАЙ 9 10 Смеситель Rivex 11000 СЛОВЕНИЯ 10 11 Blender SmithPRO 14000 ЯПОНИЯ 11 12 Смеситель Tragefar 7000 РОССИЯ

we filter by the second (in Python / Pandas, the numbering starts from 0 , so the second column has an index: 1 ) column

 In [138]: df[df.ix[:,1].str.lower().str.contains(text.lower())] Out[138]: № товара Наименование товара Цена товара Страна производитель 2 3 Blender BEKO 4000 ЯПОНИЯ 6 7 Blender Messimo 9000 США 10 11 Blender SmithPRO 14000 ЯПОНИЯ In [139]: df[df.ix[:,1].str.lower().str.contains(text)].to_excel('d:/temp/result.xlsx', index=False)

result.xlsx:

enter image description here

PS The fastest and easiest way to install Pandas is to install Anaconda , which already includes all the necessary modules for data analysis and processing: NumPy, SciPy, sklearn, Pandas, and more.

  • Thank you very much. But there is a flaw, I forgot to bring it back - I renamed blender in the source file to blender for the sake of the test, in the original it is Blender in Russian. I tried your script - do not look for Russian names :( Is there any way out of this situation? - S. Stuart
  • Thank!!! If it's easy for you, tell me how else you can add a search for several words and output them in groups by name, separating them with a blank line, since looking at such a code doesn’t even have any ideas :( - S. Stuart
  • I didn’t understand this: вывод их группами по названиям, разделяя пустым рядом - can you give an example in the form of text in the question ? - MaxU
  • for some reason, the main post does not correct ("an error occurred", hmm). Here is a link to a picture with an example f4.s.qip.ru/18qb6SzE.png - S. Stuart
  • those. you do not need to filter strings? You just need to group by name and divide by empty line? - MaxU