Excel(xlsx)操作

作者: LCSan | 来源:发表于2020-05-05 12:54 被阅读0次

好用的工具库
python用openpyxl来操作excel表格
python基础之类与对象的应用-操作excel
大家一起学python（9）
3.Python合并多个Excel数据
Excel(xlsx)操作
node读写excel文件
java采坑之路-导出excel超链接
input="file" 浏览时只显示指定文件，筛选特定文件类型
python操作excel之openpyxl

#encoding=utf-8
'''
Created on 2019年11月9日

@author: 瞌睡蟲子
'''
import openpyxl
from openpyxl import utils,Workbook
from openpyxl.drawing.image import Image
from openpyxl.styles import Font,PatternFill

class Excel07:
    def __init__(self):
        self.excel=None
        self.path=None

    # 创建xlsx文件
    def CreateExcel(self, sPath):
        self.excel=Workbook();
        self.excel["Sheet"].title = "Sheet1";
        self.excel.save(sPath);
        self.path=sPath;
        return self;
        
    # 打开xlsx文件
    def OpenExcel(self, sPath):
        stuff=str.lower(sPath[-4:]);
        if stuff=="xlsm":
            self.excel=openpyxl.load_workbook(sPath,keep_vba=True);
        else:
            self.excel=openpyxl.load_workbook(sPath);
        self.path=sPath;
        return self;
    
    # 保存xlsx文件
    def Save(self, bSave=True):
        if bSave:
            self.excel.save(self.path);
        
    # 创建工作表 
    def CreateSheet(self, strSheetName, strWhere="after", bSave=False):
        asheet = self.excel.active
        asheet = self.excel.index(asheet)
        if strWhere.lower() == "after":
            strWhere = asheet + 1
        elif strWhere.lower() == "before":
            strWhere = asheet
        else:
            self._getException(strWhere)
        if strWhere:
            self.excel.create_sheet(strSheetName, strWhere);
        else:
            self.excel.create_sheet(strSheetName);
        self.Save(bSave);    
         
    # 获取所有工作表名 
    def GetSheetsName(self):
        return self.excel.sheetnames;        
         
    # 重命名工作表 
    def SheetRename(self, sheet, strNewName, bSave=False):
        self._getSheet(sheet).title = strNewName;
        self.Save(bSave);
        
    # 复制工作表 
    def CopySheet(self, sheet, strNewSheetName, bSave=False):
        tempSheet=self.excel.copy_worksheet(self._getSheet(sheet));
        tempSheet.title = strNewSheetName;
        self.Save(bSave);   
            
    # 删除工作表 
    def DeleteSheet(self, sheet, bSave=False):  
        self.excel.remove(self._getSheet(sheet));
        self.Save(bSave);
        
    # 激活工作表 
    def ActiveSheet(self, sheet):
        Workbook.active=self._getSheet(sheet);
        
    # 写入单元格 
    def WriteCell(self, sheet, strCell, data, bSave=False):
        cell=self._getCellOrRange(sheet, strCell);
        cell.value = data;
        self.Save(bSave);
        
    # 读取单元格 
    def ReadCell(self, sheet, strCell):
        cell=self._getCellOrRange(sheet, strCell);
        return cell.value;
        
    # 写入行 
    def WriteRow(self, sheet, strCell, data, bSave=False):
        tempSheet=self._getSheet(sheet);  
        tempCells=self._getCells(strCell);        
        column, row = utils.cell.coordinate_from_string(tempCells);
        column=utils.column_index_from_string(column);
        for k in range(len(data)):
            cell='%s%d'%(utils.get_column_letter(column+k),row);
            tempSheet[cell].value = data[k];
        self.Save(bSave);    
        
    # 写入列 
    def WriteColumn(self, sheet, strCell, data, bSave=False):
        tempSheet=self._getSheet(sheet);  
        tempCells=self._getCells(strCell);        
        column, row = utils.cell.coordinate_from_string(tempCells);
        for k in range(len(data)):
            cell='%s%d'%(column,row+k);
            tempSheet[cell].value = data[k];
        self.Save(bSave);
        
    # 读取行 
    def ReadRow(self, sheet, strCell):
        strRange=self._getRow(sheet, strCell);
        return self.ReadRange(sheet, strRange)[0];
        
    # 读取列 
    def ReadColumn(self, sheet, strCell):
        strRange=self._getColumn(sheet, strCell);
        tempRange=self.ReadRange(sheet, strRange)
        return [y for x in tempRange for y in x];
        
    # 插入行 
    def InsertRow(self, sheet, strCell, data, bSave=False):
        tempSheet=self._getSheet(sheet);
        if type(strCell)==int:
            tempSheet.insert_rows(strCell);
            strCell='%s%d'%("A",strCell);
        else:
            tempCells=self._getCells(strCell);        
            column, row = utils.cell.coordinate_from_string(tempCells);
            tempSheet.insert_rows(row);
            strCell=tempCells;
        self.WriteRow(sheet, strCell, data, bSave);  
        
    # 插入列 
    def InsertColumn(self, sheet, strCell, data, bSave=False):
        tempSheet=self._getSheet(sheet);
        if type(strCell)==int:
            tempSheet.insert_cols(strCell);
            strCell='%s%d'%(utils.get_column_letter(strCell),1);
        else:
            tempCells=self._getCells(strCell);        
            column, row = utils.cell.coordinate_from_string(tempCells);
            tempSheet.insert_cols(utils.column_index_from_string(column));
            strCell=tempCells;
        self.WriteColumn(sheet, strCell, data, bSave);
    
    # 合并或拆分单元格 
    def MergeRange(self, sheet, strRange, option=True, bSave=False):
        tempSheet=self._getSheet(sheet);
        tempCells=self._getCells(strRange);
        if option:
            tempSheet.merge_cells(tempCells);
        else:
            tempSheet.unmerge_cells(tempCells);        
        self.Save(bSave);

    # 关闭Excel 
    def CloseExcel(self, bSave=False):
        self.Save(bSave);        
        self.excel.close();

    # 读取区域 
    def ReadRange(self, sheet, strRange):
        cells = self._getCellOrRange(sheet, strRange);
        res=[]
        for r in cells:
            res.append([v.value for v in r]);
        return res;
            
    # 获取行数 
    def GetRowsCount(self, sheet):
        return self._getSheet(sheet).max_row;
    
    # 获取列数 
    def GetColumsCount(self, sheet):
        return self._getSheet(sheet).max_column;        
        
    # 删除行 
    def DeleteRow(self, sheet, strCell, bSave=False):
        tempSheet=self._getSheet(sheet);
        if type(strCell)==int:
            tempSheet.delete_rows(strCell);
        else:
            tempCells=self._getCells(strCell);        
            column, row = utils.cell.coordinate_from_string(tempCells);
            tempSheet.delete_rows(row);
        self.Save(bSave);  
        
    # 删除列 
    def DeleteColumn(self, sheet, strCell, bSave=False):
        tempSheet=self._getSheet(sheet);
        if type(strCell)==int:
            tempSheet.delete_cols(strCell);
        else:
            tempCells=self._getCells(strCell);        
            column, row = utils.cell.coordinate_from_string(tempCells);
            tempSheet.delete_cols(utils.column_index_from_string(column));
        self.Save(bSave);
        
    # 插入图片 
    def InsertImage(self, sheet, strCell, sFilePath, fWidth, fHeight, bSave=False):
        tempSheet=self._getSheet(sheet);  
        tempCells=self._getCells(strCell);        
        column, row = utils.cell.coordinate_from_string(tempCells);
        img = Image(sFilePath);
        img.width, img.height = (fWidth, fHeight);        
        tempSheet.column_dimensions[column].width = fWidth;
        tempSheet.row_dimensions[row].height = fHeight;
        tempSheet.add_image(img, tempCells);
        self.Save(bSave);
         
    # 删除图片 
    def DeleteImage(self, sheet, objPic, bSave=False):
        tempSheet = self._getSheet(sheet);
        del tempSheet._images[objPic];
        self.Save(bSave);
        
    # 写入区域 
    def WriteRange(self, sheet, strCell, data, bSave=False):
        tempSheet=self._getSheet(sheet);  
        tempCells=self._getCells(strCell);        
        column, row = utils.cell.coordinate_from_string(tempCells);
        column=utils.column_index_from_string(column);
        for rg in range(len(data)):
            for r in range(len(data[rg])):
                cell='%s%d'%(utils.get_column_letter(column+r),row+rg);
                tempSheet[cell].value = data[rg][r];
        self.Save(bSave);
        
    # 清除区域 
    def ClearRange(self, sheet, strRange, bClearFormat=True, bSave=False):
        cells=self._getCellOrRange(sheet, strRange);
        for row in cells:
            for cell in row:
                cell.value =None;
                if bClearFormat:
                    cell.font=Font();
                    cell.fill=PatternFill();
        self.Save(bSave);
        
    # 删除区域 
    def DeleteRange(self, sheet, strRange, bSave=False):
        tempRange=self._getCells(strRange);
        min_col, min_row, max_col, max_row1 = utils.cell.range_boundaries(tempRange);
        max_row=self.GetRowsCount(sheet);
        tempRange=self._getCells([[max_row1,min_col],[max_row,max_col]]);
        tempSheet=self._getSheet(sheet);
        tempSheet.move_range(tempRange, rows=(min_row-max_row1), cols=0, translate=True);
        self.ClearRange(sheet, [[max_row-(max_row1-min_row),min_col],[max_row,max_col]], True, False);        
        self.Save(bSave);
        
    # 设置行高 
    def SetRowHeight(self, sheet, strCell, fHeight, bSave=False):
        tempSheet=self._getSheet(sheet);  
        tempCells=self._getCells(strCell);        
        print(tempCells)
        column, row = utils.cell.coordinate_from_string(tempCells);
        tempSheet.row_dimensions[row].height = fHeight;
        self.Save(bSave);      

    # 设置列宽 
    def SetColumnWidth(self, sheet, strCell, fWidth, bSave=False):
        tempSheet=self._getSheet(sheet);  
        tempCells=self._getCells(strCell);        
        column, row = utils.cell.coordinate_from_string(tempCells);
        print(column)
        tempSheet.column_dimensions[column].width = fWidth;
        self.Save(bSave);        
        
    # 设置单元格字体颜色 
    def SetCellFontColor(self, sheet, strCell, listColor, bSave=False):
        cell=self._getCellOrRange(sheet, strCell);
        cell.font=Font(color=self._getColor(listColor));
        self.Save(bSave);
        
        
    # 设置区域字体颜色 
    def SetRangeFontColor(self, sheet, strRange, listColor, bSave=False):
        cells=self._getCellOrRange(sheet, strRange);
        tempColor=Font(color=self._getColor(listColor));
        for row in cells:
            for cell in row:
                cell.font=tempColor;
        self.Save(bSave);
        
    # 设置单元格颜色 
    def SetCellColor(self, sheet, strCell, listColor, bSave=False):
        cell=self._getCellOrRange(sheet, strCell);
        cell.fill=PatternFill("solid", fgColor=self._getColor(listColor));
        self.Save(bSave);
        
    # 设置区域颜色 
    def SetRangeColor(self, sheet, strRange, listColor, bSave=False):
        cells=self._getCellOrRange(sheet, strRange);
        tempColor=PatternFill("solid", fgColor=self._getColor(listColor));
        for row in cells:
            for cell in row:
                cell.fill=tempColor;
        self.Save(bSave);        
        
    def _getSheet(self, sheet):
        if type(sheet) == str:
            return self.excel[sheet];
        elif type(sheet) == int:
            return self.excel.worksheets[sheet];
    
    def _getCells(self, strCell):
        if type(strCell) == str:
            return strCell.upper();
        elif type(strCell) == list and len(strCell) == 2:
            valueTemp = strCell[0];
            expTemp="";
            if type(valueTemp) == int:
                expTemp = '%s%d' % (utils.get_column_letter(strCell[1]), valueTemp);
            elif type(valueTemp) == list:
                expTemp = ['%s%d' % (utils.get_column_letter(r[1]), r[0]) for r in strCell]
                expTemp = ":".join(expTemp);
            else:
                self._getException(strCell)
            return expTemp.upper();
        else:
            self._getException(strCell)
    
    def _getColumn(self, sheet, strCell):
        tempSheet=self._getSheet(sheet);        
        if type(strCell) == str:
            column, row = utils.cell.coordinate_from_string(strCell);
            return '%s:%s%d' % (strCell,column,tempSheet.max_row);
        elif type(strCell) == list:
            tempList=list(filter(lambda x: type(x) == int, strCell));       
            if len(tempList)==2:
                strRange=[];
                strRange.append(strCell);
                strRange.append([tempSheet.max_row,strCell[1]]);
                return strRange;
            else:
                self._getException(strCell)
            
    def _getRow(self, sheet, strCell):
        tempSheet=self._getSheet(sheet);        
        if type(strCell) == str:
            column, row = utils.cell.coordinate_from_string(strCell);
            return '%s:%s%d' % (strCell,utils.get_column_letter(tempSheet.max_column),row);
        elif type(strCell) == list:
            tempList=list(filter(lambda x: type(x) == int, strCell));       
            if len(tempList)==2:
                strRange=[];
                strRange.append(strCell);
                strRange.append([strCell[0],tempSheet.max_column]);
                return strRange;
            else:
                self._getException(strCell)
    
    def _getCellOrRange(self, sheet, strCell):
        tempSheet=self._getSheet(sheet);
        tempCells=self._getCells(strCell);
        return tempSheet[tempCells];
    
    def _getColor(self, color):
        if type(color) == str and len(color) == 6:
            return color.upper();
        elif type(color) == list and len(color) == 3:
            tempList=list(filter(lambda x: type(x) == int and 0 <= x <= 255, color));       
            if len(tempList)==3:
                return "".join([str(hex(r))[-2:].replace('x','0').upper() for r in color]); 
            else:
                self._getException(color);
        else:
            self._getException(color);
    
    def _getException(self,msg):
        raise Exception('参数错误：%s' % str(msg));

 
if __name__ == '__main__':
    ""
#     sPath=r"C:\Users\Administrator\Desktop\插件3.xlsm"
#     print(str.lower(sPath[-4:]))
    xls = Excel07();
#     xls.CreateExcel(r"C:\Users\Administrator\Desktop\插件7.xlsx");
#     xls.Save();
#     xls.CloseExcel();
    xls.OpenExcel(r"C:\Users\Administrator\Desktop\插件7.xlsx");
#     print(xls.GetSheetsName());
#     xls.ActiveSheet("Sheet12");
#     xls.CreateSheet("Sheet13","before",True);
#     xls.Save();
#     xls.DeleteRow("Sheet2",[1,1],True)
#     xls.SheetRename(1, "strNewName1111",True);
#     xls.CopySheet("strNewName", "hahah1", True);
#     xls.DeleteSheet("hahah1");
#     xls.ActiveSheet("Sheet3");
#     xls.WriteCell(0, "B2", 111,True);
#     data = xls.ReadCell(0, [2,2]);
#     print(data,type(data))
#     xls.Save();
#     xls.WriteRow("strNewName", "A4", [123,"3333A","=SUM(A1:B2)"], True)
#     xls.WriteColumn("strNewName", "A5", [123,"3333A","=SUM(A1:B2)"], True)
#     print(xls.ReadRow(0, "A3"))
#     print(xls.ReadRow(0, "a3"))
#     print(xls.ReadRow(0, [3,1]))
#     print(xls.ReadColumn("strNewName", [2,2]))
#     xls.InsertRow(0, [4,5], ["mmm","bbb",666,"rrr"], True)
#     xls.InsertColumn(0, "C3", ["xxxxx","4444",666,"ddd"], True)
#     print(xls.ReadColumn("strNewName", [1,3]))
#     print(xls.ReadColumn(0, "C2"))
#     print(xls.ReadColumn(0, [3,4]))
#     xls.MergeRange(0, "F14:h17", False, True);
#     print(xls.ReadRange(0, [[3,3],[5,6]]))
#     print(xls.ReadRange(0, "c3:f5"))
#     print(xls.ReadRange(0, "c3:f5"))
#     print(xls.GetRowsCount(0))
#     print(xls.GetColumsCount("strNewName"))
#     xls.DeleteRow(0, 35, True)
#     xls.DeleteColumn(0, "E30", True)
#     xls.InsertImage(0, "H21", r"C:\Users\Administrator\Desktop\11.jpg", 64, 64,True)
#     xls.DeleteImage(0, 0, True);
#     xls.WriteRange(0, "B24", [["111","1112","1113","1114","1115"],["111","1112","1113","1114","1115"],["111","1112","1113","1114","1115"],["111","1112","1113","1114","1115"]], True);
#     xls.ClearRange("Sheet1", "B5:d8", True  , True);
    xls.DeleteRange(0, "b5:d8", True);
#     xls.SetColumnWidth(0, [29,5], 0 , True);
#     xls.SetRowHeight(0, [29,5], 0 , True);
#     xls.SetCellColor(0, "A20", "1874CD", True)
#     xls.SetCellFontColor(0, "A20", "FE44FF", True)
#     xls.SetRangeFontColor("strNewName", "B22:e25", "FE44FF", True);
    xls.SetRangeColor("Sheet1", "B24:e27", [255,255,0], True);
    xls.CloseExcel();

好用的工具库
前端操作Excel JS-XLSX支持Excel文件的导入，json数据导出xlsx，支持各大主流前端框架，详见官...
python用openpyxl来操作excel表格
用openpyxl操作excel表格 openpyxl 是一个用来操作excel 2010 的xlsx/xlsm的...
python基础之类与对象的应用-操作excel
python操作excel openpyxl 前置说明安装openpyxl只支持.xlsx后缀创建excel手动...
大家一起学python（9）
python 如何读取excel openpyxl可以很好的用来操作excel表格，只能操作xlsx格式的 1.安...
3.Python合并多个Excel数据
写入数据到excel的操作这样我们得到了包含两个sheet的excel文件demo.xlsx 读取excel操作...
Excel(xlsx)操作
node读写excel文件
Node + node-xlsx 操作excel表格 node使用 node-xlse 简单读写excel表格安...
java采坑之路-导出excel超链接
使用Apache-poi操作Excel xls格式----HSSF xlsx格式----HSSF 运行效果展示： ...
input="file" 浏览时只显示指定文件，筛选特定文件类型
显示 .xls, .xlsx, .csv 文件只显示 Excel (.xlsx) 文件只显示 Excel (...
python操作excel之openpyxl
前言: 下面介绍的用python对excel的简单读写操作,不涉及复杂功能. 1. 支持excel格式 xlsx ...