 | Developers can make use of Aspose.Cells to open existing files for different purposes. For example, you can open an existing file to retrieve data from it or you can use a pre-defined Designer Spreadsheet file to speed up your development process etc. Aspose.Cells allows developers to open different kinds of source files. These source files can be Excel reports (XLS, XLSX, XLSM, XLSB etc.), SpreadsheetML, CSV or Tab Delimited files. In this topic, we will discuss about opening these different kinds of source files using Aspose.Cells. |
Simple Ways to Open Excel Files
Opening through Path
Developers can simply open an Excel file through its file path specified in the constructor of the Workbook class. All you have to do, is to pass the file path in the constructor (the component will auto-detect the file format type) as shown below:
Example:
//Creating a Workbook object and opening an Excel file using its file path Workbook workbook = new Workbook("C:\\book1.xls");
'Creating a Workbook object and opening an Excel file using its file path
Dim workbook As Workbook = New Workbook("C:\book1.xls")
Opening through Stream
Sometimes, the Excel file (that we need to open) is stored as a stream. In such cases, developers can use an overloaded version of the constructor method that takes the Stream object (containing the Excel file) to open the file.
Example:
//Create a Stream object FileStream fstream = new FileStream("C:\\book1.xls", FileMode.Open); //Creating a Workbook object, open the file from a Stream object //that contains the content of file and it should support seeking Workbook workbook = new Workbook(fstream);
'Create a Stream object
Dim fstream As FileStream = New FileStream("C:\book1.xls", FileMode.Open)
'Creating an Workbook object, open the file from a Stream object
'that contains the content of file and it should support seeking
Dim workbook As Workbook = New Workbook(fstream)
Opening Files of Different Microsoft Excel Versions
It's very common to believe that the Excel files might be created using different versions of Microsoft Excel such as, Microsoft Excel 97-2003, 2007 etc. You might require to load a file with your different formats, e.g XLS, XLSX, XLSM, XLSB, Spreadsheet XML, Tab Delimited, CSV, ODS etc. In such cases, developers can use the constructor or specify the FileFormat attribute of Workbook class that specifies the format of the Excel file using the FileFormatType enumeration.
LoadFormat enumeration contains many pre-defined file formats (that can be chosen by you accordingly) as follows:
| File Format Types | Description |
|---|---|
| CSV | Represents a CSV file |
| Excel97To2003 | Represents an Excel 97 - 2003 file |
| Xlsx | Represents an Excel 2007 xlsx file |
| Xlsm | Represents an Excel 2007 xlsm file |
| Xltx | Represents an Excel 2007 template xltx file |
| Xltm | Represents an Excel 2007 macro-enabled xltm file |
| Xlsb | Represents an Excel 2007 binary xlsb file |
| Excel2003XML | Represents a Spreadsheet XML file |
| TabDelimited | Represents a Tab Delimited text file |
| ODS | Represents an ODS file |
Opening Microsoft Excel 97 - 2003 Files
To open an existing file of Microsoft Excel 97 - 2003, you can make use of LoadOptions and set the related attribute/options of the LoadOptions class for the template file to be loaded. See the example below.
Example:
//Get the Excel file into stream FileStream stream = new FileStream("e:\\test\\Book1.xls", FileMode.Open); //Instantiate LoadOptions specified by the LoadFormat. LoadOptions loadOptions = new LoadOptions(LoadFormat.Excel97To2003); //Create a Workbook object and opening the file from the stream Workbook wb = new Workbook(stream,loadOptions);
'Get the Excel file into stream
Dim stream As New FileStream("e:\test\Book1.xls", FileMode.Open)
'Instantiate LoadOptions specified by the LoadFormat.
Dim loadOptions As New LoadOptions(LoadFormat.Excel97To2003)
'Create a Workbook object and opening the file from the stream
Dim wb As New Workbook(stream,loadOptions)
Opening Microsoft Excel 2007 Xlsx Files
To open an existing file of Microsoft Excel 2007 format e.g XLSX, XLSB, developers should specify file’s path. You can also make use of LoadOptions and set the related attribute/options of the LoadOptions class for the template file to be loaded. See the example below.
Example:
//Instantiate LoadOptions specified by the LoadFormat. LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx); //Create a Workbook object and opening the file from its path Workbook wb = new Workbook("e:\\test\\Book1.xlsx", loadOptions);
'Instantiate LoadOptions specified by the LoadFormat.
Dim loadOptions As New LoadOptions(LoadFormat.Xlsx)
'Create a Workbook object and opening the file from its path
Dim wb As New Workbook("e:\test\Book1.xlsx", loadOptions)
Opening Files with Different Formats
Aspose.Cells allows developers to open spreadsheet files with different formats such as SpreadsheetML, CSV, Tab Delimited, ODS files. To open such files, developers can use the same methodology as they use for opening files of different Microsoft Excel versions.
Opening SpreadsheetML Files
SpreadsheetML files are the XML representations of your spreadsheets including all information about the spreadsheet such as formatting, formulae etc. Since Microsoft Excel XP, an XML export option is added to Microsoft Excel that exports your spreadsheets to SpreadsheetML files.
Example:
//Instantiate LoadOptions specified by the LoadFormat. LoadOptions loadOptions = new LoadOptions(LoadFormat.SpreadsheetML); //Create a Workbook object and opening the file from its path Workbook wb = new Workbook("e:\\test\\Book1.xml", loadOptions);
'Instantiate LoadOptions specified by the LoadFormat.
Dim loadOptions As New LoadOptions(LoadFormat.SpreadsheetML)
'Create a Workbook object and opening the file from its path
Dim wb As New Workbook("e:\test\Book1.xml", loadOptions)
Opening CSV Files
Comma Separated Values (CSV) files contain records whose values are delimited or separated by commas. In CSV files, data is stored in tablular format that has fields separated by the comma character and quoted by the double quote character. If a field's value contains a double quote character it is escaped with a pair of double quote characters. You can also use Microsoft Excel to export your spreadsheet data to a CSV file.
Example:
//Instantiate LoadOptions specified by the LoadFormat. LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV); //Create a Workbook object and opening the file from its path Workbook wb = new Workbook("e:\\test\\Book1.csv", loadOptions);
'Instantiate LoadOptions specified by the LoadFormat.
Dim loadOptions As New LoadOptions(LoadFormat.CSV)
'Create a Workbook object and opening the file from its path
Dim wb As New Workbook("e:\test\Book1.csv", loadOptions)
Opening Text Files with Custom Separator
Text files are used to contain spreadsheet data but without any formatting. The file is a kind of plain text file that can have some customized delimiters between its data.
Example:
//Instantiate Text File's LoadOptions TxtLoadOptions txtLoadOptions = new TxtLoadOptions(); //Specify the separator txtLoadOptions.Separator = Convert.ToChar("|"); //Specify the encoding type txtLoadOptions.Encoding = System.Text.Encoding.UTF8; //Create a Workbook object and opening the file from its path Workbook wb = new Workbook("e:\\test\\abc.txt", txtLoadOptions);
'Instantiate Text File's LoadOptions Dim txtLoadOptions As New TxtLoadOptions() 'Specify the separator txtLoadOptions.Separator = Convert.ToChar("|") 'Specify the encoding type txtLoadOptions.Encoding = System.Text.Encoding.UTF8 'Create a Workbook object and opening the file from its path Dim wb As New Workbook("e:\test\abc.txt", txtLoadOptions)
Opening Tab Delimited Files
Tab Delimited files are also used to contain spreadsheet data but without any formatting. Data is arranged in rows and columns such as tables and spreadsheets. Shortly, a tab delimited file is a special kind of plain text file with a tab between each column in the text.
Example:
//Instantiate LoadOptions specified by the LoadFormat. LoadOptions loadOptions = new LoadOptions(LoadFormat.TabDelimited); //Create a Workbook object and opening the file from its path Workbook wb = new Workbook("e:\\test\\Book1.txt", loadOptions);
'Instantiate LoadOptions specified by the LoadFormat.
Dim loadOptions As New LoadOptions(LoadFormat.TabDelimited)
'Create a Workbook object and opening the file from its path
Dim wb As New Workbook("e:\test\Book1.txt", loadOptions)
Opening Encrypted Excel Files
We know that it's possible to create encrypted Excel files using Microsoft Excel. To open an existing encrypted file, developers can make use of LoadOptions and set the related attribute/options of the LoadOptions class (e.g give password) for the template file to be loaded. See the example below.
Example:
//Instantiate LoadOptions LoadOptions loadOptions = new LoadOptions(); //Specify the password loadOptions.Password = "007"; //Create a Workbook object and opening the file from its path Workbook wb = new Workbook("e:\\test\\EncryptedBook.xls", loadOptions);
'Instantiate LoadOptions Dim loadOptions As New LoadOptions() 'Specify the password loadOptions.Password = "007" 'Create a Workbook object and opening the file from its path Dim wb As New Workbook("e:\test\EncryptedBook.xls", loadOptions)
Opening File with Data only
To open an existing file with data only, developers can make use of LoadOptions and LoadDataOption classes, set the related attribute/options of the classes for the template file to be loaded. See the example below.
Example:
//Load only specific sheets with data and formulas //Other objects, items etc. would be discarded //Instantiate LoadOptions specified by the LoadFormat LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx); //Set the LoadDataOption LoadDataOption dataOption = new LoadDataOption(); //Specify the sheet(s) in the template file to be loaded dataOption.SheetNames = new string[] { "Sheet2" }; dataOption.ImportFormula = true; //Only data should be loaded. loadOptions.LoadDataOnly = true; //Specify the LoadDataOption loadOptions.LoadDataOptions = dataOption; //Create a Workbook object and opening the file from its path Workbook wb = new Workbook("e:\\test\\Book1.xlsx", loadOptions);
'Load only specific sheets with data and formulas 'Other objects, items etc. would be discarded 'Instantiate LoadOptions specified by the LoadFormat Dim loadOptions As New LoadOptions(LoadFormat.Xlsx) 'Set the LoadDataOption Dim dataOption As New LoadDataOption() 'Specify the sheet(s) in the template file to be loaded dataOption.SheetNames = New String() { "Sheet2" } dataOption.ImportFormula = True 'Only data should be loaded. loadOptions.LoadDataOnly = True 'Specify the LoadDataOption loadOptions.LoadDataOptions = dataOption 'Create a Workbook object and opening the file from its path Dim wb As New Workbook("e:\test\Book1.xlsx", loadOptions)
Important to Know
Aspose.Cells supports Excel file formats from Excel 97 to Excel 2007/2010. But, if you save your Excel file in the following format as shown below in the figure then your Excel file will be failed to open using Aspose.Cells.
Figure: Don't save your Excel files in this format
It is because that in this case, Excel file will contain data in Excel 5.0/95 format, which is not supported by Aspose.Cells. So, please save your Excel file as Microsoft Excel Workbook as shown below:
Figure: Save your Excel files in this format