怎样使用Pandas批量拆分与合并Excel文件?

Pandas批量拆分Excel与合并Excel

实例演示:
1. 将一个大Excel等份拆成多个Excel
2. 将多个小Excel合并成一个大Excel并标记来源

work_dir="./course_datas/c15_excel_split_merge"
splits_dir=f"{work_dir}/splits"

import os
if not os.path.exists(splits_dir):
    os.mkdir(splits_dir)

0、读取源Excel到Pandas

import pandas as pd
df_source = pd.read_excel(f"{work_dir}/crazyant_blog_articles_source.xlsx")
df_source.head()
idtitletags
02585Tensorflow怎样接收变长列表特征python,tensorflow,特征工程
12583Pandas实现数据的合并concatpandas,python,数据分析
22574Pandas的Index索引有什么用途?pandas,python,数据分析
32564机器学习常用数据集大全python,机器学习
42561一个数据科学家的修炼路径数据分析
df_source.index
RangeIndex(start=0, stop=258, step=1)
df_source.shape
(258, 3)
total_row_count = df_source.shape[0]
total_row_count
258

一、将一个大Excel等份拆成多个Excel

  1. 使用df.iloc方法,将一个大的dataframe,拆分成多个小dataframe
  2. 将使用dataframe.to_excel保存每个小Excel

1、计算拆分后的每个excel的行数

# 这个大excel,会拆分给这几个人
user_names = ["xiao_shuai", "xiao_wang", "xiao_ming", "xiao_lei", "xiao_bo", "xiao_hong"]
# 每个人的任务数目
split_size = total_row_count // len(user_names)
if total_row_count % len(user_names) != 0:
    split_size += 1

split_size
43

2、拆分成多个dataframe

df_subs = []
for idx, user_name in enumerate(user_names):
    # iloc的开始索引
    begin = idx*split_size
    # iloc的结束索引
    end = begin+split_size
    # 实现df按照iloc拆分
    df_sub = df_source.iloc[begin:end]
    # 将每个子df存入列表
    df_subs.append((idx, user_name, df_sub))

3、将每个datafame存入excel

for idx, user_name, df_sub in df_subs:
    file_name = f"{splits_dir}/crazyant_blog_articles_{idx}_{user_name}.xlsx"
    df_sub.to_excel(file_name, index=False)

二、合并多个小Excel到一个大Excel

  1. 遍历文件夹,得到要合并的Excel文件列表
  2. 分别读取到dataframe,给每个df添加一列用于标记来源
  3. 使用pd.concat进行df批量合并
  4. 将合并后的dataframe输出到excel

1. 遍历文件夹,得到要合并的Excel名称列表

import os
excel_names = []
for excel_name in os.listdir(splits_dir):
    excel_names.append(excel_name)
excel_names
['crazyant_blog_articles_0_xiao_shuai.xlsx',
 'crazyant_blog_articles_1_xiao_wang.xlsx',
 'crazyant_blog_articles_2_xiao_ming.xlsx',
 'crazyant_blog_articles_3_xiao_lei.xlsx',
 'crazyant_blog_articles_4_xiao_bo.xlsx',
 'crazyant_blog_articles_5_xiao_hong.xlsx']

2. 分别读取到dataframe

df_list = []

for excel_name in excel_names:
    # 读取每个excel到df
    excel_path = f"{splits_dir}/{excel_name}"
    df_split = pd.read_excel(excel_path)
    # 得到username
    username = excel_name.replace("crazyant_blog_articles_", "").replace(".xlsx", "")[2:]
    print(excel_name, username)
    # 给每个df添加1列,即用户名字
    df_split["username"] = username

    df_list.append(df_split)
crazyant_blog_articles_0_xiao_shuai.xlsx xiao_shuai
crazyant_blog_articles_1_xiao_wang.xlsx xiao_wang
crazyant_blog_articles_2_xiao_ming.xlsx xiao_ming
crazyant_blog_articles_3_xiao_lei.xlsx xiao_lei
crazyant_blog_articles_4_xiao_bo.xlsx xiao_bo
crazyant_blog_articles_5_xiao_hong.xlsx xiao_hong

3. 使用pd.concat进行合并

df_merged = pd.concat(df_list)
df_merged.shape
(258, 4)
df_merged.head()
idtitletagsusername
02585Tensorflow怎样接收变长列表特征python,tensorflow,特征工程xiao_shuai
12583Pandas实现数据的合并concatpandas,python,数据分析xiao_shuai
22574Pandas的Index索引有什么用途?pandas,python,数据分析xiao_shuai
32564机器学习常用数据集大全python,机器学习xiao_shuai
42561一个数据科学家的修炼路径数据分析xiao_shuai
df_merged["username"].value_counts()
xiao_hong     43
xiao_bo       43
xiao_shuai    43
xiao_lei      43
xiao_wang     43
xiao_ming     43
Name: username, dtype: int64

4. 将合并后的dataframe输出到excel

df_merged.to_excel(f"{work_dir}/crazyant_blog_articles_merged.xlsx", index=False)

2012年度读写Excel文件的最佳PHP类库收集

PHPExcel

php_excel

PHPExcel是一个使用纯PHP编写的类库,它提供了一系列的类用于读写不同的表格式的文件,比如Excel(BIFF格式).xls文件,Excel 2007 (OfficeOpenXML格式).xlsx文件,CSV文件,Libre/OpenOffice Calc .ods文件,Gnumeric文件,PDF文件,HTML文件等等。本项目使用微软的OpenXML标准和PHP进行构建。

SimpleExcel PHP

simple-excel-php

该PHP类库是一个简单但是非常强大的,支持对微软的Excel、XML、CSV格式文件进行写和解析的类库。

它支持按行读取单元行,或者仅仅一个单元格的内容(和获取一列一样)。

PHP-Excel

用于生成Excel文件的一个简洁的类库。

它需要一个二维数组作为参数,并且使用一个很简单的函数来转换数据。

PHP Export Data

一个简单的类库,用于类表格的数据导出到类似Excel的文件,比如XML、CSV和TSV。它支持按流的方式将内容输出到一个文件,或者直接输出到浏览器。因此它很适合到处大型的数据集合(这样你就不会出现内存不够的情况了)。

Excel Writer (XML) for PHP

是一系列的PHP类库,允许动态的生成Excel文件。它几乎可以称作微软XML体系的一个完整实现。

PHP Excel Reader

该PHP类库体现了在SourceForge上的一个关于PHP对于Excel读取的项目的良好进展。

它直接用二进制的方式读取XLS文件,并且能够从任何单元格返回值和相关联的格式。

 

本文来自疯狂的蚂蚁www.crazyant.net

PHP输出Excel实例代码

这里使用PHPExcel的开源类

一个完整的实例:

<?php 
require_once("../includes/function.php"); //提供了SQL注入检测函数inject_check
require_once("../class/DB.php"); //DB操作类,自己扩展一下
$db = new DB(); 
 
if($_GET['show_year']){ 
    $_GET['show_year'] = inject_check($_GET['show_year']); 
} else { 
    echo "<script>location.href='{$_SERVER['REQUEST_URI']}';</script>"; 
    return; 
} 
 
//查出开设预约的班级的学生名单 
$sql = "select * from bishe_student,student where bishe_student.sno=student.sno "; 
 
if($_GET['show_year'] && $_GET['show_year']!='-1'){ 
    $lagreYear = $_GET['show_year']+1; 
    $sql .= " and bishe_student.time>='{$_GET['show_year']}-01-01 00:00:00' and bishe_student.time<'{$lagreYear}-01-01 00:00:00'"; 
} 
 
$sql .= "order by bishe_student.time asc; "; 
 
$rs = $db->query($sql); 
 
/** 
*该PHP代码块开始为能够输出EXCEL文件作准备 
*/ 
 
////////////////////////////////////////////**********************第一步,开头操作,包括引入类、设定文件基本属性 
require_once("../Excel/Classes/PHPExcel.php"); 
require_once("../Excel/Classes/PHPExcel/Writer/Excel5.php"); 
 
//单元格的字母坐标 
$colx = array('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','aa','ab','ac','ad','ae','af','ag','ah','ai','ak','al','am','an','ao','ap','aq','ar','as','at','au','av','aw','ax','ay','az'); 
// 创建一个处理对象实例        
$objExcel = new PHPExcel();        
       
// 创建文件格式写入对象实例, uncomment        
$objWriter = new PHPExcel_Writer_Excel5($objExcel);  
 
//设置文档基本属性        
$objProps = $objExcel->getProperties();        
$objProps->setCreator( gbkToUtf8("计算机学院专业实践中心") );        
$objProps->setLastModifiedBy( gbkToUtf8("计算机学院专业实践中心") );        
$objProps->setTitle( gbkToUtf8("毕业设计预约学生表") );        
$objProps->setSubject( gbkToUtf8("毕业设计预约学生表") );        
$objProps->setDescription( gbkToUtf8("毕业设计预约学生表") );        
$objProps->setKeywords( gbkToUtf8("毕业设计预约学生表") );        
$objProps->setCategory( gbkToUtf8("毕业设计预约学生表") );  
 
//*************************************        
//设置当前的sheet索引,用于后续的内容操作。        
//一般只有在使用多个sheet的时候才需要显示调用。        
//缺省情况下,PHPExcel会自动创建第一个sheet被设置SheetIndex=0        
$objExcel->setActiveSheetIndex(0);        
$objActSheet = $objExcel->getActiveSheet();        
       
//设置当前活动sheet的名称        
$objActSheet->setTitle('sheet1');  
 
 
//设置宽度 
function setWidth($index,$width=5){ 
    global $objActSheet; 
    $objActSheet->getColumnDimension(gbkToUtf8($index))->setWidth($width); 
} 
 
//宽度设置 
for($i=0;$i<51;$i++){ 
    //echo $colx[$i].""; 
    $ind = $colx[$i]; 
     
    setWidth(gbkToUtf8("$ind"),11); 
} 
 
$objActSheet->getRowDimension(1)->setRowHeight(30);     
$objActSheet->getRowDimension(2)->setRowHeight(20);     
$objActSheet->getRowDimension(3)->setRowHeight(16);  
 
 
//函数:设置border 
function setBorder($index){ 
    global $objActSheet; 
    $objActSheet->getStyle($index)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle($index)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle($index)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objActSheet->getStyle($index)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );  
    $objActSheet->getStyle($index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
    $objActSheet->getStyle($index)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);   
    $objActSheet->getStyle($index)->getAlignment()->setWrapText(true); 
     
     
} 
 
//设置值 
function setValue($index,$value){ 
    global $objActSheet; 
    $objActSheet->setCellValue($index,gbkToUtf8($value) );     
} 
 
//设置值 
function setValueGBK($index,$value){ 
    global $objActSheet; 
    $objActSheet->getCell($index)->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); 
} 
 
//合并单元格 
function merge($fc,$lc){ 
    global $objActSheet; 
    $objActSheet->mergeCells($fc.":".$lc);  
} 
 
//********************开始写入数据 
 
$lastIndex = 10; 
 
$objActSheet->setCellValue('A1',gbkToUtf8( '毕业设计上机预约学生表' ) );     
//合并单元格    
 
$objActSheet->mergeCells("A1:".$colx[$lastIndex]."1");     
//设置样式    
$objStyleA1 = $objActSheet->getStyle('A1');        
$objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
$objFontA1 = $objStyleA1->getFont();        
$objFontA1->setName('Courier New');        
$objFontA1->setSize(18);      
$objFontA1->setBold(true);  
 
 
 
$rowIndexB = 3; 
function showBottomRows($xuhao='序号',$xuehao='学号',$xingming='姓名',$bishelaoshi='毕设老师',$bishetimu='毕设题目',$phone='电话',$email='Email',$beizhu='备注'){ 
    global $colx; 
    global $rowIndexB; 
    $colIndexB = 0; 
     
    //都分三步,设值、合并、边框 
    //序号 
    setValue($colx[$colIndexB].$rowIndexB   ,   $xuhao); 
    merge($colx[$colIndexB].$rowIndexB   ,    $colx[$colIndexB].$rowIndexB ); 
    $colIndexB = $colIndexB+1;   
    //学号 
    setValue($colx[$colIndexB].$rowIndexB   ,   $xuehao); 
    merge($colx[$colIndexB].$rowIndexB   ,    $colx[$colIndexB].$rowIndexB ); 
    $colIndexB = $colIndexB+1; 
     
    //姓名 
    setValue($colx[$colIndexB].$rowIndexB   ,   $xingming); 
    merge($colx[$colIndexB].$rowIndexB   ,    $colx[$colIndexB].$rowIndexB ); 
    $colIndexB = $colIndexB+1; 
     
    //毕设老师 
    setValue($colx[$colIndexB].$rowIndexB   ,   $bishelaoshi); 
    merge($colx[$colIndexB].$rowIndexB   ,    $colx[$colIndexB].$rowIndexB ); 
    $colIndexB = $colIndexB+1; 
     
    //毕设题目 
    setValue($colx[$colIndexB].$rowIndexB   ,   $bishetimu); 
    merge($colx[$colIndexB].$rowIndexB   ,    $colx[$colIndexB+2].$rowIndexB ); 
    $colIndexB = $colIndexB+3; 
     
    //电话 
    setValue($colx[$colIndexB].$rowIndexB   ,   $phone); 
    merge($colx[$colIndexB].$rowIndexB   ,    $colx[$colIndexB].$rowIndexB ); 
    $colIndexB = $colIndexB+1; 
 
    //Email 
    setValue($colx[$colIndexB].$rowIndexB   ,   $email); 
    merge($colx[$colIndexB].$rowIndexB   ,    $colx[$colIndexB+1].$rowIndexB ); 
    $colIndexB = $colIndexB+2; 
     
    //备注 
    setValue($colx[$colIndexB].$rowIndexB   ,   $beizhu); 
    merge($colx[$colIndexB].$rowIndexB   ,    $colx[$colIndexB].$rowIndexB ); 
    $colIndexB = $colIndexB+1; 
             
     
    for($xx=0;$xx<$lastIndex;$xx++){ 
        setBorder($colx[$xx].$rowIndexB); 
    } 
    $rowIndexB++; 
} 
showBottomRows(); 
//********************************************表格头部结束 
////////////////////////////////////////////*********************第一步结束,第二步就可以开始打印数据,在下面的主要函数里面 
 
 
$order=1; 
while($row = $db->getRows($rs)){ 
    showBottomRows( 
                $order++ , 
                " ".$row['sno'],  
                " ".$row['sname'] ,  
                " ".$row['bishe_teacher'], 
                " ".$row['bishe_title'], 
                " ".$row['bishe_phone'], 
                " ".$row['bishe_email'], 
                " " 
    ); 
} 
 
//刷新border 
for($i=0;$i<=$lastIndex;$i++){ 
    $objStyleA3 = $objActSheet->getStyle('A3');   
    $objStyleA3->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objStyleA3->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objStyleA3->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );    
    $objStyleA3->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );  
    $objStyleA3->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);  
    $objStyleA3->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
 
    //$objActSheet->duplicateStyle($objStyleA3, 'A3:'.$colx[$lastIndex].(mysql_num_rows( $result_stuList )+3) );  
    $objActSheet->duplicateStyle($objStyleA3, 'A3:'.$colx[$lastIndex].($db->getRowsNum($rs)+3) );  
} 
 
$cancel_time=date("YmdHis");    
$outputFileName = "{$cancel_time}bisheYuyueStudentList.xls";        
//到文件        
$objWriter->save('../temp/'.$outputFileName); 
 
 
    header("Content-Type: application/OCTET-STREAM"); 
    header("Content-Disposition: inline; filename={$_GET['show_year']}年毕业设计上机预约学生表.xls"); 
    readfile('../temp/'.$outputFileName); 
     
echo "<script language='javascript'> 
        location.href='{$_SERVER['REQUEST_URI']}'; 
    </script> 
    "; 
?>