整合營銷服務商
電腦端+手機端+微信端=數(shù)據(jù)同步管理
免費咨詢熱線：
電腦端+手機端+微信端=數(shù)據(jù)同步管理
免費咨詢熱線：
前我們介紹過幾個模塊：xlrd、xlwt、XLsxWriter等，但是這些模塊要門只能只讀，要么只能只寫，那么有沒有即可讀，也可寫的模塊，答案是肯定的，如pandas、openpyxl、文2com等，在此介紹其中一個：openpyxl。
openpyxl 是一個用于讀寫Excel的模塊，支持xlsx、xlsm、xltx、xltm等文件的讀寫，不支持xls格式，如果讀取大的Excel文件，可以使用read_only（只讀模式），如果寫入大的Excel文件，可以使用write_only（只寫模式）。
一、openpysx模塊創(chuàng)建Excel文件4步法
1.1、Workbook()：創(chuàng)建Excel文件
此函數(shù)是用來創(chuàng)建Excel文件的一個對象，返回的是一個對象，可以用此對象，創(chuàng)建工作表，保存文件等，如：
import openpyxl # 導入模塊
wb=Workbook() # 生成 Excel 文件對象
print(wb)執(zhí)行結(jié)果：
1.2、create_sheet("sheet_name")：創(chuàng)建工作表
openpyxl 在創(chuàng)建 Excel 文件對象時，會默認創(chuàng)建一個名叫“sheet”的工作表，如果想生成工作表，則需要使用 create_sheet() 函數(shù)，可用 wb.sheetnames 屬性查看所有工作表的名稱，如：
import openpyxl # 導入模塊
wb=Workbook() # 生成 Excel 文件對象
print(wb.sheetnames) # 默認生成的工作表 sheet
wb.create_sheet("new_sheet")
print(wb.sheetnames) # 增加了工作表 new_sheet執(zhí)行結(jié)果：
備注：openpyxl 在只寫模式下，不會生成默認工作表，此時只能使用 create_sheet() 函數(shù)創(chuàng)建。
1.3、向工作表中添加內(nèi)容
openpyxl 添加內(nèi)容比較接近 Python 語法，可以使用賦值語句 "="，也可以使用 append()語句（按行加入），如：
import openpyxl # 導入模塊
wb=Workbook() # 生成 Excel 文件對象
ws=wb.active # 默認工作表 sheet
# 添加內(nèi)容（賦值語句）
ws["A1"]=3
ws["A2"]="hello world"
print(ws["A1"].value)
print(ws["A2"].value)
# 添加內(nèi)容（append語句）,按行添加
ws.append(["hello python","hello java","hello php"])
print(ws["A3"].value)
print(ws["B3"].value)
print(ws["C3"].value)執(zhí)行結(jié)果：
1.4、save("filename")：保存文件
當我們完成所有的操作后，就可以使用 save() 函數(shù)來保存我們創(chuàng)建的 Excel 文件，filename 是文件名稱及文件所保存的位置，如：
save("file/op/添加數(shù)據(jù).xlsx") # 保存文件執(zhí)行結(jié)果：
以上就是 openpyxl 創(chuàng)建 Excel 文件的4個步驟，相比于 XLsxWriter 模塊，openpyxl 在數(shù)據(jù)添加這塊簡單，方便，與 python 語法接近。
二、openpyxl 讀取 Excel 文件
openpyxl 模塊可以使用 load_workbook("filename") 方法來加載本地 Excel 文件，filename 為文件路徑，如：
import openpyxl # 導入模塊
wb=load_workbook("file/op/編程語言排名榜單.xlsx") # 加載本地 Excel 文件
ws=wb["2019年"]
print("修改前 B3 的值：",ws["B3"].value) # 打印單元格 B3 的值
ws["B3"].value="HTML" # 修改單元格 B3 的值
print("修改后 B3 的值：",ws["B3"].value) # 打印單元格 B3 的值執(zhí)行結(jié)果：
備注：openpyxl 不能完全讀取所有的內(nèi)容，因此，如果本地Excel表格含有圖片或者圖表之類的，openpyxl 是不能讀取的，且在打開或者保存相同名稱的Excel表格時，原先表格的圖片或者圖表將會丟失。
三、openpyxl 進階用法
3.1、合并和拆分單元格
3.1.1、merge_cells(*args)：合并單元格
在 openpyxl 模塊中，我們可以用 merge_cells(start_row, start_column, end_row, end_column) 方法來合并單元格，其中：
start_row：開始行
start_column：開始列
end_row：結(jié)束行
end_column：結(jié)束列
import openpyxl # 導入模塊
from openpyxl.styles import Alignment # 導入格式模塊
wb=Workbook() # 創(chuàng)建Excel文件對象
ws=wb.active # 選取當前sheet工作表
ws.merge_cells(start_row=2,start_column=1,end_row=4,end_column=4) # 合并單元格
# 也可以用 ws.merge_cells("A2:D2")
ws["A2"].alignment=Alignment(horizontal="center",vertical="center") # 添加樣式
ws["A2"].value="合并單元格" # 添加內(nèi)容
wb.save("file/op/合并單元格.xlsx") # 保存文件執(zhí)行結(jié)果：
3.1.2、unmerge_cells()：拆分單元格
與 merge_cells() 相反，在 openpyxl 模塊中，我們可以用 unmerge_cells(start_row, start_column, end_row, end_column) 方法來拆分單元格，其中：
start_row：開始行
start_column：開始列
end_row：結(jié)束行
end_column：結(jié)束列
import openpyxl # 導入模塊
wb=load_workbook("file/op/合并單元格.xlsx") # 加載本地 Excel 文件
ws=wb.active # 選取工作表格
ws.unmerge_cells("A2:D4") # 拆分單元格
ws["A2"].value="拆分單元格" # 添加內(nèi)容
wb.save("file/op/合并單元格.xlsx") # 保存文件執(zhí)行結(jié)果：
3.2、行高和列寬
在 openpyxl 中，ws.row_dimensions["row_index"].height 用于設置行高，ws.column_dimensions["col_index"].width 用于設置列寬，如：
import openpyxl # 導入模塊
wb=Workbook() # 創(chuàng)建 Excel 文件對象
ws=wb.active # 選取工作表
ws.row_dimensions[1].height=40 # 設置行高為 40 字符
ws.column_dimensions["D"].width=40 # 設置列寬為 40 字符
ws["A1"]="行高為20個字符" # 添加內(nèi)容
ws["D1"]="列寬為40個字符" # 添加內(nèi)容
wb.save("file/op/設置行高和列寬.xlsx")執(zhí)行結(jié)果：
3.3、add_image(Image("image_path"),"cell")：插入圖片
在 openpyxl 模塊中，可以使用 add_image() 方法來向單元格添加圖片，如：
import openpyxl # 導入模塊
from openpyxl.drawing.image import Image # 導入圖片模塊
wb=Workbook() # 創(chuàng)建 Excel 文件對象
ws=wb.active # 選取工作表
ws.add_image(Image("file/test.jpg"),"C3") # 在 C3 單元格插入圖片
wb.save("file/op/插入圖片.xlsx")執(zhí)行結(jié)果：
3.4、HYPERLINK("url","name")：添加鏈接
在 openpyxl 中，添加超鏈接很簡單，使用 HYPERLINK() 即可向單元格里添加超鏈接，如：
import openpyxl # 導入模塊
from openpyxl.styles import Font
wb=Workbook() # 創(chuàng)建 Excel 文件對象
ws=wb.active # 選取工作表
ws["C3"].font=Font(color='000000FF', i=True, underline='single') # 超鏈接文字樣式
ws["C3"].value='=HYPERLINK("http://www.baidu.com","百度")' # 添加超鏈接
wb.save("file/op/添加鏈接.xlsx") #保存文件執(zhí)行結(jié)果：
3.4、樣式設置
openpyxl 樣式設置主要有 字體、填充、邊框、對齊、數(shù)字格式、保護等，具體用法如下：
下面我們介紹幾個：
import openpyxl # 導入模塊
from openpyxl.styles import Font, Border, Alignment, PatternFill, Side
wb=Workbook() # 創(chuàng)建 Excel 文件對象
ws=wb.active # 選取工作表
# 字體樣式設置
font=Font(name="黑體",
size=20,
bold=True,
italic=True,
color='00CC99FF'
)
# 填充樣式設置
fill=PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000'
)
# 邊框樣式設置
border=Border(left=Side(border_style='dotted',color='FF000000'),
right=Side(border_style='dotted',color='FF000000'),
top=Side(border_style=None,color='FF000000'),
bottom=Side(border_style=None,color='FF000000'),
vertical=Side(border_style=None,color='FF000000'),
horizontal=Side(border_style=None,color='FF000000')
)
# 對齊方式設置
align=Alignment(horizontal='center',
vertical='center'
)
C3=ws["C3"] # 獲取 C3 單元格
C3.value="I Love Python" # 添加內(nèi)容
C3.font=font # 設置字體樣式
C3.fill=fill # 設置填充樣式
C3.border=border # 設置邊框樣式
C3.alignment=align # 設置對齊方式
wb.save("file/op/樣式設置.xlsx") #保存文件執(zhí)行結(jié)果：
3.5、單元格屬性獲取
單元格的屬性比較多，下面列舉幾個比較常用的，如下：
from openpyxl import load_workbook # 導入模塊
wb=load_workbook("file/op/編程語言排名榜單.xlsx") # 打開本地文件
ws=wb.active # 選擇工作表
b5=ws["B5"] # 獲取單元格 B5
print("單元格 B5 的值：",b5.value) # 獲取 B5 的值
print("單元格 B5 的行索引：",b5.row) # 獲取 B5 的行索引
print("單元格 B5 的列索引：",b5.col_idx) # 獲取 B5 的列索引
print("單元格 B5 的列名稱：",b5.column_letter) # 獲取 B5 的列名稱
print("單元格 B5 的坐標：",b5.coordinate) # 獲取 B5 的坐標
print("單元格 B5 的數(shù)據(jù)類型：",b5.data_type) # 獲取 B5 的數(shù)據(jù)類型
print("單元格 B5 的默認編碼：",b5.encoding) # 獲取 B5 的默認編碼
print("單元格 B5 的樣式：",b5.style) # 獲取 B5 的樣式
print("單元格 B5 的樣式id：",b5.style_id) # 獲取 B5 的樣式id執(zhí)行結(jié)果：
3.6、單元格數(shù)據(jù)獲取
單元格的數(shù)據(jù)獲取可以使用 value 屬性獲取，如：
from openpyxl import load_workbook # 導入模塊
wb=load_workbook("file/op/編程語言排名榜單.xlsx") # 打開本地文件
ws=wb.active # 選擇工作表
# 單個數(shù)據(jù)獲取
print("單個數(shù)據(jù)獲取：",ws["B5"],ws["B6"])
# 按行獲取數(shù)據(jù)
print("按行獲取數(shù)據(jù)：")
for i in ws.values:
print(i)
# 范圍獲取數(shù)據(jù)
print("范圍獲取數(shù)據(jù)：")
for i in ws["A3:E6"]:
for j in i:
print(j,j.value)執(zhí)行結(jié)果：
以上為 openpyxl 的一些簡單介紹，還有很多用法，在此就不一一介紹了，感興趣的朋友，可以參考官方文檔。
HP讀取excel、csv文件的庫有很多，但用的比較多的有：PHPOffice/PHPExcel、PHPOffice/PhpSpreadsheet，現(xiàn)在PHPExcel已經(jīng)不再維護了，最新的一次提交還是在2017年12月25號，建議直接使用PhpSpreadsheet，而且這兩個項目都是同一個組織維護的，本文介紹PhpSpreadsheet的使用。
PhpSpreadsheet這個庫是純PHP寫的，提供了非常豐富的類和方法，而且支持很多文件格式：
PhpSpreadsheet支持文件格式
環(huán)境要求
開始使用
我們寫一個簡單的demo，來學習PhpSpreadsheet的使用，大概就是一個簡單的文件上傳頁面，上傳我們要讀取的Excel文件，PHP接收到文件，調(diào)用PhpSpreadsheet讀取Excel里面的內(nèi)容。
0. 配置環(huán)境
略...，自己配置
我當前的PHP版本是7.2.13
1. 新建一個項目
2. 安裝
使用composer安裝：
默認安裝的是最新的穩(wěn)定版本(1.5)，如果想要安裝dev版本，可以執(zhí)行下面的命令：
上面步驟執(zhí)行完畢后，目錄結(jié)構(gòu)是這樣的：
3. 新建一個簡單的html文件，用來上傳Excel文件
index.html里面的內(nèi)容很簡單，如下：
這里要注意下：form表單的enctype一定要是multipart/form-data
這只是一個簡單的demo，一個form表單就可以了，運行后就是下面這樣了 :)
4. PhpSpreadsheet如何使用？
在處理前端傳過來的Excel文件之前，先來介紹下PhpSpredsheet如何使用。
4.1 讀取文件
PhpSpreadsheet中讀取文件有很多種，對于不同格式的文件有不同的讀取方法，比如：xlsx格式，使用\PhpOffice\PhpSpreadsheet\Reader\Xlsx()，csv格式，使用\PhpOffice\PhpSpreadsheet\Reader\Csv()，乍一看這么多類就感覺有點復雜，其實這些類都實現(xiàn)了\PhpOffice\PhpSpreadsheet\Reader\IReader和\PhpOffice\PhpSpreadsheet\Writer\IWriter接口，指定了要加載的文件類型。我們可以直接使用\PhpOffice\PhpSpreadsheet\IOFactory這個工廠類：
如果想在讀寫文件的時候設置一些屬性，比如讀寫屬性，可以這樣設置：
使用這個工廠類的好處就是你不需要關(guān)心文件上傳的格式，它能自動幫識別，其實這個工廠類就是對你上傳的文件做一些識別，如果識別出來是xls格式，就返回xls的reader，如果是csv，就返回csv的reader，通過分析代碼我們可以看到這個IOFactory可以生產(chǎn)出如下的reader和writer：
可以看到支持的類型還是蠻多的，但是很多都不常用。
在IOFactory工廠中還可以指定讀寫的文件類型，返回對應的reader，這樣就免去了識別文件類型的步驟，如下：
4.2 從源碼比較兩種讀寫方式
首先，來看下IOFactory這個工廠類，我們在不指定reader類型時直接load，代碼內(nèi)部是要做一個識別格式的操作：
從上面的代碼，可以看到在load前是做了文件檢測和類型判斷的操作，然后再返回對應的reader，接下來，再來看看當我們指定了類型后，做了哪些操作的：
上面的就比較簡單了，直接創(chuàng)建reader，然后就load了，只是做了一些實例化的操作。這兩種方法相比，第二種方法性能更好一點，當然前提是要知道文件格式。
5. 讀取Excel文件內(nèi)容
讓我們接著繼續(xù)上面的index.html，我們需要編寫一個PHP文件來處理請求：
我們先引入autoload，接著創(chuàng)建了一個Xlsx的reader，然后load我們上傳的文件，因為在excel中，內(nèi)容都是按sheet區(qū)分的，每一個sheet中都由行和列組成，我們獲取到當前使用的sheet，通過sheet獲取到行的迭代對象，再針對每一行得到每一列對象，在PhpSpreadsheet中，cell是一個最小的單元，對應著第幾行第幾列，數(shù)據(jù)都是存在cell中，得到cell對象我們就能獲取到數(shù)據(jù)。
當我們上傳如下內(nèi)容后：
返回結(jié)果如下：
因為我們在讀取時，是從第二行開始的，所以第一行的內(nèi)容就不顯示了。
這里說一下，在Excel中第三列是一個時間，PhpSpreadsheet對時間的處理有點特殊。在PhpSpreadsheet中date和time在存儲時都是作為數(shù)字類型，當要區(qū)分數(shù)字是時間格式時，需要用到format mask，默認情況下，format mask是開啟了的，但如果設置setReadDataOnly等于true的話，就不能使用format mask，從而就區(qū)分不了數(shù)字和時間格式，PhpSpreatsheet將會全部作為數(shù)字處理。
此時，我們開啟只讀模式看一下，
輸出結(jié)果如下：
第三列就變成了奇怪的數(shù)字，當初這個問題還困擾了我半天。
5. PhpSpreadsheet讀取文件時的一些常用方法
1.如果一個Excel中有多個sheet，只想操作其中的某幾個sheet，可以設置setLoadSheetsOnly
2.讀取指定行和列的數(shù)據(jù)
上面的例子不夠通用，可以修改下使之更為通用：
3.列出Excel中所有sheet的名字
4.列出一個sheet的信息，包括多少列、多少行
PhpSpreadsheet的學習與使用就到這，真的很強大，幾乎滿足了日常的所有需求，是讀取Excel、CSV文件的利器。
如分隔符不同時，如何有效識別下圖中文本數(shù)據(jù)？
const getMark=(chatContent)=> {
// 符號 行 列 分割正則
const regToken=/(\s+|[,\.\|\\\/;\-_~]+|[^\x00-\xff\u4e00-\u9fa5]+|[\w\u4e00-\u9fa5]+)/gi;
// 符號正則
const regMarkToken=/(\s+|[,\.\|\\\/;\-_~]+|[^\x00-\xff\u4e00-\u9fa5]+)/i;
// 可能的對話內(nèi)容
//const chatContent=`行政區(qū)域;人口數(shù)量。四川;1`;
// token數(shù)組
const contents=Array.from(chatContent.match(regToken) || []);
// 符號計數(shù) 待定
const markMap={};
contents.forEach((key)=> {
const status=regMarkToken.test(key);
if (status) {
markMap[key]=(markMap[key] || 0) + 1;
}
});
const markKeys=Object.keys(markMap);
const markLeng=markKeys.length;
// 行分隔符
let markRow=" ";
// 列分隔符
let markCol=" ";
// 一般只有行分割
markLeng===1 && (markRow=markKeys[0]);
// 在一段話中，第一次出現(xiàn)的符號，一般認為是列分割
// 在一段話中，第二次出現(xiàn)的符號，一般認為是行分割
if (markLeng > 1) {
const markColIndex=chatContent.indexOf(markKeys[0]);
const markRowIndex=chatContent.indexOf(markKeys[1]);
const markDifState=markColIndex > markRowIndex;
markCol=markDifState ? markKeys[1] : markKeys[0];
markRow=markDifState ? markKeys[0] : markKeys[1];
}
console.log(contents, "列分隔符", markCol, "行分隔符", markRow);
return { markRow, markCol };
};
getMark("行政區(qū)域;人口數(shù)量。四川;1");
getMark("行政區(qū)域 人口數(shù)量 四川 1");
getMark("行政區(qū)域,人口數(shù)量;四川,1");人人為我，我為人人，歡迎您的瀏覽，我們一起加油吧。
*請認真填寫需求信息，我們會在24小時內(nèi)與您取得聯(lián)系。
