Save Workbook to Text CSV Format

Introduction

When dealing with data, the format you choose can really determine how easily you can work with it. Among the most common formats for handling tabular data is CSV (Comma-Separated Values). If you’re a developer working with Excel files and need to convert workbooks into CSV format, Aspose.Cells for .NET is a fantastic library that simplifies this task. In this tutorial, we will break down the steps to convert an Excel workbook to a text CSV format seamlessly.

Prerequisites

Before we dive in, let’s ensure you have everything in place to get started:

  1. Basic Knowledge of C# and .NET: Since we’ll be writing code in C#, familiarity with the language and .NET framework is essential.
  2. Aspose.Cells Library: Make sure you have the Aspose.Cells for .NET library installed in your development environment. You can download it here.
  3. Visual Studio or Any C# IDE: You will need an integrated development environment (IDE) to write and execute your code. Visual Studio is a popular choice.
  4. Excel Workbook: Prepare a sample Excel workbook (e.g., “book1.xls”) that contains some data to test the conversion.

Import Packages

Now that we have our prerequisites covered, the first step in the process is to import the necessary packages. In your C# project, you need to include the following namespace at the top of your code file:

using System.IO;
using Aspose.Cells;
using System;

These namespaces will give you access to the classes and methods needed for working with Excel files and managing memory streams.

Step 1: Define the Path to the Documents Directory

The first step in our process is to define where our documents (Excel workbooks) are stored. This is essential as it allows our program to know where to find the files it needs to process.

// The path to the documents directory.
string dataDir = "Your Document Directory";

Make sure to replace "Your Document Directory" with the actual path where your “book1.xls” file resides. This could be a directory on your computer or a path to a server.

Step 2: Load Your Source Workbook

Next, we need to load the Excel workbook that will be converted to CSV format.

// Load your source workbook
Workbook workbook = new Workbook(dataDir + "book1.xls");

The Workbook class from the Aspose.Cells library allows for manipulation and access to Excel workbooks. By passing the file path, we’re loading the specified workbook for processing.

Step 3: Initialize a Byte Array for Workbook Data

Before we start converting the workbook into CSV, we need to initialize an empty byte array that will eventually hold all the worksheet data.

// 0-byte array
byte[] workbookData = new byte[0];

This byte array will combine the data from each worksheet into a single structure that we can write out to a file later.

Step 4: Set Up Text Save Options

Now, let’s set up the options for how we want to save the text format. You can choose custom delimiters or stick with tabs.

// Text save options. You can use any type of separator
TxtSaveOptions opts = new TxtSaveOptions();
opts.Separator = '\t'; // Setting tab as separator

In this example, we’re using a tab character as the separator. You can replace '\t' with any character you wish, like a comma (,), depending on how you want your CSV formatted.

Step 5: Iterate Through Each Worksheet

Next, we’ll iterate through all the worksheets within the workbook, saving each one to our workbookData array, but you must first select which worksheet to work on.

// Copy each worksheet data in text format inside workbook data array
for (int idx = 0; idx < workbook.Worksheets.Count; idx++)
{
    // Save the active worksheet into text format
    MemoryStream ms = new MemoryStream();
    workbook.Worksheets.ActiveSheetIndex = idx;
    workbook.Save(ms, opts);

The loop runs through each worksheet in the workbook. ActiveSheetIndex is set so that each time through the loop, we’re saving the current worksheet. The results will be saved into memory using a MemoryStream.

Step 6: Retrieve Worksheet Data

After saving a worksheet to the memory stream, the next step is to retrieve this data and append it to our workbookData array.

    // Save the worksheet data into sheet data array
    ms.Position = 0; // Reset position of memory stream
    byte[] sheetData = ms.ToArray(); // Get the byte array

ms.Position = 0; resets the position for reading after writing. Then, we use ToArray() to convert the memory stream into a byte array that holds the worksheet data.

Step 7: Combine Worksheet Data

Now, we will combine the data from each worksheet into the single workbookData array initialized earlier.

    // Combine this worksheet data into workbook data array
    byte[] combinedArray = new byte[workbookData.Length + sheetData.Length];
    Array.Copy(workbookData, 0, combinedArray, 0, workbookData.Length);
    Array.Copy(sheetData, 0, combinedArray, workbookData.Length, sheetData.Length);
    workbookData = combinedArray;
}

We create a new array that’s large enough to hold both existing workbook data and new worksheet data. We then copy the existing and new data into this combined array for later use.

Step 8: Save Entire Workbook Data into File

Finally, with all the data combined in our workbookData array, we can save this array to a specified file path.

// Save entire workbook data into file
File.WriteAllBytes(dataDir + "out.txt", workbookData);

WriteAllBytes takes the combined byte array and writes it into a text file named “out.txt” in the specified directory.

Conclusion

And there you have it! You’ve successfully converted an Excel workbook into a CSV format using Aspose.Cells for .NET. Not only is this process efficient, but it allows for easy manipulation of Excel data for further analysis or reporting. Now you can automate your data processing tasks or even integrate this functionality into larger applications.

FAQ’s

Can I use different delimiters for the CSV file?

Yes, you can change the opts.Separator to any character you want, such as commas or pipes.

Is Aspose.Cells free to use?

Aspose.Cells is not free, but you can get a free trial here.

What types of formats can I save to besides CSV?

Aspose.Cells allows saving to multiple formats including XLSX, PDF, and more.

Can I process large Excel files using Aspose.Cells?

Yes, Aspose.Cells is designed to handle large files efficiently, but performance may depend on system resources.

Where can I find more detailed documentation?

You can find comprehensive documentation and examples on their reference site.

Save File in SpreadsheetML Format Save XLS File