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

Question

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

Navigation

#1 by (1 votes)

0

Good ...

I need to export the data of the Listview populated by the LINQ method of a SQL Database ... I can not get the Column Headers in Excel ... Can anyone help?

Reference: link

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 Event of a Label (Call 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

asked by anonymous 17.07.2016 / 06:02

1 answer

How to do a 'select multiple' start with the first option already checked PHP Mysql Regex to get a parenthesis and the string inside

score 1 · Accepted Answer

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);
        }

    }
}}