C#_.NetFramework_Web项目_EXCEL数据导入

【推荐阅读我的最新的core版文章，是最全的介绍：c#_.netcore_web项目_excel数据导出】

 

 

需要引用npoi的nuget包：npoi-v2.4.1

 

b-1：excel数据导入--c#获取数据：

 

/// <summary>
        /// excel帮助类
        /// </summary>
        /// <typeparam name="t">泛型类</typeparam>
        /// <typeparam name="tcollection">泛型类集合</typeparam>
        public class excelhelp<t, tcollection> where t : new() where tcollection : list<t>, new()
        {
            //http请求request对象
            public static httprequest baserequest = httpcontext.current.request;
            //http请求response对象
            public static httpresponse baseresponse = httpcontext.current.response;
            /// <summary>
            /// 将数据导出excel
            /// </summary>
            /// <param name="columnnameandshownamedic">列名+显示名</param>
            /// <param name="tcoll">数据集（tcoll里的类属性名必须和字典中的列名一致）</param>
            public static void exportexceldata(dictionary<string, string> columnnameandshownamedic, tcollection tcoll)
            {
                iworkbook workbook = new hssfworkbook();
                isheet worksheet = workbook.createsheet("sheet1");

                list<string> columnnamelist = columnnameandshownamedic.keys.tolist();
                list<string> shownamelist = columnnameandshownamedic.values.tolist();
                //设置首列显示
                irow row1 = worksheet.getrow(0);
                icell cell = null;
                for (var i = 0; i < columnnamelist.count; i++)
                {
                    cell = row1.createcell(i);
                    cell.setcellvalue(columnnamelist[i]);
                }

                dictionary<int, propertyinfo> indexpropertydic = getindexpropertydic(columnnamelist);

                for (int i = 0; i < tcoll.count; i++)
                {
                    row1 = worksheet.getrow(i + 1);
                    for (int j = 0; j < indexpropertydic.count; j++)
                    {
                        cell = row1.createcell(i);
                        cell.setcellvalue(indexpropertydic[j].getvalue(tcoll[i]).tostring());
                    }
                }
                
                memorystream ms = new memorystream();
                workbook.write(ms);

                byte[] buffer = ms.getbuffer();

                baseresponse.clear();
                baseresponse.buffer = true;
                baseresponse.contentencoding = system.text.encoding.utf8;
                //baseresponse.contenttype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                baseresponse.contenttype = "application/vnd.ms-excel";
                //设置导出文件名
                baseresponse.addheader("content-disposition", "attachment;  filename=" + "maintainreport" + ".xlsx");
                baseresponse.addheader("content-length", buffer.length.tostring());

                baseresponse.binarywrite(buffer);
                baseresponse.flush();
                baseresponse.end();
            }
            /// <summary>
            /// 根据属性名顺序获取对应的属性对象
            /// </summary>
            /// <param name="fieldnamelist"></param>
            /// <returns></returns>
            private static dictionary<int, propertyinfo> getindexpropertydic(list<string> fieldnamelist)
            {
                dictionary<int, propertyinfo> indexpropertydic = new dictionary<int, propertyinfo>(fieldnamelist.count);
                list<propertyinfo> tpropertyinfolist = typeof(t).getproperties().tolist();
                propertyinfo propertyinfo = null;
                for (int i = 0; i < fieldnamelist.count; i++)
                {
                    propertyinfo = tpropertyinfolist.find(m => m.name.equals(fieldnamelist[i], stringcomparison.ordinalignorecase));
                    indexpropertydic.add(i, propertyinfo);
                }

                return indexpropertydic;
            }
        }