예제 #1
0
        private void btnExcelByProcess_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 = Excel.Application.FromProcess(System.Diagnostics.Process.GetProcessesByName("EXCEL")[0]))
            {
                if (excel == null)
                {
                    MessageBox.Show("No existing running instance of Excel found.");
                    return;
                }

                excel.Visible = true;

                using (var workbook = excel.Workbooks.Add())
                {
                    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("D2:G10"))
                        {
                            Image img = Image.FromFile(@"c:\users\tdixon\pictures\grid1.png");

                            //worksheet.Shapes.AddPicture(img, range, true, true).Dispose();

                            /*using (var shape = worksheet.Shapes.AddPicture(@"c:\users\tdixon\pictures\grid1.png", false, true, range, true))
                             * {
                             *  MessageBox.Show(shape.Name);
                             * }*/
                        }
                    }

                    workbook.Close();
                }

                excel.Quit();
            }
        }
예제 #2
0
        private void btnFromDataTable_Click(object sender, EventArgs e)
        {
            using (SqlConnection cnn = new SqlConnection(@"Server=lau-sql2005\development;Database=PRImateDB;Trusted_Connection=True;"))
            {
                try
                {
                    cnn.Open();
                }
                catch
                {
                    MessageBox.Show("connection failed", "failure", MessageBoxButtons.OK);
                    return;
                }

                DataTable      dt = new DataTable();
                SqlDataAdapter da;
                SqlCommand     cmd = new SqlCommand("select * from viewRPT_DIER_ProposalBody where ProposalID = -613377248", cnn);

                da = new SqlDataAdapter(cmd);
                da.Fill(dt);

                ADODB.Recordset rs = ADODB.Recordset.FromDataTable(dt);
            }

            //using (SqlConnection cnn = new SqlConnection("Server=lau-sql2005;Database=PRImateDB;Trusted_Connection=True;"))
            //{
            //    try
            //    {
            //        cnn.Open();
            //    }
            //    catch
            //    {
            //        MessageBox.Show("connection failed", "failure", MessageBoxButtons.OK);
            //        return;
            //    }

            //    DataTable dt = new DataTable();
            //    SqlDataAdapter da;
            //    SqlCommand cmd = new SqlCommand("select * from listEmployees", cnn);

            //    da = new SqlDataAdapter(cmd);
            //    da.Fill(dt);

            //    ADODB.Recordset rs = ADODB.Recordset.FromDataTable(dt);
            //}
        }
예제 #3
0
        private void btnExcelByProcess_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 = Excel.Application.FromProcess(System.Diagnostics.Process.GetProcessesByName("EXCEL")[0]))
            {
                if (excel == null)
                {
                    MessageBox.Show("No existing running instance of Excel found.");
                    return;
                }

                excel.Visible = true;

                using (var workbook = excel.Workbooks.Add())
                {
                    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("D2:G10"))
                        {
                            Image img = Image.FromFile(@"c:\users\tdixon\pictures\grid1.png");

                            //worksheet.Shapes.AddPicture(img, range, true, true).Dispose();

                            /*using (var shape = worksheet.Shapes.AddPicture(@"c:\users\tdixon\pictures\grid1.png", false, true, range, true))
                            {
                                MessageBox.Show(shape.Name);
                            }*/
                        }
                    }

                    workbook.Close();
                }

                excel.Quit();
            }
        }
예제 #4
0
        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);
        }
예제 #5
0
        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);
        }
예제 #6
0
        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);
        }
예제 #7
0
        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);
        }
예제 #8
0
        /// <summary>
        /// Builds a Recordset from a DataTable object.
        /// </summary>
        /// <param name="table">The source DataTable.</param>
        /// <param name="columnDefs">Optional. Provide size, precision, scale etc for columns in the DataTable.</param>
        /// <returns>A Recordset</returns>
        public static Recordset FromDataTable(DataTable table, IEnumerable <Defs.ColumnDef> columnDefs = null)
        {
            Recordset rs = new Recordset();

            var defs = new Dictionary <string, Defs.ColumnDef>();

            if (columnDefs != null)
            {
                foreach (var columnDef in columnDefs)
                {
                    if (string.IsNullOrEmpty(columnDef.Name))
                    {
                        throw new ArgumentException("columnDefs contains object with empty Name property.");
                    }

                    defs.Add(columnDef.Name, columnDef);
                }
            }

            // Add the columns to the recordset
            for (int x = 0; x < table.Columns.Count; x++)
            {
                DataColumn     col = table.Columns[x];
                Enums.DataType rsDataType;
                long           size;

                size = 0; // default used for data types which do not have size

                // figure out what the data type of the current column is
                if (col.DataType == typeof(Int16))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.SmallInt;
                }
                else if (col.DataType == typeof(Int32))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Integer;
                }
                else if (col.DataType == typeof(Int64))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.BigInt;
                }
                else if (col.DataType == typeof(Boolean))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Boolean;
                }
                else if (col.DataType == typeof(DateTime))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Date;
                }
                else if (col.DataType == typeof(Decimal))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Decimal;
                }
                else if (col.DataType == typeof(Double))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Double;
                }
                else if (col.DataType == typeof(Guid))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.GUID;
                }
                else if (col.DataType == typeof(String))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.VarWChar;
                    size       = 1000; // should be sufficient for most large strings.
                }
                else if (col.DataType == typeof(Single))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.Single;
                }
                else if (col.DataType == typeof(byte))
                {
                    rsDataType = STC.Automation.Office.ADODB.Enums.DataType.TinyInt;
                }
                else
                {
                    throw new NotImplementedException("Unsupported datatype found in DataTable during converion to Recordset");
                }

                Defs.ColumnDef def = null;
                if (defs.ContainsKey(col.ColumnName))
                {
                    def = defs[col.ColumnName];
                    if (def.DefinedSize.HasValue)
                    {
                        size = def.DefinedSize.Value;
                    }
                }

                // create the column
                if (size == 0)
                {
                    // size is not relevant
                    rs.Fields.Append(col.Caption, rsDataType, STC.Automation.Office.ADODB.Enums.FieldAttribute.MayBeNull);
                }
                else
                {
                    // size is relevant
                    rs.Fields.Append(col.Caption, rsDataType, size, STC.Automation.Office.ADODB.Enums.FieldAttribute.MayBeNull);
                }

                if (def?.Precision != null)
                {
                    rs.Fields[col.ColumnName].Precision = def.Precision.Value;
                }
                if (def?.NumericScale != null)
                {
                    rs.Fields[col.ColumnName].NumericScale = def.NumericScale.Value;
                }
            }

            rs.Open();

            // Now, add the rows
            foreach (DataRow dr in table.Rows)
            {
                rs.AddNew();

                for (int x = 0; x < table.Columns.Count; x++)
                {
                    DataColumn col = table.Columns[x];
                    if (dr[col.Caption].GetType() == typeof(Guid))
                    {
                        rs.Fields[col.Caption].Value = ((Guid)dr[col.Caption]).ToString("B"); //need to format the GUID to a format that ABODB will accept
                    }
                    else
                    {
                        rs.Fields[col.Caption].Value = dr[col.Caption];
                    }
                }
                rs.Update();
            }

            return(rs);
        }