Java 操作 Excel (读取Excel2003 2007，Poi实现)

原文地址：

http://boendev.iteye.com/blog/758690

Excel Java Apache 单元测试 FP

一. Apache POI 简介（ http://poi.apache.org/）

使用Java程序读写Microsoft Office，提供了下面这几种类型：

HSSF－提供读写Microsoft Excel XLS格式档案的功能。
XSSF－提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF－提供读写Microsoft Word DOC格式档案的功能。
HSLF－ 供读写Microsoft PowerPoint格式档案的功能。
HDGF－提供读Microsoft Visio格式档案的功能。
HPBF－提供读Microsoft Publisher格式档案的功能。


二、POI操作Excel


1. 官方快速帮助：http://poi.apache.org/spreadsheet/quick-guide.html

2. 导入包：poi-3.6.jar


参考：



1. http://www.blogjava.net/vwpolo/archive/2009/09/16/295243.html

2. http://hacker-zxf.iteye.com/blog/746546

3. http://zmx.iteye.com/blog/622536

4. http://canfly2010.iteye.com/blog/701726

5. http://blog.youkuaiyun.com/aerchi

Java代码

1. packageexcel.poi;
2. importjava.io.File;
3. importjava.io.FileInputStream;
4. importjava.io.FileOutputStream;
5. importjava.io.IOException;
6. importjava.io.InputStream;
7. importjava.util.Date;
8. importjava.util.Iterator;
10. importorg.apache.poi.POITextExtractor;
11. importorg.apache.poi.extractor.ExtractorFactory;
12. importorg.apache.poi.hssf.usermodel.HSSFCell;
13. importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
14. importorg.apache.poi.hssf.usermodel.HSSFDataFormat;
15. importorg.apache.poi.hssf.usermodel.HSSFRow;
16. importorg.apache.poi.hssf.usermodel.HSSFSheet;
17. importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
18. importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;
19. importorg.apache.poi.openxml4j.exceptions.OpenXML4JException;
20. importorg.apache.poi.poifs.filesystem.POIFSFileSystem;
21. importorg.apache.poi.ss.usermodel.IndexedColors;
22. importorg.apache.poi.xssf.usermodel.XSSFCell;
23. importorg.apache.poi.xssf.usermodel.XSSFCellStyle;
24. importorg.apache.poi.xssf.usermodel.XSSFCreationHelper;
25. importorg.apache.poi.xssf.usermodel.XSSFRow;
26. importorg.apache.poi.xssf.usermodel.XSSFSheet;
27. importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
28. importorg.apache.xmlbeans.XmlException;
29. publicclassReadExcel{
30. /**
31. *读取office2003xls
32. *@paramfilePath
33. */
34. @SuppressWarnings({"unchecked","deprecation"})
35. publicvoidloadXls(StringfilePath){
36. try{
37. InputStreaminput=newFileInputStream("D:\\aerchi\\test\\xls\\流量.xls");
38. POIFSFileSystemfs=newPOIFSFileSystem(input);
39. HSSFWorkbookwb=newHSSFWorkbook(fs);
40. HSSFSheetsheet=wb.getSheetAt(0);
41. //Iterateovereachrowinthesheet
42. Iteratorrows=sheet.rowIterator();
43. while(rows.hasNext()){
44. HSSFRowrow=(HSSFRow)rows.next();
45. System.out.println("Row#"+row.getRowNum());
46. //Iterateovereachcellintherowandprintoutthecell"s
47. //content
48. Iteratorcells=row.cellIterator();
49. while(cells.hasNext()){
50. HSSFCellcell=(HSSFCell)cells.next();
51. System.out.println("Cell#"+cell.getCellNum());
52. switch(cell.getCellType()){
53. caseHSSFCell.CELL_TYPE_NUMERIC:
54. System.out.println(cell.getNumericCellValue());
55. break;
56. caseHSSFCell.CELL_TYPE_STRING:
57. System.out.println(cell.getStringCellValue());
58. break;
59. caseHSSFCell.CELL_TYPE_BOOLEAN:
60. System.out.println(cell.getBooleanCellValue());
61. break;
62. caseHSSFCell.CELL_TYPE_FORMULA:
63. System.out.println(cell.getCellFormula());
64. break;
65. default:
66. System.out.println("unsuportedselltype");
67. break;
68. }
69. }
70. }
71. }catch(IOExceptionex){
72. ex.printStackTrace();
73. }
74. }
75. /**
76. *读取xlsx文本
77. *@paramfilePath
78. */
79. publicvoidloadXlsxText(StringfilePath){
80. FileinputFile=newFile("D:\\aerchi.xlsx");
81. try{
82. POITextExtractorextractor=ExtractorFactory.createExtractor(inputFile);
83. System.out.println(extractor.getText());
84. }catch(InvalidFormatExceptione){
85. e.printStackTrace();
86. }catch(IOExceptione){
87. e.printStackTrace();
88. }catch(OpenXML4JExceptione){
89. e.printStackTrace();
90. }catch(XmlExceptione){
91. e.printStackTrace();
92. }
93. }
94. /**
95. *读取office2007xlsx
96. *@paramfilePath
97. */
98. publicvoidloadXlsx(StringfilePath){
99. //构造XSSFWorkbook对象，strPath传入文件路径
100. XSSFWorkbookxwb=null;
101. try{
102. xwb=newXSSFWorkbook("D:\\aerchi.xlsx");
103. }catch(IOExceptione){
104. System.out.println("读取文件出错");
105. e.printStackTrace();
106. }
107. //读取第一章表格内容
108. XSSFSheetsheet=xwb.getSheetAt(0);
109. //定义row、cell
110. XSSFRowrow;
111. Stringcell;
112. //循环输出表格中的内容
113. for(inti=sheet.getFirstRowNum()+1;i<sheet.getPhysicalNumberOfRows();i++){
114. row=sheet.getRow(i);
115. for(intj=row.getFirstCellNum();j<row.getPhysicalNumberOfCells();j++){
116. //通过row.getCell(j).toString()获取单元格内容，
117. if(j==1&&i!=0){
118. cell=row.getCell(j).getDateCellValue().toLocaleString();
119. }else{
120. cell=row.getCell(j).toString();
121. }
122. /*//获取字体和背景颜色
123. StringrgbShort=row.getCell(j).getCellStyle().getFont().getCTFont().getColorArray()[0].xmlText();
124. rgbShort=ReadExcel.substringBetween(rgbShort,"rgb=\"","\"/>");
125. StringrgbShort=row.getCell(j).getCellStyle().getFillBackgroundXSSFColor().getCTColor().toString();
126. Colorcolor=newColor(Color.BLUE.getRGB());
127. System.out.print(cell+",index:"+rgbShort+"red:"+color.getRed()+"blue:"+color.getBlue()+"\t");*/
128. System.out.print(cell+"\t");
129. }
130. System.out.println("");
131. }
132. }
133. /**
134. *HSSF写入excelxls格式
135. *@paramfilePath
136. *@throwsIOException
137. */
138. publicvoidwriteXls(StringfilePath)throwsIOException{
139. //工作簿23.
140. HSSFWorkbookhssfworkbook=newHSSFWorkbook();
141. //创建sheet页25.
142. HSSFSheethssfsheet=hssfworkbook.createSheet();
143. //sheet名称
144. hssfworkbook.setSheetName(0,"研发部门");
145. //取得第一行29.
146. HSSFRowhssfrow=hssfsheet.createRow(0);
147. //创建第一个单元格并处理乱码31.
148. HSSFCellhssfcell_0=hssfrow.createCell((short)0);
149. //hssfcell_0.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
150. //对第一个单元格赋值34.
151. hssfcell_0.setCellValue("研发工程师1");
152. //日期单元格格式处理
153. HSSFCellStylehssfcellstyle=hssfworkbook.createCellStyle();
154. //m/d/yyh:mm39.
155. hssfcellstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
156. //创建第二个单元格41.
157. HSSFCellhssfcell_1=hssfrow.createCell((short)1);
158. hssfcell_1.setCellValue(newDate());
159. hssfcell_1.setCellStyle(hssfcellstyle);
160. hssfrow.createCell((short)2).setCellValue(true);
161. hssfrow.createCell((short)3).setCellValue(122.00);
162. //输出49.
163. FileOutputStreamfileoutputstream=newFileOutputStream("d:\\exceltext.xls");
164. hssfworkbook.write(fileoutputstream);
165. fileoutputstream.close();
166. }
167. @SuppressWarnings("static-access")
168. publicvoidwriteXlsx(StringfilePath)throwsIOException{
169. //工作簿
170. XSSFWorkbookhssfworkbook=newXSSFWorkbook();
171. //获得CreationHelper对象,这个应该是一个帮助类
172. XSSFCreationHelperhelper=hssfworkbook.getCreationHelper();
173. //创建sheet页
174. XSSFSheethssfsheet=hssfworkbook.createSheet();
175. //设置sheet名称
176. hssfworkbook.setSheetName(0,"我的测试sheet");
177. //取得第一行
178. XSSFRowfirstRow=hssfsheet.createRow(0);
179. //创建第一个单元格
180. XSSFCellhssfcell_0=firstRow.createCell(0);
181. //hssfcell_0.setEncoding(HSSFWorkbook.ENCODING_UTF_16);并处理乱码
182. //对第一个单元格赋值
183. hssfcell_0.setCellValue("名称");
184. //创建第二个单元格
185. XSSFCellhssfcell_1=firstRow.createCell(1);
186. hssfcell_1.setCellValue("创建日期");
187. //日期单元格格式处理
188. XSSFCellStyledateCellStyle=hssfworkbook.createCellStyle();
189. //m/d/yyh:mm设置日期格式
190. dateCellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-ddhh:mm:ss"));
191. dateCellStyle=ReadExcel.setFillBackgroundColors(dateCellStyle,IndexedColors.BLACK.getIndex(),IndexedColors.YELLOW.getIndex(),dateCellStyle.SOLID_FOREGROUND);
192. //设置其他标题
193. firstRow.createCell(2).setCellValue("用户");
194. firstRow.createCell(3).setCellValue("备注");
196. //写入所有内容行
197. for(introwInt=1;rowInt<10;rowInt++){
198. XSSFRowrow=hssfsheet.createRow(rowInt);
199. XSSFCellcell_0=row.createCell(0);
200. cell_0.setCellValue("碌人乘凉");
201. XSSFCellcell_1=row.createCell(1);
202. cell_1.setCellValue(newDate());
203. cell_1.setCellStyle(dateCellStyle);
204. XSSFCellcell_2=row.createCell(2);
205. cell_2.setCellValue("超级会员");
206. XSSFCellcell_3=row.createCell(3);
207. cell_3.setCellValue("这里是备注信息");
209. }
210. //输出49.
211. FileOutputStreamfileoutputstream=newFileOutputStream("d:\\exceltext.xlsx");
212. hssfworkbook.write(fileoutputstream);
213. fileoutputstream.close();
214. }
215. /**
216. *前景和背景填充的着色
217. *@paramcellStyle
218. *@parambgIndexedColors.ORANGE.getIndex();
219. *@paramfgIndexedColors.ORANGE.getIndex();
220. *@paramfpCellStyle.SOLID_FOREGROUND
221. *@return
222. */
223. publicstaticXSSFCellStylesetFillBackgroundColors(XSSFCellStylecellStyle,shortbg,shortfg,shortfp){
224. cellStyle.setFillBackgroundColor(bg);
225. cellStyle.setFillForegroundColor(fg);
226. cellStyle.setFillPattern(fp);
227. returncellStyle;
228. }
229. publicstaticvoidmain(String[]args){
230. ReadExcelreadExcel=newReadExcel();
231. /*try{
232. readExcel.writeXlsx("");
233. }catch(IOExceptione){
234. e.printStackTrace();
235. }*/
236. readExcel.loadXls("");
237. }
240. }

<wbr></wbr>