C# # MVC #分部页# 导入#试题

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using System.IO;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace WebApplication1.Controllers
{
    public class DefaultController : Controller
    {
        // GET: Default
        public ActionResult Index()
        {

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString))//连接对象
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand("select Topic.*,ExamType.TypeName from Topic join ExamType on ExamType.Id=Topic.TId", conn);
                SqlDataAdapter sqlData = new SqlDataAdapter(cmd);
                DataTable data = new DataTable();
                sqlData.Fill(data);

                return View(data );
            }
        }
        [HttpPost]
        public ActionResult Import() {
            //1.接收文件
            HttpPostedFileBase fileBase = Request.Files["fileExcel"];
            //2.判断是否上传文件
            if (fileBase==null)
            {
                Response.Write("<script>alert('请上传文件')</script>");
                return null ;
            }
            //3.文件是否是excl
            //4.文件转数据流
            Stream stream = fileBase.InputStream;

            //5.数据流转工作簿 数据库
            IWorkbook workbook = null;
            if (Path.GetExtension(fileBase.FileName).ToLower().Equals(".xls"))
            {
                workbook = new HSSFWorkbook(stream);
            }
            if (Path.GetExtension(fileBase.FileName).ToLower().Equals(".xlsx"))
            {
                workbook = new XSSFWorkbook(stream);
            }

            //6.获取sheet  数据表
            ISheet sheet = workbook.GetSheetAt(0);
            //7.获取sheet头
            IRow headRow = sheet.GetRow(0);

            //8.定义内存表DataTable
            DataTable dt = new DataTable();
            //9.DataTable加列（来自sheet头）
            foreach (ICell item in headRow.Cells)
            {
                dt.Columns.Add(item.StringCellValue);
            }
            //10.遍历sheet每一行添加到DataTable
            for (int i = 1; i <=sheet.LastRowNum; i++)//遍历行
            {
                //创建行
                DataRow dr = dt.NewRow();
                for (int j = 0; j < sheet.GetRow(i).Cells.Count; j++)//遍历列
                {
                    //获取每个单元格的类型
                    CellType cellType = sheet.GetRow(i).Cells[j].CellType;
                    //获取datatable索引
                    int columnIndex = sheet.GetRow(i).Cells[j].ColumnIndex;
                    switch (cellType)
                    {
                        case CellType.Numeric:dr[columnIndex] = sheet.GetRow(i).Cells[j].NumericCellValue;break;
                        case CellType.Boolean: dr[columnIndex] = sheet.GetRow(i).Cells[j].BooleanCellValue; break;
                        case CellType.String: dr[columnIndex] = sheet.GetRow(i).Cells[j].StringCellValue; break;
                    }
                }                                             
                dt.Rows.Add(dr);//行追加到dt
            }

            //11.遍历DataTable拼接sql语句
            
            using (SqlConnection conn=new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString) )//连接对象
            {
                if (conn.State==ConnectionState.Closed)
                {
                    conn.Open();
                }

                using (SqlTransaction trans=conn.BeginTransaction())
                {
                    try
                    {
                        //命令对象
                        SqlCommand command = conn.CreateCommand();
                        command.Transaction = trans;
                        foreach (DataRow item in dt.Rows)//行
                        {
                            command.CommandText = "insert into Topic values(";
                            foreach (object it in item.ItemArray)//列
                            {
                                string value = it == null ? "" : it.ToString();
                                if (value.Equals("1-单选")|| value.Equals("2-多选") || value.Equals("0-判断"))
                                {
                                    command.CommandText += $"(select Id from ExamType where TypeName='{value}'),";
                                    continue;
                                }
                                command.CommandText += $"'{it}',";
                            }
                            command.CommandText = command.CommandText.TrimEnd(',') + ")";
                            command.ExecuteNonQuery();
                        }
                        //12.执行sql
                        trans.Commit();


                    

                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally {
                        trans.Dispose();
                        conn.Dispose();
                    }                          
                }
            }



            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString))//连接对象
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand("select Topic.*,ExamType.TypeName from Topic join ExamType on ExamType.Id=Topic.TId", conn);
                SqlDataAdapter sqlData = new SqlDataAdapter(cmd);
                DataTable data = new DataTable();
                sqlData.Fill(data);

                return PartialView("_PartialPage1", data);
            }
        }
    }
}

@{
    ViewBag.Title = "Index";
}
@using System.Data
@model DataTable
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>

<h2>Index</h2>

@using (Ajax.BeginForm("Import", null, new AjaxOptions { UpdateTargetId = "divData" }, new { enctype = "multipart/form-data" }))
{
    <input id="File1" type="file" name="fileExcel" />
    <input id="Submit1" type="submit" value="导入" class="btn-primary btn" />
}
<div id="divData">
    @Html.Partial("_PartialPage1", Model)
</div>

@using System.Data
@model DataTable
<table class="table-bordered table">
    <tr>
        <th>知识点</th>
        <th>类型</th>
        <th>题干</th>
        <th>选项A</th>
        <th>选项B</th>
        <th>选项C</th>
        <th>选项D</th>
        <th>答案</th>
        <th>出题人</th>
    </tr>
    @foreach (DataRow item in Model.Rows)
    {
        <tr>
            <td>@item["knowledge"]</td>
            <td>@item["TypeName"]</td>
            <td>@item["TiGan"]</td>
            <td>@item["MenuA"]</td>
            <td>@item["MenuB"]</td>
            <td>@item["MenuC"]</td>
            <td>@item["MenuD"]</td>
            <td>@item["Rsesult"]</td>
            <td>@item["WritingPeople"]</td>
        </tr>
    }
</table>