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 can work cross-platform) 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 GrapeCity Documents for Excel .NET, which has built-in support for importing CSV data, generating a new spreadsheet with tables, charts, etc., and exporting a new XLSX using the following steps:

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 GrapeCity Documents for Excel.NET API)
4. Process the CSV (rearrange columns, create a table, and create a chart with trendlines).
5. Return the XLSX (using GrapeCity Documents for Excel.NET API)

Ready to Try it Out? Download GrapeCity Documents 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.

Figure 1 Create New ASP.NET Core Web API Project in Visual Studio 2022

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

Figure 2 Configure New ASP.NET Core Web API Project in Visual Studio 2022

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.

Figure 3 Configure Framework Target

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.

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

Figure 4 Under Controllers, rename WeatherForecastController.cs file 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:

Figure 5 Rename code references - click Yes here

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

Figure 6 Confirm deleting WeatherForecast.cs from the project

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

Figure 7 Open Manage NuGet Packages from Solution Explorer Dependencies

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

Query the Data

Now, in 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;
}

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 GrapeCity.Documents.Excel is automatically added to the BTCChartController.cs file. Otherwise, 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.

Then 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:

Figure 8 Press F5 to Run BTC_Chart_WebService in Debug

Open the dropdown and click Try it out:

Figure 9 Press Try it out to test the web service

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

Figure 10 Success result and XLSX file download link

The resulting chart looks like this in Excel:

Download the sample for this blog.

Ready to Try it Out? Download GrapeCity Documents for Excel Today!