//打印整个workbook /// <summary> /// 打印整个workbook,使用默认pdf打印机转换为pdf /// </summary> /// <param name="fromExcelPath">源excel路径</param> /// <returns>成功或失败</returns> public bool ConvertExcelWorkbookPDF(string fromExcelPath) { bool flag = true; try { if (fromExcelPath.Length == 0) { flag = false; throw new Exception("需要转换的源文件路径不能为空。"); } Microsoft.Office.Interop.Excel.ApplicationClass applicationClass = new Microsoft.Office.Interop.Excel.ApplicationClass(); applicationClass.GetType(); Workbooks workbooks = applicationClass.Workbooks;//.get_Workbooks(); Type type = workbooks.GetType(); object obj = fromExcelPath; object[] objArray = new object[] { obj, true, true }; Microsoft.Office.Interop.Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)type.InvokeMember("Open", BindingFlags.InvokeMethod, null, workbooks, objArray); workbook.GetType(); object value = Missing.Value; //目标路径仅在打印失败时写入,成功时都默认在打印机路径下 //故不使用目标路径,直接使用打印机默认路径 workbook.PrintOutEx(value, value, value, value, value, false, value, value, value); //item.PrintOut(value, value, value, value, value, false, value, value); if (workbook != null) { workbook.Close(false, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); //Marshal.FinalReleaseComObject(workBook); workbook = null; } if (workbooks != null) { workbooks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; } if (applicationClass != null) { applicationClass.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(applicationClass); applicationClass = null; } GC.Collect(); GC.WaitForPendingFinalizers(); return(flag); } catch (Exception exception) { classLims_NPOI.WriteLog(exception, ""); throw exception; } finally { } }
public void CallXlConfigure(Products pd, string sFileName, NameValueCollection nParms, string sQry) { Microsoft.Office.Interop.Excel.Workbook wb; Microsoft.Office.Interop.Excel.ApplicationClass xl; xl = new Microsoft.Office.Interop.Excel.ApplicationClass(); xl.DisplayAlerts = false; // sFileName = @"C:\cci\"; // + sp.sFileName; // Build the call to setset the Excel Sheet try { if (pd.sVisible == "Y") { xl.Visible = true; } else { xl.Visible = false; } xl.Interactive = false; //Microsoft.Office.Core.MsoAutomationSecurity secAuto = (MsoAutomationSecurity)MsoAutomationSecurity.msoAutomationSecurityLow; //xl.Application.AutomationSecurity = secAuto; wb = xl.Workbooks.Open( sFileName , 2 , false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); int iSize = nParms.Count + 1; System.Threading.AutoResetEvent waiter = new System.Threading.AutoResetEvent(false); while (xl.Ready != true) { waiter.WaitOne(60 * 1000); } ; object[] oParms = new object[] { pd.sMethodCall }; // ,"Hello World!" // ,nvpKeys.Get("SystemNm") // ,nvpKeys.Get("FinanceType") // , null // }; int ix = 1; Array.Resize(ref oParms, iSize); foreach (string skey in nParms.Keys) { string sVal = ""; sVal = nParms.Get(skey); if (sVal != null) { if (sVal.Contains(",")) {// is an array object[] sTarget; sTarget = processstring(sVal); oParms[ix] = sTarget; } else { oParms[ix] = nParms.Get(skey); if (skey == "queryend") { oParms[ix] = sQry; } // oParms[ix] = nParms.Get(skey); } } else { oParms[ix] = nParms.Get(skey) == null ? "" : nParms.Get(skey); if ( skey == "specialind" || skey == "budgetind" || skey == "otherdepts" || skey == "salariedcodes" || skey == "salariedwithsupp" || skey == "certifiedcodes" || skey == "princodes" || skey == "suppcodes" ) { oParms[ix] = new object[] { "" }; } } ix += 1; } // Sub SetSheet(ConnectionNm As String, SystemNm As String, SystemType As String, FinanceType As String, UserType As String, ProcPrefix As String, QueryEnd As String) // //tcookcci: Added another tag called Maintenance in the XML //(Y or N) - if they are not on Maintenance, it will be blank for now. // The current QueryEnd is 070109 - once we are ready to update, it will change to 070110 try { if (pd.sMethodCall.Length > 0 && pd.sMethodCall != "None") { RunMacro(xl, oParms); } } catch (Exception ex) { xl.Quit(); throw ex; } Debug.Print(xl.Version); // if (pd.sReadOnly == "Y") wb.ChangeFileAccess(XlFileAccess.xlReadOnly, Type.Missing, false); // do file extension work // = false; try { Type type = xl.GetType(); PropertyInfo prop = type.GetProperty("CheckCompatibility"); prop.SetValue(xl, false, null); } catch { } string sFTarget = this.sTarget + pd.sProduct; if (String.Compare(xl.Version, "11.0") > 0) { if (File.Exists(sFTarget + ".xlsm")) { FileInfo fi2 = new FileInfo(sFTarget + ".xlsm"); fi2.IsReadOnly = false; fi2.Delete(); } wb.SaveAs(sTarget + pd.sProduct + ".xlsm" , 52, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } else { if (File.Exists(sFTarget + ".xls")) { FileInfo fi2 = new FileInfo(sFTarget + ".xls"); fi2.IsReadOnly = false; fi2.Delete(); } wb.SaveAs(sTarget + pd.sProduct + ".xls" , XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } // wb.Save(); xl.Quit(); } catch (Exception ex) { if (xl != null) { xl.Quit(); } throw ex; } finally { try { xl.Quit(); } catch { } } }
/// <summary> /// 按索引查找sheet,使用默认pdf打印机转换为pdf /// </summary> /// <param name="fromExcelPath">源excel路径</param> /// <param name="worksheetIndex">sheet索引,注意从1开始</param> /// <returns>成功或失败</returns> public bool ConvertExcelWorkSheetPDF_index(string fromExcelPath, int worksheetIndex) { bool flag = true; try { if (fromExcelPath.Length == 0) { flag = false; throw new Exception("需要转换的源文件路径不能为空。"); } Microsoft.Office.Interop.Excel.ApplicationClass applicationClass = new Microsoft.Office.Interop.Excel.ApplicationClass(); applicationClass.GetType(); Workbooks workbooks = applicationClass.Workbooks;//.get_Workbooks(); Type type = workbooks.GetType(); object obj = fromExcelPath; object[] objArray = new object[] { obj, true, true }; Microsoft.Office.Interop.Excel.Workbook workbook = (Microsoft.Office.Interop.Excel.Workbook)type.InvokeMember("Open", BindingFlags.InvokeMethod, null, workbooks, objArray); //workbooks.Open(this._sourcePath); //Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Item[0]; workbook.GetType(); //object obj1 = "c:\\temp.ps"; //obj1 = toPdfPath; object obj2 = "-4142"; //Microsoft.Office.Interop.Excel.Worksheet item = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Item[worksheetIndex]; Microsoft.Office.Interop.Excel.Worksheet item = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(worksheetIndex); if (item == null) { flag = false; throw new Exception("需要转换的WorkSheet名称不存在。"); } //item.get_Cells().get_Interior().set_ColorIndex(obj2); item.Cells.Interior.ColorIndex = obj2;//.get_Interior().set_ColorIndex(obj2); object value = Missing.Value; //item.PrintOut(value, value, value, value, value, true, value, obj1); //Microsoft.Office.Interop.Excel.XlFixedFormatType targetType = Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF; //item.ExportAsFixedFormat(targetType, obj1, Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,true,false, value, value, value, value); //目标路径仅在打印失败时写入,成功时都默认在打印机路径下 //故不使用目标路径,直接使用打印机默认路径 item.PrintOut(value, value, value, value, value, false, value, value); if (item != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(item); //Marshal.FinalReleaseComObject(sheet); item = null; } if (workbook != null) { workbook.Close(false, Type.Missing, Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); //Marshal.FinalReleaseComObject(workBook); workbook = null; } if (workbooks != null) { workbooks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; } if (applicationClass != null) { applicationClass.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(applicationClass); applicationClass = null; } GC.Collect(); GC.WaitForPendingFinalizers(); return(flag); } catch (Exception exception) { classLims_NPOI.WriteLog(exception, ""); throw exception; } finally { } }