Excel区域复制粘贴

        <!-- XSSFWorkbook poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        

  1 package com.example.demo.controller;
  2 
  3 import java.io.FileInputStream;
  4 import java.io.IOException;
  5 import java.io.InputStream;
  6 import java.io.OutputStream;
  7 import java.util.List;
  8 import java.util.Set;
  9 import java.util.TreeSet;
 10 
 11 import javax.servlet.http.HttpServletResponse;
 12 
 13 import org.apache.poi.ss.usermodel.Cell;
 14 import org.apache.poi.ss.usermodel.Row;
 15 import org.apache.poi.ss.usermodel.Sheet;
 16 import org.apache.poi.ss.usermodel.Workbook;
 17 import org.apache.poi.ss.util.CellRangeAddress;
 18 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 19 
 20 public class ExcelOutputController {
 21     
 22     
 23     public void outputExcel(HttpServletResponse response) throws IOException {
 24         InputStream excelFile = new FileInputStream("c:/test.xlsx");
 25         
 26         
 27         Workbook wbsrc = new XSSFWorkbook(excelFile);
 28         
 29         Sheet oldSheet = wbsrc.getSheetAt(0);
 30         
 31         Sheet newSheet = wbsrc.getSheetAt(1);
 32         
 33         int startRow = 0,endRow = 11;
 34         
 35         int startCol = 0,endCol = 11;
 36         
 37         int newCellRow = 3,newCellCol =3;
 38         
 39         copyArea(oldSheet,startRow,endRow,startCol,endCol,newSheet,newCellRow,newCellCol);
 40         
 41         OutputStream out = null;
 42         
 43         out = response.getOutputStream();
 44         
 45         response.setContentType("application/vnd.ms-excel");
 46         
 47         response.setCharacterEncoding("UTF-8");
 48         
 49         response.setHeader("Content-Disposition", "attachment;filename=fff.xlsx");
 50         
 51         wbsrc.write(out);
 52         wbsrc.close();
 53         
 54     }
 55 
 56     private void copyArea(Sheet oldSheet, int startRow, int endRow, int startCol, int endCol, Sheet newSheet,
 57             int newCellRow, int newCellCol) {
 58         
 59         Row oldRow = null;
 60         Row newRow = null;
 61         
 62         Cell oldCell = null;
 63         Cell newCell = null;
 64         
 65         List<CellRangeAddress> mergedRegions = oldSheet.getMergedRegions();
 66         
 67         Set<String> newMergedRegions = new TreeSet<>();
 68         
 69         for(int i = startRow;i<=endRow;i++) {
 70             oldRow = oldSheet.getRow(i);
 71             newRow = newSheet.getRow(newCellRow+i);
 72             
 73             if(newRow==null) {
 74                 newRow = newSheet.createRow(newCellRow+i);
 75             }
 76             for(int j= startCol;j<=endCol;j++) {
 77                 oldCell = oldRow.getCell(j);
 78                 boolean isNew = false;
 79                 CellRangeAddress mergedRegion = judgeMergedRegion(mergedRegions,oldCell);
 80                 if(mergedRegion !=null) {
 81                     CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow()+newCellRow,
 82                             mergedRegion.getLastRow()+newCellRow,mergedRegion.getFirstColumn()+newCellCol,mergedRegion.getLastColumn()+newCellCol);
 83                     if(judgeRegionExisted(newMergedRegion,newMergedRegions)) {
 84                         isNew = false;
 85                         
 86                     } else {
 87                         newMergedRegions.add(newMergedRegion.formatAsString());
 88                         newSheet.addMergedRegion(newMergedRegion);
 89                         isNew =true;
 90                     }
 91                 } else { //not a mergedRegion
 92                     isNew = true;
 93                 }
 94                 newCell = newRow.getCell(newCellCol+j);
 95                 if(newCell == null) {
 96                     newCell = newRow.createCell(newCellCol+j);
 97                 }
 98                 newCell.setCellStyle(oldCell.getCellStyle());
 99                 if(isNew) {
100                     switch(oldCell.getCellType()) {
101                     case Cell.CELL_TYPE_NUMERIC:
102                         newCell.setCellValue(oldCell.getNumericCellValue());
103                         break;
104                     case Cell.CELL_TYPE_STRING:
105                         newCell.setCellValue(oldCell.getStringCellValue());
106                         break;
107                     case Cell.CELL_TYPE_FORMULA:
108                         newCell.setCellFormula(oldCell.getCellFormula());
109                         break;
110                     case Cell.CELL_TYPE_BLANK:
111                         newCell.setCellType(Cell.CELL_TYPE_BLANK);
112                         break;
113                     case Cell.CELL_TYPE_BOOLEAN:
114                         newCell.setCellValue(oldCell.getBooleanCellValue());
115                         break;
116                     case Cell.CELL_TYPE_ERROR:
117                         newCell.setCellValue(oldCell.getErrorCellValue());
118                         break;
119                     default:
120                         break;
121                         
122                     }
123                 }
124             }
125         }
126     }
127 
128     private boolean judgeRegionExisted(CellRangeAddress newMergedRegion, Set<String> newMergedRegions) {
129         return newMergedRegions.contains(newMergedRegion.formatAsString());
130     }
131 
132     private CellRangeAddress judgeMergedRegion(List<CellRangeAddress> mergedRegions, Cell cell) {
133         
134         for(CellRangeAddress cra:mergedRegions){
135             if(cra.isInRange(cell))
136                 return cra;
137         }
138         return null;
139     }
140     
141     
142 
143 }