none
read excel file in c# RRS feed

  • Question

  • I am a beginner in C# and I do not know many thing about it. I have an excel file with about 30000 rows and I want to search its special columns and if its value is between special values, increase a counter and write that row into new excel file and save. but I have problem with reading excel file in c#. first I use the below code but it takes about 2 hours to implement that is very bad. after that I try to use openxml but I do not know how should I use it? the examples also use foreach that is time consuming too. can you guide me? if there is a code with this conditions is very good. because I do not know many functions in C# and some times I can not understand some explanations. please help me. I need this and I think this should be very simple but I can not find good things about this problem.

     Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Workbook xlWorkBookahan1;
                Excel.Workbook xlWorkBookahan2;
                Excel.Workbook xlWorkBookahangh;
                Excel.Worksheet xlWorkSheet;
                Excel.Worksheet xlWorkSheetahan1;
                Excel.Worksheet xlWorkSheetahan2;
                Excel.Worksheet xlWorkSheetahangh;
                Excel.Range range;
    
                string str;
                int rCnt;
                int cCnt;
                int rw = 0;
                int cl = 0;
    
                //
                Excel.Application xlahan1 = new Microsoft.Office.Interop.Excel.Application();
                Excel.Application xlahan2 = new Microsoft.Office.Interop.Excel.Application();
                Excel.Application xlahangh = new Microsoft.Office.Interop.Excel.Application();
                object misValue = System.Reflection.Missing.Value;
    
    
                if (xlahan1 == null)
                {
                    MessageBox.Show("Excel is not properly installed!!");
                    return;
                }
                xlWorkBookahan1 = xlahan1.Workbooks.Add(misValue);
                xlWorkBookahan2 = xlahan2.Workbooks.Add(misValue);
                xlWorkBookahangh = xlahangh.Workbooks.Add(misValue);
                xlWorkSheetahan1 = (Excel.Worksheet)xlWorkBookahan1.Worksheets.get_Item(1);
                xlWorkSheetahan2 = (Excel.Worksheet)xlWorkBookahan2.Worksheets.get_Item(1);
                xlWorkSheetahangh = (Excel.Worksheet)xlWorkBookahangh.Worksheets.get_Item(1);
    
    
                //
    
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\maedeh\Desktop\Base.xls", 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;
                rw = range.Rows.Count;
                cl = range.Columns.Count;
    
                int ahanghtei = 0, ahan1 = 0, ahan2 = 0, fooladghatei = 0, foolad1 = 0, foolad2 = 0, ngarmghatei = 0, ngarm1 = 0, ngarm2 = 0, nsardghatei = 0, nsard1 = 0, nsard2 = 0, energyghatei = 0, energy1 = 0, energy2 = 0, hamlghatei = 0, haml1 = 0, haml2 = 0, tmarkazighatei = 0, tmarkazi1 = 0, tmarkazi2 = 0, tgmarkazighatei = 0, tgmarkazi1 = 0, tgmarkazi2 = 0, dfnetghatei = 0, dfnet1 = 0, dfnet2 = 0, mtnasoozghatei = 0, mtnasooz1 = 0, mtnasooz2 = 0, fooladsabaghatei = 0, fooladsaba1 = 0, fooladsaba2 = 0;
    
                for (rCnt = 2; rCnt <= rw; rCnt++)
                {
                    if (range.Cells[rCnt, 25].value == 1)//
                    {
                            if (((range.Cells[rCnt, 16]).value <= 1999 && (range.Cells[rCnt, 16]).value >= 1000))
                      {
                          if ((range.Cells[rCnt, 120]).value >= 1000 && (range.Cells[rCnt, 120]).value <= 1999)
                          {
                              ahanghtei++;
                              xlWorkSheetahangh.Cells[ahanghtei, 1] = range.Cells[rCnt, 1];
                              xlWorkSheetahangh.Cells[ahanghtei, 2] = range.Cells[rCnt, 2];
                              xlWorkSheetahangh.Cells[ahanghtei, 3] = range.Cells[rCnt, 3];
                          }
                          else
                          {
                              xlWorkSheet.Rows[rCnt].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                              ahan1++;
                              xlWorkSheetahan1.Cells[ahan1, 1] = range.Cells[rCnt, 1];
                              xlWorkSheetahan1.Cells[ahan1, 2] = range.Cells[rCnt, 2];
                              xlWorkSheetahan1.Cells[ahan1, 3] = range.Cells[rCnt, 3];
                          }
                      }
                      else
                      {
                          if ((range.Cells[rCnt, 120]).value >= 1000 && (range.Cells[rCnt, 120]).value <= 1999)
                          {
                              xlWorkSheet.Rows[rCnt].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                              //xlWorkSheet.Cells[rCnt, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                              ahan2++;
                              xlWorkSheetahan2.Cells[ahan2, 1] = range.Cells[rCnt, 1];
                              xlWorkSheetahan2.Cells[ahan2, 2] = range.Cells[rCnt, 2];
                              xlWorkSheetahan2.Cells[ahan2, 3] = range.Cells[rCnt, 3];
    
                          }
                      }
    
    xlWorkBookahan1.SaveAs("C:\\ahan1.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBookahan2.SaveAs("C:/Users/maedeh/Desktop/ahan2.xls");
                xlWorkBookahangh.SaveAs("C:/Users/maedeh/Desktop/ahangh.xls");
                xlWorkBookahan1.Close(true, misValue, misValue);
                xlWorkBookahan2.Close(true, misValue, misValue);
                xlWorkBookahangh.Close(true, misValue, misValue);
                xlahan1.Quit();
                xlahan2.Quit();
                xlahangh.Quit();
    
                Marshal.ReleaseComObject(xlWorkSheetahan1);
                Marshal.ReleaseComObject(xlWorkSheetahan2);
                Marshal.ReleaseComObject(xlWorkSheetahangh);
                Marshal.ReleaseComObject(xlWorkBookahan1);
                Marshal.ReleaseComObject(xlWorkBookahan2);
                Marshal.ReleaseComObject(xlWorkBookahangh);
                Marshal.ReleaseComObject(xlahan1);
                Marshal.ReleaseComObject(xlahan2);
                Marshal.ReleaseComObject(xlahangh);
    
                Marshal.ReleaseComObject(range);
                Marshal.ReleaseComObject(xlWorkSheet);
    
                //close and release
                xlWorkBook.Close();
                Marshal.ReleaseComObject(xlWorkBook);
    
                //quit and release
                xlApp.Quit();
                Marshal.ReleaseComObject(xlApp);

    Tuesday, December 19, 2017 8:15 AM

Answers

  • Hello nadianaji,

    I think it is not a good idea to iterate through rows to export data to other workbooks row by row. It will spend too much for a source file which has more than 30000 rows data.

    According to your current information, I would suggest you filter on column 16,25 and 120 to get result data you want to export to one workbook and then copy all the data to another workbook together. 

    I create a simply source file like this.

    And then I use below code. You could try to adjust it for you need.

    Excel.Application xlApp= new Excel.Application();
                xlApp.Visible = true;
                Excel.Workbook SourceWorkbook;
                Excel.Workbook TargetWorkbook1;
                Excel.Workbook TargetWorkbook2;
                Excel.Workbook TargetWorkbook3;
    
                Excel.Worksheet SourceWorksheet;
                Excel.Worksheet TargetWorksheet1;
                Excel.Worksheet TargetWorksheet2;
                Excel.Worksheet TargetWorksheet3;
    
                SourceWorkbook = xlApp.Workbooks.Open(@"C:\Users\Administrator\Desktop\Book1.xlsx");
                SourceWorksheet = SourceWorkbook.Worksheets[1];
                TargetWorkbook1 = xlApp.Workbooks.Add();
                TargetWorksheet1 = TargetWorkbook1.Worksheets[1];
                TargetWorkbook2 = xlApp.Workbooks.Add();
                TargetWorksheet2 = TargetWorkbook2.Worksheets[1];
                TargetWorkbook3 = xlApp.Workbooks.Add();
                TargetWorksheet3 = TargetWorkbook3.Worksheets[1];
                xlApp.ScreenUpdating = false;
                //replace 4 with 25, 5 with 16, 6 with 120 in your case
                SourceWorksheet.Columns.AutoFilter(4, "1");
                SourceWorksheet.Columns.AutoFilter(5, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                SourceWorksheet.Columns.AutoFilter(6, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                SourceWorksheet.UsedRange.Columns["A:C"].Copy(TargetWorksheet1.Range["A1"]);
                SourceWorksheet.AutoFilterMode = false;
    
                SourceWorksheet.Columns.AutoFilter(4, "1");
                SourceWorksheet.Columns.AutoFilter(5, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                SourceWorksheet.Columns.AutoFilter(6, "<1000", Excel.XlAutoFilterOperator.xlOr, ">1999");
                SourceWorksheet.UsedRange.Columns["A:C"].Copy(TargetWorksheet2.Range["A1"]);
                SourceWorksheet.UsedRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                SourceWorksheet.AutoFilterMode = false;
    
                SourceWorksheet.Columns.AutoFilter(4, "1");
                SourceWorksheet.Columns.AutoFilter(5, "<1000", Excel.XlAutoFilterOperator.xlOr, ">1999");
                SourceWorksheet.Columns.AutoFilter(6, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                SourceWorksheet.UsedRange.Columns["A:C"].Copy(TargetWorksheet3.Range["A1"]);
                SourceWorksheet.UsedRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                SourceWorksheet.AutoFilterMode = false;
                xlApp.ScreenUpdating = true;

    If you still have issue adjusting it for your need, I would suggest you share a source file so we could use it to try to reproduce your issue. You could share file via One Drive and then put link address here. Thanks for understanding.

    Best Regards,

    Terry


    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.


    • Edited by Terry Xu - MSFT Wednesday, December 20, 2017 7:37 AM
    • Proposed as answer by Terry Xu - MSFT Tuesday, December 26, 2017 1:26 AM
    • Marked as answer by nadianaji Tuesday, December 26, 2017 11:15 AM
    Wednesday, December 20, 2017 7:32 AM

All replies

  • Hello nadianaji,

    I think it is not a good idea to iterate through rows to export data to other workbooks row by row. It will spend too much for a source file which has more than 30000 rows data.

    According to your current information, I would suggest you filter on column 16,25 and 120 to get result data you want to export to one workbook and then copy all the data to another workbook together. 

    I create a simply source file like this.

    And then I use below code. You could try to adjust it for you need.

    Excel.Application xlApp= new Excel.Application();
                xlApp.Visible = true;
                Excel.Workbook SourceWorkbook;
                Excel.Workbook TargetWorkbook1;
                Excel.Workbook TargetWorkbook2;
                Excel.Workbook TargetWorkbook3;
    
                Excel.Worksheet SourceWorksheet;
                Excel.Worksheet TargetWorksheet1;
                Excel.Worksheet TargetWorksheet2;
                Excel.Worksheet TargetWorksheet3;
    
                SourceWorkbook = xlApp.Workbooks.Open(@"C:\Users\Administrator\Desktop\Book1.xlsx");
                SourceWorksheet = SourceWorkbook.Worksheets[1];
                TargetWorkbook1 = xlApp.Workbooks.Add();
                TargetWorksheet1 = TargetWorkbook1.Worksheets[1];
                TargetWorkbook2 = xlApp.Workbooks.Add();
                TargetWorksheet2 = TargetWorkbook2.Worksheets[1];
                TargetWorkbook3 = xlApp.Workbooks.Add();
                TargetWorksheet3 = TargetWorkbook3.Worksheets[1];
                xlApp.ScreenUpdating = false;
                //replace 4 with 25, 5 with 16, 6 with 120 in your case
                SourceWorksheet.Columns.AutoFilter(4, "1");
                SourceWorksheet.Columns.AutoFilter(5, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                SourceWorksheet.Columns.AutoFilter(6, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                SourceWorksheet.UsedRange.Columns["A:C"].Copy(TargetWorksheet1.Range["A1"]);
                SourceWorksheet.AutoFilterMode = false;
    
                SourceWorksheet.Columns.AutoFilter(4, "1");
                SourceWorksheet.Columns.AutoFilter(5, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                SourceWorksheet.Columns.AutoFilter(6, "<1000", Excel.XlAutoFilterOperator.xlOr, ">1999");
                SourceWorksheet.UsedRange.Columns["A:C"].Copy(TargetWorksheet2.Range["A1"]);
                SourceWorksheet.UsedRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                SourceWorksheet.AutoFilterMode = false;
    
                SourceWorksheet.Columns.AutoFilter(4, "1");
                SourceWorksheet.Columns.AutoFilter(5, "<1000", Excel.XlAutoFilterOperator.xlOr, ">1999");
                SourceWorksheet.Columns.AutoFilter(6, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                SourceWorksheet.UsedRange.Columns["A:C"].Copy(TargetWorksheet3.Range["A1"]);
                SourceWorksheet.UsedRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                SourceWorksheet.AutoFilterMode = false;
                xlApp.ScreenUpdating = true;

    If you still have issue adjusting it for your need, I would suggest you share a source file so we could use it to try to reproduce your issue. You could share file via One Drive and then put link address here. Thanks for understanding.

    Best Regards,

    Terry


    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.


    • Edited by Terry Xu - MSFT Wednesday, December 20, 2017 7:37 AM
    • Proposed as answer by Terry Xu - MSFT Tuesday, December 26, 2017 1:26 AM
    • Marked as answer by nadianaji Tuesday, December 26, 2017 11:15 AM
    Wednesday, December 20, 2017 7:32 AM
  • Hi nadianaji,

    you had mentioned that,"I have an excel file with about 30000 rows and I want to search its special columns and if its value is between special values, increase a counter and write that row into new excel file and save. but I have problem with reading excel file in c#. first I use the below code but it takes about 2 hours to implement that is very bad"

    we can see that you are working with large amount of data.

    you have code but it is working slow.

    if we review your steps below,

    -> first you want to search for column and rows for specific values.

    -> then increase the counter.

    -> save that particular row to new excel file and save it.

    -> repeat this process on all the data of Excel.

    so because of many steps which contains looping , searching, matching, copy to new file, saving it. It is taking much time.

    further , you need to develop your own logic based on your data to complete this process as fast as possible.

    you can try for a batch operation instead of working on a single row.

    you can first try to search data then filter and sort it.

    then you can move all the rows together to other Excel file at once.

    by this way , you can enhance the performance of your code.

    further , you can try to study searching Algorithms to make searching functionality more effective and fast.

    you can implement that Algorithm in your code for better performance.

    also you can refer link below will give you the information about how to enhance the performance in Excel.

    Excel performance: Tips for optimizing performance obstructions

    after doing all these things, you will able to see some improvements in performance of the code.

    if you just try to use any random code with different technologies then it will not going to work in this case.

    Regards

    Deepak   


    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.

    Wednesday, December 20, 2017 7:50 AM
    Moderator
  • Dear Terry Xu,

    thank you very much for your response. this answer solved lots of my problem but I have other problems now. the first row of my excel file contains string which are the headings of each columns. So, when I use filtering on columns (like column 25 or others) it produce an exception and if I remove first row it works. now, I do not know what should I do. I do not want to change main excel file? also I want to have first row( which contains headings) in result excel files.

    2.An other question is about coloring cells. how dose it work? dose it color all cells yellow or this is done based on special conditions?

    3. what should I do if I want to know the number of each condition part? for example I used different variables in for loops as counter but I do not know how can I count the results of each filtering part? 

    4.what should I do if I want to save the new produced excel?

    5. I have an other problem too. the first row of main excel is shown in final output while it is not correct!?

    6. is there any way to use Excel.XlAtuoOperator.xOr with 3 or more inputs? for example I need to apply 3 or conditions on cell value but this operator accept 2 conditions?(

      SourceWorksheet.Columns.AutoFilter(81,"23",Excel.XlAutoFilterOperator.xlOr,"12",Excel.XlAutoFilterOperator.xlOr,"13");

    7. if I want to search special heading instead column number , what should I do?

    thank you for your cooperation.







    • Edited by nadianaji Saturday, December 23, 2017 3:41 PM
    Saturday, December 23, 2017 7:27 AM
  • Hello nadianaji,

    Please share a simply data source file and clarify your requirement more detailed so we could try to reproduce your issue.

    You could share file via One Drive and then put link address here.

    Best Regards,

    Terry


    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.

    Monday, December 25, 2017 5:14 AM
  • Hi,

    The better way to process excel files is EPPlus.

    Please refer the below link for basic implementation. Let me know in case of any help required further.

    EPPlus excel processing

    - Vote or mark as answer if this post 

    Regards,

    Sugumar Panneerselvam

    Monday, December 25, 2017 5:24 AM
  • Hi, I upload the source file on the following address and the column of 8, 16 and 75 should be considered in if conditions (col 8=1 and 1000<col 16<1999,...), but for further  processing I need to consider other column which its value should be 21 or 23 or 13. Now, I have the previous problems that I said in previous post. please guide me.

    https://1drv.ms/u/s!AkPwfyenywG7a2mpjGj6lgHHLpA

    thanks


    • Edited by nadianaji Monday, December 25, 2017 7:11 AM
    Monday, December 25, 2017 7:11 AM
  • Hello nadianaji,

    Thanks for sharing the file. I have tried to adjust and test my project according to the file.

    For your problem.

    1. I did not get the issue. What's the exception you get? What's the error message?

    2. How would you want to color the cells? I tried to color the cells according to your previous code. After filtering, only data rows correspond to the conditions are shown then I colored all the data rows. You could try to adjust the code for your need.

    3. You could get rows count from target worksheet and then minus 1 (title row), the result is number of each part.

    4. As your previous code, use SaveAs() Function.

    5. What do you mean the first row is not correct? Are the columns not the columns you want to copy?

    6,7 It's out of the scope of your original issue. I would suggest you mark helpful reply to close current thread and post a new thread for the two points. Thanks for understanding.

    Best Regards,

    Terry


    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.

    Monday, December 25, 2017 9:24 AM
  • I have a new problem. is there any way to filter special columns base on their heading. for example in previous code you write this: SourceWorksheet.Columns.AutoFilter(25, "1"); but if I want to filter base on column name instead column number, what should I do? please explain me.

    thanks.

    Monday, December 25, 2017 11:12 AM
  • Hello nadianaji,

    For the issue, I would suggest you use WorksheetFunction.Match to get the column index of the column name and then still use the column index to do filter.

    Since this issue is out of your original issue, I would suggest you mark useful reply to close current thread. If you still has issue how to use the match function, please feel free to post new thread for new issue.

    By the way, for your new thread

    https://social.msdn.microsoft.com/Forums/office/en-US/1bada3f5-1eb4-42b9-b0da-8579adf4e14d/how-can-i-invisible-all-combobox-instead-current-combobox-in-c?forum=exceldev 

    Please clarify your scenario more detailed so we could try to reproduce your issue.

    Thanks for understanding.

    Best Regards,

    Terry


    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, December 26, 2017 1:33 AM
  • Hello nadianaji,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer or provide your solution and mark as answer to close this thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    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, December 26, 2017 9:16 AM