none
How to read irregular cells in Excel after merged? RRS feed

Answers

  • Don't use ADO to read.

    Actually you can try this library, after add reference  then add:

               Workbook workbook=new Workbook();
                workbook.LoadFromFile("test.xlsx");
                Worksheet sheet = workbook.Worksheets[0];
                sheet.Range["A1:C3"].Merge();
                string str = sheet.Range["A1"].Value.ToString();
                Console.WriteLine(str);
                Console.ReadKey();

    Wednesday, April 8, 2015 3:10 AM
  • Hi Floraaa,

    Your code will always return a DataTable to you, the only difference is that it'll only fill the first cell of the merged cells with data, but other cells will be empty. Maybe what you want is that all the cells should have the same value.

    If you have Office application installed on your machine, I recommend that you automate Excel with Excel PIA, fill the DataTable by iterating all the cells:

    How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

    Try to use the Range.MergeCells property to determine if the range contains the merged cells

    Range.MergeCells property

    Sample code here:

    excelApplication = new Application();   
                    //Opening/Loading the workBook in memory   
                    excelWorkBook = excelApplication.Workbooks.Open(EXCEL_PATH);   
       
                    //retrieving the worksheet counts inside the excel workbook   
                    int workSheetCounts = excelWorkBook.Worksheets.Count;   
                    int totalColumns = 0;   
                    Range objRange = null;   
       
                    for (int sheetCounter = 1; sheetCounter <= workSheetCounts; sheetCounter++)   
                    {   
                        Worksheet workSheet = excelWorkBook.Sheets[sheetCounter];   
       
                        totalColumns = workSheet.UsedRange.Cells.Columns.Count + 1;   
       
                        object[] data = null;   
       
                        //Iterating from row 2 because first row contains HeaderNames   
                        for (int row = 2; row < workSheet.UsedRange.Cells.Rows.Count; row++)   
                        {   
                            data = new object[totalColumns - 1];   
       
                            for (int col = 1; col < totalColumns; col++)   
                            {   
                                objRange = workSheet.Cells[row, col];   
                                if (objRange.MergeCells)   
                                {   
                                    data[col - 1] = Convert.ToString(((Range)objRange.MergeArea[1, 1]).Text).Trim();   
                                }   
                                else   
                                {   
                                    data[col - 1] = Convert.ToString(objRange.Text).Trim();   
                                }   
                            }   
                            AddRow(data);   
                        }   
                    } 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, April 9, 2015 2:15 AM
    Moderator

All replies

  • Your code please? Which way you have tried?
    Wednesday, April 8, 2015 3:06 AM
  • Your code please? Which way you have tried?
     DataTable dtGBPatient = new DataTable();
    
                string strConn;
    
                string excelName;
    
                string excelPath;
    
                OpenFileDialog fileDlg = new OpenFileDialog();           
    
                fileDlg.Multiselect = false;
    
                fileDlg.Filter = "所有文件(*.*)|*.*";//"excel 2003 文件(*.xls)|*.xls|excel 2007 文件 (*.xlsx)|*.xlsx";
                if (fileDlg.ShowDialog() == DialogResult.OK)
                {
                    excelPath = fileDlg.FileName;
    
                  //  excelPath.inde
               
                strConn = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '{0}'; Extended Properties = 'Excel 8.0; HDR = YES; IMEX = 1';";
    
                string strConnection = string.Format(strConn, excelPath);
    
                OleDbConnection conn = new OleDbConnection(strConnection);
    
                conn.Open(); 
              //  excelName= GetFirstSheetNameFromExcelFileName(excelPath,0);
    
                excelName = "The note list of Container";
                OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + excelName + "$]", strConnection);
    
                dtGBPatient.TableName = "gbPatientInfo";
    
                oada.Fill(dtGBPatient);
    
                conn.Close();
    

    Can't read irregular excel. Please help to solve.
    Wednesday, April 8, 2015 3:07 AM
  • Don't use ADO to read.

    Actually you can try this library, after add reference  then add:

               Workbook workbook=new Workbook();
                workbook.LoadFromFile("test.xlsx");
                Worksheet sheet = workbook.Worksheets[0];
                sheet.Range["A1:C3"].Merge();
                string str = sheet.Range["A1"].Value.ToString();
                Console.WriteLine(str);
                Console.ReadKey();

    Wednesday, April 8, 2015 3:10 AM
  • Hi Floraaa,

    Your code will always return a DataTable to you, the only difference is that it'll only fill the first cell of the merged cells with data, but other cells will be empty. Maybe what you want is that all the cells should have the same value.

    If you have Office application installed on your machine, I recommend that you automate Excel with Excel PIA, fill the DataTable by iterating all the cells:

    How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

    Try to use the Range.MergeCells property to determine if the range contains the merged cells

    Range.MergeCells property

    Sample code here:

    excelApplication = new Application();   
                    //Opening/Loading the workBook in memory   
                    excelWorkBook = excelApplication.Workbooks.Open(EXCEL_PATH);   
       
                    //retrieving the worksheet counts inside the excel workbook   
                    int workSheetCounts = excelWorkBook.Worksheets.Count;   
                    int totalColumns = 0;   
                    Range objRange = null;   
       
                    for (int sheetCounter = 1; sheetCounter <= workSheetCounts; sheetCounter++)   
                    {   
                        Worksheet workSheet = excelWorkBook.Sheets[sheetCounter];   
       
                        totalColumns = workSheet.UsedRange.Cells.Columns.Count + 1;   
       
                        object[] data = null;   
       
                        //Iterating from row 2 because first row contains HeaderNames   
                        for (int row = 2; row < workSheet.UsedRange.Cells.Rows.Count; row++)   
                        {   
                            data = new object[totalColumns - 1];   
       
                            for (int col = 1; col < totalColumns; col++)   
                            {   
                                objRange = workSheet.Cells[row, col];   
                                if (objRange.MergeCells)   
                                {   
                                    data[col - 1] = Convert.ToString(((Range)objRange.MergeArea[1, 1]).Text).Trim();   
                                }   
                                else   
                                {   
                                    data[col - 1] = Convert.ToString(objRange.Text).Trim();   
                                }   
                            }   
                            AddRow(data);   
                        }   
                    } 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, April 9, 2015 2:15 AM
    Moderator