phpexcel默認行高,exce表格怎麼設置行高

本文目錄一覽：

1、phpexcel 怎麼根據字符串長度設置單元格的寬度
2、thinkphp 導出excel 怎麼合併單元格
3、thinkphp PHPexcel 例子
4、導出數據超出EXCEL行列本身限制時如何解決！！！
5、phpExcel怎麼設置單元格寬度
6、關於phpexcel讀取excel表的問題，如下

phpexcel 怎麼根據字符串長度設置單元格的寬度

1、判斷一個單元格內的字符串長度用Len函數，用法如下：

Len(Range(“A1”).Text)

這裡的s就表示字符串有多少個字符組成的。

2、判斷選中的單元格是由幾個小格子組成的可以這樣：

比如：

Sub

yy()

[h6:k8].Merge

‘將h6:k8區域合併單元格

[h6].Select

‘選中H6：k8這個合併單元格

MsgBox

Selection.Count

‘通過Selection.Count這個命令就可以得到選中的這個區域的單元格數目End

Sub3、讓單元格自動調整行高，用AutoFit，自動適應方法，用法如下：

Sub

xx()

Range(“A1:F6”).Rows.AutoFitEnd

Sub這樣就可以自動調整行高了！

thinkphp 導出excel 怎麼合併單元格

PHPexcel導出_帶合併單元格/邊框背景/_有效果圖_thinkphp

##供稿單位導出

public function gonggao_excel(){

$color=’0xCC000000′;

//查詢所有供稿單位

$order=’paixu asc,id desc’;

$where=array(‘token’=$this-token,’status’=’1’);

$data=M(‘gonggao’)-where($where)-order($order)-select();

//增加供稿單位的文章數量

foreach ($data as $key = $value) {

$data[$key][‘shuliang’]=M(‘img’)-where(array(‘gonggaoid’=$value[‘id’]))-count();

$data[$key][‘title’]=M(‘img’)-where(array(‘gonggaoid’=$value[‘id’]))-getfield(‘title’,true);

}

// //查詢出所有行數

// $all_count=0;

// foreach ($data as $key = $value) {

// if(!empty($value[‘title’])){

// $all_count=$all_count+count($value[‘title’]);

// }else{

// $all_count++;

// }

//重組數組

foreach ($data as $key = $value) {

if(!empty($value[‘title’])){

foreach ($value[‘title’] as $k = $v) {

$newData[]=$v;

}

}else{

$newData[]=”;

}

//dump($newData);

//dump($data);

//die;

// 引用phpexcel類

import(‘Lib/ORG/PHPExcel’);

// 創建對象

$objPHPExcel = new PHPExcel();

// 顯示錯誤信息

error_reporting(E_ALL);

// Set properties

$objPHPExcel-getProperties()-setCreator(“趙英傑”)

-setLastModifiedBy(“趙英傑”)

-setTitle(“Office 2007 XLSX Test Document”)

-setSubject(“Office 2007 XLSX Test Document”)

-setDescription(“Test document for Office 2007 XLSX, generated using PHP classes.”)

-setKeywords(“office 2007 openxml php”)

-setCategory(“Test result file”);

// 設置寬度

$objPHPExcel-getActiveSheet()-getColumnDimension(‘A’)-setWidth(10);

$objPHPExcel-getActiveSheet()-getColumnDimension(‘B’)-setWidth(40);

$objPHPExcel-getActiveSheet()-getColumnDimension(‘C’)-setWidth(15);

$objPHPExcel-getActiveSheet()-getColumnDimension(‘D’)-setWidth(60);

// 設置行高度

$objPHPExcel-getActiveSheet()-getDefaultRowDimension()-setRowHeight(20); //設置默認行高

$objPHPExcel-getActiveSheet()-getRowDimension(‘1’)-setRowHeight(30); //第一行行高

$objPHPExcel-getActiveSheet()-getRowDimension(‘2’)-setRowHeight(20); //第二行行高

// 字體和樣式

$objPHPExcel-getActiveSheet()-getDefaultStyle()-getFont()-setSize(12); //字體大小

$objPHPExcel-getActiveSheet()-getStyle(‘A2:D2’)-getFont()-setBold(false); //第二行是否加粗

$objPHPExcel-getActiveSheet()-getStyle(‘A1’)-getFont()-setBold(true); //第一行是否加粗

$objPHPExcel-getActiveSheet()-getStyle(‘A1’)-getFont()-setSize(16); //第一行字體大小

// 設置垂直居中

$objPHPExcel-getActiveSheet()-getStyle(‘A1’)-getAlignment()-setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘A2:D2’)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//邊框樣式

$styleArray = array(

‘borders’ = array(

‘allborders’ = array(

//’style’ = PHPExcel_Style_Border::BORDER_THICK,//邊框是粗的

‘style’ = PHPExcel_Style_Border::BORDER_THIN,//細邊框

‘color’ = array(‘argb’ = $color),

);

$objPHPExcel-getActiveSheet()-getStyle(‘A2:D2’)-applyFromArray($styleArray);

//邊框

//$objPHPExcel-getActiveSheet()-getStyle(‘A2:D2’)-getBorders()-getAllBorders()-setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// 設置水平居中

$objPHPExcel-getActiveSheet()-getStyle(‘A1’)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘A’)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘B’)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘C’)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘D’)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

// 合併

$objPHPExcel-getActiveSheet()-mergeCells(‘A1:D1’);

// 表頭

$objPHPExcel-setActiveSheetIndex(0)

-setCellValue(‘A1’, ‘供稿單位文章統計’)

-setCellValue(‘A2’, ‘編號’)

-setCellValue(‘B2’, ‘供稿單位’)

-setCellValue(‘C2’, ‘文章數量’)

-setCellValue(‘D2’, ‘文章名稱’);

// 內容

// for ($i = 0, $len = count($data); $i $len; $i++) {

// $objPHPExcel-getActiveSheet(0)-setCellValue(‘A’ . ($i + 3), $i);

// $objPHPExcel-getActiveSheet(0)-setCellValue(‘B’ . ($i + 3), $data[$i][‘gonggao’]);

// $objPHPExcel-getActiveSheet(0)-setCellValue(‘C’ . ($i + 3), $data[$i][‘shuliang’]);

// $objPHPExcel-getActiveSheet(0)-setCellValue(‘D’ . ($i + 3), $data[$i][‘status’]);

// $objPHPExcel-getActiveSheet()-getStyle(‘A’ . ($i + 3) . ‘:D’ . ($i + 3))-getAlignment()-setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

// $objPHPExcel-getActiveSheet()-getStyle(‘A’ . ($i + 3) . ‘:D’ . ($i + 3))-getBorders()-getAllBorders()-setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// $objPHPExcel-getActiveSheet()-getRowDimension($i + 3)-setRowHeight(16);

// }

//重構內容-文章名稱錄入

for ($i = 0, $len = count($newData); $i $len; $i++) {

$objPHPExcel-getActiveSheet(0)-setCellValue(‘D’ . ($i + 3), $newData[$i]);

$objPHPExcel-getActiveSheet()-getStyle(‘A’ . ($i + 3) . ‘:D’ . ($i + 3))-getAlignment()-setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

//左對齊

$objPHPExcel-getActiveSheet()-getStyle(‘D’)-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

//邊框設置

//$objPHPExcel-getActiveSheet()-getStyle(‘A’ . ($i + 3) . ‘:D’ . ($i + 3))-getBorders()-getAllBorders()-setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// $objPHPExcel-getActiveSheet()-getRowDimension($i + 3)-setRowHeight(16); //重構行高

$objPHPExcel-getActiveSheet()-getStyle(‘A’ . ($i + 3) . ‘:D’ . ($i + 3))-applyFromArray($styleArray);

}

// //查詢出所有行數

// $all_count=0;

// foreach ($data as $key = $value) {

// if(!empty($value[‘title’])){

// $all_count=$all_count+count($value[‘title’]);

// }else{

// $all_count++;

// }

//合併對應單元格並錄入編號/供稿單位/數量。

$ii=3;//從第三行開始的

for ($i = 0, $len = count($data); $i $len; $i++) {

if(count($data[$i][‘title’])0){

//合併注意-1，因為當前單位有內容且是第一篇

$objPHPExcel-getActiveSheet()-mergeCells(‘A’ . ($ii) . ‘:A’ . ($ii-1+count($data[$i][‘title’]) ));

$objPHPExcel-getActiveSheet()-mergeCells(‘B’ . ($ii) . ‘:B’ . ($ii-1+count($data[$i][‘title’]) ));

$objPHPExcel-getActiveSheet()-mergeCells(‘C’ . ($ii) . ‘:C’ . ($ii-1+count($data[$i][‘title’]) ));

//錄入記錄

$objPHPExcel-getActiveSheet(0)-setCellValue(‘A’ . ($ii), $i+1);

$objPHPExcel-getActiveSheet(0)-setCellValue(‘B’ . ($ii), $data[$i][‘gonggao’]);

$objPHPExcel-getActiveSheet(0)-setCellValue(‘C’ . ($ii), count($data[$i][‘title’]));

//居中

$objPHPExcel-getActiveSheet()-getStyle(‘A’ . ($ii))-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘B’ . ($ii))-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘C’ . ($ii))-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$ii=$ii-1+count($data[$i][‘title’]);

$ii++;

}else{

//錄入記錄

$objPHPExcel-getActiveSheet(0)-setCellValue(‘A’ . ($ii), $i+1);

$objPHPExcel-getActiveSheet(0)-setCellValue(‘B’ . ($ii), $data[$i][‘gonggao’]);

$objPHPExcel-getActiveSheet(0)-setCellValue(‘C’ . ($ii), count($data[$i][‘title’]));

//居中

$objPHPExcel-getActiveSheet()-getStyle(‘A’ . ($ii))-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘B’ . ($ii))-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel-getActiveSheet()-getStyle(‘C’ . ($ii))-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$ii++;

}

// Rename sheet

$objPHPExcel-getActiveSheet()-setTitle(‘供稿單位文章統計’);

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel-setActiveSheetIndex(0);

// 輸出

header(‘Content-Type: application/vnd.ms-excel’);

header(‘Content-Disposition: attachment;filename=”‘ . ‘供稿單位’ . ‘.xls”‘);

header(‘Cache-Control: max-age=0’);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);

$objWriter-save(‘php://output’);

exit;

}

thinkphp PHPexcel 例子

Vendor(“PHPExcel.PHPExcel”);

$obj = new PHPExcel();

$obj-getProperties()-setCreator(“JAMES”)

-setLastModifiedBy(“JAMES”)

-setTitle(“zltrans”)

-setSubject(“Dorder”)

-setDescription(“Dorder List”)

-setKeywords(“Dorder”)

-setCategory(“Test result file”);

$obj-getDefaultStyle()-getAlignment()-setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

$obj-getDefaultStyle()-getAlignment()-setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//相關行高

$obj-getActiveSheet()-getRowDimension(‘1’)-setRowHeight(40);

$obj-getActiveSheet()-getRowDimension(‘2’)-setRowHeight(23);

$obj-getActiveSheet()-getRowDimension(‘3’)-setRowHeight(24);

//標題

$obj-getActiveSheet()-mergeCells(‘A1:’.$arr[$len-1].”1″);

$obj-getActiveSheet()-getStyle(‘A1’)-getFont()-setSize(14);

$obj-getActiveSheet()-getStyle(‘A1’)-getFont()-setBold(true);

//寫入相關值

$obj-getActiveSheet()-setCellValue(“A1″,”門票日報表”);

//導入

Vendor(“PHPExcel.PHPExcel.IOFactory”);

$file_name = “s1”;

header(‘Content-Type: application/vnd.ms-excel’);

header(‘Content-Disposition: attachment;filename=”‘.$file_name .’.xls”‘);

header(‘Cache-Control: max-age=0’);

$objWriter = PHPExcel_IOFactory::createWriter($obj, ‘Excel5’);

$objWriter-save(‘php://output’);

導出數據超出EXCEL行列本身限制時如何解決！！！

1、//導入PHPExcel類庫，因為PHPExcel沒有用命名空間，只能inport導入

import(“Org.Util.PHPExcel”);

import(“Org.Util.PHPExcel.Writer.Excel5”);

import(“Org.Util.PHPExcel.IOFactory.php”);

$date = date(“Y_m_d”,time());

$fileName .= “_{$date}.xls”;

2、 //創建PHPExcel對象，注意，不能少了\

$objPHPExcel = new \PHPExcel();

$objProps = $objPHPExcel-getProperties();

3、//設置表頭

$key = 0;

4、 //print_r($headArr);exit;

foreach($headArr as $v){

5、//注意，不能少了。將列數字轉換為字母\

$colum = \PHPExcel_Cell::stringFromColumnIndex($key);

$objPHPExcel-setActiveSheetIndex(0) -setCellValue($colum.’1′, $v);

$key += 1; }

$column = 2;

$objActSheet = $objPHPExcel-getActiveSheet();

foreach($data as $key = $rows){ //行寫入 $span = 0;

foreach($rows as $keyName=$value){// 列寫入

$j = \PHPExcel_Cell::stringFromColumnIndex($span);

$objActSheet-setCellValue($j.$column, $value);

$span++; }

$column++; }

$fileName = iconv(“utf-8”, “gb2312”, $fileName);

7、//重命名表

$objPHPExcel-getActiveSheet()-setTitle($date);

8、//設置字體大小

$objPHPExcel-getDefaultStyle()-getFont()-setSize(14);

9、 //設置單元格寬度

$objPHPExcel-getActiveSheet()-getColumnDimension(‘A’)-setWidth(15);

10、 //設置默認行高

$objPHPExcel-getActiveSheet()-getDefaultRowDimension()-setRowHeight(23);

11、//設置活動單指數到第一個表,所以Excel打開這是第一個表

$objPHPExcel-setActiveSheetIndex(0);

header(‘Content-Type: application/vnd.ms-excel’);

header(“Content-Disposition: attachment;filename=\”$fileName\””);

header(‘Cache-Control: max-age=0’);

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5’);

$objWriter-save(‘php://output’); //文件通過瀏覽器下載exit;}

phpExcel怎麼設置單元格寬度

/設置寬度

$objActSheet-getColumnDimension(‘B’)-setAutoSize(true);

$objActSheet-getColumnDimension(‘A’)-setWidth(30);

關於phpexcel讀取excel表的問題，如下

$sheet_i = 2;//行

$sheet_j = 1;//列

for($currentRow = 2;$currentRow = $allRow;$currentRow++){

/**從第A列開始輸出*/

for($currentColumn= ‘A’;$currentColumn= $allColumn; $currentColumn++){

$val = $currentSheet-getCellByColumnAndRow(ord($currentColumn) – 65,$currentRow)-getValue();/**ord()將字符轉為十進制數*/

if($sheet_i = 2$sheet_j = 1)//自己算好輸出第幾行第幾列

{

if($currentColumn == ‘A’)

{

echo GetData($val).”\t”;

}else{

//echo $val;

/**如果輸出漢字有亂碼，則需將輸出內容用iconv函數進行編碼轉換，如下將gb2312編碼轉為utf-8編碼輸出*/

echo iconv(‘utf-8′,’gb2312’, $val).”\t”;

}

$sheet_j ++;

}

$sheet_i++;

$sheet_j = 0;//重置列數

echo “/br”;

}

echo “\n”;

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/195839.html

phpexcel默認行高,exce表格怎麼設置行高

本文目錄一覽：

phpexcel 怎麼根據字符串長度設置單元格的寬度

thinkphp 導出excel 怎麼合併單元格

thinkphp PHPexcel 例子

導出數據超出EXCEL行列本身限制時如何解決！！！

phpExcel怎麼設置單元格寬度

關於phpexcel讀取excel表的問題，如下

相關推薦

發表回復