• C#读取Excel导入到数据库及读取Excel工作表为任意表名的方法


    添加openFileDialog1,用于选择Excel表

    using System.IO;
    using System.Data.OleDb;

    //导入Excel表
    private void btnInto_Click(object sender, EventArgs e)
    {

    string resultFile = "";
    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    openFileDialog1.Filter = "表格文件(*.xls,*.xlsx)|*.xls;*.xlsx";
    openFileDialog1.FilterIndex = 2;
    openFileDialog1.RestoreDirectory = true;
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
    resultFile = openFileDialog1.FileName;

    GetExcel(resultFile);

    }
    }
    /// <summary>
    /// 获取Excel表格内容
    /// </summary>
    /// <param name="fileName">Excel表名字</param>
    private void GetExcel(string fileName)
    {

    根据表名创建链接字符串
    string excelStr = "Provider= Microsoft.Ace.OleDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

    string strSheetName = GetExcelFirstTableName(fileName);//获取第一个工作表名字
    if (strSheetName!=null)
    {

    DataTable dt = new DataTable();

    using (System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter("select * from[" + strSheetName + "]", excelStr))
    {
    try
    {
    myCommand.Fill(dt);

    }
    catch (Exception ex)
    {

    MessageBox.Show("操作失败" + ex.Message);
    }

    }
    dgvShow.DataSource = dt;


    }
    else
    {
    MessageBox.Show("这是张空表!");
    }

    }


    /// <summary>
    /// 获取excel第一个工作表名字
    /// </summary>
    /// <param name="excelFileName">Excel表名字</param>
    /// <returns></returns>
    public static string GetExcelFirstTableName(string excelFileName)
    {
    string tableName = null;
    if (File.Exists(excelFileName))
    {
    string excelStr = "Provider= Microsoft.Ace.OleDB.12.0;Data Source=" + excelFileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
    using (OleDbConnection conn = new OleDbConnection(excelStr))
    {
    conn.Open();
    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取table内容
    tableName = dt.Rows[0][2].ToString().Trim();
    }
    }
    return tableName;
    }

  • 相关阅读:
    登录权限
    ajax搜索分页
    dos命令
    tp5单删
    MVC简易封装
    linux环境安装swoole
    nginx环境安装laravel404问题
    ABZ职业规划
    Yii安装curl
    SKU的概念和理解
  • 原文地址:https://www.cnblogs.com/zilinyufeng/p/4138141.html
Copyright © 2020-2023  润新知