We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

SpreadsheetML support

Hi there,


I’m evaluating Aspose Cells for our application. We want to remove our application’s reliance on the Excel.interop. Right now, we use the interop to convert Excel 2003 files into the spreadsheet ml file format. Our application has a lot of code that manipulates the spreadsheet ML to generate the results we want. It’s what prevents us from jumping straight to Open Office XML.

I tried to use Aspose Cells to replicate what the interop is doing, but I’m having trouble. The resulting xml file does not match what the interop is producing. The two files look different just from visually inspection, and the interop file is over 50% larger.

Our application doesn’t work when we try to use the Aspose Cells produced spreadsheet ML file.

Am I doing something wrong or does Aspose cells produce a different version of spreadsheet ML than the interop?

Aspose.Cells code:
Workbook workbook;
FileStream fstream = new FileStream(filePath, FileMode.Open);
workbook = new Workbook(fstream);
workbook.Save(outputPath, SaveFormat.SpreadsheetML);


Hi,


Well, Aspose.Cells does follows Ms Excel standards and specifications when rendering to SpreadsheetML file format. Could you try to use SpreadsheetML2003SaveOptions and explicitly set your desired options/ settings on accordingly when saving to SpreadsheetML (.xml) file, see the sample code snippet for your reference:
e.g
Sample code:

SpreadsheetML2003SaveOptions saveOptions = new SpreadsheetML2003SaveOptions();
saveOptions.ExportColumnIndexOfCell = true;
workbook.Save(“out1.xml”,saveOptions);


Let us know if we can be of any further help.

Thank you.

Thanks for the response. I tried your suggestion. The resulting xml file is larger, but it’s still smaller and not quite the same as the interop version.


One difference that I noticed is that the interop version has the tag. The tag is missing from the Aspose Cells version. Parts of our code queries for the tag.

How do I get Aspose Cells to output ?

Hi,


Could you try to set SpreadsheetML2003SaveOptions.SortNames Boolean attribute to set it to true if it makes any difference.

If you still find any issue. kindly do post your template Excel file and output SpreadsheetML file by Aspose.Cells APIs and paste your sample code (runnable) here, we will check it soon. Also provide your desired file that you could get using Office interop. APIs and provide some screen shots to highlight the problematic areas comparing both output files.


Thank you.

Amjad,


I’ve pasted some code that will mimic what we’re seeing. I’ve also attached the relevant input/output files. You’ll notice the interop output is about 3.6 kb larger.

One of the problems we’re having with the Aspose output is that it doesn’t generate the tags. A search for “NamedCell” doesn’t return any results.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Aspose.Cells;

namespace ExcelTransform
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string startupPath = AppDomain.CurrentDomain.BaseDirectory;
string filePath = startupPath + “TemplateExcel.xls”;
string asposeOutputPath = startupPath + “AsposeOutput.xml”;
string interopOutputPath = startupPath + “InteropOutput.xml”;

AsposeExcelTransform asposeExcelTransform = new AsposeExcelTransform(filePath);
asposeExcelTransform.SaveWorkbookAsXml(asposeOutputPath);

InteropExcelTransform interopExcelTransform = new InteropExcelTransform(filePath);
interopExcelTransform.SaveWorkbookAsXml(interopOutputPath);
interopExcelTransform.CleanupExcel();
}
}

public class AsposeExcelTransform
{
protected Workbook AsposeWorkbook { get; set; }

public AsposeExcelTransform(string filePath)
{
try
{
//Create a Stream object
FileStream fstream = new FileStream(filePath, FileMode.Open);

//Creating a Workbook object, open the file from a Stream object
//that contains the content of file and it should support seeking
AsposeWorkbook = new Workbook(fstream);
}
catch (Exception ex)
{
AsposeWorkbook = null;
var errorMessage = ex.Message + "\n ||| " + ex.StackTrace;
}
}

public void SaveWorkbookAsXml(string outputPath)
{
SaveWorkbookAsXml(AsposeWorkbook, outputPath);
}

public static void SaveWorkbookAsXml(Workbook workbook, string outputPath)
{
//Save in SpreadsheetML format;
SpreadsheetML2003SaveOptions saveOptions = new
SpreadsheetML2003SaveOptions();
saveOptions.ExportColumnIndexOfCell = true;
saveOptions.SortNames = true;

workbook.Save(outputPath, saveOptions);
}
}

public class InteropExcelTransform
{
protected Microsoft.Office.Interop.Excel.Application InteropExcel { get; set; }
private Microsoft.Office.Interop.Excel.Workbook InteropWorkbook { get; set; }
static readonly object Missing = Type.Missing;

public InteropExcelTransform(string filePath)
{
try
{

InteropExcel = new Microsoft.Office.Interop.Excel.Application
{
DisplayAlerts = false,
Visible = false
};

InteropWorkbook = InteropExcel.Workbooks.Open(filePath, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);
}
catch (Exception ex)
{
var errorMessage = ex.Message + "\n ||| " + ex.StackTrace;
}
}

public void CleanupExcel()
{
try
{
if ((InteropWorkbook != null))
{
InteropWorkbook.Close(false, Missing, Missing);
InteropWorkbook = null;
}

if (InteropExcel != null)
{
InteropExcel.Quit();
Marshal.ReleaseComObject(InteropExcel);
InteropExcel = null;
}
}
catch (Exception ex)
{
var errorMessage = ex.Message + "\n ||| " + ex.StackTrace;
}
}

public void SaveWorkbookAsXml(string outputPath)
{
SaveWorkbookAsXml(InteropWorkbook, outputPath);
}

public static void SaveWorkbookAsXml(Microsoft.Office.Interop.Excel.Workbook workbook, string outputPath)
{
// Save the workbook
workbook.SaveAs(outputPath, // Filename
Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, //FileFormat
Missing, // Password
Missing, // WriteResPassword
Missing, // ReadOnlyRecommended
Missing, // CreateBackup
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, // AccessMode
Missing, // ConflictResolution
Missing, // AddToMru
Missing, // TextCodepage
Missing, // TextVisualLayout
Missing); // Local
}
}
}

Hi,


Thanks for providing us template file and output XML files.

We will check your issue and get back to you soon.

Thank you.

Hi,


After an initial test, I observed the issue as you mentioned by using the following sample code with your template file. I found no “” tags in the rendered SpreadsheetML file when converting XLS to XML Spreadsheet 2003. When I simply open the template XLS file into Ms Excel and render to Spreadsheet ML, its size is about 14MB and it does have “” tags in it. The generated XML file (by Aspose.Cells) has size equal to 11MB and it does not have “” tags in it:
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\TemplateExcel.xls”);

//Save in SpreadsheetML format;
SpreadsheetML2003SaveOptions saveOptions = new SpreadsheetML2003SaveOptions();
saveOptions.ExportColumnIndexOfCell = true;

workbook.Save(“e:\test2\out1.xml”, saveOptions);

I have logged a ticket with an id “CELLSNET-43947” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

Hi,

Thanks for your posting and using Aspose.Cells.

We think NamedCell tag is useless, it will consume performance if we export NamedCell tag because we have to check all named range when we exported a cell.

Could you please explain that why your code requires NamedCell tag? And it’s better that you can remove the codes which requires NamedCell tag.

We use the NamedCell tag because we need to know what named range is associated with that cell.

Our client can change the column names at will, so our application basically ignores the column names for processing purposes. Instead we use named ranges because they tend to stay the same year to year. The named ranges act more like a column header than the real column headers.

Do you know if Aspose has an alternative way of obtaining the named range of a given cell?

Hi Amuh Dinka,

Thanks for your posting and using Aspose.Cells.

We have logged your comment in our database against this issue for investigation. We will look into it and update you. Once there is some news for you, we will let you know asap.

Hi,

I am afraid, we have no good solution for it, only have to check each named range.
See following codes:
e.g
Sample code:

.............
private static void Main(string[] args)
{
Workbook workbok = new Workbook(path + "TemplateExcel.xls");
Range[] ranges = workbok.Worksheets.GetNamedRanges();



}
public ArrayList GetNamdRange(Range[] ranges ,Worksheet sheet,int row, int column)
{
ArrayList list = new ArrayList();
for(int i = 0; i < ranges.Length; i++)
{ Range r = ranges[i];
if (ranges[i].Worksheet == sheet
&& r.FirstRow <= row
&& r.FirstRow + r.RowCount > row
&& r.FirstColumn <= column
&& r.FirstColumn + r.ColumnCount > column)
{
list.Add(r);
}
}
return list;
}


Thank you.