Opening Files with Different Formats

Using Aspose.Cells you can open files with different formats. Aspose.Cells can open a range of file formats such as Microsoft Excel spreadsheets (XLS, XLSX, XLSM, XLSB), SpreadsheetML, Comma-separated values (CSV), Tab Delimited or Tab-separated values (TSV) files etc.

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

Opening Files with Different Formats

Aspose.Cells allows developers to open spreadsheet files with different formats such as SpreadsheetML, Comma-separated values (CSV), Tab Delimited or Tab-separated values (TSV), 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 XML representations of spreadsheets including all information about it, 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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
 // Opening SpreadsheetML Files
// Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions3 = new LoadOptions(LoadFormat.SpreadsheetML);

// Create a Workbook object and opening the file from its path
Workbook wbSpreadSheetML = new Workbook(dataDir + "Book3.xml", loadOptions3);
Console.WriteLine("SpreadSheetML file opened successfully!");

Opening HTML Files

Aspose.Cells allows you to open HTML file into Workbook object. The HTML file should Microsoft Excel oriented i.e MS-Excel should be able to open it.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);


string filePath = dataDir + "Book1.html";

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

// Create a Workbook object and opening the file from its path
Workbook wb = new Workbook(filePath, loadOptions);
// Save the MHT file
wb.Save(filePath + "output.xlsx");

Opening CSV Files

Comma Separated Values (CSV) files contain records where the values are separated by commas. Data is stored as a table where each column is separated by the comma character and quoted by the double quote character. If a field value contains a double quote character it is escaped with a pair of double quote characters. You can also use Microsoft Excel to export spreadsheet data to CSV.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions4 = new LoadOptions(LoadFormat.Csv);

// Create a Workbook object and opening the file from its path
Workbook wbCSV = new Workbook(dataDir + "Book_CSV.csv", loadOptions4);
Console.WriteLine("CSV file 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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
            
var filename = sourceDir + "[20180220142533][ASPOSE_CELLS_TEST].csv";

//Load CSV file
var workbook = new Workbook(filename, new TxtLoadOptions() { Separator = ';', LoadFilter = new LoadFilter(LoadDataFilterOptions.CellData), CheckExcelRestriction = false, ConvertNumericData = false, ConvertDateTimeData = false });

Console.WriteLine(workbook.Worksheets[0].Name); // (20180220142533)(ASPOSE_CELLS_T
Console.WriteLine(workbook.Worksheets[0].Name.Length); // 31
Console.WriteLine("CSV file opened successfully!");

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 preferred parser.  

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

samplePreferredParser.csv

outputsamplePreferredParser.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
class TextParser : ICustomParser
{
    public object ParseObject(string value)
    {
        return value;
    }
    public string GetFormat()
    {
        return "";
    }
}

class DateParser : ICustomParser
{
    public object ParseObject(string value)
    {
        DateTime myDate = DateTime.ParseExact(value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
        return myDate;
    }
    public string GetFormat()
    {
        return "dd/MM/yyyy";
    }
}

public static void Main()
{
    // Initialize Text File's LoadFormat
    LoadFormat oLoadFormat = LoadFormat.Csv;

    // Initialize Text File's Load options
    TxtLoadOptions oTxtLoadOptions = new TxtLoadOptions(oLoadFormat);

    // Specify the separatot character
    oTxtLoadOptions.Separator = Convert.ToChar(",");

    // Specify the encoding scheme
    oTxtLoadOptions.Encoding = System.Text.Encoding.UTF8;

    // Set the flag to true for converting datetime data
    oTxtLoadOptions.ConvertDateTimeData = true;

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

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

    // Get the first cell
    Cell oCell = oExcelWorkBook.Worksheets[0].Cells["A1"];

    // Display type of value
    Console.WriteLine("A1: " + oCell.Type.ToString() + " - " + oCell.DisplayStringValue);

    // Get the second cell
    oCell = oExcelWorkBook.Worksheets[0].Cells["B1"];

    // Display type of value
    Console.WriteLine("B1: " + oCell.Type.ToString() + " - " + oCell.DisplayStringValue);

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

    Console.WriteLine("OpeningCSVFilesWithPreferredParser executed successfully.\r\n");
}

Opening Text Files with Custom Separator

Text files are used to hold spreadsheet data without formatting. The file is a kind of plain text file that can have some customized delimiters.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir + "Book11.csv";

// 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(filePath, txtLoadOptions);

// Save file
wb.Save(dataDir+ "output.txt");

Opening Tab Delimited Files

Tab delimited (Text) file contains spreadsheet data but without any formatting. Data is arranged in rows and columns like in tables and spreadsheets. Basically, a tab delimited file is a special kind of plain text file with a tab between each column.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
            
 // Opening Tab Delimited Files
// Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions5 = new LoadOptions(LoadFormat.TabDelimited);

// Create a Workbook object and opening the file from its path
Workbook wbTabDelimited = new Workbook(dataDir + "Book1TabDelimited.txt", loadOptions5);
Console.WriteLine("Tab delimited file opened successfully!");

Opening Tab-Separated Values (TSV) Files

Tab-separated values (TSV) file contains spreadsheet data but without any formatting. It is the same with Tab Delimited file where data is arranged in rows and columns like in tables and spreadsheets.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();

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

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

// Using the Sheet 1 in Workbook
Worksheet worksheet = workbook.Worksheets[0];

// Accessing a cell using its name
Cell cell = worksheet.Cells["C3"];

Console.WriteLine("Cell Name: " + cell.Name + " Value: " + cell.StringValue);

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
//Source directory
string sourceDir = RunExamples.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.Worksheets[0];

// Accessing a cell using its name
Cell cell = worksheet.Cells["C3"];

Console.WriteLine("Cell Name: " + cell.Name + " Value: " + cell.StringValue);

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
//Source directory
string sourceDir = RunExamples.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);

Console.WriteLine("FODS file opened successfully!");