java 生成Excel工具类

记录下自己写好的工具类，便于以后参考。

package salary.client.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.swing.filechooser.FileSystemView;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
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.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * 定义处理Excel的各种操作
 * 
 * @author 
 * 
 * @version 1.0 2017-5-27
 * 
 * @since JDK 1.8
 * 
 * @category 
 * */
public class ExcelUtil {
	
	//定义初始化属性(可设定、获取)
	private String fileName; //文件名称
	private String path; 	//文件路径
	private String sheet0Name; //第一页sheet的名称
	private String version; //版本
	private int rowHeight; //行高
	private int columnWidth; //列宽
	
	public ExcelUtil(){
		this.sheet0Name = "Sheet1";
	}
	
	/**
	 * 构造函数
	 * */
	public ExcelUtil(String sheet0Name){
		this.sheet0Name = sheet0Name;
	}
	
	/**
	 * 初始化构造函数
	 * 
	 * @param fileName 文件名称。默认为日期。导入可空，导出非空
	 * 
	 * @param path 文件路径。默认桌面目录。
	 * 
	 * @param sheet0Name 第一sheet页。默认Sheet1
	 * 
	 * @param version 版本。仅支持[HSSF/XSSF]。默认XSSF
	 * */
	public ExcelUtil(String fileName,String path,String sheet0Name,String version,int rowHeight,int columnWidth){
		if(checkStrNull(fileName)){
			Date date = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
			this.fileName =  sdf.format(date);  
		}else{
			this.fileName = fileName;
		}
		if(checkStrNull(path)){
			File desktopDir = FileSystemView.getFileSystemView().getHomeDirectory();
			String desktopPath = desktopDir.getAbsolutePath();
			this.path = desktopPath + "/";
		}else{
			this.path = path;
		}
		if(checkStrNull(sheet0Name)){
			this.sheet0Name = "Sheet1";
		}else{
			this.sheet0Name = sheet0Name;
		}
		if(checkStrNull(version)){
			this.version = "XSSF"; 
		}else{
			this.version = version; 
		}
		if(rowHeight <= 0){
			this.rowHeight = 17;
		}else{
			this.rowHeight = rowHeight;
		}
		if(columnWidth <= 0){
			this.columnWidth = 20;
		}else{
			this.columnWidth = columnWidth;
		}
	}

	/**
	 * 导出Excel(97、03 .xls)格式的Excel，适合数据量少的数据
	 * 
	 * @param titles 标题集合
	 * 
	 * @param data 数据
	 * 
	 * @param output 输出流(写入对象)
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void exportExcel(String[] titles,List<LinkedHashMap<String, Object>> data,OutputStream output){
		HSSFWorkbook work = null;
		try {
			//如果没有任何数据，则退出
			if((titles == null || titles.length == 0) && (data == null || data.size() == 0)){
				return;
			}
			
			//创建HSSFWorkbook对象(excel的文档对象)  
			work = new HSSFWorkbook();
			
			//数据为空则说明为只导出标题
			if(data == null || data.size() == 0){
				//建立第一个sheet对象（excel的表单） 
				HSSFSheet sheet = work.createSheet(this.sheet0Name);
				
				//设定默认的行高、列宽
				sheet.setDefaultRowHeightInPoints(this.rowHeight);
				sheet.setDefaultColumnWidth(this.columnWidth);
				
				//锁定首行
				sheet.createFreezePane(0, 1, 0, 1);
				
				//单元格格式为文本
				CellStyle cellTextStyle = work.createCellStyle();  
				DataFormat format = work.createDataFormat();  
				cellTextStyle.setDataFormat(format.getFormat("@"));
				
				//设定样式
				CellStyle cellStyle = this.getDefaultCellStyle(work, true);
				
				//创建第一个行
				HSSFRow rowTitle = sheet.createRow(0);
				//标题
				if(titles !=  null && titles.length > 0){
					for (int i = 0; i < titles.length; i++) {
						//生成标题列
						HSSFCell cell = rowTitle.createCell(i);
						this.setObjectToCell(cell,titles[i]);
						cell.setCellStyle(cellStyle);
						//设置整列格式
						sheet.setDefaultColumnStyle(i, cellTextStyle);  
					}
				}
			}else{
				int sheetIdx = 1;
				int rowIdx = 0;
				HSSFSheet sheet = null;
				for(Iterator iterator = data.iterator(); iterator.hasNext();){
					//如果是第一行或超过65535行，新一页
					if(rowIdx == 60000 || rowIdx == 0){
						if(rowIdx == 0){
							sheet = work.createSheet(this.sheet0Name);
						}else{
							sheet = work.createSheet("Sheet" + (sheetIdx + 1) );
							rowIdx = 0;
						}
						
						sheetIdx++;
						
						//锁定首行
						sheet.createFreezePane(0, 1, 0, 1);
						
						//设定默认的行高、列宽
						sheet.setDefaultRowHeightInPoints(this.rowHeight);
						sheet.setDefaultColumnWidth(this.columnWidth);
						
						//单元格格式为文本
						CellStyle cellTextStyle = work.createCellStyle();  
						DataFormat format = work.createDataFormat();  
						cellTextStyle.setDataFormat(format.getFormat("@"));
						
						//设定样式
						CellStyle cellStyle = this.getDefaultCellStyle(work, true);
						
						//创建第一个内容
						HSSFRow rowTitle = sheet.createRow(rowIdx);
						for (int i = 0; i < titles.length; i++) {
							//生成标题列
							HSSFCell cell = rowTitle.createCell(i);
							this.setObjectToCell(cell,titles[i]);
							cell.setCellStyle(cellStyle);
							//设置整列格式
							sheet.setDefaultColumnStyle(i, cellTextStyle);  
						}
					}else{
						HSSFRow rowData = sheet.createRow(rowIdx);
						//设定样式
						CellStyle cellStyle = this.getDefaultCellStyle(work, false);
						
						int cellIdx = 0;
						Map<String, Object> map = (Map<String, Object>) iterator.next();
						for(Object obj : map.values()){
							HSSFCell cell = rowData.createCell(cellIdx);
							this.setObjectToCell(cell,obj);
							cell.setCellStyle(cellStyle);
							cellIdx++;
						}
					}
					
					rowIdx++;
				}
			}
			
			//写入输出流
			work.write(output);
			output.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(work != null){
					work.close();
				}
				if(output != null){
					output.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 导出Excel(07 .xlsx)格式的Excel，适合数据量多的数据
	 * 
	 * @param titles 标题集合
	 * 
	 * @param data 数据
	 * 
	 * @param output 输出流(写入对象)
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void exportExcelX(String[] titles,List<LinkedHashMap<String, Object>> data,OutputStream output){
		SXSSFWorkbook work = null;
		try {
			//如果没有任何数据，则退出
			if((titles == null || titles.length == 0) && (data == null || data.size() == 0)){
				return;
			}
			
			//创建HSSFWorkbook对象(excel的文档对象)  
			work = new SXSSFWorkbook();
			
			//数据为空则说明为只导出标题
			if(data == null || data.size() == 0){
				//建立第一个sheet对象（excel的表单） 
				SXSSFSheet sheet = work.createSheet(this.sheet0Name);
				
				//设定默认的行高、列宽
				sheet.setDefaultRowHeightInPoints(this.rowHeight);
				sheet.setDefaultColumnWidth(this.columnWidth);
				
				//锁定首行
				sheet.createFreezePane(0, 1, 0, 1);
				
				//单元格格式为文本
				CellStyle cellTextStyle = work.createCellStyle();  
				DataFormat format = work.createDataFormat();  
				cellTextStyle.setDataFormat(format.getFormat("@"));
				
				//设置样式
				CellStyle cellStyle = this.getDefaultCellStyle(work, true);
				
				//创建第一个行
				SXSSFRow rowTitle = sheet.createRow(0);
				//标题
				if(titles !=  null && titles.length > 0){
					for (int i = 0; i < titles.length; i++) {
						//生成标题列
						SXSSFCell cell = rowTitle.createCell(i);
						this.setObjectToCell(cell,titles[i]);
						cell.setCellStyle(cellStyle);
						
						//设置整列格式
						sheet.setDefaultColumnStyle(i, cellTextStyle);  
						
						//自动列宽
						//sheet.trackAllColumnsForAutoSizing(); 
						//sheet.autoSizeColumn(i);
					}
				}
			}else{
				int sheetIdx = 1;
				int rowIdx = 0;
				SXSSFSheet sheet = null;
				for(Iterator iterator = data.iterator(); iterator.hasNext();){
					//如果是第一行或超过65535行，新一页
					if(rowIdx == 60000 || rowIdx == 0){
						if(rowIdx == 0){
							sheet = work.createSheet(this.sheet0Name);
						}else{
							sheet = work.createSheet("Sheet" + (sheetIdx + 1) );
							rowIdx = 0;
						}
						sheetIdx++;
						//锁定首行
						sheet.createFreezePane(0, 1, 0, 1);
						
						//设定默认的行高、列宽
						sheet.setDefaultRowHeightInPoints(this.rowHeight);
						sheet.setDefaultColumnWidth(this.columnWidth);
						//单元格格式为文本
						CellStyle cellTextStyle = work.createCellStyle();  
						DataFormat format = work.createDataFormat();  
						cellTextStyle.setDataFormat(format.getFormat("@"));
						
						//设置样式
						CellStyle cellStyle = this.getDefaultCellStyle(work, true);
						
						//创建第一个行
						SXSSFRow rowTitle = sheet.createRow(rowIdx);
						for (int i = 0; i < titles.length; i++) {
							//生成标题列
							SXSSFCell cell = rowTitle.createCell(i);
							this.setObjectToCell(cell,titles[i]);
							cell.setCellStyle(cellStyle);
							
							//设置整列格式
							sheet.setDefaultColumnStyle(i, cellTextStyle);  
						}
					}else{
						SXSSFRow rowData = sheet.createRow(rowIdx);
						CellStyle cellStyle = this.getDefaultCellStyle(work, false);
						
						int cellIdx = 0;
						Map<String, Object> map = (Map<String, Object>) iterator.next();
						for(Object obj : map.values()){
							SXSSFCell cell = rowData.createCell(cellIdx);
							this.setObjectToCell(cell,obj);
							cell.setCellStyle(cellStyle);
							cellIdx++;
						}
					}
					
					rowIdx++;
				}
			}
			
			//写入输出流
			work.write(output);
			output.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(work != null){
					work.close();
				}
				if(output != null){
					output.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 导出Excel(07 .xlsx)格式的Excel，适合多页在一个Excel文件中 <br>
	 * 限制：每一页数据不能超过65535行。集合必须顺序相同
	 * 
	 * @param sheetNameList sheet页名字合集
	 * 
	 * @param titleList 标题数组集合
	 * 
	 * @param dataList 数据集合
	 * 
	 * @param output 输出流(写入对象)
	 * 
	 * @author Sjc add 2017-6-8
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked"})
	public void exportSheetsMergedExcelX(LinkedList<String> sheetNameList, LinkedList<String[]> titleList,
			LinkedList<List<LinkedHashMap<String, Object>>> dataList, OutputStream output){
		SXSSFWorkbook work = null;
		try {
			//如果没有任何数据，则退出
			if((sheetNameList == null || sheetNameList.size() == 0)
					&& (titleList == null || titleList.size() == 0)
					&& (dataList == null || dataList.size() == 0)){
				return;
			}
			//如果要写3页，而数据或标题不为3，则直接退出
			if(sheetNameList.size() != titleList.size() || sheetNameList.size() != dataList.size()){
				return;
			}
			
			//创建HSSFWorkbook对象(excel的文档对象)  
			work = new SXSSFWorkbook();
			
			//创建Sheet对象并写入数据
			for(int idx = 0 ; idx < sheetNameList.size(); idx++){
				//获取SheetName、标题、数据
				String sheetName = sheetNameList.get(idx);
				String[] titles = titleList.get(idx);
				List<LinkedHashMap<String, Object>> data = dataList.get(idx);
				int rowIdx = 0;
				
				//为空处理
				if(this.checkStrNull(sheetName)){
					sheetName = "Sheet" + (idx + 1);
				}
				
				//创建Sheet对象
				SXSSFSheet sheet = work.createSheet(sheetName);
				//设定默认的行高、列宽
				sheet.setDefaultRowHeightInPoints(this.rowHeight);
				sheet.setDefaultColumnWidth(this.columnWidth);
				
				//单元格格式为文本
				CellStyle cellTextStyle = work.createCellStyle();  
				DataFormat format = work.createDataFormat();  
				cellTextStyle.setDataFormat(format.getFormat("@"));
				
				//创建标题
				if(titles != null && titles.length != 0){
					//创建第一个行
					SXSSFRow rowTitle = sheet.createRow(0);
					rowIdx++;
					
					//锁定首行
					sheet.createFreezePane(0, 1, 0, 1);
					
					//标题
					for (int i = 0; i < titles.length; i++) {
						//生成标题列
						SXSSFCell cell = rowTitle.createCell(i);
						this.setObjectToCell(cell,titles[i]);
						
						//自动列宽
						//sheet.trackAllColumnsForAutoSizing(); 
						//sheet.autoSizeColumn(i);
						
						//设置整列格式
						sheet.setDefaultColumnStyle(i, cellTextStyle);  
						
						//设置默认样式
						//this.setDefaultCellStyle(work,cell,null,true,titles[i]);
					}
				}
				
				//写入数据
				if(data != null && data.size() > 0){
					for(Iterator iterator = data.iterator(); iterator.hasNext();){
						//创建数据行
						SXSSFRow rowData = sheet.createRow(rowIdx);
						Map<String, Object> map = (Map<String, Object>) iterator.next();
						int cellIdx = 0;
						for(Object obj : map.values()){
							SXSSFCell cell = rowData.createCell(cellIdx);
							this.setObjectToCell(cell,obj);
							
							//设置默认样式
							//this.setDefaultCellStyle(work,cell,null,false,obj);
							
							cellIdx++;
						}
						
						rowIdx++;
					}
				}
				
			}
			
			//写入输出流
			work.write(output);
			output.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(work != null){
					work.close();
				}
				if(output != null){
					output.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 导出Excel(07 .xlsx)格式的Excel	(参考版)<br>
	 * 
	 * 适合指定格式，sheet就一页。先写数据后合并，所以要注意坐标,坐标从0开始算
	 * 
	 * 
	 * @param xyv 写入数据。key必须：rowX，cellY，value
	 * 
	 * @param mergeds 合并数据。key必须：startRow，endRow，startColumn，endColumn
	 * 
	 * @param output 输出流(写入对象)
	 * */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void exportExcelXByFormat(List<Map<String,Object>> xyv, List<Map<String,Integer>> mergeds, OutputStream output){
		SXSSFWorkbook work = null;
		try {
			//如果没有任何数据，则退出
			if((xyv == null || xyv.size() == 0) && (mergeds == null || mergeds.size() == 0)){
				return;
			}
			
			//创建HSSFWorkbook对象(excel的文档对象)  
			work = new SXSSFWorkbook();
			SXSSFSheet sheet = work.createSheet(this.sheet0Name);
			
			//先构建一个20X20的列。然后在里面操作。不然写入会报空异常。运用到业务时先写好数据在sheet中
			for (int i = 0; i < 20; i++) {
				SXSSFRow row = sheet.createRow(i);
				for (int j = 0; j < 20; j++) {
					SXSSFCell cell = row.createCell(j);
					cell.setCellValue("");
				}
			}
			
			//写入数据，样式根据需求自己新写
			if(xyv != null && xyv.size() > 0){
				for (Iterator iterator = xyv.iterator(); iterator.hasNext();) {
					Map<String, Object> map = (Map<String, Object>) iterator.next();
					//获取所需数据
					int rowX = Integer.parseInt(map.get("rowX").toString());
					int cellY = Integer.parseInt(map.get("cellY").toString());
					Object value = map.get("value");
					
					//写入
					this.setCellValueByXY(sheet, rowX, cellY, value);
				}
			}
			
			//进行合并
			if(mergeds != null && mergeds.size() > 0){
				for (Iterator iterator = mergeds.iterator(); iterator.hasNext();) {
					Map<String, Integer> map = (Map<String, Integer>) iterator.next();
					//获取所需数据
					int startRow = map.get("startRow");
					int endRow = map.get("endRow");
					int startColumn = map.get("startColumn");
					int endColumn = map.get("endColumn");
					
					//合并
					this.setMerged(sheet, startRow, endRow, startColumn, endColumn);
				}
			}
			
			//写入输出流
			work.write(output);
			output.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(work != null){
					work.close();
				}
				if(output != null){
					output.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 将Object写入cell中
	 * 
	 * @param cell
	 *  
	 * @param value
	 * */
	public void setObjectToCell(Cell cell,Object value){
		if(value instanceof Double){
			BigDecimal bd =  new BigDecimal(value.toString().trim());
			bd.setScale(8);//保留8位小数
        	cell.setCellValue(bd.stripTrailingZeros().toPlainString());
		}else if (value instanceof Date) {
            Date dateValue = (Date) value;
            //格式化
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            cell.setCellValue(sdf.format(dateValue));
        } else if (value instanceof Calendar) {
            Calendar calValue = (Calendar) value;
          //格式化
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            cell.setCellValue(sdf.format(calValue.getTime()));
        } else if (value instanceof Boolean) {
            boolean boolValue = Boolean.parseBoolean(value.toString().trim());
            cell.setCellValue(boolValue);
        } else if(value instanceof BigDecimal){
        	BigDecimal bd = (BigDecimal)value;
        	bd.setScale(8);//保留8位小数
        	cell.setCellValue(bd.stripTrailingZeros().toPlainString());
        } else{
        	//对空数据进行判断
        	if(checkStrNull(String.valueOf(value))){
        		cell.setCellValue("");
        	}else{
        		String strValue = value.toString().trim();
                cell.setCellValue(strValue);
        	}
        }
	}
	
	/**
	 * 判断字符串是否为空。
	 * 
	 * @param val 判断String
	 * 
	 * @return boolean true:为空。false:不为空
	 * */
	private boolean checkStrNull(String val){
		 if( null != val && !"".equals(val) && !"null".equals(val)){
			 return false;
		 }else{
			 return true;
		 }
	}
	
	
	/**
	 * 设定默认的列样式
	 * 
	 * @param work
	 * 
	 * @param fontFlag 是否设置字体。默认不设置
	 * */
	@SuppressWarnings("deprecation")
	public CellStyle getDefaultCellStyle(Workbook work,boolean fontFlag){
		//创建样式对象
		CellStyle cellStyle = work.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); 
		
	    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
	    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
	    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
	    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	    
	     if(fontFlag){
	    	Font fontStyle = work.createFont();
	    	fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置粗体    
			cellStyle.setFont(fontStyle);
	     }

	     return cellStyle;
	}
	
	/**
	 * 设置样式和字体(仅供参考)
	 * 
	 * @param work
	 * 
	 * @param cell
	 * 
	 * @param row
	 * */
	@SuppressWarnings("deprecation")
	public void setStyle(Workbook work, Cell cell,Row row){
		//创建样式对象
		CellStyle cellStyle = work.createCellStyle();
		//设置单元格的横向和纵向对齐方式
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);  
		
		 //设置填充方式(填充图案)  
	     cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);  
	      
	     //设置前景色  
	     cellStyle.setFillForegroundColor(HSSFColor.RED.index);  
	      
	     //设置背景颜色  
	     cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);  
	     cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	     
	     cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
	     cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
	     cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
	     cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	     
	     //设置自动换行
	     cellStyle.setWrapText(true);
	     
	     Font fontStyle = work.createFont();
		 //设置字体样式  
	     fontStyle.setFontName("宋体");    
	     //设置字体大小
	     fontStyle.setFontHeightInPoints((short)20);    
	     //设置字体颜色  
	     fontStyle.setColor(HSSFColor.BLUE.index);  
	     //设置粗体  
	     fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	     //设置斜体  
	     fontStyle.setItalic(true);  
	     //设置下划线  
	     fontStyle.setUnderline(HSSFFont.U_SINGLE);  
	     
	     cellStyle.setFont(fontStyle);  
	     
	     if(cell != null){
	    	 cell.setCellStyle(cellStyle); 
	     }
	     if(row != null){
	    	 row.setRowStyle(cellStyle);  
	     }
	}
	
	/**
	 *设置列宽
	 *
	 * @param sheet
	 * 
	 * @param cell
	 * 
	 * @param width
	 * */
	public void setColumnWidht(Sheet sheet,Cell cell,int width){
		sheet.setColumnWidth(cell.getColumnIndex(), width);
	}
	
	/**
	 * 合并单元格
	 * 
	 * @param sheet
	 * 
	 * @param startRow 起始行
	 * 
	 * @param endRow 截至行
	 * 
	 * @param startColumn 起始列
	 * 
	 * @param endColumn 截至列 
	 * */
	public void setMerged(Sheet sheet,int startRow,int endRow,int startColumn,int endColumn){
		sheet.addMergedRegion(new CellRangeAddress(startRow,endRow,startColumn,endColumn)); 
	}
	
	/**
	 * 向指定坐标写入的列写入值
	 * 
	 * @param sheet
	 * 
	 * @param rowX 行坐标
	 * 
	 * @param cellY 列坐标
	 *  
	 * @param value 写入对象
	 * 
	 * */
	public void setCellValueByXY(Sheet sheet,int rowX,int cellY,Object value){
		Row row = sheet.getRow(rowX);
		Cell cell = row.getCell(cellY);
		this.setObjectToCell(cell, value);
	}
	
	/**
	 * 导出标题到指定目录下的文件（参考）
	 * 
	 * @param titles 标题数据
	 * @param path 输出文件路径。如果不知道，则默认桌面
	 * @param fileName 文件名称(无后缀)
	 * @param sheet0Name 
	 * @param version HSSF/XSSF
	 * */
	public static void exportToExcel(String [] titles,List<LinkedHashMap<String, Object>> data,String fileName,String path,String sheet0Name,String version,int rowHeight,int columnWidth){
		try {
			//处理所有为空
			if(titles == null || titles.length == 0){
				titles = new String[]{"无标题数据"};
			}
			
			ExcelUtil excelUtil = new ExcelUtil(fileName, path, sheet0Name, version,rowHeight,columnWidth);
			if("XSSF".equals(excelUtil.getVersion())){
				FileOutputStream os = new FileOutputStream(excelUtil.getPath()+excelUtil.getFileName() + ".xlsx");
				excelUtil.exportExcelX(titles, data, os);
			}else{
				FileOutputStream os = new FileOutputStream(excelUtil.getPath()+excelUtil.getFileName()  + ".xls");
				excelUtil.exportExcel(titles, data, os);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 导出标题（参考）
	 * */
	public static void exportTitleToExcel(String [] titles,String fileName){
		ExcelUtil.exportToExcel(titles,null,fileName,null,null,null,0,0);
	}
	
	/**
	 * 导出标题和内容（参考）
	 * */
	public static void exportDataToExcel(String [] titles,List<LinkedHashMap<String, Object>> data,String fileName){
		ExcelUtil.exportToExcel(titles,data,fileName,null,null,null,0,0);
	}
	
	
	//测试
	public static void main(String[] args) {
		try {
			Date date = new Date();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
			
			/*String[] titles = new String[]{"姓名","性别","年龄","工资"};
			
			List<LinkedHashMap<String, Object>> list = new ArrayList<LinkedHashMap<String, Object>>();
			LinkedHashMap<String, Object> map1 = new LinkedHashMap<String,Object>();
			map1.put("name", "张三");
			map1.put("sex", "男");
			map1.put("age", 18);
			map1.put("may", 12.8);
			list.add(map1);
			LinkedHashMap<String, Object> map3 = new LinkedHashMap<String,Object>();
			map3.put("name", "李四");
			map3.put("sex", "男");
			map3.put("age", 20);
			map3.put("may", 5214545.021);
			list.add(map3);
			
			//ExcelUtil.exportTitleToExcel(titles,"测试","D:/");
			ExcelUtil.exportDataToExcel(null,null,sdf.format(date)+"_测试");
			*/
			
			
			ExcelUtil eu = new ExcelUtil("测试合并_" + sdf.format(date), null, null, null, 0, 0);
			FileOutputStream os = new FileOutputStream(eu.getPath() + eu.getFileName() + ".xlsx");
			
			List<Map<String, Object>> xyv = new ArrayList<Map<String, Object>>();
			Map<String, Object> map1 = new HashMap<String, Object>();	
			map1.put("rowX", 0); 
			map1.put("cellY", 0); 
			map1.put("value", "2017年6月");
			xyv.add(map1);
			Map<String, Object> adsfasdf = new HashMap<String, Object>();	
			adsfasdf.put("rowX", 2); 
			adsfasdf.put("cellY", 0); 
			adsfasdf.put("value", "部门是");
			xyv.add(adsfasdf);
			Map<String, Object> xfxfxf = new HashMap<String, Object>();	
			xfxfxf.put("rowX", 2); 
			xfxfxf.put("cellY", 1); 
			xfxfxf.put("value", "测试部门");
			xyv.add(xfxfxf);
			List<Map<String, Integer>> mergeds = new ArrayList<Map<String, Integer>>();
			Map<String, Integer> m1 = new HashMap<String, Integer>();	
			m1.put("startRow", 0); 
			m1.put("endRow", 1); 
			m1.put("startColumn", 0);
			m1.put("endColumn", 1);
			mergeds.add(m1);
			
			eu.exportExcelXByFormat(xyv, mergeds, os);
			
			System.out.println("文件已生成在桌面");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * 提供属性的get/set方法，便于以后获取/赋值
	 * */
	public String getFileName() {
		return fileName;
	}
	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public String getPath() {
		return path;
	}
	public void setPath(String path) {
		this.path = path;
	}

	public String getSheet0Name() {
		return sheet0Name;
	}
	public void setSheet0Name(String sheet0Name) {
		this.sheet0Name = sheet0Name;
	}

	public String getVersion() {
		return version;
	}
	public void setVersion(String version) {
		this.version = version;
	}

	public int getRowHeight() {
		return rowHeight;
	}

	public void setRowHeight(int rowHeight) {
		this.rowHeight = rowHeight;
	}

	public int getColumnWidth() {
		return columnWidth;
	}

	public void setColumnWidth(int columnWidth) {
		this.columnWidth = columnWidth;
	}
}

 

转载于:https://my.oschina.net/niaoge/blog/1635945