public ExcelHelper(string _filename)
{
excelApp = new Excel.Application();
object objOpt = System.Reflection.Missing.Value;
wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(_filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
}
public ExcelHelper(string _filename)
{
fileName = _filename;
excelApp = new Excel.Application();
object objOpt = System.Reflection.Missing.Value;
wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(_filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
}
/// <summary>
/// 构造
/// </summary>
/// <param name="FileName"></param>
public ExcelHelper(string FileName)
{
ExcelApp = new InteropExcel.Application();
object objOpt = System.Reflection.Missing.Value;
wbclass = (InteropExcel.WorkbookClass)ExcelApp.Workbooks.Open(FileName, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
}
public void Main()
{
Variables UserVariables = null;
Dts.VariableDispenser.LockForRead("User::VBA_MacroName");
Dts.VariableDispenser.LockForRead("User::VBA_Script");
Dts.VariableDispenser.LockForRead("User::VBA_Parameters");
Dts.VariableDispenser.GetVariables(ref UserVariables);
//The macro and the macro name were stored in the original data set, so we can get those from local variables.
string Macro = UserVariables["User::VBA_Script"].Value.ToString();
string Macro_name = UserVariables["User::VBA_MacroName"].Value.ToString();
XmlDocument doc = new XmlDocument();
doc.LoadXml(UserVariables["User::VBA_Parameters"].Value.ToString());
XmlNodeList Parameters = doc.GetElementsByTagName(@"Parameter");
object[] AllParamArray = new object[31];
object[] MyParamArray = new object[Parameters.Count];
//Fill the array with as many missing values as there are parameters in the Run macro command
for (int i = 0; i < AllParamArray.Length; i++)
{
AllParamArray[i] = Missing.Value;
}
//get the parameters that we are actually going to use.
for (int i = 0; i < Parameters.Count; i++)
{
MyParamArray[i] = Parameters[i].Attributes["Value"].Value.ToString();
}
//the first parameter is always the macro name
AllParamArray[0] = Macro_name;
//after that, we can insert our list of all the parameters we need into the list of all the parameters Excel needs
MyParamArray.CopyTo(AllParamArray, 1);
//Get Excel ready to be opened
Excel.Application ExcelObject = default(Excel.Application);
Excel.WorkbookClass oBook = default(Excel.WorkbookClass);
Excel.Workbooks oBooks = default(Excel.Workbooks);
//get the vba module ready
VBIDE.VBComponent module = null;
//open excel in the background
ExcelObject = new Excel.Application();
ExcelObject.Visible = false;
ExcelObject.DisplayAlerts = false;
//Open our report
oBooks = ExcelObject.Workbooks;
oBook = (Excel.WorkbookClass)oBooks.Add(Missing.Value);
//Add a module to our report and populate it with our vba macro
module = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(Macro);
ExcelObject.Run
(AllParamArray[0], AllParamArray[1], AllParamArray[2], AllParamArray[3], AllParamArray[4], AllParamArray[5], AllParamArray[6], AllParamArray[7], AllParamArray[8],
AllParamArray[9], AllParamArray[10], AllParamArray[11], AllParamArray[12], AllParamArray[13], AllParamArray[14], AllParamArray[15], AllParamArray[16], AllParamArray[17],
AllParamArray[18], AllParamArray[19], AllParamArray[20], AllParamArray[21], AllParamArray[22], AllParamArray[23], AllParamArray[24], AllParamArray[25], AllParamArray[26],
AllParamArray[27], AllParamArray[28], AllParamArray[29], AllParamArray[30]);
UserVariables["User::TriedToSaveFileAs"].Value = UserVariables["User::Current_DataFile"].Value.ToString();
oBook.Close(false, Missing.Value, Missing.Value);
ExcelObject.Application.Quit();
ExcelObject = null;
}
public bool LoadExcelFile()
{
object objOpt = System.Reflection.Missing.Value;
try
{
wbclass = (Excel.WorkbookClass)excelApp.Workbooks.Open(_fileName, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
if (wbclass == null)
{
Program.Logger.ErrorFormat("ExcelHelper , Excel file not exist {0}",_fileName);
}
return true;
}
catch (System.Exception ex)
{
Program.Logger.Error("ExcelHelper , Excel file not exist", ex);
excelApp.Quit();
excelApp = null;
}
return false;
}
