Beginner. WPF Export chart + Combo Box values to Excel

Question

Hi, I'm a beginner. I have few combo box called Start date, end date, Part name, Line, Dimension. When I select that and click load button i will get a chart. Now What I wanted to do is export this chart and the combo box values to Excel, when i click button.

I have written code to create image from the chart and save it in my project bin folder. Now I'm not knowing how to export the combo box + saved image to chart.

private void btnExport_Click(object sender, RoutedEventArgs e)
        {
            try
            {
code that creates image from the chart.
                RenderTargetBitmap bm = new RenderTargetBitmap((int)mcChart.ActualWidth, (int)mcChart.ActualHeight, 96, 96, PixelFormats.Default);
                bm.Render(mcChart);
                PngBitmapEncoder enc = new PngBitmapEncoder();
                enc.Frames.Add(BitmapFrame.Create(bm));
                FileStream fs = new FileStream("chart.png", FileMode.Create);
                enc.Save(fs);
                fs.Close();              
               
            }
            catch (Exception ex)
            {
            }
        }

Please help me. It's urgent.

Answer 1

Try this, all controls in WPF are easily cast to visuals.  This code will save them as bitmaps.

void SaveToBmp(FrameworkElement visual, string fileName)
{
    var encoder = new BmpBitmapEncoder();
    SaveUsingEncoder(visual, fileName, encoder);
}

void SaveToPng(FrameworkElement visual, string fileName)
{
    var encoder = new PngBitmapEncoder();
    SaveUsingEncoder(visual, fileName, encoder);
}

void SaveUsingEncoder(FrameworkElement visual, string fileName, BitmapEncoder encoder)
{
    RenderTargetBitmap bitmap = new RenderTargetBitmap(
        (int)visual.ActualWidth,
        (int)visual.ActualHeight,
        96,
        96,
        PixelFormats.Pbgra32);
    bitmap.Render(visual);
    BitmapFrame frame = BitmapFrame.Create(bitmap);
    encoder.Frames.Add(frame);

    using (var stream = File.Create(fileName))
    {
        encoder.Save(stream);
    }
}

---

JP Cowboy Coders Unite!

Answer 2

Thanks for responding. Where should i write this code?

Answer 3

Is this code to save to .png format? I have already done that. What I want to do is the combo box values and the chart to export to excel.

Answer 4

Put it below this : private void btnExport_Click

Then in your main code do this: SaveToPng((visual)combobox,"comboBox1.png")

---

JP Cowboy Coders Unite!

Answer 5

It's is user control where I have written this code. Will this code export chart to Excel? Sorry If i'm wrong

Answer 6

No this code creates a PNG file of the combo box.  To export to Excel you have to use Excel Interop.

http://msdn.microsoft.com/en-us/library/dd264733.aspx

---

JP Cowboy Coders Unite!

Answer 7

Thanks for your response.

using MSExcel = Microsoft.Office.Interop.Excel;

 

 MSExcel.Application xlApp;
                    MSExcel.Workbook xlWorkBook;
                    MSExcel.Worksheet xlWorkSheet;
                    object misValue = System.Reflection.Missing.Value;//used when we don't want to define the values
                    xlApp = new MSExcel.ApplicationClass(); // I have added this, but i'm getting error in this line of code

This is what I'm getting

Answer 8

Try this first:

 var excelApp = new Excel.Application();
    // Make the object visible.
    excelApp.Visible = true;

    // Create a new, empty workbook and add it to the collection returned
    // by property Workbooks. The new workbook becomes the active workbook.
    // Add has an optional parameter for specifying a praticular template.
    // Because no argument is sent in this example, Add creates a new workbook.
    excelApp.Workbooks.Add();

    // This example uses a single workSheet. The explicit type casting is
    // removed in a later procedure.
    Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;

---

JP Cowboy Coders Unite!

Answer 9

Thank you. But how are we calling the saved image chart? I know to save the chart as image.

private void btnExport_Click(object sender, RoutedEventArgs e)
        {
            try
            {
code that creates image from the chart.
                RenderTargetBitmap bm = new RenderTargetBitmap((int)mcChart.ActualWidth, (int)mcChart.ActualHeight, 96, 96, PixelFormats.Default);
                bm.Render(mcChart);
                PngBitmapEncoder enc = new PngBitmapEncoder();
                enc.Frames.Add(BitmapFrame.Create(bm));
                FileStream fs = new FileStream("chart.png", FileMode.Create);
                enc.Save(fs);
                fs.Close();              
               
            }
            catch (Exception ex)
            {
            }
        }

Answer 10

Well I thought you wanted to put the image into Excel right?  First thing is to get sheet opened.

---

JP Cowboy Coders Unite!

Answer 11

okay. Then What should I do?

Answer 12

Step 1 get the excel worksheet up and running first.

Step 2 Put code in like this:

Worksheet worksheet = (Worksheet) workbook.Worksheets["Sheet1"];
 Range cellRange = worksheet.get_Range("A2", "A2");
 string picFilePath = @"d:\sailboat.jpg"; // here use the image that you download to server
 Image picObject = Image.FromFile(picFilePath);
 System.Windows.Forms.Clipboard.SetDataObject(picObject, true);
 worksheet.Paste(cellRange, picObject); 
workbook.Save();
 

---

JP Cowboy Coders Unite!

Answer 13

Thats how you get the image into excel...

---

JP Cowboy Coders Unite!

Answer 14

I'm getting this error when i run in debug mode in this line

var excelApp = new Excel.Application();

Locating source for 'e:\dd\WPFOOB\src\wpfoob\WindowChrome\Microsoft.Windows.Shell\Standard\MessageWindow.cs'. Checksum: MD5 {c2 e2 a8 26 23 f1 3c c9 da 37 8a f9 a9 98 44 11}
The file 'e:\dd\WPFOOB\src\wpfoob\WindowChrome\Microsoft.Windows.Shell\Standard\MessageWindow.cs' does not exist.
Looking in script documents for 'e:\dd\WPFOOB\src\wpfoob\WindowChrome\Microsoft.Windows.Shell\Standard\MessageWindow.cs'...
Looking in the projects for 'e:\dd\WPFOOB\src\wpfoob\WindowChrome\Microsoft.Windows.Shell\Standard\MessageWindow.cs'.
The file was not found in a project.
Looking in directory 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\crt\src\'...
Looking in directory 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\atlmfc\src\mfc\'...
Looking in directory 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\atlmfc\src\atl\'...
Looking in directory 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\atlmfc\include\'...
The debug source files settings for the active solution indicate that the debugger will not ask the user to find the file: e:\dd\WPFOOB\src\wpfoob\WindowChrome\Microsoft.Windows.Shell\Standard\MessageWindow.cs.
The debugger could not locate the source file 'e:\dd\WPFOOB\src\wpfoob\WindowChrome\Microsoft.Windows.Shell\Standard\MessageWindow.cs'.

Answer 15

That error message has nothing to do with Excel. 

There's a file MessageWindow.cs referenced on the e drive which makes no sense to me.   What does that file mean to you?

---

JP Cowboy Coders Unite!

Answer 16

using Excel = Microsoft.Office.Interop.Excel;

This line of code should be at the top of your program....

---

JP Cowboy Coders Unite!

Answer 17

Error    2    The type or namespace name 'Worksheet' could not be found (are you missing a using directive or an assembly reference?)  

Error    4    The name 'workbook' does not exist in the current context   

Error    5    Using the generic type 'System.Windows.Controls.DataVisualization.Range<T>' requires 1 type arguments   

Error    8    'System.Windows.Controls.Image' does not contain a definition for 'FromFile'   

Error    9    The type or namespace name 'Forms' does not exist in the namespace 'System.Windows' (are you missing an assembly reference?)   

Error    10    The name 'workbook' does not exist in the current context   

These are the errors I'm getting

Answer 18

Even I'm not knowing,  because of this I'm not able to run in debug more. I have already added that line of code

Answer 19

Adding the using statement will fix Error 2, 4, and 10.

Error 5 is an ambiguous call to the method RANGE, you have to remove DATAVISUALIATION if you are not using it.

Error 8 is a different problem.

Error 9 means that you don't have a reference to System.Windows.Forms.  But I'm not sure why you need that anyway.

---

JP Cowboy Coders Unite!

Answer 20

For 2 which namespace should I use?

Answer 21

Error    7    Cannot implicitly convert type 'System.Drawing.Image' to 'System.Windows.Controls.Image'   

Answer 22

// This example uses a single workSheet. The explicit type casting is
// removed in a later procedure.
    Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;

    

Worksheet worksheet = (Worksheet) workbook.Worksheets["Sheet1"];
Do we have to add these 2 lines of code of one is enough?

Answer 23

Thanks a ton for helping me out

Answer 24

No prob.. had to leave last night, it was getting too late. 

Is your prb. resolved?  I think you were close.

---

JP Cowboy Coders Unite!

Answer 25

I made few changes to your code and it's working now. Sorry some thing came up.

private MSExcel.Application m_objExcel = null;
        private MSExcel.Workbooks m_objBooks = null;
        private MSExcel._Workbook m_objBook = null;
        private MSExcel.Sheets m_objSheets = null;
        private MSExcel._Worksheet m_objSheet = null;
        private MSExcel.Range m_objRange = null;
        // Frequenty-used variable for optional arguments.
        private object m_objOpt = System.Reflection.Missing.Value;
        private void btnExport_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                var m_objExcel = new MSExcel.Application();
                m_objBooks = (MSExcel.Workbooks)m_objExcel.Workbooks;
                 m_objBook = (MSExcel._Workbook)(m_objBooks.Add(m_objOpt));

                 m_objExcel.Cells[1, 1] = "Start Date"; //The company Name
                 m_objExcel.Cells[1, 2] = "End Date";
                 m_objExcel.Cells[1, 3] = "Part Name";
                 m_objExcel.Cells[1, 4] = "Line Name";
                 m_objExcel.Cells[1, 5] = "Cavity";
                 m_objExcel.Cells[1, 6] = "SubGroup";
                 m_objExcel.Cells[1, 7] = "Dimension";

                 //Put the data in Cell This is the Sales data
                 m_objExcel.Cells[2, 1] = startdate;
                 m_objExcel.Cells[2, 2] = enddate;
                 m_objExcel.Cells[2, 3] = PartNumber;
                 m_objExcel.Cells[2, 4] = line;
                 m_objExcel.Cells[2, 5] = CavityName;
                 m_objExcel.Cells[2, 6] = SubGroup;
                 m_objExcel.Cells[2, 7] = dimenion;
                // Make the object visible.
                m_objExcel.Visible = true;


                // Add data to cells in the first worksheet in the new workbook.
                m_objSheets = (MSExcel.Sheets)m_objBook.Worksheets;
                m_objSheet = (MSExcel._Worksheet)(m_objSheets.get_Item(2));
                 m_objRange = m_objSheet.get_Range("A1", "A2");
                //m_objRange = m_objSheet.get_Range("A1", "A1");

                string imgPath = AppDomain.CurrentDomain.BaseDirectory + "XBarChart.png";
                SaveImage(imgPath, this.mcChart);

                string imgPath1 = AppDomain.CurrentDomain.BaseDirectory + "RBarChart.png";
                SaveImage(imgPath1, mcRangeChart);

                System.Drawing.Image picObject = System.Drawing.Image.FromFile(imgPath);
                System.Windows.Forms.Clipboard.SetDataObject(picObject, true);
                m_objSheet.Paste(m_objRange, picObject);

                System.Drawing.Image picObject1 = System.Drawing.Image.FromFile(imgPath1);
                System.Windows.Forms.Clipboard.SetDataObject(picObject1, true);
                m_objSheet.Paste(m_objRange, picObject1);

                m_objBook.Save();

            }
            catch (Exception ex)
            {
            }
        }

I had one more thing if you can help me it's merging datagrid cells. Can you help me with that?

                                                              

Answer 26

This is my DataGrid.

<my1:DataGrid.CellStyle>
                <Style TargetType="{x:Type my1:DataGridCell}">
                    <Style.Triggers>
                        <Trigger Property="IsKeyboardFocusWithin" Value="true">
                            <Setter Property="Background" Value="Blue" />
                            <Setter Property="Foreground" Value="white" />
                        </Trigger>
                    </Style.Triggers>
                </Style>
            </my1:DataGrid.CellStyle>
        </my1:DataGrid>

private void btnLoad_Click(object sender, RoutedEventArgs e)
        {
              partdetails.ItemsSource = null;
            DataSet partdetailsds = new DataSet();
            oenGetActivePartsResult partelectitem = (oenGetActivePartsResult)cmbpart.SelectedItem;
            int partid = partelectitem.PartID;
            SqlConnection grdCon = null;
            grdCon = new SqlConnection(ConfigurationManager.AppSettings["FCI_OEN"]);
            SqlCommand partdetailsCmd;
            SqlDataAdapter partdetailsAdapter;
            partdetailsCmd = new SqlCommand("oenGetcolumnDimension", grdCon);
            partdetailsCmd.CommandType = CommandType.StoredProcedure;
            partdetailsCmd.Parameters.AddWithValue("@p_PartID", partid);
            partdetailsAdapter = new SqlDataAdapter(partdetailsCmd);
            partdetailsAdapter.Fill(partdetailsds);
            partdetails.ItemsSource = partdetailsds.Tables[0].DefaultView;
            partdetails.Columns[0].IsReadOnly = true;
            partdetails.Columns[1].IsReadOnly = true;
            var srnumber = objDataclass.oengetmaxserialnumberforpart();
            foreach (oengetmaxserialnumberforpartResult snumber in srnumber)
            {
                serialnumber = Int32.Parse(snumber.SerialNumber.ToString());
            }
            
        }

There is a DataGrid and a load button and 2 combo box. When I select values from 2 combo box and press load button, data shgould be displayed in DataGrid which has following fields CavityName, Part Name, Line Name etc. I have to merge CavityName field values ie reapted values I have merge. Can you please help me with this?

Answer 27

You mentioned DataGrid but also have been working on Excel SpreadSheet.

You want to merge data in a datagridcell or merge data in an excel spread sheet cell?

---

JP Cowboy Coders Unite!

Answer 28

This is different module, merge data in datagrid.

Answer 29

Ok you ready for this?

DataGridViews have a ITEMSOURCE property.  You can and should use a CollectionViewSource for that property.  CVS are an abstraction that is nice because it allows you to keep the data but show different views. etc.  In WPF the power is in the Binding, so for a DataGridView that has a Binding whose ItemsSource is a CollectionViewSource that holds for example a collection<ofSomeType> you can then bind "ofSometype" paths to the columns of the datagridview.  This line of code shows how to bind the path of item_number to a datagridview text column.  What it doesn't show you is path of what?  The answer is the path contained within the ItemsSource property of the Datagridview...  Assume that the ItemsSource is an ObservableCollection<Product>, then this column below shows the Product.item_number field.  But as this shows only the item_number is shown.

 <DataGridTextColumn x:Name="item_numberColumn" Binding="{Binding Path=item_number}" Header="item number" Width="SizeToHeader" />

WPF Binding changes how data is displayed by using this Datacontext/path model.  So let's say you need to combine columns.  How do you do it?  You create a new field in your class that is the combination of what you need.

public class Product
{
    public string item_number { get; set; }
    public string item_name { get; set; }
    //combine the two to get desired effect
    public string item_name_number
    {
        get
        {
            return item_name + ":" + item_number;
        }
    }
}

Now all you have to do is this:

 <DataGridTextColumn x:Name="item_numberColumn" Binding="{Binding Path=item_name_number}" Header="item number" Width="SizeToHeader" />

Simple elegant and fast!!!!!!

---

JP Cowboy Coders Unite!

Answer 30

Thank you so much.. Will try and let you know for sure. I don't know how to thank you.. You are excellent helper.

Answer 31

public class CavityName
{
    public string cavity_name { get; set; }
    //combine the two to get desired effect
    public string cavity_name
    {
        get
        {
            return cavity_name;
        }
    }
}

<DataGridTextColumn x:Name="cavity_name" Binding="{Binding Path=cavity_name}" Header="item number" Width="SizeToHeader" />

Will this bind similar name rows?

Like the one showed in this link. Here country & State they have merged. I wanted just one field to be merged. Will this accomplish this? Sorry if i'm asking too much

http://wpf.codeplex.com/discussions/57669?ProjectName=wpf

Answer 32

DataGrid Values I'm retrieving from database. Now when i wrote this code, I'm getting 2 columns with same name one retieved frm database & other frm the code which we included and the column is empty

Answer 33

In the codeplex solution they are using a Grouping option for displaying the data, Grouping alters the look of the data vertically, what I showed alters the look of the data horizontally.

public class CavityName
{

    public string part_name {get;set;}
    public string cavity_name { get; set; }
    //combine the two to get desired effect
    public string cavity_name_partname
    {
        get
        {
            return cavity_name + part_name


        }
    }
}

---

JP Cowboy Coders Unite!

Answer 34

Thanks you.I'm looking for vertical grouping like the link where they did for state & country. How can we do that?.

Answer 35

http://blog.smoura.com/wpf-toolkit-datagrid-part-iv-templatecolumns-and-row-grouping/

http://wpftutorial.net/DataGrid.html#grouping

http://www.c-sharpcorner.com/uploadfile/dpatra/grouping-in-datagrid-in-wpf/

---

JP Cowboy Coders Unite!

Answer 36

Thanks. Will try this and let you know.