C#可实现简单导入导出原转载请说明

最新推荐文章于 2022-12-23 10:03:32 发布

原创最新推荐文章于 2022-12-23 10:03:32 发布 · 置顶 · 355 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#C#

C#导入导出专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用C#实现的数据库表格与Excel文件之间的导入导出方法。通过SQL查询从数据库中导出指定的数据到Excel，并将Excel文件中的数据批量导入到数据库表格中。文章详细解释了代码实现过程，包括SQL查询语句的构造、数据表列名的修改、Excel文件的保存及读取等关键步骤。

   需要添加DBHelper和Excel以及引用其他的导入导出文件


/// <summary>
    /// 导出方法
    /// </summary>
    /// <param name="tablename"></param>
    /// <returns></returns>
    public FileResult DaoChu1(string tablename)
    {
        return DaoChu(tablename, "biaoName", "biaoSex", "biaoAge");
    }
    /// <summary>
    /// 导出功能
    /// </summary>
    /// <returns></returns>
    public FileResult DaoChu(string tablename, string cloum1, string cloum2, string cloum3)
    {
        string SqlStr = "SELECT biaoName,biaoSex,biaoAge  from " + tablename + " WHERE 1=1";//想要获取那些数据就查询那些数据
        var dt = DBHelper.GetDataTable(SqlStr);
        dt.Columns[cloum1].ColumnName = "名字";
        dt.Columns[cloum2].ColumnName = "性别";
        dt.Columns[cloum3].ColumnName = "年龄";

        var name = DateTime.Now.ToString("yyyyMMddHHmmssfff");
        var path = Excel.SaveExcel(dt, name, "头像");
        return File(path, "text/plain", name + ".xlsx");
    }

    /// <summary>
    /// 导入方法
    /// </summary>
    /// <returns></returns>
    public void UploadFile(string table)
    {

        UpLoadExcel(table);
    }
    public void UpLoadExcel(string tablename)//必须与数据库中的字段相同   上传
    {
        for (int i = 0; i < Request.Files.Count; i++)
        {
            var item = Request.Files[i];
            var FileName = item.FileName;  //文件名
            var SubName = FileName.Substring(FileName.LastIndexOf(".")); //扩展名
            IWorkbook wb = null;
            if (SubName.Equals(".xlsx"))
            {
                wb = new XSSFWorkbook(item.InputStream);
            }
            else if (SubName.Equals(".xls"))
            {
                wb = new HSSFWorkbook(item.InputStream);
            }
            ISheet sh = wb.GetSheetAt(0);  //获取表
            IRow rows = sh.GetRow(1);   //获取行
            for (int k = 1; k <= sh.LastRowNum; k++)   //循环行
            {
                rows = sh.GetRow(k);
                string sql = string.Format("insert into " + tablename + " values ( ");
                //string sql1 = string.Format("insert into Preson values ('{0}','{1}','{2}')");
                for (int j = 0; j < rows.LastCellNum; j++)  //循环列
                {
                    try
                    {
                        string value = rows.GetCell(j).ToString();
                        sql += string.Format("'" + value.ToString() + "',");
                    }
                    catch
                    {
                    }
                }
                sql = sql.Substring(0, sql.Length - 1) + ")";
                DBHelper.ExecuteNonQuery(sql);
            }
        }
        Response.Write("<script>alert('导入成功!');location.href='/Home/Show'</script>");
    }

    <div>//Csml
        <form action="/Home/UploadFile?table=biao" method="post" enctype="multipart/form-data">
            <table>
                <tr>
                    <td><input type="file" id="Excel" name="Excel" class="btn-primary" /></td>
                    <td>&nbsp;&nbsp;</td>
                    <td><input type="submit" class="btn-primary" value="文件导入" /></td>
                </tr>
            </table>
        </form>
        <a href="/Home/DaoChu1?tablename=biao">导出</a>
    </div>