×

poi 导入 sheet复制

企业级框架工作中使用的Sheet复制、导入工具类POIUtil

我的笔记 我的笔记 发表于2018-09-04 15:10:59 浏览3597 评论0

抢沙发发表评论

这个工具类基于POI,POM文件中加入

<!-- poi file upload -->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>${poi.version}</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>${poi.version}</version>
</dependency>
<dependency>
     <groupId>org.apache.xmlbeans</groupId>
     <artifactId>xmlbeans</artifactId>
     <version>2.3.0</version>
   </dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-scratchpad</artifactId>
   <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-examples</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-excelant</artifactId>
   <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.2</version>
</dependency>
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.3.2</version>
</dependency>

接下来是工具类,注释应该能看懂,不懂在下方留言:

import java.io.*;
import java.util.*;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.datadriver.core.util.Utils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * POI工具类 功能点: 
 * 1、实现excel的sheet复制,复制的内容包括单元的内容、样式、注释
 * 2、setMForeColor修改HSSFColor.YELLOW的色值,setMBorderColor修改PINK的色值
 * @Acthor:fyd
 * @ClassName: PoiUtil
 * @Date 2017-7-14 上午9:24:30
 */
public final class PoiUtil {
   /**
    * 
    * @Acthor:fyd
    * @Title: readExcel
    * @Date 2017-7-14 上午9:32:16
    * @param targetWb 新的工作空间
    * @param filePathAndName 文件路径/名称
    * @param excelName 新的文件名称
    * @param sheetName sheet 名称
    * @throws Exception 
    * @return void 返回类型
    */
   public static void readExcel(HSSFWorkbook targetWb, String filePathAndName, String excelName, String sheetName)
       throws Exception {
      // 打开已有的excel
      InputStream in = new FileInputStream(filePathAndName);
      HSSFWorkbook sourceWb = new HSSFWorkbook(in);
      for (int i = 0; i < sourceWb.getNumberOfSheets(); i++) {
         HSSFSheet sourceSheet = sourceWb.getSheetAt(i);
         copySheet(sourceSheet, targetWb, sourceWb, sheetName + "_" +  sourceSheet.getSheetName());
      }
   }
   
   /**
    * 
    * @Acthor:fyd
    * @Title: readExcelT
    * @Date 2017-8-31 上午10:54:27
    * @param targetWb
    * @param filePathAndName
    * @param excelName
    * @param sheetName
    * @throws Exception 
    * @return void 返回类型
    */
   public static void readExcelT(HSSFWorkbook targetWb, String filePathAndName, String excelName, String sheetName)
          throws Exception {
         // 打开已有的excel
         InputStream in = new FileInputStream(filePathAndName);
         HSSFWorkbook sourceWb = new HSSFWorkbook(in);
         for (int i = 0; i < sourceWb.getNumberOfSheets(); i++) {
            HSSFSheet sourceSheet = sourceWb.getSheetAt(i);
            copySheet(sourceSheet, targetWb, sourceWb, sheetName);
         }
      }
   
   /**
    * 功能:拷贝sheet
    * 实际调用    copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true)
    * @param targetSheet
    * @param sourceSheet
    * @param targetWork
    * @param sourceWork                                                                   
    */
   public static void copySheet(HSSFSheet sourceSheet,
         HSSFWorkbook targetWork, HSSFWorkbook sourceWork, String sheetName) throws Exception{
      if(Utils.isEmpty(sourceSheet) || Utils.isEmpty(targetWork) 
            || Utils.isEmpty(sourceWork) || Utils.isEmpty(sheetName)){
         throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时,targetSheet、sourceSheet、targetWork、sourceWork都不能为空,故抛出该异常!");
      }
      HSSFSheet targetSheet = targetWork.createSheet(sheetName);
      copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true);
   }

   /**
    * 功能:拷贝sheet
    * @param targetSheet
    * @param sourceSheet
    * @param targetWork
    * @param sourceWork
    * @param copyStyle                boolean 是否拷贝样式
    */
   public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet,
         HSSFWorkbook targetWork, HSSFWorkbook sourceWork, boolean copyStyle)throws Exception {
      if(Utils.isEmpty(targetSheet) || Utils.isEmpty(sourceSheet) 
            || Utils.isEmpty(targetWork) || Utils.isEmpty(sourceWork)){
         throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时,targetSheet、sourceSheet、targetWork、sourceWork都不能为空,故抛出该异常!");
      }
      //复制源表中的合并单元格
      mergerRegion(targetSheet, sourceSheet);
      
      //复制源表中的行
      int maxColumnNum = 0;

      Map styleMap = (copyStyle) ? new HashMap() : null;
      
      //设置目标sheet的列宽
      for (int i = 0; i <= maxColumnNum; i++) {
         targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));
      }
      
      HSSFPatriarch patriarch = targetSheet.createDrawingPatriarch(); //用于复制注释
      for (Iterator rowIt = sourceSheet.rowIterator(); rowIt.hasNext();) {  
            HSSFRow sourceRow = (HSSFRow) rowIt.next();  
            HSSFRow targetRow = targetSheet.createRow(sourceRow.getRowNum());  
           if (!Utils.isEmpty(sourceRow)) {
            //行复制  
            copyRow(targetRow, sourceRow,
               targetWork, sourceWork,patriarch, styleMap);
            }
            if (sourceRow.getLastCellNum() > maxColumnNum) {
            maxColumnNum = sourceRow.getLastCellNum();
         }
        }
      
   }
   
   /**
    * 功能:拷贝row
    * @param targetRow
    * @param sourceRow
    * @param styleMap
    * @param targetWork
    * @param sourceWork
    * @param targetPatriarch
    */
   public static void copyRow(HSSFRow targetRow, HSSFRow sourceRow,
         HSSFWorkbook targetWork, HSSFWorkbook sourceWork,HSSFPatriarch targetPatriarch, Map styleMap) throws Exception {
      if(Utils.isEmpty(targetRow) || Utils.isEmpty(sourceRow) || 
            Utils.isEmpty(targetWork) || Utils.isEmpty(sourceWork)){
         throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时,targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空,故抛出该异常!");
      }
      //设置行高
      targetRow.setHeight(sourceRow.getHeight());
      
      for (Iterator cellIt = sourceRow.cellIterator(); cellIt.hasNext();) {  
            HSSFCell sourceCell = (HSSFCell) cellIt.next();  
            if (sourceCell != null) {
                HSSFCell targetCell = targetRow.createCell(sourceCell.getColumnIndex());  
            //拷贝单元格,包括内容和样式
            copyCell(targetCell, sourceCell, targetWork, sourceWork, styleMap);
            
            //拷贝单元格注释
            copyComment(targetCell,sourceCell,targetPatriarch);
         } 
        }  
   }
   
   /**
    * 功能:拷贝cell,依据styleMap是否为空判断是否拷贝单元格样式
    * @param targetCell         不能为空
    * @param sourceCell         不能为空
    * @param targetWork         不能为空
    * @param sourceWork         不能为空
    * @param styleMap          可以为空            
    */
   public static void copyCell(HSSFCell targetCell, HSSFCell sourceCell, HSSFWorkbook targetWork, HSSFWorkbook sourceWork,Map styleMap) {
      if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){
         throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时,targetCell、sourceCell、targetWork、sourceWork都不能为空,故抛出该异常!");
      }
      
      //处理单元格样式
      if(styleMap != null){
         if (targetWork == sourceWork) {
            targetCell.setCellStyle(sourceCell.getCellStyle());
         } else {
            String stHashCode = "" + sourceCell.getCellStyle().hashCode();
            HSSFCellStyle targetCellStyle = (HSSFCellStyle) styleMap
                  .get(stHashCode);
            if (targetCellStyle == null) {
               targetCellStyle = targetWork.createCellStyle();
               targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
               styleMap.put(stHashCode, targetCellStyle);
            }
            targetCell.setCellStyle(targetCellStyle);
         }
      }
         
      //处理单元格内容
      switch (sourceCell.getCellType()) {
      case HSSFCell.CELL_TYPE_STRING:
         targetCell.setCellValue(sourceCell.getRichStringCellValue());
         break;
      case HSSFCell.CELL_TYPE_NUMERIC:
         targetCell.setCellValue(sourceCell.getNumericCellValue());
         break;
      case HSSFCell.CELL_TYPE_BLANK:
         targetCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
         break;
      case HSSFCell.CELL_TYPE_BOOLEAN:
         targetCell.setCellValue(sourceCell.getBooleanCellValue());
         break;
      case HSSFCell.CELL_TYPE_ERROR:
         targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
         break;
      case HSSFCell.CELL_TYPE_FORMULA:
         targetCell.setCellFormula(sourceCell.getCellFormula());
         break;
      default:
         break;
      }
   }
   
   /**
    * 功能:拷贝comment
    * @param targetCell
    * @param sourceCell
    * @param targetPatriarch
    */
   public static void copyComment(HSSFCell targetCell,HSSFCell sourceCell,HSSFPatriarch targetPatriarch)throws Exception{
      if(targetCell == null || sourceCell == null || targetPatriarch == null){
         throw new IllegalArgumentException("调用PoiUtil.copyCommentr()方法时,targetCell、sourceCell、targetPatriarch都不能为空,故抛出该异常!");
      }
      
      //处理单元格注释
      HSSFComment comment = sourceCell.getCellComment();
      if(comment != null){
         HSSFComment newComment = targetPatriarch.createComment(new HSSFClientAnchor());
         newComment.setAuthor(comment.getAuthor());
         newComment.setColumn(comment.getColumn());
         newComment.setFillColor(comment.getFillColor());
         newComment.setHorizontalAlignment(comment.getHorizontalAlignment());
         newComment.setLineStyle(comment.getLineStyle());
         newComment.setLineStyleColor(comment.getLineStyleColor());
         newComment.setLineWidth(comment.getLineWidth());
         newComment.setMarginBottom(comment.getMarginBottom());
         newComment.setMarginLeft(comment.getMarginLeft());
         newComment.setMarginTop(comment.getMarginTop());
         newComment.setMarginRight(comment.getMarginRight());
         newComment.setNoFill(comment.isNoFill());
         newComment.setRow(comment.getRow());
         newComment.setShapeType(comment.getShapeType());
         newComment.setString(comment.getString());
         newComment.setVerticalAlignment(comment.getVerticalAlignment());
         newComment.setVisible(comment.isVisible());
         targetCell.setCellComment(newComment);
      }
   }
   
   /**
    * 功能:复制原有sheet的合并单元格到新创建的sheet
    * 
    * @param sheetCreat
    * @param sourceSheet
    */
   public static void mergerRegion(HSSFSheet targetSheet, HSSFSheet sourceSheet)throws Exception {
      if(targetSheet == null || sourceSheet == null){
         throw new IllegalArgumentException("调用PoiUtil.mergerRegion()方法时,targetSheet或者sourceSheet不能为空,故抛出该异常!");
      }
      
      for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
         CellRangeAddress oldRange = sourceSheet.getMergedRegion(i);
         CellRangeAddress newRange = new CellRangeAddress(
               oldRange.getFirstRow(), oldRange.getLastRow(),
               oldRange.getFirstColumn(), oldRange.getLastColumn());
         targetSheet.addMergedRegion(newRange);
      }
   }

   /**
    * 功能:重新定义HSSFColor.YELLOW的色值
    * 
    * @param workbook
    * @return
    */
   public static HSSFColor setMForeColor(HSSFWorkbook workbook) {
      HSSFPalette palette = workbook.getCustomPalette();
      HSSFColor hssfColor = null;
       byte[] rgb = { (byte) 221, (byte) 241, (byte) 255 };
       try {
          hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]);
          if (hssfColor == null) {
             palette.setColorAtIndex(HSSFColor.YELLOW.index, rgb[0], rgb[1],
                   rgb[2]);
             hssfColor = palette.getColor(HSSFColor.YELLOW.index);
          }
       } catch (Exception e) {
          e.printStackTrace();
       }
       return hssfColor;
   }
   /**
    * 功能:重新定义HSSFColor.PINK的色值
    * 
    * @param workbook
    * @return
    */
   public static HSSFColor setMBorderColor(HSSFWorkbook workbook) {
      HSSFPalette palette = workbook.getCustomPalette();
      HSSFColor hssfColor = null;
      byte[] rgb = { (byte) 0, (byte) 128, (byte) 192 };
      try {
         hssfColor = palette.findColor(rgb[0], rgb[1], rgb[2]);
         if (hssfColor == null) {
            palette.setColorAtIndex(HSSFColor.PINK.index, rgb[0], rgb[1],
                  rgb[2]);
            hssfColor = palette.getColor(HSSFColor.PINK.index);
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
      return hssfColor;
   }

   /***
    * 把Excel文件返回List集合
    * @param f
    * @param filePath
    * @return
    */
   public static List excelToList(File f, String filePath){
      List list=null;//返回对象
      if(null==f && (null==filePath || "".equals(filePath))){
         return null;
      }
      File file = f;
      if(null!=filePath && !"".equals(filePath)){//已指定文件路径
         file = new File(filePath);
      }

      String filename = file.getName();
      if (filename == null || "".equals(filename)){
      }
      try {
         InputStream input = new FileInputStream(file);
         Workbook workBook = null;
         //以下做法是为了区分不同版本,然后使用XSSF或HSSF
         String flag="";//XSSF或HSSF的标记
         try {
            workBook = new XSSFWorkbook(input);
            flag="XSSF";
         } catch (Exception ex) {
            workBook = new HSSFWorkbook(new FileInputStream(file));
            flag="HSSF";
         }

         //根据标记使用不同的方法来解析excel
         if("XSSF".equals(flag)){
            XSSFSheet sheet = (XSSFSheet) workBook.getSheetAt(0);
            if (sheet != null)
            {      //list
               list = new ArrayList();
               for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++)
               {   //object
                  XSSFRow row = sheet.getRow(i);
                  List obj = new ArrayList();
                  for (int j = 0; j < row.getPhysicalNumberOfCells(); j++)
                  {   //column
                     XSSFCell cell = row.getCell(j);
                     String cellStr = cell.toString();
                     obj.add(cellStr);
                  }
                  System.out.println();
                  list.add(obj);
               }
            }
         }else if ("HSSF".equals(flag)){
            HSSFSheet sheet = (HSSFSheet) workBook.getSheetAt(0);
            if (sheet != null)
            {      //list
               list = new ArrayList();
               for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++)
               {   //object
                  HSSFRow row = sheet.getRow(i);
                  List obj = new ArrayList();
                  for (int j = 0; j < row.getPhysicalNumberOfCells(); j++)
                  {   //column
                     HSSFCell cell = row.getCell(j);
                     String cellStr = cell.toString();

                     obj.add(cellStr);
                  }
                  System.out.println();
                  list.add(obj);
               }
            }
         }else{
         }
      }
      catch (Exception e) {
         e.printStackTrace();
      }
      return list;
   }
}


我的笔记博客版权我的笔记博客版权