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創(chuàng)建和讀取2003-2007版本Excel文件*/publicclassCreatAndReadExcelpublicstaticvoidmain(Stringargs)throwsExceptioncreat2003Excel();/創(chuàng)建2007版Excel文件creat2007Excel();/創(chuàng)建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();創(chuàng)建2007版Excel文件*throwsFileNotFoundException*throwsIOException*/privatestaticvoidcreat2007Excel()throwsFileNotFoundException,IOException/HSSFWorkbookworkBook=newHSSFWorkbook();/創(chuàng)建一個excel文檔對象XSSFWorkbookworkBook=newXSSFWorkbook();XSSFSheetsheet=workBook.createSheet();/創(chuàng)建一個工作薄對象sh

8、eet.setColumnWidth(1,10000);/設(shè)置第二列的寬度為XSSFRowrow=sheet.createRow(1);/創(chuàng)建一個行對象row.setHeightInPoints(23);/設(shè)置行高23像素XSSFCellStylestyle=workBook.createCellStyle();/創(chuàng)建樣式對象/設(shè)置字體XSSFFontfont=workBook.createFont();/創(chuàng)建字體對象font.setFontHeightInPoints(short)15);/設(shè)置字體大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

9、/設(shè)置粗體font.setFontName("黑體");/設(shè)置為黑體字style.setFont(font);/將字體加入到樣式對象/設(shè)置對齊方式style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);水平居中style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);/垂直居中/設(shè)置邊框style.setBorderTop(HSSFCellStyle.BORDER_THICK);/頂部邊框粗線style.setTopBorderColor(HSSFColor.

10、RED.index);/設(shè)置為紅色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);/創(chuàng)建

11、單元格cell.setCellValue(newDate();/寫入當前日期cell.setCellStyle(style);/應(yīng)用樣式對象/文件輸出流FileOutputStreamos=newFileOutputStream("style_2007.xlsx");workBook.write(os);/將文檔對象寫入文件輸出流os.close();/關(guān)閉文件輸出流System.out.println("創(chuàng)建成功office2007excel");/*創(chuàng)建2003版本的Excel文件*/privatestaticvoidcreat2003Excel()

12、throwsFileNotFoundException,IOExceptionHSSFWorkbookworkBook=newHSSFWorkbook();/創(chuàng)建一個excel文檔對象HSSFSheetsheet=workBook.createSheet();/創(chuàng)建一個工作薄對象sheet.setColumnWidth(1,10000);/設(shè)置第二列的寬度為HSSFRowrow=sheet.createRow(1);/創(chuàng)建一個行對象row.setHeightInPoints(23);/設(shè)置行高23像素HSSFCellStylestyle=workBook.createCellStyle();/

13、創(chuàng)建樣式對象/設(shè)置字體HSSFFontfont=workBook.createFont();/創(chuàng)建字體對象font.setFontHeightInPoints(short)15);/設(shè)置字體大小font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);/設(shè)置粗體font.setFontName("黑體");/設(shè)置為黑體字style.setFont(font);/將字體加入到樣式對象/設(shè)置對齊方式style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);水平居中style.setVerti

14、calAlignment(HSSFCellStyle.VERTICAL_CENTER);/垂直居中/設(shè)置邊框style.setBorderTop(HSSFCellStyle.BORDER_THICK);/頂部邊框粗線style.setTopBorderColor(HSSFColor.RED.index);/設(shè)置為紅色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);/創(chuàng)建單元格cell.setCellValue(newDate();/寫入當前日期cell.setCellStyle(style);/應(yīng)用樣式對象/文件輸出流FileOutputStreamos=newFileOutputStream("style_2003.xls");workBook.write(

16、os);/將文檔對象寫入文件輸出流os.close();/關(guān)閉文件輸出流System.out.println("創(chuàng)建成功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內(nèi)容如下：");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");/格式化數(shù)字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);/構(gòu)造XSSFWorkbook對象，strPath傳入文件路徑XSSFWorkbookxwb=newXSSFWorkbook(newFileInputStream(file);/讀取第一章表格內(nèi)容XSSFSheetsheet=xwb.getSheetAt(0);Objectvalue=null;XSSFRowrow=null;XSSFCellcell=null;System.out.println("

27、;讀取office2007excel內(nèi)容如下：");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");/格式化數(shù)字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