I was confused because there were multiple ways to play with Excel in Python, so I summarized them in a table.
Since the table is large, I posted Excel to another server without writing it in Qiita. Only the operations that are likely to be used often are described. http://mirutsurumi.html.xdomain.jp/Excel_pylib.html (Scheduled to be updated as appropriate.)
The following libraries are summarized.
| use | |
|---|---|
| openpyxl | Treat Excel as Excel and use it for operations that care about cell formats. Click here for many explanations in books. |
| pandas | Use this when you are not interested in Excel formats and want to specialize only in data analysis. openpyxl as engine variable,xlsxwriter can also be used. |
| xlwings (editing) | What you can do is similar to openpyxl, but the operation feeling is different. When you execute it, Excel itself starts, and if you use an interactive shell, you can operate while watching the contents change. It can also officially read pandas df. Since you can also operate VBA, you can specify the cell range with range.("A1:C3")It is also easy to understand that you can specify like. |
| xlrd, xlwt | Close to openpyxl, an image like its traditional version. Since it is also included in pandas, it seems unlikely that it will be used alone. You need to install these before you can operate Excel with pandas. |
| xlsxwriter | Close to openpyxl, an image like its traditional version. Since it is also included in pandas, it seems unlikely that it will be used alone. You need to install xlsxwriter in advance when operating Excel with pandas. |
The table is for simple reference, so detailed explanation is omitted.
Xlwings and openpyxl are also compared in the article below. https://qiita.com/m5knt/items/ab56f1d0a783f3422ee3
■ Basic procedure 【openpyxl, xlrd/xlwt, xlsxwriter】 ① Create wb object with library name.Workbook () ② ws = wb. Create ws object with sheet addition function (xlsxwriter is up to here) ③ In the same way, create from ws to cell object, and then operate with your favorite function. ④ Save (new Excel appears at this stage)
【pandas】 (1) Set data (read existing Excel with pd.read_excel) * You can also write directly with pd.DataFrame. (2) Cell value manipulation and filtering with df.loc etc. (Pivot_table can also be used although not shown in the table) ③ Create new Excel with df.to_excel
Since the relationship between read_excel, to_excel, and ExcelWriter is complicated, I will supplement it.
■read_excel Read the data df of the existing Excel specified sheet (or the leftmost sheet if not specified). It is just reading df (DataFrame type) and does not define Excel workbook type.
■to_excel Write df to the specified Excel file.
■ExcelWriter Define the Excel file to be written with to_excel. (Similar to the image that defines wb in openpyxl.) You can write with to_excel (file name) without defining ExcelWriter, but by combining with with statement, you can write data to multiple sheets at once. In addition, the description of to_excel becomes redundant unless the Excel file name is made variable (in some cases, for each path) by combining this with the with statement. After all, it exists to simplify the description, not a mast to use.
Recommended Posts