Export Listview to Excel (.xls) from a list populated by LINQ, WPF project (C#)?

Asked

Viewed 478 times

0

Good...

I need to export the Listview data populated by the LINQ method of an SQL database... I’m not getting the Column Headers in Excel... Anyone can help?

Reference: https://www.youtube.com/watch?v=_OV0PnMVeZY

Export Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;//-----> Referência
using System.Data;
using System.Windows.Controls;
using System.Windows;
using TRSSystem.AcessoDados;

namespace TRSSystem
{
    class ExportToExcel
    {
        //Exportar ListView para um Arquivo Excel
        //Referência: https://www.youtube.com/watch?v=_OV0PnMVeZY
        public void ExportarListViewToExcel_TabPrdTRS (ListView ltv)
        {
            try
            {
                Microsoft.Win32.SaveFileDialog arquivo = new Microsoft.Win32.SaveFileDialog();
                arquivo.Filter = "Excel (*.xls)|*.xls";
                arquivo.FileName = "ArquivoExportado";

                if (arquivo.ShowDialog() == true)
                {
                    Microsoft.Office.Interop.Excel.Application app;
                    Microsoft.Office.Interop.Excel.Workbook work_book;
                    Microsoft.Office.Interop.Excel.Worksheet work;

                    app = new Microsoft.Office.Interop.Excel.Application();
                    work_book = app.Workbooks.Add();
                    work = (Microsoft.Office.Interop.Excel.Worksheet)work_book.Worksheets.get_Item(1);

                    //Lendo a ListView
                for (int i = 0; i < ltv.Items.Count; i++)
                {                      
                    tabPrdTR dados = (tabPrdTR)ltv.Items[i];
                    work.Cells[i + 1, 1] = dados.DataPrd;
                    work.Cells[i + 1, 2] = dados.OP;
                    work.Cells[i + 1, 3] = dados.Codigo;
                    work.Cells[i + 1, 4] = dados.Descricao;
                    work.Cells[i + 1, 5] = dados.Tipo;
                    work.Cells[i + 1, 6] = dados.Palete;
                    work.Cells[i + 1, 7] = dados.Prog;
                    work.Cells[i + 1, 8] = dados.Prd;
                    work.Cells[i + 1, 9] = dados.TRS;
                    work.Cells[i + 1, 10] = Convert.toString(dados.InicioHora);
                    work.Cells[i + 1, 11] = Convert.toString(dados.FimHora);
                    work.Cells[i + 1, 12] = dados.Tempo;
                    work.Cells[i + 1, 13] = dados.TempoEfetivo;
                    work.Cells[i + 1, 14] = dados.Previsto;
                    work.Cells[i + 1, 15] = dados.Maquina;
                    work.Cells[i + 1, 16] = dados.Bicos;
                    work.Cells[i + 1, 17] = dados.DataLancada;
                    work.Cells[i + 1, 18] = dados.Login;                        
                }
                    work_book.SaveAs(arquivo.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
                    work_book.Close(true);
                    app.Quit();
                }

            }
            catch(Exception ex)
            {
                MessageBox.Show("Erro ao exportar a informação devido a: " + ex.ToString(), "Problema na Exportação", MessageBoxButton.OK, MessageBoxImage.Error);
            }

        }
    }

Double Click Label Event (Calls Export):

private void ExportTOExcel_ApontaPrd(object sender, MouseButtonEventArgs e)
{
    ExportToExcel export = new ExportToExcel();
    Export.ExportarListViewToExcel_TabPrdTRS(listView_tabPrdTRS);
}

XMAL:

 <ListView x:Name="listView_tabPrdTRS" Margin="34,345,618,51" ItemsSource="{Binding}" ScrollViewer.CanContentScroll="True" SelectionMode="Extended" MouseDoubleClick="SelecionarAltear_ApontaPrd" SelectionChanged="BuscaParadas_Selecao_ApontaPrd">
                            <ListView.ItemContainerStyle>
                                <Style TargetType="{x:Type ListViewItem}">
                                    <Setter Property="BorderBrush" Value="LightGray" />
                                    <Setter Property="BorderThickness" Value="0,0,0,1" />
                                </Style>
                            </ListView.ItemContainerStyle>
                            <ListView.View>
                                <GridView>
                                    <GridView.ColumnHeaderContainerStyle>
                                        <Style TargetType="{x:Type GridViewColumnHeader}">
                                            <!-- Set any properties you want to set -->
                                            <Setter Property="Background" Value="{StaticResource LightColorBrush}" />
                                            <Setter Property="Foreground" Value="Black" />
                                        </Style>
                                    </GridView.ColumnHeaderContainerStyle>
                                    <GridViewColumn Header="Data Prod." Width="Auto"  DisplayMemberBinding="{Binding DataPrd, StringFormat=dd/MM/yy}"/>
                                    <GridViewColumn Header="OP" Width="Auto" DisplayMemberBinding="{Binding OP}"/>
                                    <GridViewColumn Header="Código" Width="Auto" DisplayMemberBinding="{Binding Codigo}"/>
                                    <GridViewColumn Header="Descrição" Width="Auto" DisplayMemberBinding="{Binding Descricao}"/>
                                    <GridViewColumn Header="Tipo" Width="Auto" DisplayMemberBinding="{Binding Tipo}"/>
                                    <GridViewColumn Header="Palete(t)" Width="Auto" DisplayMemberBinding="{Binding Palete, ConverterCulture=pt-BR, StringFormat=0.000}"/>
                                    <GridViewColumn Header="Prog.(t)" Width="Auto" DisplayMemberBinding="{Binding Prog, ConverterCulture=pt-BR, StringFormat=#\,###\,##0.000}"/>
                                    <GridViewColumn Header="Prod.(t)" Width="Auto" DisplayMemberBinding="{Binding Prd, ConverterCulture=pt-BR, StringFormat=#\,###\,##0.000}"/>
                                    <GridViewColumn Header="TRS(%)" Width="Auto"  DisplayMemberBinding="{Binding TRS, ConverterCulture=pt-BR, StringFormat=0.00}"/>
                                    <GridViewColumn Header="H. Inicial" Width="Auto" DisplayMemberBinding="{Binding InicioHora}"/>
                                    <GridViewColumn Header="H. Final" Width="Auto" DisplayMemberBinding="{Binding FimHora}"/>
                                    <GridViewColumn Header="Tempo(h)" Width="Auto" DisplayMemberBinding="{Binding Tempo, ConverterCulture=pt-BR, StringFormat=0.00}"/>
                                    <GridViewColumn Header="T. Efetivo(h)" Width="Auto" DisplayMemberBinding="{Binding TempoEfetivo, ConverterCulture=pt-BR, StringFormat=0.00}"/>
                                    <GridViewColumn Header="Prev.(t)" Width="Auto" DisplayMemberBinding="{Binding Previsto, ConverterCulture=pt-BR, StringFormat=#\,###\,##0.000}"/>
                                    <GridViewColumn Header="Máquina" Width="Auto" DisplayMemberBinding="{Binding Maquina}"/>
                                    <GridViewColumn Header="Ensac." Width="Auto" DisplayMemberBinding="{Binding Bicos}"/>
                                    <GridViewColumn Header="Atualização" Width="Auto" DisplayMemberBinding="{Binding DataLancada, StringFormat=dd/MM/yy HH:mm:ss}"/>

                                </GridView>
                            </ListView.View>
                        </ListView>
c# excel wpf linq-to-sql export

1 answer

1


EDIT: Modify your Exporttoexcel class like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.Windows.Controls;
using System.Windows;
using TRSSystem.AcessoDados;

namespace TRSSystem
{
   class ExportToExcel
   {
      private object _optionalValue = Missing.Value;
      //Exportar ListView para um Arquivo Excel
      public void ExportarListViewToExcel_TabPrdTRS(ListView ltv)
      {
        try
        {
            Microsoft.Win32.SaveFileDialog arquivo = new Microsoft.Win32.SaveFileDialog();
            arquivo.Filter = "Excel (*.xls)|*.xls";
            arquivo.FileName = "ArquivoExportado";
            if (arquivo.ShowDialog() == true)
            {
                //start excel
                Excel.Application excapp = new Excel.Application();
                excapp.Visible = false;
                var workbook = excapp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);


                //Specifying sheet
                var sheet = (Excel.Worksheet)workbook.Sheets[1]; //indexing starts from 1
                excapp.ActiveWindow.DisplayGridlines = false;

                int headercount = 1;
                var range = sheet.get_Range("A1", "A1");
                //Filling Headers
                List<object> objHeaders = new List<object>();

                foreach (var headerItem in ((GridView)ltv.View).Columns)
                {
                    objHeaders.Add(headerItem.Header);
                    headercount++;
                }
                headercount -= 1;
                var headerToAdd = objHeaders.ToArray();
                range = range.get_Resize(1, headercount);
                range.set_Value(_optionalValue, headerToAdd);
                range.Columns.AutoFit();

                //Filling values
                object[,] objData = new object[ltv.Items.Count, headercount];
                int i = 0;


                for (i = 0; i < ltv.Items.Count; i++)
                {
                      tabPrdTR dados = (tabPrdTR)ltv.Items[i];
                      objData[i, 0] = dados.DataPrd;
                      objData[i, 1] = dados.OP;
                      objData[i, 2] = dados.Codigo;
                      objData[i, 3] = dados.Descricao;
                      objData[i, 4] = dados.Tipo;
                      objData[i, 5] = dados.Palete;
                      objData[i, 6] = dados.Prog;
                      objData[i, 7] = dados.Prd;
                      objData[i, 8] = dados.TRS;
                      objData[i, 9] = Convert.ToString(dados.InicioHora);
                      objData[i, 10] = Convert.ToString(dados.FimHora);
                      objData[i, 11] = dados.Tempo;
                      objData[i, 12] = dados.TempoEfetivo;
                      objData[i, 13] = dados.Previsto;
                      objData[i, 14] = dados.Maquina;
                      objData[i, 15] = dados.Bicos;
                      objData[i, 16] = dados.DataLancada;
                      //objData[i, 17] = dados.Login;
                }

                range = sheet.get_Range("A2", _optionalValue);
                range = range.get_Resize(i, headercount);
                range.set_Value(_optionalValue, objData);
                range.Columns.AutoFit();

                workbook.SaveAs(arquivo.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                workbook.Close();
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show("Erro ao exportar a informação devido a: " + ex.ToString(), "Problema na Exportação", MessageBoxButton.OK, MessageBoxImage.Error);
        }

    }
}}

  • I still haven’t found... Making the dictionary would be another move, wouldn’t? I wonder the way I put in the question to pull the headers...

  • Okay, I edited according to your code. Take a look

  • Opa... Sorry it took me so long to reply... Error 79 work.Cells[i + 1, 17] = data;

  • You know what it can be?

  • When you can...

  • This is Felipe! Sorry to keep you waiting. Comment the part of the code that Voce fills the same values, from the comment //Filling Values until before saving to excel. Confirm for me if the headers are correct please

  • Opa... I commented until range.Columns.Autofit(). And pulled the headers yes and saved in spreadsheet...

  • In your waiting...

  • believe objData[i + 1, 17] may be popping the size allocated to objData[,]. Since I don’t know your code, it is difficult for me to understand what is wrong. But I think it may be that headercount is less than 17, or something. You could debug and see which iteration the program breaks

  • I remember the error that gives please and in which iteration no for()

  • Voce knows how to chat here?

  • Jeez... it took me a while to see this I’m looking at now...

  • Error when exporting information due to: System.Indexoutofrangeexception: The index was outside the matrix limits. in Exporttoexcel on line 79. Row 79 is this targetData[i + 1.17] = data;

  • Let’s go continue this discussion in chat.

  • Forlani, good afternoon. How do I include in these commands a Progressbar in conjunction with Backgroundworker in the Duploclique Event? Name Progressbar_apontaprd

  • I tried through that explanation, but I couldn’t...https://www.youtube.com/watch?v=FQ5qtC2Z-rg

  • Felipe, I have a code here that does this, but I think it’s gonna get a little big for us to argue around here, maybe discuss it over email? Maybe by email? [email protected]

  • Blz... Enter the Angouts!!

Show 13 more comments

Browser other questions tagged c# excel wpf linq-to-sql export

You are not signed in. Login or sign up in order to post.