internal override void Dispose(bool disposing) { if (disposing) { // Free managed if (_application != null) { _application.Dispose(); _application = null; } } base.Dispose(true); }
internal override void Dispose(bool disposing) { if (_comment != null) { _comment.Dispose(); _comment = null; } if (_font != null) { _font.Dispose(); _font = null; } if (_borders != null) { _borders.Dispose(); _borders = null; } if (_columns != null) { _columns.Dispose(); _columns = null; } if (_rows != null) { _rows.Dispose(); _rows = null; } if (_entireColumn != null) { _entireColumn.Dispose(); _entireColumn = null; } if (_entireRow != null) { _entireRow.Dispose(); _entireRow = null; } if (_worksheet != null) { _worksheet.Dispose(); _worksheet = null; } if (_application != null) { _application.Dispose(); _application = null; } base.Dispose(disposing); }
private void btnAutoFilter_Click(object sender, EventArgs e) { int colCount = 32; int dataCount = 1648; int lastRow = 1651; using (var excel = new Excel.Application()) { excel.Visible = true; using (var workbook = excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\AutoFilter.xls")) { using (var worksheet = workbook.ActiveSheet) { worksheet.AutoFilterMode = false; int start = 0; using (var r = worksheet.Range("__Table__")) start = r.Row; using (var r = worksheet.Range(STC.Automation.Office.Excel.Utilities.Ranges.Format(start - 1, 1))) r.AutoFilter(); for (int c = 1; c <= colCount; c++) { using (var r = worksheet.Range(STC.Automation.Office.Excel.Utilities.Ranges.Format(start, c, start + dataCount, c))) { // set formats before calculating column autofit if (c >= 13 && c <= 20) r.NumberFormat = Ranges.ConvertFormat("C0"); if (c >= 21 && c <= 24) r.NumberFormat = Ranges.ConvertFormat("dd/MM/yyyy"); r.Columns.AutoFit(); if (r.ColumnWidth > 80) r.ColumnWidth = 80; } } //// sort the AutoFilter by data in column B //using (var filter = worksheet.AutoFilter) //{ // filter.Sort.SortFields.Clear(); // using (var range = worksheet.Range("B4:B" + lastRow)) // filter.Sort.SortFields.Add(range, Excel.Enums.SortOn.Values, Excel.Enums.SortOrder.Ascending, Excel.Enums.SortDataOption.Normal); // filter.Sort.Header = Excel.Enums.YesNoGuess.Yes; // filter.Sort.MatchCase = false; // filter.Sort.Orientation = Excel.Enums.SortOrientation.Columns; // filter.Sort.Apply(); //} } excel.DisplayAlerts = false; workbook.Save(); excel.DisplayAlerts = true; //workbook.Close(); } //excel.Quit(); } }
private void btnNewExcel_Click(object sender, EventArgs e) { ADODB.Recordset rs = new ADODB.Recordset(); rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Open(); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" }); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" }); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" }); // Excel using (var excel = new Excel.Application()) { excel.NewWorkbook += new STC.Automation.Office.Excel.Events.NewWorkbookEventHandler(excel_NewWorkbook); excel.Visible = true; MessageBox.Show("Version: " + excel.Version.ToString()); using (var workbook = (sender == btnNewExcel) ? excel.Workbooks.Add() : excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\Open Tester.xls")) { using (var worksheet = workbook.ActiveSheet) { using (var range = worksheet.Cells) { (range[1, 1]).Value = "Test"; } using (var range = worksheet.Range("A2")) { range.CopyFromRecordset(rs.InternalObject, null, null); } using (var range = worksheet.Range("A2:B3")) { range.Font.Bold = true; range.Font.Color = Color.Teal; range.Font.Italic = true; } using (var range = worksheet.Range("D2:G10")) { string imgPath = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"Resources\Koala.jpg"); Image img = null; try { img = Image.FromFile(imgPath); } catch { } //worksheet.Shapes.AddPicture(img, range, true, true).Dispose(); if (img != null) { using (var shape = worksheet.Shapes.AddPicture(imgPath, false, true, range, true)) { MessageBox.Show(shape.Name); worksheet.Hyperlinks.Add(shape, "http://www.google.com/").Dispose(); } } } using (var range = worksheet.Range("B2")) { range.AddComment("A comment on cell B2"); worksheet.Hyperlinks.Add(range, "http://lmgtfy.com/?q=excel+automation", screenTip: "Let Me Google That For You", textToDisplay: "LMGTFY").Dispose(); } using (var range = worksheet.Range("B2")) { if (range.Comment != null) range.Comment.Text(" - New text", 20, false); } using (var range = worksheet.Range("B3")) { if (range.Comment == null) range.AddComment("Another text"); if (range.Comment == null) range.AddComment("This should never be seen"); } using (var range = worksheet.Range("C2")) { using (var interior = range.Interior) { interior.Color = Color.IndianRed; range.AddComment(interior.Color.ToString()); } } } using (var testWorksheet = workbook.Worksheets.Add() as STC.Automation.Office.Excel.Worksheet) { testWorksheet.Name = "Programmatic Worksheet"; using (var range = testWorksheet.Cells) { range[1, 1].Value = "Worksheet #2"; } using (var chart = workbook.Sheets.Add(testWorksheet, type: Excel.Enums.SheetType.Chart) as STC.Automation.Office.Excel.Chart) { chart.Name = "Programmatic Chart"; } } //workbook.Close(); } //excel.Quit(); } //Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); //excel.Visible = true; //var workbook = excel.Workbooks.Add(DBNull.Value); //Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; //Microsoft.Office.Interop.Excel.Range range = sheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range; //range.set_Item(0, 1, "Test"); //range.CopyFromRecordset(rs.InternalObject, System.Reflection.Missing.Value, 10); }
private void ExcelToPdfButton_Click(object sender, EventArgs e) { // Copy and paste job from btnNewExcel_Click ADODB.Recordset rs = new ADODB.Recordset(); rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Open(); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" }); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" }); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" }); // Excel using (var excel = new Excel.Application()) { excel.NewWorkbook += new STC.Automation.Office.Excel.Events.NewWorkbookEventHandler(excel_NewWorkbook); excel.Visible = true; excel.ScreenUpdating = false; excel.DisplayAlerts = false; MessageBox.Show("Version: " + excel.Version.ToString()); using (var workbook = (sender == btnNewExcel) ? excel.Workbooks.Add() : excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\Open Tester.xls")) { using (var worksheet = workbook.ActiveSheet) { using (var range = worksheet.Cells) { (range[1, 1]).Value = "Test"; } using (var range = worksheet.Range("A2")) { range.CopyFromRecordset(rs.InternalObject, null, null); } using (var range = worksheet.Range("A2:B3")) { range.Font.Bold = true; range.Font.Color = Color.Teal; range.Font.Italic = true; } using (var range = worksheet.Range("D2:G10")) { string imgPath = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"Resources\Koala.jpg"); Image img = null; try { img = Image.FromFile(imgPath); } catch { } //worksheet.Shapes.AddPicture(img, range, true, true).Dispose(); if (img != null) { using (var shape = worksheet.Shapes.AddPicture(imgPath, false, true, range, true)) { MessageBox.Show(shape.Name); worksheet.Hyperlinks.Add(shape, "http://www.google.com/").Dispose(); } } } using (var range = worksheet.Range("B2")) { range.AddComment("A comment on cell B2"); worksheet.Hyperlinks.Add(range, "http://lmgtfy.com/?q=excel+automation", screenTip: "Let Me Google That For You", textToDisplay: "LMGTFY").Dispose(); } using (var range = worksheet.Range("B2")) { if (range.Comment != null) range.Comment.Text(" - New text", 20, false); } using (var range = worksheet.Range("B3")) { if (range.Comment == null) range.AddComment("Another text"); if (range.Comment == null) range.AddComment("This should never be seen"); } using (var range = worksheet.Range("C2")) { using (var interior = range.Interior) { interior.Color = Color.IndianRed; range.AddComment(interior.Color.ToString()); } } } using (var testWorksheet = workbook.Worksheets.Add() as STC.Automation.Office.Excel.Worksheet) { testWorksheet.Name = "Programmatic Worksheet"; using (var range = testWorksheet.Cells) { range[1, 1].Value = "Worksheet #2"; } using (var chart = workbook.Sheets.Add(testWorksheet, type: Excel.Enums.SheetType.Chart) as STC.Automation.Office.Excel.Chart) { chart.Name = "Programmatic Chart"; } } // Code stolen from http://stackoverflow.com/a/7401831/23401 var exportSuccessful = true; var outputPath = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\test.pdf"; try { // Call Excel's native export function (valid in Office 2007 and Office 2010, AFAIK) workbook.ExportAsFixedFormat(Excel.Enums.FixedFormatType.TypePDF, outputPath); } catch (System.Exception ex) { // Mark the export as failed for the return value... exportSuccessful = false; // Do something with any exceptions here, if you wish... // MessageBox.Show... } // You can use the following method to automatically open the PDF after export if you wish // Make sure that the file actually exists first... if (System.IO.File.Exists(outputPath)) { System.Diagnostics.Process.Start(outputPath); } if (!exportSuccessful ) { MessageBox.Show("Uh oh. Something went wrong", "Excel to pdf"); } //workbook.Close(); } //excel.Quit(); } //Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); //excel.Visible = true; //var workbook = excel.Workbooks.Add(DBNull.Value); //Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; //Microsoft.Office.Interop.Excel.Range range = sheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range; //range.set_Item(0, 1, "Test"); //range.CopyFromRecordset(rs.InternalObject, System.Reflection.Missing.Value, 10); }
private void btnAutoFilter_Click(object sender, EventArgs e) { int colCount = 32; int dataCount = 1648; int lastRow = 1651; using (var excel = new Excel.Application()) { excel.Visible = true; using (var workbook = excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\AutoFilter.xls")) { using (var worksheet = workbook.ActiveSheet) { worksheet.AutoFilterMode = false; int start = 0; using (var r = worksheet.Range("__Table__")) start = r.Row; using (var r = worksheet.Range(STC.Automation.Office.Excel.Utilities.Ranges.Format(start - 1, 1))) r.AutoFilter(); for (int c = 1; c <= colCount; c++) { using (var r = worksheet.Range(STC.Automation.Office.Excel.Utilities.Ranges.Format(start, c, start + dataCount, c))) { // set formats before calculating column autofit if (c >= 13 && c <= 20) { r.NumberFormat = Ranges.ConvertFormat("C0"); } if (c >= 21 && c <= 24) { r.NumberFormat = Ranges.ConvertFormat("dd/MM/yyyy"); } r.Columns.AutoFit(); if (r.ColumnWidth > 80) { r.ColumnWidth = 80; } } } //// sort the AutoFilter by data in column B //using (var filter = worksheet.AutoFilter) //{ // filter.Sort.SortFields.Clear(); // using (var range = worksheet.Range("B4:B" + lastRow)) // filter.Sort.SortFields.Add(range, Excel.Enums.SortOn.Values, Excel.Enums.SortOrder.Ascending, Excel.Enums.SortDataOption.Normal); // filter.Sort.Header = Excel.Enums.YesNoGuess.Yes; // filter.Sort.MatchCase = false; // filter.Sort.Orientation = Excel.Enums.SortOrientation.Columns; // filter.Sort.Apply(); //} } excel.DisplayAlerts = false; workbook.Save(); excel.DisplayAlerts = true; //workbook.Close(); } //excel.Quit(); } }
private void btnNewExcel_Click(object sender, EventArgs e) { ADODB.Recordset rs = new ADODB.Recordset(); rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Open(); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" }); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" }); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" }); // Excel using (var excel = new Excel.Application()) { excel.NewWorkbook += new STC.Automation.Office.Excel.Events.NewWorkbookEventHandler(excel_NewWorkbook); excel.Visible = true; MessageBox.Show("Version: " + excel.Version.ToString()); using (var workbook = (sender == btnNewExcel) ? excel.Workbooks.Add() : excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\Open Tester.xls")) { using (var worksheet = workbook.ActiveSheet) { using (var range = worksheet.Cells) { (range[1, 1]).Value = "Test"; } using (var range = worksheet.Range("A2")) { range.CopyFromRecordset(rs.InternalObject, null, null); } using (var range = worksheet.Range("A2:B3")) { range.Font.Bold = true; range.Font.Color = Color.Teal; range.Font.Italic = true; } using (var range = worksheet.Range("D2:G10")) { string imgPath = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"Resources\Koala.jpg"); Image img = null; try { img = Image.FromFile(imgPath); } catch { } //worksheet.Shapes.AddPicture(img, range, true, true).Dispose(); if (img != null) { using (var shape = worksheet.Shapes.AddPicture(imgPath, false, true, range, true)) { MessageBox.Show(shape.Name); worksheet.Hyperlinks.Add(shape, "http://www.google.com/").Dispose(); } } } using (var range = worksheet.Range("B2")) { range.AddComment("A comment on cell B2"); worksheet.Hyperlinks.Add(range, "http://lmgtfy.com/?q=excel+automation", screenTip: "Let Me Google That For You", textToDisplay: "LMGTFY").Dispose(); } using (var range = worksheet.Range("B2")) { if (range.Comment != null) { range.Comment.Text(" - New text", 20, false); } } using (var range = worksheet.Range("B3")) { if (range.Comment == null) { range.AddComment("Another text"); } if (range.Comment == null) { range.AddComment("This should never be seen"); } } using (var range = worksheet.Range("C2")) { using (var interior = range.Interior) { interior.Color = Color.IndianRed; range.AddComment(interior.Color.ToString()); } } } using (var testWorksheet = workbook.Worksheets.Add() as STC.Automation.Office.Excel.Worksheet) { testWorksheet.Name = "Programmatic Worksheet"; using (var range = testWorksheet.Cells) { range[1, 1].Value = "Worksheet #2"; } using (var chart = workbook.Sheets.Add(testWorksheet, type: Excel.Enums.SheetType.Chart) as STC.Automation.Office.Excel.Chart) { chart.Name = "Programmatic Chart"; } } //workbook.Close(); } //excel.Quit(); } //Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); //excel.Visible = true; //var workbook = excel.Workbooks.Add(DBNull.Value); //Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; //Microsoft.Office.Interop.Excel.Range range = sheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range; //range.set_Item(0, 1, "Test"); //range.CopyFromRecordset(rs.InternalObject, System.Reflection.Missing.Value, 10); }
private void ExcelToPdfButton_Click(object sender, EventArgs e) { // Copy and paste job from btnNewExcel_Click ADODB.Recordset rs = new ADODB.Recordset(); rs.Fields.Append("A", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Fields.Append("B", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Fields.Append("C", STC.Automation.Office.ADODB.Enums.DataType.VarWChar, 100); rs.Open(); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A1", "B2", "C3" }); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A4", "B5", "C6" }); rs.AddNew(new string[] { "A", "B", "C" }, new object[] { "A7", "B8", "C9" }); // Excel using (var excel = new Excel.Application()) { excel.NewWorkbook += new STC.Automation.Office.Excel.Events.NewWorkbookEventHandler(excel_NewWorkbook); excel.Visible = true; excel.ScreenUpdating = false; excel.DisplayAlerts = false; MessageBox.Show("Version: " + excel.Version.ToString()); using (var workbook = (sender == btnNewExcel) ? excel.Workbooks.Add() : excel.Workbooks.Open(Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetModules()[0].FullyQualifiedName) + "\\Templates\\Open Tester.xls")) { using (var worksheet = workbook.ActiveSheet) { using (var range = worksheet.Cells) { (range[1, 1]).Value = "Test"; } using (var range = worksheet.Range("A2")) { range.CopyFromRecordset(rs.InternalObject, null, null); } using (var range = worksheet.Range("A2:B3")) { range.Font.Bold = true; range.Font.Color = Color.Teal; range.Font.Italic = true; } using (var range = worksheet.Range("D2:G10")) { string imgPath = Path.Combine(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location), @"Resources\Koala.jpg"); Image img = null; try { img = Image.FromFile(imgPath); } catch { } //worksheet.Shapes.AddPicture(img, range, true, true).Dispose(); if (img != null) { using (var shape = worksheet.Shapes.AddPicture(imgPath, false, true, range, true)) { MessageBox.Show(shape.Name); worksheet.Hyperlinks.Add(shape, "http://www.google.com/").Dispose(); } } } using (var range = worksheet.Range("B2")) { range.AddComment("A comment on cell B2"); worksheet.Hyperlinks.Add(range, "http://lmgtfy.com/?q=excel+automation", screenTip: "Let Me Google That For You", textToDisplay: "LMGTFY").Dispose(); } using (var range = worksheet.Range("B2")) { if (range.Comment != null) { range.Comment.Text(" - New text", 20, false); } } using (var range = worksheet.Range("B3")) { if (range.Comment == null) { range.AddComment("Another text"); } if (range.Comment == null) { range.AddComment("This should never be seen"); } } using (var range = worksheet.Range("C2")) { using (var interior = range.Interior) { interior.Color = Color.IndianRed; range.AddComment(interior.Color.ToString()); } } } using (var testWorksheet = workbook.Worksheets.Add() as STC.Automation.Office.Excel.Worksheet) { testWorksheet.Name = "Programmatic Worksheet"; using (var range = testWorksheet.Cells) { range[1, 1].Value = "Worksheet #2"; } using (var chart = workbook.Sheets.Add(testWorksheet, type: Excel.Enums.SheetType.Chart) as STC.Automation.Office.Excel.Chart) { chart.Name = "Programmatic Chart"; } } // Code stolen from http://stackoverflow.com/a/7401831/23401 var exportSuccessful = true; var outputPath = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\test.pdf"; try { // Call Excel's native export function (valid in Office 2007 and Office 2010, AFAIK) workbook.ExportAsFixedFormat(Excel.Enums.FixedFormatType.TypePDF, outputPath); } catch (System.Exception ex) { // Mark the export as failed for the return value... exportSuccessful = false; // Do something with any exceptions here, if you wish... // MessageBox.Show... } // You can use the following method to automatically open the PDF after export if you wish // Make sure that the file actually exists first... if (System.IO.File.Exists(outputPath)) { System.Diagnostics.Process.Start(outputPath); } if (!exportSuccessful) { MessageBox.Show("Uh oh. Something went wrong", "Excel to pdf"); } //workbook.Close(); } //excel.Quit(); } //Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); //excel.Visible = true; //var workbook = excel.Workbooks.Add(DBNull.Value); //Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; //Microsoft.Office.Interop.Excel.Range range = sheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range; //range.set_Item(0, 1, "Test"); //range.CopyFromRecordset(rs.InternalObject, System.Reflection.Missing.Value, 10); }