-1

I have a scenario, where i have a storage Blob which will have the Excel file, so in code level there is no Physical File path i have, so its Stream of file i will get the code. I need to Convert the same to CSV & push it back to the storage.

Tried below:-

  1. Tried with Microsoft.Office.Interop.Excel

    Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
// Open Excel Workbook for conversion.
Excel.Workbook excelWorkbook = app.Workbooks.Open(sourceFile);
// Save file as CSV file.
excelWorkbook.SaveAs(destinationFile, Excel.XlFileFormat.xlCSV);

Issue:- in the SourcePath , i don't have a physical location, and moreover there is no overload seems to take Byte or stream of file.

  1. Tried https://github.com/youngcm2/CsvHelper.Excel , Demo code as follows.

    using var reader = new CsvReader(new ExcelParser(FileContent, "JOB STATUSES", new CsvConfiguration(CultureInfo.CurrentCulture)));

Tried Below code even:-

using var parser = new ExcelParser(FileContent,CultureInfo.InvariantCulture); using var reader = new CsvReader(parser);

But here the ExcelParser is failing with Corrupterdfile with a valid CSV :(

Issue:- Here although there is a OverLoad to pass the Stream but is critical in my case. As there is no specific file format i have. It can be any Random EXCEL file. There no Specific class i can define.

I am missing something , can anyone help on this.

Scenario in my case:-

  • No Physical path to the File location . it's in Storage account, so Stream/Byte .
  • EXCEL File can be of any number of rows or columns no Fixed Model i can have but single sheet.
lokanath das
  • 736
  • 1
  • 10
  • 35
  • Office interop can only run on the local machine as you need office installed, etc. This just isn't going to work. It's not designed for use on a web server – Liam Feb 18 '21 at 11:21
  • If you just stream the file out to the output stream that should send it to the client, you don't need office to stream files – Liam Feb 18 '21 at 11:22
  • Yes that's right . you mean, stream of File & convert the content-type & push back with .csv? – lokanath das Feb 18 '21 at 11:22
  • Does this answer your question? [How to return a file (FileContentResult) in ASP.NET WebAPI](https://stackoverflow.com/questions/26038856/how-to-return-a-file-filecontentresult-in-asp-net-webapi) – Liam Feb 18 '21 at 11:23
  • Nope in my case its a conversion from one format to another then stream conversion then to send. – lokanath das Feb 18 '21 at 11:29
  • @lokanathdas do you have a real Excel file? Or a CSV or HTML table with a fake extension? What is `FileContent`? – Panagiotis Kanavos Feb 18 '21 at 12:39
  • FileContent is Stream of an Excel File. – lokanath das Feb 18 '21 at 12:46

1 Answers1

0

Use ExcelDataReader. It's available in NuGet.

        using (var reader = ExcelReaderFactory.CreateReader(memoryStream, excelConfig))
        {
            var spreadsheet = reader.AsDataSet();
            var table = spreadsheet.Tables[0];
            var csv = table.ToCSV();
            var bytes = Encoding.UTF8.GetBytes(csv);

            return new StreamDataSource(bytes, table.TableName);
        }


public static class TableExtension
{
    public static string ToCSV(this DataTable dtDataTable)
    {
        var builder = new StringBuilder();
        foreach (DataRow dr in dtDataTable.Rows)
        {
            for (int i = 0; i < dtDataTable.Columns.Count; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                    string value = dr[i].ToString();
                    if (value.Contains(","))
                    {
                        value = string.Format("\"{0}\"", value);
                        builder.Append(value);
                    }
                    else
                    {
                        builder.Append(dr[i].ToString());
                    }
                }
                if (i < dtDataTable.Columns.Count - 1)
                {
                    builder.Append(",");
                }
            }
            builder.Append(Environment.NewLine);
        }

        var csv = builder.ToString();
        return csv;
    }
}
Gustav
  • 129
  • 4