Importing, Exporting CSV and Excel XLSX in .NET C# Applications

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 v5.2, 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)

Create the Project

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

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 2 Configure 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 3 Configure Framework Target

Select .NET 6.0 (Long-term-support) for the Framework, then 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. 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 version 5.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 unneeded 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

//    private static readonly string[] Summaries = new[]
//    {
//    "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
//};
// 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 copy the following code into the Get() method, updating the Name in the HttpGet attribute to GetBTC-USDChartWorkbook, and also change the return type to FileContentResult, then comment out the code related to the weather forecast before adding 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);
    }

Process the CSV

Now copy the following code 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);
// 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, 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

Now copy the following 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()
} // BTCChartController

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!

Also Here