Hi Team,
I have word document where I have embedded an excel sheet into it for further calculation. How do I access that excel sheet through this dll.
public void UpdateOle(string fileName)
{
Document doc = new Document();
doc.LoadFromFile(fileName);
Section sec = doc.Sections[0];
MemoryStream xls = new MemoryStream();
//Traverse through all Child Objects in the body of each section
foreach (DocumentObject obj in sec.Body.ChildObjects)
{
if (obj is Paragraph)
{
Paragraph par = obj as Paragraph;
//Traverse through all Child Objects in Paragraph
foreach (DocumentObject o in par.ChildObjects)
{
//Find the Ole Objects and Change the link
if (o.DocumentObjectType == DocumentObjectType.OleObject)
{
DocOleObject Ole = o as DocOleObject;
GetObjectStream(Ole, xls);
DocPicture pic = new DocPicture(doc);
pic.LoadImage(SaveTXlsImage(xls));
pic.Width = Ole.OlePicture.Width;
pic.Height = Ole.OlePicture.Height;
Ole.SetOlePicture(pic);
Ole.SetNativeData(xls.ToArray());
}
}
}
}
doc.SaveToFile("OleObject2.docx", Spire.Doc.FileFormat.Docx);
xls.Close();
}
public static void GetObjectStream(DocOleObject obj, MemoryStream xls)
{
using (MemoryStream ms = new MemoryStream(obj.NativeData))
{
Workbook workbook1 = new Workbook();
workbook1.LoadFromStream(ms);
Worksheet sheet1 = workbook1.Worksheets[0];
sheet1.Range["A1"].Value2 = 3000;// do something
workbook1.SaveToStream(xls);//Excel version needs to be same as original.
}
}
public static Image SaveTXlsImage(MemoryStream ms)
{
Workbook workbook2 = new Workbook();
workbook2.LoadFromStream(ms);
Worksheet sheet2 = workbook2.Worksheets[0];
sheet2.PageSetup.LeftMargin = 0;
sheet2.PageSetup.TopMargin = 0;
sheet2.PageSetup.RightMargin = 0;
sheet2.PageSetup.BottomMargin = 0;
int lastRow = sheet2.LastRow;
int lastColumn = sheet2.LastColumn;
return sheet2.ToImage(1, 1, lastRow, lastColumn);
}