Hi Team
can you please share sample code on how to update excel sheet which is inside word doc.
thanks
Hi Yatin,
Thanks for your inquiry. Yes, you can meet this requirement using Aspose.Words and Aspose.Cells APIs.
1) Extract embedded Excel document from Word document and load it inside Aspose.Cells DOM using following code:
Document doc = new Document(“in.docx”);
// Get collection of shapes<o:p></o:p>
NodeCollection shapes = doc.GetChildNodes(NodeType.Shape, true);<o:p></o:p>
int i = 0;<o:p></o:p>
//Loop through all shapes<o:p></o:p>
foreach (Shape shape in shapes)<o:p></o:p>
{<o:p></o:p>
if (shape.OleFormat != null)<o:p></o:p>
{<o:p></o:p>
if (!shape.OleFormat.IsLink)<o:p></o:p>
{<o:p></o:p>
//Extract OLE Word object<o:p></o:p>
if (shape.OleFormat.ProgId == “Word.Document.12”)<o:p></o:p>
{<o:p></o:p>
MemoryStream stream = new MemoryStream();<o:p></o:p>
shape.OleFormat.Save(stream);<o:p></o:p>
Document newDoc = new Document(stream);<o:p></o:p>
newDoc.Save(string.Format(@“C:\test\outEmbeded_{0}.html”, i));<o:p></o:p>
i++;<o:p></o:p>
}<o:p></o:p>
//Extract OLE Excel object<o:p></o:p>
if (shape.OleFormat.ProgId == “Excel.Sheet.12”)<o:p></o:p>
{<o:p></o:p>
// Here you can use Aspose.Cells component<o:p></o:p>
// to be able to convert MS Excel files to separate HTML files<o:p></o:p>
}<o:p></o:p>
}
}<o:p></o:p>
}
2) Do required modification using Aspose.Cells
3) Aspose.Words supports insertion of embedded OLE objects in Microsoft Word document. Please see the following examples:
Insert embedded Excel 97-2003 document as iconic OLE object from stream using predefined image:
Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);<o:p></o:p>
Stream memoryStream = File.OpenRead(MyDir + “Book1.xls”);<o:p></o:p>
Shape oleObject = builder.InsertOleObject(memoryStream, “Excel.Sheet.8”, true, null);<o:p></o:p>
doc.Save(MyDir + @“out.docx”);Hope, this helps.
Best regards,
Hi Awais,
I am able to read the embedded xls in word .But after updating the cell values I am not sure how to save the xls back in the word.
The xls has 2 sheets Input and Final . We need to update few values in Input and in the Final sheet based on some formatting the values will be displayed .
In the end the word will be converted to pdf format.
Please see the code below and kindly advice.
if(shape.getOleFormat().getProgId().equals(“Excel.Sheet.8”) ){
ByteArrayOutputStream stream = new ByteArrayOutputStream();
shape.getOleFormat().save(stream);
InputStream stream1 = new ByteArrayInputStream(stream.toByteArray());
Workbook xls = new Workbook(stream1);
Worksheet sheet = xls.getWorksheets().get(0);
Cells cells = sheet.getCells();
Cell valueCell = cells.get(0, 1);
valueCell.setValue(“111”);
}
Hi
Can you. Please reply as we need to complete a poc urgently.
thanks
Hi Yatin,
Thanks for your inquiry. Embedded excel file in Word document is represented as a Shape node in Aspose.Words’ DOM; you can loop through Shape nodes collection and get reference to the original object, after modifying the excel file using Aspose.Cells, you can insert it as a new OLE object using the following way and of-course after that remove the old Shape:
Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);<o:p></o:p>
Stream memoryStream = File.OpenRead(MyDir + “Book1.xls”);<o:p></o:p>
Shape oleObject = builder.InsertOleObject(memoryStream, “Excel.Sheet.8”, true, null);<o:p></o:p>
doc.Save(MyDir + @“out.docx”);You can insert new Shape after the old Shape using the CompositeNode.InsertAfter method.
Best regards,