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)
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.
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)
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
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.
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
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
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.
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?
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.
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