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);