使用poi读写excel文件_poi读取单元格数据通过列号找到表头名称-CSDN博客

使用poi读写excel文件

今天一个同学需要处理一个excel文件，于是我便在网上搜了一下方法，顺便自己研究一下。刚刚参考网上资料，使用poi库测试了一下读取excel文件，效果不错，跟大家分享一下。

要读取的excel文件内容如下：

第一列是数值型，第二列是字符型，代码如下：

 
    01package poi;
 
    02 
 
    03import java.io.FileInputStream;
 
    04import java.io.InputStream;
 
    05import java.util.Iterator;
 
    06 
 
    07import org.apache.poi.hssf.extractor.ExcelExtractor;
 
    08import org.apache.poi.hssf.usermodel.HSSFCell;
 
    09import org.apache.poi.hssf.usermodel.HSSFRow;
 
    10import org.apache.poi.hssf.usermodel.HSSFSheet;
 
    11import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
    12import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 
    13import org.apache.poi.ss.usermodel.Cell;
 
    14import org.apache.poi.ss.usermodel.Row;
 
    15 
 
    16/**
 
    17 * 测试poi读取excel文件内容
 
    18 * @author lihui
 
    19 *
 
    20 */
 
    21public class TestRead {
 
    22 
 
    23        /**
 
    24         * @param args
 
    25         */
 
    26        public static void  main(String[] args){
 
    27 
 
    28                HSSFWorkbook wb = null;
 
    29                POIFSFileSystem fs = null;
 
    30                try {
 
    31                        //设置要读取的文件路径
 
    32                        fs = new POIFSFileSystem(new FileInputStream("d:\\book1.xls"));
 
    33                        //HSSFWorkbook相当于一个excel文件，HSSFWorkbook是解析excel2007之前的版本（xls）
 
    34                        //之后版本使用XSSFWorkbook（xlsx）
 
    35                        wb = new HSSFWorkbook(fs);
 
    36                        //获得sheet工作簿
 
    37                        HSSFSheet sheet = wb.getSheetAt(0);
 
    38                        //获得行
 
    39                        HSSFRow row = sheet.getRow(3);
 
    40                        //获得行中的列，即单元格
 
    41                        HSSFCell cell = row.getCell(0);
 
    42                        //获得单元格中的值，这里该单元格的值为数字，所以使用getNumericCellValue，如为字符串则会报错
 
    43                        //如何取别的值，见print2方法
 
    44                        double msg = cell.getNumericCellValue();
 
    45                        System.out.println(msg);
 
    46                        print1();
 
    47                        print2();
 
    48                } catch (Exception e) {
 
    49                        e.printStackTrace();
 
    50                }
 
    51        }
 
    52 
 
    53        public static void  print1() throws Exception {
 
    54                InputStream is = new FileInputStream("d:\\book1.xls");
 
    55                HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
 
    56                //A text extractor for Excel files.
 
    57                //Returns the textual content of the file, suitable for indexing by something like Lucene,
 
    58                //but not really intended for display to the user.
 
    59                //用来获得整个excel文件的内容，表示为字符串
 
    60                ExcelExtractor extractor = new ExcelExtractor(wb);
 
    61                //字符串所包含的类型，详见api
 
    62                extractor.setIncludeSheetNames(true);
 
    63                extractor.setFormulasNotResults(false);
 
    64                extractor.setIncludeCellComments(true);
 
    65                //获得字符串形式
 
    66                String text = extractor.getText();
 
    67                System.out.println(text);
 
    68        }
 
    69 
 
    70        public static void  print2() throws Exception {
 
    71                HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(
 
    72                                "d:\\book1.xls"));
 
    73                HSSFSheet sheet = wb.getSheetAt(0);
 
    74                //迭代行
 
    75                for (Iterator<Row> iter = (Iterator<Row>) sheet.rowIterator(); iter
 
    76                                .hasNext();) {
 
    77                        Row row = iter.next();
 
    78                        //迭代列
 
    79                        for (Iterator<Cell> iter2 = (Iterator<Cell>) row.cellIterator(); iter2
 
    80                                        .hasNext();) {
 
    81                                Cell cell = iter2.next();
 
    82                                //用于测试的文件就2列，第一列为数字，第二列为字符串
 
    83                                //对于数字cell.getCellType的值为HSSFCell.CELL_TYPE_NUMERIC，为0
 
    84                                //对于字符串cell.getCellType的值为HSSFCell.CELL_TYPE_STRING，为1
 
    85                                //完整的类型列表请查看api
 
    86                                String content = cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC?cell.getNumericCellValue()+"":cell.getStringCellValue();
 
    87                                System.out.println(content);
 
    88                        }
 
    89                }
 
    90        }
 
    91 
 
    92}

下面是创建一个excel文件

代码是参考网上自己做了些修改，加了些注释，已经写得很详细了，我就不多说了。poi库的下载地址可以去官方http://apache.freelamp.com/jakarta/poi/ 下载，我使用的是最新稳定版3.7，这里我也提供单独的jar包给大家下载http://cangzhitao.com/wp-content/uploads/2011/03/poi-3.7-20101029.jar

附cell类型说明：