从c＃中获取excel的所有值 [英] get all the value from excel in c#

问题描述

嗨


i有excel文件路径，我知道头名称（即sql中的列名）.how从excel获取所有值？

Hi
i have the excel file path and i know the header names (ie is column names in sql).how to get all the values from excel?

推荐答案

参见使用MS Excel（xls / xlsx）使用MDAC和Oledb [ ^ ]。

您可以使用Microsoft.Office读取Excel文件。 Interop.Excel;

使用Microsoft.Office.Interop.Excel的基本概念是您将读取范围内的数据。即从单元格A1到C25读取数据。此范围之间的所有数据将被视为表（二维数组）。您可以轻松选择范围。





这是示例代码，可以帮助您。这不是完整的代码，但它会对你有所帮助。

希望你觉得这很有用。

You can read Excel file using Microsoft.Office.Interop.Excel;
Basic concept using Microsoft.Office.Interop.Excel is that you will read data in range. i.e read data from cell A1 to C25. All that data between this range will be treated as table (2 dimensional array). You can easily play with selected range.


Here is sample code, which can help you. It is not complete code, but it will help you lot.
Hope you find this helpful.

 using Excel = Microsoft.Office.Interop.Excel;     //use namespace
       
        Excel.Application excelApp = null;
        Excel.Workbook excelWorkBk = null;
        Excel.Worksheet excelSheet = null;
        Excel.Worksheet excelSheet2 = null;
        Excel.Range excelRange = null;


 public bool OpenExcelFile(string filepath, object password)
        {
            if (password == null)
            {
                password = Missing.Value;
            }
            excelApp = new Excel.Application();
            excelApp.Visible = false;
            excelApp.DisplayAlerts = false;
            try
            {
                excelWorkBk = excelApp.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value, password, password, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                excelSheet = (Excel.Worksheet)excelWorkBk.ActiveSheet;
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

///--------------------------------------------------------------------------

   public void ReadExcel()
        {
		  if (OpenExcelFile("FileName.xls", null))
                {
                    try
                    {
                        for (int row = 6; row <= excelSheet.UsedRange.Rows.Count; row++)
                        {
                            excelRange = excelSheet.Range[excelSheet.Cells[row, 1], excelSheet.Cells[row, excelSheet.UsedRange.Columns.Count]];
                            object[,] ObjctrlList = (object[,])excelRange.Value2;

                            if (ObjctrlList[1, 2] != null)        //   Control Name (description) 	//where [1,2] ==>1=row, 2=column(cell);
                            {
                            string    window_id = ObjctrlList[1, 7].ToString();          //Window Id
                            string    control_id = ObjctrlList[1, 8].ToString();         //Control Id
                            string    control_type = ObjctrlList[1, 6].ToString();        //Control Type
                            string  value1= ObjctrlList[1, 11].ToString();
							}
                        }
                    }
                    catch (Exception ex)
                    {
                       MessageBox.Show(ex.Message);
                       
                    }
                    finally
                    {
                        // Clean up
                        // NOTE: When in release mode, this does the trick
                        if (excelWorkBk != null)
                        {
                            excelWorkBk.Close(Type.Missing, Type.Missing, Type.Missing);
                            Marshal.ReleaseComObject(excelWorkBk);
                        }
                        if (excelSheet != null)
                        {
                            Marshal.ReleaseComObject(excelSheet);
                        }
                        if (excelApp != null)
                        {
                            Marshal.ReleaseComObject(excelApp);
                        }
                        excelSheet = null;
                        excelRange = null;
                        excelWorkBk = null;
                        GC.WaitForPendingFinalizers();
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                        GC.Collect();
                    }
                }
        }

这篇关于从c＃中获取excel的所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！