How to Programmatically Convert CSV to Excel XLSX in C# .NET

Microsoft Excel XLSX and text-based CSV (comma-separated values) are common file formats for data interchange, and applications can significantly benefit from implementing support for reading and writing these file formats. In this example, we will download the latest monthly Bitcoin-US Dollar market data in CSV format and then import that CSV data into a C# Web Service Application (which has cross-platform support) that generates a new XLSX spreadsheet containing the data in a table with a chart and some trendlines to analyze moving averages. The web application will then return the XLSX spreadsheet, which can be opened in any spreadsheet application that reads standard Open Object XML (OOXML) spreadsheet files.

We will use the AlphaVantage web service to get the data (follow the link to get your free API key to use in the code examples) and Document Solutions for Excel, .NET, which has built-in support for importing CSV data, generating a new spreadsheet with tables, charts, trendlines, and more. Finally, we will export a new XLSX file.

Steps to Convert CSV to XLSX using C# .NET:

1. Create the Project (using Visual Studio 2022 to create a new ASP.NET Core Web API project).
2. Query the Data (using the AlphaVantage web service to get monthly BTC-USD data in CSV format).
3. Load the CSV (using the Document Solutions for Excel, .NET API).
4. Process the CSV (rearrange columns, create a table, and create a chart with trendlines).
5. Return the XLSX (using the Document Solutions for Excel, .NET API).

Ready to Try It Out? Download Document Solutions for Excel Today!

Create the Project

Using Visual Studio 2022, create a new project (CTRL+SHIFT+N) and select C#, All Platforms, and WebAPI in the drop-downs to quickly find the project type ASP.NET Core Web API, then select it and click Next.

Type BTC_Chart_WebService for Project Name and select a Location for the project, then click Next.

Select .NET 6.0 (Long-term-support) or later for the Framework. With the default values selected for the other configurations in the dialog, click Next.

This will create a template ASP.NET Core WebAPI project, which contains sample code to return a weather forecast. We won't need that in our project, but we can reuse and repurpose the Controller file.

Use the Solution Explorer (CTRL+ALT+L) to rename the Controller file in the project (under Controllers) to BTCChartController.cs:

When you change the filename, Visual Studio will prompt you and ask if you also want to change all code references in the project – click Yes in the dialog:

Then select the project file WeatherForecast.cs and delete it from the project (DEL), then click Yes in the dialog asking you to confirm:

Now, in Solution Explorer (CTRL+ALT+L), right-click Dependencies and select Manage NuGet Packages:

Search for DS.Documents.Excel on NuGet.org and install the latest version (7.1.4 or later) in the project.

Query the Data

Now, in the Solution Explorer (CTRL+ALT+L), select the file BTCChartController.cs for editing, and comment out the code for Summaries related to the weather forecast:

BTCChartController

public class BTCChartController : ControllerBase
{
    //    private static readonly string[] Summaries = new[]
    //    {
    //    "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
    //};

Now add the following code below the commented code above to query the AlphaVantage web service for the BTC-USD monthly data in CSV format (substituting your API key in the code):

GetCSVData

// Get the CSV data from the AlphaVantage web service
private string GetCsvData()
{
    string csv;
    string API_KEY = "YOUR_KEY_HERE";
    string QUERY_URL = $"https://www.alphavantage.co/query?function=DIGITAL_CURRENCY_MONTHLY&symbol=BTC&market=USD&apikey={API_KEY}&datatype=csv";
    Uri queryUri = new Uri(QUERY_URL);
    using (HttpClient client = new HttpClient())
    {
        Task<string> t = client.GetStringAsync(queryUri);
        while (!t.IsCompleted)
            t.Wait();
        csv = t.Result;
    }
    return csv;
}

Be sure to replace the API_KEY variable with your unique API key. 

Load the CSV

Now, replace the code for the Get() method with the following snippet. This will:

• update the Name in the HttpGet attribute to GetBTC-USDChartWorkbook
• change the return type to FileContentResult
• comment out the code related to the weather forecast
• add the code to call GetCsvData() and import it into the new Workbook

BTCChartController.Get

[HttpGet(Name = "GetBTC-USDChartWorkbook")]
//public IEnumerable<WeatherForecast> Get()
public FileContentResult Get()
{
    //return Enumerable.Range(1, 5).Select(index => new WeatherForecast
    //{
    //    Date = DateTime.Now.AddDays(index),
    //    TemperatureC = Random.Shared.Next(-20, 55),
    //    Summary = Summaries[Random.Shared.Next(Summaries.Length)]
    //})
    //.ToArray();
 
    // first get CSV data
    string csv = GetCsvData();
 
    // create new workbook
    IWorkbook wbk = new Workbook();
    // open CSV data in GcExcel using MemoryStream
    using (Stream s = new MemoryStream())
    {   // convert to byte array using UTF8 encoding
        byte[] arr = System.Text.Encoding.UTF8.GetBytes(csv.ToCharArray());
        s.Write(arr);
        s.Seek(0, SeekOrigin.Begin);
        // open CSV in workbook
        wbk.Open(s, OpenFileFormat.Csv);
    }

Note: Assembly reference for DS.Documents.Excel is automatically added to the BTCChartController.cs file. If there is no assembly reference, then you must add it manually.

Process the CSV

Next, copy the following code (after the using block in the previous code snippet) to process the CSV in the workbook: 

BTCChartController.Get (cont)

// get the worksheet with the CSV data
IWorksheet wks = wbk.Worksheets[0];

// move volume to column B, before open/high/low/close
// it works better to create the StockVOHLC chart with
// the series in the correct order (date-volume-open-high-low-close)
wks.Range["B:B"].Insert();
wks.Range["K:K"].Copy(wks.Range["B:B"]);
wks.Range["K:K"].Delete();

// get the range containing the CSV data
IRange used = wks.UsedRange;

// create a new Table for the CSV data named "BTC_Monthly"
ITable tbl = wks.Tables.Add(used, true);
tbl.Name = "BTC_Monthly";
// auto-fit the columns in the table to show all the cell values
used.AutoFit();

// add the StockVOHLC chart shape over the table (the table is completely beneath the chart)
IShape shape = wks.Shapes.AddChart(ChartType.StockVOHLC, 0, 0, used.Width, used.Height);
// get the IChart from the shape
IChart chart = shape.Chart;
// set the chart title
chart.ChartTitle.Text = "BitCoin Monthly Open-High-Low-Close-Volume";
// add the chart series (the first 6 columns in the table)
chart.SeriesCollection.Add(wks.Range[0, 0, used.RowCount, 6], RowCol.Columns, true, true);
// change category axis base to months
IAxis categoryAxis = chart.Axes.Item(AxisType.Category);
categoryAxis.BaseUnit = TimeUnit.Months;
// change category axis tick label orientation and number format
categoryAxis.TickLabels.Orientation = 45;
categoryAxis.TickLabels.NumberFormat = "d/m/yyyy";

// add trend line for Volume series in Blue
ITrendline voltrend = chart.SeriesCollection[0].Trendlines.Add();
voltrend.Name = "3 Month Moving Avg VOL";
voltrend.Type = TrendlineType.MovingAvg;
voltrend.Period = 3;
voltrend.Format.Line.Color.RGB = Color.Blue;
voltrend.Format.Line.DashStyle = LineDashStyle.RoundDot;
// add trend line for High series in Green
ITrendline hightrend = chart.SeriesCollection[2].Trendlines.Add();
hightrend.Name = "3 Month Moving Avg HIGH";
hightrend.Type = TrendlineType.MovingAvg;
hightrend.Period = 3;
hightrend.Format.Line.Color.RGB = Color.Green;
hightrend.Format.Line.DashStyle = LineDashStyle.RoundDot;
// add trend line for Low series in Red
ITrendline lowtrend = chart.SeriesCollection[3].Trendlines.Add();
lowtrend.Name = "3 Month Moving Avg LOW";
lowtrend.Type = TrendlineType.MovingAvg;
lowtrend.Period = 3;
lowtrend.Format.Line.Color.RGB = Color.Red;
lowtrend.Format.Line.DashStyle = LineDashStyle.RoundDot;

First, the code gets the IWorksheet with the CSV data and rearranges the columns to put the Volume column between the Date and Open columns. Then, it creates a Table named BTC_Monthly containing the CSV data and auto-fits the columns in the Table.

Next, the code adds a new Chart in the worksheet of type StockVOHLC (Volume-Open-High-Low-Close) over the entire table range, sets the chart title, adds the series to the chart, changes the category axis unit to Months, updates the category axis tick label orientation & number format, and then creates three Trendlines. The trendlines show three-month moving averages for Volume in blue, High in green, and Low in red.

Return the XLSX

Finally, copy the following code after the previous code to save the Workbook to XLSX and return it from the web service:

    // Save Workbook to XLSX and return from web service as "BTC_Chart.xlsx"
    using (MemoryStream ms = new MemoryStream())
    {
        wbk.Save(ms, SaveFileFormat.Xlsx);
        ms.Seek(0, SeekOrigin.Begin);
        byte[] bytes = ms.ToArray();
        return File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "BTC_Chart.xlsx");
    }
} // Get()

Now, the project is complete and ready to run! Press F5 to run in debug, and you can try it out:

Open the dropdown and click "Try it out":

Then click "Execute," and you should soon see the success result and the link to download the XLSX file:

The resulting chart looks like this in Excel:

Download the sample for this blog.

Ready to Try It Out? Download Document Solutions for Excel Today!