Java操作Excel整理

之前项目通过poi对Excel进行操作，简单整理一下常用的处理。
1.获取Java版本
14.0表示Excel版本是2010，12.0对应2007，15.0对应2013，16.0对应2016

    private String getExcelApplicationVersion()
	{
		ActiveXComponent xl = new ActiveXComponent("Excel.Application");
		String version = new String( xl.getProperty("Version").toString() );
		System.out.println("version=" + xl.getProperty("Version")); 
		return version;
	}

2.通过输入流创建workBook

		private static Workbook createExcel(InputStream in) throws IOException, InvalidFormatException {
		if (!in.markSupported()) {
			in = new PushbackInputStream(in, 8);
		}
		if (POIFSFileSystem.hasPOIFSHeader(in)) {
			return new HSSFWorkbook(in);
		}
		if (POIXMLDocument.hasOOXMLHeader(in)) {
			return new XSSFWorkbook(OPCPackage.open(in));
		}
		throw new IllegalArgumentException("excel版本poi解析不了");
	}

3.取得Workbook以后就可以对excel进行操作了，包括获取sheet，获取某一行，获取某一个单元格等。

Sheet sheet1 = wb.getSheetAt(0);
Sheet  sheet2 = wb.getSheet("sheet1");
XSSFRow tmpRow = sheet.getRow(0);
String tmpValue = tmpRow.getCell(0).getStringCellValue();

4.字体设置

Font font = wb.createFont();
		font.setFontName(fontName);
		font.setBoldweight(bold);
		font.setItalic(isItalic);
		font.setFontHeightInPoints(size);

5.单元格格式设置

CellStyle cellStyle1 = wb.createCellStyle();
cellStyle1.setWrapText(true);// 设置自动换行
			cellStyle1.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 设置水平对齐的样式为居中对齐
			cellStyle1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 设置垂直对齐的样式为居中对齐
			cellStyle1.setFont(fontTitle);
			cellStyle1.setBorderRight(CellStyle.BORDER_THIN);
			cellStyle1.setBorderLeft(CellStyle.BORDER_THIN);
			cellStyle1.setBorderBottom(CellStyle.BORDER_THIN);
			cellStyle1.setBorderTop(CellStyle.BORDER_THIN);
			cellStyle1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			cellStyle1.setFillPattern(CellStyle.SOLID_FOREGROUND);

6.合并单元格

CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn);
sheet.addMergedRegion(region);

7.设置签名

XSSFName name = wb.createName();
name.setNameName( "testQianMing" + sheetIndex);
StringBuffer sb = new StringBuffer();
sb.append("'").append(sheetName).append("'!$")
					.append(cellName.getCol()).append("$").append(cellName.getRow());
name.setRefersToFormula(sb.toString());