Opening Files with Different Formats

Developers use of Aspose.Cells to open files for different purposes. For example, open a file to retrieve data from it, or use a pre-defined designer spreadsheet file to speed up your development process. Aspose.Cells allows developers to open different kinds of source files. These source files can be Microsoft Excel reports, SpreadsheetML, Comma-separated values (CSV), Tab Delimited or Tab-separated values (TSV) files. This article discusses opening these different source files using Aspose.Cells.

If you need to know all supported file formats, please refer to the following pages: Supported File Formats

Simple Ways to Open Excel Files

Opening through Path

To open a Microsoft Excel file using the file path, pass the path of the file as a parameter while creating the instance of the Workbook class. The following sample code demonstrates opening an Excel file using the file path.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(OpeningFilesThroughPath.class) + "files/";

// Opening from path.
// Creating an Workbook object with an Excel file path
Workbook workbook1 = new Workbook(dataDir + "Book1.xlsx");

// Print message
System.out.println("Workbook opened using path successfully.");

Opening through Stream

Sometimes, the Excel file that you want to open is stored as a stream. In that case, similar to opening a file using the file path, pass the stream as a parameter while instantiating the Workbook class. The following sample code demonstrates opening an Excel file using stream.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(OpeningFilesThroughStream.class) + "loading_saving/";

// Opening workbook from stream
// Create a Stream object
FileInputStream fstream = new FileInputStream(dataDir + "Book2.xls");

// Creating an Workbook object with the stream object
Workbook workbook2 = new Workbook(fstream);

fstream.close();

// Print message
System.out.println("Workbook opened using stream successfully.");

Opening Files of Different Microsoft Excel Versions

User may use the LoadOptions class to specify the format of the Excel file using the LoadFormat enumeration.

The LoadFormat enumeration contains many pre-defined file formats some of which are given below.

Format Types	Description
Csv	Represents a CSV file
Excel97To2003	Represents an Excel 97 - 2003 file
Xlsx	Represents an Excel 2007/2010/2013/2016/2019 and Office 365 XLSX file
Xlsm	Represents an Excel 2007/2010/2013/2016/2019 and Office 365 XLSM file
Xltx	Represents an Excel 2007/2010/2013/2016/2019 and Office 365 template XLTX file
Xltm	Represents an Excel 2007/2010/2013/2016/2019 and Office 365 macro-enabled XLTM file
Xlsb	Represents an Excel 2007/2010/2013/2016/2019 and Office 365 binary XLSB file
SpreadsheetML	Represents a SpreadsheetML file
Tsv	Represents a Tab-separated values file
TabDelimited	Represents a Tab Delimited text file
Ods	Represents an ODS file
Html	Represents an HTML file
Mhtml	Represents an MHTML file

Opening Microsoft Excel 95/5.0 Files

To open Microsoft Excel 95 files, instantiate the Workbook instance with the path or stream of the template file. Sample file to test the code can be downloaded from the following link:

Excel95_5.0.xls

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Opening Microsoft Excel 97 Files
// Creating an EXCEL_97_TO_2003 LoadOptions object

// Creating an Workbook object with excel 97 file path and the
// loadOptions object
new Workbook(srcDir + "Excel95_5.0.xls");

Opening Microsoft Excel 97 or later versions XLS Files

To open XLS files of Microsoft Excel XLS 97 or later versions, instantiate the Workbook instance with the path or stream of the template file. Or use the LoadOptions method and select the EXCEL_97_TO_2003 value in the LoadFormat enumeration.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(OpeningMicrosoftExcel972003Files.class) + "loading_saving/";

// Opening Microsoft Excel 97 Files
// Createing and EXCEL_97_TO_2003 LoadOptions object
LoadOptions loadOptions1 = new LoadOptions(LoadFormat.EXCEL_97_TO_2003);

// Creating an Workbook object with excel 97 file path and the
// loadOptions object
Workbook workbook3 = new Workbook(dataDir + "Book_Excel97_2003.xls", loadOptions1);

// Print message
System.out.println("Excel 97 Workbook opened successfully.");

Opening Microsoft Excel 2007 or later versions XLSX Files

To open XLSX files of Microsoft Excel 2007 or later versions, instantiate the Workbook instance with the path or stream of the template file. Or use the LoadOptions class and select the XLSX value in the LoadFormat enumeration.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(OpeningMicrosoftExcel2007XlsxFiles.class) + "loading_saving/";

// Opening Microsoft Excel 2007 XLSX Files. Createing and XLSX LoadOptions object
LoadOptions loadOptions2 = new LoadOptions(LoadFormat.XLSX);

// Creating an Workbook object with 2007 xlsx file path and the loadOptions object
Workbook workbook4 = new Workbook(dataDir + "Book_Excel2007.xlsx", loadOptions2);

// Print message
System.out.println("Excel 2007 Workbook opened successfully.");

Opening Files with Different Formats

Aspose.Cells allows developers to open spreadsheet files with different formats such as SpreadsheetML, CSV, Tab Delimited 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.

To open SpreadsheetML files, use the LoadOptions class and select the SPREADSHEET_ML value in the LoadFormat enumeration.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(OpeningSpreadsheetMLFiles.class) + "loading_saving/";

// Opening SpreadsheetML Files
// Creating and EXCEL_2003_XML LoadOptions object
LoadOptions loadOptions3 = new LoadOptions(LoadFormat.SPREADSHEET_ML);

// Creating an Workbook object with SpreadsheetML file path and the
// loadOptions object
Workbook workbook5 = new Workbook(dataDir + "Book3.xml", loadOptions3);

// Print message
System.out.println("SpreadSheetML format workbook has been opened successfully.");

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 a tabular 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.

To open CSV files, use the LoadOptions class and select the CSV value, predefined in the LoadFormat enumeration.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(OpeningCSVFiles.class) + "loading_saving/";
// Opening CSV Files
// Creating and CSV LoadOptions object
LoadOptions loadOptions4 = new LoadOptions(LoadFormat.CSV);

// Creating an Workbook object with CSV file path and the loadOptions
// object
Workbook workbook6 = new Workbook(dataDir + "Book_CSV.csv", loadOptions4);

// Print message
System.out.println("CSV format workbook has been opened successfully.");

Opening CSV files and replacing invalid characters

In Excel, when CSV file with special characters is opened, the characters are automatically replaced. The same is done by Aspose.Cells API which is demonstrated in the code example given below.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
//Source directory
String dataDir = Utils.getSharedDataDir(OpeningCSVFilesAndReplacingInvalidCharacters.class) + "LoadingSavingConvertingAndManaging/";
        
LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);

//Load CSV file
Workbook workbook = new Workbook(dataDir + "[20180220142533][ASPOSE_CELLS_TEST].csv", loadOptions);

System.out.println(workbook.getWorksheets().get(0).getName()); // (20180220142533)(ASPOSE_CELLS_T
System.out.println(workbook.getWorksheets().get(0).getName().length()); // 31
System.out.println("CSV file opened successfully!");

Opening CSV files using preferred parser

This is not always necessary to use default parser settings for opening the CSV files. Sometimes importing CSV file does not create expected output like date format is not as expected or empty fields are handled differently. For this purpose TxtLoadOptions.PreferredParsers is available to provide own preferred parser to parse different data types as per the requirement. Following sample code demonstrates the usage of the preferred parser.  

Sample source file and output files can be downloaded from the following links for testing this feature.

samplePreferredParser.csv

outputsamplePreferredParser.xlsx

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
class TextParser implements ICustomParser
{
	@Override
	public Object parseObject(String s) {
		return s;
	}

	@Override
	public String getFormat() {
		return "";
	}
}

class DateParser implements ICustomParser {
	@Override
	public Object parseObject(String s) {
		Date myDate = null;
		SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
		try {
			myDate = formatter.parse(s);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return myDate;
	}

	@Override
	public String getFormat() {
		return "dd/MM/yyyy";
	}
}

public class OpeningCSVFilesWithPreferredParser {

	//Source directory
	private static String sourceDir = Utils.Get_SourceDirectory();
	private static String outputDir = Utils.Get_OutputDirectory();

	public static void main(String[] args) throws Exception {

		// Initialize Text File's Load options
		TxtLoadOptions oTxtLoadOptions = new TxtLoadOptions(LoadFormat.CSV);

		// Specify the separatot character
		oTxtLoadOptions.setSeparator(',');

		// Specify the encoding scheme
		oTxtLoadOptions.setEncoding(Encoding.getUTF8());

		// Set the flag to true for converting datetime data
		oTxtLoadOptions.setConvertDateTimeData(true);

		// Set the preferred parsers
		oTxtLoadOptions.setPreferredParsers(new ICustomParser[] { new TextParser(), new DateParser() });

		// Initialize the workbook object by passing CSV file and text load options
		Workbook oExcelWorkBook = new Workbook(sourceDir + "samplePreferredParser.csv", oTxtLoadOptions);

		// Get the first cell
		Cell oCell = oExcelWorkBook.getWorksheets().get(0).getCells().get("A1");

		// Display type of value
		System.out.println("A1: " + getCellType(oCell.getType()) + " - " + oCell.getDisplayStringValue());

		// Get the second cell
		oCell = oExcelWorkBook.getWorksheets().get(0).getCells().get("B1");

		// Display type of value
		System.out.println("B1: " + getCellType(oCell.getType()) + " - " + oCell.getDisplayStringValue());

		// Save the workbook to disc
		oExcelWorkBook.save(outputDir + "outputsamplePreferredParser.xlsx");

		System.out.println("OpeningCSVFilesWithPreferredParser executed successfully.\r\n");
	}

	private static String getCellType(int type){
		if(type == CellValueType.IS_STRING){
			return "String";
		} else if(type == CellValueType.IS_NUMERIC){
			return "Numeric";
		} else if(type == CellValueType.IS_BOOL){
			return "Bool";
		} else if(type == CellValueType.IS_DATE_TIME){
			return "Date";
		} else if(type == CellValueType.IS_NULL){
			return "Null";
		} else if(type == CellValueType.IS_ERROR){
			return "Error";
		} else{
			return "Unknown";
		}
	}

Opening TSV(Tab Delimited) Files

Tab-delimited files 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.

To open tab-delimited files, developers should use the LoadOptions class and select the TSV value, predefined in the LoadFormat enumeration.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(OpeningTabDelimitedFiles.class) + "loading_saving/";

// Creating and TAB_DELIMITED LoadOptions object
LoadOptions loadOptions5 = new LoadOptions(LoadFormat.TSV);

// Creating an Workbook object with Tab Delimited text file path and the
// loadOptions object
Workbook workbook7 = new Workbook(dataDir + "Book1TabDelimited.txt", loadOptions5);

// Print message
System.out.println("Tab Delimited workbook has been opened successfully.");

Opening Encrypted Excel Files

We know that it’s possible to create encrypted Excel files using Microsoft Excel. To open such encrypted files, developers should call a special overloaded LoadOptions method and select the DEFAULT value, predefined in the FileFormatType enumeration. This method would also take the password for the encrypted file as shown below in the example.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(OpeningEncryptedExcelFiles.class) + "loading_saving/";

// Opening Encrypted Excel Files
// Creating and EXCEL_97_TO_2003 LoadOptions object
LoadOptions loadOptions6 = new LoadOptions(LoadFormat.EXCEL_97_TO_2003);

// Setting the password for the encrypted Excel file
loadOptions6.setPassword("1234");

// Creating an Workbook object with file path and the loadOptions object
Workbook workbook8 = new Workbook(dataDir + "encryptedBook.xls", loadOptions6);

// Print message
System.out.println("Encrypted workbook has been opened successfully.");

Aspose.Cells also supports opening password-protected MS Excel 2013 files.

There are fair chances that the Workbook constructor may throw System.OutOfMemoryException while loading large spreadsheets. This exception suggests that the available memory is insufficient to completely load the spreadsheet into the memory, therefore, the spreadsheet has to be loaded while enabling the Memory Preferences.

Opening SXC Files

StarOffice Calc is similar to Microsoft Excel and supports formulas, charts, functions, and macros. The spreadsheets created with this software are saved with the SXC extension. The SXC file is also used for OpenOffice.org Calc spreadsheet files. Aspose.Cells can read SXC files as demonstrated by the following code sample.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the source directory.
String sourceDir = Utils.Get_SourceDirectory();

// Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.SXC);

// Create a Workbook object and opening the file from its path
Workbook workbook = new Workbook(sourceDir + "SampleSXC.sxc", loadOptions);
      
// Using the Sheet 1 in Workbook
Worksheet worksheet = workbook.getWorksheets().get(0);

// Accessing a cell using its name
Cell cell = worksheet.getCells().get("C3");

System.out.println("Cell Name: " + cell.getName() + " Value: " + cell.getStringValue());

Opening FODS Files

FODS file is spreadsheet saved in OpenDocument XML without any compression. Aspose.Cells can read FODS files as demonstrated by the following code sample.

Example

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the source directory.
String sourceDir = Utils.Get_SourceDirectory();

// Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.FODS);

// Create a Workbook object and opening the file from its path
Workbook workbook = new Workbook(sourceDir + "SampleFods.fods", loadOptions);

// Print message
System.out.println("FODS file opened successfully!");

Advance topics

• Filter Defined Names while loading Workbook
• Filter Objects while loading Workbook or Worksheet
• Get Warnings while Loading Excel File
• Keep Separators for Blank Rows while exporting spreadsheets to CSV format
• Load Workbook with specified Printer Paper Size
• Opening Different Microsoft Excel Versions Files
• Optimizing Memory Usage while Working with Big Files having Large Datasets
• Read Numbers Spreadsheet Developed by Apple Inc. using Aspose.Cells
• Reading CSV File with Multiple Encodings
• Stop conversion or loading using InterruptMonitor when it is taking too long
• Using LightCells API