JAVA用POI读取和创建2003和2007版本Excel完美示例

1、importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.LinkedList;importjava.util.List;importorg.apache.poi.hssf.

2、usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFDataFormat;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFFont;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSh

3、eet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFCellStyle;importorg.apache.poi.xssf.usermodel.XSSFFont;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.x

4、ssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;/*可以从/这里下载到POI的jar包POI创建和读取2003-2007版本Excel文件*/publicclassCreatAndReadExcelpublicstaticvoidmain(Stringargs)throwsExceptioncreat2003Excel();/创建2007版Excel文件creat2007Excel();/创建2003版Excel文件/读取2003Excel文件获取项目文件路

5、Stringpath2003=System.getProperty("user.dir")+System.getProperty("file.separator")+"style_2003.xls"/+2003版文件名System.out.println("路径："+path2003);Filef2003=newFile(path2003);tryreadExcel(f2003);catch(IOExceptione)/TODOAuto-generatedcatchblocke.printStackTrace();

6、/读取2007Excel文件Stringpath2007=System.getProperty("user.dir")+System.getProperty("file.separator")+"style_2007.xlsx"/获取项目文件路径+2007版文件名System.out.println("路径："+path2007);Filef2007=newFile(path2007);tryreadExcel(f2007);catch(IOExceptione)/TODOAuto-generatedcatchbl

7、ocke.printStackTrace();创建2007版Excel文件*throwsFileNotFoundException*throwsIOException*/privatestaticvoidcreat2007Excel()throwsFileNotFoundException,IOException/HSSFWorkbookworkBook=newHSSFWorkbook();/创建一个excel文档对象XSSFWorkbookworkBook=newXSSFWorkbook();XSSFSheetsheet=workBook.createSheet();/创建一个工作薄对象sh

8、eet.setColumnWidth(1,10000);/设置第二列的宽度为XSSFRowrow=sheet.createRow(1);/创建一个行对象row.setHeightInPoints(23);/设置行高23像素XSSFCellStylestyle=workBook.createCellStyle();/创建样式对象/设置字体XSSFFontfont=workBook.createFont();/创建字体对象font.setFontHeightInPoints(short)15);/设置字体大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

9、/设置粗体font.setFontName("黑体");/设置为黑体字style.setFont(font);/将字体加入到样式对象/设置对齐方式style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);水平居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);/垂直居中/设置边框style.setBorderTop(HSSFCellStyle.BORDER_THICK);/顶部边框粗线style.setTopBorderColor(HSSFColor.

10、RED.index);/设置为红色style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);/底部边框双线style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);/左边边框style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);/右边边框/格式化日期style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yyh:mm");XSSFCellcell=row.createCell(1);/创建

11、单元格cell.setCellValue(newDate();/写入当前日期cell.setCellStyle(style);/应用样式对象/文件输出流FileOutputStreamos=newFileOutputStream("style_2007.xlsx");workBook.write(os);/将文档对象写入文件输出流os.close();/关闭文件输出流System.out.println("创建成功office2007excel");/*创建2003版本的Excel文件*/privatestaticvoidcreat2003Excel()

12、throwsFileNotFoundException,IOExceptionHSSFWorkbookworkBook=newHSSFWorkbook();/创建一个excel文档对象HSSFSheetsheet=workBook.createSheet();/创建一个工作薄对象sheet.setColumnWidth(1,10000);/设置第二列的宽度为HSSFRowrow=sheet.createRow(1);/创建一个行对象row.setHeightInPoints(23);/设置行高23像素HSSFCellStylestyle=workBook.createCellStyle();/

13、创建样式对象/设置字体HSSFFontfont=workBook.createFont();/创建字体对象font.setFontHeightInPoints(short)15);/设置字体大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);/设置粗体font.setFontName("黑体");/设置为黑体字style.setFont(font);/将字体加入到样式对象/设置对齐方式style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);水平居中style.setVerti

14、calAlignment(HSSFCellStyle.VERTICAL_CENTER);/垂直居中/设置边框style.setBorderTop(HSSFCellStyle.BORDER_THICK);/顶部边框粗线style.setTopBorderColor(HSSFColor.RED.index);/设置为红色style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);/底部边框双线style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);/左边边框style.setBorderRight(HSSFCell

15、Style.BORDER_MEDIUM);/右边边框/格式化日期style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yyh:mm");HSSFCellcell=row.createCell(1);/创建单元格cell.setCellValue(newDate();/写入当前日期cell.setCellStyle(style);/应用样式对象/文件输出流FileOutputStreamos=newFileOutputStream("style_2003.xls");workBook.write(

16、os);/将文档对象写入文件输出流os.close();/关闭文件输出流System.out.println("创建成功office2003excel");/*对外提供读取excel的方法*/publicstaticList<List<Object>>readExcel(Filefile)throwsIOExceptionStringfileName=file.getName();Stringextension=fileName.lastIndexOf(".")=-1?"":fileName.substring

17、(fileName.lastIndexOf(".")+1);if("xls".equals(extension)returnread2003Excel(file);elseif("xlsx".equals(extension)returnread2007Excel(file);elsethrownewIOException("不支持的文件类型");读取office2003excel*throwsIOException*throwsFileNotFoundException*/privatestaticList<

18、;List<Object>>read2003Excel(Filefile)throwsIOExceptionList<List<Object>>list=newLinkedList<List<Object>>();HSSFWorkbookhwb=newHSSFWorkbook(newFileInputStream(file);HSSFSheetsheet=hwb.getSheetAt(0);Objectvalue=null;HSSFRowrow=null;HSSFCellcell=null;System.out.println(

19、"读取office2003excel内容如下：");for(inti=sheet.getFirstRowNum();i<=sheet.getPhysicalNumberOfRows();i+)row=sheet.getRow(i);if(row=null)continue;List<Object>linked=newLinkedList<Object>();for(intj=row.getFirstCellNum();j<=row.getLastCellNum();j+)cell=row.getCell(j);if(cell=null)c

20、ontinue;DecimalFormatdf=newDecimalFormat("0");/格式化numberString/字符SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss");/格式化日期字符串DecimalFormatnf=newDecimalFormat("0.00");/格式化数字switch(cell.getCellType()caseXSSFCell.CELL_TYPE_STRING:/System.out.println(i+"行&qu

21、ot;+j+"列isStringtype");value=cell.getStringCellValue();System.out.print(""+value+"");break;caseXSSFCell.CELL_TYPE_NUMERIC:/System.out.println(i+"行"+j/+"列isNumbertype;DateFormt:"/+cell.getCellStyle().getDataFormatString();if("".equals(cell.g

22、etCellStyle().getDataFormatString()value=df.format(cell.getNumericCellValue();elseif("General".equals(cell.getCellStyle().getDataFormatString()value=nf.format(cell.getNumericCellValue();elsevalue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue();System.out.print(""+v

23、alue+"");break;caseXSSFCell.CELL_TYPE_BOOLEAN:/System.out.println(i+"行"+j+"列isBooleantype");value=cell.getBooleanCellValue();System.out.print(""+value+"");break;caseXSSFCell.CELL_TYPE_BLANK:/System.out.println(i+"行"+j+"列isBlanktype&quo

24、t;);value=""System.out.print(""+value+"");break;default:/System.out.println(i+"行"+j+"列isdefaulttype");value=cell.toString();System.out.print(""+value+"");if(value=null|"".equals(value)continue;linked.add(value);System.ou

25、t.println("");list.add(linked);returnlist;*读取Office2007excel*/privatestaticList<List<Object>>read2007Excel(Filefile)throwsIOExceptionList<List<Object>>list=newLinkedList<List<Object>>();/Stringpath=System.getProperty("user.dir")+/System.getProp

26、erty("file.separator")+"dd.xlsx"/System.out.println("路径："+path);/构造XSSFWorkbook对象，strPath传入文件路径XSSFWorkbookxwb=newXSSFWorkbook(newFileInputStream(file);/读取第一章表格内容XSSFSheetsheet=xwb.getSheetAt(0);Objectvalue=null;XSSFRowrow=null;XSSFCellcell=null;System.out.println("

27、;读取office2007excel内容如下：");for(inti=sheet.getFirstRowNum();i<=sheet.getPhysicalNumberOfRows();i+)row=sheet.getRow(i);if(row=null)continue;List<Object>linked=newLinkedList<Object>();for(intj=row.getFirstCellNum();j<=row.getLastCellNum();j+)cell=row.getCell(j);if(cell=null)contin

28、ue;DecimalFormatdf=newDecimalFormat("0");/格式化numberString/字符SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss");/格式化日期字符串DecimalFormatnf=newDecimalFormat("0.00");/格式化数字switch(cell.getCellType()caseXSSFCell.CELL_TYPE_STRING:/System.out.println(i+"行"+j+"列isStringtype");value=cell.getStringCellValue();System.out.print(""+value+"");break;caseXSSFCell.CELL_TYPE_NUMERIC:/System.out.println(i+"行"+j/+"列isNumbertype;DateFormt:"/+cell.getCellStyle().getDataFormatStrin