How to import excel data include embedded object using asp.net c#

Ajay Kalidindi 1 Reputation point
2022-06-11T07:38:43.363+00:00

Hi,

I have an excel file and one cell of every row contain embed pdf file object and remaining cells contain string values. Excel file looks like click here

I want to save this excel file data in sql database table, I can read the cell data of Date, Store, Category, Cost, Total, Method columns using oledb provider or using Workbook and Worksheet classes and save it in database table fine.

but coming to the Receipt column it showing null value when reading from either oledb or worksheet,

1) I want to save embedded object in local folder row wise and get filepath

2) Insert excel row data and for 'Receipt' column I need to save filepath

I will show this data in grid, for Receipt column I will set hyperlink and give filepath, so user can view the pdf that saved of that row.

How to implement using "Microsoft.Office.Interop.Excel" namespace and "OLEObject" class.

Any ideas please share it will helpful, I dont want to use third pary assembly references like GemBox, Aspose, etc.

Thanks,

Ajay

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
7,036 questions
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
1,212 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Karen Payne MVP 29,236 Reputation points MVP
    2022-06-11T13:49:17.703+00:00

    The correct approach is to store information in the database tables then when a request is made, create the receipt as a PDF on the fly (done with or without a pre-done template). How you implement on the fly PDF creation is up to you via a free library or paid for library. Also, when storing information in the database be mindful of edits which can be handled via roles table level to start.


  2. Ajay Kalidindi 1 Reputation point
    2022-06-11T19:14:03.527+00:00

    Hi karenpayneoregon, If we ask client to add file name and excel like

    click here

    I have wrote code for save pdf files using System.IO.Packaging;

        public void ExtractPdf(string excelPath, string destinationDirectory)    
        {    
            using (var package = Package.Open(excelPath))    
            {    
                int i = 1;    
                foreach (var part in package.GetParts())    
                {    
                    if (part.ContentType == "application/vnd.openxmlformats-officedocument.oleObject")    
                    {    
                        // PDF data is embedded into OLE Object package part.    
                        var pdfContent = GetPdfContent(part.GetStream());    
                        if (pdfContent != null)    
                            File.WriteAllBytes(Path.Combine(destinationDirectory, "EmbeddedPdf" + (i++) + ".pdf"), pdfContent);    
                    }    
                }    
            }    
        }    
    
        private byte[] GetPdfContent(Stream stream)    
        {    
            // Every PDF file/data starts with '%PDF' and ends with '%%EOF'.    
            const string pdfStart = "%PDF", pdfEnd = "%%EOF";    
    
            byte[] bytes = ConvertStreamToArray(stream);    
    
            string text = Encoding.ASCII.GetString(bytes);    
    
            int startIndex = text.IndexOf(pdfStart, StringComparison.Ordinal);    
            if (startIndex < 0)    
                return null;    
    
            int endIndex = text.LastIndexOf(pdfEnd, StringComparison.Ordinal);    
            if (endIndex < 0)    
                return null;    
    
            var pdfBytes = new byte[endIndex + pdfEnd.Length - startIndex];    
            Array.Copy(bytes, startIndex, pdfBytes, 0, pdfBytes.Length);    
    
            return pdfBytes;    
        }    
    
        private static byte[] ConvertStreamToArray(Stream stream)    
        {    
            var buffer = new byte[16 * 1024];    
            using (var ms = new MemoryStream())    
            {    
                int read;    
                while ((read = stream.Read(buffer, 0, buffer.Length)) > 0)    
                    ms.Write(buffer, 0, read);    
    
                return ms.ToArray();    
            }    
        }    
    

    If we save the pdf with embedded object file name, then problem will solve.. Is any way to get the file name of embedded object? when "File.WriteAllBytes"

    Thank,
    Ajay.