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

《JAVA用POI读取和创建2003和2007版本Excel完美示例》由会员分享，可在线阅读，更多相关《JAVA用POI读取和创建2003和2007版本Excel完美示例（9页珍藏版）》请在装配图网上搜索。

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;/*可以从http:/poi.apache.org/这里下载到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();/读取2007Excel文件Stringpath2007=System.getProperty

6、(user.dir)+System.getProperty(file.separator)+style_2007.xlsx;/获取项目文件路径+2007版文件名System.out.println(路径：+path2007);Filef2007=newFile(path2007);tryreadExcel(f2007);catch(IOExceptione)/TODOAuto-generatedcatchblocke.printStackTrace();创建2007版Excel文件*throwsFileNotFoundException*throwsIOException*/privatest

7、aticvoidcreat2007Excel()throwsFileNotFoundException,IOException/HSSFWorkbookworkBook=newHSSFWorkbook();/创建一个excel文档对象XSSFWorkbookworkBook=newXSSFWorkbook();XSSFSheetsheet=workBook.createSheet();/创建一个工作薄对象sheet.setColumnWidth(1,10000);/设置第二列的宽度为XSSFRowrow=sheet.createRow(1);/创建一个行对象row.setHeightInPoi

8、nts(23);/设置行高23像素XSSFCellStylestyle=workBook.createCellStyle();/创建样式对象/设置字体XSSFFontfont=workBook.createFont();/创建字体对象font.setFontHeightInPoints(short)15);/设置字体大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);/设置粗体font.setFontName(黑体);/设置为黑体字style.setFont(font);/将字体加入到样式对象/设置对齐方式style.setAlignment(HSSF

9、CellStyle.ALIGN_CENTER_SELECTION);水平居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);/垂直居中/设置边框style.setBorderTop(HSSFCellStyle.BORDER_THICK);/顶部边框粗线style.setTopBorderColor(HSSFColor.RED.index);/设置为红色style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);/底部边框双线style.setBorderLeft(HSSFCellSty

10、le.BORDER_MEDIUM);/左边边框style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);/右边边框/格式化日期style.setDataFormat(HSSFDataFormat.getBuiltinFormat(m/d/yyh:mm);XSSFCellcell=row.createCell(1);/创建单元格cell.setCellValue(newDate();/写入当前日期cell.setCellStyle(style);/应用样式对象/文件输出流FileOutputStreamos=newFileOutputStream(sty

11、le_2007.xlsx);workBook.write(os);/将文档对象写入文件输出流os.close();/关闭文件输出流System.out.println(创建成功office2007excel);/*创建2003版本的Excel文件*/privatestaticvoidcreat2003Excel()throwsFileNotFoundException,IOExceptionHSSFWorkbookworkBook=newHSSFWorkbook();/创建一个excel文档对象HSSFSheetsheet=workBook.createSheet();/创建一个工作薄对象sh

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

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

14、设置为红色style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);/底部边框双线style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);/左边边框style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);/右边边框/格式化日期style.setDataFormat(HSSFDataFormat.getBuiltinFormat(m/d/yyh:mm);HSSFCellcell=row.createCell(1);/创建单元格cell.setCellValue(new

15、Date();/写入当前日期cell.setCellStyle(style);/应用样式对象/文件输出流FileOutputStreamos=newFileOutputStream(style_2003.xls);workBook.write(os);/将文档对象写入文件输出流os.close();/关闭文件输出流System.out.println(创建成功office2003excel);/*对外提供读取excel的方法*/publicstaticListListreadExcel(Filefile)throwsIOExceptionStringfileName=file.getName(

16、);Stringextension=fileName.lastIndexOf(.)=-1?:fileName.substring(fileName.lastIndexOf(.)+1);if(xls.equals(extension)returnread2003Excel(file);elseif(xlsx.equals(extension)returnread2007Excel(file);elsethrownewIOException(不支持的文件类型);读取office2003excel*throwsIOException*throwsFileNotFoundException*/priv

17、atestaticListListread2003Excel(Filefile)throwsIOExceptionListListlist=newLinkedListList();HSSFWorkbookhwb=newHSSFWorkbook(newFileInputStream(file);HSSFSheetsheet=hwb.getSheetAt(0);Objectvalue=null;HSSFRowrow=null;HSSFCellcell=null;System.out.println(读取office2003excel内容如下：);for(inti=sheet.getFirstRow

18、Num();i=sheet.getPhysicalNumberOfRows();i+)row=sheet.getRow(i);if(row=null)continue;Listlinked=newLinkedList();for(intj=row.getFirstCellNum();j=row.getLastCellNum();j+)cell=row.getCell(j);if(cell=null)continue;DecimalFormatdf=newDecimalFormat(0);/格式化numberString/字符SimpleDateFormatsdf=newSimpleDateFo

19、rmat(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/+列is

20、Numbertype;DateFormt:/+cell.getCellStyle().getDataFormatString();if(.equals(cell.getCellStyle().getDataFormatString()value=df.format(cell.getNumericCellValue();elseif(General.equals(cell.getCellStyle().getDataFormatString()value=nf.format(cell.getNumericCellValue();elsevalue=sdf.format(HSSFDateUtil.

21、getJavaDate(cell.getNumericCellValue();System.out.print(+value+);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);value=;System.out.prin

22、t(+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.out.println();list.add(linked);returnlist;*读取Office2007excel*/privatestaticListListread2007Excel(Filefile)throwsIOException

23、ListListlist=newLinkedListList();/Stringpath=System.getProperty(user.dir)+/System.getProperty(file.separator)+dd.xlsx;/System.out.println(路径：+path);/构造XSSFWorkbook对象，strPath传入文件路径XSSFWorkbookxwb=newXSSFWorkbook(newFileInputStream(file);/读取第一章表格内容XSSFSheetsheet=xwb.getSheetAt(0);Objectvalue=null;XSSF

24、Rowrow=null;XSSFCellcell=null;System.out.println(读取office2007excel内容如下：);for(inti=sheet.getFirstRowNum();i=sheet.getPhysicalNumberOfRows();i+)row=sheet.getRow(i);if(row=null)continue;Listlinked=newLinkedList();for(intj=row.getFirstCellNum();j=row.getLastCellNum();j+)cell=row.getCell(j);if(cell=null)

25、continue;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.getStringCellVa

26、lue();System.out.print(+value+);break;caseXSSFCell.CELL_TYPE_NUMERIC:/System.out.println(i+行+j/+列isNumbertype;DateFormt:/+cell.getCellStyle().getDataFormatString();if(.equals(cell.getCellStyle().getDataFormatString()value=df.format(cell.getNumericCellValue();elseif(General.equals(cell.getCellStyle()

27、.getDataFormatString()value=nf.format(cell.getNumericCellValue();elsevalue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue();System.out.print(+value+);break;caseXSSFCell.CELL_TYPE_BOOLEAN:/System.out.println(i+行+j+列isBooleantype);value=cell.getBooleanCellValue();System.out.print(+value+

28、);break;caseXSSFCell.CELL_TYPE_BLANK:/System.out.println(i+行+j+列isBlanktype);value=;/System.out.println(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.out.println();list.add(linked);returnlist;