/// <summary>
/// 以excelOpenFileName为模板新建Excel文件
/// </summary>
public bool OpenExcelFile()
{
if (xlsApp != null)
{
//检查文件是否存在
if (xlsOpenFileName == "")
{
throw new Exception("请选择文件!");
}
}
if (!File.Exists(xlsOpenFileName))
{
throw new Exception(xlsOpenFileName + "该文件不存在!");
}
try
{
xlsApp = new Excel.ApplicationClass();
xlsWbs = xlsApp.Workbooks;
xlsWb = ((Excel.Workbook)xlsWbs.Open(xlsOpenFileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value));
xlsWs = (Excel.Worksheet)xlsWb.Worksheets[excelActiveWorkSheetIndex];
xlsApp.Visible = false;
return(true);
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception("(1)没有安装Excel 2003;\n(2)或没有安装Excel 2003 .NET 可编程性支持;\n详细信息:\n" + e.Message);
}
}
// 运行excel的vba宏
public object RunExcelMacro(string strMacroName, object args)
{
LuaTable ltArgs = args as LuaTable;
if (ltArgs == null)
{
MessageBox.Show("调用RunExcelMacro(string strMacroName, object args)时参数为空！");
return(null);
}
object[] oArgArr = new object[ltArgs.Values.Count];
for (int i = 0; i < oArgArr.Length; i++)
{
oArgArr[i] = ltArgs[i];
}
object[] ArgArr30 = new object[30];
for (int i = 0; i < oArgArr.Length; i++)
{
ArgArr30[i] = oArgArr[i];
}
for (int i = oArgArr.Length; i < ArgArr30.Length; i++)
{
ArgArr30[i] = Missing.Value;
}
if (excelApp == null) // 未初始化成功
{
MessageBox.Show("运行Excel的vba宏失败!\n\n请检查是否已在机器上装有Excel.");
return(null); // 脚本已经对null值的处理
}
object result_macro = null;
try
{
result_macro = excelApp.Run(strMacroName,
ArgArr30[0], ArgArr30[1], ArgArr30[2], ArgArr30[3], ArgArr30[4], ArgArr30[5],
ArgArr30[6], ArgArr30[7], ArgArr30[8], ArgArr30[9], ArgArr30[10], ArgArr30[11],
ArgArr30[12], ArgArr30[13], ArgArr30[14], ArgArr30[15], ArgArr30[16], ArgArr30[17],
ArgArr30[18], ArgArr30[19], ArgArr30[20], ArgArr30[21], ArgArr30[22], ArgArr30[23],
ArgArr30[24], ArgArr30[25], ArgArr30[26], ArgArr30[27], ArgArr30[28], ArgArr30[29]);
}
catch (Exception ex)
{
MessageBox.Show("宏运行错误,请不要保存所作修改！");
}
return(result_macro as string[]);
#if false
Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
oExcel.Visible = true;
Excel.Workbooks oBooks = oExcel.Workbooks;
Excel._Workbook oBook = null;
string strXL = "C:/Documents and Settings/kuangsihao/桌面/剑网3-NPC数值.xlsm";
oBook = oBooks.Open(strXL, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
RunMacro(oExcel, new Object[] { "矩形1_Click" });
/// <summary>
/// Excel文件打印预览
/// </summary>
/// <param name="ExcelFile">文件路径包含文件名称</param>
public static void ExcelPreview(string ExcelFile)
{
try
{
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
throw new Exception("无法创建Excel对象，可能您的计算机未安装Excel");
}
Excel.Workbooks xlsWbs = xlsApp.Workbooks;
Excel.Workbook xlsWb = xlsWbs.Open(
ExcelFile, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlsApp.Visible = true;
xlsWb.PrintPreview(false);
xlsWb = null;
xlsApp.Quit();
xlsApp = null;
}
catch (Exception ex)
{
throw new Exception(ex.Message + "Excel文件操作失败");
}
}
/// <summary>
/// 功能：实现Excel应用程序的打开
/// </summary>
/// <param name="TemplateFilePath">模板文件物理路径</param>
public void Open(string TemplateFilePath)
{
//打开对象
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
m_objExcel.Visible = false;
m_objExcel.DisplayAlerts = false;
if (m_objExcel.Version != "11.0")
{
MessageBox.Show("您的Excel 版本不是11.0 （Office 2003），操作可能会出现问题。");
m_objExcel.Quit();
return;
}
m_objBooks = (Workbooks)m_objExcel.Workbooks;
if (TemplateFilePath.Equals(String.Empty))
{
m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt));
}
else
{
m_objBook = m_objBooks.Open(TemplateFilePath, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
m_objSheets = (Sheets)m_objBook.Worksheets;
m_objSheet = (_Worksheet)(m_objSheets.get_Item(1));
m_objExcel.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(m_objExcel_WorkbookBeforeClose);
}
public static string ProcesarArchivo(string pathArchivo)
{
string msgResul = "Migración realizada correctamente.";
ImportadorExcelBE infoImportar = new ImportadorExcelBE();
UIUtility.COMObjectAplication ObjCom = null;
Excel.Application xlApp = null;
try
{
ObjCom = new UIUtility.COMObjectAplication("Excel.Application", "EXCEL");
xlApp = (Excel.Application)ObjCom.ObjetoAplication;
Excel.Workbooks oBooks = xlApp.Workbooks;
oBooks.Open(pathArchivo, ReadOnly: true, IgnoreReadOnlyRecommended: true);
Excel.Workbook xlLibro = oBooks.Item[1];
Excel.Sheets oSheets = xlLibro.Worksheets;
Excel.Worksheet hojaConfig = (Excel.Worksheet)oSheets.Item[1];
DataTable dtConfig = ObtenerConfigPrincipal(infoImportar, hojaConfig);
int indiceHojaParaMigrar;
Excel.Worksheet hojaParaMigrar;
foreach (DataRow infoTabla in dtConfig.Rows)
{
indiceHojaParaMigrar = ObtenerIndiceHojaExcel(oSheets, infoTabla["Pestania"].ToString());
if (indiceHojaParaMigrar > 0)
{
hojaParaMigrar = (Excel.Worksheet)oSheets.Item[indiceHojaParaMigrar];
DataTable dtDefTablas = null;
dtDefTablas = ObtenerConfigColumnasSegunTabla(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());
DataTable dtDatos = null;
dtDatos = ObtenerRegistrosTablaSegunPestania(infoImportar, hojaParaMigrar, infoTabla["NombreTablaBD"].ToString());
}
else
{
throw new ImportadorExcelExceptionBE("No se ha encontrado la Hoja Excel: " + infoTabla["Pestania"].ToString());
}
}
ImportadorExcelBM objImportadorBM = new ImportadorExcelBM();
objImportadorBM.GuardarImportacion(infoImportar);
}
catch (Exception ex)
{
if (xlApp != null)
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
msgResul = ex.Message;
}
finally
{
if (ObjCom != null)
{
ObjCom.terminarProceso();
}
}
return(msgResul); // Si es "" (VACIO) entonces es PROCESO OK
}