java poi 操作excel 集锦，包含列合并

存放一下方案工具类放置处。

ps:这篇文章只是作为我个人代码归档而已，不是技术分享文档。可能你们看不懂觉得乱七八糟。。

 

 

pom.xml

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

工具Util

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;

import javax.servlet.http.HttpServletResponse;

import com.logytar.bo.TestMapDTO;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Font;
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.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.Date;
/**
 * 导出excel表格
 * 
 */
public class ExportExcelUtil {
     
  private static Logger log=LoggerFactory.getLogger(ExportExcelUtil.class);
	
  private SXSSFWorkbook wb;
  private Sheet sheet;
  private Map<String,CellStyle> styles;
  private int rownum;
  List<Object[]> annotationList = new ArrayList<Object[]>();
  
  public ExportExcelUtil(String title,Class<?> cls) {
	  this(title,cls,1);
  }
  public ExportExcelUtil(String title,Class<?> cls,int type,int...groups) {
	  Field[] fs=cls.getDeclaredFields();
	  for(Field f:fs) {
		  ExcelField ef= f.getAnnotation(ExcelField.class);
		  if(ef != null && (ef.type() ==0 || ef.type() ==type)) {
			  if(groups !=null && groups.length>0) {
				  boolean inGroup = false;
				  for(int g:groups) {
					  if(inGroup) {
						  break;
					  }
					  for(int efg: ef.groups()) {
						  if(g == efg) {
							  inGroup=true;
							  annotationList.add(new Object[] {ef,f});
						  }
					  }
				  }
			  }else {
				  annotationList.add(new Object[] {ef,f});
			  }
		  }
	  }
	  Method[] ms = cls.getDeclaredMethods();
	  for(Method m:ms) {
		  ExcelField ef =m.getAnnotation(ExcelField.class);
		  if(ef != null && (ef.type() ==0 || ef.type() ==type)) {
			  if(groups !=null && groups.length>0) {
				  boolean inGroup = false;
				  for(int g: groups) {
					  if(inGroup) {
						  break;
					  }
					  for(int efg: ef.groups()) {
						  if(g ==efg) {
							  inGroup =true;
							  annotationList.add(new Object[] {ef,m});
							  break;
						  }
					  }
				  }
			  }else {
				  annotationList.add(new Object[] {ef,m});
			  }
		  }
	  }
	  // 
	  Collections.sort(annotationList,new Comparator<Object[]>() {
		@Override
		public int compare(Object[] o1, Object[] o2) {
			
			return new Integer(((ExcelField)o1[0]).sort()).compareTo(
					new Integer((((ExcelField)o2[0]).sort())));
		}
		});
	  //
	 List<String> headerList = new ArrayList<String>();
	 for(Object[] os: annotationList) {
		 String t = ((ExcelField)os[0]).title();
		 if(type ==1) {
			 String[] ss = StringUtils.split(t,"**",2);
			 if(ss.length==2) {
				 t = ss[0];
			 }
		 }
		 headerList.add(t);
	 }
	  initialize(title,headerList);
  }
  //
  public ExportExcelUtil (String title,String[] headers) {
	  //initialize(title,new ArrayList().add(headers));
  }
  
  public ExportExcelUtil (String title,List<String> headerList) {
	  initialize(title,headerList);
  }
  public ExportExcelUtil(String title,int hSize){
  	//工作汇报
	  initializeTitle(title,hSize);
  }
  public void initialize(String title,List<String> headerList) {
	  this.wb= new SXSSFWorkbook(500);
	  this.sheet = wb.createSheet(title);
	  //this.styles = createStyles(wb); 
	  CellStyle style = wb.createCellStyle();
	  if(StringUtils.isNotBlank(title)) {
		  Row titleRow = sheet.createRow(rownum++);
		  titleRow.setHeightInPoints(30);
		  Cell titleCell = titleRow.createCell(0);
		  style.setAlignment(CellStyle.ALIGN_CENTER);
		  
		  Font titleFont = wb.createFont();
		  titleFont.setFontName("Arial");
		  titleFont.setFontHeightInPoints((short)16);//设置字号
		  titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		  style.setFont(titleFont);
		  
		  titleCell.setCellStyle(style);
		  titleCell.setCellValue(title);
		  
		  sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
				  titleRow.getRowNum(),titleRow.getRowNum(),headerList.size()-1));
	  }
	  if(headerList ==null) {
		  throw new RuntimeException("标题行不能为空!");
	  }
	  Row hreaderRow = sheet.createRow(rownum++);
	  hreaderRow.setHeightInPoints(16);
	  for(int i=0;i<headerList.size();i++) {
		  Cell cell = hreaderRow.createCell(i);
		  //cell.setCellStyle(styles.get("header"));
		  String[] ss =StringUtils.split(headerList.get(i),"**",2);
		  if(ss.length ==2) {
			  cell.setCellValue(ss[0]);
			  Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
					  new XSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
		  }else {
			 cell.setCellValue(headerList.get(i)); 
		  }  
		 sheet.autoSizeColumn(i);
	  }
	  for(int i=0;i<headerList.size();i++) {
		  int colWidth = sheet.getColumnWidth(i)*2;
		  sheet.setColumnWidth(i, colWidth<3000?3000:colWidth);
	  }
  }
  
  public void initializeTitle(String title,int hSize){
	  this.wb= new SXSSFWorkbook(500);
	  this.sheet = wb.createSheet(title);
	  //this.styles = createStyles(wb);
	  CellStyle style = wb.createCellStyle();
	  if(StringUtils.isNotBlank(title)) {
		  Row titleRow = sheet.createRow(rownum++);
		  titleRow.setHeightInPoints(30);
		  Cell titleCell = titleRow.createCell(0);
		  style.setAlignment(CellStyle.ALIGN_CENTER);

		  Font titleFont = wb.createFont();
		  titleFont.setFontName("Arial");
		  titleFont.setFontHeightInPoints((short)16);//设置字号
		  titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		  style.setFont(titleFont);

		  titleCell.setCellStyle(style);
		  titleCell.setCellValue(title);

		  sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
				  titleRow.getRowNum(),titleRow.getRowNum(),hSize));

	  }

  }
  // 创建样式
  private Map<String,CellStyle> createStyles(Workbook wb){
	  Map<String,CellStyle> styles= new HashMap<String,CellStyle>();
	  CellStyle style =wb.createCellStyle();
	  style.setAlignment(CellStyle.ALIGN_CENTER);
	  style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	  Font titleFont = wb.createFont();
	  titleFont.setFontName("Arial");
	  titleFont.setFontHeight((short)16);
	  titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	  style.setFont(titleFont);
	  styles.put("title", style);
	  
	  style = wb.createCellStyle();
	  style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	  style.setBorderRight(CellStyle.BORDER_THIN);
	  style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	  style.setBorderLeft(CellStyle.BORDER_THIN);
	  style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	  style.setBorderTop(CellStyle.BORDER_THIN);
	  style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	  style.setBorderBottom(CellStyle.BORDER_THIN);
	  style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	  Font dataFont = wb.createFont();
	  dataFont.setFontHeightInPoints((short) 10);
	  style.setFont(dataFont);
	  styles.put("data",style);
	  
	  style = wb.createCellStyle();
	  style.cloneStyleFrom(styles.get("data"));
	  style.setAlignment(CellStyle.ALIGN_CENTER);
	  styles.put("data1", style);
	  
	  style = wb.createCellStyle();
	  style.cloneStyleFrom(styles.get("data"));
	  style.setAlignment(CellStyle.ALIGN_CENTER);
	  styles.put("data2", style);
	  
	  style = wb.createCellStyle();
	  style.cloneStyleFrom(styles.get("data"));
	  style.setAlignment(CellStyle.ALIGN_CENTER);
	  styles.put("data3", style);
	  
	  style = wb.createCellStyle();
	  style.cloneStyleFrom(styles.get("data"));
	  style.setAlignment(CellStyle.ALIGN_CENTER);
	  style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
	  style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	  Font headerFont = wb.createFont();
	  headerFont.setFontName("Arial");
	  headerFont.setFontHeightInPoints((short) 10);
	  headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	  headerFont.setColor(IndexedColors.WHITE.getIndex());
	  style.setFont(headerFont);
	  styles.put("header",style);
	  
	  return styles;
  }
  
  public Row addRow() {
	  return sheet.createRow(rownum++);
  }
  
  public Cell addCell(Row row ,int column,Object val) {
	  return this.addCell(row, column,val,0,Class.class);
  }
  public Cell addAlignCenterCell(Row row,int column,Object val){
	  CellStyle style = wb.createCellStyle();
	  style.setAlignment(CellStyle.ALIGN_CENTER);
	  Cell cell = row.createCell(column);
	  cell.setCellValue((String) val);
	  cell.setCellStyle(style);
	  return cell;
  }
  public Cell addCell(Row row,int column,Object val,int align,Class<?> fieldType) {
	  Cell cell = row.createCell(column);
	  String cellFormatString ="@";
	  try {
		  if(val ==null) {
			  cell.setCellValue("");
		  }else if(fieldType != Class.class) {
			  cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
		  }else {
			 if(val instanceof String) {
			  cell.setCellValue((String) val);
			  }else if(val instanceof Integer) {
				  cell.setCellValue((Integer) val);
				  cellFormatString ="0";
			  }else if(val instanceof Long) {
				  cell.setCellValue((Long) val);
				  cellFormatString ="0";
			  }else if(val instanceof Double) {
				  cell.setCellValue((Double) val);
				  cellFormatString ="0.00";
			  }else if(val instanceof Float) {
				  cell.setCellValue((Float) val);
				  cellFormatString ="0.00";
			  }else if(val instanceof Date) {
				  cell.setCellValue((Date)val);
				  cellFormatString ="yyyy-MM-dd HH:mm";
			  }else {
				  cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
						  "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
			  }
		  }
//		 if(val != null) {
//			 CellStyle style=styles.get("data_colunm_"+column);
//			 if(style == null) {
//				 style = wb.createCellStyle();
//				 style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
//				 style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
//				 styles.put("data_column_"+column, style);
//			 }
//			 cell.setCellStyle(style);
//		 }
	  }catch(Exception e) {
		  cell.setCellValue(val.toString());
		  e.printStackTrace();
	  }
	  return cell;
  }

  public ExportExcelUtil customerizeMerge(int startRow,int endRow,int startCol,int endCol){
	  sheet.addMergedRegion(new CellRangeAddress(startRow,
			  endRow,startCol,endCol));
	  return this;
  }
  public ExportExcelUtil merge(String[] headers, List<Map<String, String>> dataset, String[] mergeColumns) {
	  CellStyle style = wb.createCellStyle();
	  style.setAlignment(CellStyle.ALIGN_CENTER);
	  List<PoiModel> poiModels = new ArrayList<PoiModel>();
	  Iterator<Map<String, String>> iterator = dataset.iterator();
	  int index = 2;    //这里2是从excel的第二行开始，第一、二行已经塞入标题了
	  while (iterator.hasNext()) {
		  Row row = sheet.createRow(index);
		  // 取得当前这行的map，该map中以key，value的形式存着这一行值
		  Map<String, String> map = iterator.next();
		  // 循环列数，给当前行塞值
		  for (int i = 0; i < headers.length; i++) {
			  String old = "";
			  // old存的是上一行统一位置的单元的值，第一行是最上一行了，所以从第二行开始记
			  if (index > 2) {
				  old = poiModels.get(i) == null ? "" : poiModels.get(i).getContent();
			  }

			  String value = map.get(headers[i]);
			  CellRangeAddress cra = null;
			  // 循环需要合并的列
			  for (int j = 0; j < mergeColumns.length; j++) {
				  PoiModel poiModel = null;
				  if (index == 2) {
					  poiModel = new PoiModel();
					  poiModel.setOldContent(value);
					  poiModel.setContent(value);
					  poiModel.setRowIndex(2);
					  poiModel.setCellIndex(i);
					  poiModels.add(poiModel);
					  old = value;
					  break;
				  }
				  poiModel = poiModels.get(i);

				  int rowStartIndex = poiModel.getRowIndex();
				  int rowEndIndex = index - 1;
				  int cellIndex = poiModel.getCellIndex();
				  String content = poiModel.getContent();
				  String preOldContent = poiModels.get(0).getOldContent();
				  String preValue = map.get(headers[0]);
				  Boolean isHeaderEquals = mergeColumns[j].equals(headers[i]);

				  if (i == 0 && isHeaderEquals && !content.equals(value)) {
					  if (rowStartIndex != rowEndIndex) {
						  cra = new CellRangeAddress(rowStartIndex, rowEndIndex, cellIndex, cellIndex);
						  sheet.addMergedRegion(cra);
					  }
					  // 重新记录该列的内容为当前内容，行标记改为当前行标记
					  poiModel.setContent(value);
					  poiModel.setRowIndex(index);
					  poiModel.setCellIndex(i);
				  } else if (i > 0 && isHeaderEquals) {
					  if (!content.equals(value) || (content.equals(value) && !preOldContent.equals(preValue))) {
						  if (rowStartIndex != rowEndIndex) {
							  cra = new CellRangeAddress(rowStartIndex, rowEndIndex, cellIndex, cellIndex);
							  sheet.addMergedRegion(cra);
						  }
						  poiModels.get(i).setContent(value);
						  poiModels.get(i).setRowIndex(index);
						  poiModels.get(i).setCellIndex(i);
					  }
				  }
				  if (isHeaderEquals && index == (dataset.size()+1)) {
					  if (i == 0) {
						  if (content.equals(value)) {
							  cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);
							  sheet.addMergedRegion(cra);
						  }
					  } else if (i > 0) {
						  if (content.equals(value) && preOldContent.equals(preValue)) {
							  cra = new CellRangeAddress(rowStartIndex, index, cellIndex, cellIndex);
							  sheet.addMergedRegion(cra);
						  }
					  }
				  }
			  }
			  Cell cell = row.createCell(i, Cell.CELL_TYPE_STRING);
			  cell.setCellValue(value);
			  // 在每一个单元格处理完成后，把这个单元格内容设置为old内容
			  poiModels.get(i).setOldContent(old);
		  }
		  
		  index++;
	  }
	  return this;
  }

  public ExportExcelUtil write(OutputStream os) throws IOException {
	  wb.write(os);
	  return this;
  }
  
  public ExportExcelUtil write(HttpServletResponse response,String fileName) throws IOException {
	  response.setStatus(HttpServletResponse.SC_OK);
	  response.setContentType("application/octet-stream");
      response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
      response.addHeader("Pargam", "no-cache");
      response.addHeader("Cache-Control", "no-cache");
      OutputStream os=response.getOutputStream();
      wb.write(os);
      os.flush();
      os.close();
	  return this;
  }
  
  public ExportExcelUtil writeFile(String fileName) throws IOException {
	 FileOutputStream os =new FileOutputStream(fileName);
	 this.write(os);
	 os.flush();
	 os.close();
	 return this;
  }
  
  public ExportExcelUtil  dispose() {
	  wb.dispose();
	  return this;
  }
  
  public CellStyle createCellStyle() {
	  return wb.createCellStyle();
  }
  
 	public static void main1(String[] args) throws Exception {
	 List<Map<String, String>> list = new ArrayList<>();
	 for(int i =1;i<40;i++){
		 TestMapDTO dto = new TestMapDTO();
		 Map<String,String> map = new HashMap<>();
		 if (i<=10) {
			 dto.setRn(10);
			 if(i<=(5))
				 dto.setSupvLvlId("a");
			 else
				 dto.setSupvLvlId("b");
		 }
		 else if (i>10 && i<=20) {
			 dto.setRn(20);
			 if(i<=(15))
				 dto.setSupvLvlId("A");
			 else
				 dto.setSupvLvlId("B");
		 }
		 else if (i>20 && i<=30){
			 dto.setRn(30);
			 if(i<=(25))
				 dto.setSupvLvlId("A");
			 else
				 dto.setSupvLvlId("B");
		 }

		 else {
			 dto.setRn(40);
			 if (i <= (35))
				 dto.setSupvLvlId("A");
			 else
				 dto.setSupvLvlId("B");
		 }
		 dto.setEmplid("emplid"+i);
		 dto.setName("name"+i);
		 dto.setPeriodid("per" + i);
		 dto.setSecAgency("per"+i);
		 map = MapUtil.getNamValMap(dto,false);
		 list.add(map);
	 }
	 List<String> headerList = new ArrayList<String>();
	 headerList.add("序号");
	 headerList.add("职等");
	 headerList.add("员工id");
	 headerList.add("姓名");
	 headerList.add("汇报期间");
	 headerList.add("上级二级机构");

	 String[] headers = new String[]{"rn", "emplid", "supvLvlId", "periodid","secAgency","name"};
	 String[] regions = new String[]{"rn"};
	 ExportExcelUtil ee=new ExportExcelUtil("表格标题",headerList);
	 ee.merge(headers, list, regions);
	 ee.writeFile("e:\\b.xlsx");
/*	 List<String> headerList = new ArrayList<String>();
	 for(int i=1;i<=10;i++) {
		 headerList.add("表头"+i);
	 }
	 
	 List<String> dataRowList= new ArrayList<String>();
	 for(int i=1;i<=headerList.size();i++) {
		 dataRowList.add("数据"+i);
	 }
	 List<List<String>> dataList= new ArrayList<List<String>>();
	 for(int i=1;i<=100;i++) {
		 dataList.add(dataRowList);
	 }
	 
	 ExportExcelUtil ee=new ExportExcelUtil("表格标题",headerList); 
	 for(int i=0;i<dataList.size();i++) {
		 Row row = ee.addRow();
		 for(int j=0;j<dataList.get(i).size();j++) {
			 ee.addCell(row, j, dataList.get(i).get(j));
		 }
	 }
	 ee.writeFile("f:b.xlsx");*/
}
	public static void main(String[] args) throws Exception {
		ExportExcelUtil ee = null;
		ee = new ExportExcelUtil("工作汇报明细表", 8);
		Row row = ee.addRow();
		int j=0;
		for (int k = 0; k < 2; k++) {
			ee.addCell(row, j++, "a");
			ee.addCell(row, j++, "b");
			ee.addCell(row, j++, "a");
			if (k==0){
				ee.addAlignCenterCell(row, j++, "一月");
				ee.addAlignCenterCell(row, j++, "一月");
				ee.addAlignCenterCell(row, j++, "一月");
				ee.addAlignCenterCell(row, j++, "二月");
				ee.addAlignCenterCell(row, j++, "二月");
				ee.addAlignCenterCell(row, j++, "二月");
			} else{
				ee.addCell(row, j++, "一1月");
				ee.addCell(row, j++, "一1月");
				ee.addCell(row, j++, "一1月");
				ee.addCell(row, j++, "二2月");
				ee.addCell(row, j++, "二2月");
				ee.addCell(row, j++, "二2月");
			}

			row = ee.addRow();
			j=0;
		}
		ee.customerizeMerge(1,2,0,0);
		ee.customerizeMerge(1,1,3,5);
		ee.writeFile("e:\\d.xlsx");
	}
}

这个类只是作为测试用的。

public class TestMapDTO {
    private Integer rn;
    private String emplid;
    private String supvLvlId;
    private String periodid;
    private String secAgency;
    private String name;

    public Integer getRn() {
        return rn;
    }

    public void setRn(Integer rn) {
        this.rn = rn;
    }

    public String getEmplid() {
        return emplid;
    }

    public void setEmplid(String emplid) {
        this.emplid = emplid;
    }

    public String getSupvLvlId() {
        return supvLvlId;
    }

    public void setSupvLvlId(String supvLvlId) {
        this.supvLvlId = supvLvlId;
    }

    public String getPeriodid() {
        return periodid;
    }

    public void setPeriodid(String periodid) {
        this.periodid = periodid;
    }

    public String getSecAgency() {
        return secAgency;
    }

    public void setSecAgency(String secAgency) {
        this.secAgency = secAgency;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

运行util的main方法即可。里面有两个方法，一个main一个main1，自己改为切换为main单独执行即可。把这个工具作为工具类后面就容易运用了。如果发现调用报错，看看是不是类没有导全，上面给的pom是主要的文件，我我已经给了import了，缺少什么自己百度一下来源于哪里。