private void btnTest2_Click(object sender, EventArgs e) { Excel.Application m_excel; Excel.Workbook m_workbook; m_excel = new Excel.Application();//对象实例化 m_workbook = m_excel.Workbooks.Open("D:\\test.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //m_workbook = m_excel.Workbooks.Open("D:\\test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); m_excel.Visible = true;//显示Excel内容(为false时,excel.exe进程在后台运行,未找到关闭方法) Range rng; object obj; String str; rng = (Excel.Range)m_excel.Cells[2, 2]; obj = rng.Value2; System.Diagnostics.Debug.WriteLine(obj.ToString()); //输出调试信息 str = rng.NumberFormatLocal; System.Diagnostics.Debug.WriteLine(str); //输出调试信息 this.txtTest2.Text = obj.ToString(); m_excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框 m_excel.AlertBeforeOverwriting = false; m_excel.Workbooks.Close(); //object oV = System.Reflection.Missing.Value; //反复用到 //System.Runtime.InteropServices.Marshal.ReleaseComObject(rng); //System.Runtime.InteropServices.Marshal.ReleaseComObject(m_excel); //GC.Collect(); m_excel.Quit(); }
private void QuitExcel(ref Excel.Application eps, ref Workbook wkCur) { try { if (wkCur != null) { wkCur.Save(); wkCur.Close(false); } //无论是否成功,都退出 if (eps != null) { PrintLog("Convert Excel file To PDF end"); eps.Quit(); } } catch (Exception ex) { PrintLog(ex.Message.ToString()); //m_nConvertStatus = EErrorType.OTP_EXCEPTION_FAILED; } finally { eps = null; wkCur = null; } }
private void exp_excel(bool print) { try { int be = 3, dong = 5, sodong = ds.Tables[0].Rows.Count + 5, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 1; tenfile = ttb.Export_Excel(ds, "bcngay"); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; oxl.ActiveWindow.DisplayGridlines = true; osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(0) + "1").EntireColumn.Delete(Missing.Value); for (int i = 0; i < be; i++) { osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value); } osheet.get_Range(ttb.getIndex(be) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00"; osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline; for (int i = 1; i < dong; i++) { osheet.Cells[dong - 1, i] = get_ten(i - 1); } orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(socot) + sodong.ToString()); osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 5] = "Cộng"; for (int i = 0; i < dsngay.Tables[0].Rows.Count; i++) { osheet.Cells[dong - 1, i + 5] = " " + dsngay.Tables[0].Rows[i]["ten"].ToString(); } osheet.get_Range(ttb.getIndex(4) + "4", ttb.getIndex(dsngay.Tables[0].Rows.Count + 5) + "4").Orientation = 90; osheet.get_Range(ttb.getIndex(0) + "4", ttb.getIndex(dsngay.Tables[0].Rows.Count + 5) + "4").RowHeight = 30; orange.Font.Name = "Arial"; orange.Font.Size = 8; orange.EntireColumn.AutoFit(); oxl.ActiveWindow.DisplayZeros = false; osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; osheet.PageSetup.LeftMargin = 20; osheet.PageSetup.RightMargin = 20; osheet.PageSetup.TopMargin = 30; osheet.PageSetup.CenterFooter = "Trang : &P/&N"; osheet.Cells[1, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv; osheet.Cells[1, 4] = "BÁO CÁO SỬ DỤNG"; osheet.Cells[2, 4] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày : " + tu.Text + " đến : " + den.Text; orange = osheet.get_Range(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "2"); orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; orange.Font.Size = 12; orange.Font.Bold = true; if (print) { osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } else { oxl.Visible = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <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); } }
private void butExcel_Click(object sender, System.EventArgs e) { get_data(); DataSet tmp = new DataSet(); tmp = ds.Copy(); tmp.Clear(); tmp.Merge(ds.Tables[0].Select("true", "tenbd")); if (tmp.Tables[0].Rows.Count > 0) { d.check_process_Excel(); string tenfile = d.Export_Excel(tmp, "dmthuoc"); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; for (int i = 0; i < 8; i++) { osheet.Cells[1, i + 1] = get_ten(i); } oxl.ActiveWindow.DisplayGridlines = true; oxl.ActiveWindow.DisplayZeros = false; int row = tmp.Tables[0].Rows.Count + 1; osheet.get_Range(d.getIndex(0) + "1", d.getIndex(tmp.Tables[0].Columns.Count - 1) + row.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline; osheet.PageSetup.CenterFooter = "Trang : &P/&N"; oxl.Visible = true; } }
public void Open(string file) { deactivateevents = true; try { if (xlApp == null) { xlApp = new Excel.Application(); } if (xlsWnd == 0) { xlsWnd = FindWindow("XLMAIN", null); } try { xlApp.CommandBars.AdaptiveMenus = false; xlApp.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(OnClose); SetParent(xlsWnd, this.Handle.ToInt32()); xlApp.Visible = true; xlApp.UserControl = true; SetWindowPos(xlsWnd, this.Handle.ToInt32(), 0, 0, this.Bounds.Width, this.Bounds.Height, SWP_NOZORDER | SWP_NOMOVE | SWP_DRAWFRAME | SWP_NOSIZE); OnResize(); } catch { } xlApp.Workbooks.Open(file, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); UnLock(); workbook = xlApp.Workbooks.get_Item(1); } finally { deactivateevents = false; } }
private void butExcel_Click(object sender, System.EventArgs e) { if (butExcel.Enabled == false) { return; } d.check_process_Excel(); string tenfile = d.Export_Excel(dt, ReportFile.Substring(0, ReportFile.Length - 4)); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; oxl.ActiveWindow.DisplayGridlines = true; oxl.ActiveWindow.DisplayZeros = false; try { osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; osheet.PageSetup.LeftMargin = 20; osheet.PageSetup.RightMargin = 20; osheet.PageSetup.TopMargin = 30; osheet.PageSetup.CenterFooter = "Trang : &P/&N"; } catch { } oxl.Visible = true; }
private void butIn_Click(object sender, System.EventArgs e) { d.check_process_Excel(); string sql = "select a.ma,a.tenhc,trim(a.ten)||' '||a.hamluong as ten,a.dang as dvt,a.gia_bh from " + user + ".d_dmbd a," + user + ".d_dmnhom c where a.manhom=c.id and a.nhom=" + i_nhom + " and gia_bh>0 and bhyt<>0"; sql += "order by "; if (d.bSort_mabd) { sql += " a.ma"; } else { sql += " a.ten"; } d.check_process_Excel(); string tenfile = d.Export_Excel(d.get_data(sql), "dmthuoc"); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; oxl.ActiveWindow.DisplayGridlines = true; oxl.ActiveWindow.DisplayZeros = false; osheet.PageSetup.Orientation = XlPageOrientation.xlPortrait; osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; osheet.PageSetup.LeftMargin = 20; osheet.PageSetup.RightMargin = 20; osheet.PageSetup.TopMargin = 30; osheet.PageSetup.CenterFooter = "Trang : &P/&N"; oxl.Visible = true; }
public void CreateExcel(System.Data.DataTable dt, string FilePath, string targetFileName) { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); for (int i = 0; i < dt.Columns.Count; i++) { excel.Cells[1, i + 1] = dt.Columns[i].ColumnName; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { try { excel.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); } catch { } } } //保存 SaveExcelFile(excel, FilePath, targetFileName); //打开显示 excel.Visible = true; // excel.Quit(); // excel=null; // GC.Collect();//垃圾回收 }
/// <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></param> public void CloseExcelApplication() { try { xlsWbs = null; xlsWb = null; xlsWs = null; xlsRg = null; if (xlsApp != null) { xlsApp.ActiveWorkbook.Close(false, null, null); xlsApp.Workbooks.Close(); //Object missing = Type.Missing; xlsApp.Quit(); xlsApp = null; //ReleaseAllRef(excelApplication);//Error } } finally { GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }
/// <summary> /// 基于excel.dll打开excel文件 /// </summary> /// <param name="FileName">文件名</param> public void OpenExcel(string FileName) { //打开excel app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); app.Visible = true; }
/// <summary> /// 关闭Excel /// </summary> public void Close() { myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); }
/// <summary> /// 关闭Excel /// </summary> /// <param name="isSave">是否保存</param> /// <param name="fileName">存储文件名</param> public void Close(bool isSave, string fileName) { myWorkBook.Close(isSave, fileName, Type.Missing); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); }
public ClsAllExcel() { xlsApp = null; xlsWbs = null; xlsWb = null; xlsWs = null; ActiveSheetIndex = 1; }
public static void doit(Excel.Application app, ArrayList excelnames, string command) { ArrayList prods = new ArrayList(); ArrayList words = new ArrayList(); Hashtable nts = new Hashtable(); foreach (string excelname in excelnames) { Excel.Workbook wb = app.Workbooks.Open( excelname, // Filename 0, // UpdateLinks true, // ReadOnly 5, // Format "", // Password "", // WriteResPassword true, // IgnoreReadOnlyRecommended Excel.XlPlatform.xlWindows, // Origin "\t", // Delimiter false, // Editable false, // Notify 0, // Converter true, // AddToMru true, // Local true // CorruptLoad ); prods = process(wb, prods); nts = processTypes(wb, nts); words = processKeyword(wb, words); } switch (command) { default: System.Console.WriteLine("usage: a.out (grammar|keywords) excelfile"); break; case "gram": prods = addOpts(prods, nts); foreach (production p in prods) { System.Console.WriteLine(p.prod); } break; case "rewrite": prods = addOpts(prods, nts); emitParse2AST(nts, prods); break; case "keywords": foreach (string s in words) { System.Console.WriteLine(s); } break; } }
public void Open(string FileName)//打开一个Excel文件 { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); //wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing); //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); mFilename = FileName; }
public void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom) { app = Application as Application; app.WorkbookOpen += AppOnWorkbookOpen; app.WorkbookBeforeSave += AppOnWorkbookBeforeSave; app.WorkbookBeforeClose += AppOnWorkbookBeforeClose; app.SheetSelectionChange += AppOnSheetSelectionChange; app.SheetActivate += AppOnSheetActivate; }
private static void KillSpecialExcel(Excel.Application m_objExcel) { if (m_objExcel != null) { int lpdwProcessId; GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId); System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill(); } }
public void CreateExcel(IDictionary <string, System.Data.DataTable> iDicDT, string FilePath, bool Visible) { Excel.Application excel = new Excel.Application(); //引用Excel对象 Excel.Workbook book = excel.Application.Workbooks.Add(System.Reflection.Missing.Value); //引用 Excel.Worksheet sheet3 = (Excel.Worksheet)book.Worksheets["Sheet3"];//选中 object nothing = System.Reflection.Missing.Value; book.Worksheets.Add(nothing, sheet3, iDicDT.Count - 3, nothing); int index = 0; foreach (KeyValuePair <string, System.Data.DataTable> val in iDicDT) { index++; Excel.Worksheet tempExcel = (Excel.Worksheet)book.Worksheets["Sheet" + index];//选中 tempExcel.Name = val.Key; System.Data.DataTable tempdt = val.Value; for (int i = 0; i < tempdt.Columns.Count; i++) { tempExcel.Cells[1, i + 1] = tempdt.Columns[i].ColumnName; } for (int i = 0; i < tempdt.Rows.Count; i++) { for (int j = 0; j < tempdt.Columns.Count; j++) { try { tempExcel.Cells[i + 2, j + 1] = tempdt.Rows[i][j].ToString(); } catch { } } } } //保存 if (!string.IsNullOrEmpty(FilePath)) { SaveExcelFile(excel, FilePath); } //打开显示 excel.Visible = Visible; excel.Quit(); excel = null; GC.Collect();//垃圾回收 }
private void exp_excel(bool print) { try { ttb.check_process_Excel(); ds = dsxml.Copy(); int be = 2, dong = 4, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1; tenfile = ttb.Export_Excel(ds, "dutru"); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; oxl.ActiveWindow.DisplayGridlines = true; for (int i = 0; i < be; i++) { osheet.get_Range(ttb.getIndex(i) + "1", ttb.getIndex(i) + "1").EntireRow.Insert(Missing.Value); } osheet.get_Range(ttb.getIndex(be - 1) + dong.ToString(), ttb.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00"; osheet.get_Range(ttb.getIndex(0) + "3", ttb.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline; int pos = 7; foreach (DataRow r in dtmakp.Select("makp<>''", "stt,makp")) { osheet.Cells[dong - 1, pos] = r["tenkp"].ToString(); pos += 1; } //osheet.Cells[dong-1,pos]="Tồn "+makho.Text; for (int i = 0; i < 6; i++) { osheet.Cells[dong - 1, i + 1] = get_ten(i); } orange = osheet.get_Range(ttb.getIndex(0) + "1", ttb.getIndex(socot) + sodong.ToString()); orange.Font.Name = "Arial"; orange.Font.Size = 8; orange.EntireColumn.AutoFit(); oxl.ActiveWindow.DisplayZeros = false; osheet.Cells[1, 2] = ttb.Syte; osheet.Cells[2, 2] = ttb.Tenbv; s_mmyy = tu.Value.ToString().PadLeft(2, '0') + yyyy.Value.ToString().PadLeft(4, '0').Substring(2, 2); osheet.Cells[1, 4] = "TỔNG HỢP DÙ TRÙ"; string s_title = ttb.title(tu.Value.ToString(), tu.Value.ToString()) + " năm " + yyyy.Value.ToString(); osheet.Cells[2, 4] = s_title; orange = osheet.get_Range(ttb.getIndex(3) + "1", ttb.getIndex(socot - 1) + "2"); orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; orange.Font.Size = 12; orange.Font.Bold = true; if (print) { osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } else { oxl.Visible = true; } } catch { } }
public void StartExcel() { // Create new Excel instance and add a workbook. // Using the Excel Wrapper for EPLAN Scripts this looks like COM Interop: Excel.Application xlsApp = new Excel.Application(); xlsApp.Visible = true; xlsApp.Workbooks.Add(); // Your code here... }
//关闭Excel public void CloseExcel() { wb.Close(); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; GC.Collect(); }
private void exp_excel() { try { string s; int be = 3, dong = 6, sodong = ds.Tables[0].Rows.Count + 6, socot = ds.Tables[0].Columns.Count - 2, dongke = sodong - 2; tenfile = d.Export_Excel(ds, "biendong"); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; oxl.ActiveWindow.DisplayGridlines = true; for (int i = 0; i < be; i++) { osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value); } osheet.Cells[dong - 2, 3] = "Tên hoạt chất"; osheet.Cells[dong - 2, 4] = "Tên thuốc - hàm lượng"; osheet.Cells[dong - 2, 5] = "ĐVT"; osheet.Cells[dong - 2, 6] = "Diễn giải"; for (int i = 0; i < i_rec; i++) { s = ds.Tables[0].Columns[i + 6].ColumnName.ToString(); osheet.Cells[dong - 2, i + 7] = "'" + s.Substring(4, 2) + "/" + s.Substring(2, 2) + "/" + s.Substring(0, 2); } osheet.Cells[dong - 2, i_rec + 7] = "Tỷ lệ"; osheet.Cells[dong - 2, i_rec + 8] = "Ghi chú"; osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value); osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value); osheet.get_Range(d.getIndex(be) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00"; osheet.get_Range(d.getIndex(0) + "4", d.getIndex(socot - 1) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline; orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString()); orange.Font.Name = "Arial"; orange.Font.Size = 8; orange.EntireColumn.AutoFit(); oxl.ActiveWindow.DisplayZeros = false; osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; osheet.PageSetup.LeftMargin = 20; osheet.PageSetup.RightMargin = 20; osheet.PageSetup.TopMargin = 30; osheet.PageSetup.CenterFooter = "Trang : &P/&N"; osheet.Cells[1, 1] = d.Syte; osheet.Cells[2, 1] = d.Tenbv; osheet.Cells[1, 3] = "BIẾN ĐỘNG GIÁ THUỐC"; osheet.Cells[2, 3] = "Từ ngày " + tu.Text + " đến " + den.Text; orange = osheet.get_Range(d.getIndex(2) + "1", d.getIndex(socot - 1) + "2"); orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; orange.Font.Size = 12; orange.Font.Bold = true; oxl.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void exp_excel(bool print) { d.check_process_Excel(); ds = dsxml.Copy(); int be = 2, dong = 4, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1; tenfile = d.Export_Excel(ds, "cosotutruc"); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; oxl.ActiveWindow.DisplayGridlines = true; for (int i = 0; i < be; i++) { osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value); } osheet.get_Range(d.getIndex(be - 1) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = "#,##0.00"; osheet.get_Range(d.getIndex(0) + "3", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline; for (int i = 0; i < 8; i++) { osheet.Cells[dong - 1, i + 1] = get_ten(i); } orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString()); orange.Font.Name = "Arial"; orange.Font.Size = 8; orange.EntireColumn.AutoFit(); oxl.ActiveWindow.DisplayZeros = false; osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; osheet.PageSetup.CenterFooter = "Trang : &P/&N"; osheet.Cells[1, 2] = d.Syte; osheet.Cells[2, 2] = d.Tenbv; osheet.Cells[1, 4] = "BÁO CÁO SỬ DỤNG NỘI TRÚ"; string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } osheet.Cells[2, 4] = s_title; orange = osheet.get_Range(d.getIndex(3) + "1", d.getIndex(socot - 1) + "2"); orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; orange.Font.Size = 12; orange.Font.Bold = true; if (print) { osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } else { oxl.Visible = true; } }
//关闭一个Excel对象,销毁对象 public void Close() { //wb.Save(); wb.Close(Type.Missing, Type.Missing, Type.Missing); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; GC.Collect(); }
public static void Kill(Excel.Application excel) { try { IntPtr t = new IntPtr(excel.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); } catch { } }
public void Export2DatasetDSPhongThi(System.Data.DataSet i_DataSet, string i_TableName, int i_iSheetStartRow) { try { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); m_objExcelApp = new Excel.Application(); m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null); m_objExcelApp.Workbooks[1].Worksheets.Select(1); m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]); int i_iExcelRow = 0; bool v_bol_stop = false; while (!v_bol_stop) { int i_iExcelCol = 0; System.Data.DataRow v_iDataRow = default(System.Data.DataRow); v_iDataRow = i_DataSet.Tables[i_DataSet.Tables[i_TableName].TableName].NewRow(); v_iDataRow[i_iExcelCol] = i_iExcelCol + 1; for (i_iExcelCol = 0; i_iExcelCol <= i_DataSet.Tables[i_TableName].Columns.Count - 2; i_iExcelCol++) { if (!object.ReferenceEquals(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, 4]/*.Value()*/, null)) { if (!(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/ == null)) { v_iDataRow[i_iExcelCol + 1] = m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/; } } else { v_bol_stop = true; } } if (!v_bol_stop) { i_DataSet.Tables[i_TableName].Rows.InsertAt(v_iDataRow, i_iExcelRow); i_iExcelRow++; } } m_objExcelApp.DisplayAlerts = false; m_objExcelApp.Workbooks.Close(); m_objExcelApp.DisplayAlerts = true; m_objExcelApp.Quit(); Unmount(); } catch (Exception v_e) { m_objExcelApp.DisplayAlerts = false; m_objExcelApp.Workbooks.Close(); m_objExcelApp.DisplayAlerts = true; m_objExcelApp.Quit(); Unmount(); throw (v_e); } }
public CExcelReport(string i_strTemplateFileWithoutPath, int i_iSheetStartRow, int i_iSheetStartCol) { InitPaths(); m_strTemplateFileNameWithPath = m_strTemplatesPath + i_strTemplateFileWithoutPath; m_iSheetStartCol = i_iSheetStartCol; m_iSheetStartRow = i_iSheetStartRow; m_objExcelApp = new Excel.Application(); FindAndReplaceCollection = new Hashtable(); m_init_successful = false; m_b_haved_show = false; init_excel(); }
/// <summary> /// 直接创建Excel表 /// </summary> /// <param name="dtSource">数据源DataTable</param> /// <param name="filePath">保存路径</param> /// <param name="drTitle">标题DataRow</param> /// <param name="TableName">Excel表头</param> /// <returns></returns> public static bool Save(System.Data.DataTable dtSource, string filePath, System.Data.DataRow drTitle, string TableName) { Excel.Application app = new Excel.Application();//EXCEL实例 try { app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; if (dtSource.Rows.Count > 0) { int row = dtSource.Rows.Count + 1; int col = dtSource.Columns.Count + 1; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = dtSource.Rows[i][j].ToString(); wSheet.Cells[i + 3, j + 1] = str; } } } int size = dtSource.Columns.Count; for (int i = 0; i < size; i++) { wSheet.Cells[2, 1 + i] = drTitle[i].ToString(); } wSheet.get_Range(wSheet.Cells[1, 0], wSheet.Cells[1, size]).Merge(Type.Missing); wSheet.Cells[1, 1] = TableName; //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(filePath); app.SaveWorkspace(filePath); app.Quit(); app = null; return(true); } catch { return(false); } finally { } }
private void createExcel() { Excel.Application excelApp = null; Excel.Workbook workbook = null; Excel.Sheets sheets = null; Excel.Worksheet newSheet = null; try { FileInfo file = new FileInfo(fileLoc); if (file.Exists) { excelApp = new Excel.Application(); workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); sheets = workbook.Sheets; //check columns exist foreach (Excel.Worksheet sheet in sheets) { Console.WriteLine(sheet.Name); sheet.Select(Type.Missing); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); } newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing); newSheet.Name = "My New Sheet"; newSheet.Cells[1, 1] = "BOO!"; workbook.Save(); workbook.Close(null, null, null); excelApp.Quit(); } } finally { System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); newSheet = null; sheets = null; workbook = null; excelApp = null; GC.Collect(); } }
private void exp_excel(bool print) { d.check_process_Excel(); ds = dsxml.Copy(); int dong = 2, sodong = ds.Tables[0].Rows.Count + dong, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong - 1; tenfile = d.Export_Excel(ds, "cosotutruc"); oxl = new Excel.Application(); owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value)); osheet = (Excel._Worksheet)owb.ActiveSheet; oxl.ActiveWindow.DisplayGridlines = true; osheet.get_Range(d.getIndex(3) + dong.ToString(), d.getIndex(socot + 1) + sodong.ToString()).NumberFormat = format_soluong; osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline; int pos = 5; foreach (DataRow r in dtmakp.Select("makp<>''", "stt,makp")) { osheet.Cells[dong - 1, pos] = r["tenkp"].ToString(); pos += 1; } for (int i = 0; i < 4; i++) { osheet.Cells[dong - 1, i + 1] = get_ten(i); } orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot) + sodong.ToString()); orange.Font.Name = "Arial"; orange.Font.Size = 10; orange.EntireColumn.AutoFit(); oxl.ActiveWindow.DisplayZeros = false; osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; osheet.PageSetup.CenterFooter = "Trang : &P/&N"; string s_title = "Từ ngày " + tu.Text + " đến ngày " + den.Text; if (tu.Text == den.Text) { s_title = "Ngày " + tu.Text; } osheet.PageSetup.LeftHeader = d.Syte + "\n" + d.Tenbv; osheet.PageSetup.CenterHeader = "&\"Arial,Bold\"&14BÁO CÁO CƠ SỐ TỦ TRỰC\n" + s_title; if (print) { osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } else { oxl.Visible = true; } }
public Excel.Application StartApplication() { if (this.app != null) { app.Quit(); app = null; return app = new Excel.Application(); } else { return app = new Excel.Application(); } }
public Class1(string strXL, object extraMenuItem, SqlConnection conn) { Conn = conn; try { excelApp = new Excel.ApplicationClass(); workbook = excelApp.Workbooks.Open(strXL, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); } catch(Exception ex) { MessageBox.Show("在打开Excel文件{0}时产生异常: " + ex.ToString()); } }
/// <summary> /// ����һ��Excelʾ�� /// </summary> public void CreateExcel() { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Cells[1, 1] = "��1�е�1��"; excel.Cells[1, 2] = "��1�е�2��"; excel.Cells[2, 1] = "��2�е�1��"; excel.Cells[2, 2] = "��2�е�2��"; excel.Cells[3, 1] = "��3�е�1��"; excel.Cells[3, 2] = "��3�е�2��"; //���� excel.ActiveWorkbook.SaveAs("./tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); //����ʾ excel.Visible = true; // excel.Quit(); // excel=null; // GC.Collect();//�������� }
private DateTime afterTime; //Excel启动之后时间 #region 创建一个Excel示例 /// <summary> /// 创建一个Excel示例 /// </summary> public void CreateExcel() { Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); excel.Cells[1, 1] = "第1行第1列"; excel.Cells[1, 2] = "第1行第2列"; excel.Cells[2, 1] = "第2行第1列"; excel.Cells[2, 2] = "第2行第2列"; excel.Cells[3, 1] = "第3行第1列"; excel.Cells[3, 2] = "第3行第2列"; //保存 excel.ActiveWorkbook.SaveAs("./tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); //打开显示 excel.Visible = true; // excel.Quit(); // excel=null; // GC.Collect();//垃圾回收 }
public ExcelController(bool excelVisible, IReport report) { dtBefore = System.DateTime.Now; CurExcel = new Excel.ApplicationClass(); CurExcel.DisplayAlerts = false; dtAfter = System.DateTime.Now; CurExcel.Visible = excelVisible; //CurExcel.Workbooks.Add(true); CurExcel.Workbooks.Add(mValue); sheetIndex = 1; CurSheet = CurExcel.Worksheets[sheetIndex] as Excel.Worksheet; CurBook = CurExcel.Workbooks[1]; pictureIndex = 0; log = new EasilyReportLog("Excel Report", this.GetType().FullName, LogFileInfo.logFileName, report); }
protected override void Dispose(bool disposing) { ApplicationControllerTrace trace = new ApplicationControllerTrace("Dispose requested"); lock (m_lock) { if (m_disposed) return; m_disposed = true; trace.Log("Dispose started"); try { Quit(); } catch (System.Exception e) { trace.Log(e.Message); // ignore. } finally { trace.Log("Releasing host application"); if (null != m_hostApplication) Marshal.FinalReleaseComObject(m_hostApplication); m_hostApplication = null; trace.Log("Dispose completed"); } base.Dispose(disposing); } }
/// <summary> /// �ͷ��ڴ�ռ� /// </summary> public void Dispose() { 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) { // MessageBox.Show("���ͷ�Excel�ڴ�ռ�ʱ������һ������"+ex.Message); } finally { //foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel")) // if (pro.StartTime > this.dtBefore && pro.StartTime < this.dtAfter) // { // try // { // pro.Kill(); // } // catch { } // } } System.GC.SuppressFinalize(this); }
/// <summary> /// 将DataTable的数据导出显示为报表 /// </summary> /// <param name="dt">要导出的数据</param> /// <param name="strTitle">导出报表的标题</param> /// <param name="FilePath">保存文件的路径</param> /// <returns></returns> public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath) { beforeTime = DateTime.Now; Excel.Application excel; Excel._Workbook xBk; Excel._Worksheet xSt; int rowIndex = 4; int colIndex = 1; excel = new Excel.Application(); xBk = excel.Workbooks.Add(true); xSt = (Excel._Worksheet)xBk.ActiveSheet; //取得列标题 foreach (DataColumn col in dt.Columns) { colIndex++; excel.Cells[4, colIndex] = col.ColumnName; //设置标题格式为居中对齐 xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true; xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select(); xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色,共计有56种 } //取得表格中的数据 foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 1; foreach (DataColumn col in dt.Columns) { colIndex++; if (col.DataType == System.Type.GetType("System.DateTime")) { excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐 } else if (col.DataType == System.Type.GetType("System.String")) { excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString(); xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐 } else { excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } } } //加载一个合计行 int rowSum = rowIndex + 1; int colSum = 2; excel.Cells[rowSum, 2] = "合计"; xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //设置选中的部分的颜色 xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select(); //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色,共计有56种 //取得整个报表的标题 excel.Cells[2, 2] = strTitle; //设置整个报表的标题格式 xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true; xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22; //设置报表表格为最适应宽度 xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select(); xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit(); //设置整个报表的标题为跨列居中 xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select(); xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection; //绘制边框 xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1; xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗 xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗 xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗 xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗 afterTime = DateTime.Now; //显示效果 //excel.Visible=true; //excel.Sheets[0] = "sss"; ClearFile(FilePath); string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls"; excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null); //wkbNew.SaveAs strBookName; //excel.Save(strExcelFileName); #region 结束Excel进程 //需要对Excel的DCOM对象进行配置:dcomcnfg //excel.Quit(); //excel=null; xBk.Close(null, null, null); excel.Workbooks.Close(); excel.Quit(); //注意:这里用到的所有Excel对象都要执行这个操作,否则结束不了Excel进程 // if(rng != null) // { // System.Runtime.InteropServices.Marshal.ReleaseComObject(rng); // rng = null; // } // if(tb != null) // { // System.Runtime.InteropServices.Marshal.ReleaseComObject(tb); // tb = null; // } if (xSt != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xSt = null; } if (xBk != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); xBk = null; } if (excel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; } GC.Collect();//垃圾回收 #endregion return filename; }
private void btnXZYB_Click(object sender, EventArgs e) { OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.InitialDirectory = @"F:\昆钢项目\"; //指定打开文件默认路径 openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名 openFileDialog.RestoreDirectory = true; openFileDialog.FilterIndex = 1; if (openFileDialog.ShowDialog() == DialogResult.OK) { app = new Excel.Application(); app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; object MissingValue = Type.Missing; workbook = workbooks.Add(openFileDialog.FileName); txtYBWJ.Text = openFileDialog.FileName; } }
/// <summary> /// 读取并保存Excel中的信息到数据库中 /// </summary> private void ReadAndSaveExcelInfo() { //Workbook wBook = excel.Workbooks.Add(true); Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); //Worksheet wSheet = (Excel._Worksheet)wBook.ActiveSheet; //Worksheet worksheet = (Excel.Worksheet)wBook.ActiveSheet; app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; string strTmpTable, strTmpField, strTmpValue; string val1 = ""; string val2 = ""; string val3 = ""; string val4 = ""; string val5 = ""; string val6 = ""; string val7 = ""; string val8 = ""; string val9 = ""; string val10 = ""; string val11 = ""; int num = 0; Excel.Range range = worksheet.get_Range("A2", "P1000");//A2、O1000表示到Excel从第A列的第2行到第O列的第1000-1行 A2、P65535表示到Excel从第A列的第2行到第P列的第65535-1行 System.Array values = (System.Array)range.Formula; num = values.GetLength(0); for (int i = 1; i <= num; i++) { if (values.GetValue(i, 1).ToString().Trim() == "" || values.GetValue(i, 2).ToString().Trim() == "") { break; } else { val1 = values.GetValue(i, 1).ToString().Trim(); //记帐日期 val2 = values.GetValue(i, 2).ToString().Trim(); //订单号 val3 = values.GetValue(i, 3).ToString().Trim(); //订单行项目号 val4 = values.GetValue(i, 4).ToString().Trim(); //物料编码 val5 = values.GetValue(i, 5).ToString().Trim(); //物料名称 val6 = values.GetValue(i, 6).ToString().Trim(); //批号 val7 = values.GetValue(i, 7).ToString().Trim(); //收货数量 val8 = values.GetValue(i, 8).ToString().Trim(); //计量单位 val9 = values.GetValue(i, 9).ToString().Trim(); //工厂 val10 = values.GetValue(i, 10).ToString().Trim(); //库存地点 val11 = values.GetValue(i, 11).ToString().Trim(); //抬头文本 string strLRR = CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName(); string strLRD = CoreFS.SA06.CoreUserInfo.UserInfo.GetDepartment(); strTmpTable = "DT_SAP261"; strTmpField = "FS_WEIGHTNO,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_MATERIAL,FS_MATERIALNAME,FS_STOVENO," + "FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,FS_HEADER,FS_WEIGHTTYPE,FS_DRDW,FS_AUDITOR"; strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + val1 + "','" + val2 + "','" + val3 + "','" + val4 + "','" + val5 + "','" + val6 + "'," + val7 + ",'" + val9 + "','" + val10 + "','" + val11 + "'," + "'261',"; /* if ((CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC1001") || (CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC1002")) { strTmpValue += "'棒线厂一作',"; } if (CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC2001") { strTmpValue += "'棒线厂二作',"; }*/ strTmpValue += "'" + strLRD + "','" + strLRR + "'"; CoreClientParam ccp = new CoreClientParam(); ccp.ServerName = "ygjzjl.base.QueryData"; ccp.MethodName = "insertDataInfo"; ccp.ServerParams = new object[] { strTmpTable, strTmpField, strTmpValue }; try { this.ExecuteNonQuery(ccp, CoreInvokeType.Internal); } catch(Exception ex1) { lstHint1.Items.Add(ex1.Message + "第" + i.ToString() + "行信息导入成功!"); } } } app.Quit(); app = null; lstHint1.Items.Add("信息导入成功!"); System.Diagnostics.Process[] myProcesses; myProcesses = System.Diagnostics.Process.GetProcesses(); foreach (System.Diagnostics.Process myProcess in myProcesses) { if(myProcess.ProcessName == "EXCEL") myProcess.Kill(); } }
private void DataGridViewToExcel() { bool flag = false; if (this._GrdData != null) { Exception exception2; this.dBegin = DateTime.Now; if (this.appExcel == null) { this.appExcel = new ApplicationClass(); } this.wkBook = this.appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); this.wkSheet = (Worksheet) this.wkBook.Worksheets[1]; this.appExcel.Visible = false; this.dEnd = DateTime.Now; this.nCols = this._GrdData.ColumnCount; this.nRows = this._GrdData.Rows.Count; this.nCurrR = 1; this.nCurrC = 1; if (this._RptTitle != "") { this.SetControlText(this.lblState, "正在处理报表头......"); this.wkSheet.Cells[this.nCurrR, this.nCurrC] = this._RptTitle; this.range = this.wkSheet.get_Range(this.wkSheet.Cells[this.nCurrR, 1], this.wkSheet.Cells[this.nCurrR, this.nCols]); this.range.MergeCells = true; this.range.Font.Bold = true; this.range.Font.Size = 0x12; this.range.Font.Name = "宋体"; this.range.HorizontalAlignment = XlHAlign.xlHAlignCenter; this.range.VerticalAlignment = XlVAlign.xlVAlignCenter; this.range.EntireRow.AutoFit(); this.nRows++; this.nCurrR++; } this.nRows++; this.SetProgressMaxMin(this.prgMain, this.nRows, 0); this.SetProgressValue(this.prgMain, this.nCurrR); this.SetControlText(this.lblState, "正在处理列头......"); this.nCurrC = 1; while (this.nCurrC <= this.nCols) { this.wkSheet.Cells[this.nCurrR, this.nCurrC] = this._GrdData.Columns[this.nCurrC - 1].HeaderText; this.nCurrC++; } this.nCurrR++; try { foreach (DataGridViewRow row in (IEnumerable) this._GrdData.Rows) { this.nCurrC = 0; while (this.nCurrC < this.nCols) { Exception exception; string str3; if (row.Cells[this.nCurrC].Value != null) { try { string str2 = row.Cells[this.nCurrC].Value.ToString().Trim(); double a; if (str2.StartsWith("=") || double.TryParse(str2,out a)) { str2 = "'" + str2; } this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1] = str2; } catch (Exception exception1) { exception = exception1; str3 = this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1].ToString(); string str4 = row.Cells[this.nCurrC].Value.ToString(); MessageBox.Show(string.Format("在:nCurrC=[{3}] nCurrR=[{4}]==[{0}]=:=[{1}]=:=[{2}] nCols=[{5}]", new object[] { str3, exception.Message, str4, this.nCurrC, this.nCurrR, this.nCols })); } } else { try { this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1] = ""; } catch (Exception exception3) { exception = exception3; str3 = this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1].ToString(); } } this.nCurrC++; } this.SetControlText(this.lblState, "正在处理数据......(" + this.nCurrR.ToString() + "/" + this.nRows.ToString() + ")"); this.SetProgressValue(this.prgMain, this.nCurrR); this.nCurrR++; } } catch (Exception exception4) { exception2 = exception4; MessageBox.Show(exception2.Source + "\r\n" + exception2.Message + exception2.StackTrace + "\r\n" + exception2.TargetSite.ToString()); } this.range = null; this.range = this.wkSheet.get_Range(this.wkSheet.Cells[1, 1], this.wkSheet.Cells[this.nRows, this.nCols]); this.range.Cells.Borders.LineStyle = XlLineStyle.xlContinuous; this.range.Borders.Weight = XlBorderWeight.xlThin; this.range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlMedium; this.range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlMedium; this.range.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlMedium; this.range.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium; if (this._FileName.Trim() == "") { this._FileName = Path.Combine(System.Windows.Forms.Application.StartupPath, "datagrid.xls"); } if (this._FileName.ToLower().IndexOf(".xls") <= 0) { this._FileName = this._FileName + ".xls"; } if (File.Exists(this._FileName)) { try { File.Delete(this._FileName); } catch (Exception exception5) { exception2 = exception5; MessageBox.Show(exception2.Message); } } try { this.wkBook.SaveAs(this._FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); flag = true; } catch (Exception exception6) { exception2 = exception6; flag = false; MessageBox.Show(exception2.Message); } try { this.appExcel.Workbooks.Close(); this.appExcel.Quit(); DataIE.KillProgress((IntPtr) this.appExcel.Hwnd); this.appExcel = null; } catch (Exception exception7) { exception2 = exception7; flag = false; MessageBox.Show(exception2.Message); } if (flag) { MessageBox.Show("已经成功导出:" + this._FileName); } base.Close(); } }
public int GetCountRow() { try { m_objExcelApp = new Excel.Application(); System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null); m_objExcelApp.Workbooks[1].Worksheets.Select(1); m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]); return m_objExcelWorksheet.UsedRange.Rows.Count; } catch (Exception v_e) { throw (v_e); } }
public override void Quit() { base.Quit(); lock (m_lock) { if (null == m_hostApplication) return; ApplicationControllerTrace trace = new ApplicationControllerTrace("Quit started"); try { if (m_hostApplication.Visible) return; WaitForExcelToRespond(); m_hostApplication.DisplayAlerts = false; m_hostApplication.Quit(); trace.Log("Quit complete"); } catch (COMException e) { trace.Log(e); if (RPC_E_DISCONNECTED != e.ErrorCode) throw e; } finally { trace.Log("Releasing application"); if (null != m_hostApplication) Marshal.FinalReleaseComObject(m_hostApplication); m_hostApplication = null; } } }
private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e) { switch (e.Tool.Key.ToString()) { case "Query": if (!Decision())//判断时间区间//杨滔添加 { return; } { Query(); break; } case "Update": { if (this.ultraGrid3.ActiveRow == null || this.ultraGrid3.ActiveRow.Selected == false || this.ultraGrid3.Rows.Count <= 0) { MessageBox.Show("请选择一条信息", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (DialogResult.Yes == MessageBox.Show("您确认要修改该条记录吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information)) { Update(); Query(); } break; } case "Add": { Add(); Query(); break; } case "Delete": { if (DialogResult.Yes == MessageBox.Show("是否确认要删除当前数据?", "删除提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)) { Delete(); Query(); } break; } case "Print": { Print(); Query(); break; } case "Print1": { Print1(); Query(); break; } case "ChooseExcel": { OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.InitialDirectory = @"C:\桌面\"; //指定打开文件默认路径 openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名 openFileDialog.RestoreDirectory = true; openFileDialog.FilterIndex = 1; if (openFileDialog.ShowDialog() == DialogResult.OK) { app = new Excel.Application(); app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; object MissingValue = Type.Missing; workbook = workbooks.Add(openFileDialog.FileName); txtYBWJ.Text = openFileDialog.FileName; } break; } case "BatchUpload": { //this.ClearData(); if (this.txtYBWJ.Text == "") { MessageBox.Show("请选择要导入的预报信息!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } this.ReadAndSaveExcelInfo(); Query(); this.txtYBWJ.Text = ""; break; } default: break; } }
public void OpenExcelAppWithFile(string filePath, int sheetIndex, string sheetName, bool visible) { _excelApplication = new Excel.Application(); _excelApplication.Visible = visible; _workBooks = (Excel.Workbooks)_excelApplication.Workbooks; _workBook = (Excel._Workbook)(_workBooks.Add(filePath)); _excelSheets = (Excel.Sheets)_workBook.Worksheets; _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(sheetIndex)); _excelSheet.Name = sheetName; }
public void WritePlantSchedule(IOrderedEnumerable<KeyValuePair<string, MRPlantScheduleData>> data, string savepath) { app = StartApplication(); app.Visible = false; app.UserControl = false; app.ScreenUpdating = false; var path = Directory.GetCurrentDirectory(); var wb = app.Workbooks.Open(path + @"\Excel Templates\PlantSchedule.xlsx"); Excel.Worksheet ws = wb.Worksheets["Data"]; var datavalues = data.ToDictionary<KeyValuePair<string, MRPlantScheduleData>,string>(x=>x.Key).Values; var r = 5; foreach(var x in datavalues) { var fields = x.Value.GetType().GetFields(); for(var i = 0; i < fields.Count<FieldInfo>(); i++) { ws.Cells[r, i + 1].Value = fields[i].GetValue(x.Value); } r += 1; } string fileName = savepath + "\\" + Guid.NewGuid().ToString() + ".xlsx"; wb.SaveAs(fileName); wb.Close(); wb = app.Workbooks.Open(fileName); app.Visible = true; app.UserControl = true; app.ScreenUpdating = true; }
/// <summary> /// 从EXCEL导入数据 /// </summary> private void openExcel() { OpenFileDialog openFileDialog = new OpenFileDialog(); // openFileDialog.InitialDirectory = @"E:\计量系统\安装3.0\"; //指定打开文件默认路径 openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名 openFileDialog.RestoreDirectory = true; openFileDialog.FilterIndex = 1; if (openFileDialog.ShowDialog() == DialogResult.OK) { app = new Excel.Application(); app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; object MissingValue = Type.Missing; workbook = workbooks.Add(openFileDialog.FileName); } if (openFileDialog.FileName == "") { MessageBox.Show("请选择导入的Excel文件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } ReadAndSaveExcelInfo(); }
public void Export2Grid(C1FlexGrid i_fg, int i_iSheetStartRow, int i_iSheetCol, int i_iGridCol) { try { System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); m_objExcelApp = new Excel.Application(); m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null); m_objExcelApp.Workbooks[1].Worksheets.Select(1); m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]); int v_iGridRow = 0; for (v_iGridRow = i_fg.Rows.Fixed; v_iGridRow <= i_fg.Rows.Count - 1; v_iGridRow++) { i_fg[v_iGridRow, i_iGridCol] = m_objExcelWorksheet.Cells[i_iSheetStartRow + v_iGridRow - i_fg.Rows.Fixed, i_iSheetCol];//.Value } m_objExcelApp.Workbooks.Close(); m_objExcelApp.Quit(); Unmount(); } catch (Exception v_e) { m_objExcelApp.Workbooks.Close(); m_objExcelApp.Quit(); Unmount(); throw (v_e); } }
/// <summary> /// 把DataTable中的数据导出到Excel /// </summary> /// <param name="dt"></param> public static void DataTableToExcel(System.Data.DataTable srcDt,string savename) { System.Data.DataTable dt = new System.Data.DataTable(); dt = srcDt; if (dt == null) return; string saveFileName = ""; bool fileSaved = false; /*SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xlsx"; saveDialog.Filter = "Excel文件|*.xlsx"; saveDialog.FileName = "导出文件"; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) return; //被点了取消 */ Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel"); return; } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth = 40; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight = 50; //worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText = true; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); //worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); for (int i = 2; i < 12; i++) { if (dt.Rows[i-2][3].ToString() == "E") { worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3; } if (dt.Rows[i - 2][3].ToString() == "W") { worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6; } } //写入字段 worksheet.Cells[1, 1] = "参数名称"; worksheet.Cells[1, 2] = "参数值"; worksheet.Cells[1, 3] = "推荐值"; worksheet.Cells[1, 4] = "报警值"; worksheet.Cells[1, 5] = "详情"; worksheet.Cells[1, 6] = "参数类型"; worksheet.Cells[1, 7] = "重要级别"; worksheet.Cells[1, 8] = "历史变化"; //写入数值 for (int r = 0; r < dt.Rows.Count; r++) { for (int i = 0; i < dt.Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i]; } System.Windows.Forms.Application.DoEvents(); } //worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 /*string md5 = ""; for (int r = 0; r < 5; r++) { for (int i = 0; i < 8; i++) { md5 += dt.Rows[r][i].ToString(); worksheet.Cells[2, 9] = md5; } } */ //worksheet.Cells.Width = 39; //if (saveFileName != "") //{ try { workbook.Saved = true; saveFileName = savename; workbook.SaveCopyAs(saveFileName); fileSaved = true; MessageBox.Show("导出完成!"); } catch (Exception ex) { fileSaved = false; MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); } //} //else //{ // fileSaved = false; //} xlApp.Quit(); GC.Collect();//强行销毁 if (fileSaved && System.IO.File.Exists(saveFileName)) { System.Diagnostics.Process.Start(saveFileName); //打开EXCEL } }
/// <summary> /// 读取并保存Excel中的信息到数据库中 /// </summary> /// <param name="iOpt">1-101,2-261</param> /// <param name="sCzdw">导入单位名称</param> /// <returns>返回错误列表</returns> private ArrayList ReadAndSaveExcelInfo(int iOpt, string sCzdw) { ArrayList lstTmp = new ArrayList(); Sheets sheets = workbook.Worksheets; _Worksheet worksheet = (_Worksheet)sheets.get_Item(1); app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; string strTmpTable, strTmpField, strTmpValue; string[] arrayTmp; arrayTmp = new string[16]; strTmpTable = ""; strTmpField = ""; strTmpValue = ""; int num = 0; Excel.Range range = worksheet.get_Range("A2", "P65535");//A2、O65535表示到Excel从第A列的第2行到第O列的第65535-1行 A2、P65535表示到Excel从第A列的第2行到第P列的第65535-1行 System.Array values = (System.Array)range.Formula; num = values.GetLength(0); for (int i = 1; i <= num; i++) { if (values.GetValue(i, 1).ToString().Trim() == "" || values.GetValue(i, 2).ToString().Trim() == "") { break; } else { string strLRR = CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName(); string strLRD = CoreFS.SA06.CoreUserInfo.UserInfo.GetDepartment(); switch(iOpt) { case 1: arrayTmp[0] = values.GetValue(i, 1).ToString().Trim(); //记帐日期 arrayTmp[1] = values.GetValue(i, 2).ToString().Trim(); //订单号 arrayTmp[2] = values.GetValue(i, 3).ToString().Trim(); //订单行项目号 arrayTmp[3] = values.GetValue(i, 4).ToString().Trim(); //物料编码 //arrayTmp[4] = values.GetValue(i, 5).ToString().Trim(); //物料名称 arrayTmp[5] = values.GetValue(i, 5).ToString().Trim(); //批号 arrayTmp[6] = values.GetValue(i, 6).ToString().Trim(); //收货数量 arrayTmp[7] = values.GetValue(i, 7).ToString().Trim(); //计量单位 arrayTmp[8] = values.GetValue(i, 8).ToString().Trim(); //工厂 arrayTmp[9] = values.GetValue(i, 9).ToString().Trim(); //库存地点 arrayTmp[10] = values.GetValue(i, 10).ToString().Trim(); //抬头文本 strTmpTable = "DT_SAP261"; strTmpField = "FS_WEIGHTNO,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_MATERIAL,FS_STOVENO," + "FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,FS_HEADER,FS_WEIGHTTYPE,FS_DRDW,FS_AUDITOR"; strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + arrayTmp[0] + "','" + arrayTmp[1] + "','" + arrayTmp[2] + "','" + arrayTmp[3] + "','" + arrayTmp[5] + "'," + arrayTmp[6] + ",'" + arrayTmp[8] + "','" + arrayTmp[9] + "','" + arrayTmp[10] + "'," + "'261','" + sCzdw + "','" + strLRR + "'"; break; case 2: arrayTmp[0] = values.GetValue(i, 1).ToString().Trim(); //记帐日期 arrayTmp[1] = values.GetValue(i, 2).ToString().Trim(); //订单号 arrayTmp[2] = values.GetValue(i, 3).ToString().Trim(); //订单行项目号 arrayTmp[3] = values.GetValue(i, 4).ToString().Trim(); //物料编码 //arrayTmp[4] = values.GetValue(i, 5).ToString().Trim(); //物料名称 arrayTmp[5] = values.GetValue(i, 5).ToString().Trim(); //批号 arrayTmp[6] = values.GetValue(i, 6).ToString().Trim(); //收货数量 arrayTmp[7] = values.GetValue(i, 7).ToString().Trim(); //计量单位 arrayTmp[8] = values.GetValue(i, 8).ToString().Trim(); //工厂 arrayTmp[9] = values.GetValue(i, 9).ToString().Trim(); //库存地点 arrayTmp[10] = values.GetValue(i, 10).ToString().Trim(); //抬头文本 strTmpTable = "DT_SAP261"; strTmpField = "FS_WEIGHTNO,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_MATERIAL,FS_STOVENO," + "FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,FS_HEADER,FS_WEIGHTTYPE,FS_DRDW,FS_AUDITOR"; strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + arrayTmp[0] + "','" + arrayTmp[1] + "','" + arrayTmp[2] + "','" + arrayTmp[3] + "','" + arrayTmp[5] + "'," + arrayTmp[6] + ",'" + arrayTmp[8] + "','" + arrayTmp[9] + "','" + arrayTmp[10] + "'," + "'261','" + sCzdw + "','" + strLRR + "'"; break; } CoreClientParam ccp = new CoreClientParam(); ccp.ServerName = "ygjzjl.base.QueryData"; ccp.MethodName = "insertDataInfo"; ccp.ServerParams = new object[] { strTmpTable, strTmpField, strTmpValue }; try { this.ExecuteNonQuery(ccp, CoreInvokeType.Internal); } catch (Exception ex1) { lstTmp.Add(ex1.Message + "第" + i.ToString() + "行信息导入失败!"); } } } app.Quit(); app = null; System.Diagnostics.Process[] myProcesses; myProcesses = System.Diagnostics.Process.GetProcesses(); foreach (System.Diagnostics.Process myProcess in myProcesses) { if (myProcess.ProcessName == "EXCEL") myProcess.Kill(); } if (lstTmp.Count == 0) lstTmp.Add("Excel导入成功!"); return lstTmp; }
public void WriteSalesTaxReport(IList<SalesWithTax> data, string savepath) { app = StartApplication(); app.Visible = false; app.UserControl = false; app.ScreenUpdating = false; var path = Directory.GetCurrentDirectory(); var wb = app.Workbooks.Open(path + @"\Excel Templates\SalesWithTax.xlsx"); Excel.Worksheet ws = wb.Worksheets["Source"]; for(var i = 0; i < data.Count; i++) { var fields = data[i].GetType().GetFields(); for(var j = 0; j < fields.Count<FieldInfo>(); j++) { ws.Cells[i+2, j+1].Value = fields[j].GetValue(data[i]); } } ws = wb.Worksheets["Report"]; ws.Activate(); ws.PivotTables("PivotTable1").PivotCache.Refresh(); ws.PivotTables("PivotTable1").PivotFields("Location").PivotItems("(blank)").Visible = false; string fileName = savepath + "\\SalesTax_" + Guid.NewGuid().ToString() + ".xlsx"; wb.SaveAs(fileName); wb.Close(); wb = app.Workbooks.Open(fileName); app.Visible = true; app.UserControl = true; app.ScreenUpdating = true; }
/// <summary> /// 执行EXCEL导入 /// </summary> /// <param name="iOpt">1-101,2-261</param> /// <param name="sCzdw">导入单位名称</param> /// <returns>返回错误列表</returns> public ArrayList ExcelToDatabase(int iOpt, string sCzdw) { ArrayList lstTmp = new ArrayList(); OpenFileDialog openFileDialog = new OpenFileDialog(); //openFileDialog.InitialDirectory = @"E:\计量系统\安装3.0\"; //指定打开文件默认路径 openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名 openFileDialog.RestoreDirectory = true; openFileDialog.FilterIndex = 1; if (openFileDialog.ShowDialog() == DialogResult.OK) { app = new Excel.Application(); app.Visible = false; app.UserControl = true; Workbooks workbooks = app.Workbooks; object MissingValue = Type.Missing; workbook = workbooks.Add(openFileDialog.FileName); } string strTmpFile = openFileDialog.FileName.ToLower(); if (strTmpFile == "") { lstTmp.Add("请选择导入的Excel文件!"); return lstTmp; } string strTmp = chkExcute(iOpt, strTmpFile); if (strTmp != "") { lstTmp.Add(strTmp); return lstTmp; } lstTmp = ReadAndSaveExcelInfo(iOpt, sCzdw); return lstTmp; }
public void WriteVendorSalesReport(IList<VendorSales> data, string savepath) { app = StartApplication(); app.Visible = false; app.UserControl = false; app.ScreenUpdating = false; var path = Directory.GetCurrentDirectory(); var wb = app.Workbooks.Open(path + @"\Excel Templates\VendorSales.xlsx"); Excel.Worksheet ws = wb.Worksheets["Source"]; for (var i = 0; i < data.Count; i++) { var fields = data[i].GetType().GetFields(); for (var j = 0; j < fields.Count<FieldInfo>(); j++) { ws.Cells[i + 2, j + 1].Value = fields[j].GetValue(data[i]); } } string fileName = savepath + "\\" + Guid.NewGuid().ToString() + ".xlsx"; wb.SaveAs(fileName); wb.Close(); wb = app.Workbooks.Open(fileName); app.Visible = true; app.UserControl = true; app.ScreenUpdating = true; }
private void DataTableToExcel() { if (this._TbData != null) { this.dBegin = DateTime.Now; if (this.appExcel == null) { this.appExcel = new ApplicationClass(); } this.wkBook = this.appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); this.wkSheet = (Worksheet) this.wkBook.Worksheets[1]; this.appExcel.Visible = false; this.dEnd = DateTime.Now; this.nCols = this._TbData.Columns.Count; this.nRows = this._TbData.Rows.Count; this.nCurrR = 1; this.nCurrC = 1; if (this._RptTitle != "") { this.SetControlText(this.lblState, "正在处理表头......"); this.wkSheet.Cells[this.nCurrR, this.nCurrC] = this._RptTitle; this.range = this.wkSheet.get_Range(this.wkSheet.Cells[this.nCurrR, 1], this.wkSheet.Cells[this.nCurrR, this.nCols]); this.range.MergeCells = true; this.range.Font.Bold = true; this.range.Font.Size = 0x12; this.range.Font.Name = "宋体"; this.range.HorizontalAlignment = XlHAlign.xlHAlignCenter; this.range.VerticalAlignment = XlVAlign.xlVAlignCenter; this.range.EntireRow.AutoFit(); this.nRows++; this.nCurrR++; } this.nRows++; this.SetProgressMaxMin(this.prgMain, this.nRows, 0); this.SetProgressValue(this.prgMain, this.nCurrR); this.SetControlText(this.lblState, "正在处理列头......"); this.nCurrC = 1; while (this.nCurrC <= this.nCols) { this.wkSheet.Cells[this.nCurrR, this.nCurrC] = this._TbData.Columns[this.nCurrC - 1].ColumnName; this.nCurrC++; } this.nCurrR++; this.SetProgressValue(this.prgMain, this.nCurrR); foreach (DataRow row in this._TbData.Rows) { this.nCurrC = 0; while (this.nCurrC < this.nCols) { if (row[this.nCurrC] != null) { string str = row[this.nCurrC].ToString().Trim(); double a; if (str.StartsWith("=") || double.TryParse(str, out a)) { str = "'" + str; } this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1] = str; } else { this.wkSheet.Cells[this.nCurrR, this.nCurrC + 1] = ""; } this.nCurrC++; } this.SetControlText(this.lblState, "正在处理数据......(" + this.nCurrR.ToString() + "/" + this.nRows.ToString() + ")"); this.SetProgressValue(this.prgMain, this.nCurrR); this.nCurrR++; } this.range = null; this.range = this.wkSheet.get_Range(this.wkSheet.Cells[1, 1], this.wkSheet.Cells[this.nRows, this.nCols]); this.range.Cells.Borders.LineStyle = XlLineStyle.xlContinuous; this.range.Borders.Weight = XlBorderWeight.xlThin; this.range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlMedium; this.range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlMedium; this.range.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlMedium; this.range.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium; if (this._FileName.Trim() == "") { this._FileName = Path.Combine(System.Windows.Forms.Application.StartupPath, this._TbData.TableName + ".xls"); } if (this._FileName.ToLower().IndexOf(".xls") <= 0) { this._FileName = this._FileName + ".xls"; } this.wkBook.SaveAs(this._FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); this.appExcel.Workbooks.Close(); this.appExcel.Quit(); DataIE.KillProgress((IntPtr) this.appExcel.Hwnd); this.appExcel = null; MessageBox.Show("已经成功导出:" + this._FileName); base.Close(); } }
public void WritePlantInventory(string savepath, string datapath) { app = StartApplication(); app.Visible = true; app.UserControl = true; app.ScreenUpdating = true; var path = Directory.GetCurrentDirectory(); var wb = app.Workbooks.Open(path + @"\Excel Templates\PlantInventoryTemplate.xlsx"); Excel.Worksheet ws = wb.Worksheets.Add(After:wb.Worksheets[wb.Worksheets.Count]); ws.Name = "Data"; var qt = ws.QueryTables.Add(String.Format("TEXT;{0}", datapath), ws.Range["A1"]); qt.FieldNames = true; qt.RowNumbers = false; qt.FillAdjacentFormulas = false; qt.PreserveFormatting = true; qt.RefreshOnFileOpen = false; qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells; qt.SavePassword = false; qt.SaveData = true; qt.AdjustColumnWidth = true; qt.RefreshPeriod = 0; qt.TextFilePromptOnRefresh = false; qt.TextFilePlatform = Excel.XlPlatform.xlWindows; qt.TextFileStartRow = 1; qt.TextFileParseType = Excel.XlTextParsingType.xlDelimited; qt.TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierDoubleQuote; qt.TextFileConsecutiveDelimiter = false; qt.TextFileTabDelimiter = false; qt.TextFileSemicolonDelimiter = false; qt.TextFileCommaDelimiter = true; qt.TextFileSpaceDelimiter = false; qt.TextFileColumnDataTypes = new int[] { 1, 1, 2, 1, 1, 1, 1, 1 }; qt.Refresh(); for (var i = 1; i <= wb.Worksheets.Count; i++) { wb.Worksheets[i].Activate(); foreach (Excel.PivotTable p in wb.Worksheets[i].PivotTables()) { p.RefreshTable(); switch (i) { case 1: p.PivotFields("Location").AutoSort(XlSortOrder.xlAscending, "Location"); p.PivotFields("Location").PivotItems("(blank)").Visible = false; break; case 2: p.PivotFields("Location").PivotItems("(blank)").Visible = false; p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false; p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false; p.PivotFields("Location").PivotItems("Penrose").Visible = false; p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible = false; p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false; p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false; p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false; p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false; p.PivotFields("Location").PivotItems("Shelter (GRO) | 403-00246").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible = false; p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00003 [43rd]").Visible = false; p.PivotFields("Location").ShowDetail = false; break; case 3: p.PivotFields("Location").PivotItems("(blank)").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false; p.PivotFields("Location").PivotItems("Penrose").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible = false; p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false; p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00003 [43rd]").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = false; p.PivotFields("Phase").PivotItems("(blank)").Visible = false; p.PivotFields("Phase").PivotItems("Clone").Visible = false; p.PivotFields("Phase").PivotItems("Harvesting").Visible = false; p.PivotFields("Phase").PivotItems("Germination").Visible = false; p.PivotFields("Room").ShowDetail = false; break; case 4: switch (p.Name) { case "PivotTable2": p.PivotFields("Location").PivotItems("(blank)").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false; p.PivotFields("Location").PivotItems("Penrose").Visible = false; p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible = false; p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false; p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false; p.PivotFields("Location").PivotItems("Shelter (GRO) | 403-00246").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00003 [43rd]").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible = false; p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = false; break; //case "PivotTable11": // p.PivotFields("Location").PivotItems("(blank)").Visible = false; // p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible // = false; // p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible = // false; // p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false; // p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible = // false; // p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false; // p.PivotFields("Location").PivotItems("Penrose").Visible = false; // p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false; // p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible = // false; // p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false; // p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false; // p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false; // p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false; // p.PivotFields("Location").PivotItems("Shelter (GRO) | 403-00246").Visible = false; // p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false; // p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = // false; // break; //case "PivotTable12": // p.PivotFields("Location").PivotItems("(blank)").Visible = false; // p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible // = false; // p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible = // false; // p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false; // p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible = // false; // p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false; // p.PivotFields("Location").PivotItems("Penrose").Visible = false; // p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false; // p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-01487 [Race]").Visible = // false; // p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false; // p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false; // p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false; // p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false; // p.PivotFields("Location").PivotItems("Shelter (GRO) | 403-00246").Visible = false; // p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false; // p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible = // false; // break; case "PivotTable13": p.PivotFields("Location").PivotItems("(blank)").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00318 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01502 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Retreat (GRO) | 403-00244").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403R-00006 [51st]").Visible = false; p.PivotFields("Location").PivotItems("Spring (GRO) | 403R-00041").Visible = false; p.PivotFields("Location").PivotItems("Penrose").Visible = false; p.PivotFields("Location").PivotItems("Ridge (GRO) | 403-01488").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403R-00003 [43rd]").Visible = false; p.PivotFields("Location").PivotItems("Annie's (GRO) | 403R-00002").Visible = false; p.PivotFields("Location").PivotItems("Haven (GRO) | 403-00270").Visible = false; p.PivotFields("Location").PivotItems("Shelter (GRO) | 403R-00011").Visible = false; p.PivotFields("Location").PivotItems("Haven (GRO) | 403R-00005").Visible = false; p.PivotFields("Location").PivotItems("Sanctuary (GRO) | 403-00243 [43rd]").Visible = false; p.PivotFields("Location").PivotItems("Annie's (GRO) | 403-00602").Visible = false; p.PivotFields("Location").PivotItems("Grove (GRO) | 403-01314").Visible = false; break; } break; } } } wb.Worksheets[1].Activate(); string fileName = savepath + "\\" + Guid.NewGuid().ToString() + ".xlsx"; wb.SaveAs(fileName); wb.Close(); wb = app.Workbooks.Open(fileName); app.Visible = true; app.UserControl = true; app.ScreenUpdating = true; }
public void OpenExcelApp(bool visible) { _excelApplication = new Excel.Application(); _excelApplication.Visible = visible; _workBooks = (Excel.Workbooks)_excelApplication.Workbooks; _workBook = (Excel._Workbook)(_workBooks.Add(_value)); _excelSheets = (Excel.Sheets)_workBook.Worksheets; _excelSheet = (Excel._Worksheet)(_excelSheets.get_Item(1)); }
private void Unmount() { m_objExcelWorksheet = null; m_objExcelApp = null; }