yc_java excel文件流

最新推荐文章于 2024-07-17 13:09:31 发布

好好学习，天天往上

最新推荐文章于 2024-07-17 13:09:31 发布

阅读量173

点赞数

分类专栏：笔记

笔记专栏收录该内容

2 篇文章 0 订阅

订阅专栏

java excel文件流

1. ExcelReader

package com.jeesuite.common2.excel;

import java.io.Closeable;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jeesuite.common2.excel.annotation.TitleCell;



public final class ExcelReader implements Closeable {

    private static final Logger     LOG    = LoggerFactory.getLogger(ExcelReader.class);
    /**
     * 时日类型的数据默认格式化方式
     */
    private              DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private       int      startRow;
    private       String   sheetName;
    private final String   excelFilePath;
    private final Workbook workbook;

    /**
     * 构造方法，传入需要操作的excel文件路径
     *
     * @param excelFilePath 需要操作的excel文件的路径
     * @throws IOException            IO流异常
     * @throws InvalidFormatException 非法的格式异常
     */
    public ExcelReader(String excelFilePath) throws IOException, InvalidFormatException {
        this.startRow = 0;
        this.sheetName = "Sheet1";
        this.excelFilePath = excelFilePath;
        this.workbook = createWorkbook();
    }

    /**
     * 通过数据流操作excel，仅用于读取数据
     *
     * @param inputStream excel数据流
     * @throws IOException            IO流异常
     * @throws InvalidFormatException 非法的格式异常
     */
    public ExcelReader(InputStream inputStream) throws IOException, InvalidFormatException {
        this.startRow = 0;
        this.sheetName = "Sheet1";
        this.excelFilePath = "";
        this.workbook = WorkbookFactory.create(inputStream);
    }

    /**
     * 通过数据流操作excel
     *
     * @param inputStream excel数据流
     * @param outFilePath 输出的excel文件路径
     * @throws IOException            IO流异常
     * @throws InvalidFormatException 非法的格式异常
     */
    public ExcelReader(InputStream inputStream, String outFilePath) throws IOException, InvalidFormatException {
        this.startRow = 0;
        this.sheetName = "Sheet1";
        this.excelFilePath = outFilePath;
        this.workbook = WorkbookFactory.create(inputStream);
    }

    /**
     * 开始读取的行数，这里指的是标题内容行的行数，不是数据开始的那行
     *
     * @param startRow 开始行数
     */
    public void setStartRow(int startRow) {
        if (startRow < 1) {
            throw new RuntimeException("最小为1");
        }
        this.startRow = --startRow;
    }

    /**
     * 设置需要读取的sheet名字，不设置默认的名字是Sheet1，也就是excel默认给的名字，所以如果文件没有自已修改，这个方法也就不用调了
     *
     * @param sheetName 需要读取的Sheet名字
     */
    public void setSheetName(String sheetName) {
//        Sheet sheet = this.workbook.getSheet(sheetName);
//        if (null == sheet) {
//            throw new RuntimeException("sheetName:" + sheetName + " is not exist");
//        }
        this.sheetName = sheetName;
    }

    /**
     * 设置时间数据格式
     *
     * @param format 格式
     */
    public void setFormat(String format) {
        this.format = new SimpleDateFormat(format);
    }

    /**
     * 解析读取excel文件
     *
     * @param clazz 对应的映射类型
     * @param <T>   泛型
     * @return 读取结果
     */
    public <T> List<T> parse(Class<T> clazz) {
        List<T> resultList = null;
        try {
            Sheet sheet = workbook.getSheet(this.sheetName);
            if (null != sheet) {
                resultList = new ArrayList<T>(sheet.getLastRowNum() - 1);
                Row row = sheet.getRow(this.startRow);

                Map<String, Field> fieldMap = new HashMap<String, Field>();
                Map<String, String> titleMap = new HashMap<String, String>();

                Field[] fields = clazz.getDeclaredFields();
                //这里开始处理映射类型里的注解
                for (Field field : fields) {
                    if (field.isAnnotationPresent(TitleCell.class)) {
                        TitleCell mapperCell = field.getAnnotation(TitleCell.class);
                        fieldMap.put(mapperCell.name(), field);
                    }
                }

                for (Cell title : row) {
                    CellReference cellRef = new CellReference(title);
                    titleMap.put(cellRef.getCellRefParts()[2], title.getRichStringCellValue().getString());
                }

                for (int i = this.startRow + 1; i <= sheet.getLastRowNum(); i++) {
                    T t = clazz.newInstance();
                    Row dataRow = sheet.getRow(i);
                    for (Cell data : dataRow) {
                        CellReference cellRef = new CellReference(data);
                        String cellTag = cellRef.getCellRefParts()[2];
                        String name = titleMap.get(cellTag);
                        Field field = fieldMap.get(name);
                        if (null != field) {
                            field.setAccessible(true);
                            getCellValue(data, t, field);
                        }
                    }
                    resultList.add(t);
                }
            } else {
                throw new RuntimeException("sheetName:" + this.sheetName + " is not exist");
            }
        } catch (InstantiationException e) {
            LOG.error("初始化异常", e);
        } catch (IllegalAccessException e) {
            LOG.error("初始化异常", e);
        } catch (ParseException e) {
            LOG.error("时间格式化异常:{}", e);
        } catch (Exception e) {
            LOG.error("其他异常", e);
        }
        return resultList;
    }


    private void getCellValue(Cell cell, Object o, Field field) throws IllegalAccessException, ParseException {
        LOG.debug("cell:{}, field:{}, type:{}", cell.getCellTypeEnum(), field.getName(), field.getType().getName());
        switch (cell.getCellTypeEnum()) {
            case BLANK:
                break;
            case BOOLEAN:
                field.setBoolean(o, cell.getBooleanCellValue());
                break;
            case ERROR:
                field.setByte(o, cell.getErrorCellValue());
                break;
            case FORMULA:
                field.set(o, cell.getCellFormula());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    if (field.getType().getName().equals(Date.class.getName())) {
                        field.set(o, cell.getDateCellValue());
                    } else {
                        field.set(o, format.format(cell.getDateCellValue()));
                    }
                } else {
                    if (field.getType().isAssignableFrom(Integer.class) || field.getType().getName().equals("int")) {
                        field.setInt(o, (int) cell.getNumericCellValue());
                    } else if (field.getType().isAssignableFrom(Short.class) || field.getType().getName().equals("short")) {
                        field.setShort(o, (short) cell.getNumericCellValue());
                    } else if (field.getType().isAssignableFrom(Float.class) || field.getType().getName().equals("float")) {
                        field.setFloat(o, (float) cell.getNumericCellValue());
                    } else if (field.getType().isAssignableFrom(Byte.class) || field.getType().getName().equals("byte")) {
                        field.setByte(o, (byte) cell.getNumericCellValue());
                    } else if (field.getType().isAssignableFrom(Double.class) || field.getType().getName().equals("double")) {
                        field.setDouble(o, cell.getNumericCellValue());
                    } else if (field.getType().isAssignableFrom(String.class)) {
                        String s = String.valueOf(cell.getNumericCellValue());
                        if (s.contains("E")) {
                            s = s.trim();
                            BigDecimal bigDecimal = new BigDecimal(s);
                            s = bigDecimal.toPlainString();
                        }
                        //防止整数判定为浮点数
                        if (s.endsWith(".0")) {
                            s = s.substring(0, s.indexOf(".0"));
                        }
                        field.set(o, s);
                    } else {
                        field.set(o, cell.getNumericCellValue());
                    }
                }
                break;
            case STRING:
                if (field.getType().getName().equals(Date.class.getName())) {
                    field.set(o, format.parse(cell.getRichStringCellValue().getString()));
                } else {
                    field.set(o, cell.getRichStringCellValue().getString());
                }
                break;
            default:
                field.set(o, cell.getStringCellValue());
                break;
        }
    }

    private Workbook createWorkbook() throws IOException, InvalidFormatException {
        Workbook workbook;
        File file = new File(this.excelFilePath);
        if (!file.exists()) {
            LOG.warn("文件:{} 不存在！创建此文件！", this.excelFilePath);
            if (!file.createNewFile()) {
                throw new IOException("文件创建失败");
            }
            workbook = new XSSFWorkbook();
        } else {
            workbook = WorkbookFactory.create(file);
        }
        return workbook;
    }


    /**
     * 获取指定单元格的值
     *
     * @param rowNumber  行数，从1开始
     * @param cellNumber 列数，从1开始
     * @return 该单元格的值
     */
    public String getCellValue(int rowNumber, int cellNumber) {
        String result;
        checkRowAndCell(rowNumber, cellNumber);
        Sheet sheet = this.workbook.getSheet(this.sheetName);
        Row row = sheet.getRow(--rowNumber);
        Cell cell = row.getCell(--cellNumber);
        switch (cell.getCellTypeEnum()) {
            case BLANK:
                result = cell.getStringCellValue();
                break;
            case BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                result = String.valueOf(cell.getErrorCellValue());
                break;
            case FORMULA:
                result = cell.getCellFormula();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    result = format.format(cell.getDateCellValue());
                } else {
                    result = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case STRING:
                result = cell.getRichStringCellValue().getString();
                break;
            default:
                result = cell.getStringCellValue();
                break;
        }
        return result;
    }

    @Override
    public void close() throws IOException {
        this.workbook.close();
    }

    private void checkRowAndCell(int rowNumber, int cellNumber) {
        if (rowNumber < 1) {
            throw new RuntimeException("rowNumber less than 1");
        }
        if (cellNumber < 1) {
            throw new RuntimeException("cellNumber less than 1");
        }
    }
}

2. ExcelPerfModeReader

package com.jeesuite.common2.excel;

import java.io.IOException;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidOperationException;
import org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException;
import org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;

import com.jeesuite.common2.excel.convert.XLS2CSV;
import com.jeesuite.common2.excel.convert.XLSX2CSV;
import com.jeesuite.common2.excel.helper.ExcelBeanHelper;
import com.jeesuite.common2.excel.helper.ExcelValidator;

/**
 * 性能模式读取工具（适合大数据量读取，解决内存消耗过高的问题）
 * @description <br>
 * @author <a href="mailto:vakinge@gmail.com">vakin</a>
 * @date 2017年1月18日
 */
public class ExcelPerfModeReader {

    private final String   excelFilePath;
    private int titleStartAt = 1;

    public ExcelPerfModeReader(String excelFilePath) {
		this.excelFilePath = excelFilePath;
	}

    public ExcelPerfModeReader titleStartAt(int start){
    	this.titleStartAt = start;
    	return this;
    }

	private List<String> read(){

		List<String> result = null;
		if(excelFilePath.toLowerCase().endsWith(ExcelValidator.XLS_SIFFIX)){
			try {
				result = readAsXLS(excelFilePath);
			} catch (OfficeXmlFileException e) {
				result = readAsXLSX(excelFilePath);
			}
		}else{
			try {
				result = readAsXLSX(excelFilePath);
			} catch (OLE2NotOfficeXmlFileException e) {
				result =  readAsXLS(excelFilePath);
			}
		}

		removeLineBeforeTitle(result);
		return result;
	}

	public <T> List<T> read(Class<T> clazz){
		List<String> rows = read();
		if(rows == null || rows.size() <= 1) {
			throw new ExcelOperBaseException("记录不存在");
		}
		return ExcelBeanHelper.setRowValues(clazz, rows);
	}


//	public <T> void read(Class<T> clazz,ResultProcessor<T> processor){
//
//	}

	private List<String> readAsXLS(String path){
		try {
			XLS2CSV xls2csv = new XLS2CSV(path, -1);
			return xls2csv.process();
		} catch (Exception e) {
			if(e instanceof NotOLE2FileException || e instanceof NotOfficeXmlFileException || e instanceof OfficeXmlFileException){
				throw new ExcelOperBaseException("请选择正确格式excel文件");
			}
			if(e instanceof IOException){
				throw new ExcelOperBaseException("文件读取失败");
			}
			throw new RuntimeException(e);
		}
	}


	private List<String> readAsXLSX(String path){
		OPCPackage opcPackage = null;
		try {
			opcPackage = OPCPackage.open(path, PackageAccess.READ);
			XLSX2CSV xlsx2csv = new XLSX2CSV(opcPackage, System.out, -1);
			return xlsx2csv.process();
		} catch (Exception e) {
			if(e instanceof OLE2NotOfficeXmlFileException || e instanceof NotOLE2FileException || e instanceof NotOfficeXmlFileException || e instanceof OfficeXmlFileException){
				throw new ExcelOperBaseException("请选择正确格式excel文件");
			}
			if(e instanceof IOException){
				throw new ExcelOperBaseException("文件读取失败");
			}
			if(e instanceof InvalidOperationException){
				throw new ExcelOperBaseException(e);
			}
			throw new RuntimeException(e);
		}finally{
			try {opcPackage.close();} catch (Exception e) {}
		}
	}

	private void removeLineBeforeTitle(List<String> lines){
		//第一行固定为sheet名
		if(titleStartAt == 1 || lines == null || lines.size() - 1 <= titleStartAt) {
			return;
		}
		for (int i = 1; i < titleStartAt; i++) {
			lines.remove(1);
		}
	}

}

3. ExcelWriter

package com.jeesuite.common2.excel;

import java.io.Closeable;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jeesuite.common2.excel.helper.ExcelBeanHelper;
import com.jeesuite.common2.excel.model.ExcelMeta;
import com.jeesuite.common2.excel.model.TitleMeta;



public final class ExcelWriter implements Closeable {

    private static final Logger     LOG    = LoggerFactory.getLogger(ExcelWriter.class);

    private       String   sheetName;
    private       OutputStream outputStream;
    private final SXSSFWorkbook workbook;

    /**
     * 构造方法，传入需要操作的excel文件路径
     *
     * @param excelFilePath 需要操作的excel文件的路径
     * @throws IOException            IO流异常
     * @throws InvalidFormatException 非法的格式异常
     */
    public ExcelWriter(String excelFilePath,String sheetName) throws IOException, InvalidFormatException {
    	this.sheetName = sheetName;
    	File file = new File(excelFilePath);
    	boolean exists = file.exists();
		if(!exists)file.createNewFile();
    	outputStream = new FileOutputStream(file);
        this.workbook = createWorkbook(exists ? file : null);
    }
    
    public ExcelWriter(String excelFilePath) throws IOException, InvalidFormatException {
    	this(excelFilePath,"Sheet1");
    }
    
    public ExcelWriter(OutputStream outputStream) throws IOException, InvalidFormatException {
    	this(outputStream,"Sheet1");
    }

    public ExcelWriter(OutputStream outputStream, String sheetName) throws InvalidFormatException, IOException {
		super();
		this.outputStream = outputStream;
		this.sheetName = sheetName;
		this.workbook = createWorkbook(null);
	}

	/**
     * 设置需要读取的sheet名字，不设置默认的名字是Sheet1，也就是excel默认给的名字，所以如果文件没有自已修改，这个方法也就不用调了
     *
     * @param sheetName 需要读取的Sheet名字
     */
    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    private SXSSFWorkbook createWorkbook(File existFile) throws IOException, InvalidFormatException {
    	SXSSFWorkbook workbook;
        if (existFile == null) {
            workbook = new SXSSFWorkbook(1000);//内存中保留 1000条数据，以免内存溢出
        } else {
        	workbook = new SXSSFWorkbook(new XSSFWorkbook(existFile), 1000);
        }
        return workbook;
    }
    
    /**
     * 将数据写入excel文件
     *
     * @param list 数据列表
     * @param <T>  泛型
     * @return 写入结果
     */
	public <T> boolean write(List<T> list, Class<T> clazz) {
		
		ExcelMeta excelMeta = ExcelBeanHelper.getExcelMeta(clazz);
		try {
			Sheet sheet = workbook.createSheet(this.sheetName);
			sheet.setDefaultColumnWidth(15); 
			CellStyle titleStyle = workbook.createCellStyle(); 
			titleStyle.setAlignment(HorizontalAlignment.CENTER);
			titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
			titleStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
			titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
			titleStyle.setBorderLeft(BorderStyle.THIN);
			Font font = workbook.createFont();
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 13);
			titleStyle.setFont(font);
			//列值类型
			Class<?>[] cellValueTypes = new Class<?>[excelMeta.getTitleColumnNum()];
			//写标题
			for (int i = 1; i <= excelMeta.getTitleRowNum(); i++) {
				Row excelRow = sheet.createRow(i - 1);
				for (int j = 1; j <= excelMeta.getTitleColumnNum(); j++) {
					TitleMeta titleMeta = excelMeta.getTitleMeta(i, j);
					Cell cell = excelRow.createCell(j - 1);
					cell.setCellValue(titleMeta == null ? "" : titleMeta.getTitle());
					cell.setCellStyle(titleStyle);
					cellValueTypes[j-1] = titleMeta.getValueType();
				}
			}
			//合并表头
            //sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 8));
			//sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
			mergeColumns(sheet,titleStyle);
			mergeRows(sheet,titleStyle,excelMeta);
			
			// 行数
			int rowsCount = sheet.getPhysicalNumberOfRows();
			//冻结表头
			//sheet.createFreezePane(0, rowsCount - 1);
			
			// 写入内容
			List<Object[]> rows = ExcelBeanHelper.beanToExcelValueArrays(list, clazz);
	        // 列数
	        //int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
			for (int i = 0; i < rows.size(); i++) {
				Row excelRow = sheet.createRow(i + rowsCount);
				
				Object[] vals = rows.get(i);
				for (int j = 0; j < vals.length; j++) {
					Cell cell = excelRow.createCell(j);
					if(cellValueTypes[j] == int.class || cellValueTypes[j] == Integer.class){
					    cell.setCellValue(vals[j] == null ? 0f : Integer.parseInt(vals[j].toString()));
					}else if(cellValueTypes[j] == float.class || cellValueTypes[j] == Float.class 
							|| cellValueTypes[j] == double.class || cellValueTypes[j] == Double.class
							|| cellValueTypes[j] == BigDecimal.class){
					    cell.setCellValue(vals[j] == null ? 0d : Double.parseDouble(vals[j].toString()));
					}else{
						cell.setCellValue(vals[j] == null ? "" : vals[j].toString());
					}
				}
			}
			workbook.write(outputStream);
			return true;
		} catch (IOException e) {
			LOG.error("流异常", e);
		} catch (Exception e) {
			LOG.error("其他异常", e);
		} finally {
		} 
		return false;
	}

	
	 /**
     * 合并列
     */
    private void mergeColumns(Sheet sheet,CellStyle cellStyle) {
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();
        // 列数
        int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
 
        Row row = null;
        Cell cell1 = null;
        Cell cell2 = null;
 
        int colSpan = 0;
 
        for (int r = 0; r < rowsCount; r++) {
            row = sheet.getRow(r);
            // 重置
            colSpan = 0;
            row = sheet.getRow(r);
            for (int c = 0; c < colsCount; c++) {
                cell1 = row.getCell(c);
                cell2 = row.getCell(c + 1);
                if (cell1 == null) {// 如果当前单元格是空的，跳过，继续当前行的后一个单元格查找
                    if (c == colsCount - 1) {
                        break;
                    } else {
                        continue;
                    }
                }
                if (cell2 == null) {// 说明当前行已经到最后一个单元格了
                    if (colSpan >= 1) {// 判断colSpan是否大于等于1，大于1就要合并了
                        // 合并行中连续相同的值的单元格
                        sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                        break;
                    }
                }
                if (cell1 != null && cell2 != null) {
                    // 如果当前单元格和下一个单元格内容相同，那么colSpan加1
                    if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                        colSpan++;
                    } else {
                        // 如果当前单元格和下一个不等，那么判断colSpan是否大于等于1
                        if (colSpan >= 1) {
                            // 合并行中连续相同的值的单元格
                            sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                            Cell nowCell = sheet.getRow(r).getCell(c - colSpan);
                    		nowCell.setCellStyle(cellStyle);
                            // 合并后重置colSpan
                            colSpan = 0;
                            continue;
                        }
                    }
                }
 
            }
        }
 
    }
 
    /**
     * 合并行
     */
    //TODO 暂时支持两行表头
    private void mergeRows(Sheet sheet,CellStyle cellStyle,ExcelMeta excelMeta) {
    	
    	Row row = null;
    	Cell cell = null;
    	String[] lastRowVals = new String[excelMeta.getTitleColumnNum()];
    	for (int r = 0; r < excelMeta.getTitleRowNum(); r++) {
			for (int c = 0; c < excelMeta.getTitleColumnNum(); c++) {
				row = sheet.getRow(r);
                cell = row.getCell(c);
                if(r == 0){
                	lastRowVals[c] = cell.getStringCellValue();
                }else{                	
                	if(StringUtils.equals(lastRowVals[c],cell.getStringCellValue())){
                		cell.setCellValue("");
                		sheet.addMergedRegion(new CellRangeAddress(0, r, c, c));
                		Cell nowCell = sheet.getRow(0).getCell(c);
                		nowCell.setCellStyle(cellStyle);
                	}
                }
                
			}
		}
    
    }
   

    @Override
    public void close() throws IOException {
    	try {this.outputStream.close();} catch (Exception e) {}
    	try {this.workbook.close();} catch (Exception e) {}
    }
}

4. ExcelTemplateWriter

package com.jeesuite.common2.excel;

import java.io.Closeable;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jeesuite.common2.excel.helper.ExcelBeanHelper;
import com.jeesuite.common2.excel.model.ExcelMeta;
import com.jeesuite.common2.excel.model.TitleMeta;



public final class ExcelWriter implements Closeable {

    private static final Logger     LOG    = LoggerFactory.getLogger(ExcelWriter.class);

    private       String   sheetName;
    private       OutputStream outputStream;
    private final SXSSFWorkbook workbook;

    /**
     * 构造方法，传入需要操作的excel文件路径
     *
     * @param excelFilePath 需要操作的excel文件的路径
     * @throws IOException            IO流异常
     * @throws InvalidFormatException 非法的格式异常
     */
    public ExcelWriter(String excelFilePath,String sheetName) throws IOException, InvalidFormatException {
    	this.sheetName = sheetName;
    	File file = new File(excelFilePath);
    	boolean exists = file.exists();
		if(!exists)file.createNewFile();
    	outputStream = new FileOutputStream(file);
        this.workbook = createWorkbook(exists ? file : null);
    }
    
    public ExcelWriter(String excelFilePath) throws IOException, InvalidFormatException {
    	this(excelFilePath,"Sheet1");
    }
    
    public ExcelWriter(OutputStream outputStream) throws IOException, InvalidFormatException {
    	this(outputStream,"Sheet1");
    }

    public ExcelWriter(OutputStream outputStream, String sheetName) throws InvalidFormatException, IOException {
		super();
		this.outputStream = outputStream;
		this.sheetName = sheetName;
		this.workbook = createWorkbook(null);
	}

	/**
     * 设置需要读取的sheet名字，不设置默认的名字是Sheet1，也就是excel默认给的名字，所以如果文件没有自已修改，这个方法也就不用调了
     *
     * @param sheetName 需要读取的Sheet名字
     */
    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    private SXSSFWorkbook createWorkbook(File existFile) throws IOException, InvalidFormatException {
    	SXSSFWorkbook workbook;
        if (existFile == null) {
            workbook = new SXSSFWorkbook(1000);//内存中保留 1000条数据，以免内存溢出
        } else {
        	workbook = new SXSSFWorkbook(new XSSFWorkbook(existFile), 1000);
        }
        return workbook;
    }
    
    /**
     * 将数据写入excel文件
     *
     * @param list 数据列表
     * @param <T>  泛型
     * @return 写入结果
     */
	public <T> boolean write(List<T> list, Class<T> clazz) {
		
		ExcelMeta excelMeta = ExcelBeanHelper.getExcelMeta(clazz);
		try {
			Sheet sheet = workbook.createSheet(this.sheetName);
			sheet.setDefaultColumnWidth(15); 
			CellStyle titleStyle = workbook.createCellStyle(); 
			titleStyle.setAlignment(HorizontalAlignment.CENTER);
			titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
			titleStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
			titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
			titleStyle.setBorderLeft(BorderStyle.THIN);
			Font font = workbook.createFont();
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 13);
			titleStyle.setFont(font);
			//列值类型
			Class<?>[] cellValueTypes = new Class<?>[excelMeta.getTitleColumnNum()];
			//写标题
			for (int i = 1; i <= excelMeta.getTitleRowNum(); i++) {
				Row excelRow = sheet.createRow(i - 1);
				for (int j = 1; j <= excelMeta.getTitleColumnNum(); j++) {
					TitleMeta titleMeta = excelMeta.getTitleMeta(i, j);
					Cell cell = excelRow.createCell(j - 1);
					cell.setCellValue(titleMeta == null ? "" : titleMeta.getTitle());
					cell.setCellStyle(titleStyle);
					cellValueTypes[j-1] = titleMeta.getValueType();
				}
			}
			//合并表头
            //sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 8));
			//sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
			mergeColumns(sheet,titleStyle);
			mergeRows(sheet,titleStyle,excelMeta);
			
			// 行数
			int rowsCount = sheet.getPhysicalNumberOfRows();
			//冻结表头
			//sheet.createFreezePane(0, rowsCount - 1);
			
			// 写入内容
			List<Object[]> rows = ExcelBeanHelper.beanToExcelValueArrays(list, clazz);
	        // 列数
	        //int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
			for (int i = 0; i < rows.size(); i++) {
				Row excelRow = sheet.createRow(i + rowsCount);
				
				Object[] vals = rows.get(i);
				for (int j = 0; j < vals.length; j++) {
					Cell cell = excelRow.createCell(j);
					if(cellValueTypes[j] == int.class || cellValueTypes[j] == Integer.class){
					    cell.setCellValue(vals[j] == null ? 0f : Integer.parseInt(vals[j].toString()));
					}else if(cellValueTypes[j] == float.class || cellValueTypes[j] == Float.class 
							|| cellValueTypes[j] == double.class || cellValueTypes[j] == Double.class
							|| cellValueTypes[j] == BigDecimal.class){
					    cell.setCellValue(vals[j] == null ? 0d : Double.parseDouble(vals[j].toString()));
					}else{
						cell.setCellValue(vals[j] == null ? "" : vals[j].toString());
					}
				}
			}
			workbook.write(outputStream);
			return true;
		} catch (IOException e) {
			LOG.error("流异常", e);
		} catch (Exception e) {
			LOG.error("其他异常", e);
		} finally {
		} 
		return false;
	}

	
	 /**
     * 合并列
     */
    private void mergeColumns(Sheet sheet,CellStyle cellStyle) {
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();
        // 列数
        int colsCount = sheet.getRow(0).getPhysicalNumberOfCells();
 
        Row row = null;
        Cell cell1 = null;
        Cell cell2 = null;
 
        int colSpan = 0;
 
        for (int r = 0; r < rowsCount; r++) {
            row = sheet.getRow(r);
            // 重置
            colSpan = 0;
            row = sheet.getRow(r);
            for (int c = 0; c < colsCount; c++) {
                cell1 = row.getCell(c);
                cell2 = row.getCell(c + 1);
                if (cell1 == null) {// 如果当前单元格是空的，跳过，继续当前行的后一个单元格查找
                    if (c == colsCount - 1) {
                        break;
                    } else {
                        continue;
                    }
                }
                if (cell2 == null) {// 说明当前行已经到最后一个单元格了
                    if (colSpan >= 1) {// 判断colSpan是否大于等于1，大于1就要合并了
                        // 合并行中连续相同的值的单元格
                        sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                        break;
                    }
                }
                if (cell1 != null && cell2 != null) {
                    // 如果当前单元格和下一个单元格内容相同，那么colSpan加1
                    if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                        colSpan++;
                    } else {
                        // 如果当前单元格和下一个不等，那么判断colSpan是否大于等于1
                        if (colSpan >= 1) {
                            // 合并行中连续相同的值的单元格
                            sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                            Cell nowCell = sheet.getRow(r).getCell(c - colSpan);
                    		nowCell.setCellStyle(cellStyle);
                            // 合并后重置colSpan
                            colSpan = 0;
                            continue;
                        }
                    }
                }
 
            }
        }
 
    }
 
    /**
     * 合并行
     */
    //TODO 暂时支持两行表头
    private void mergeRows(Sheet sheet,CellStyle cellStyle,ExcelMeta excelMeta) {
    	
    	Row row = null;
    	Cell cell = null;
    	String[] lastRowVals = new String[excelMeta.getTitleColumnNum()];
    	for (int r = 0; r < excelMeta.getTitleRowNum(); r++) {
			for (int c = 0; c < excelMeta.getTitleColumnNum(); c++) {
				row = sheet.getRow(r);
                cell = row.getCell(c);
                if(r == 0){
                	lastRowVals[c] = cell.getStringCellValue();
                }else{                	
                	if(StringUtils.equals(lastRowVals[c],cell.getStringCellValue())){
                		cell.setCellValue("");
                		sheet.addMergedRegion(new CellRangeAddress(0, r, c, c));
                		Cell nowCell = sheet.getRow(0).getCell(c);
                		nowCell.setCellStyle(cellStyle);
                	}
                }
                
			}
		}
    
    }
   

    @Override
    public void close() throws IOException {
    	try {this.outputStream.close();} catch (Exception e) {}
    	try {this.workbook.close();} catch (Exception e) {}
    }
}

5.pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>com.jeesuite</groupId>
		<artifactId>jeesuite-libs</artifactId>
		<version>1.3.4-SNAPSHOT</version>
	</parent>
	<artifactId>jeesuite-common2</artifactId>

	<dependencies>
		<dependency>
			<groupId>com.jeesuite</groupId>
			<artifactId>jeesuite-common</artifactId>
			<version>${project.parent.version}</version>
		</dependency>

		<dependency>
			<groupId>com.jeesuite</groupId>
			<artifactId>jeesuite-cache</artifactId>
			<version>${project.parent.version}</version>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.15</version>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.15</version>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.15</version>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-collections4</artifactId>
			<version>4.1</version>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.apache.zookeeper</groupId>
			<artifactId>zookeeper</artifactId>
			<version>${zookeeper.version}</version>
			<exclusions>
				<exclusion>
					<groupId>javax.jms</groupId>
					<artifactId>jms</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jdmk</groupId>
					<artifactId>jmxtools</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jmx</groupId>
					<artifactId>jmxri</artifactId>
				</exclusion>
				<exclusion>
					<groupId>org.slf4j</groupId>
					<artifactId>slf4j-log4j12</artifactId>
				</exclusion>
			</exclusions>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.6</version>
			<scope>test</scope>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-deploy-plugin</artifactId>
				<configuration>
					<skip>false</skip>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>