java POI操作Excel代码收藏 （包括标题垂直水平居中）

1.HSSF 2003版Excel导出

package com.hwt.glmf.common;

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

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.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 导出Excel公共方法
 * @version 1.0
 * 
 * @author wangcp
 *
 */
public class ExportExcel extends BaseAction {
	
	//显示的导出表的标题
	private String title;
	//导出表的列名
	private String[] rowName ;
	
	private List<Object[]>  dataList = new ArrayList<Object[]>();
	
	HttpServletResponse  response;
	
	//构造方法，传入要导出的数据
	public ExportExcel(String title,String[] rowName,List<Object[]>  dataList){
		this.dataList = dataList;
		this.rowName = rowName;
		this.title = title;
	}
			
	/*
	 * 导出数据
	 * */
	public void export() throws Exception{
		try{
			HSSFWorkbook workbook = new HSSFWorkbook();						// 创建工作簿对象
			HSSFSheet sheet = workbook.createSheet(title);		 			// 创建工作表
			
			// 产生表格标题行
	        HSSFRow rowm = sheet.createRow(0);
	        HSSFCell cellTiltle = rowm.createCell(0);
	        
	        //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
	        HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
	        HSSFCellStyle style = this.getStyle(workbook);					//单元格样式对象
	        
	        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));  
	        cellTiltle.setCellStyle(columnTopStyle);
	        cellTiltle.setCellValue(title);
    		
			// 定义所需列数
			int columnNum = rowName.length;
			HSSFRow rowRowName = sheet.createRow(2);				// 在索引2的位置创建行(最顶端的行开始的第二行)
			
			// 将列头设置到sheet的单元格中
			for(int n=0;n<columnNum;n++){
				HSSFCell  cellRowName = rowRowName.createCell(n);				//创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);				//设置列头单元格的数据类型
    			HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
    			cellRowName.setCellValue(text);									//设置列头单元格的值
    			cellRowName.setCellStyle(columnTopStyle);						//设置列头单元格样式
    		}
			
    		//将查询出的数据设置到sheet对应的单元格中
			for(int i=0;i<dataList.size();i++){
				
				Object[] obj = dataList.get(i);//遍历每个对象
				HSSFRow row = sheet.createRow(i+3);//创建所需的行数
				
				for(int j=0; j<obj.length; j++){
					HSSFCell  cell = null;   //设置单元格的数据类型
					if(j == 0){
						cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellValue(i+1);	
					}else{
						cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
						if(!"".equals(obj[j]) && obj[j] != null){
							cell.setCellValue(obj[j].toString());						//设置单元格的值
						}
					}
					cell.setCellStyle(style);									//设置单元格样式
				}
			}
			//让列宽随着导出的列长自动适应
			for (int colNum = 0; colNum < columnNum; colNum++) {
	            int columnWidth = sheet.getColumnWidth(colNum) / 256;
	            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
	                HSSFRow currentRow;
	                //当前行未被使用过
	                if (sheet.getRow(rowNum) == null) {
	                    currentRow = sheet.createRow(rowNum);
	                } else {
	                    currentRow = sheet.getRow(rowNum);
	                }
	                if (currentRow.getCell(colNum) != null) {
	                    HSSFCell currentCell = currentRow.getCell(colNum);
	                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
	                        int length = currentCell.getStringCellValue().getBytes().length;
	                        if (columnWidth < length) {
	                            columnWidth = length;
	                        }
	                    }
	                }
	            }
	            if(colNum == 0){
	            	sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
	            }else{
	            	sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
	            }
	        }
			
			if(workbook !=null){
				try
			    {
			        String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
			        String headStr = "attachment; filename=\"" + fileName + "\"";
			        response = getResponse();
			        response.setContentType("APPLICATION/OCTET-STREAM");
			        response.setHeader("Content-Disposition", headStr);
			        OutputStream out = response.getOutputStream();
			        workbook.write(out);
			    }
			    catch (IOException e)
			    {
			        e.printStackTrace();
			    }
			}

		}catch(Exception e){
			e.printStackTrace();
		}
		
	}
	
	/* 
	 * 列头单元格样式
	 */    
  	public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
  		
  		  // 设置字体
    	  HSSFFont font = workbook.createFont();
    	  //设置字体大小
    	  font.setFontHeightInPoints((short)11);
    	  //字体加粗
    	  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    	  //设置字体名字 
    	  font.setFontName("Courier New");
    	  //设置样式; 
    	  HSSFCellStyle style = workbook.createCellStyle();
    	  //设置底边框; 
    	  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	  //设置底边框颜色;  
    	  style.setBottomBorderColor(HSSFColor.BLACK.index);
    	  //设置左边框;   
    	  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	  //设置左边框颜色; 
    	  style.setLeftBorderColor(HSSFColor.BLACK.index);
    	  //设置右边框; 
    	  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	  //设置右边框颜色; 
    	  style.setRightBorderColor(HSSFColor.BLACK.index);
    	  //设置顶边框; 
    	  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	  //设置顶边框颜色;  
    	  style.setTopBorderColor(HSSFColor.BLACK.index);
    	  //在样式用应用设置的字体;  
    	  style.setFont(font);
    	  //设置自动换行; 
    	  style.setWrapText(false);
    	  //设置水平对齐的样式为居中对齐;  
    	  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	  //设置垂直对齐的样式为居中对齐; 
    	  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	  
    	  return style;
    	  
  	}
  	
  	/*  
	 * 列数据信息单元格样式
	 */  
  	public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
	  	  // 设置字体
	  	  HSSFFont font = workbook.createFont();
	  	  //设置字体大小
	  	  //font.setFontHeightInPoints((short)10);
	  	  //字体加粗
	  	  //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	  	  //设置字体名字 
	  	  font.setFontName("Courier New");
	  	  //设置样式; 
	  	  HSSFCellStyle style = workbook.createCellStyle();
	  	  //设置底边框; 
	  	  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	  	  //设置底边框颜色;  
	  	  style.setBottomBorderColor(HSSFColor.BLACK.index);
	  	  //设置左边框;   
	  	  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	  	  //设置左边框颜色; 
	  	  style.setLeftBorderColor(HSSFColor.BLACK.index);
	  	  //设置右边框; 
	  	  style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	  	  //设置右边框颜色; 
	  	  style.setRightBorderColor(HSSFColor.BLACK.index);
	  	  //设置顶边框; 
	  	  style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	  	  //设置顶边框颜色;  
	  	  style.setTopBorderColor(HSSFColor.BLACK.index);
	  	  //在样式用应用设置的字体;  
	  	  style.setFont(font);
	  	  //设置自动换行; 
	  	  style.setWrapText(false);
	  	  //设置水平对齐的样式为居中对齐;  
	  	  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	  	  //设置垂直对齐的样式为居中对齐; 
	  	  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	  	 
	  	  return style;
  	
  	}
}

2.XSSF 2007版Excel导出

package com.nfky.dmt.common.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

/**
 * excel导出公共类
 * 
 * @author lanwx
 * @time 2017年8月24日上午9:07:14
 */
public class ExcelUtils {

	private ExcelUtils(){}
	
	public static void exportExcel(String sheetTitle, Map<String, String> headerMap, List<Map<String, Object>> dataset, OutputStream out) {
		export(sheetTitle,headerMap,dataset,out);
	}
	
	/**
	 * excel导出
	 * 
	 * @param sheetTitle
	 * @param headerMap  key 是英文名  value是中文名
	 * @param dataset  key对应dataset中Map的key
	 * @param out
	 */
	private static void export(String sheetTitle, Map<String, String> headerMap, List<Map<String, Object>> dataset, OutputStream out){
		SXSSFWorkbook wb = new SXSSFWorkbook(-1);
		// 使用gzip压缩
		wb.setCompressTempFiles(true);
		
		CellStyle style1 = wb.createCellStyle();
		style1.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
		style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// 水平居中
		style1.setAlignment((short) 2);
		//垂直居中
		style1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style1.setBorderBottom(CellStyle.BORDER_THIN);
		style1.setBorderRight(CellStyle.BORDER_THIN);
		
		CellStyle style2 = wb.createCellStyle();
		style2.setAlignment((short) 2);
		style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style2.setBorderBottom(CellStyle.BORDER_THIN);
		style2.setBorderRight(CellStyle.BORDER_THIN);
		
		
		CellStyle style3 = wb.createCellStyle();
		// 左对齐
		style3.setAlignment((short) 1);
		style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style3.setBorderBottom(CellStyle.BORDER_THIN);
		style3.setBorderRight(CellStyle.BORDER_THIN);
		
		Sheet sh = wb.createSheet(sheetTitle);
		// 设置表头
		int index = 0;
		Row row = sh.createRow(index++);
		row.setHeight((short) (25 * 20));
		Cell cell = row.createCell(0);
		// 设置风格
		cell.setCellStyle(style1);
		XSSFRichTextString text = new XSSFRichTextString("业务数据");
		cell.setCellValue(text);
		// 因为有多列合并 所以需要对每一列都进行设置style
		for(int i=1,n=headerMap.size();i<n;i++){
			cell = row.createCell(i);
			cell.setCellStyle(style1);
		}
		// 设置宽度
		sh.addMergedRegion(new CellRangeAddress(0, 0, 0, headerMap.size() - 1));
		// 第二行开始导入数据
		row = sh.createRow(index++);
		row.setHeight((short) (25 * 20));
		int c = 0 ;
		for (Entry<String, String> entry:headerMap.entrySet()) {
			// 设置每列的宽度
			sh.setColumnWidth(c, 40 * 256);
			Cell cellHeader = row.createCell(c++);
			cellHeader.setCellStyle(style2);
			text = new XSSFRichTextString(entry.getValue());
			cellHeader.setCellValue(text);
		}
		Cell rowCell = null;
		for (int i = 0, n = dataset.size(); i < n; i++) {
			row = sh.createRow(index++);
			c=0;
			for (Entry<String, String> entry:headerMap.entrySet()) {
				rowCell = row.createCell(c++);
				rowCell.setCellStyle(style3);
				rowCell.setCellValue((String) dataset.get(i).get(entry.getKey()));
			}
		}
		try {
			wb.write(out);
			out.close();
			wb.dispose();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}