.net读取Excel 并返回Datatable

小贤820 2014-02-25 12:30:56

Excel.Application xApp = new Excel.Application(); //引用Excel对象
xApp.Visible = false;
Excel.Workbook xBook = xApp.Workbooks._Open(filePath,
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];//读取第一个Sheets 工作簿
int rowsint = xSheet.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = xSheet.UsedRange.Cells.Columns.Count;//得到列数
//关闭进程
xSheet = null;
xBook.Close(Missing.Value, Missing.Value, Missing.Value);
xBook = null;
xApp.Quit();
xApp = null;

我现在可以得到行数和列数，但是我希望得到Datable 循环就别说了！
Microsoft.Office.Interop.Excel 方法有没有直接返回DataTable的;

...全文

201 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

小贤820 2014-02-25

打赏
举报

引用 11 楼 liuchaolin 的回复:

NPOI http://wenku.baidu.com/link?url=aeWjuFwwKRdj0QUxpOFzWS1nW36SOOWoXVSHrJAMd0C4hx34gq2WOgvpvnXMm_0BhKaPmlVRlNhrf0FFscntiT3wvawwuRZ9AH4j7D3rjpS

我想问的是微软自带的Microsoft.Office.Interop.Excel 有没有哪个属性是可以直接转换成DataTable的！

md5e 2014-02-25

打赏
举报

NPOI http://wenku.baidu.com/link?url=aeWjuFwwKRdj0QUxpOFzWS1nW36SOOWoXVSHrJAMd0C4hx34gq2WOgvpvnXMm_0BhKaPmlVRlNhrf0FFscntiT3wvawwuRZ9AH4j7D3rjpS

md5e 2014-02-25

打赏
举报

/// <summary>读取excel 249 /// 默认第一行为标头 250 /// </summary> 251 /// <param name="strFileName">excel文档路径</param> 252 /// <returns></returns> public static DataTable Import(string strFileName)

小贤820 2014-02-25

打赏
举报

引用 7 楼 u013100360 的回复:

Excel 输入导入到DataTable，免费的Excel控件。 http://www.e-iceblue.com/Introduce/free-xls-component.html
 Workbook workbook = new Workbook();
	             
 workbook.LoadFromFile(@"..\..\parts.xls",ExcelVersion.Version97to2003);
 //Initialize worksheet
 Worksheet sheet = workbook.Worksheets[0];
 DataTable dataTable = sheet.ExportDataTable();

我引用的是 Microsoft.Office.Interop.Excel 不知道这方法有没有直接返回DataTable的属性！

小贤820 2014-02-25

打赏
举报

引用 6 楼 qy1116 的回复:

 public DataTable GetExcelTable(string uploadPath, string sheetName)
    {
        DataTable myTable = new DataTable();
        string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1  F2  F3
        using (OleDbConnection Conn = new OleDbConnection(Xls_ConnStr))
        {
            try
            {
                Conn.Open();
                string sql_str = "select * from [{0}$]";
                sql_str = string.Format(sql_str, sheetName);
                OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
                da.Fill(myTable);
                Conn.Close();
            }
            catch (Exception ex)
            {
                if (Conn != null)
                    Conn.Close(); 
                writeLog(ex.Message, "");
                myTable = null;
            }
        }
        return myTable;
    }

0.0 这种方式不能用， Microsoft.Office.Interop.Excel 有没有直接返回一个Datatable的！

bigstone01 2014-02-25

打赏
举报

Excel 输入导入到DataTable，免费的Excel控件。 http://www.e-iceblue.com/Introduce/free-xls-component.html

 Workbook workbook = new Workbook();
	             
 workbook.LoadFromFile(@"..\..\parts.xls",ExcelVersion.Version97to2003);
 //Initialize worksheet
 Worksheet sheet = workbook.Worksheets[0];
 DataTable dataTable = sheet.ExportDataTable();

好基友一被子 2014-02-25

打赏
举报

 public DataTable GetExcelTable(string uploadPath, string sheetName)
    {
        DataTable myTable = new DataTable();
        string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1  F2  F3
        using (OleDbConnection Conn = new OleDbConnection(Xls_ConnStr))
        {
            try
            {
                Conn.Open();
                string sql_str = "select * from [{0}$]";
                sql_str = string.Format(sql_str, sheetName);
                OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
                da.Fill(myTable);
                Conn.Close();
            }
            catch (Exception ex)
            {
                if (Conn != null)
                    Conn.Close(); 
                writeLog(ex.Message, "");
                myTable = null;
            }
        }
        return myTable;
    }

小贤820 2014-02-25