Free Support Forum - aspose.com

Convert Excel Automation to Aspose.cell

Hello,

We have used Excel Automation many places in our application. We want to use Aspose.cell instead of it.

Can you please provide me some reference documentation that makes conversion easy?

for example, we are converting an Excel File into an XML Spreadsheet using excel automation like below

’ 'Save spreadsheet as XML file using automation
excelApp = New Excel.ApplicationClass
excelApp.DisplayAlerts = False
excelApp.Workbooks.Open(Me.XLSFile, 0,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, True, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value)

            excelApp.Calculation = **Excel.XlCalculation.xlCalculationManual**
            excelApp.ScreenUpdating = False

            wkbook = excelApp.Workbooks(1)

            For Each worksheet As Excel.Worksheet In wkbook.Worksheets
                worksheet.AutoFilterMode = False
            Next
            wkbook.SaveAs(mXMLSpreadsheetFile, **Excel.XlFileFormat.xlXMLSpreadsheet**, AccessMode:=Excel.XlSaveAsAccessMode.xlExclusive)

what should be the equivalent format/api we use?

Thanks,
Prerak Shah

@PrerakShah,

Thank you for your query.

Aspose.Cells is a better alternative of Excel Automation and provides comprehensive features to perform all the related tasks. You can perform the required tasks as follows:

Workbook workbook = new Workbook();

// Set the Formula Calculation Mode to Manual
workbook.Settings.CalcMode = CalcModeType.Manual;

foreach (var sheet in workbook.Worksheets)
{
    Worksheet ws = workbook.Worksheets[0];
    ws.RemoveAutoFilter();
}
// Save the workbook
workbook.Save("output_out.xml", Aspose.Cells.SaveFormat.SpreadsheetML);

See the documents with example codes for your reference.
Developers Guide

If you want to test feature using ready to run examples along with template files, please download sample projects from Aspose.Cells for .NET and follow How to run the Examples

If you want to test Aspose.Cells without evaluation version limitations, you can also request a 30 Day Temporary License.

HI Thank you for your reply.

When I compare the shpredsheet xml file generated by Aspose.cells with SaveFormat.SpreadsheetML and ExcelAutomation I found below difference.

Excel Automation :

Aspose.Cell
<ss:Table ss:DefaultRowHeight=“12.75” ss:DefaultColumnWidth=“48” ss:ExpandedRowCount=“339415” ss:StyleID=“S22”>

Aspose.Cell has missing ss:ExpandedColumnCount="1 and x:FullColumns=“1”

can you please let us know how can we get this attributes?

Our code is processing based on these attributes that are missing.

Thanks in advance.

Prerak Shah

forget to add excel automation attribute.

Excel Automation :

Also attaching actual generated files. ExcelAutomation.ss.zip (84.8 KB)

@PrerakShah,

Please provide us the sample code which is used to create this “Aspose.ss.xml” for our analysis here. Also mention the Aspose.Cells version which is used for this testing. If you are not using latest version, download the latest version Aspose.Cells for .NET 18.8 and test the issue again. If issue is still there, provide us code along with any other detail if required.

Please find the code below to generate the Aspose.ss.xml File

mXMLSpreadsheetFile = “Aspose.ss.xml”
Me.XLSFile = “O:\Prerak\ProcCode.xlsx”
Using workBook As Aspose.Cells.Workbook = New Aspose.Cells.Workbook(Me.XLSFile)
workBook.Settings.CalcMode = Aspose.Cells.CalcModeType.Manual
For Each worksheet As Aspose.Cells.Worksheet In workBook.Worksheets
worksheet.RemoveAutoFilter()
Next
'Save spreadsheet as XML file
''Set LimitAsXls = false of SpreadsheetML2003SaveOptions to support more than 256 columns
'Dim saveOptions As SpreadsheetML2003SaveOptions = New SpreadsheetML2003SaveOptions()
'saveOptions.LimitAsXls = False
workBook.Save(mXMLSpreadsheetFile, Aspose.Cells.SaveFormat.SpreadsheetML)
End Using

We are using version 17.9.0.0 for Aspose.cells
I have also uploaded the xlsx file.
ProcCode.zip (48.4 KB)

Hi,

I just tried with Version 18.8 and notice same output.

There are other excel files also I have tested which is not includes ss:ExpandedCoumnCount in the output xml file.

Please do needful to resolve the issue.

Thanks,
Prerak Shah

The original Automation code to generate excelAutomation.ss.xml is as below.

'Use Excel Automation to convert an Excel File into an XML Spreadsheet
Dim excelApp As Excel.ApplicationClass = Nothing
Dim wkbook As Excel.Workbook = Nothing
Try
mXMLSpreadsheetFile = “O:\Prerak\excelAutomation.ss.xml”
Me.XLSFile = “O:\Prerak\ProcCode.xlsx”

            'Save spreadsheet as XML file using automation
            excelApp = New Excel.ApplicationClass
            excelApp.DisplayAlerts = False
            excelApp.Workbooks.Open(Me.XLSFile, 0,
            System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
            System.Reflection.Missing.Value, True, System.Reflection.Missing.Value,
            System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
            System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
            System.Reflection.Missing.Value)

            excelApp.Calculation = Excel.XlCalculation.xlCalculationManual
            excelApp.ScreenUpdating = False

            wkbook = excelApp.Workbooks(1)

            For Each worksheet As Excel.Worksheet In wkbook.Worksheets
                worksheet.AutoFilterMode = False
            Next
            wkbook.SaveAs(mXMLSpreadsheetFile, Excel.XlFileFormat.xlXMLSpreadsheet, AccessMode:=Excel.XlSaveAsAccessMode.xlExclusive)

            'wkbook.SaveAs(mXMLSpreadsheetFile, Excel.XlFileFormat.xlXMLSpreadsheet, System.Reflection.Missing.Value, _
            'System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
            '  Excel.XlSaveAsAccessMode.xlExclusive, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
            'System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)

        Finally
            If Not excelApp Is Nothing Then
                excelApp.Workbooks.Close()
                excelApp.Quit()
                If Not wkbook Is Nothing Then
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(wkbook)
                    wkbook = Nothing
                End If
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
                excelApp = Nothing
                System.GC.Collect()
                GC.WaitForPendingFinalizers()
            End If
        End Try

@PrerakShah,

We were able to observe the difference but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46292 - Difference in XML files

Thank you!

We are also facing another issue with Aspoce.cell as below.

When we have Tab Delimited file (TSV) or Comma Delimited file (CSV) and if we save that file as SpreadsheetML, saveed file has worksheet name as “sheet1”

With Excel Automation if you save as Excel.XlFileFormat.xlXMLSpreadsheet for TSV and CSV file has same worksheet name as file name up 31 characters
while Aspose renames it to “sheet1” when we save with SpreadsheetML2003SaveOptions or SaveFormat.SpreadsheetML

Can you please resolve this issue too?

Thanks,
Prerak Shah

@PrerakShah,

We were able to understand the requirement but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46294 - Name Worksheet same as source file name while converting CSV/TSV to Spreadsheet

@PrerakShah,

This is to inform you that we have fixed your issues CELLSNET-46294 and CELLSNET-46292 now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@PrerakShah,

Please try our latest version/fix (attached): Aspose.Cells for .NET v18.8.2

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells18.8.2 For .Net2_AuthenticodeSigned.Zip (4.6 MB)
Aspose.Cells18.8.2 For .Net4.0.Zip (4.6 MB)

A post was split to a new topic: Exception “This file’s format is not supported or you don’t specify a correct format.” while loading XLS file

Regarding CELLSNET-46294 issue, now it’s giving worksheet name as file name however it’s taking only first 23 characters. Excel Automation is taking first 31 characters. So can you please update all to take the 31 characters?

@PrerakShah,

I have checked different CSV files with names upto 31 characters and observed that the output file contains proper sheet name upto 31 characters. You may please test the scenario again using attached sample file and provide the feedback. Please test with file names with length more than 31 characters also and share your comments.

1234567890123456789012345678901.zip (5.7 KB)
1234567890123456789012345678901 (2).zip (208 Bytes)

The issues you have found earlier (filed as CELLSNET-46294,CELLSNET-46292) have been fixed in Aspose.Cells for .NET v18.9. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi