Java读写Excel实例分享
话不多说,请看代码:
ExcelUtil.java
packagepers.kangxu.datautils.utils; importjava.io.File; importjava.io.FileInputStream; importjava.io.FileOutputStream; importjava.io.IOException; importjava.io.InputStream; importjava.util.ArrayList; importjava.util.HashMap; importjava.util.Iterator; importjava.util.List; importjava.util.Map; importorg.apache.poi.hssf.usermodel.HSSFCellStyle; importorg.apache.poi.hssf.usermodel.HSSFFont; importorg.apache.poi.hssf.usermodel.HSSFWorkbook; importorg.apache.poi.ss.usermodel.Cell; importorg.apache.poi.ss.usermodel.CellStyle; importorg.apache.poi.ss.usermodel.CellValue; importorg.apache.poi.ss.usermodel.Font; importorg.apache.poi.ss.usermodel.FormulaEvaluator; importorg.apache.poi.ss.usermodel.Row; importorg.apache.poi.ss.usermodel.Sheet; importorg.apache.poi.ss.usermodel.Workbook; importorg.apache.poi.ss.util.CellRangeAddress; /** * *<b> *excel工具 *</b> *@authorkangxu * */ publicclassExcelUtil{ /** *导出excel *@paramfilePath文件全路径 *@paramsheetNamesheet页名称 *@paramsheetIndex当前sheet下表从0开始 *@paramfileHeader头部 *@paramdatas内容 */ publicstaticvoidwriteExcel(StringfilePath,StringsheetName, intsheetIndex, String[]fileHeader, List<String[]>datas){ //创建工作簿 Workbookwb=newHSSFWorkbook(); //创建工作表sheet Sheets=wb.createSheet(); wb.setSheetName(sheetIndex,sheetName); Rowr=s.createRow(0); Cellc=null; Fontfont=null; CellStylestyleHeader=null; CellStylestyleContent=null; //粗体 font=wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置头样式 styleHeader=wb.createCellStyle(); styleHeader.setFont(font); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框 styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //设置内容样式 styleContent=wb.createCellStyle(); styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框 styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 //设置头 for(inti=0;i<fileHeader.length;){ c=r.createCell(i); c.setCellStyle(styleHeader); c.setCellValue(fileHeader[i]); i++; } //设置内容 for(intrownum=0;rownum<datas.size();){//行rowdatas.size() r=s.createRow(rownum+1);//创建行 for(intcellnum=0;cellnum<fileHeader.length;){ c=r.createCell(cellnum); c.setCellValue(datas.get(rownum)[cellnum]); c.setCellStyle(styleContent); cellnum++; } rownum++; } FileOutputStreamout=null; try{ //创建文件或者文件夹,将内容写进去 if(FileUtil.createFile(newFile(filePath))){ out=newFileOutputStream(filePath); wb.write(out); } }catch(Exceptione){ e.printStackTrace(); }finally{ try{ //关闭流 if(out!=null){ out.flush(); out.close(); } }catch(IOExceptione){ e.printStackTrace(); } } } /** *读取excel文件内容 *@paramfilePath *@paramsheetIndex */ publicstaticList<Map<String,String>>readExcel(StringfilePath,intsheetIndex){ List<Map<String,String>>mapList=newArrayList<Map<String,String>>(); //头 List<String>list=newArrayList<String>(); // intcnt=0; intidx=0; try{ InputStreaminput=newFileInputStream(filePath);//建立输入流 Workbookwb=null; wb=newHSSFWorkbook(input); //获取sheet页 Sheetsheet=wb.getSheetAt(sheetIndex); Iterator<Row>rows=sheet.rowIterator(); while(rows.hasNext()){ Rowrow=rows.next(); Iterator<Cell>cells=row.cellIterator(); Map<String,String>map=newHashMap<String,String>(); if(cnt==0){//将头放进list中 while(cells.hasNext()){ Cellcell=cells.next(); if(isContainMergeCell(sheet)){ cancelMergeCell(sheet); } list.add(getStringCellValue(cell)); } cnt++; continue; }else{ while(cells.hasNext()){ Cellcell=cells.next(); if(isContainMergeCell(sheet)){ cancelMergeCell(sheet); } //区别相同的头 list=ListUtil.changeSameVal(list); map.put(list.get(idx++),getStringCellValue(cell)); } } idx=0; mapList.add(map); } returnmapList; }catch(IOExceptionex){ ex.printStackTrace(); } returnnull; } /** *合并单元格 *@paramsheet当前sheet页 *@paramfirstRow开始行 *@paramlastRow结束行 *@paramfirstCol开始列 *@paramlastCol结束列 */ publicstaticintmergeCell(Sheetsheet,intfirstRow,intlastRow,intfirstCol,intlastCol){ if(sheet==null){ return-1; } returnsheet.addMergedRegion(newCellRangeAddress(firstRow,lastRow,firstCol,lastCol)); } /** *取消合并单元格 *@paramsheet *@paramidx */ publicstaticvoidcancelMergeCell(Sheetsheet){ intsheetMergeCount=sheet.getNumMergedRegions(); for(intidx=0;idx<sheetMergeCount;){ CellRangeAddressrange=sheet.getMergedRegion(idx); Stringval=getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow()); //取消合并单元格 sheet.removeMergedRegion(idx); for(intrownum=range.getFirstRow();rownum<range.getLastRow()+1;){ for(intcellnum=range.getFirstColumn();cellnum<range.getLastColumn()+1;){ sheet.getRow(rownum).getCell(cellnum).setCellValue(val); cellnum++; } rownum++; } idx++; } } /** *判断指定单元格是否是合并单元格 *@paramsheet当前sheet页 *@paramfirstRow开始行 *@paramlastRow结束行 *@paramfirstCol开始列 *@paramlastCol结束列 *@return */ publicstaticbooleanisMergeCell(Sheetsheet, introw,intcolumn){ intsheetMergeCount=sheet.getNumMergedRegions(); for(inti=0;i<sheetMergeCount;){ CellRangeAddressrange=sheet.getMergedRegion(i); intfirstColumn=range.getFirstColumn(); intlastColumn=range.getLastColumn(); intfirstRow=range.getFirstRow(); intlastRow=range.getLastRow(); if(row>=firstRow&&row<=lastRow){ if(column>=firstColumn&&column<=lastColumn){ returntrue; } } i++; } returnfalse; } /** *判断sheet页中是否含有合并单元格 *@paramsheet *@return */ publicstaticbooleanisContainMergeCell(Sheetsheet){ if(sheet==null){ returnfalse; } returnsheet.getNumMergedRegions()>0?true:false; } /** *获取指定合并单元的值 *@paramsheet *@paramrow *@paramcolumn *@return */ publicstaticStringgetMergeCellValue(Sheetsheet, introw,intcolumn){ intsheetMergeCount=sheet.getNumMergedRegions(); for(inti=0;i<sheetMergeCount;){ CellRangeAddressrange=sheet.getMergedRegion(i); intfirstColumn=range.getFirstColumn(); intlastColumn=range.getLastColumn(); intfirstRow=range.getFirstRow(); intlastRow=range.getLastRow(); if(row>=firstRow&&row<=lastRow){ if(column>=firstColumn&&column<=lastColumn){ RowfRow=sheet.getRow(firstRow); CellfCell=fRow.getCell(firstColumn); returngetStringCellValue(fCell); } } i++; } returnnull; } /** *获取单元格的值 *@paramcell *@return */ publicstaticStringgetStringCellValue(Cellcell){ StringstrCell=""; if(cell==null)returnstrCell; switch(cell.getCellType()){ caseCell.CELL_TYPE_STRING: strCell=cell.getRichStringCellValue().getString().trim(); break; caseCell.CELL_TYPE_NUMERIC: strCell=String.valueOf(cell.getNumericCellValue()); break; caseCell.CELL_TYPE_BOOLEAN: strCell=String.valueOf(cell.getBooleanCellValue()); break; caseCell.CELL_TYPE_FORMULA: FormulaEvaluatorevaluator=cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator(); evaluator.evaluateFormulaCell(cell); CellValuecellValue=evaluator.evaluate(cell); strCell=String.valueOf(cellValue.getNumberValue()); break; default: strCell=""; } returnstrCell; } }
调用方式如下
ExcelUtilTester.java
packagepers.kangxu.datautils.test; importjava.util.ArrayList; importjava.util.List; importpers.kangxu.datautils.utils.ExcelUtil; publicclassExcelUtilTester{ publicstaticvoidmain(String[]args){ List<String[]>datas=newArrayList<String[]>(); datas.add(newString[]{"狗熊","母","250"}); datas.add(newString[]{"猪粮","不明","251"}); //ExcelUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls","sheet1",0,newString[]{"姓名","年龄","性别"},datas); System.out.println(ExcelUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test\\test\\test.xls",0)); } }
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持毛票票!