C#读取Excel文件(*.xls)|*.xls（2种方法）

---

1.

---

public static DataTable ImportExcel()
{
    XlsDocument document = null;
    OpenFileDialog dialog = new OpenFileDialog {
        Title = "导入Excel",
        Filter = "Excel文件(*.xls)|*.xls"
    };
    if (dialog.ShowDialog() == DialogResult.OK)
    {
        try
        {
            document = new XlsDocument(dialog.FileName);
        }
        catch
        {
        }
    }
    else
    {
        return null;
    }
    try
    {
        Worksheet worksheet = document.Workbook.Worksheets[0];
        ushort cellCount = worksheet.Rows[1].CellCount;
        DataTable table = new DataTable();
        int num2 = 1;
        while (num2 <= cellCount)
        {
            string columnName = worksheet.Rows[1].GetCell(ushort.Parse(num2.ToString())).Value.ToString();
            table.Columns.Add(columnName);
            num2++;
        }
        for (int i = 2; i < worksheet.Rows.Count; i++)
        {
            DataRow row = table.NewRow();
            for (num2 = 1; num2 <= cellCount; num2++)
            {
                string str2 = worksheet.Rows[ushort.Parse(i.ToString())].GetCell(ushort.Parse(num2.ToString())).Value.ToString();
                row[num2 - 1] = str2;
            }
            table.Rows.Add(row);
        }
        return table;
    }
    catch
    {
        return null;
    }
}

 

 
 1 0x1public static DataTable ImportExcel()
{
    XlsDocument document = null;
    OpenFileDialog dialog = new OpenFileDialog {
        Title = "导入Excel",
        Filter = "Excel文件(*.xls)|*.xls"
    };
    if (dialog.ShowDialog() == DialogResult.OK)
    {
        try
        {
            document = new XlsDocument(dialog.FileName);
        }
        catch
        {
        }
    }
    else
    {
        return null;
    }
    try
    {
        Worksheet worksheet = document.Workbook.Worksheets[0];
        ushort cellCount = worksheet.Rows[1].CellCount;
        DataTable table = new DataTable();
        int num2 = 1;
        while (num2 <= cellCount)
        {
            string columnName = worksheet.Rows[1].GetCell(ushort.Parse(num2.ToString())).Value.ToString();
            table.Columns.Add(columnName);
            num2++;
        }
        for (int i = 2; i < worksheet.Rows.Count; i++)
        {
            DataRow row = table.NewRow();
            for (num2 = 1; num2 <= cellCount; num2++)
            {
                string str2 = worksheet.Rows[ushort.Parse(i.ToString())].GetCell(ushort.Parse(num2.ToString())).Value.ToString();
                row[num2 - 1] = str2;
            }
            table.Rows.Add(row);
        }
        return table;
    }
    catch
    {
        return null;
    }
}

 

**

2.

**

  //获取字典内容
        private void simpleButton5_Click(object sender, EventArgs e)
        {
            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "工作薄(*.xlsx)|*.xlsx|工作薄(*.xls)|*.xls|所有文件(*.*)|*.*";
            if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
            {
                ExcelToDS(openfile.FileName, "DEPT");
            }
        }

  public DataTable ExcelToDS(string path, string type)
        {
            //try
            //{
            string path2 = path;
            //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path2 + ";Extended Properties=Excel 12.0;"; 
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path2 + ";Extended Properties='Excel 12.0; HDR=yes; IMEX=1'";
            //string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + path + "; Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;

            if (type == "DEPT")
            {
                strExcel = @"select [编码],	[名称],	[说明],'0' as color from [CT08.00.002 科室代码$]";
            }
            else if (type == "YPJX")
            {
                strExcel = @"select [编码],	[名称],	[说明],'0' as color from [CV08.50.002药物剂型代码$]";
            }
            else if (type == "YYPL")
            {
                strExcel = @"select [编码],	[名称],	[说明],'0' as color from [用药频率$]";
            }
            else if (type == "YYFF")
            {
                strExcel = @"select [编码],	[名称],	[说明],'0' as color from [CV06.00.102用药途径代码$]";
            }
            else if (type == "YWLX")
            {
                strExcel = @"select [编码],	[名称],	[说明],'0' as color from [CV5301.06药物类型代码$]";
            }else if(type=="JJJC")
            {
                strExcel = @"select [编码],	[名称],	[说明],'0' as color from [CV5199.01检验检查类别代码$]";
            }

            myCommand = new OleDbDataAdapter(strExcel, strConn);
            DataTable table1 = new DataTable();
            ds = new DataSet();
            myCommand.Fill(table1);
           return  table1;
            }