none
Чтение и обработка большого объема данных с Excel файла, WPF C# RRS feed

  • Вопрос

  • Коллеги, возник вопрос имеется Excel файл с данными большого размера (размер изначально неизвестен, может варьироваться), например, 50 столбцов на 20000 строк. Имена столбцов массива постоянно и не меняется и находятся на 50 строке, т.е. считывание нужно начинать с 50 строки. Затем обработка данных и помещение только обработанных данных (5 столбцов на 20000 строк) в DataGrid.  Вопрос в следующем, стоит ли такой объем данных обрабатывать средствами VS (WPF C#), как оптимизировать мой код для ускорения считывания данных и как поместить полученные данные в DataGrid ?  

    Сам код:

    <Window x:Class="WpfExcel.MainWindow"
            xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
            xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
            Title="MainWindow"  Height="350" Width="525">
        <Grid>
            <Menu HorizontalAlignment="Left" Height="24" VerticalAlignment="Top" Width="517">
                <MenuItem Header="Файл" >
                    <MenuItem Header="Открыть" Click="OpenClick" >
                        <MenuItem.Icon>
                            <Image Source="open.gif"/>
                        </MenuItem.Icon>
                    </MenuItem>
                </MenuItem>
            </Menu>
            <DataGrid Name="MyDataGrid"  AutoGenerateColumns="True" 
                      HorizontalAlignment="Left" Margin="10,29,0,0" 
                      VerticalAlignment="Top" Height="251" Width="497"/>
        </Grid>
    </Window>
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows;
    using System.Windows.Controls;
    using System.Windows.Data;
    using System.Windows.Documents;
    using System.Windows.Input;
    using System.Windows.Media;
    using System.Windows.Media.Imaging;
    using System.Windows.Navigation;
    using System.Windows.Shapes;
    using Microsoft.Office.Interop.Excel;
    using System.IO;
    
    namespace WpfExcel
    {
    
        /// <summary>
        /// Interaction logic for MainWindow.xaml
        /// </summary>
        public partial class MainWindow : System.Windows.Window
        {
            private Microsoft.Office.Interop.Excel.Application ExcelApp;
            private Microsoft.Office.Interop.Excel.Workbook WorkBookExcel;
            private Microsoft.Office.Interop.Excel.Worksheet WorkSheetExcel;
            private Microsoft.Office.Interop.Excel.Range RangeExcel;
            public MainWindow()
            {
                InitializeComponent();
    
            }
    
            private void OpenClick(object sender, RoutedEventArgs e)
            {
                Microsoft.Win32.OpenFileDialog openDialog = new Microsoft.Win32.OpenFileDialog();
                openDialog.Filter = "Файл Excel|*.XLSX;*.XLS";
                var result = openDialog.ShowDialog();
                if (result == false)
                {
                    MessageBox.Show("Файл не выбран!", "Информация", MessageBoxButton.YesNo, MessageBoxImage.Information);
                    return;
                }
                string fileName = System.IO.Path.GetFileName(openDialog.FileName);
    
                ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                    //Книга.
                WorkBookExcel = ExcelApp.Workbooks.Open(openDialog.FileName);
                    //Таблица.
               // WorkSheetExcel = ExcelApp.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
               //    RangeExcel = null;
                    WorkSheetExcel = (Microsoft.Office.Interop.Excel.Worksheet)WorkBookExcel.Sheets[1];
    
                    var lastCell = WorkSheetExcel.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
                    string[,] list = new string[lastCell.Column, lastCell.Row];
                
                for(int i=0;i<(int)lastCell.Column;i++)
                    for(int j=0;j<(int)lastCell.Row;j++)
                        list[i, j] = WorkSheetExcel.Cells[j + 1, i + 1].Text.ToString();//считал текст в строку
    
                WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыть не сохраняя
                ExcelApp.Quit(); // вышел из Excel
                GC.Collect(); // убрал за собой
    
                MyDataGrid.ItemsSource = list;
            }
        }
    }


    3 ноября 2014 г. 17:25

Ответы

  • Добрый день,

    напрямую с OpenXML SDK работать тяжеловато, используйте обертки над OpenXML типа ClosedXML , с ними работать одно удовольствие.

    • Помечено в качестве ответа AlexFV 8 ноября 2014 г. 14:05
    7 ноября 2014 г. 3:13
  • Не забывайте про конструкцию:

    <DataGrid>
       <DataGrid.Columns>
            <DataGridTextColumn/>
       </DataGrid.Columns>
    </DataGrid>

    Пропущен тэг: 

    <DataGrid.Columns> </DataGrid.Columns>


    • Помечено в качестве ответа AlexFV 15 ноября 2014 г. 11:11
    14 ноября 2014 г. 20:48

Все ответы

  • Не знаю насколько быстро идет считывание данных из Excel, но загрузка данных большого размера полностью в Grid не хорошая идея. Лучше загружать частями по мере необходимости. Если каждое обращение к файлу в таком случае происходит медленно, то можно воспользоваться какой-нибудь небольшой БД (SQLite, SQLCe) как буфером хранения данных. Т.е. при открытии файла вы асинхронно запрашиваете все данные и переносите их в БД, когда часть данных уже загружена, можно  отобразить первую порцию, в это время в фоне ваша база будет загружаться данными из файла. В дальнейшем вся ваша работа будет происходить с базой.


    [Блог] [Twitter]


    4 ноября 2014 г. 8:29
  • Я согласен, что лучше загружать частями, как это сделать, пример кода, могли бы показать, пожалуйста. По поводу базы данных опять таки ее необходимо заполнить данными с Excel, как это сделать с таким объемом?
    4 ноября 2014 г. 16:50
  • Добрый день.

    1. Разберитесь что именно у вас работает медленно.

    2. Если медленно идет именно загрузка данных, то откажитесь от Ole и используйте для чтения данных OpenXML.

    3. Если медленно идет отображение (т.е. вы данные загрузили в промежуточную коллекцию, подсовываете их в DataGrid и он начинает тупить), то подумайте о механизме постраничного просмотра. Под гридом добавьте TextBlock с номером страницы и две кнопки (уменьшить и увеличить), после нажатия на которые подсовывайте в датагрид только тысячу записей с указанной страницы.

    Ну а если тормозит и то и другое, то придется делать и 2 и 3.

    5 ноября 2014 г. 11:13
    Отвечающий
  • 1) Медленно работает именно загрузка данных с Excel так, как большой объем данных.

    2) Спасибо за идею с OpenXML, но OpenXML работает только с Excel 2010 и выше, а что например делать с фалом Excel 2007, может быть через ExcelDataReader???

    5 ноября 2014 г. 18:25
  • Эм, не хочу вас расстраивать, но Office 2007 использует тот-же формат файлов, что и Office 2010. Ну а если у вас могут прийти файлы от Office 2003, то попробуйте, быстрее чем через Ole будет практически любое решение.
    5 ноября 2014 г. 18:30
    Отвечающий
  • Да я в курсе :), что формат один и тот же. 

    Итак добавил код:

    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    ReadExcelFile(fileName);
    static void ReadExcelFile(string fileName)
            {
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
                {
                    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    
                    OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
                    string text;
                    while (reader.Read())
                    {
                        if (reader.ElementType == typeof(CellValue))
                        {
                            text = reader.GetText();
                        }
                    }
                }
            }

    Файлы Excel 2003 и файлы *.xls вызывает исключение в  spreadsheetDocument.

    Файлы  *.xlsx проходят, но в text пусто, так, как условие не выполняется, подскажите, что не так???



    • Изменено AlexFV 6 ноября 2014 г. 17:43
    5 ноября 2014 г. 20:27
  • Сам тестовый файл простая таблица 3 столбца на 5 строк.

    6 ноября 2014 г. 19:15
  • Добрый день,

    напрямую с OpenXML SDK работать тяжеловато, используйте обертки над OpenXML типа ClosedXML , с ними работать одно удовольствие.

    • Помечено в качестве ответа AlexFV 8 ноября 2014 г. 14:05
    7 ноября 2014 г. 3:13
  • Если не разберетесь с OpenXML, используйте OleDb. Это лучше чем работать с листом напрямую. Посредством OleDb можно получить из excel-файла DataTable и уже работать с ним.

    Для интереса можно засечь время выполнения той или иной операции  с помощью класса Stopwatch и произвести оптимизацию кода/алгоритма.


    7 ноября 2014 г. 12:03
  • Все получилось, как и хотел с помощью ClosedXML, всем спасибо за помощь. 
    8 ноября 2014 г. 14:08
  • AlexFaraonCar, поделитесь статистикой. Сколько по времени у вас занимает выборка содержимого листа при помощи ClosedXML? Каковы размеры таблицы Excel (кол-во столбцов и строк)?

    Хотелось бы сравнить с OleDb. Для примера, я сделал выборку из листа Excel, состоящего из 22 столбцов и 41000 строк, и время считывания в DataTable составило 21 секунду. 

    8 ноября 2014 г. 15:11
  • Самое длительное время заняло открытие файла:  

    StopWatch показал 5.5238 сек считал весь Excel лист.

    У меня такой вопрос даные считал, как их записать в DataGrid, делал так:

    <DataGrid Name="MyDataGrid"  AutoGenerateColumns="True" 
                      HorizontalAlignment="Left" Margin="10,29,0,0" 
                      VerticalAlignment="Top" Height="281" Width="497" >
                <DataGridTextColumn Header="DATA #1"/>
    
            </DataGrid>

    List<String> firstData;

    firstData = new List<string>(); ReadExcelFileMy(fileName, out firstData); MyDataGrid.ItemsSource = firstData;

     Ошибка: значения коллекции должны быть пусты перед использованием ItemsSource (Additional information: Items collection must be empty before using ItemsSource.)
    • Изменено AlexFV 14 ноября 2014 г. 17:28
    13 ноября 2014 г. 19:10
  • StopWatch показал 5.5238 сек. 

    Worksheet  (один Лист) размер A1 на XFD1048576

    Уточнение для kremlinbot.
    14 ноября 2014 г. 17:28
  • Не забывайте про конструкцию:

    <DataGrid>
       <DataGrid.Columns>
            <DataGridTextColumn/>
       </DataGrid.Columns>
    </DataGrid>

    Пропущен тэг: 

    <DataGrid.Columns> </DataGrid.Columns>


    • Помечено в качестве ответа AlexFV 15 ноября 2014 г. 11:11
    14 ноября 2014 г. 20:48
  • Спасибо, не дописал, сразу кодить пошел.
    15 ноября 2014 г. 11:11
  • Если связываю данные, то данные не преобразуются, в каком формате должны быть данные для binding-a?

    Вот как реализовал:

    First = new List<string>();
    Second = new List<string>();
    
    IEnumerable<DataInput> ResultDetails = new List<DataInput> { new DataInput(First, Second) };
    
    MyDataGrid.ItemsSource = ResultDetails;
    <DataGrid.Columns>
                    <DataGridTextColumn Header="Data #1" Binding="{Binding Path=First}"></DataGridTextColumn>
                    <DataGridTextColumn Header="Data #2" Binding="{Binding Path=Second}"></DataGridTextColumn>          
    </DataGrid.Columns>


    15 ноября 2014 г. 16:43
  • Попробовал через код, получилось вывести не данные а ->  WpfExcel.DataInput  в ячейку, как можно подкорректировать и желательно, реализовать через XAML? Я так понимаю надо путь к First, как это сделать?

    Сам код:

                MyDataGrid.AutoGenerateColumns = false;
                var MyDGColumn = new DataGridTextColumn();
                MyDGColumn.Header = "DATA #1";
                MyDataGrid.Columns.Add(MyDGColumn);
                MyDataGrid.ItemsSource = ResultDetails;
                MyDGColumn.Binding = new Binding();


    • Изменено AlexFV 15 ноября 2014 г. 20:25
    15 ноября 2014 г. 20:02
  • Может, что-то подскажите, какие есть идеи?
    17 ноября 2014 г. 17:37
  • Примерно так (не ручаюсь, что корректно):

    XAML-вариант:

    <DataGrid x:Name="MyDataGrid" ItemsSource="{Binding ResultDetails}" Width="100" Height="100"  AutoGenerateColumns="False" >
       <DataGrid.Columns>
            <DataGridTextColumn Header="#" Width="Auto" />
            <DataGridTextColumn Header="1st" Binding="{Binding Path=First}"  Width="Auto" />
            <DataGridTextColumn Header="2nd" Binding="{Binding Path=Second}"  Width="Auto" />         
       </DataGrid.Columns>
    </DataGrid>

    Или через код C# в .cs:

    MyDataGrid.AutoGenerateColumns = false;
    MyDataGrid.Columns.Add(new DataGridTextColumn() {Header = "1st", Binding = new Binding ("First")});
    MyDataGrid.Columns.Add(new DataGridTextColumn() {Header = "2nd", Binding = new Binding ("Second")});
    MyDataGrid.ImageSource = ResultDetails;



    • Изменено kremlinbot 17 ноября 2014 г. 18:47
    17 ноября 2014 г. 18:46
  • Оба варианта дают в одной ячейке первой колонки надпись -> (Collection).  Вторая колонка всегда пуста хотя имя ей присвоила корректно.

    First и Second список String данных.

    • Изменено AlexFV 17 ноября 2014 г. 19:29
    17 ноября 2014 г. 19:28
  • kremlinbot Binding = new Binding ("First")

    связь списка First, неверно, поэтому и показывает, что есть коллекция, но параметры с коллекции не вытягивает, да и через C# выводит в обоих ячейках -> (Collection), сорри за неточность, в классе второй список был private. Может как то можно конвертнуть списки First и Second?

    18 ноября 2014 г. 18:24
  • Может есть идеи, не стесняемся излагаем, может решим проблему.
    19 ноября 2014 г. 18:52
  • Тему можно закрыть.
    20 ноября 2014 г. 17:48
  • Как решили проблему?
    22 ноября 2014 г. 18:36
  • Переписал код и все нужные колонки скинул в DataTable ну, а там все просто.
    25 ноября 2014 г. 21:04