今天一个同学需要处理一个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 }