Read Excel files dynamically, not depending on rows, and write json c#

Question

I'm trying to generate a JSON file from Excel files. I have different Excel files and I would like to read them and generate a JSON file. I imagine it must be quite easy, but I'm having some trouble.

Ok, so I read this link using Excel reader tool, as this is what my leader says we should use. I tried following this link https://www.hanselman.com/blog/ConvertingAnExcelWorksheetIntoAJSONDocumentWithCAndNETCoreAndExcelDataReader.aspx I always get the readTimeout and writeTimeout error. Also, it never reads my Excel. It always writes null on my JSON document.

        public static IActionResult GetData(
                [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
                ILogger log)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

            var inFilePath = "C:\\Users\\a\\Desktop\\exelreader\\Wave.xlsx";
            var outFilePath = "C:\\Users\\a\\Desktop\\exelreader\\text.json";

            using (var inFile = File.Open(inFilePath, FileMode.Open, FileAccess.Read))
            using (var outFile = File.CreateText(outFilePath))
            {
                using (var reader = ExcelReaderFactory.CreateReader(inFile, new ExcelReaderConfiguration()
                { FallbackEncoding = Encoding.GetEncoding(1252) }))
                using (var writer = new JsonTextWriter(outFile))
                {
                    writer.Formatting = Formatting.Indented; //I likes it tidy
                    writer.WriteStartArray();
                    reader.Read(); //SKIP FIRST ROW, it's TITLES.
                    do
                    {
                        while (reader.Read())
                        {
                            //peek ahead? Bail before we start anything so we don't get an empty object
                            var status = reader.GetString(1);
                            if (string.IsNullOrEmpty(status)) break;

                            writer.WriteStartObject();
                            writer.WritePropertyName("Source");
                            writer.WriteValue(reader.GetString(1));

                            writer.WritePropertyName("Event");
                            writer.WriteValue(reader.GetString(2));

                            writer.WritePropertyName("Campaign");
                            writer.WriteValue(reader.GetString(3));

                            writer.WritePropertyName("EventDate");
                            writer.WriteValue(reader.GetString(4));

                            //writer.WritePropertyName("FirstName");
                            //writer.WriteValue(reader.GetString(5).ToString());

                            //writer.WritePropertyName("LastName");
                          //writer.WriteValue(reader.GetString(6).ToString());
                            writer.WriteEndObject();
                        }
                    } while (reader.NextResult());
                    writer.WriteEndArray();
                }
            }
            //never mind this return
            return null;
        }

Can anybody give some help on this matter. The idea is to read the first row of my Excel files as headers and then the other rows as values, so I can write the JSON.

Answer 1

For converting excel data to json, you could try read excel data as dataset and then serialize the dataset to json.

Try code below:

public async Task<IActionResult> ConvertExcelToJson()
{
    var inFilePath = @"xx\Wave.xlsx";
    var outFilePath = @"xx\text.json";

    using (var inFile = System.IO.File.Open(inFilePath, FileMode.Open, FileAccess.Read))
    using (var outFile = System.IO.File.CreateText(outFilePath))
    {
        using (var reader = ExcelReaderFactory.CreateReader(inFile, new ExcelReaderConfiguration()
        { FallbackEncoding = Encoding.GetEncoding(1252) }))
        {
            var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            });
            var table = ds.Tables[0];
            var json = JsonConvert.SerializeObject(table, Formatting.Indented);
            outFile.Write(json);
        }
    }
    return Ok();
}

For AsDataSet, install package ExcelDataReader.DataSet, if you got any error related with Encoding.GetEncoding(1252), configure the code below in Startup.cs

System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

Reference: ExcelDataReader