Пример #1
0
        /// <summary>
        /// Reads the given Excel file.
        /// </summary>
        /// <param name="filePath">The Excel file path.</param>
        /// <param name="progress">The progress reporter.</param>
        /// <returns>A <see cref="LogTable"/>.</returns>
        private static LogTable ReadXlFile(string filePath, IProgress <int> progress = null)
        {
            var returnTable = new LogTable();
            var excelApp    = new Excel.Application
            {
                DisplayAlerts  = false,
                ScreenUpdating = false,
                Visible        = true
            };
            var workbooks = excelApp.Workbooks;

            returnTable.BeginLoadData();
            var thisWorkBook = workbooks.Open(filePath, ReadOnly: true);

            // Initialize progress reporting
            var rowsRead  = 0;
            var totalRows = thisWorkBook.Sheets.OfType <Excel.Worksheet>().Sum(w => w.UsedRange.Rows.Count - 1); // Minus header row

            progress?.Report(0);

            try
            {
                foreach (var thisSheet in thisWorkBook.Sheets.OfType <Excel.Worksheet>())
                {
                    var thisRange = thisSheet.UsedRange;
                    var thisDate  = Convert.ToDateTime(thisSheet.Name, Values.AppCulture);

                    // Get column headers
                    var colDict = new Dictionary <int, int>();
                    for (var col = 1; col <= thisRange.Columns.Count; col++)
                    {
                        var headerName = Convert.ToString(thisRange.Cells[1, col].Value2);
                        var colName    = returnTable.Columns.OfType <DataColumn>()
                                         .First(c => c.Caption == headerName || c.ColumnName == headerName).ColumnName;
                        var colIndex = returnTable.Columns.IndexOf(colName);
                        if (colIndex > 0)
                        {
                            colDict.Add(col, colIndex);
                            continue;
                        }

                        Tools.DebugLog($"Error importing column '{colName}': column not found in target table.");
                    }

                    // Read each row below the column headers
                    for (var row = 2; row <= thisRange.Rows.Count; row++)
                    {
                        var newRow = returnTable.NewRow();
                        newRow.Date = thisDate;
                        for (var col = 1; col <= thisRange.Columns.Count; col++)
                        {
                            if (colDict.ContainsKey(col))
                            {
                                // Safe casts in case cell values are null
                                var cellValue = Convert.ToString(thisRange.Cells[row, col].Value2) ?? string.Empty;
                                if (returnTable.Columns[colDict[col]].DataType == typeof(bool))
                                {
                                    newRow[colDict[col]] = cellValue.ToUpper() == Values.BoolExportString;
                                }
                                else
                                {
                                    newRow[colDict[col]] = cellValue;
                                }
                            }
                        }

                        returnTable.Add(newRow);

                        // Report progress
                        rowsRead++;
                        progress?.Report(100 * rowsRead / totalRows);
                    }
                }
            }
            catch (Exception error)
            {
                Tools.DebugLog(error.Message);
            }
            finally
            {
                thisWorkBook.Close();
                workbooks.Close();
            }

            excelApp.Quit();
            returnTable.EndLoadData();
            return(returnTable);
        }