We are using Aspose Cells as a in memory model for generating excel like reports. We do all our formula calculation inside Aspose Cells and render a HTML version of the excel snapshot to the screen. Our excel sheet contains temporary data that is used for calculation purposes only which need not be shown to the user. Only a small part of the excel is to be shown to the user in a grid like view. I understand that SpreadsheetML is for exporting the complete excel sheet but is there any way where I can extract a named cell range to XML (SpreadsheetML like table tag format) using Aspose?
-Shashi
Hi Shashi,
Thanks for considering Aspose.
Well, Workbook.Save(fileName, FileFormatType.SpreadsheetML).... saves all the data, contents, formattings etc. of a spreadsheet and not a specific part of a workbook. Since you want only to render a specific named range of cells to xml. I think you can try: After making your required temporary calculations and other tasks, you may eliminate them (except for your required named range of cells) from the spreadsheet (or save them using some means or sources) and convert the rest to make SpreadsheetML contents.
Thank you.
Hi Amjad,
Thanks for the quick reply. I am running this application inside an application server and am more concerned about the number of temporary Aspose objects created in the memory. I have still not done analysis on how light the Aspose Model is from a memory standpoint.
To summarize what your suggestion:
a) Do the calculations using the temporary data cells. Have a named cell range for the data that is to be rendered as HTML.
b) Create a new workbook in memory and copy this cell range (only the values as the formulas will have dependencies on the temp data cells - Is this possible?) to the new worksheet. --> Will consume some extra memory and CPU cycles...
c) Export the complete worksheet as a SpreadsheetML and then use XSL to render the sheet. Is this what you are suggesting?
Thanks,
Shashi
Hi,
Here is a sample code, you may consult it:
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get all the worksheets in the book.
Worksheets worksheets = workbook.Worksheets;
//Get the first worksheet in the worksheets collection.
Worksheet worksheet = workbook.Worksheets[0];
//Get the cells.
Cells cells = worksheet.Cells;
//Input some data into A1:J10
for(int i =0;i<10;i++)
{
for(int j = 0;j<10;j++)
{
cells[i,j].PutValue(i.ToString() + "," +j.ToString());
}
}
//Create a range of cells.
Range range1 = worksheet.Cells.CreateRange("E12","I12");
//Name the range.
range1.Name = "MyRange";
//Input some data with
range1[0,0].PutValue("Test");
range1[0,4].PutValue("123");
//Export datatable based on the range.
DataTable dt = range1.ExportDataTable();
//Create a new workbook.
Workbook wb1 = new Workbook();
//Get its first worksheet.
Worksheet ws = wb1.Worksheets[0];
//Extract data from the datatable to the worksheet cells.
ws.Cells.ImportDataTable(dt,false,0,0);
//Save the xml file.
wb1.Save("d:\\test\\onlyrange.xml",FileFormatType.SpreadsheetML);
Thank you.