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

Question

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

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

Answer 1

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.

Answer 2

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

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.