VSTO之四

19读取word中插入得excel

原理就是用package.open打开excel的包。我们如果修改chart图表的数据源excel，那么会修改会直接反映到页面，但是修改excel，页面不会变化，因为在页面中显示得表格是一个缓存的图片，只有我们重新打开这个excle激活一次，变化才会显示到页面。

public static voidEditExcel()

        {           

            string WordPath1 = @"testXml.docx";

            //构建Package对象，打开word文档

            using ( Packagepackage = Package.Open(WordPath1))

            {              

                //用uri对象设置包内文件路径

                Uri documetUri = newUri("/word/embeddings/Microsoft_Office_Excel____1.xlsx",UriKind.Relative);

                //取得包内文件

                PackagePart documanetPart =package.GetPart(documetUri);

 

 

                //打开word包中得excel。注意open方法中要将文件模式设置为 FileMode.Open, FileAccess.ReadWrite可读写，不然

                //修改excel后无法保存会包中。

                using (PackagepackageExcel = Package.Open(documanetPart.GetStream(),FileMode.Open, FileAccess.ReadWrite))

                {

                   try

                   {

                       Uri ExcelUri = new Uri("/xl/worksheets/sheet1.xml", UriKind.Relative);

                       PackagePart ExcelPart =packageExcel.GetPart(ExcelUri);

 

                       //声明一个xml对象

                       XmlDocument ExcelXml = new XmlDocument();

                       ExcelXml.Load(ExcelPart.GetStream());

 

                       setXml(ExcelXml, "B1", "101");//修改xml节点值

 

                       //保存xml文件

                     ExcelXml.Save(ExcelPart.GetStream(FileMode.Create,FileAccess.ReadWrite));

                     

                   }

                   

                   catch (Exceptionex)

                   {

                       

                       

                    }            

                   finally

                   {

                       packageExcel.Close();

                   }

                   

                }

               package.Close();

            } 

        }

 

        //修改excel中xml节点值

        private static void setXml(XmlDocumentdocumentXml, string changAccess, string changValue)

        {

            //构建excle的Schema

            const stringworksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";

            NameTable nt = newNameTable();

            XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);

           nsManager.AddNamespace("d",worksheetSchema);

            //从xml中取出节点

            XmlNode cellNode = documentXml.SelectSingleNode(string.Format("//d:sheetData/d:row/d:c[@r='{0}']",changAccess), nsManager);

           cellNode["v"].InnerText =changValue;//修改节点值

        }

.

在VSTO中读取word中得excel，也可以用该方法激活openxm中修改后得excel

直接在代码中取得excel对象修改后保存。但是该方法不能取得chart图表对象得数据源。

string paTh = Environment.CurrentDirectory;

            string filePath = paTh + @"\testXml.docx";

            object missing = Type.Missing;

            object[] filename = newobject[1] { filePath };

           Microsoft.Office.Interop.Word.ApplicationwordApp = new Microsoft.Office.Interop.Word.Application();

            Document wordDoc = wordApp.Documents.Open(ref filename[0], refmissing, ref missing, refmissing, ref missing, refmissing, ref missing, refmissing, ref missing, refmissing, ref missing, refmissing, ref missing, refmissing, ref missing, refmissing);

            try

            {

               Microsoft.Office.Interop.Excel.Applicationm_excelApp = newMicrosoft.Office.Interop.Excel.ApplicationClass();//excel操作对象

               

                //打开excel               

               Microsoft.Office.Interop.Word.InlineShapeInlineShape = wordDoc.InlineShapes[2];

                object VerbIndex = Microsoft.Office.Interop.Word.WdOLEVerb.wdOLEVerbOpen;

               InlineShape.OLEFormat.DoVerb(refVerbIndex);

               InlineShape.OLEFormat.Activate();

                Workbook excelWorkbook = (Workbook)InlineShape.OLEFormat.Object;

               Microsoft.Office.Interop.Excel.ApplicationexcelApp = excelWorkbook.Application;

                WorksheetexcelWorksheet = (Worksheet)excelApp.ActiveSheet;

                //修改单元格

               excelApp.Cells[2, 2] = "21";

               wordDoc.Save();

               

 

            }

            catch (Exceptionex)

            {

                //MessageBox.Show(ex.Message);

                //return false;

            }

            finally

            {

                //保存word

                object[] options = newobject[1] { true};

 

               wordDoc.Close(ref options[0], ref missing, refmissing);

            }

 

20 使用VSTO读写excel

 

1 在当前sheet写入单元格

this.Application.Cells[1, 1] = "123";

2 在当前sheet读取单元格

Range range = (Range)Application.Cells[1, 1];

            string str =range.Text.ToString();

 

3 在其他sheet或book，控件中读写单元格

Globals.Sheet1.Cells[1, 2] = 555;

 

            Range range =(Range)Globals.Sheet1.Cells[1, 1];

            string str =range.Text.ToString();

            MessageBox.Show(str);