How to save Excel Table as a Picture using vb.net?

Question

I'm trying to save tables from excel sheets as pictures. Is there a way to just put that table on the clipboard and save it? This is what I've got so far but the library referenced is not there?

Thank you in advance!

-Rueben Ramirez

Public Sub extract_excelTable(ByRef data_file As String, ByRef app1 As excel.Application, ByRef sheet_name As String)
    'defining new app to prevent out of scope open applications
    Dim temp_app As excel.Application = app1
    Dim workbook As excel.Workbook = temp_app.Workbooks.Open(Path.GetFullPath(data_file))
    temp_app.Visible = False
    For Each temp_table As excel.DataTable In workbook.Worksheets(sheet_name)
        temp_table.Select()
        'temp_app.Selection.CopyAsPicture? 
    Next
End Sub

I don't know if the `DataTable` object has this, but the `Range` object beneath the DataTable has `CopyPicture` - You could do something along the lines of `rng.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap)` — John Bustos, Feb 18 '16 at 21:38
The DataTable object is used in Charts; what you want to do is iterate over the Worksheet.ListObjects collection. A ListObject has a Range property that you can then use the method described by @JohnBustos to copy as a bitmap. — TnTinMn, Feb 18 '16 at 21:52
Thank you everyone for the responses! I referenced the ListObject collection that @TnTinMn suggested above. — Rueben.Ramirez, Feb 22 '16 at 14:26

score 0 · Answer 1 · edited May 23 '17 at 11:59

I'm not going to write any code here, but I will outline a solution for you that will work. Note that this will not reproduce the formatting of the excel document, just simply get the data from it, and put it on an image in the same column/row order as the excel file.

STEP 1: My solution to this problem would be to read the data from the excel file using an OLEDB connection as outlined in the second example of this post: Reading values from an Excel File

Alternatively, you may need to open the document in excel and re-save it as a CSV if it's too large to fit in your computer's memory. I have some code that reads a CSV into a string list in C# that may help you:

    static void Main(string[] args)
    {
        string Path = "C:/File.csv";

        System.IO.StreamReader reader = new System.IO.StreamReader(Path);

        //Ignore the header line
        reader.ReadLine();
        string[] vals;

        while (!reader.EndOfStream)
        {
            ReadText = reader.ReadLine();
            vals = SplitLine(ReadText);

            //Do some work here
        }
    }


    private static string[] SplitLine(string Line)
    {
        string[] vals = new string[42];
        string Temp = Line;

        for (int i = 0; i < 42; i++)
        {
            if (Temp.Contains(","))
            {
                if (Temp.Substring(0, Temp.IndexOf(",")).Contains("\""))
                {
                    vals[i] = Temp.Substring(1, Temp.IndexOf("\",", 1) - 1);

                    Temp = Temp.Substring(Temp.IndexOf("\",", 1) + 2);
                }
                else {
                    vals[i] = Temp.Substring(0, Temp.IndexOf(","));

                    Temp = Temp.Substring(Temp.IndexOf(",") + 1);
                }
            }
            else
            {
                vals[i] = Temp.Trim();
            }
        }

        return vals;
    }

STEP 2: Create a bitmap object to create an image, then use a for loop to draw all of the data from the excel document onto the image. This post had an example of using the drawstring method to do so: how do i add text to image in c# or vb.net

How to save Excel Table as a Picture using vb.net?

1 Answers1