/// <summary>
/// Release all references, must do lower to higher level...
/// </summary>
public static void DeleteObject()
{
if (LogCalls)
{
DebugLog.Message("ExcelOp DeleteObject");
}
ReleaseAllObjects();
ReleaseObject(XlApp);
XlApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
if (DebugFileSaveOperation)
{
return; // debug
}
try // hack to kill Excel process until proper close can be done
{
if (ExcelProcess != null && !ExcelProcess.HasExited)
{
ExcelProcess.Kill();
}
}
catch (Exception ex)
{
throw new Exception("Kill Process Failed: " + ex.Message);
}
}
public ExcelClass()
{
if (m_ExcelApplication == null)
{
m_ExcelApplication = new Excel.Application();
}
}
private void fileOpen()
{
try
{
mApp = new Excel.Application();
mApp.Visible = true;
//myBook = mApp.Workbooks.Add(filepath);
myBook = mApp.Workbooks.Open(filepath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true, Type.Missing, Type.Missing);
sheets = myBook.Sheets;
mySheet = sheets[1];
if (mySheet == null)
{
Console.WriteLine("没有工作簿");
return;
}
mySheet.Activate();
rowCount = mySheet.UsedRange.Rows.Count + 1;
Console.WriteLine("加载时行数：" + rowCount);
status_textBox.Text = "文件初始化成功";
insertTitle();
}catch (Exception ex)
{
status_textBox.Text = ex.Message;
mApp.Quit();
}
}
//Method to initialize opening Excel
private void ExcelInit(String path, out List<Product> idinfo)
{
xlApp = new Excel.Application();
string dsSheet = "datasheet";
string idinfoSheet = "idinfo";
idinfo = null;
if (System.IO.File.Exists(path)) {
// then go and load this into excel
xlWorkBook = xlApp.Workbooks.Open(path,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
false, false, 0, true, 1, 0);
//read info datasheet first to generate product specs list
info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet);
idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet);
//read datasheet and load the data into product specs list
//ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet);
//ReadDatasheetIntoDataTable(ds_xlWorkSheet);
} else {
MessageBox.Show("Unable to open excel file!");
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.Windows.Forms.Application.Exit();
}
}
/// <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 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);
}
//Method to initialize opening Excel
private void ExcelInit(String path, out List <Product> idinfo)
{
xlApp = new Excel.Application();
string dsSheet = "datasheet";
string idinfoSheet = "idinfo";
idinfo = null;
if (System.IO.File.Exists(path))
{
// then go and load this into excel
xlWorkBook = xlApp.Workbooks.Open(path,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
false, false, 0, true, 1, 0);
//read info datasheet first to generate product specs list
info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet);
idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet);
//read datasheet and load the data into product specs list
//ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet);
//ReadDatasheetIntoDataTable(ds_xlWorkSheet);
}
else
{
MessageBox.Show("Unable to open excel file!");
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.Windows.Forms.Application.Exit();
}
}
/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, mValue, mValue);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;
CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
{
//if (pro.StartTime < DateTime.Now)
pro.Kill();
}
}
System.GC.SuppressFinalize(this);
}
public ExcelHelper(string _filePath)
{
filePath = _filePath;
excelApp = new Excel.Application();
object objOpt = System.Reflection.Missing.Value;
wbclass = (Excel.Workbook)excelApp.Workbooks.Open(_filePath, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
}
static void MergeExcels(Excel._Application app, IEnumerable <string> sourcePaths, string destinationPath)
{
var dstWb = app.Workbooks.Add("");
var srcWbs = sourcePaths.Select(sourcePath => app.Workbooks.Add(sourcePath));
// Don't start with i = 1 because trying to delete the last sheet makes an error.
for (var i = dstWb.Worksheets.Count; 2 <= i; i--)
{
dstWb.Worksheets[i].Delete();
}
// Keep the last sheet to be deleted when it is no longer the last sheet.
Excel.Worksheet ws1 = dstWb.Worksheets[1];
// app.Workbooks[1] is a destination so needs to be skipped.
for (var i = app.Workbooks.Count; 2 <= i; i--)
{
var srcWb = app.Workbooks[i];
for (var j = srcWb.Worksheets.Count; 1 <= j; j--)
{
srcWb.Worksheets[j].Copy(dstWb.Worksheets[1]);
}
}
ws1.Delete();
dstWb.SaveAs(destinationPath);
foreach (var srcWb in srcWbs)
{
srcWb.Close();
}
}
public static string MessageInStatusBar(
[Parameter("The String ")] string Message
)
{
XL._Application theApp = ExcelInstance.Instance();
theApp.StatusBar = Message;
return("--");
}
public static string MyAddress(
[Parameter("x")] double x
)
{
XL._Application theApp = ExcelInstance.Instance();
XL.Range range = (XL.Range)theApp.get_Caller(System.Type.Missing);
return(range.get_Address(System.Type.Missing, System.Type.Missing, XL.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing));
}
static public void Open(string path, bool visible)
{
exApp = new Ex.Application();
exApp.Visible = visible;
exApp.DisplayAlerts = false;
workBook = exApp.Workbooks.Open(path);
workSheet = workBook.ActiveSheet;
}
/// <summary>
/// 释放
/// </summary>
public void Close()
{
if (ExcelApp != null)
{
ExcelApp.Quit();
ExcelApp = null;
}
}
public DocExcel(string nameExcel, string nameSheet, XlPageOrientation orientation) //конструктор
{
excelapp = new ExcelApp.Application();
excelapp.Caption = nameExcel;
excelBook = excelapp.Workbooks.Add(Type.Missing);
_currentSheet = (ExcelApp.Worksheet)excelBook.Sheets.get_Item(1);
_currentSheet.PageSetup.Orientation = orientation;
}
public void ExportCallQualityData(string formPath, string savePath, string fileFormat, DataTable dt, string contragent, string ocinyvach, string ocinyvanyi, string callType, string opDate, string criticalError, string filePath, string result)
{
try
{
Cursor.Current = Cursors.WaitCursor;
app = new Excel.Application();
Excel._Workbook workbook = app.Workbooks.Open(formPath);
//Excel._Worksheet worksheet = null;
Excel._Worksheet worksheet = workbook.Sheets[1];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Оцінка якості";
worksheet.Cells.Replace("{OpDateTime}", opDate);
worksheet.Cells.Replace("{Ochinuvanyy}", ocinyvanyi);
worksheet.Cells.Replace("{CallType}", callType);
worksheet.Cells.Replace("{Contragent}", contragent);
worksheet.Cells.Replace("{FilePath}", filePath);
worksheet.Cells.Replace("{Ocinyvach}", ocinyvach);
worksheet.Cells.Replace("{CriticalError}", criticalError);
worksheet.Cells.Replace("{Result}", result);
int startRow = 14;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i < dt.Rows.Count - 1)
{
worksheet.Rows[(startRow + i) + ":" + (startRow + i)].Copy();
worksheet.Rows[(startRow + i + 1) + ":" + (startRow + i + 1)].Insert();
}
worksheet.Range["B" + (startRow + i)].Value = dt.Rows[i][0].ToString();
worksheet.Range["D" + (startRow + i)].Value = dt.Rows[i][1].ToString();
worksheet.Range["E" + (startRow + i)].Value = dt.Rows[i][2].ToString();
}
worksheet.Rows[7 + ":" + (dt.Rows.Count - 1)].EntireRow().AutoFit();
if (fileFormat.Contains("xlsx"))
{
workbook.SaveAs(savePath);
app.Visible = true;
}
else if (fileFormat.Contains("pdf"))
{
workbook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, savePath);
workbook.Close(false);
app.Quit();
System.Diagnostics.Process.Start(savePath);
}
Cursor.Current = Cursors.Default;
//MessageBox.Show("Дані вивантажено!" + Environment.NewLine + savePath, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
if (app.Workbooks.Count == 1)
{
app.Quit();
}
app = null;
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Quit the current application
/// </summary>
protected void QuitApplication()
{
if (_application != null)
{
_application.Quit();
}
_application = null;
}
public void Close()
{
try
{
excelApp.Quit();
}
catch { }
excelApp = null;
}
private static void ExcelClose(Excel._Workbook workbook, Excel._Application application)
{
var missingObject = System.Reflection.Missing.Value;
workbook.Close(false, missingObject, missingObject);
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
GC.Collect();
}
/// <summary>
/// Cette méthode permet de supprimer la feuille excel nouvellement créée.
/// Ceci nous permet de n'avoir qu'une seule feuille présente dans le SheetPanel.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Office_WorkbookNewSheet(object sender, EventArgs e)
{
if (this.Office.GetCurrentProgID() == EXCEL_ID)
{
Excel._Application xlApp = this.Office.GetApplication() as Excel._Application;
xlApp.DisplayAlerts = false;
Excel._Worksheet workSheet = (Excel._Worksheet)xlApp.ActiveWorkbook.ActiveSheet;
//workSheet.Delete();
}
}
public RptSummary()
{
_xlApp = new Microsoft.Office.Interop.Excel.Application();
if (_xlApp == null)
{
MessageBox.Show("Error (Excel Interop) : cannot start !");
}
_xlApp.Visible = true;
}
/// <summary>
/// Create the Excel object
/// </summary>
public static void CreateObject()
{
KeepExcelVisibleForDebugging = SS.I.UserIniFile.ReadBool("ExcelOpKeepExcelVisibleForDebugging", KeepExcelVisibleForDebugging);
if (LogCalls)
{
DebugLog.Message("ExcelOp CreateObject");
}
XlApp = new Microsoft.Office.Interop.Excel.Application();
MessageFilter.Register(); // filter messages, e.g. RetryRejectedCall ("Call was rejected by callee")
ExcelProcess = GetNewExcelProcess();
XlBooks = XlApp.Workbooks;
try // avoid popups that lockup Excel
{
XlApp.AskToUpdateLinks = false; // XlApp.Workbooks.Open Filename:="C:\Book1withLinkToBook2.xlsx", UpdateLinks:=False also works
XlApp.DisplayAlerts = false; // avoid alert popup dialogs
//XlApp.AutomationSecurity = // AutomationSecurity normally starts at: msoAutomationSecurityLow
// MsoAutomationSecurity.msoAutomationSecurityForceDisable; // setting to ForceDisable won't allow structures to be inserted which is something that we don't want
}
catch (Exception ex) { ex = ex; }
Version version = ExcelOp.GetExcelVersion();
bool visible = XlApp.Visible;
StructureInsertCount = 0;
if (!KeepExcelVisibleForDebugging)
{
ShowExcelApp(false); // hide the app
//if (version.Major >= 14) // >= Excel 2010 - window visible & no screen updating during sheet build
//{
// ExcelOp.HideExcelApp(true);
// ExcelOp.ScreenUpdating(false);
//}
//else // Excel 2007 or earlier - window invisible & no screen updating during sheet build
//{
// ExcelOp.HideExcelApp(false); // hide Excel instance
// ExcelOp.ScreenUpdating(false);
//}
}
else // make visible for dev/debug
{
XlApp.Visible = true;
XlApp.WindowState = XlWindowState.xlNormal;
}
return;
}
internal void CollectData(IEnumerable <string> years)
{
_excelApp = new ExcelApp.Application();
foreach (var year in years)
{
CollectDataForYear(year);
}
_excelApp.Quit();
}
static ExcelInstance()
{
Hashtable result = new Hashtable();
IntPtr numFetched = IntPtr.Zero;
IRunningObjectTable runningObjectTable;
IEnumMoniker monikerEnumerator;
IMoniker[] monikers = new IMoniker[1];
GetRunningObjectTable(0, out runningObjectTable);
runningObjectTable.EnumRunning(out monikerEnumerator);
monikerEnumerator.Reset();
while (monikerEnumerator.Next(1, monikers, numFetched) == 0)
{
System.Runtime.InteropServices.ComTypes.IBindCtx ctx;
CreateBindCtx(0, out ctx);
string runningObjectName;
monikers[0].GetDisplayName(ctx, null, out runningObjectName);
object runningObjectVal;
runningObjectTable.GetObject(monikers[0], out runningObjectVal);
if (runningObjectVal is XL._Workbook)
{
XL._Workbook theExcel = (XL._Workbook)runningObjectVal;
int theHandle = theExcel.Application.Hwnd;
int ProcessId;
GetWindowThreadProcessId(theHandle, out ProcessId);
if (ProcessId == Process.GetCurrentProcess().Id)
{
theInstance=theExcel.Application;
return;
}
}
if (runningObjectVal is XL._Application)
{
XL._Application theExcel = (XL._Application)runningObjectVal;
int theHandle = theExcel.Hwnd;
int ProcessId;
GetWindowThreadProcessId(theHandle, out ProcessId);
if (ProcessId == Process.GetCurrentProcess().Id)
{
theInstance = (XL._Application)runningObjectVal;
return;
}
}
}
throw new Exception("Didn't get a Handle");
}
static void SaveAsCsv(Excel._Application app, string path)
{
var wb = app.Workbooks.Add(path);
foreach (Excel.Worksheet ws in wb.Worksheets)
{
ws.Activate();
wb.SaveAs(Path.ChangeExtension(Io.File1.AppendSuffix(path, "-" + ws.Name), ".csv"), Excel.XlFileFormat.xlCSV);
}
app.Workbooks.Close();
}
/// <summary>
/// Closes the workbook and the excel application.
/// </summary>
/// <param name="application">Excel application object</param>
public static void Close(this InteropExcel._Application application)
{
if (application != null)
{
foreach (InteropExcel.Workbook workbook in application.Workbooks)
{
workbook.Close(false);
}
application.Quit();
}
}
/// <summary>
/// Создание страницы с параметрами техобъектов проекта
/// </summary>
private static void CreateObjectParamsPage(ref Excel.Worksheet xlWorkSheet,
ref Excel._Application xlApp)
{
xlWorkSheet = xlApp.Sheets.Add(Type.Missing, xlWorkSheet) as Excel.Worksheet;
xlWorkSheet.Name = "Параметры объектов";
TreeView tree = TechObject.TechObjectManager.GetInstance().SaveParamsAsTree();
int row = 1;
WriteTreeNode(ref xlWorkSheet, tree.Nodes, ref row);
xlWorkSheet.Cells.EntireColumn.AutoFit();
xlWorkSheet.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryAbove;
}
/// <summary>
/// Создание страницы с итоговыми данными по устройствам
/// </summary>
private static void CreateTotalDevicePage(ref Excel.Worksheet xlWorkSheet,
ref Excel._Application xlApp)
{
xlWorkSheet = xlApp.Sheets.Add(Type.Missing,
xlWorkSheet) as Excel.Worksheet;
xlWorkSheet.Name = "Сводная таблица устройств";
object[,] res = Device.DeviceManager.GetInstance().SaveSummaryAsArray();
string endPos = "Q" + res.GetLength(0);
xlWorkSheet.Range["A1", endPos].Value2 = res;
xlWorkSheet.Cells.EntireColumn.AutoFit();
}
private void ClearClient()
{
ClientWorkSheetList.Clear();
ExcelListBox.Items.Clear();
ClientConfigSheetList.Clear();
ExcelApp.Quit();
foreach (ExcelItem item in ClientWorkSheetList)
{
item.Workbook.Close(false, false, Missing.Value);
}
ExcelApp = new Excel.Application();
}
static ExcelInstance()
{
Hashtable result = new Hashtable();
IntPtr numFetched = IntPtr.Zero;
IRunningObjectTable runningObjectTable;
IEnumMoniker monikerEnumerator;
IMoniker[] monikers = new IMoniker[1];
GetRunningObjectTable(0, out runningObjectTable);
runningObjectTable.EnumRunning(out monikerEnumerator);
monikerEnumerator.Reset();
while (monikerEnumerator.Next(1, monikers, numFetched) == 0)
{
System.Runtime.InteropServices.ComTypes.IBindCtx ctx;
CreateBindCtx(0, out ctx);
string runningObjectName;
monikers[0].GetDisplayName(ctx, null, out runningObjectName);
object runningObjectVal;
runningObjectTable.GetObject(monikers[0], out runningObjectVal);
if (runningObjectVal is XL._Workbook)
{
XL._Workbook theExcel = (XL._Workbook)runningObjectVal;
int theHandle = theExcel.Application.Hwnd;
int ProcessId;
GetWindowThreadProcessId(theHandle, out ProcessId);
if (ProcessId == Process.GetCurrentProcess().Id)
{
theInstance = theExcel.Application;
return;
}
}
if (runningObjectVal is XL._Application)
{
XL._Application theExcel = (XL._Application)runningObjectVal;
int theHandle = theExcel.Hwnd;
int ProcessId;
GetWindowThreadProcessId(theHandle, out ProcessId);
if (ProcessId == Process.GetCurrentProcess().Id)
{
theInstance = (XL._Application)runningObjectVal;
return;
}
}
}
throw new Exception("Didn't get a Handle");
}
//关闭Excel
public void close()
{
wbclass.Close();
wbclass = null;
excelApp.Quit();
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
/// <summary>
/// Создание страницы с итоговыми данными по устройствам
/// </summary>
private static void CreateDeviceConnectionPage(ref Excel.Worksheet xlWorkSheet,
ref Excel._Application xlApp)
{
xlWorkSheet = xlApp.Sheets.Add(Type.Missing,
xlWorkSheet) as Excel.Worksheet;
xlWorkSheet.Name = "Подключение устройств";
TreeView tree = Device.DeviceManager.GetInstance().SaveConnectionAsTree();
int row = 1;
WriteTreeNode(ref xlWorkSheet, tree.Nodes, ref row);
xlWorkSheet.Cells.EntireColumn.AutoFit();
xlWorkSheet.Outline.SummaryRow = Excel.XlSummaryRow.xlSummaryAbove;
}
public ExcelHelper(string _filename)
{
if (excelApp == null)
{
excelApp = new Excel.Application();
}
object objOpt = System.Reflection.Missing.Value;
wbclass = (Excel.Workbook)excelApp.Workbooks.Open(_filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
excelApp.DisplayAlerts = false; //是否需要显示提示
excelApp.AlertBeforeOverwriting = false; //是否弹出提示覆盖
excelApp.Visible = false;
}
public void ProtectSheet(String password = "******")
{
if (this.Office.GetCurrentProgID() == EXCEL_ID)
{
Excel._Application xlApp = this.Office.GetApplication() as Excel._Application;
xlApp.ActiveSheet.Protect(password, true, false, true,
true, true, true,
true, false, false,
false, false, false,
false, false, true);
}
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
Excel._Application app = (Excel._Application)Globals.ThisAddIn.Application;
Excel._Workbook wb2 = app.ActiveWorkbook;
Type to = typeof(Excel.Application);
//object f= to.InvokeMember("ThisWorkbook", BindingFlags.GetProperty, null, app, null);
Excel.Workbook wb = (Excel.Workbook)app.ActiveWorkbook;
string name = wb.Name;
Excel.Workbook addedwb = Globals.ThisAddIn.Application.Workbooks.Add();
addedwb.SaveAs("newwb");
}
//Method to close excel connection
private void ExcelClose()
{
if (xlApp != null) {
try {
xlWorkBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
xlWorkBook = null;
} catch (Exception ex) {
xlApp = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
} finally {
GC.Collect();
}
}
}
private void Init(ref MSExcel._Workbook wb, out bool success)
{
try
{
_excelApp = new MSExcel.Application();
//int[] oldproc = GetPIDs(@"EXCEL");
object oMissiong = System.Reflection.Missing.Value;
wb = _excelApp.Workbooks.Open(path, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
success = true;
//获取Excel App的句柄
hwnd = new IntPtr(ExcelApp.Hwnd);
//通过Windows API获取Excel进程ID
GetWindowThreadProcessId(hwnd, out pid);
_desiredName = path;
}
catch (System.Exception ex)
{
LogHelper.AddLog(@"异常24", ex.Message, true);
success = false;
}
}
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;
}
public ExcelHelper(string filename)
{
_fileName = filename;
outstring = "";
excelApp = new Excel.Application();
}
public void Close()
{
excelApp.Quit();
excelApp = null;
}
//private bool createJobCostingSheet(ServiceSubmissionModel submission, string filename)
private int createJobCostingSheet(ServiceSheetViewModel submission, string filename, bool firstSubmission, int lineNumber)
{
Excel.Range range;
//First Open excel
if (firstSubmission)
{
excelApplication = new Excel.Application();
excelApplication.Visible = true;
excelWorkbook = excelApplication.Workbooks.Open(filename);
excelWorksheet = excelWorkbook.ActiveSheet;
//Add the job title
string customer = submission.Customer;
string machineMake = submission.MachineMakeModel;
string serialNumber = submission.MachineSerial;
string jobDescription = submission.JobDescription;
range = excelWorksheet.Cells[13, 1];
range.Value2 = string.Concat(customer, " - ", machineMake, " - S/N: ", serialNumber, " - ", jobDescription);
range = excelWorksheet.Cells[11, 9];
range.Value2 = submission.MttJobNumber;
}
//Load all the days and loop through them. Output to the sheet
AllServiceDayViewModels serviceDays = submission.AllServiceDays;
//Need the engineers initials for each row
string engFirstName = submission.UserFirstName;
string engSurname = submission.UserSurname;
string initials = string.Concat(engFirstName[0], engSurname[0]);
int sheetNo = submission.SubmissionNumber;
int currentSpreadsheetRow;
if (lineNumber == -1)
{
currentSpreadsheetRow = 17;
}
else
{
currentSpreadsheetRow = lineNumber++;
}
foreach (ServiceDayViewModel currentDay in serviceDays.AllServiceDayVMs)
{
range = excelWorksheet.Cells[currentSpreadsheetRow, 1];
range.Value2 = currentDay.DtReport;
string day = currentDay.DtReport.DayOfWeek.ToString();
range = excelWorksheet.Cells[currentSpreadsheetRow, 2];
range.Value2 = day;
double standardHours = calculateStandardHours(currentDay);
range = excelWorksheet.Cells[currentSpreadsheetRow, 3];
range.Value2 = standardHours;
double overtimeHours = calculateOvertimeHours(currentDay);
range = excelWorksheet.Cells[currentSpreadsheetRow, 4];
range.Value2 = overtimeHours;
range = excelWorksheet.Cells[currentSpreadsheetRow, 5];
range.Value2 = currentDay.TotalTravelTime;
bool dailyAllowance = currentDay.DailyAllowance;
int dailyAllowanceValue = convertBoolToIntForAllowances(dailyAllowance);
range = excelWorksheet.Cells[currentSpreadsheetRow, 6];
range.Value2 = dailyAllowanceValue;
bool overNight = currentDay.OvernightAllowance;
int overnightAllowanceValue = convertBoolToIntForAllowances(overNight);
range = excelWorksheet.Cells[currentSpreadsheetRow, 7];
range.Value2 = overnightAllowanceValue;
range = excelWorksheet.Cells[currentSpreadsheetRow, 8];
range.Value2 = currentDay.Mileage;
range = excelWorksheet.Cells[currentSpreadsheetRow, 9];
range.Value2 = initials;
range = excelWorksheet.Cells[currentSpreadsheetRow, 10];
range.Value2 = sheetNo;
currentSpreadsheetRow++;
}
MessageBox.Show("Need to handle bank holidays");
return currentSpreadsheetRow;
}
public void OnStartupComplete(ref System.Array custom)
{
CommandBars oCommandBars;
CommandBar oStandardBar;
object omissing = System.Reflection.Missing.Value;
Thread.Sleep(50);
app = (Microsoft.Office.Interop.Excel._Application)applicationObject;
try
{
oCommandBars = (CommandBars)applicationObject.GetType().InvokeMember("CommandBars", BindingFlags.GetProperty, null, applicationObject, null);
}
catch (Exception)
{
return;
}
// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars["Worksheet Menu Bar"];
}
catch (Exception)
{
return;
}
CommandBarControls controls = oStandardBar.Controls;
// remove old menus...
foreach (CommandBarControl control in controls)
{
String caption = control.Caption;
if (caption.EndsWith("Remote Documents") || caption.EndsWith("Remote documents"))
{
control.Delete(null);
}
}
// In case the button was not deleted, use the exiting one.
try
{
eXoMenu = (CommandBarPopup)oStandardBar.Controls["Remote documents"];
Open = (CommandBarButton)eXoMenu.Controls["Open"];
Save = (CommandBarButton)eXoMenu.Controls["Save"];
SaveAs = (CommandBarButton)eXoMenu.Controls["SaveAs"];
Search = (CommandBarButton)eXoMenu.Controls["Search"];
Settings = (CommandBarButton)eXoMenu.Controls["Settings"];
About = (CommandBarButton)eXoMenu.Controls["About"];
}
catch (Exception)
{
eXoMenu = (CommandBarPopup)oStandardBar.Controls.Add(MsoControlType.msoControlPopup, omissing, omissing, omissing, true);
eXoMenu.Caption = "Remote Documents";
eXoMenu.Tag = eXoMenu.Caption;
Open = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing);
Open.Caption = "Open...";
Open.Tag = Open.Caption;
Save = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing);
Save.Caption = "Save";
Save.Tag = Save.Caption;
SaveAs = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing);
SaveAs.Caption = "Save As...";
SaveAs.Tag = SaveAs.Caption;
Search = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing);
Search.Caption = "Search...";
Search.Tag = Search.Caption;
Settings = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing);
Settings.Caption = "Settings...";
Settings.Tag = Settings.Caption;
About = (CommandBarButton)eXoMenu.Controls.Add(1, omissing, omissing, omissing, omissing);
About.Caption = "About...";
About.Tag = About.Caption;
}
Open.Visible = true;
Open.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.Open_Click);
Save.Visible = true;
Save.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.Save_Click);
SaveAs.Visible = true;
SaveAs.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.SaveAs_Click);
Search.Visible = true;
Search.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.Search_Click);
Settings.Visible = true;
Settings.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.Settings_Click);
About.Visible = true;
About.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(this.About_Click);
object oName = applicationObject.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, applicationObject, null);
oStandardBar = null;
oCommandBars = null;
clearRepository();
}
/// <summary>
/// 释放所引用的COM对象。注意：这个过程一定要执行。
/// </summary>
public void DisposeExcel(ref MSExcel._Workbook _excelDoc)
{
ReleaseObj(_excelDoc);
ReleaseObj(_excelApp);
_excelDoc = null;
_excelApp = null;
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
System.Threading.Thread.Sleep(1000);
//强制结束Excel进程
if (pid > 0) //_excelApp != null &&
{
int ExcelProcess = OpenProcess(PROCESS_VM_READ | PROCESS_VM_WRITE, false, pid);
//判断进程是否仍然存在
if (ExcelProcess > 0)
{
try
{
//通过进程ID,找到进程
System.Diagnostics.Process process = System.Diagnostics.Process.GetProcessById(pid);
//Kill 进程
process.Kill();
}
catch
{
//强制结束Excel进程失败,可以记录一下日志.
//AddLog(@"退出", " 结束进程失败，异常：" + ex.Message, false);
//AddLog(@"退出", " " + ex.TargetSite, false);
}
}
else
{
//进程已经结束了
}
}
}