package frame.file.office.excel;

import java.io.File;

import org.apache.log4j.Logger;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

public class ExcelTranslator {
	
	private static Logger logger;
	
	static {
		logger = Logger.getLogger(ExcelTranslator.class);
	}
	
	public synchronized static boolean testComActive() {
		ComThread.InitSTA();
		ActiveXComponent xl = new ActiveXComponent("Excel.Application");
		xl.invoke("Quit", new Variant[] {});
		return true;
	}
	
	public synchronized static void saveAs07(File sourceFile, File targetFile) throws Exception {
		if (targetFile.exists() && targetFile.canRead()) {
			targetFile.delete();
		}
		
		ComThread.InitSTA();
		try {
			ActiveXComponent xl = new ActiveXComponent("Excel.Application");
			try {
				Dispatch workbooks = null; Dispatch workbook = null;
				
				try {
					// 不显示Excel
					xl.setProperty("Visible", new Variant(false));
					// 不提示警告等信息
					xl.setProperty("DisplayAlerts", new Variant(false));

					workbooks = xl.getProperty("Workbooks").toDispatch();
					workbook = Dispatch.call(workbooks, "Open", sourceFile.getAbsolutePath()).toDispatch();
					
					// 另保存为Excel, 18-2003, 51-2007
					Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[] { targetFile.getAbsolutePath(), new Variant(51) }, new int[1]);
				}
				finally {
					try {
						Dispatch.call((Dispatch) workbook, "Close", new Variant(false));
					}
					catch (Exception ex) {
					}					
				}
			}
			finally {
				try {
					xl.invoke("Quit", new Variant[] {});
				}
				catch (Exception ex) {
				}
			}
		}
		catch (Exception e) {
			logger.debug(e);
			throw e;
		}
		finally {
			ComThread.Release();
		}
	}
	
	public synchronized static void saveAs03(File sourceFile, File targetFile) throws Exception {
		if (targetFile.exists() && targetFile.canRead()) {
			targetFile.delete();
		}
		
		ComThread.InitSTA();
		try {
			ActiveXComponent xl = new ActiveXComponent("Excel.Application");
			try {
				Dispatch workbooks = null; Dispatch workbook = null;
				try {
					// 不显示Excel
					xl.setProperty("Visible", new Variant(false));
					// 不提示警告等信息
					xl.setProperty("DisplayAlerts", new Variant(false));

					workbooks = xl.getProperty("Workbooks").toDispatch();
					workbook = Dispatch.call(workbooks, "Open", sourceFile.getAbsolutePath()).toDispatch();
					
					// 另保存为Excel, 18-2003, 51-2007
					Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[] { targetFile.getAbsolutePath(), new Variant(18) }, new int[1]);
					
				}
				finally {
					try {
						Dispatch.call((Dispatch) workbook, "Close", new Variant(false));
					}
					catch (Exception ex) {
					}
				}
			}
			finally {
				try {
					xl.invoke("Quit", new Variant[] {});
				}
				catch (Exception ex) {
					ex.printStackTrace();
					logger.error(ex.getMessage());
				}				
			}
		}
		catch (Exception e) {
			logger.debug(e);
			throw e;
		}
		finally {
			ComThread.Release();
		}
	}

	public synchronized static void saveAsXlsb(File sourceFile, File targetFile) {
		ComThread.InitSTA();
		ActiveXComponent xl = new ActiveXComponent("Excel.Application");
		Dispatch workbooks = null;
		Dispatch workbook = null;

		try {
			if (targetFile.exists() && targetFile.canRead()) {
				targetFile.delete();
			}

			// 不显示Excel
			xl.setProperty("Visible", new Variant(false));
			// 不提示警告等信息
			xl.setProperty("DisplayAlerts", new Variant(false));

			workbooks = xl.getProperty("Workbooks").toDispatch();
			//workbook = Dispatch.call(workbooks, "Open", sourceFile.getAbsolutePath()).toDispatch();
			//0--正常打开； 1--修复工作簿； 2--修复数据 (不能用于打开07)
			workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method, 
					new Object[] { sourceFile.getAbsolutePath(), 
		            	Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, Variant.VariantNull,
		            	Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, Variant.VariantNull,
		            	Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, 1
			}, new int[1]).toDispatch();
			
			// 另保存为Excel, 50-xlsb
			Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[] { targetFile.getAbsolutePath(), new Variant(50) }, new int[1]);

		}
		catch (Exception e) {
			logger.debug(e);
			throw new RuntimeException(e);
		}
		finally {
			try {
				Dispatch.call((Dispatch) workbook, "Close", new Variant(false));
			}
			catch (Exception ex) {
			}

			try {
				xl.invoke("Quit", new Variant[] {});
			}
			catch (Exception ex) {
			}

			ComThread.Release();
		}
	}
	
	public static void main(String[] args) {
		File sourceFile = new File("D:\\test\\prod.xlsx");
		File targetFile = new File("D:\\test\\test2.xlsx");
		
		if (!sourceFile.exists()) {
			System.out.println("file not exist:" + sourceFile);
			return;
		}
		
		if (targetFile.exists() && targetFile.canRead()) {
			targetFile.delete();
		}
		
		ComThread.InitSTA();
		try {
			ActiveXComponent xl = new ActiveXComponent("Excel.Application");
			try {
				Object xlo = xl.getObject();
				Dispatch workbooks = null; Dispatch workbook = null;
				
				try {
					// 不显示Excel
					System.out.println("version=" + xl.getProperty("Version"));  
		            System.out.println("version=" + Dispatch.get((Dispatch) xlo, "Version"));				
					xl.setProperty("Visible", new Variant(true));
					// 不提示警告等信息
					xl.setProperty("DisplayAlerts", new Variant(false));

					workbooks = xl.getProperty("Workbooks").toDispatch();
					workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method,  
				               new Object[] { sourceFile.getAbsolutePath(),  
				             Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, Variant.VariantNull,
				             Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, Variant.VariantNull,
				             Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, Variant.VariantNull, 1
					}, new int[1]).toDispatch();
//					workbook = Dispatch.call(workbooks, "Open", sourceFile.getAbsolutePath()).toDispatch();
					// 另保存为Excel, 18-2003, 51-2007
					Dispatch.invoke(workbook, "SaveAs", Dispatch.Method, new Object[] { targetFile.getAbsolutePath(), new Variant(51) }, new int[1]);
					System.err.println("ok!");
				}
				finally {
					try {
						Dispatch.call((Dispatch) workbook, "Close", new Variant(false));
					}
					catch (Exception ex) {
					}					
				}
			}
			finally {
				try {
					xl.invoke("Quit", new Variant[] {});
				}
				catch (Exception ex) {
				}
			}
		}
		catch (Exception e) {
			System.out.println(e.getMessage());
		}
		finally {
			ComThread.Release();
		}
		System.err.println("done!");
	}
}