How to Programmatically Convert CSV to Excel XLSX in C# .NET
Tags: devops, webdev, csharp, tutorial
Published: 2023-11-07 20:46:09

---
canonical_url: https://developer.mescius.com/blogs/how-to-programmatically-convert-csv-to-excel-xlsx-in-c-net
description: Learn how to programmatically convert CSV to Excel XLSX documents in C# .NET. See more and build your application today.
---
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](https://www.alphavantage.co/support/#api-key)** 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**](#create) (using Visual Studio 2022 to create a new **ASP.NET Core Web API** project).
2.  [**Query the Data**](#query)(using the **AlphaVantage** web service to get monthly **BTC-USD** data in **CSV** format)**.**
3.  [**Load the CSV**](#load) (using GrapeCity Documents for Excel.NET API)
4.  [**Process the CSV**](#process) (rearrange columns, create a table, and create a chart with trendlines).
5.  [**Return the XLSX**](#return) (using GrapeCity Documents for Excel.NET API)

### <a id="create"></a>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**.

![](//cdn.mescius.io/umb/media/o25mq4pp/01-vs2022-create-new-project.png?width=500&height=332.6771653543307)

_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**.

![](//cdn.mescius.io/umb/media/s3qfgopb/02-vs2022-config-new-project.png?width=500&height=332.6771653543307)

_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**.

![](//cdn.mescius.io/umb/media/kjqfzfp0/03-vs2022-new-project-info.png?width=500&height=332.6771653543307)

_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**_:

![](//cdn.mescius.io/umb/media/qwfl2oyh/04-rename-controller.png?width=318&height=264)

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

![](//cdn.mescius.io/umb/media/dwnmotbo/05-rename-code-references.png?width=438&height=161)

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

![](//cdn.mescius.io/umb/media/dpdmmjxs/06-delete-project-file.png?width=351&height=153)

_Figure 6 Confirm deleting WeatherForecast.cs from the project_

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

![](//cdn.mescius.io/umb/media/t4vfiym1/07-manage-nuget-packages.png?width=299&height=320)

_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.

### <a id="query"></a>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](https://www.alphavantage.co/support/#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;
    }
```

### <a id="load"></a>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.

### <a id="process"></a>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**.

### <a id="return"></a>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:

![](//cdn.mescius.io/umb/media/vyfn1ik1/08-run-debug-swagger-chrome.png?width=500&height=379.19463087248323)

_Figure 8 Press **F5** to Run **BTC_Chart_WebService** in Debug_

Open the dropdown and click Try it out:

![](//cdn.mescius.io/umb/media/4pbhnp2l/09-open-dropdown-try-it-out.png?width=500&height=379.19463087248323)

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

![](//cdn.mescius.io/umb/media/zchn2aiv/10-execute-results-download-file.png?width=425.7142857142857&height=500)

_Figure 10 Success result and **XLSX** file download link_

The resulting chart looks like this in Excel:

![Convert CSV to XLSX C#](//cdn.mescius.io/umb/media/3wugymif/newresultschart-2.png?rmode=max&width=758&height=521)

Download the [sample](https://cdn.mescius.io/assets/developer/blogs/gcdocuments/20231017-how-to-programmatically-convert-csv-to-excel-xlsx-in-c-sharp-net/BTC_Chart_WebService.zip) for this blog.