PHPExcel读取excel文件2

来源：互联网发布：mysql statistics状态编辑：程序博客网时间：2024/06/06 05:24

PHPExcel读取excel文件

PHPExcel是个很强大的类库，以前只使用过它生成Excel文件，非常方便。

今天接到个项目要读取Excel的文件，以前也做过Excel转换写入数据库的工作，

不过相对简单一些，是转换成CSV格式再进行解析的。

首先下载PHPExcel类库。http://phpexcel.codeplex.com/

包含PHPExcel类库文件，如果不能确定文件类型的话可以使用PHPExcel_IOFactory::identify方法返回文件的类型，传递给该函数一个文件名就可以。

然后根据返回的文件类型创建该类型的读取对象，进行文件的load。

之后就可以进行数据的读取了，
具体代码如下所示：

<?php
require_once(‘include/common.inc.php’);
require_once(ROOTPATH . ‘include/phpExcel/PHPExcel/IOFactory.php’);
$filePath = ‘./file/xls/110713.xls’;
$fileType = PHPExcel_IOFactory::identify($filePath); //文件名自动判断文件类型
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($filePath);
$currentSheet = $objPHPExcel->getSheet(0); //第一个工作簿
$allRow = $currentSheet->getHighestRow(); //行数
$output = array();
$preType = ”;
$qh = $currentSheet->getCell(‘A4′)->getValue();
//按照文件格式从第7行开始循环读取数据
for($currentRow = 7;$currentRow<=$allRow;$currentRow++){
//判断每一行的B列是否为有效的序号，如果为空或者小于之前的序号则结束
$xh = (int)$currentSheet->getCell(‘B’.$currentRow)->getValue();
if(empty($xh))break;
$tmpType = (string)$currentSheet->getCell(‘C’.$currentRow)->getValue(); //赛事类型
if(!empty($tmpType))$preType = $tmpType;
$output[$xh][‘type’] = $preType;
$output[$xh][‘master’] = $currentSheet->getCell(‘F’.$currentRow)->getValue(); //主队
$output[$xh][‘guest’] = $currentSheet->getCell(‘H’.$currentRow)->getValue(); //客队
}
//从当前行开始往下循环，取出第一个不为空的行
for( ; ; $currentRow++){
$xh = (int)$currentSheet->getCell(‘B’.$currentRow)->getValue();
if(!empty($xh))break;
}
for( ; $currentRow <= $allRow; $currentRow++){
$xh = (int)$currentSheet->getCell(‘B’.$currentRow)->getValue();
if(empty($xh))break;
$output[$xh][‘rq’] = $currentSheet->getCell(‘I’.$currentRow)->getValue();
}
header(“content-type:text/html; charset=utf-8″);
echo ‘期号：’ . $qh . “\n\n”;
if(!empty($output)){
printf(“%-5s\t%-15s\t%-40s\t%-40s\t%-5s\n”, ‘序号’, ‘赛事类型’, ‘主队’, ‘客队’, ‘让球值’);
foreach($output as $key => $row){
$format = “%-5d\t%-15s\t%-40s\t%-40s\t%-5s\n”;
printf($format, $key, $row[‘type’], $row[‘master’], $row[‘guest’], $row[‘rq’]);
}
}
?>
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
/*
* 将excel转换为数组 by aibhsc
* */
require(ROOT_PATH .'includes/PHPExcel.php');//引入PHP EXCEL类
function format_excel2array($filePath='',$sheet=0){
        if(empty($filePath)or !file_exists($filePath)){die('file not exists');}
        $PHPReader= new PHPExcel_Reader_Excel2007();        //建立reader对象
        if(!$PHPReader->canRead($filePath)){
                $PHPReader= new PHPExcel_Reader_Excel5();
                if(!$PHPReader->canRead($filePath)){
                        echo'no Excel';
                        return;
                }
        }
        $PHPExcel= $PHPReader->load($filePath);       //建立excel对象
        $currentSheet= $PHPExcel->getSheet($sheet);       //**读取excel文件中的指定工作表*/
        $allColumn= $currentSheet->getHighestColumn();       //**取得最大的列号*/
        $allRow= $currentSheet->getHighestRow();       //**取得一共有多少行*/
        $data= array();
        for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){       //循环读取每个单元格的内容。注意行从1开始，列从A开始
                for($colIndex='A';$colIndex<=$allColumn;$colIndex++){
                        $addr= $colIndex.$rowIndex;
                        $cell= $currentSheet->getCell($addr)->getValue();
                        if($cellinstanceof PHPExcel_RichText){//富文本转换字符串
                                $cell= $cell->__toString();
                        }
                        $data[$rowIndex][$colIndex] = $cell;
                }
        }
        return$data;
}


使用方法：
                        $filePath= ROOT_PATH.'data/diamondStock.xlsx';       //钻石库存文件
                        $data= format_excel2array($filePath);
                        print_r($data);die;

输出结果示例：

Array
(
    [1] => Array
        (
            [A] => 商品编号
            [B] => 商品名称
            [C] => 总重量
            [D] => 进货价格
            [E] => 销售价格
            [F] => 4C备注
        )

    [2] => Array
        (
            [A] => 10001
            [B] => GIA-2156685995
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [3] => Array
        (
            [A] => 10002
            [B] => GIA-2156685996
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [4] => Array
        (
            [A] => 10003
            [B] => GIA-2156685997
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [5] => Array
        (
            [A] => 10004
            [B] => GIA-2156685998
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [6] => Array
        (
            [A] => 10005
            [B] => GIA-2156685999
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [7] => Array
        (
            [A] => 10006
            [B] => GIA-2156686000
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [8] => Array
        (
            [A] => 10007
            [B] => GIA-2156686001
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [9] => Array
        (
            [A] => 10008
            [B] => GIA-2156686002
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [10] => Array
        (
            [A] => 10009
            [B] => GIA-2156686003
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [11] => Array
        (
            [A] => 10010
            [B] => GIA-2156686004
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [12] => Array
        (
            [A] => 10011
            [B] => GIA-2156686005
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [13] => Array
        (
            [A] => 10012
            [B] => GIA-2156686006
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

    [14] => Array
        (
            [A] => 10013
            [B] => GIA-2156686007
            [C] => 0.7
            [D] => 1760
            [E] => 1848
            [F] => G色、0.7ct、SI1、FR
        )

)

0 0