对指定的单元格进行填充具体的值:
public static boolean setValuebySheetRowColumn(Sheet OneSheet, int row, int column, String value) { Row Sheet_row; Cell Sheet_cell; Sheet_row = OneSheet.getRow(row); Sheet_cell = Sheet_row.getCell(column); Sheet_cell.setCellValue(value); return true; }
对指定的单元格进行指定格式填充:
XSSFFont 对象
CellStyle 对象
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillBackgroundColor(IndexedColors.RED.index); // 背景色
XSSFFont font = wb.createFont();
font.setColor(Font.COLOR_RED);//字体颜色
font.setStrikeout(true);//删除线
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
对指定的单元格进行图片水印填充:(自定义偏移量,缩放比例)
多图片:
public static boolean InsertPic(List<String> piclist, XSSFWorkbook xssfWorkbook, Sheet sheet, List<Integer> row, List<Integer> column) throws IOException { try{ for (int i = 0; i < piclist.size(); i++) { // piclist.set(i,"C:\\Users\\yhl\\Desktop\\code\\client\\vueT5-dev\\src\\assets\\icons\\add_cnc.png") ; FileInputStream stream=new FileInputStream(piclist.get(i)); BufferedImage bufferImg; bufferImg = ImageIO.read(new File(piclist.get(i))); int maxWidth = 17,maxHeight=8; //x1=12左侧预留12宽度,y1=15上方预留15宽度 int x1=2,y1=2,x2=17,y2=8; /* 原始图像的宽度和高度 */ int width = bufferImg.getWidth(); int height = bufferImg.getHeight(); if (width>height) {//宽大于高 //假设左右各留出12宽度,图片宽设置为最大,占据约1000(=maxWidth-12*2) double rate = (double)(maxWidth-x1*2)/width;//比例 int eHeight = (int)(height*rate);//等比例算出需要的高度 //如果图片高度+y1大于255,则设置图片的高度为255-y1=240,重新计算宽度 if(eHeight+y1>maxHeight) { rate = (double) (maxHeight-y1)/height;//重新计算比例 int ewidth = (int)(width*rate);//等比例算出需要的宽度 y2 = (maxHeight-y1);//y2坐标 x2 = ewidth+x1; if(x2<16){//如果图片不居中,重新计算x1位置,使图片居中 x1 = (maxWidth-x2)/2;//图片居中 x2 = ewidth+x1; } }else { y2 = y1+eHeight;//这里就不设置图片上下居中了(保留图片距离上方15) x2 = (maxWidth-x1*2); } }else{//高大于宽 //假设上留出15宽度,图片高设置为最大,占据240(=255-15) double rate = (double)(maxHeight-y1)/height;//比例 int ewidth = (int)(width*rate);//等比例算出需要的宽度 //如果图片高度>宽,高最大为240,宽最大为1012,所以宽不会超出 y2 = (maxHeight-y1); x2 = ewidth+x1; if(x2<16){//如果图片不居中,重新计算x1位置,使图片居中 x1 = (maxWidth-x2)/2;//图片居中 x2 = ewidth+x1; } } byte[] bytes= IOUtils.toByteArray(stream); //向Excel添加一张图片,并返回该图片在Excel中的图片集合中的下标 int pictureIdx = xssfWorkbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); //绘图工具类 CreationHelper helper = xssfWorkbook.getCreationHelper(); //创建一个绘图对象 Drawing<?> patriarch = sheet.createDrawingPatriarch(); //创建锚点,设置图片坐标 ClientAnchor anchor = helper.createClientAnchor(); anchor.setRow1(row.get(i)); anchor.setCol1(column.get(i)); anchor.setDx1(x1* XSSFShape.EMU_PER_POINT); anchor.setDy1(y1*XSSFShape.EMU_PER_POINT); anchor.setDx2(x2*XSSFShape.EMU_PER_POINT); anchor.setDx2(y2*XSSFShape.EMU_PER_POINT); // 创建图片 Picture picture = patriarch.createPicture(anchor, pictureIdx); //计算缩放比例 Row sheetRow = sheet.getRow(row.get(i)); Cell cell = sheetRow.getCell(column.get(i)); double standardWidth = 35; double standardHeight = 20; // 计算单元格的长宽 double cellWidth = sheet.getColumnWidthInPixels(cell.getColumnIndex()); double cellHeight = cell.getRow().getHeightInPoints()/72*96; // 计算需要的长宽比例的系数 double a = standardWidth / cellWidth; double b = standardHeight / cellHeight; picture.resize(a,b); } }catch (Exception ignored){ }finally { } return true; }
合并单元格 :
CellRangeAddress newRange=new CellRangeAddress(rowIdx, rowIdx, 16, 18);
//开始行、结束行,开始列,结束列
sheet.addMergedRegionUnsafe(newRange);
//取消合并单元格 public static void removeMerged(Sheet sheet, Integer startRow, Integer endRow, Integer startColumn, Integer endColumn) { if(startRow==null){ startRow= sheet.getFirstRowNum(); } if(endRow==null){ endRow= sheet.getLastRowNum(); } //获取所有的单元格 int sheetMergeCount = sheet.getNumMergedRegions(); //用于保存要移除的那个合并单元格序号 List<Integer> indexList = new ArrayList<>(); for (int i = 0; i < sheetMergeCount; i++) { //获取第i个单元格 CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if (startRow <= firstRow && endRow >= lastRow && startColumn <= firstColumn && endColumn >= lastColumn) { indexList.add(i); } } sheet.removeMergedRegions(indexList); }