天天看点

asp.net用npoi读取ecxel数据到oracle表1.html部分  2.javascript3.后台代码4.数据库表

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)
);
           

继续阅读