Excel中合并文件和sheet

Sub 汇总多个工作表()
'定义对话框变量
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'新建一个工作簿
Dim newwb As Workbook
Set newwb = Workbooks.Add

With fd
If .Show = -1 Then
'定义单个文件变量
Dim vrtSelectedItem As Variant

'定义循环变量
Dim i As Integer
i = 1

'开始文件检索
For Each vrtSelectedItem In .SelectedItems
'打开被合并工作簿
Dim tempwb As Workbook
Set tempwb = Workbooks.Open(vrtSelectedItem)

'复制工作表
tempwb.Worksheets(1).Copy Before:=newwb.Worksheets(i)

'把新工作簿的工作表名字改成被复制工作簿文件名，这儿应用于xls文件，即Excel97-2003的文件，如果是Excel2007，需要改成xlsx
'newwb.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".xls", "")

'关闭被合并工作簿
tempwb.Close SaveChanges:=False

i = i + 1
Next vrtSelectedItem
End If
End With

Set fd = Nothing
End Sub 

新建一个工作表，命名后保存到和与合并的100个文件同一个文件文件夹，摁 alt + f11,双击工程资源管理器里面的sheet1(sheet1),在右侧的代码区粘贴如下代码。运行。等候一会就OK了。

Sub 合并当前目录下所有工作簿的全部工作表()
Dim MyPath, MyName, AWbName
Dim Wb As Workbook, WbN As String
Dim G As Long
Dim Num As Long
Dim BOX As String
Application.ScreenUpdating = False
MyPath = ActiveWorkbook.Path
MyName = Dir(MyPath & "\" & "*.xls")
AWbName = ActiveWorkbook.Name
Num = 0
Do While MyName ""
If MyName AWbName Then
Set Wb = Workbooks.Open(MyPath & "\" & MyName)
Num = Num + 1
With Workbooks(1).ActiveSheet
.Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)
For G = 1 To Sheets.Count
Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1)
Next
WbN = WbN & Chr(13) & Wb.Name
Wb.Close False
End With
End If
MyName = Dir
Loop
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"
End Sub 

 同字段的sheet合并到一个sheet

<?php 
  require 'PHPExcel-1.8/Classes/PHPExcel.php';
  ini_set('memory_limit','-1');
  ini_set('max_execution_time','10000');
 
    hebin('resumes.xlsx');
   
  
 
    //读取excel表中的数据
    function hebin($filename, $exts = 'xlsx')
    {
 
        header("Content-Type:text/html;charset=utf-8");
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
 
//        Vendor("PHPExcel");
        // Vendor('PHPExcel');
        //创建PHPExcel对象，注意，不能少了\
        $PHPExcel = new \PHPExcel();
  
        //如果excel文件后缀名为.xls，导入这个类
        if ($exts == 'xls') {
//            Vendor("PHPExcel.Reader.Excel5");
            $PHPReader = new \PHPExcel_Reader_Excel5();
 
        } else if ($exts == 'xlsx') {
//            Vendor("PHPExcel.Reader.Excel2007");
            $PHPReader = new \PHPExcel_Reader_Excel2007();
        }
        //载入文件
        $PHPExcel = $PHPReader->load($filename, $encode = 'utf-8');
 
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
		$sheetCount = $PHPExcel->getSheetCount();
        $data=array();
		$allfields = array();
		for( $is = 0; $is < $sheetCount; $is++)
		{
		
			
			$currentSheet = $PHPExcel->getSheet($is);
			//获取总列数
			$columnCount = $currentSheet->getHighestColumn();
			$columnCount = PHPExcel_Cell::columnIndexFromString($columnCount);
			//获取总行数
			$allRow = $currentSheet->getHighestRow();
			//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
			$currentRow = 1 ;
				//从哪列开始，A表示第一列
			for ($currentColumn = 0; $currentColumn < $columnCount; $currentColumn++) {
				//数据坐标
				//读取到的
				$val = $currentSheet->getCellByColumnAndRow($currentColumn,$currentRow)->getValue();

				#数据，保存到数组$arr中
				$allfields[$val] = $val;
					
            }
			
			
		}

		$data=array();
		$data[] = $allfields;
		echo "fieldsCount:".count($allfields);

		$sheetCount = $PHPExcel->getSheetCount();
       
		for( $is = 0; $is < $sheetCount; $is++)
		{
			echo "开始处理sheet$is... \n";
			$fields = array();
			$currentSheet = $PHPExcel->getSheet($is);
			//获取总列数
			$columnCount = $currentSheet->getHighestColumn();
			$columnCount = PHPExcel_Cell::columnIndexFromString($columnCount);
			//获取总行数
			$allRow = $currentSheet->getHighestRow();
			//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
			for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {

				//从哪列开始，A表示第一列
				$dataRow=Array();

				for ($currentColumn = 0; $currentColumn < $columnCount; $currentColumn++)
				{
					//数据坐标
					//读取到的
					$val = $currentSheet->getCellByColumnAndRow($currentColumn,$currentRow)->getValue();

					#数据，保存到数组$arr中
					if ($currentRow == 1 ) 
					{
						$fields[$currentColumn] = $val;
						foreach ($allfields as $field)
						{
								$dataRow[$field] = '';
						}
					}
					else
					{
						
	   				$dataRow[$fields[$currentColumn]] = $val;
						
					}
				}

				$data[] = $dataRow;
 
			}
			echo "sheet$is 总数  $allRow \n";
			echo "sheet$is 处理完毕";
		}
		

	 $objPHPExcel = new PHPExcel();                     //实例化一个PHPExcel()对象
	 $objSheet = $objPHPExcel->getActiveSheet();        //选取当前的sheet对象
	 $objSheet->setTitle('汇总');                      //对当前sheet对象命名
	
	$records = count($data);
	$length = count($data);
	 
	echo " 总记录 : $records, 字段数： $length \n";

	$row = 0;
	$col = 0;
   foreach ($data as $f=>$v)
  {
	$col=0;
     foreach (  $v as $r)
    {
		//  $objSheet->setCellValueByColumnAndRow($col,$row, $r); 
		$objSheet->setCellValueByColumnAndRow($col,$row, $r, \PHPExcel_Cell_DataType::TYPE_STRING);
        //objSheet->setCellValue($cellName[$j] . ($i+$startLine), $testValue[$i][$j], \PHPExcel_Cell_DataType::TYPE_STRING);
		
		$col++;
    }
	$row++;
  }
 
// $objSheet->fromArray($data);  //利用fromArray()直接一次性填充数据
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');   //设定写入excel的类型
 $objWriter->save('test.xlsx');   //保存文件
 
 }
 
?>