Java如何使用POI操作Excel(excel,java,poi,web开发)

Java使用POI操作Excel

1. POI操作Excel

1.1. 依赖

<!--操作excel--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.0</version></dependency>

1.2. 读取Excel

1.2.1. Excel文件内容

1.2.2. 代码

/***读取excel*/publicstaticvoidreadExcel(){InputStreaminputStream=null;XSSFWorkbookxssfWorkbook=null;try{Stringpast="/操作excel.xlsx";inputStream=newFileInputStream(past);xssfWorkbook=newXSSFWorkbook(inputStream);//获取sheet的个数intnumberOfSheets=xssfWorkbook.getNumberOfSheets();//获取指定的sheetSystem.out.println(numberOfSheets);//通过指定名称获取XSSFSheetsheet=xssfWorkbook.getSheet("笔记本");//通过下标获取XSSFSheetsheetAt=xssfWorkbook.getSheetAt(1);if(sheetAt!=null){//最后一行有数据的intlastRowNum=sheetAt.getLastRowNum();XSSFRowrow;shortlastCellNum;XSSFCellcell;for(inti=0;i<=lastRowNum;i++){//获取指定行row=sheetAt.getRow(i);if(row==null){continue;}//最后一列有数据的lastCellNum=row.getLastCellNum();for(intj=0;j<=lastCellNum;j++){cell=row.getCell(j);if(cell==null){continue;}//数据类型CellTypecellType=cell.getCellType();//字符串if(CellType.STRING==cellType){System.out.println(cell.toString());}//数字elseif(CellType.NUMERIC==cellType){try{System.out.println(cell.getDateCellValue());}catch(Exceptione){System.out.println(cell.toString());}}//&hellip;&hellip;else{System.out.println(cell.toString());}}}}}catch(Exceptione){e.printStackTrace();}finally{if(inputStream!=null){try{inputStream.close();}catch(IOExceptione){e.printStackTrace();}}}}

1.2.3. 控制台输出结果

2便签名称便签分类创建时间创建人拥有人小明的便签学习便签TueSep0300:00:00CST2019小明小明小明的个人便签个人便签SunSep0800:00:00CST2019小明小明

1.3. 生成excel

1.3.1. 代码

/***生成excel*/publicstaticvoidcreatExcel(){XSSFWorkbookxssfWorkbook=newXSSFWorkbook();//创建一个sheetXSSFSheetsheet1=xssfWorkbook.createSheet("第一个新建的sheet");//设置高度和宽度，也可以每行每列单独分开设置//参数为字符个数sheet1.setDefaultColumnWidth(20);sheet1.setDefaultRowHeight((short)(33*20));//第二个参数为字符宽度的1/256sheet1.setColumnWidth(5,30*256);//设置单元格样式XSSFCellStylecellStyle=xssfWorkbook.createCellStyle();//字体样式FontfontStyle=xssfWorkbook.createFont();fontStyle.setBold(true);//字体fontStyle.setFontName("等线");//大小fontStyle.setFontHeightInPoints((short)11);//将字体样式添加到单元格样式中cellStyle.setFont(fontStyle);//水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置单元格填充色DefaultIndexedColorMapdefaultIndexedColorMap=newDefaultIndexedColorMap();XSSFColorclr=newXSSFColor(defaultIndexedColorMap);byte[]bytes={(byte)217,(byte)217,(byte)217};clr.setRGB(bytes);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setFillForegroundColor(clr);//设置单元格不为锁定，可编辑，反是用了这个样式的都可编辑cellStyle.setLocked(false);//锁定整个sheet不可编辑sheet1.protectSheet("1231312");//创建一行数据XSSFRowrow;XSSFCellcell;row=sheet1.createRow(0);cell=row.createCell(0);//设值cell.setCellValue("2");//合并单元格CellRangeAddresscra=newCellRangeAddress(1,1,0,3);//起始行,终止行,起始列,终止列sheet1.addMergedRegion(cra);//设置合并单元格的样式//使用RegionUtil类为合并后的单元格添加边框//下边框RegionUtil.setBorderBottom(BorderStyle.MEDIUM_DASHED,cra,sheet1);//左边框RegionUtil.setBorderLeft(BorderStyle.MEDIUM_DASHED,cra,sheet1);row=sheet1.getRow(1);//设置合并单元格内的文本样式//但这个单元格的边框样式会覆盖上面设置的合并单元格的样式CellUtil.getCell(row,0).setCellStyle(cellStyle);//设置单个单元格的样式row=sheet1.createRow(2);cell=row.createCell(0);cell.setCellStyle(cellStyle);//设置数据校验//序列校验String[]strArray={"星期一","星期二","星期三"};XSSFDataValidationHelperdvHelper=newXSSFDataValidationHelper((XSSFSheet)sheet1);XSSFDataValidationConstraintdvConstraint=(XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint(strArray);CellRangeAddressListaddressList=newCellRangeAddressList(3,3,0,2);XSSFDataValidationvalidation=(XSSFDataValidation)dvHelper.createValidation(dvConstraint,addressList);//显示报错提示框validation.setShowErrorBox(true);validation.createErrorBox("错误提示","只能选择指定的内容！");//设置单元格右侧显示剪头符号,显示可用的选项，默认为truevalidation.setSuppressDropDownArrow(true);//显示提示信息validation.setShowPromptBox(true);validation.createPromptBox("提示信息","请选择星期填入！");sheet1.addValidationData(validation);//保护工作薄不可被修改xssfWorkbook.lockStructure();//这个不知道有啥用xssfWorkbook.lockRevision();//锁定excel的窗口大小，不能无限制的横向，纵向拉伸。xssfWorkbook.lockWindows();xssfWorkbook.createSheet("第二个人sheet");OutputStreamoutputStream=null;try{outputStream=newFileOutputStream("/创建excel.xlsx");xssfWorkbook.write(outputStream);outputStream.flush();}catch(FileNotFoundExceptione){e.printStackTrace();}catch(IOExceptione){e.printStackTrace();}finally{if(outputStream!=null){try{outputStream.close();}catch(IOExceptione){e.printStackTrace();}}}}

1.3.2. 生成excel文件内容