1

I am new in C#, I have one problem in my c# Windows Application. I am Created one windows application in c#, it is successfully working all the task, but when I am trying to open my c# windows application first time it requires approximately 1 minute and 30 seconds because one excel file is load in application when first time trying to open the application.

This excel file is use for "AutoCompleteStringCollection".

Please see the following code and suggest me solution how I can reduce time to my application Opening time. 

private void Form1_Load(object sender, EventArgs e)
  {

 InitializeTextSearchListView();

 textBox1.AutoCompleteMode = AutoCompleteMode.Suggest;           
            textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
            AutoCompleteStringCollection DataCollection = new AutoCompleteStringCollection();
            addItems(DataCollection,listView1);
            textBox1.AutoCompleteCustomSource = DataCollection;

  }

  public void addItems(AutoCompleteStringCollection col, ListView ListView1)
    {

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;

        object missing = System.Reflection.Missing.Value;

        string str;
        int rCnt = 0;
        int cCnt = 0;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open("C:\\SAM Files\\Static Ticker Data.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);           
        range = xlWorkSheet.UsedRange;

        for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
        {
            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
            {
                if (!(((range.Cells[rCnt, cCnt] as Excel.Range).Value2) == null))
                {
                    if ((range.Cells[rCnt, cCnt] as Excel.Range).Value2.GetType().ToString() == "System.Double")
                    {
                        double d1 = (Double)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                        str = Convert.ToString(d1);
                        col.Add(str);
                    }
                    else
                    {
                        str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                        col.Add(str);
                    }
                }

            }
        }

Note: When I select less data contain excel then application opening time is also reduce means its problem for to select large data contain excel file.

So please tell me how I can reduce time to Open my Application.

Improve this question
3
  • You can try using different thread to load the data from excel after application startup. Commented Jul 29, 2016 at 7:51
  • The process interop required to bridge the divide between your program and the Excel process makes this code very expensive. Be sure to read just once, read an array instead of one cell at a time. And strongly consider using the OpenXML api instead so you don't have to wait until Excel starts up. Commented Jul 29, 2016 at 7:59
  • From that, your just adding a linear list of items, so why not use a simple text file or an XML config file instead of Excel? Commented Jul 29, 2016 at 9:28

3 Answers 3

Reset to default
1

Stepping through various Cells in a Range in Excel is very slow. It is usually much faster (for both read and write operations) to handle the entire range as an array of objects. Assuming your data is in a simple table structure (and that UsedRange is not spanning different combinations of tables, cells etc), then I would suggest trying:

var array = range.Value;

This will create an array of objects matching your table.

Taking the row and column dimensions from the Range object, it should be much faster to step through the resultant array, than stepping through the cells in the Excel range.

Please note that the array this provides is 1-based (not 0-based), i.e. you need to do: for (int i = 1; i <= range.Rows.Count; i++) { for (int j = 1; j <= range.Columns.Count; j++) { //process array[i, j] here } }

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I would suggest you to not use Excel itself to load the Excel sheet into your application. There are many reasons for this:

  1. You need to have Excel installed on each computer where you run the application.
  2. It is slow.
  3. There might be all kinds of security issues that you can run into.

From what I see in your code you only need to read values from the Excel sheet. A good alternative in this case would be the NPOI library. This is an open source library that you can find here: https://npoi.codeplex.com/.

If you would need more Excel functionality, like for example write data into the Excel sheet and have the Excel sheet recalculate, then you can consider the SpreadSheet Gear library that you can find here: http://www.spreadsheetgear.com/. This is not open source, but it works great.

The NPOI library version I now use is NPOI 2.2.1, it can be downloaded from https://npoi.codeplex.com/releases.

Below is code that you can use to open an Excel sheet and iterate through the data.

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            HSSFWorkbook hssfwb;
            using (var file = new FileStream(args[0], FileMode.Open, FileAccess.Read))
            {
                hssfwb = new HSSFWorkbook(file);
            }

            ISheet sheet = hssfwb.GetSheet("Arkusz1");
            for (int row = 0; row <= sheet.LastRowNum; row++)
            {
                if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
                {
                    Console.WriteLine(sheet.GetRow(row).GetCell(0).StringCellValue);
                }
            }
        }
    }
}
Improve this answer

2 Comments

Thanks for suggestion, but have no idea how to use and which version of NPOI library use in my code, please tell me about that.
@TusharPatil, I have just added a code example and a link from where to get the library
0

A more general answer to your question "I've got a heavy/long process blocking my program", is to use multiple threads. The easiest way is to use the built-in BackgroundWorker class: MSDN page

Basically in Form1_Load you want something similar to:

textBox1.AutoCompleteMode = AutoCompleteMode.Suggest;           
textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
AutoCompleteStringCollection DataCollection = new AutoCompleteStringCollection();

BackgroundWorker worker = new BackgroundWorker();
worker.DoWork += (s, ev) =>
{
   addItems(DataCollection,listView1);
};
worker.RunWorkerCompleted += (s, ev) =>
{
   textBox1.AutoCompleteCustomSource = DataCollection;     
};
worker.RunWorkerAsync();

So in the DoWork method is the heavy/long running process, then in the RunWorkerCompleted is what to do when that process has finished.

This is not without it's pitfalls, esp with UI, as generally only one thread can update the UI so you have to marshall the threads. And you've also got secondary questions to answer: How far can the program be used while this work is underway? Do I need to show users its working in the background? etc

Improve this answer

1 Comment

Thank you so much you suggested code (BackgroundWorker) is perfectly working for my application. Thanks once again "cjb100".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.