asp.net用npoi读取ecxel数据到oracle表

来源：互联网发布：海地为什么这么穷知乎编辑：程序博客网时间：2024/05/16 06:34

1.html部分

           <div style="margin-bottom: 0px; margin-top: 5px; vertical-align: top;">                    <span style="height: 30px; vertical-align: top;">上传文件：</span> <span id="uploadFile">                        加载中...</span>                </div>

2.javascript

<script type="text/javascript" language="javascript" src="<%= Page.ResolveUrl("~/Scripts/flash/fileupload.js") %>"></script>        <script type="text/javascript">        $(function () {            var params = {                serverUrl: "/Pages/ZHCXPG/ImportAREAExcel.aspx?faid=" + parent.faid,                jsFunction: "callBackFun",                uploadText: "上传文件",                filter: "*.xls;*.xlsx",                maxFileCount: 1            }            initData(params, "uploadFile", 300);        });    </script>

3.后台代码

        protected void Page_Load(object sender, EventArgs e)        {            var model = UploadArchivesFile(Request.Files[0]);            DataTable data = new DataTable();            int startRow = 0;            var filepath = Server.MapPath(GlobalConfig.BasePath + model.FileRelativePath);            var array = new ArrayList();            if (model.success == 200)            {                var fi = new FileInfo(filepath);                var ext = fi.Extension.ToLower();                IWorkbook book = null;                using (var file = new FileStream(filepath, FileMode.Open, FileAccess.Read))                {                    if (ext.Equals(".xlsx"))                    {                        book = new XSSFWorkbook(file);                    }                    else                    {                        book = new HSSFWorkbook(file);                    }                }                var sheet = book.GetSheetAt(0);                if (sheet != null)                {                    IRow firstRow = sheet.GetRow(0);                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                    {                        ICell cell = firstRow.GetCell(i);                        if (cell != null)                        {                            string cellValue = cell.StringCellValue;                            if (cellValue != null)                            {                                DataColumn column = new DataColumn(cellValue);                                data.Columns.Add(column);                            }                        }                    }                    startRow = sheet.FirstRowNum + 1;                    //最后一列的标号                    int rowCount = sheet.LastRowNum;                    for (int i = startRow; i <= rowCount; ++i)                    {                        IRow row = sheet.GetRow(i);                        if (row == null) continue; //没有数据的行默认是null　　　　　　　                        DataRow dataRow = data.NewRow();                        for (int j = row.FirstCellNum; j < cellCount; ++j)                        {                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null                                dataRow[j] = row.GetCell(j).ToString();                        }                        var sql = "insert into Z_GBZ_AREA values(" + i + ",'" + dataRow[0] + "','" + dataRow[1] + "','" + dataRow[2] + "','" + dataRow[3] + "','" + dataRow[4] + "')";                        array.Add(sql);                    }                }                if (array.Count > 0)                {                    DbHelperOra.ExecuteSqlTran(array);                    var retval =                        JsonConvert.SerializeObject(                            new { success = 200, msg = "上传成功", filepath, filename = model.File_Model.FileName });                    Response.Write(retval);                }            }            Response.End();        }

<pre name="code" class="csharp">        public FileMsg UploadArchivesFile(HttpPostedFile httpPostedFileBase)        {            var result = new FileMsg();            if (httpPostedFileBase == null)            {                result.success = -200;                result.msg = "参数无效";                return result;            }            var path = "GBZ/AREA/Excels/";            var fileUpload = new UploadFile("Bg");            var savePath = GlobalConfig.BasePath + path;            var physicalPath = Server.MapPath(savePath);            var fileModel = fileUpload.Save(                httpPostedFileBase,                DateTime.Now.ToString("yyyyMMddHHmmssffffff"),                physicalPath);            if (string.IsNullOrEmpty(fileModel.ErrorString))            {                result.success = 200;                result.FileRelativePath = string.Concat(path, fileModel.FileNewName);                result.File_Model = fileModel;                return result;            }            result.success = -200;            result.msg = fileModel.ErrorString;            return result;        }

4.数据库表

create table Z_GBZ_AREA (   ID                   NUMBER(8)            not null,   XZQDM                VARCHAR2(38),   XZQMCNAME            VARCHAR2(50),   GJJBN                CHAR(1)              default '0',   GBZJB                CHAR(1)              default '0',   GJJPK                CHAR(1)              default '0',   constraint PK_Z_GBZ_AREA primary key (ID));

0 0