ExcelWriter类

//ExcelWriter类

package com.cst.kit.poi.excel;

import java.io.File;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.Workbook;

import com.cst.kit.object.ObjectKit;
import com.cst.kit.string.StringKit;


import java.util.*;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

/**

 * 

 * @author guweichao

 * @created 2019年3月23日

 * @updated 2019年3月23日

 * @return
   */
   public class ExcelWriter {
   private Workbook wb;

   public ExcelWriter(InputStream in, boolean xls) {
   	wb = PoiUtil.getWorkbook(in, xls);
   }

   public ExcelWriter(File file) {
   	wb = PoiUtil.getWorkbook(file);
   }

   public ExcelWriter(String file) {
   	wb = PoiUtil.getWorkbook(file);
   }

   /**

    * @author guweichao
    * @created_date 2019年3月23日
    * @description 将excel中的${*} 设置为infos中的值
      */
      public void setMarker(Map<String, Object> infos) {
      setWorkbookMarkers(infos);
      }

   /**

    * @desc 将excel中的${*} 和${*.*}标记替换为map中的信息.${*} 为替换表格内容，${*.*}为多行替换表格内容
    * @param infos
    * @param sheetIndex:仅替换excel中指定sheet页(从0开始)
      */
      public void setMarker(Map<String, Object> infos, int sheetIndex) {
      setSheetMarkers(wb.getSheetAt(sheetIndex), infos);
      }

   /**

    * @desc 将excel中的${*} 和${*.*}标记替换为map中的信息
    * @param infos
    * @param sheetName :仅替换excel中指定sheet页
      */
      public void setMarker(Map<String, Object> infos, String sheetName) {
      setSheetMarkers(wb.getSheet(sheetName), infos);
      }

   public byte[] getBytes() {
   	return PoiUtil.workbook2ByteArray(wb);
   }

   private void setWorkbookMarkers(Map<String, Object> infos) {
   	for (int x = 0, sw = wb.getNumberOfSheets(); x < sw; x++) {
   		setSheetMarkers(wb.getSheetAt(x), infos);
   	}
   }

   private static void setSheetMarkers(Sheet sh, Map<String, Object> sheetInfo) {
   	for (int x = sh.getLastRowNum(); x >= 0; x--) {
   		setRowMarkers(sh.getRow(x), sheetInfo);
   	}
   }

   @SuppressWarnings({ "unchecked" })
   private static void setRowMarkers(Row row, Map<String, Object> sheetInfo) {
   	if (row == null) {
   		return;
   	}
   	String rowKey = null;
   	Map<Integer, String> keyMap = new HashMap<>();
   	// 检查行内容,抽取listmarker的主key,并替换mapMarker
   	for (int x = 0, lc = row.getLastCellNum(); x < lc; x++) {
   		Cell cell = row.getCell(x);
   		if (cell != null) {
   			String content = PoiUtil.getCellString(cell);
   			String[] marker = checkMarker(content);
   			if (marker != null) {
   				String listKey = marker[0];
   				String mapKey = marker[1];
   				if (listKey == null) {
   					if (mapKey != null) {
   						cell.setCellValue(StringKit.of(sheetInfo.get(mapKey)));
   					}
   				} else {
   					if (rowKey == null) {
   						rowKey = listKey;
   					} else if (!rowKey.equals(listKey)) {
   						throw new RuntimeException("marker标记错误:" + content);
   					}
   					keyMap.put(x, mapKey);
   				}
   			}
   		}
   	}
   	// 存在listMarker,需要处理
   	if (rowKey != null) {
   		List<Object> listInfo = (List<Object>) sheetInfo.get(rowKey);
   		if (listInfo == null || listInfo.isEmpty()) {// 如果为空,只需要把marker替换为空
   			String tmp = null;
   			for (Map.Entry<Integer, String> e : keyMap.entrySet()) {
   				row.getCell(e.getKey()).setCellValue(tmp);
   			}
   		} else {
   			Map<String, Object> map0 = ObjectKit.toMap(listInfo.get(0));
   			for (Map.Entry<Integer, String> e : keyMap.entrySet()) {
   				row.getCell(e.getKey()).setCellValue(StringKit.of(map0.get(e.getValue())));
   			}
   			int r = row.getRowNum();
   			for (int x = 1, s = listInfo.size(); x < s; x++) {
   				map0 = ObjectKit.toMap(listInfo.get(x));
   				Row rowx = PoiUtil.insertRow(row.getSheet(), row, r + x);
   				for (Map.Entry<Integer, String> e : keyMap.entrySet()) {
   					int cellNum = e.getKey();
   					Cell cell = rowx.getCell(cellNum);
   					if (cell == null) {
   						cell = rowx.createCell(cellNum);
   					}
   					cell.setCellValue(StringKit.of(map0.get(e.getValue())));
   				}

   			}
   		}
   	}

   }

   /**

    * 
    * @param marker
    * @return:null表示不是marker不需要处理,markers[0]代表list的key,markers[1]代表map的key
      */
      private static String[] checkMarker(String marker) {
      String[] markers = null;
      if (StringKit.notEmpty(marker)) {
      	int start = marker.indexOf("${");
      	int d = marker.indexOf(".");
      	int ld = marker.lastIndexOf(".");
      	if (d != ld) {
      		throw new RuntimeException("marker标记错误:" + marker);
      	}
      	int end = marker.indexOf("}");
      	int len = marker.length() - 1;
      	if (start == 0 && end == len) {// 是marker格式的内容
      		if (d != -1) { // ${*.*}
      			String lkey = marker.substring(2, d);
      			String mkey = marker.substring(d + 1, len);
      			if (StringKit.notEmpty(lkey) && StringKit.notEmpty(mkey)) {
      				markers = new String[2];
      				markers[0] = lkey;
      				markers[1] = mkey;
      			} else {
      				throw new RuntimeException("marker标记错误:" + marker);
      			}
      		} else {// ${*}
      			String mkey = marker.substring(2, len);
      			if (StringKit.notEmpty(mkey)) {
      				markers = new String[2];
      				markers[1] = mkey;
      			} else {
      				throw new RuntimeException("marker标记错误:" + marker);
      			}
      		}
      	}
      }
      return markers;
      }

}