how to expoert data to excel in wpf

Question

Hi,

   I am using stackpanel with multiple textboxes tht form like a datagrid.in this i hve data. now i hve to import data from stackpanel text boxes to excel on button click in wpf..can anyone plese help me out..

Answer 1

The interop is the same as ASP.Net:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317719
http://articles.techrepublic.com.com/5100-10878_11-5815515.html#

The best implementation would be to create a class that holds the TextBox values and use it to populate the Excel worksheet.

---

Bigsby, Lisboa, Portugal - O que for, quando for, é que será o que é... http://bigsby.eu

Answer 2

Hi krish.v.n

 

Here are some more resources on how to achieve the goal. When you master the Office automation API, it will be easy for you to achieve this kind of functionality.

 

 

http://www.codeproject.com/KB/aspnet/ExportClassLibrary.aspx

http://www.codeproject.com/KB/grid/exceldatagridview.aspx

http://www.codeproject.com/KB/cs/WriteDataToExcel.aspx?msg=2611993

http://www.codeproject.com/KB/vb/Senthil_S__Software_Eng_.aspx

http://www.codeproject.com/KB/dotnet/ExportToExcel.aspx

 

The code for exporting data to excel can also be applied in WPF, if you find anything difficult to implement, please feel free to contact us.

 

Best regards,

Bruce Zhou

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

hi,

 Thanks for your reply. i need to export data from stackpanel to excel..in c#.pls help me..

With Regards,

 

krishna

Answer 4

The links above contains sample code with C# language. If you still have difficulty,  please let me know the XAML code for the Stackpanel. Maybe I could write the code demonstrating how to achieve that.

Best regards,
Bruce Zhou

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 5

hi bruce,

I am sending xaml codea nand code behind..

xaml---

<Grid Height="388" Name="grid1" Width="858">

            <ScrollViewer ScrollViewer.HorizontalScrollBarVisibility="Visible" ScrollViewer.VerticalScrollBarVisibility="Visible">

                <WrapPanel Name="wptxt1" >

                </WrapPanel>

            </ScrollViewer>

        </Grid>

Codebehind..

        public void prepareChart(System.Data.DataTable fpcdt, String StartDate, String[] Weekly, string[] Monthly, string[] Yealy, string[] Custom)

        {

            try

            {

                DateTime sstime = Convert.ToDateTime("01/01/1901 " + "09:00:00 AM");

                cbiDurationTime = (ComboBoxItem)cbBand.SelectedValue;

                int Duration = Convert.ToInt32(cbiDurationTime.Tag.ToString());

                DataTable dt = new DataTable("Tab");

                // dt.Clear();

                dt.Columns.Add("Time");

                timeFrames.Clear();

                wptxt1.Children.Clear();

                //For Frames 

                for (int i = 0; i < 1440 / Duration; i++)

                {

                    timeFrames.Add(sstime.ToLongTimeString());

                    sstime = sstime.AddMinutes(Duration);

                }

                // For SrartTime and Enditme

                for (int s = 0; s < fpcdt.Rows.Count; s++)

                {

                    string[] FromTime = new string[2];

                    FromTime = fpcdt.Rows[s].ItemArray[8].ToString().Split(' ');

                    if (!timeFrames.Contains(Convert.ToDateTime(FromTime[1]+ " " + FromTime[2])))

                        AddedDates.Add(Convert.ToDateTime("01/01/1901 " + (FromTime[1] + " " + FromTime[2])));

                    string[] EndTime = new string[2];

                    EndTime = fpcdt.Rows[s].ItemArray[9].ToString().Split(' ');

                    if (!timeFrames.Contains(Convert.ToDateTime(EndTime[1] + " " + EndTime[2])))

                        AddedDates.Add(Convert.ToDateTime("01/01/1901 " + (EndTime[1] + " " + EndTime[2])));

                   

                }

                AddedDates.Sort();

                // For Remove Duplicates

                for (int i = 0, j; i < AddedDates.Count; ++i)

                {

                    j = i + 1;

                    while (j < AddedDates.Count)

                    {

                        if (AddedDates[j] == AddedDates[i])

                        {

                            AddedDates.RemoveAt(j);

                        }

                        else

                        {

                            ++j;

                        }

                    }

                }

                int FrameCount = 0;

                int FramesCount = timeFrames.Count;

                // To insert start dates and enddate in betweenn time frames

                if (AddedDates.Count > 0)

                {

                    for (int TF = 0; TF < FramesCount; TF++)

                    {

                        if (AddedDates.Count > 0)

                        {

                            for (int AddFrameCount = 0; AddFrameCount < AddedDates.Count; AddFrameCount++)

                            {

                                if (Convert.ToDateTime("01/01/1901 " + timeFrames[TF]) < AddedDates[AddFrameCount] && Convert.ToDateTime("01/01/1901 " + timeFrames[TF + 1]) > AddedDates[AddFrameCount])

                                {

                                    timeFrames.Insert(TF + 1, AddedDates[AddFrameCount].ToLongTimeString());

                                }

                            }

                        }

                    }

                }

                // For Add rows to datatable

                for (int i = 0; i < timeFrames.Count; i++)

                {

                    dt.Rows.Add();

                    dt.Rows[dt.Rows.Count - 1][0] = timeFrames[i].ToString();

                }

                DateTime date = System.DateTime.Now;

                // For Particular Day

                if (Weekly == null && Monthly == null && Yealy == null && Custom == null)

                {

                    // Add Columns to table

                    dt.Columns.Add(StartDate);

                }

                // For Weekly report

                else if (StartDate == null && Monthly == null && Yealy == null && Custom == null)

                {

                    // Add Columns to table

                    for (int i = 0; i < 7; i++)

                    {

                        if (Weekly[i] != null)

                            dt.Columns.Add(Weekly[i]);

                        else

                            break;

                    }

                }

                // For Monthly Report

                else if (StartDate == null && Weekly == null && Yealy == null && Custom == null)

                {

                    // Add Columns to table

                    for (int i = 0; i < 31; i++)

                    {

                        if (Monthly[i] != null)

                            dt.Columns.Add(Monthly[i]);

                        else

                            break;

                    }

                }

                // For Yearly Report

                else if (StartDate == null && Weekly == null && Monthly == null && Custom == null)

                {

                    // Add Columns to table

                    for (int i = 0; i < 365; i++)

                    {

                        if (Yealy[i] != null)

                            dt.Columns.Add(Yealy[i]);

                        else

                            break;

                    }

                }

                // For Custom Report

                else if (StartDate == null && Weekly == null && Monthly == null && Yealy == null)

                {

                    // Add Columns to table

                    for (int i = 0; i < Custom.Length; i++)

                    {

                        if (Custom[i] != null)

                            dt.Columns.Add(Custom[i]);

                        else

                            break;

                    }

                }

                //  dgFPC.DataContext = dt.DefaultView;

                System.DateTime CurrentDate = System.DateTime.Now;

                for (int i = 0; i <= 1; i++)

                {

                    var stackpanel = new StackPanel();

                    stackpanel.Name = "sp_" + i.ToString();

                    for (int j = 0; j < timeFrames.Count; j++)

                    {

                        var txt = new TextBox();

                        txt.Name = "txt_" + j.ToString() + "_" + i.ToString();

                        if (i == 0 && j == 0)

                        {

                            txt.Text = "Time";

                            txt.Background = new SolidColorBrush(Colors.Orange);

                            txt.IsReadOnly = true;

                        }

                        else if (i == 0 && j > 0)

                        {

                            txt.Text = timeFrames[j].ToString();

                            //MessageBox.Show(timeFrames[j].ToString());

                            txt.Background = new SolidColorBrush(Colors.Silver);

                            txt.IsReadOnly = true;

                        }

                        else if (i > 0 && j == 0)

                        {

                            txt.Text = "8-18-2009";

                            txt.Background = new SolidColorBrush(Colors.Orange);

                            txt.IsReadOnly = true;

                        }

                        txt.Width = 100;

                        txt.Height = 22;

                        stackpanel.Children.Add(txt);

                    }

                    wptxt1.Children.Add(stackpanel);

                }

                // To display data in datagrid

                for (int i = 0; i <= fpcdt.Rows.Count - 1; i++)

                {

                    string ScheduleDate = Convert.ToDateTime(fpcdt.Rows[i]["ScheduleDate"].ToString()).ToShortDateString();

                    string ProgramName = fpcdt.Rows[i]["ProgramName"].ToString() + " - " + Convert.ToString(Convert.ToInt64(fpcdt.Rows[i]["ProgramDuration"].ToString()) / 60);

                    string StartTime = Convert.ToDateTime(fpcdt.Rows[i]["ScheduleTime"].ToString()).ToLongTimeString();

                    string EndTime = Convert.ToDateTime(fpcdt.Rows[i]["EndTime"].ToString()).ToLongTimeString();

                    string ProgramTypeName = fpcdt.Rows[i]["ProgramTypeName"].ToString();

                    string programtypeuid = fpcdt.Rows[i]["ProgramUId"].ToString();

                    // Rows loop

                    for (int j = 0; j < timeFrames.Count; j++)

                    {

                        string ColumnTime = timeFrames[j].ToString();

                        dt.Rows[j].ItemArray[0].ToString();

                        //Columns loop////////////////////////////////////////////////////////////////////////

                        for (int k = 1; k <= 1; k++)

                        {

                            ///////////////////////////////////////////////////////////////////////////////////

                            String ColumnDate = "8-18-2009";

                            if (ColumnDate == ScheduleDate.Replace("/", "-"))

                            {

                                if (ColumnTime == StartTime)

                                {

                                    foreach (StackPanel sp in wptxt1.Children)

                                    {

                                        foreach (TextBox tbC in sp.Children)

                                        {

                                            string s = tbC.Name;

                                            if ("txt_" + j.ToString() + "_" + k.ToString() == s)

                                            {

                                                tbC.Text = ProgramName;

                                                tbC.IsReadOnly = true;

                                            }

                                        }

                                    }

                                    dt.Rows[j][k] = ProgramTypeName;

                                    // For Change Background color

                                    for (int r = j; r < dt.Rows.Count; r++)

                                    {

                                        if (timeFrames[r].ToString() == EndTime)

                                            break;

                                        else

                                        {

                                            foreach (StackPanel sp in wptxt1.Children)

                                            {

                                                foreach (TextBox tbC in sp.Children)

                                                {

                                                    string s = tbC.Name;

                                                    if ("txt_" + r.ToString() + "_" + k.ToString() == s)

                                                    {

                                                        tbC.IsReadOnly = true;

                                                        tbC.Background = Brushes.Green;

                                                        tbC.BorderThickness = new Thickness(0);

                                                    }

                                                }

                                            }

                                        }

                                    }

                                }

                            }

                        }

                    }

                }

                //  dgFPC.DataContext = dt.DefaultView;

                // ds.Dispose();

            }

            catch (Exception e) { MessageBox.Show(e.Message); }

        }

with this code we prepare chart with data..this data have to be export to excel..

Answer 6

Hi krish.v,

The code you provides lacks of the definitions of some members, so I can't not write corresponding code based on yours. However, I write a simple program to demonstrate how to export the datatable to the Excel using C#.

    public partial class Window1 : Window
    {
        DataTable dt = new DataTable();
        object misValue = System.Reflection.Missing.Value;
        public Window1()
        {
            InitializeComponent();
            dt.Columns.Add("AA", typeof(string));
            dt.Columns.Add("BB", typeof(string));
            dt.Rows.Add("11", "22");
            dt.Rows.Add("33", "44");
            
            this.dataGrid1.ItemsSource = ((IListSource)dt).GetList();
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            #region
            Microsoft.Office.Interop.Excel.Application app=null;
            Microsoft.Office.Interop.Excel.Workbook wb = null;
            Microsoft.Office.Interop.Excel.Worksheet ws=null;
            try
            {
                app = new Microsoft.Office.Interop.Excel.Application();
                wb = app.Workbooks.Add(misValue);
                ws = app.ActiveWorkbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
                Microsoft.Office.Interop.Excel.Range range;

                range = ws.get_Range("A2", misValue);
                range = range.get_Resize(dt.Rows.Count, dt.Columns.Count);

                Microsoft.Office.Interop.Excel.Range columnNameRange;
                columnNameRange = ws.get_Range("A1", misValue);
                columnNameRange = columnNameRange.get_Resize(1, dt.Columns.Count);

                string[] columNames = new string[dt.Columns.Count];

                for(int i=0;i<dt.Columns.Count;i++)
                    columNames[i] = dt.Columns[i].ColumnName;
                columnNameRange.set_Value(misValue, columNames);

                string[,] arr = new string[dt.Rows.Count, dt.Columns.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        arr[i, j] = dt.Rows[i][j].ToString();
                    }
                }
                range.set_Value(misValue, arr);
                wb.Close(true, "D:\\Test.xlsx", misValue);
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message.ToString());
            }
            finally
            {
                // make sure the office process quit after certain operations done.
                if (app != null)
                {
                    app.UserControl = false;
                    app.Quit();
                    app = null;
                }
            }
            #endregion
        }
    }

To use the above code, you may need to import the corresponding version of office interop assemblies. You may find this KB article very helpful if you have difficulty in setting up the necessary things.


Best regards,
Bruce Zhou

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 7

Hi Bruce,

 Thanks for ur reply..i will do in this way..

with regards,

krishna

Answer 8

Ok, if you find anything difficult to implement, don't hesitate to contact me.

Best regard,
Bruce Zhou

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 9

Hi kirsh.v.n,


Is your problem solved? Do let us know if there's any difficulty.


Best regards,
Bruce Zhou

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 10

Hi Bruce,

    I have done with your code . one more thing is that i have a popup in my form.while runnig the project popup is at the top of the screen..i want to minimise the popup to overcome this..is there any function to minimise popup in xaml with c#.And Thanks for your caring..

With Regards,

krishna.

Answer 11

Hi krish.v.n,

You can try to toggle the popup's Visibility property, there's no Minimize property or method could make it minimized like a window.

By the way, is it another problem? If so, you may need to start a new thread.


Best regards,
Bruce Zhou

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 12

Hi

This code does not require reference Office libraries and end opens Microsoft Excel:

      try
      {
        if (this.Contactos == null || this.Contactos.Count == 0)
          return;

        StringBuilder sb = new StringBuilder();
        //
        // Titulos
        foreach (var item in typeof(ConsultarContactoSesionesResult).GetProperties())
          sb.AppendFormat(CultureInfo.CurrentUICulture, "{0}\t", item.Name);
        sb.AppendLine();

        //
        // Contenido
        foreach (var item in this.Contactos)
        {
          foreach (var propiedad in typeof(ConsultarContactoSesionesResult).GetProperties())
            sb.AppendFormat(CultureInfo.CurrentUICulture, "{0}\t", propiedad.GetValue(item, null));

          sb.AppendLine();
        }
        //
        // Grabar Archivo
        string path = string.Format("{0}\\Contactos_{1:yyyyMMdd}.xls",
                    Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
                    DateTime.Today);
        File.WriteAllText(path, sb.ToString());
        //
        // Abrir Excel
        ProcessStartInfo obInfo = new ProcessStartInfo(path);
        obInfo.UseShellExecute = true;
        obInfo.WindowStyle = ProcessWindowStyle.Normal;

        using (Process obJob = new Process())
        {
          obJob.StartInfo = obInfo;
          obJob.Start();
        }
      }
      catch (Exception ex)
      {
        Internas.MostrarError(ex);
      }

 

Answer 13

Hi, you should switch from stackpanel with multiple textboxes to DataGrid control.

Lets say your WPF app is MVVM app. Then you could have property DataTable in your view-model.

You would then bind DataTable.DefaultView to DataGrid.ItemsSource property.

Also, your view-model would have delegate or relay command that would be bound to some button.

When user clicks that button, delegate from your delegate command would execute.

Code in that delegate could look like this (code uses this Excel C# / VB.NET library):

var ef = new ExcelFile();

// Insert data table in worksheet, starting from worksheet's first row and column and include column headers
      ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);

ef.SaveXls(dataTable.TableName + ".xls");