Java Excel 工作中的各种操作汇总记录包括：普通xssf的拷贝、流式sxssf拷贝、添加图片、下载例子等

工作中经常会用到，但是每次做完功能没有整理好，再次用到的时候。又要花费时间去找文档和理解，现在一次把各种情况都记录下来，方便后面工作节省时间。

流式sxssf是基于普通的xssf操作来，所以会先讲普通xssf操作，然后在xssf的基础上再讨论sxssf的操作。

1、导入架包的pom

这里大家根据需要导入不同版本

  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.0</version>
  </dependency>
  
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.0</version>
  </dependency>

2、官网信息，要习惯自己去官网查看信息，这很重要

POI-HSSF and POI-XSSF/SXSSF - Java API To Access Microsoft Excel Format Files

3、普通xssf操作

3.1、拷贝功能

1、拷贝行

        注意：合并单元格要记得处理

package com.springstudy.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
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;

public class ExcelUtils {
	
	public static void main(String[] args) {
		ExcelUtils.copyRowTest();

	}
	
	/**
	 * 
	 * @param wb 当前的工作
	 * @param sourceRow
	 * @param targetRow
	 */
	public static void copyRow(Workbook wb, Row sourceRow, Row targetRow) {
		//设置行高
		targetRow.setHeight(sourceRow.getHeight());
		Cell toCell = null;
		for(Cell cellTemp : sourceRow) {
			toCell = targetRow.createCell(cellTemp.getColumnIndex());
			copyCell(wb, (XSSFCell)cellTemp, (XSSFCell)toCell);
		}
	}
	
	/**
	 * 
	 * @param wb
	 * @param fromCell
	 * @param toCell
	 */
	public static void copyCell(Workbook wb, XSSFCell fromCell, XSSFCell toCell) {
		//第二个参数是使用默认的拷贝策略
		toCell.copyCellFrom(fromCell, new CellCopyPolicy());
	}
	
	public static byte[] fileToByte(String fileName) {
		byte[] bytes = null;
		try(FileInputStream fi = new FileInputStream(new File(fileName))){
			bytes = new byte[fi.available()];
			fi.read(bytes);
		} catch(IOException e) {
			//do something
			return null;
		}
		return bytes;
	}
	
	public static void copyRowTest() {
		System.out.println("begin");
		byte[] fileByte = ExcelUtils.fileToByte("D:/study/javarabbitmqworkspace/fanoutWorkspace/springdemo/doc/excel/SorceExcel.xlsx");
		//读取要拷贝的文件
		XSSFWorkbook sourceWb = null;
		try {
			//sourceWb = new XSSFWorkbook("D:/study/javarabbitmqworkspace/fanoutWorkspace/springdemo/doc/excel/SorceExcel.xlsx");
			sourceWb = new XSSFWorkbook(new ByteArrayInputStream(fileByte));
		} catch (IOException e) {
			e.printStackTrace();
		}
		XSSFSheet fromSheet = sourceWb.getSheetAt(0);
		
		/***合并单元格 获取 ***/
		List<CellRangeAddress> listMerged =   fromSheet.getMergedRegions();
		Map<Integer, List<CellRangeAddress>> mergedMap = listMerged.stream().collect(Collectors.groupingBy(e->e.getFirstRow()));
		
		//处理图片
		byte[] imageByte = ExcelUtils.fileToByte("D:/study/javarabbitmqworkspace/fanoutWorkspace/springdemo/doc/image/excelimage.jpg");
		//创建画布
		Drawing drawing = fromSheet.createDrawingPatriarch();
		ClientAnchor anchor = null;

		//源row
		XSSFRow sourceRow = fromSheet.getRow(12);
		for(int i=0;i<3;i++) {
			
			XSSFRow targetRow = fromSheet.createRow(13 + i);
			/**处理合并单元格 ***/
			if(mergedMap.containsKey(12)) {
				List<CellRangeAddress> listMergedFormat = mergedMap.get(12);
				for(CellRangeAddress crd : listMergedFormat) {
					fromSheet.addMergedRegion(new CellRangeAddress(
							crd.getFirstRow() + i + 1,
							crd.getLastRow() + i+ 1,
							crd.getFirstColumn(),
							crd.getLastColumn()));
				}
			}
			//开始拷贝
			ExcelUtils.copyRow(sourceWb, sourceRow, targetRow);
			//定义坐标
			ClientAnchor anchor1 = drawing.createAnchor(0, 0, 0, 0,   0,13 + i, 1, 13 + i + 1);