Skip to content. | Skip to navigation

Personal tools

Navigation

You are here: Home / Tips / Working with Excel / CSV Files

Working with Excel / CSV Files

Importing and Exporting Excel / CSV Data with Plone.
import csv
file_read = open('myfile.csv', 'rb')
reader = csv.reader(file_read, delimiter=',')
for row in reader:
    print row[0], row[1]

計算 CSV 的行數,小檔案可用 row_count = len(list(csv.reader(open('myfile.csv')))) 但大檔案要用 row_count = sum(1 for row in csv.reader(open('myfile.csv'))),另外也要注意欄位值可能包含 \n 造成的問題。

預設不會判斷資料型別,統一使用 String 格式,可寫程式猜測型別

Exporting

collective.excelexport exporting folder listing

例如想要匯出 myfolder 目錄的項目清單,在 ZMI 的 myfolder 目錄裡,新增 Python Script,以名稱 listing 為例,內容如下:

request = container.REQUEST
response = request.response

for brain in context.getFolderContents():
    print brain.Title + ', ' + brain.Creator

response.setHeader("Content-type","application/vnd.ms-excel")
response.setHeader("Content-disposition","attachment;filename=FolderData.xls")

return printed

在網址裡使用 myfolder/listing 就會出現另存檔案的視窗。

Mongo Query to CSV Download (ExpressJS)

Example Content Type

AnswerFolder.py

PloneSurvey

with MySQL

phpMyAdmin

pyExcelerator by Tareq Alam blog

Excel to MySQL

data = map(lambda x: {'date': x[0].value,
                      'campaign': x[1].value,
                      'users': x[2].value,
                      'sessions': x[3].value},
           ws[ROW, ws.max_row])

Exporting ZSQL Query

old plone documentation

Importing

Products.xlsimport

xlrd 可讀 xls 或 xlsx

openpyxl vs xlrd 讀取大檔 (例如10Mb 費時約 1分鐘) 需要記憶體,不然會當機。

from openpyxl import load_workbook
wb = load_workbook('my_excel.xlsx', read_only=True)
print wb.get_sheet_names()

for i in wb.get_sheet_names():
    print i

ws = wb.get_sheet_by_name('Sheet1')
# for old versions
# print ws.get_highest_row(), ws.get_highest_column()
print ws.max_row, ws.max_col cell = ws['A1'] cell.value cell.row cell.column for i in range(1,9): print i, sheet1.cell(row=i,column=1).value

excel template Pandas 新手第一篇

import pandas as pd
data = pd.read_excel('my-excel.xlsx', sheetname=0)

Filter Rows Containing a String Pattern

del df['column_name'] vs del.df.column_name

Pandas to SQL cx_Oracle

Pandas 讀取舊版 Excel 時,底層使用 xlrd 但仍可能遇到 No CODEPAGE record, no encoding_override: will use 'ascii' 錯誤。

UnicodeDecodeError 可能跟 NaN 欄位有關