使用poi读写excel文件

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

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

第一列是数值型,第二列是字符型,代码如下:

package poi;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/**
 * 测试poi读取excel文件内容
 * @author lihui
 *
 */
public class TestRead {

        /**
         * @param args
         */
        public static void main(String[] args){

                HSSFWorkbook wb = null;
                POIFSFileSystem fs = null;
                try {
                        //设置要读取的文件路径
                        fs = new POIFSFileSystem(new FileInputStream("d:\\book1.xls"));
                        //HSSFWorkbook相当于一个excel文件,HSSFWorkbook是解析excel2007之前的版本(xls)
                        //之后版本使用XSSFWorkbook(xlsx)
                        wb = new HSSFWorkbook(fs);
                        //获得sheet工作簿
                        HSSFSheet sheet = wb.getSheetAt(0);
                        //获得行
                        HSSFRow row = sheet.getRow(3);
                        //获得行中的列,即单元格
                        HSSFCell cell = row.getCell(0);
                        //获得单元格中的值,这里该单元格的值为数字,所以使用getNumericCellValue,如为字符串则会报错
                        //如何取别的值,见print2方法
                        double msg = cell.getNumericCellValue();
                        System.out.println(msg);
                        print1();
                        print2();
                } catch (Exception e) {
                        e.printStackTrace();
                }
        }

        public static void print1() throws Exception {
                InputStream is = new FileInputStream("d:\\book1.xls");
                HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
                //A text extractor for Excel files.
                //Returns the textual content of the file, suitable for indexing by something like Lucene,
                //but not really intended for display to the user.
                //用来获得整个excel文件的内容,表示为字符串
                ExcelExtractor extractor = new ExcelExtractor(wb);
                //字符串所包含的类型,详见api
                extractor.setIncludeSheetNames(true);
                extractor.setFormulasNotResults(false);
                extractor.setIncludeCellComments(true);
                //获得字符串形式
                String text = extractor.getText();
                System.out.println(text);
        }

        public static void print2() throws Exception {
                HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(
                                "d:\\book1.xls"));
                HSSFSheet sheet = wb.getSheetAt(0);
                //迭代行
                for (Iterator<Row> iter = (Iterator<Row>) sheet.rowIterator(); iter
                                .hasNext();) {
                        Row row = iter.next();
                        //迭代列
                        for (Iterator<Cell> iter2 = (Iterator<Cell>) row.cellIterator(); iter2
                                        .hasNext();) {
                                Cell cell = iter2.next();
                                //用于测试的文件就2列,第一列为数字,第二列为字符串
                                //对于数字cell.getCellType的值为HSSFCell.CELL_TYPE_NUMERIC,为0
                                //对于字符串cell.getCellType的值为HSSFCell.CELL_TYPE_STRING,为1
                                //完整的类型列表请查看api
                                String content = cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC?cell.getNumericCellValue()+"":cell.getStringCellValue();
                                System.out.println(content);
                        }
                }
        }

}

 


下面是创建一个excel文件

 1 package poi;
 2 
 3 import java.io.FileOutputStream;
 4 import java.util.Date;
 5 
 6 import org.apache.poi.hssf.usermodel.HSSFCell;
 7 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 8 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
 9 import org.apache.poi.hssf.usermodel.HSSFFont;
10 import org.apache.poi.hssf.usermodel.HSSFHyperlink;
11 import org.apache.poi.hssf.usermodel.HSSFRow;
12 import org.apache.poi.hssf.usermodel.HSSFSheet;
13 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
14 import org.apache.poi.hssf.util.CellRangeAddress;
15 import org.apache.poi.hssf.util.HSSFColor;
16 
17 public class TestWrite {
18 
19         /**
20          * @param args
21          */
22         public static void main(String[] args) throws Exception {
23                 // 创建Excel的工作书册 Workbook,对应到一个excel文档
24                 HSSFWorkbook wb = new HSSFWorkbook();
25 
26                 // 创建Excel的工作sheet,对应到一个excel文档的tab
27                 HSSFSheet sheet = wb.createSheet("sheet1");
28 
29                 // 设置excel每列宽度
30                 sheet.setColumnWidth(0, 4000);
31                 sheet.setColumnWidth(1, 3500);
32 
33                 // 创建字体样式
34                 HSSFFont font = wb.createFont();
35                 font.setFontName("Verdana");
36                 font.setBoldweight((short) 100);
37                 font.setFontHeight((short) 300);
38                 font.setColor(HSSFColor.BLUE.index);
39 
40                 // 创建单元格样式
41                 HSSFCellStyle style = wb.createCellStyle();
42                 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
43                 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
44                 style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
45                 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
46 
47                 // 设置边框
48                 style.setBottomBorderColor(HSSFColor.RED.index);
49                 style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
50                 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
51                 style.setBorderRight(HSSFCellStyle.BORDER_THIN);
52                 style.setBorderTop(HSSFCellStyle.BORDER_THIN);
53 
54                 style.setFont(font);// 设置字体
55 
56                 // 创建Excel的sheet的一行
57                 HSSFRow row = sheet.createRow(0);
58                 row.setHeight((short) 500);// 设定行的高度
59                 // 创建一个Excel的单元格
60                 HSSFCell cell = row.createCell(0);
61 
62                 // 合并单元格(startRow,endRow,startColumn,endColumn)
63                 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
64 
65                 // 给Excel的单元格设置样式和赋值
66                 cell.setCellStyle(style);
67                 cell.setCellValue("hello world");
68 
69                 // 设置单元格内容格式
70                 HSSFCellStyle style1 = wb.createCellStyle();
71                 style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
72 
73                 style1.setWrapText(true);// 自动换行
74 
75                 row = sheet.createRow(1);
76 
77                 // 设置单元格的样式格式
78 
79                 cell = row.createCell(0);
80                 cell.setCellStyle(style1);
81                 cell.setCellValue(new Date());
82 
83                 // 创建超链接
84                 HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
85                 link.setAddress("http://www.baidu.com");
86                 cell = row.createCell(1);
87                 cell.setCellValue("百度");
88                 cell.setHyperlink(link);// 设定单元格的链接
89 
90                 FileOutputStream os = new FileOutputStream("e:\\workbook.xls");
91                 wb.write(os);
92                 os.close();
93 
94         }
95 
96 }

 

posted @ 2013-01-24 15:57  斌哥  阅读(36014)  评论(1编辑  收藏