package testjson;

import java.io.FileOutputStream;
import java.util.Date;

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.Region;


public class MSExcelManager {
	private short encoding = HSSFWorkbook.ENCODING_UTF_16;
	private int cellType = HSSFCell.CELL_TYPE_STRING;
	public MSExcelManager() {
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		

		MSExcelManager msExcel = new MSExcelManager();

		String path = "d:\\"+ new Date().getTime() + ".xls";

		msExcel.write(msExcel.exportExcel(), path);
	}




	/**
	 * 报表导出excel格式
	 * 
	 * @param vistorList
	 * @param expertList
	 * @return
	 */
	public HSSFWorkbook exportExcel() {
		HSSFWorkbook workBook = new HSSFWorkbook();
		HSSFSheet sheet = workBook.createSheet();
		workBook.setSheetName(0, "temp1", encoding);
		sheet.setVerticallyCenter(true);
		sheet.setDefaultColumnWidth((short) 10);
		HSSFCellStyle tableStyle = getTableStyle(workBook,true);
		int beginRow = 0;//当前开始行
		int endRow = 3;	 //当前结束行
		int sumCol = 13; //一共多少列
		//当前开始行,当前结束行,一共多少列
		createRow(beginRow, endRow,sumCol,tableStyle,sheet);
		//第0行第0列开始 ;跨2行 到 第0列结束
		mergedRegion(0, 0, 2, 0,sheet);//合并单元格
		setValue(0, 0, "回答1", tableStyle, cellType,sheet);
		//第0行第1列开始 ;跨2行 到第1列结束
		mergedRegion(0, 1, 2, 1,sheet);//合并单元格
		setValue(0, 1, "回答2", tableStyle, cellType,sheet);
		//第0行第2列开始 ;跨2行 到第2列结束
		mergedRegion(0, 2, 2, 2,sheet);//合并单元格
		setValue(0, 2, "回答3", tableStyle, cellType,sheet);
		//第0行第3列 开始;跨2行 到第3列结束
		mergedRegion(0, 3, 2, 3,sheet);//合并单元格
		setValue(0, 3, "回答\n问题\n情况4\r\n", tableStyle, cellType,sheet);
		//第0行第4列 开始;跨0行 到第4列结束
		mergedRegion(0, 4, 0,12,sheet);//合并单元格
		setValue(0,4, "合并列ALL", tableStyle, cellType,sheet);
		
		//第1行第4列开始 ;跨0行 到第6列结束
		mergedRegion(1, 4, 0,6,sheet);//合并单元格
		setValue(1,4, "盘纸", tableStyle, cellType,sheet);
		
		//第1行第7列开始 ;跨0行 到第9列结束
		mergedRegion(1, 7, 0,9,sheet);//合并单元格
		setValue(1,7, "甘油", tableStyle, cellType,sheet);
		
		//第1行第10列开始 ;跨0行 到第9列结束
		mergedRegion(1, 10, 0,12,sheet);//合并单元格
		setValue(1,10, "丝数", tableStyle, cellType,sheet);
		
		//第2行第4列开始 ;跨0行 到第4列结束
		mergedRegion(2, 4, 0,4,sheet);//合并单元格
		setValue(2,4, "数字1", tableStyle, cellType,sheet);
		
		//第2行第5列开始 ;跨0行 到第5列结束
		mergedRegion(2, 5, 0,5,sheet);//合并单元格
		setValue(2,5, "数字2", tableStyle, cellType,sheet);
		
		//第2行第6列开始 ;跨0行 到第6列结束
		mergedRegion(2, 6, 0,6,sheet);//合并单元格
		setValue(2,6, "数字3", tableStyle, cellType,sheet);
		
		//第2行第7列开始 ;跨0行 到第7列结束
		mergedRegion(2, 7, 0,7,sheet);//合并单元格
		setValue(2,7, "数字4", tableStyle, cellType,sheet);
			
		return workBook;
	}

	/**
	 * 表格样式
	 * 
	 * @return
	 */
	public HSSFCellStyle getTableStyle(HSSFWorkbook workBook,boolean isBgGround) {
		HSSFCellStyle style = workBook.createCellStyle();
		HSSFFont font = workBook.createFont();
		font.setFontName("Arial");
		font.setFontHeightInPoints((short) 12);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上线居中
		if(isBgGround){
			style.setFillForegroundColor((short) 22);//背景颜色
			style.setFillBackgroundColor((short) 22);//背景颜色
			style.setFillPattern(HSSFCellStyle.SPARSE_DOTS);//背景颜色
		}
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setWrapText(true);
		style.setFont(font);
		return style;
	}

	
	public void createRow(int currentRow, int rowNum, int colNum,
			HSSFCellStyle style,HSSFSheet sheet) {
		for (int rowIndex = currentRow; rowIndex < rowNum; rowIndex++) {
			HSSFRow row = sheet.createRow(rowIndex);
			for (short cellIndex = 0; cellIndex < colNum; cellIndex++) {
				HSSFCell cell = row.createCell(cellIndex);
				cell.setEncoding(encoding);
				if (style != null) {
					cell.setCellStyle(style);
				}
				cell.setCellType(cellType);
				cell.setCellValue("");
			}
		}
	}
	public void mergedRegion(int rowFrom, int colFrom, int rowTo, int colTo,HSSFSheet sheet) {
		Region region = new Region(rowFrom, (short) colFrom, rowTo, (short) colTo);
		sheet.addMergedRegion(region);
	}
	public void setValue(int rowIndex, int colIndex, String value,
			HSSFCellStyle style, int type,HSSFSheet sheet) {
		HSSFRow row = sheet.getRow(rowIndex);
		HSSFCell cell = row.getCell((short) colIndex);
		cell.setCellType(type);
		if (style != null) {
			cell.setCellStyle(style);
		}
		cell.setCellValue(value);
	}
	public void write(HSSFWorkbook workBook, String path) {

		FileOutputStream fos;
		try {
			fos = new FileOutputStream(path);
			workBook.write(fos);
			fos.flush();
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}