JAVA通过vbs脚本在excel中插入图片

// ==================开始==================
// 首先获取确定Excel路径，通过路径定义Writer，比如：
String excelPath = System.getenv("TEMP") + "\\" + Util.getTimeStr() + index + ".xlsx";
FileOutputStream fileOut = null;
File newfile = new File(excelPath );
fileOut = new FileOutputStream(excelPath );
// 写入excel文件
wb.write(fileOut);
fileOut.close();
File execlFile = new File(System.getProperty("java.io.tmpdir"), "vbs_main.vbs");
FileWriter fw = new FileWriter(execlFile );
// 编写脚本开头
startOpen(fw, newfile);
// 定义图片位置及图片路径
Vector<int[]> picPos = new Vector<int[]>();
Vector<String> picPath = new Vector<String>();

// 列位置，比如说需要在G列插入图片：
int colCnt=  CellReference.convertColStringToIndex("G");

for(TestData tmpData: tesdDataAll){
 picPath.add(tmpData.getImgFile().getCanonicalPath());
picPos.add(new int[] { rowStart, colCnt, rowEnd, colCnt}); // 根据需要设定位置，如果是一行插入一个图片，那么rowStart和rowEnd的值是相同的。
}

getSheetAt(fw, sheetAtStart);
// 编写插入图片脚本
if (picPos.size() > 0) {
	insertImagesCenter(fw, picPos, picPath);
}

// 编写脚本尾
endSaveAs(fw, newfile);
fw.flush();
fw.close();

String[] cmds = new String[] { "Wscript.exe",
					execFile.getCanonicalPath() };
Runtime rt = Runtime.getRuntime();
// 执行脚本，输出数据到Excel
Process proc = rt.exec(cmds);
proc.waitFor();

// ==================结束==================


/**
	 * 打开excel并定义些变量
	 * @param fw
	 * @param reportFile
	 * @throws Exception
	 */
	private static void startOpen( FileWriter fw, File reportFile) throws Exception {
		StringBuffer sbuf = new StringBuffer( );
		sbuf.append( "Dim objXL\n" );
		sbuf.append( "Set objXL = WScript.CreateObject(\"Excel.Application\")\n" );
		sbuf.append( "objXL.DisplayAlerts = False \n" );
		sbuf.append( "objXL.Visible = FLASE\n" );
		sbuf.append( "Set eb = objXL.Workbooks.Add(\"" + reportFile.getCanonicalPath( ) + "\")\n" );
		//sbuf.append( "Dim picpath\n" );
		sbuf.append( "Dim rangeleft, rangetop, rangeright, rangedown \n" );
		//sbuf.append( "Dim picleft, pictop \n" );
		sbuf.append( "Dim beginx, beginy, endx, endy\n" );
		//sbuf.append( "Dim combineHeight,combineWidth,picHeight,picWidth\n");//合并高度, 合并宽度, 图片高度, 图片宽度
		fw.write( sbuf.toString( ) );
	}
	
private static void getSheetAt(FileWriter fw, int sheetAt) throws Exception{
		StringBuffer sbuf = new StringBuffer( );
		sbuf.append( "Set sheet = eb.Worksheets("+sheetAt+")\n" );
		sbuf.append( "sheet.activate \n");
		fw.write( sbuf.toString());
		
	}
	
private static void insertImagesCenter( FileWriter fw, Vector<int[]> picPoses, Vector<String> picPath) throws Exception {
		StringBuffer buff = new StringBuffer( );
		for ( int k = 0; k < picPoses.size( ); k++ ) {
			int[] pos = picPoses.get( k );
			String leftUp = numberToString( pos[1] ) + pos[0];
			buff = insertPicCenter(leftUp,picPath.get( k ),buff);
		}
		fw.write( buff.toString( ) );
	}

/**
	 * 合并单元格居中插入图片
	 * @param cell
	 * @param picPath
	 * @param buff
	 * @return
	 */
	private static StringBuffer insertPicCenter(String cell,String picPath,StringBuffer buff){
		buff.append( "sheet.Range(\"" ).append( cell ).append( "\").Select\n" );//选择单元格（照片所在位置）
		buff.append( "sheet.Pictures.Insert(\"").append(picPath).append("\").Select\n"); //插入图片，并选择图片
		buff.append( "With objXL.Selection\n" );
		//buff.append( "combineHeight = objXL.Range(sheet.Range(\"" ).append( cell ).append( "\").MergeArea.Address).Height\n");//（合并/非合并）单元格的高度
		//buff.append( "combineWidth = objXL.Range(sheet.Range(\"" ).append( cell ).append( "\").MergeArea.Address).Width\n");//（合并/非合并）单元格的宽度
		//buff.append("picHeight = .Height\n");
		//buff.append("picWidth = .Width\n");
		//可自行决定取舍。作用是：让图片撑满合并单元格高度，又不至于覆盖表格线（所以减去2）。   图片高度＝合并单元格的高度 - 2
		buff.append(".Height = objXL.Range(sheet.Range(\"" ).append( cell ).append( "\").MergeArea.Address).Height - 4\n");
//		buff.append(".Top = sheet.Range(\"" ).append( cell ).append( "\").Top + (sheet.Range(\"").append( cell ).append("\").MergeArea.Height - .Height) / 2 \n"); //垂直居中
//		buff.append(".Left = sheet.Range(\"" ).append( cell ).append( "\").Left + (sheet.Range(\"").append( cell ).append("\").MergeArea.Width - .Width) / 2 \n"); //水平居中
		buff.append(".Top = sheet.Range(\"" ).append( cell ).append( "\").Top + 3 \n");
		buff.append(".Left = sheet.Range(\"" ).append( cell ).append( "\").Left + 6 \n");
		buff.append("End With\n");
		return buff;
	}

/**
	 * 数字转换成字母
	 * 
	 * @param n
	 * @return
	 */
	private static String numberToString( int n ) {

		String s = ""; // result
		int r = 0; // remainder

		while (n != 0) {
			r = n % 26;
			char ch = ' ';
			if ( r == 0 )
				ch = 'Z';
			else
				ch = (char) (r - 1 + 'A');
			s = new StringBuffer( ).append( ch ).toString( ) + s;
			if ( s.charAt( 0 ) == 'Z' )
				n = n / 26 - 1;
			else
				n /= 26;
		}
		return s;
	}

private static void endSaveAs( FileWriter fw, File outputFile ) throws Exception {

		StringBuffer sbuf = new StringBuffer( );
		// sbuf.append("sheet.SaveAs(\"" + outputFile.getCanonicalPath() +
		// "\")\n");

		sbuf.append( "eb.SaveAs(\"" + outputFile.getCanonicalPath( ) + "\")\n" );
		sbuf.append( "objXL.DisplayAlerts = False\n" );
		sbuf.append( "objXL.Quit\n" );
		sbuf.append( "set objXL = nothing\n" );
		fw.write( sbuf.toString( ) );
	}