none
How do we convert Excel template to dataTable in C# RRS feed

  • Question

  • Hi,

    I have client excel template which contains many columns and rows. The row always dynamic.

    some of the columns has merged in the attached excel sheet (Ex. Remarks column (it merged almost 5 columns and becomes one column finally i.e (X12 : AC12).

    I want to get all the columns which is the row starting from Row no : 12.

    meanwhile i want to fetch all the rows in the first columns.

    My final output columns should be : A,B,C,D.........VR14 (Excel  column and row range starts from  A12 : BW12)

    How do i convert this template into data table..

    Herewith attached excel template.  ---  Test File - Client Template




    • Edited by Gani tpt Tuesday, December 3, 2019 1:21 AM
    Monday, December 2, 2019 11:55 PM

All replies

  • Hi Gani tpt,

    Thank you for posting here.

    For your question, you want to convert excel to datatable.

    Here is a code example.

            private void Form1_Load(object sender, EventArgs e)
            {
                string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =d:\\test\\111.xlsx;Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
                using (OleDbConnection connection = new OleDbConnection(constr))
                {
                    connection.Open();
                 
                    string strCom = " SELECT * FROM [sheet1$]";
    
                    DataTable dataTable = new DataTable();
                    OleDbDataAdapter oleAdpt = new OleDbDataAdapter(strCom, connection); //here we read data from sheet1  
                    oleAdpt.Fill(dataTable); //fill excel data into dataTable  
    
                    for (int i = 0; i < 6; i++)
                    {
                        dataTable.Rows.RemoveAt(i);
                    }
                    dataGridView1.DataSource = dataTable;
                }
            }
    

    You can get all the data first, and then delete the parts you don't need.

    It should be noted that because of the format of your excel file, when you delete more than 6 rows, the data will be garbled.

    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.

    Tuesday, December 3, 2019 5:18 AM
  • Thanks for your help...

    i am getting below error.

    Additional information: The Microsoft Access database engine could not find the object 'EMP'. Make sure the object exists and that you spell its name and the path name correctly. If 'EMP' is not a local object, check your network 

    EMP ==> Sheetname

    some of the column it should merge and it becomes as single column and also my last column and last data row should be identified based on my template..

    How we could do this..?


    • Edited by Gani tpt Tuesday, December 3, 2019 8:10 AM
    Tuesday, December 3, 2019 5:55 AM
  • Hi Gani tpt,

    Thanks for your feedback.

    For the error, you can refer to the following link.

    The Microsoft Access database engine could not find the object ''.

    Office 365: Microsoft Access Database Engine could not find the object

    According to my search, it seems that datatable does not support cell merging.

    And for your last question, datatable can determine which column is the last column.

    For the rows with no data at the bottom of the table, you can use the following code to delete them.

    dt = dt.Rows
         .Cast<DataRow>()
         .Where(row => !row.ItemArray.All(field => field is DBNull ||
                                                      string.IsNullOrWhiteSpace(field as string)))
         .CopyToDataTable();
    

    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.

    Wednesday, December 4, 2019 6:19 AM
  • Thanks for your feedback..

    i am trying to delete top 12 rows. The thing, it will delete only one first 6 row..loop will be executing at all the iteration.

    I tried attached sample template, but not working..

    i want row start from 12 till row filled...

    What is the problem...?

    for (int i =0; i <11; i++)
                    {
                        dataTable.Rows.RemoveAt(i);
                    }



    • Edited by Gani tpt Friday, December 6, 2019 10:18 AM
    Friday, December 6, 2019 9:57 AM