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