Microsoft Excel and text-based (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 market data in format and then import that data into a (which can work cross-platform) that generates a new spreadsheet containing the data in a table with a chart and some trendlines to analyze moving averages. The web application will then return the spreadsheet, which can be opened in any spreadsheet application that reads standard ( ) spreadsheet files. XLSX CSV Bitcoin-US Dollar CSV CSV C# Web Service Application XLSX XLSX Open Object XML OOXML We will use the web service to get the data (follow the link to get your free API key to use in the code examples) and , which has built-in support for importing data, generating a new spreadsheet with tables, charts, etc., and exporting a new using the following steps: AlphaVantage GrapeCity Documents for Excel .NET v5.2 CSV XLSX (using Visual Studio 2022 to create a new project). Create the Project ASP.NET Core Web API (using the web service to get monthly data in format) Query the Data AlphaVantage BTC-USD CSV . (using GrapeCity Documents for Excel.NET API) Load the CSV (rearrange columns, create a table, and create a chart with trendlines). Process the CSV (using GrapeCity Documents for Excel.NET API) Return the XLSX Create the Project Figure 1 Create New ASP.NET Core Web API Project in Visual Studio 2022 Using , create a new project ( ) and select , , and in the drop-downs to quickly find the project type , then select it and click . Visual Studio 2022 CTRL+SHIFT+N C# All Platforms WebAPI ASP.NET Core Web API Next Figure 2 Configure New ASP.NET Core Web API Project in Visual Studio 2022 Type for and select a for the project, then click . BTC_Chart_WebService Project Name Location Next Figure 3 Configure Framework Target Select for the , then click . .NET 6.0 (Long-term-support) Framework Next This will create a template 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 ( ) to rename the Controller file in the project (under ) to : ASP.NET Core WebAPI Solution Explorer CTRL+ALT+L Controllers BTCChartController.cs Figure 4 Under , rename file to Controllers WeatherForecastController.cs BTCChartController.cs When you change the filename, will prompt you and ask if you also want to change all code references in the project – click in the dialog: Visual Studio Yes Figure 5 Rename code references - click here Yes Then select the project file and delete it from the project ( ), then click in the dialog asking you to confirm: WeatherForecast.cs DEL Yes Figure 6 Confirm deleting WeatherForecast.cs from the project Now in ( ), right-click and select : Solution Explorer CTRL+ALT+L Dependencies Manage NuGet Packages Figure 7 Open from Manage NuGet Packages Solution Explorer Dependencies Search for on and install version (or later) in the project. GrapeCity.Documents.Excel nuget.org 5.2.0 Query the Data Now in ( ), select the file for editing, and comment out the unneeded code for related to the weather forecast: Solution Explorer CTRL+ALT+L BTCChartController.cs Summaries 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 web service for the monthly data in format (substituting in the code): AlphaVantage BTC-USD CSV your API key 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&amp;symbol=BTC&amp;market=USD&amp;apikey={API_KEY}&amp;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 method, updating the in the attribute to , and also change the return type to , then comment out the code related to the weather forecast before adding the code to call and import it into the new : Get() Name HttpGet GetBTC-USDChartWorkbook FileContentResult GetCsvData() 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 in the workbook: CSV 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 with the data and rearranges the columns to put the column between the and columns. Then it creates a named containing the data and auto-fits the columns in the . Then the code adds a new in the worksheet of type ( ) over the entire table range, sets the chart title, adds the series to the chart, and then creates three . The trendlines show three-month moving averages for , , and . IWorksheet CSV Volume Date Open Table BTC_Monthly CSV Table Chart StockVOHLC Volume-Open-High-Low-Close Trendlines Volume in blue High in green Low in red Return the XLSX Now copy the following code to save the to and return it from the web service: Workbook XLSX // 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 to Run in Debug F5 BTC_Chart_WebService Open the dropdown and click Try it out: Figure 9 Press to test the web service Try it out Then click Execute, and you should soon see the success result and the link to download the XLSX file: Figure 10 Success result and file download link XLSX The resulting chart looks like this in Excel: Download the for this blog. sample Ready to Try it Out? Download GrapeCity Documents for Excel Today! Also Here

Too Long; Didn't Read

Making security fun one episode at a time

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

Too Long; Didn't Read

MESCIUS inc.

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

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

Too Long; Didn't Read

MESCIUS inc.

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES