ImportExcelToDataTable

最新推荐文章于 2023-10-14 11:40:08 发布

lyg__wangjian

最新推荐文章于 2023-10-14 11:40:08 发布

阅读量3.3k

点赞数

分类专栏： ASP.NET 文章标签： dataset excel null string properties exception

本文链接：https://blog.csdn.net/lyg__wangjian/article/details/6411712

版权

ASP.NET 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

private DataTable importExcelToDT(string FilePath)
    {
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties='Excel 8.0; IMEX=1'";
        OleDbConnection conn = new OleDbConnection(strConn);
        OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [ItemLocation$]", strConn);
        DataSet myDataSet = new DataSet();
        try
        {
            myCommand.Fill(myDataSet);

            // 取出数据后删除文件
            // 删除已存在的文件
            if (File.Exists(FilePath))
            {
                File.Delete(FilePath);
            }
        }
        catch
        {

        }
        return myDataSet.Tables[0];
    }

private DataTable ImportExcelToDataTable(string tempFileName)
{
DataTable dt = new DataTable();

        //创建Excel对象
        Excel.Application excel = new Excel.Application();
        DateTime afterTime = DateTime.Now;
        excel.EnableEvents = false;
        excel.DisplayAlerts = false;
        excel.Visible = false;

        Excel.Workbook wBook = excel.Workbooks.Open(Server.MapPath("~/Temp/" + tempFileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        int rowCount = ((Excel.Worksheet)excel.ActiveSheet).UsedRange.Rows.Count;
        int colCount = ((Excel.Worksheet)excel.ActiveSheet).UsedRange.Columns.Count;

        try
        {
            string cellContent;

            for (int iRow = 1; iRow <= rowCount; iRow++)
            {
                DataRow dr = dt.NewRow();
                for (int iCol = 1; iCol <= colCount; iCol++)
                {
                    cellContent = (((Excel.Range)excel.Cells[iRow, iCol]).Value != null) ? ((Excel.Range)excel.Cells[iRow, iCol]).Value.ToString() : "";
                    if (iRow == 1)
                    {
                        dt.Columns.Add(cellContent);
                    }
                    else
                    {
                        dr[iCol - 1] = cellContent;
                    }
                }
                if (iRow != 1)
                    dt.Rows.Add(dr);
            }
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            wBook.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Application.Quit();
            excel.Quit();