本文目录一览：
- 1、phpexcel 怎么根据字符串长度设置单元格的宽度
- 2、thinkphp 导出excel 怎么合并单元格
- 3、thinkphp PHPexcel 例子
- 4、导出数据超出EXCEL行列本身限制时如何解决！！！
- 5、phpExcel怎么设置单元格宽度
- 6、关于phpexcel读取excel表的问题，如下
phpexcel 怎么根据字符串长度设置单元格的宽度
1、判断一个单元格内的字符串长度用Len函数，用法如下：
s
=
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";
?
