locked
How do we read one time excel file instead of every time open and close in C# RRS feed

  • Question

  • I have the requirement which is i want to read particular range from excel.

    For example below is my code:

    int endno = 500;

    for (int i=0; i <=endno; i++)

    {

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\Test\Test.xlsx");
            Excel.Worksheet sheet = xlWorkbook(sheet1);

            char[] startIndex = start.ToArray();
           char[] endIndex = end.ToArray();

            int startX = int.Parse(startIndex[0].ToString());
            string startY = startIndex[1].ToString();
            int endX = int.Parse(endIndex[0].ToString());
            string endY = endIndex[1].ToString();

            object[,] data = sheet.Range[sheet.Cells[startX, startY], sheet.Cells[endX, endY]].Cells.Value2;

             // have to do some process and calculation here

    }

    the above case, i want to open the file every loop iterating and process some calculation.

    the pain is, when i read the excel  file every time (open excel and read), it's taking too much time and and unnecessarily more excel process occupied.

    Instead of every time open and close excel file, Is it possible to read one time open the file and  convert into  data table..?

    So that, the first time will be reading the file and converting in to data table. The next iteration we can read it from the datatable and we can process it.

    Is it possible to do this..?

    Wednesday, March 25, 2020 2:05 AM

Answers

  • Hi Gani tpt,

    Thank you for posting here.

    Like Naomi said, take part of the content out of the for loop.

    Moreover, if you just want to perform simple filtering or calculation on the obtained results, there is no need to convert it into a datatable, a simple list should be fine.

            static void Main(string[] args)
            {
                List<int> list = DoWork(parameters);
                int endno = 500;
                for (int i = 0; i <= endno; i++)
                {
                    // have to do some process and calculation here
                }
            }
            
            public static List<int> DoWork(  parameters ) 
            {
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\test\excel\test.xlsx");
                Excel.Worksheet sheet = xlWorkbook.ActiveSheet;
                char[] startIndex = start.ToArray();
                char[] endIndex = end.ToArray();
    
                int startX = int.Parse(startIndex[0].ToString());
                string startY = startIndex[1].ToString();
                int endX = int.Parse(endIndex[0].ToString());
                string endY = endIndex[1].ToString();
    
                object[,] data = sheet.Range[sheet.Cells[startX, startY], sheet.Cells[endX, endY]].Cells.Value2;
    
                List<int> list = new List<int>();
                foreach (var item in data)
                {
                    list.Add(int.Parse(item.ToString()));
    
                }
                return list;
            }

    If for some reason you really need a datatable, you can do it like this.

                DataTable dataTable = new DataTable();
                int iValue = sheet.UsedRange.Cells.Columns.Count;
                int jValue = sheet.UsedRange.Cells.Rows.Count;
    
                for (int i = 0; i < iValue; i++)
                {
                    char re = Convert.ToChar(startIndex[1] + i);
                    dataTable.Columns.Add(re.ToString(), typeof(int));
                }
               
                for (int j = 1; j <= jValue; j++)
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int i = 1; i <= iValue ; i++)
                    {
                        dataRow[i - 1] = int.Parse(data[j, i].ToString());
                    }
                    dataTable.Rows.Add(dataRow);
                }

    Result:

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Monday, April 6, 2020 2:55 AM
    Wednesday, March 25, 2020 7:26 AM

All replies

  • Well, of course the first 3 lines need to go outside the loop. The rest is unclear since you didn't show the exact code. May be most of other code can also go outside and only portion that needs to access the i variable needs to be inside the loop.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 25, 2020 2:31 AM
  •  object[,] data = sheet.Range[sheet.Cells[startX, startY], sheet.Cells[endX, endY]].Cells.Value2;

    The above excel file is the range and i want to convert this in to datatable for all the process within the loop.

    Excel cell must be read (D6:D10 and I6:I10) == > want to convert datatable with excel column heading like (D to I)



    • Edited by Gani tpt Wednesday, March 25, 2020 2:43 AM
    Wednesday, March 25, 2020 2:43 AM
  • Hi Gani tpt,

    Thank you for posting here.

    Like Naomi said, take part of the content out of the for loop.

    Moreover, if you just want to perform simple filtering or calculation on the obtained results, there is no need to convert it into a datatable, a simple list should be fine.

            static void Main(string[] args)
            {
                List<int> list = DoWork(parameters);
                int endno = 500;
                for (int i = 0; i <= endno; i++)
                {
                    // have to do some process and calculation here
                }
            }
            
            public static List<int> DoWork(  parameters ) 
            {
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\test\excel\test.xlsx");
                Excel.Worksheet sheet = xlWorkbook.ActiveSheet;
                char[] startIndex = start.ToArray();
                char[] endIndex = end.ToArray();
    
                int startX = int.Parse(startIndex[0].ToString());
                string startY = startIndex[1].ToString();
                int endX = int.Parse(endIndex[0].ToString());
                string endY = endIndex[1].ToString();
    
                object[,] data = sheet.Range[sheet.Cells[startX, startY], sheet.Cells[endX, endY]].Cells.Value2;
    
                List<int> list = new List<int>();
                foreach (var item in data)
                {
                    list.Add(int.Parse(item.ToString()));
    
                }
                return list;
            }

    If for some reason you really need a datatable, you can do it like this.

                DataTable dataTable = new DataTable();
                int iValue = sheet.UsedRange.Cells.Columns.Count;
                int jValue = sheet.UsedRange.Cells.Rows.Count;
    
                for (int i = 0; i < iValue; i++)
                {
                    char re = Convert.ToChar(startIndex[1] + i);
                    dataTable.Columns.Add(re.ToString(), typeof(int));
                }
               
                for (int j = 1; j <= jValue; j++)
                {
                    DataRow dataRow = dataTable.NewRow();
                    for (int i = 1; i <= iValue ; i++)
                    {
                        dataRow[i - 1] = int.Parse(data[j, i].ToString());
                    }
                    dataTable.Rows.Add(dataRow);
                }

    Result:

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Monday, April 6, 2020 2:55 AM
    Wednesday, March 25, 2020 7:26 AM
  • Hello,

    If using .xlsx then consider using SpreadSheetLight (free) installed from NuGet here.

    Using the following.

    Declare a private level DataTable.

    private DataTable _sheetDataTable;

    Here I create the DataTable which is read in via a Excel file name and sheet name. I could also create the private DataTable here rather than a local one but did this on the fly. Also I hard wired the range.

    private DataTable ReadRange(string pFileName, string pSheetName)
    {
        var sheetTable = new DataTable();
        sheetTable.Columns.Add(new DataColumn() { ColumnName = "FirstName", DataType = typeof(string)});
        sheetTable.Columns.Add(new DataColumn() { ColumnName = "LastName", DataType = typeof(string) });
        sheetTable.Columns.Add(new DataColumn() { ColumnName = "State", DataType = typeof(string) });
    
        var fs = new FileStream(pFileName, FileMode.Open);
    
        using (var sheet = new SLDocument(fs, pSheetName))
        {
            var stats = sheet.GetWorksheetStatistics();
            for (var rowIndex = 5; rowIndex < stats.EndRowIndex + 1; rowIndex++)
            {
    
                var firstName = sheet.GetCellValueAsString(rowIndex, 6);
                var lastName = sheet.GetCellValueAsString(rowIndex, 7);
                var state = sheet.GetCellValueAsString(rowIndex, 8);
    
                sheetTable.Rows.Add(firstName, lastName, state);
    
            }
    
        }
    
        return sheetTable;
    }

    Run the method above.

    _sheetDataTable = ReadRange(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo1.xlsx"),"Sheet2");

    Write back to the range read above.

    private void UpdateData(string pFileName, string pSheetName)
    {
        using (var doc = new SLDocument(pFileName, pSheetName))
        {
            doc.ImportDataTable(5,6,_sheetDataTable,false);
        }
    }
    

    Call the method above

    UpdateData(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo1.xlsx"), "Sheet2");
    • The file name and sheet name should be private variable, again done on the fly.
    • To work with other data types see methods below.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by Naomi N Wednesday, March 25, 2020 4:09 PM
    Wednesday, March 25, 2020 11:05 AM
  • Hi,

    Has your issue been resolved?

    If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 31, 2020 9:04 AM
  • Yes. awesome.

    i will check and update...anyhow, this my requirement.

    Monday, April 6, 2020 2:56 AM