POI操作Excel总结
POI操作Excel总结，如下：
1、导入jar包: [ poi-3.6.jar , poi-ooxml-3.6.jar , poi-ooxml-schemas-3.6.jar ]
如果使用了maven，则在pom里面添加如下依赖即可：
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.6</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.6</version> </dependency>
import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Cell; public void poiMethod{ File file = new File("c:/example.xls"); FileInputStream fileInputStream = new FileInputStream(file); Workbook book = WorkbookFactory.create(fileInputStream); int sheetNum = book.getNumberOfSheets();//取得sheet数 Sheet[] sheets = new Sheet[sheetNum]; for (int i = 0; i < sheetNum; i++) { sheets[i] = book.getSheetAt(i); } Sheet sheet = sheets[0];//取得第一个sheet Row row = sh.getRow(0);//取得第一行 short cellNum = row.getLastCellNum();//取得该行的列数 for(int i=0;i<cellNum;i++){ Cell cell = row.getCell(i);//取得row行i列的cell getValueByCell(cell)//根据cell取得cell的值 } } /** * 根据cell取得cell的值 * * @param cell * @return */ private String getValueByCell(Cell cell) { if (cell == null) { return null; } int cellType = cell.getCellType(); try { if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); return sdf.format(date); } } catch (Exception e) {} if (cellType == Cell.CELL_TYPE_BLANK) { return cell.getStringCellValue(); } else if (cellType == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cellType == Cell.CELL_TYPE_ERROR) { return String.valueOf(cell.getErrorCellValue()); } else if (cellType == Cell.CELL_TYPE_FORMULA) { return cell.getStringCellValue(); } else if (cellType == Cell.CELL_TYPE_NUMERIC) { java.text.DecimalFormat formatter = new java.text.DecimalFormat("##########"); return formatter.format(cell.getNumericCellValue()); } else if (cellType == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } return null; } import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet sheet; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFPalette public void poiMethod(){ Workbook workbook = new HSSFWorkbook(); //设置背景色 HSSFPalette palette = ((HSSFWorkbook)workbook).getCustomPalette(); //根据具体的RGB值把蓝色重新定义 palette.setColorAtIndex(HSSFColor.BLUE.index, (byte)83, (byte)141, (byte)213); //设置字体、字号 Font font = workbook.createFont(); font.setFontName("Arial");//设置字体 font.setFontHeightInPoints((short)26);//设置字体大小 //创建样式 CellStyle cellStyle = workbook.createCellStyle(); //把上面设置的背景色添加到样式 cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(HSSFColor.BLUE.index); //把上面设置的字体、字号添加到样式 cellStyle.setFont(font); cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//设置居中 //以下4行是设置边框 cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(CellStyle.BORDER_THIN); //最后把样式应用到Cell Sheet sheet = workbook.createSheet(); sheet.setDefaultColumnWidth(20);//默认把列宽设置为20 sheet.setGridsPrinted(true); Row row = sheet.createRow(0); Cell cell = row.createCell(0). cell.setCellStyle(cellStyle); } import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.hssf.usermodel.HSSFSheet; public void poiMethod(){ //以下四个参数：（int firstRow, int lastRow, int firstCol, int lastCol） sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); //合并单元格，参数描述如上 //如有样式的，需要在合并单元格后执行以下方法来设置样式，其中cellStyle参数是自定义的样式类 setRegionStyle(sheet,new CellRangeAddress(0, 0, 0,3),cellStyle); //以下是隐藏单元格 sheet.createRow(3).setHeight((short) 0);// 隐藏第4行 } /** * 合并单元格后设置样式（边框、背景色、字体等） * * @param sheet * @param cellRangeAddress * @param cellStyle */ private void setRegionStyle(HSSFSheet sheet,CellRangeAddress cellRangeAddress, CellStyle cellStyle) { for (int i = cellRangeAddress.getFirstRow(); i <= cellRangeAddress.getLastRow(); i++) { HSSFRow row = sheet.getRow(i); if(row==null){ row = sheet.createRow(i); } for (int j = cellRangeAddress.getFirstColumn(); j <=cellRangeAddress.getLastColumn(); j++) { HSSFCell cell = row.getCell(j); if(cell==null){ cell = row.createCell(j); } cell.setCellStyle(cellStyle); } } } import org.apache.poi.hssf.usermodel.DVConstraint; import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.ss.util.CellRangeAddressList; public void poiMethod(){ //下拉框的内容 String[] operArray = new String[] { "[1]Add", "[2]Modify", "[3]Delete" }; //对1-100行的第1列都生成下拉框 for (int i = 0; i < 100; i++) { CellRangeAddressList regions = new CellRangeAddressList(beginRow, endRow - 1, 0, 0); // 生成下拉框内容 DVConstraint constraint = DVConstraint.createExplicitListConstraint(operArray); // 绑定下拉框和作用区域 HSSFDataValidation dataValidataion = new HSSFDataValidation(regions, constraint); // 对sheet页生效 sheet.addValidationData(dataValidataion); } }