Python: pywin32 安裝與使用教學，Python 的 Windows 擴充功能套件

pywin32 安裝與使用教學，Python 的 Windows 擴充功能套件

https://officeguide.cc/python-windows-extensions-pywin32-installation/

Python: 以openpyxl寫入大量資料至xlsx, 出現MemoryError

問題: 以openpyxl寫入大量資料至xlsx, 出現MemoryError, 但若筆數少就正常

原因: 原先只指定 wb=workbook() , 找到資料是要加註 write_only=True , 改為

  wb-workbook(write_only=True)

但仍有問題, 出現:

  I/O operation on closed file error

解法: 要再安裝lxml

Here again, the regular openpyxl.worksheet.worksheet.Worksheet has been replaced by a faster alternative, the openpyxl.writer.write_only.WriteOnlyWorksheet. When you want to dump large amounts of data make sure you have lxml installed.

果然, 裝好就能正常運作了.


Ref:
1.Write-only mode
https://openpyxl.readthedocs.io/en/default/optimized.html#write-only-mode

2.openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files
https://openpyxl.readthedocs.io/en/default/index.html


Error log:
Traceback (most recent call last):
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 832, in _get_writer
    yield file.write
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 772, in write
    serialize(write, self._root, qnames, namespaces,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 937, in _serialize_xml
    _serialize_xml(write, e, qnames, None,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 937, in _serialize_xml
    _serialize_xml(write, e, qnames, None,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 937, in _serialize_xml
    _serialize_xml(write, e, qnames, None,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 939, in _serialize_xml
    write("</" + tag + ">")
MemoryError
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "C:\temp\dl_rawdata_ebs.py", line 81, in <module>
    wb.save('c:\\shortage\\All.xlsx')
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save
    save_workbook(self, filename)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook
    writer.save()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 275, in save
    self.write_data()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 75, in write_data
    self._write_worksheets()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 215, in _write_worksheets
    self.write_worksheet(ws)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\writer\excel.py", line 200, in write_worksheet
    writer.write()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_writer.py", line 360, in write
    self.close()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_writer.py", line 368, in close
    self.xf.close()
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\worksheet\_writer.py", line 299, in get_stream
    pass
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 120, in __exit__
    next(self.gen)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\et_xmlfile\xmlfile.py", line 50, in element
    self._write_element(el)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\site-packages\et_xmlfile\xmlfile.py", line 77, in _write_element
    xml = tostring(element)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 1133, in tostring
    ElementTree(element).write(stream, encoding,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 772, in write
    serialize(write, self._root, qnames, namespaces,
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 131, in __exit__
    self.gen.throw(type, value, traceback)
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\xml\etree\ElementTree.py", line 832, in _get_writer
    yield file.write
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 525, in __exit__
    raise exc_details[1]
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 510, in __exit__
    if cb(*exc_details):
  File "C:\Users\UT05\AppData\Local\Programs\Python\Python38-32\lib\contextlib.py", line 382, in _exit_wrapper
    callback(*args, **kwds)
ValueError: I/O operation on closed file.

Python: 以cx_Oracle取Oracle DB資料時, 出現illegal multibyte sequence error

問題: Python以cx_Oracle取Oracle DB資料時, 出現illegal multibyte sequence error

原因: 預設使用Big5, 但DB中有簡體字, 無法轉換

解法: 加註encoding = 'UTF-8'


conn = cx_Oracle.connect(user='u',password ='pw', dsn=dsn_tns, encoding = 'UTF-8')


Ref:
1.How to handle unicode data in cx_Oracle and python 2.7?
https://stackoverflow.com/questions/49174710/how-to-handle-unicode-data-in-cx-oracle-and-python-2-7

2.Random character is displayed when unicode data is fetched
https://github.com/oracle/python-cx_Oracle/issues/157

Python: 把CSV轉為XLSB檔

要先安裝pywin32, 實測也可以把xlsx轉為xlsb.

 

sample code: 

import win32com.client

excel = win32com.client.Dispatch("Excel.Application")

doc = excel.Workbooks.Open('D:\\input.csv')

doc.SaveAs( 'D:\\output_bin.xlsb', 50 )

 

Ref:

1.How do I convert a csv file to xlsb using Python?
https://stackoverflow.com/questions/24159542/how-do-i-convert-a-csv-file-to-xlsb-using-python

 

2.pywin32 安裝與使用教學，Python 的 Windows 擴充功能套件
https://officeguide.cc/python-windows-extensions-pywin32-installation/