//public void AddWorkSheet(string sheetName) //{ // xl.Worksheet newWorkSheet; // newWorkSheet = (xl.Worksheet)Globals.ThisWorkbook.Worksheets.Add(); //} public void CreateExcelFileFromDataTableOriginal(DataTable tbl) { xl._Worksheet oSheet; xl.Range oRng; object misvalue = System.Reflection.Missing.Value; oSheet = (xl._Worksheet)oWB.ActiveSheet; int idxpp = 1; int columnCount = 0; List<string> listColumn = new List<string>(); foreach (DataColumn column in tbl.Columns) { columnCount += 1; listColumn.Add(column.ToString()); } string columnCountString; columnCountString = GetExcelColumnName(columnCount); foreach (String val in listColumn) { oSheet.Cells[1, idxpp] = val; idxpp += 1; } oSheet.get_Range("A1", String.Format("{0}1", columnCountString)).Font.Bold = true; oSheet.get_Range("A1", String.Format("{0}1", columnCountString)).VerticalAlignment = xl.XlVAlign.xlVAlignCenter; // Create an array to multiple values at once. var totalRow = tbl.Rows.Count; int rowno = 0; string[,] saNames = new string[totalRow, columnCount]; Debug.Print("totalrow" + totalRow.ToString()); if (tbl.Rows.Count > 0) { foreach (DataRow dr in tbl.Rows) { for (int idx = 0; idx < columnCount; idx += 1) { saNames[rowno, idx] = dr[idx].ToString(); } rowno += 1; } //Debug.Print("rowcount: "+tbl.Rows.Count+ " colcount: "+tbl.Columns.Count+" columncount: "+columnCountString +" total row: "+totalRow); //Debug.Print(string.Format("{0}{1}", columnCountString, totalRow)); oSheet.get_Range("A2", string.Format("{0}{1}", columnCountString, totalRow+1)).Value2 = saNames; } oRng = oSheet.get_Range("A1", string.Format("{0}1", columnCountString)); oRng.EntireColumn.AutoFit(); oWB.SaveAs(FileName, xl.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, xl.XlSaveAsAccessMode.xlNoChange); oWB.Close(); oXL = null; }
/// <summary> /// Close the current workbook /// </summary> /// <param name="saveChanges">Should the changes be saved to the document.</param> protected void CloseWorkbook(bool saveChanges = false) { if (_workbook != null) { object refSaveChanges = saveChanges; // Close the document // Close the workbook _workbook.Close( refSaveChanges, _refMissing, _refMissing); } if (_worksheet != null) { _worksheet = null; } if (_sheets != null) { _sheets = null; } if (_workbooks != null) { _workbooks.Close(); } _workbook = null; _workbooks = null; }
/// <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); }
/// <summary> /// Inchide toate procesele ce tin de excel si cheama garbage collector-ul /// </summary> public void Terminate() { wbXls.Close(null, null, null); appXls.Workbooks.Close(); appXls.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(appXls); System.Runtime.InteropServices.Marshal.ReleaseComObject(this.activeWsXls); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbXls); this.activeWsXls = null; wbXls = null; appXls = null; GC.Collect(); // colectarea componentelor //omoara toate procesele excel System.Diagnostics.Process [] p1 = System.Diagnostics.Process.GetProcessesByName("excel"); foreach (System.Diagnostics.Process proc in p1) { if (!proc.CloseMainWindow()) { try { proc.Kill(); } catch {} } } }
private void SaveClick(object sender, RoutedEventArgs e) { string pathFile = "D:\\Desktop\\Hello"; try { //另存活頁簿 wBook.SaveAs(pathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Console.WriteLine("儲存文件於 " + Environment.NewLine + pathFile); } catch (Exception ex) { Console.WriteLine("儲存檔案出錯,檔案可能正在使用" + Environment.NewLine + ex.Message); } //關閉活頁簿 wBook.Close(false, Type.Missing, Type.Missing); //關閉Excel excelApp.Quit(); //釋放Excel資源 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); wBook = null; excelApp = null; wSheet = null; GC.Collect(); }
//获取投放周期、投放开始日期、结束日期 public string[] Excel_cyc(string path) { excel.Application ex = new excel.Application(); excel._Workbook tb = ex.Workbooks.Open(path); excel.Worksheet wkst = tb.Worksheets[1]; try { string cyc = wkst.Cells[2, 1].value; List <string> ls = new List <string>(); ls.Add(cyc.Substring(0, cyc.IndexOf(")") + 1)); //ls.Add(cycformat(cyc)); ls.Add(cyc.Substring(0, cyc.IndexOf("年") + 1)); ls.Add(cyc.Substring(cyc.IndexOf("年") + 1, cyc.IndexOf("月") - cyc.IndexOf("年"))); ls.Add(cyc.Substring(cyc.IndexOf("第"), cyc.IndexOf("周") - cyc.IndexOf("第") + 1)); ls.Add(BeginDate(cyc).ToString()); ls.Add(EndDate(cyc).ToString()); ls.Add(cycformat(cyc)); return(ls.ToArray()); } catch (Exception dsex) { MessageBox.Show(dsex.Message); string[] err = { "err" }; return(err); } finally { wkst = null; tb.Close(); tb = null; ex.Quit(); ex = null; } }
public void Dispose() { if (Mws_Sheet != null) { Marshal.ReleaseComObject(Mws_Sheet); Mws_Sheet = null; } if (Mwb_Book != null) { Mwb_Book.Close(false, null, null); Marshal.ReleaseComObject(Mwb_Book); Mwb_Book = null; } if (Mxl_Excel != null) { Mxl_Excel.Workbooks.Close(); Mxl_Excel.Quit(); Marshal.ReleaseComObject(Mxl_Excel); Mxl_Excel = null; } GC.Collect(); GC.WaitForPendingFinalizers(); }
public static void exportAsXLS(string path, string filename) { object miss = Type.Missing; XLS app = new Excel.Application(); app.DisplayAlerts = false; app.Visible = false; Workbook wb = app.Workbooks.Add(miss); Worksheet ws = (Worksheet)wb.Sheets[1]; //ws = wb.Sheets[0]; //ws = wb.Sheets["Sheet1"]; ws = wb.ActiveSheet; ws.Name = "Detail"; /* * for (int i = 0; i < 5; i++) * { * for (int j = 0; j < 3; j++) * { * ws.Cells[i, j] = "i = " + i.ToString() + "j = " + j.ToString(); * } * } */ wb.SaveAs(path + filename); wb.Close(); //wb.SaveAs(path + filename, "xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive); app.Quit(); }
private void Writetoexcel_Click(object sender, EventArgs e) { //Start Excel and get Application object. oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; //Get a new workbook. oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add("")); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; //Add table headers going cell by cell oSheet.Cells[1, 1] = "voltage value is : "; oSheet.Cells[1, 2] = textBox2.Text; oXL.Visible = false; oXL.UserControl = false; string date = Convert.ToString(DateTime.UtcNow); string[] date2 = date.Split('/', ':'); string filename = "C:\\Users\\ayoubexo.CEBONGROUP\\source\\repos\\JCD\\JCD\\excel files\\" + string.Join("-", date2) + "_test result.xlsx"; oWB.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); oWB.Close(); }
private void UpdateExcel(int row, int col, string data) { // UpdateExcel() is the method used to replace cells inside the specified // Excel spreadsheet, using the Interop.Excel library. Excel.Application oXL = null; Excel._Workbook oWB = null; Excel._Worksheet oSheet = null; try { // This try block opens up your specified document in the File Chooser, // and attempts to write to cells with the specified data given (string data). oXL = new Excel.Application(); oWB = oXL.Workbooks.Open(excelDocument); oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text]; oSheet.Cells[row, col] = data; oWB.Save(); } catch (Exception ex) { Debug.WriteLine(ex); } finally { if (oWB != null) { oWB.Close(); // Close down document, to avoid deadlock or R/W issues. } } }
/// <summary> /// Closes Excel Application and clears Excel Objects used /// </summary> public static void Close() { UsingExcel = false; if (oXL != null) { Marshal.ReleaseComObject(oChartRSSIs); Marshal.ReleaseComObject(oChartTags); Marshal.ReleaseComObject(oSheetCharts); Marshal.ReleaseComObject(oSheetDataRSSIs); Marshal.ReleaseComObject(oSheetDataTags); Marshal.ReleaseComObject(oRng); if (oWB != null) { oWB.Close(false, null, null); // closes workbook } Marshal.ReleaseComObject(oWB); oWB = null; oXL.Quit(); // exit excel application Marshal.ReleaseComObject(oXL); oXL = null; } GC.Collect(); GC.WaitForPendingFinalizers(); }
public string DataString(int row, int col) { // This method extracts data from cells using the .ToString() function. // However, as this simply hands the instructions off, Excel is extremely // slow reading and sending data and causes a bottleneck. Looking for a better // solution. Excel.Application oXL = new Excel.Application(); Excel._Workbook oWB = null; string data = ""; try { // Why in 0.0.12, I forgot to encase this in a try/catch, is beyond me. oWB = oXL.Workbooks.Open(excelDocument); Excel._Worksheet oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text]; data = oSheet.Cells[row, col].Value.ToString(); } catch (Exception ex) { Debug.WriteLine(ex); } finally { if (oWB != null) { oWB.Close(); } } return(data); // Return the data inside the cell. }
public int RowMinMax() { // New method written so the end user doesn't go over the amount of cells already used. // However, this may be scratched as it could be that the end user wishes to fill new boxes. int rowCounter = 0; Excel.Application oXL = null; Excel._Workbook oWB = null; Excel._Worksheet oSheet = null; try { // Again, unlike 0.0.12, this should always be in a try/catch. oXL = new Excel.Application(); oWB = oXL.Workbooks.Open(excelDocument); oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text]; Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); // Finds the last used cell. Excel.Range range = oSheet.get_Range("A1", last); rowCounter = last.Row; } catch (Exception ex) { Debug.Write(ex); MessageBox.Show("Please open a valid .xlsx document!"); } finally { if (oWB != null) { oWB.Close(); // Close down document, to avoid deadlock or R/W issues. } } return(rowCounter); // Return this max value. }
public Sprawozdanie ReadRaport(string path) { string[,] dane = new string[30, 300]; Sprawozdanie raportSpolkiNotoria = new Sprawozdanie(); oWB = (Excel._Workbook)(oXL.Workbooks.Open(path)); //oSheet = (Excel._Worksheet)oWB.ActiveSheet; oSheet = (Excel._Worksheet)oWB.Worksheets[6]; oRng = oSheet.UsedRange; for (int row = 1; row < 231; row++) { Pozycja node = new Pozycja(); node.name = (string)(oRng.Cells[row, 2] as Excel.Range).Value2; node.pos = row; for (int col = 17; col < 22; col++) // w 17 kolumnie mamy 2010 rok { try { node.value[col - 17] = (int)(oRng.Cells[row, col] as Excel.Range).Value2; } catch { } } raportSpolkiNotoria.dane[row] = node; } oWB.Close(); return(raportSpolkiNotoria); }
void printLable() { string path = Directory.GetCurrentDirectory(); Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook wb = null; wb = oXL.Workbooks.Open(path + @"/../../xls/cleancut/7 标签-清洁分切.xlsx"); _Worksheet my = wb.Worksheets[wb.Worksheets.Count]; my.Select(); my.Cells[2, 2].Value = cb膜代码.Text; my.Cells[3, 2].Value = tb批号.Text; my.Cells[4, 2].Value = tb米.Text + "米; " + tbKg.Text + "Kg"; my.Cells[5, 2].Value = cb原膜代码.Text; my.Cells[6, 2].Value = String.Format("{0} {1}", dtp分切日期.Value.ToString("yyyy/MM/dd"), cb白班.Checked ? "白班☑ 夜班□" : "白班□ 夜班☑"); my = wb.Worksheets[1]; my.Select(); oXL.Visible = false; my.PrintOut(); // 关闭文件,false表示不保存 wb.Close(false); // 关闭Excel进程 oXL.Quit(); // 释放COM资源 Marshal.ReleaseComObject(wb); Marshal.ReleaseComObject(oXL); wb = null; oXL = null; }
static void Main(string[] args) { string WorkDir = @"D:\气象\"; string filename = WorkDir + "河北.xlsx"; // string[] RawDataStr_A = File.ReadAllLines(i+ ".xlsx" + ".csv", Encoding.UTF8); // RawDataStr_A = File.ReadAllLines(i + ".xlsx" + ".csv", Encoding.Default); Microsoft.Office.Interop.Excel.Application appExcel = new Application(); Microsoft.Office.Interop.Excel._Workbook ExcelBooks = null; ExcelBooks = Shashade(appExcel); if (File.Exists((string)filename)) //判断文件已经是否存在 { File.Delete((string)filename); //若已存在,则删除 } ExcelBooks.SaveAs(filename); //stopWatch.Stop(); //TimeSpan ts3 = stopWatch.Elapsed; //string elapsedTime3 = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts3.Hours, ts3.Minutes, ts3.Seconds, ts3.Milliseconds / 10); //Console.WriteLine("RunTime " + elapsedTime3); ExcelBooks.Close(); //appExcel.Quit(); ExcelBooks = null; appExcel = null; }
/// <summary> /// 释放内存 /// </summary> public void Dispose(Microsoft.Office.Interop.Excel._Worksheet CurSheet, Microsoft.Office.Interop.Excel._Workbook CurBook, Microsoft.Office.Interop.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) { // HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex); } finally { foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel")) { //if (pro.StartTime < DateTime.Now) pro.Kill(); } } System.GC.SuppressFinalize(this); }
private void button1_Click(object sender, EventArgs e) { object oMissing = System.Reflection.Missing.Value; //Create an instance of Microsoft Excel, make it visible and open invoice.xls Excel.Application oExcel = new Excel.Application(); oExcel.Visible = true; Excel.Workbooks oBooks = oExcel.Workbooks; Excel._Workbook oBook = null; int index = comboBox1.SelectedIndex; oBook = oBooks.Open(files[index], oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //Run the macro sub CSV() in the PERSONAL.XLSB file oExcel.Run("'c:\\csv\\PERSONAL.XLSB'!CSV"); //Quit Excel and clean up oBook.Close(false, oMissing, oMissing); System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); oBook = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks); oBooks = null; oExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); oExcel = null; GC.Collect(); }
/// <summary> /// 将DataSet写入excel中,dataset.table的表名作为sheet名,dataset.table的列标题作为每一个sheet的首行 /// </summary> /// <param name="fileExcel"></param> /// <param name="dsInfo"></param> public static void WriteExcel(string fileExcel, DataSet dsInfo) { DataTable dt = new DataTable(); excel = new Excel.Application(); excel.Visible = false; //不显示excel前台界面 Excel._Workbook book = null; //定义工作表 Excel._Worksheet sheet = null; //定义worksheet //Excel.Range range = null;//定义range //添加内容 try { book = excel.Workbooks.Open(fileExcel, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value); } catch (Exception ex) { Logs.WriteLog("error", ex.Message); throw; } finally { book.Close(); //excel.Quit();//好像无法结束进程 IntPtr t = new IntPtr(excel.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); Process p = Process.GetProcessById(k); p.Kill(); } }
//adds project to SEQR workbook private static void AddToWorkbook(string parkName, string projectName, bool newProject) { string region = albPath.Substring(0, albPath.Length - 1); region = region.Substring(region.LastIndexOf("\\") + 1); string smRegion = region.Replace(" ", ""); string workbook = albPath + smRegion + "SEQRWorkbook.xlsx"; //workbook = "D:\\CentralSEQRWorkbook.xlsx"; Excel.Application excelApp = new Excel.Application(); excelApp.Visible = false; Excel._Workbook wBk = excelApp.Workbooks.Open(workbook, 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); Excel._Worksheet wSht = (Excel.Worksheet)wBk.Worksheets[1]; int rowNum = 0; string searchStr = newProject ? "" : projectName; int searchCol = newProject ? 1 : 3; foreach (Excel.Range row in wSht.Rows) { Excel.Range rng = (Excel.Range)wSht.Cells[row.Row, searchCol]; if (rng.Text.ToString().Equals(searchStr)) { rowNum = row.Row; break; } } if (newProject) { PrincipalContext ctx = new PrincipalContext(ContextType.Domain); UserPrincipal user = UserPrincipal.Current; string fullName = user.GivenName + " " + user.Surname; wSht.Cells[rowNum, 1] = region; wSht.Cells[rowNum, 2] = parkName; wSht.Cells[rowNum, 3] = projectName; wSht.Cells[rowNum, 5] = DateTime.Today.Year; wSht.Cells[rowNum, 7] = fullName; } else { wSht.Hyperlinks.Add(wSht.Cells[rowNum, 4], albPath + parkName + "\\" + projectName, Type.Missing, Type.Missing, parkName + "\\" + projectName); wSht.Cells[rowNum, 13] = DateTime.Today; } wBk.Save(); wBk.Close(); excelApp.Quit(); excelApp = null; GC.Collect(); }
//public bool dB_Delete(string p_dB, string p_Col, string p_Cond) //{ // bool ret_val = false; // string sql = "DELETE FROM [Sheet1$] WHERE [" + p_Col + "] LIKE '" + p_Cond + "%';"; // ret_val = dB_Update(p_dB, sql); // return ret_val; //} public bool dB_Delete(string p_dB, string p_Col, string p_Cond) { bool ret_val = false; int col_idx = 0, row_idx = 0; string filename = DataDir + p_dB + dbFileExt; XL.Application xlApp = new XL.Application(); XL._Workbook xlWorkbook = xlApp.Workbooks.Open(filename); XL._Worksheet xlWorksheet = xlWorkbook.Worksheets["Sheet1"]; XL.Range xlRange = xlWorksheet.UsedRange; int row_cnt = xlRange.Rows.Count; int col_cnt = xlRange.Columns.Count; // First find the column number for (int i = 1; i < col_cnt; i++) { string val = xlRange.Cells[1, i].Value2.ToString(); if (val == p_Col) { col_idx = i; break; } } if (col_idx > 0) { // Next find the row number to delete for (int i = 2; i <= row_cnt; i++) { string val = xlRange.Cells[i, col_idx].Value2.ToString(); if (val == p_Cond) { row_idx = i; break; } } if (row_idx > 0) { (xlWorksheet.Rows[row_idx, System.Reflection.Missing.Value] as XL.Range).Delete(XL.XlDeleteShiftDirection.xlShiftUp); ret_val = true; } } GC.Collect(); GC.WaitForPendingFinalizers(); xlApp.DisplayAlerts = false; xlWorkbook.Save(); xlWorkbook.Close(); xlApp.Quit(); // release COM objects Marshal.ReleaseComObject(xlWorkbook); Marshal.ReleaseComObject(xlWorksheet); Marshal.ReleaseComObject(xlApp); return(ret_val); }
/// <summary> /// save the excel sheet to the location with file name /// </summary> /// <param name="fileName"></param> protected virtual void SaveExcel(string fileName) { _workBook.SaveAs(fileName, _value, _value, _value, _value, _value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, _value, _value, _value, _value, null); _workBook.Close(false, _value, _value); _excelApplication.Quit(); }
// Event Handler for the middle browse button. private void mBook2Load_Click(object sender, EventArgs e) { // Create a new OpenFileDialog object that will be used to select the second // workbook. System.Windows.Forms.OpenFileDialog fWin = new OpenFileDialog(); // Set the extensions and default folder path to be used by the dialog. fWin.DefaultExt = "xlsx"; fWin.Filter = "Spreadsheet Files (*.xlsx;*.xls;*.csv)|*.xlsx;*.xls;*.csv"; fWin.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); // Open the dialog and do addional processing if a valid result is returned. if (fWin.ShowDialog() == DialogResult.OK) { // If a workbook is currently open close it. mBook2Sheets = null; if (mBook2 != null) { mBook2.Close(false); mBook2 = null; } if (fWin.FileName.Equals(mBook1File.Text) && fWin.FileName.EndsWith(".csv")) { MessageBox.Show("Cannot compare a csv file to itself."); return; } // Open the workbook selected in the dialog. mBook2File.Text = fWin.FileName; mBook2 = (Excel._Workbook)(mExcel.Workbooks.Add(fWin.FileName)); mBook2Sheets = (Excel.Sheets)(mBook2.Sheets); // Enable and clear the combobox for the second workbook's worksheets. mBook2SheetBox.Enabled = true; mBook2SheetBox.Items.Clear(); // Populate the combobox for the second workbook. int numSheets = mBook2Sheets.Count; for (int i = 1; i <= numSheets; i++) { mBook2SheetBox.Items.Add(((Excel._Worksheet)(mBook2Sheets.Item[i])).Name); } } }
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); } }
public void CloseExcel() { if (oXL == null) { return; } oWB.Close(); oXL.Quit(); }
public void saveWorkbook(String fileName) { //save workbook oWB.SaveAs(fileName); //close workbook oWB.Close(); oXL.Quit(); }
/// <summary> /// Metod koj vrsi zacuvuvanje na dokumentot pod opredeleno ime - /// momentalno vo direktoriumot C:\TEMP\NBOFILES /// </summary> /// <param name="fileName"></param> public void SaveExcel(string fileName) { object fileNameToSave = FILE_PATH + "\\" + fileName; _workBook.SaveAs(fileNameToSave, _value, _value, _value, _value, _value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, _value, _value, _value, _value, null); _workBook.Close(false, _value, _value); _excelApplication.Quit(); }
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(); }
public static string externAsXLS(string path) { string dateNow = DateTime.Now.ToShortDateString(); string filename = dateNow + "_" + "extern_list"; object miss = Type.Missing; List <List <String> > externs = new List <List <string> >(); try { List <object[]> list = Database.Database.database.RequestTable("f_get_extern_user", 6); foreach (var o in list) { externs.Add(new List <string> { (string)o[0], (string)o[1], (string)o[2], (string)o[3], (string)o[4], (string)o[5] }); } } catch (Exception) { } XLS app = new Excel.Application(); app.DisplayAlerts = false; app.Visible = false; Workbook wb = app.Workbooks.Add(miss); Worksheet ws = (Worksheet)wb.Sheets[1]; //ws = wb.Sheets[0]; //ws = wb.Sheets["Sheet1"]; ws = wb.ActiveSheet; ws.Name = "Liste des externes"; ws.Cells[1, 1] = "Login"; ws.Cells[1, 2] = "Adresse mél"; ws.Cells[1, 3] = "Prénom"; ws.Cells[1, 4] = "Nom"; ws.Cells[1, 5] = "Adresse"; ws.Cells[1, 6] = "Numéro de téléphone"; for (int y = 2; y <= externs.Count + 1; y++) { for (int x = 1; x <= externs[y - 2].Count; x++) { ws.Cells[y, x] = externs[y - 2][x - 1]; } } ws.Columns.AutoFit(); wb.SaveAs(path + filename); wb.Close(); //wb.SaveAs(path + filename, "xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive); app.Quit(); return(filename + ".xlsx"); }
void printLable(bool flag) { string path = Directory.GetCurrentDirectory(); Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook wb = null; if (flag) { wb = oXL.Workbooks.Open(path + @"/../../xls/LDPEBag/LDPE 制袋内包标签.xlsx"); } else { wb = oXL.Workbooks.Open(path + @"/../../xls/LDPEBag/LDPE 制袋外包标签.xlsx"); } _Worksheet my = wb.Worksheets[wb.Worksheets.Count]; my.Select(); my.Cells[1, 2].Value = tc产品名称.Text; my.Cells[2, 2].Value = tc产品编码.Text; my.Cells[3, 2].Value = tc产品规格.Text; my.Cells[4, 2].Value = tc产品批号.Text; my.Cells[5, 2].Value = dc生产日期.Value.ToString("yyyy/MM/dd"); my.Cells[6, 2].Value = dc有效期至.Value.ToString("yyyy/MM"); my.Cells[7, 2].Value = tc数量.Text; my.Cells[8, 2].Value = tc包装序号.Text; my.Cells[9, 2].Value = tc毛重.Text; my.Cells[10, 2].Value = tc箱体规格.Text; my.Cells[11, 2].Value = tc注册证号.Text; my.Cells[1, 5].Value = teName.Text; my.Cells[2, 5].Value = teCode.Text; my.Cells[3, 5].Value = teSize.Text; my.Cells[4, 5].Value = teBatch.Text; my.Cells[5, 5].Value = deMfg.Value.ToString("dd/MM/yyyy"); my.Cells[6, 5].Value = deExpiry.Value.ToString("MM/yyyy"); my.Cells[7, 5].Value = teQuantity.Text; my.Cells[8, 5].Value = tePack.Text; my.Cells[9, 5].Value = tegross.Text; my.Cells[10, 5].Value = teCarton.Text; my = wb.Worksheets[c标签模板.SelectedIndex + 1]; my.Select(); oXL.Visible = false; my.PrintOut(); // 关闭文件,false表示不保存 wb.Close(false); // 关闭Excel进程 oXL.Quit(); // 释放COM资源 Marshal.ReleaseComObject(wb); Marshal.ReleaseComObject(oXL); wb = null; oXL = null; }
public List<AccountExcel> readExcel(string sExcelPath) { var lReturn = new List<AccountExcel>(); string valueString = string.Empty; objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = (Excel.Workbooks)objExcelApp.Workbooks; //Open the workbook containing the address data. objBook = objBooks.Open(sExcelPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell); long lLastRow = rngLast.Row; long lLastCol = rngLast.Column; for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2 { if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "") { var adAccount = new AccountExcel(); adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value); adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value); adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value); adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value); adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value); adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value); adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value); adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value); adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value); adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value); adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value); adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value); adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value); lReturn.Add(adAccount); } } //Close the Excel Object objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); objBooks.Close(); objExcelApp.Quit(); Marshal.ReleaseComObject(objSheet); Marshal.ReleaseComObject(objSheets); Marshal.ReleaseComObject(objBooks); Marshal.ReleaseComObject(objBook); Marshal.ReleaseComObject(objExcelApp); objSheet = null; objSheets = null; objBooks = null; objBook = null; objExcelApp = null; GC.GetTotalMemory(false); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); return (lReturn); }