在mvc4中上传、导入和导出excel表方法总结

通过excel的导入导出练习，使用NPOI组件还是方便一点，所有下面就以NPOI下的导入导出给出实例，通过网页导入excel表，首先上传，再导入数据到库，这里为了方便就不导入到库中了，直接拿到数据。导出方法比较多。

第一步下载NPOI组件，并在VS中导入dll.

第二步：为了方便，把功能提取成方法:

1 集合与DataTable间的相互转换创建类

public static class DataTableTool
    {
        /// <summary>    

        /// 转化一个DataTable    

        /// </summary>    

        /// <typeparam name="T"></typeparam>    
        /// <param name="list"></param>    
        /// <returns></returns>    
        public static DataTable ToDataTable<T>(this IEnumerable<T> list)
        {

            //创建属性的集合    
            List<PropertyInfo> pList = new List<PropertyInfo>();
            //获得反射的入口    

            Type type = typeof(T);
            DataTable dt = new DataTable();
            //把所有的public属性加入到集合 并添加DataTable的列    
            Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
            foreach (var item in list)
            {
                //创建一个DataRow实例    
                DataRow row = dt.NewRow();
                //给row 赋值    
                pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
                //加入到DataTable    
                dt.Rows.Add(row);
            }
            return dt;
        }


        /// <summary>    
        /// DataTable 转换为List 集合    
        /// </summary>    
        /// <typeparam name="TResult">类型</typeparam>    
        /// <param name="dt">DataTable</param>    
        /// <returns></returns>    
        public static List<T> ToList<T>(this DataTable dt) where T : class, new()
        {
            //创建一个属性的列表    
            List<PropertyInfo> prlist = new List<PropertyInfo>();
            //获取TResult的类型实例  反射的入口    

            Type t = typeof(T);

            //获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表     
            Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });

            //创建返回的集合    

            List<T> oblist = new List<T>();

            foreach (DataRow row in dt.Rows)
            {
                //创建TResult的实例    
                T ob = new T();
                //找到对应的数据  并赋值    
                prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
                //放入到返回的集合中.    
                oblist.Add(ob);
            }
            return oblist;
        }




        /// <summary>    
        /// 将集合类转换成DataTable    
        /// </summary>    
        /// <param name="list">集合</param>    
        /// <returns></returns>    
        public static DataTable ToDataTableTow(IList list)
        {
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();

                foreach (PropertyInfo pi in propertys)
                {
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }


        /**/

        /// <summary>    
        /// 将泛型集合类转换成DataTable    

        /// </summary>    
        /// <typeparam name="T">集合项类型</typeparam>    

        /// <param name="list">集合</param>    
        /// <returns>数据集(表)</returns>    
        public static DataTable ToDataTable<T>(IList<T> list)
        {
            return ToDataTable<T>(list, null);

        }


        /**/

        /// <summary>    
        /// 将泛型集合类转换成DataTable    
        /// </summary>    
        /// <typeparam name="T">集合项类型</typeparam>    
        /// <param name="list">集合</param>    
        /// <param name="propertyName">需要返回的列的列名</param>    
        /// <returns>数据集(表)</returns>    
        public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (propertyName != null)
                propertyNameList.AddRange(propertyName);
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        result.Columns.Add(pi.Name, pi.PropertyType);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                            result.Columns.Add(pi.Name, pi.PropertyType);
                    }
                }

                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(list[i], null);
                            tempList.Add(obj);
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(list[i], null);
                                tempList.Add(obj);
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }  
    }

2 excel导入导出类

public static class ExcelTool
    { /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <param name="fileName">文件路径</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelToDataTable(bool isFirstRowColumn, string fileName, string sheetName = "")
        {
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }
            var data = new DataTable();
            IWorkbook workbook = null;
            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
                {
                    workbook = new HSSFWorkbook(fs);
                }

                ISheet sheet = null;
                if (workbook != null)
                {
                    //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    if (sheetName == "")
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
                    }
                }
                if (sheet == null) return data;
                var firstRow = sheet.GetRow(0);
                //一行最后一个cell的编号 即总的列数
                int cellCount = firstRow.LastCellNum;
                int startRow;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        var cellValue = cell.StringCellValue;
                        if (cellValue == null) continue;
                        var column = new DataColumn(cellValue);
                        data.Columns.Add(column);
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                //最后一列的标号
                var rowCount = sheet.LastRowNum;
                for (var i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    //没有数据的行默认是null
                    if (row == null) continue;
                    var dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        //同理，没有数据的单元格都默认是null
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }

                return data;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="fileName">文件夹路径</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, string fileName)
        {
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(sheetName);
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }
            IWorkbook workbook = null;
            if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
            {
                workbook = new XSSFWorkbook();
            }
            else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
            {
                workbook = new HSSFWorkbook();
            }

            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                ISheet sheet;
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                int j;
                int count;
                //写入DataTable的列名，写入单元格中
                if (isColumnWritten)
                {
                    var row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }
                //遍历循环datatable具体数据项
                int i;
                for (i = 0; i < data.Rows.Count; ++i)
                {
                    var row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                //将文件流写入到excel
                workbook.Write(fs);
                return count;
            }
            catch (IOException ioex)
            {
                throw new IOException(ioex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }
    }

第三步：下面开始做一个很简单的网页来展示导入与导出，还有数据的显示页面。页面中使用Vue

@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <script src="~/Scripts/jquery-3.2.1.min.js"></script>
    <script src="~/Scripts/vue.js"></script>
    <script src="~/Scripts/axios.min.js"></script>
    <style>
        td{
            padding:1rem;
        }
    </style>
</head>
<body >
    <div id="app">

        <form id="myform">
            <table border="1">
                <tr>
                    <td>导入</td>
                    <td>
                        <input type="file" name="fileup" id="fileup" v-on:change="fileChange()" style="display:none"/>
                        <img src="/content/images/upload.png" v-on:click="upclick()">
                    </td>
                </tr>
            </table>
        </form>
        <table>
            <tr v-for="item in list">
                <td>{{item.No}}</td>
                <td>{{item.Name}}</td>
                <td>{{item.Age}}</td>
                <td>{{item.Sex}}</td>
            </tr>
        </table>
        <br />
        <a  href="/home/export">转成excel</a>
    </div>

    <script>

        var app = new Vue({
            el: "#app",
            data: {
                list: [],
            },
            methods: {
                downExcel: function () {
                    $.post("", {},function (res) {
                       
                    });
                },
                upclick:function(){
                    document.getElementById("fileup").click();
                },
                fileChange: function () {
                    
                    if (!document.getElementById("fileup").files[0].size) return;

                    var obj = new FormData(document.getElementById("myform"));
                    obj.append("name", "wzh");
                    var _this = this;
                    $.ajax({
                        type: 'post',
                        url: '/home/Import',
                        data: obj,
                        cache: false,
                        processData: false, // 不处理发送的数据，因为data值是Formdata对象，不需要对数据做处理
                        contentType: false, // 不设置Content-type请求头
                        success: function (data) {
                            if (data != "no") {
                                _this.list = data;
                            } else {
                                alert(data);
                            }
                        },
                    });
                },
            }
        })

    </script>
</body>
</html>

第四步：actionresult的完成

上传与导入：

 public ActionResult Import()
        {
            try
            {
                HttpPostedFileBase uploadfile = Request.Files["fileup"];
                if (uploadfile == null)
                {
                    return Content("no:非法上传");
                }
                if (uploadfile.FileName == "")
                {
                    return Content("no:请选择文件");
                }

                string fileExt = Path.GetExtension(uploadfile.FileName);
                StringBuilder sbtime = new StringBuilder();
                sbtime.Append(DateTime.Now.Year).Append(DateTime.Now.Month).Append(DateTime.Now.Day).Append(DateTime.Now.Hour).Append(DateTime.Now.Minute).Append(DateTime.Now.Second);
                string dir = "/UploadFile/" + sbtime.ToString() + fileExt;
                string realfilepath = Request.MapPath(dir);
                string readDir = Path.GetDirectoryName(realfilepath);
                if (!Directory.Exists(readDir))
                    Directory.CreateDirectory(readDir);

                uploadfile.SaveAs(realfilepath);
                //提取数据 

                var dt =ExcelTool.ExcelToDataTable(true, realfilepath);
                List<User> list = new List<User>();
                foreach (DataRow item in dt.Rows)
                {
                    list.Add(new User()
                    {
                        No = Convert.ToInt32(item[0]),
                        Name = item[1].ToString(),
                        Age = Convert.ToInt32(item[2]),
                        Sex = item[3].ToString()
                    });
                }

                return Json(list,JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                return Content(ex.Message);
            }
        }

导出：

  public ActionResult export()
        {
            var path = Server.MapPath(@"/content/user.xlsx");

            var dt = ExcelTool.ExcelToDataTable(true, path);
            List<User> list = new List<User>();
            foreach (DataRow item in dt.Rows)
            {
                list.Add(new User()
                {
                    No = Convert.ToInt32(item[0]),
                    Name = item[1].ToString(),
                    Age = Convert.ToInt32(item[2]),
                    Sex = item[3].ToString()
                });
            }

            //datatable
            //DataTable d = new DataTable();
            //d.Columns.Add("No");
            //d.Columns.Add("Name");
            //d.Columns.Add("Age");
            //d.Columns.Add("Sex");
            //foreach (var item in list)
            //{
            //    d.NewRow();
            //    d.Rows.Add(new object[]{item.No,item.Name,item.Age,item.Sex});
            //}
            //创建生成的excel的名称
            StringBuilder sbtime = new StringBuilder();
            sbtime.Append(DateTime.Now.Year).Append(DateTime.Now.Month).Append(DateTime.Now.Day).Append(DateTime.Now.Hour).Append(DateTime.Now.Minute).Append(DateTime.Now.Second);
               
            var d = DataTableTool.ToDataTableTow(list);
            var url = "/downfile/"+sbtime+".xls";
            var newpath = Server.MapPath(url);
            ExcelTool.DataTableToExcel(d, "aa", true, newpath);
            //取到生成的名称
            var name=Path.GetFileName(newpath);
            return File(newpath, "application/vnd.ms-excel",name);
        }

这样就完成此次的功能，下面补充几个知识点，

1、File中的参数contentType有

扩展名	类型/子类型
	application/octet-stream
323	text/h323
acx	application/internet-property-stream
ai	application/postscript
aif	audio/x-aiff
aifc	audio/x-aiff
aiff	audio/x-aiff
asf	video/x-ms-asf
asr	video/x-ms-asf
asx	video/x-ms-asf
au	audio/basic
avi	video/x-msvideo
axs	application/olescript
bas	text/plain
bcpio	application/x-bcpio
bin	application/octet-stream
bmp	image/bmp
c	text/plain
cat	application/vnd.ms-pkiseccat
cdf	application/x-cdf
cer	application/x-x509-ca-cert
class	application/octet-stream
clp	application/x-msclip
cmx	image/x-cmx
cod	image/cis-cod
cpio	application/x-cpio
crd	application/x-mscardfile
crl	application/pkix-crl
crt	application/x-x509-ca-cert
csh	application/x-csh
css	text/css
dcr	application/x-director
der	application/x-x509-ca-cert
dir	application/x-director
dll	application/x-msdownload
dms	application/octet-stream
doc	application/msword
dot	application/msword
dvi	application/x-dvi
dxr	application/x-director
eps	application/postscript
etx	text/x-setext
evy	application/envoy
exe	application/octet-stream
fif	application/fractals
flr	x-world/x-vrml
gif	image/gif
gtar	application/x-gtar
gz	application/x-gzip
h	text/plain
hdf	application/x-hdf
hlp	application/winhlp
hqx	application/mac-binhex40
hta	application/hta
htc	text/x-component
htm	text/html
html	text/html
htt	text/webviewhtml
ico	image/x-icon
ief	image/ief
iii	application/x-iphone
ins	application/x-internet-signup
isp	application/x-internet-signup
jfif	image/pipeg
jpe	image/jpeg
jpeg	image/jpeg
jpg	image/jpeg
js	application/x-javascript
latex	application/x-latex
lha	application/octet-stream
lsf	video/x-la-asf
lsx	video/x-la-asf
lzh	application/octet-stream
m13	application/x-msmediaview
m14	application/x-msmediaview
m3u	audio/x-mpegurl
man	application/x-troff-man
mdb	application/x-msaccess
me	application/x-troff-me
mht	message/rfc822
mhtml	message/rfc822
mid	audio/mid
mny	application/x-msmoney
mov	video/quicktime
movie	video/x-sgi-movie
mp2	video/mpeg
mp3	audio/mpeg
mpa	video/mpeg
mpe	video/mpeg
mpeg	video/mpeg
mpg	video/mpeg
mpp	application/vnd.ms-project
mpv2	video/mpeg
ms	application/x-troff-ms
mvb	application/x-msmediaview
nws	message/rfc822
oda	application/oda
p10	application/pkcs10
p12	application/x-pkcs12
p7b	application/x-pkcs7-certificates
p7c	application/x-pkcs7-mime
p7m	application/x-pkcs7-mime
p7r	application/x-pkcs7-certreqresp
p7s	application/x-pkcs7-signature
pbm	image/x-portable-bitmap
pdf	application/pdf
pfx	application/x-pkcs12
pgm	image/x-portable-graymap
pko	application/ynd.ms-pkipko
pma	application/x-perfmon
pmc	application/x-perfmon
pml	application/x-perfmon
pmr	application/x-perfmon
pmw	application/x-perfmon
pnm	image/x-portable-anymap
pot,	application/vnd.ms-powerpoint
ppm	image/x-portable-pixmap
pps	application/vnd.ms-powerpoint
ppt	application/vnd.ms-powerpoint
prf	application/pics-rules
ps	application/postscript
pub	application/x-mspublisher
qt	video/quicktime
ra	audio/x-pn-realaudio
ram	audio/x-pn-realaudio
ras	image/x-cmu-raster
rgb	image/x-rgb
rmi	audio/mid
roff	application/x-troff
rtf	application/rtf
rtx	text/richtext
scd	application/x-msschedule
sct	text/scriptlet
setpay	application/set-payment-initiation
setreg	application/set-registration-initiation
sh	application/x-sh
shar	application/x-shar
sit	application/x-stuffit
snd	audio/basic
spc	application/x-pkcs7-certificates
spl	application/futuresplash
src	application/x-wais-source
sst	application/vnd.ms-pkicertstore
stl	application/vnd.ms-pkistl
stm	text/html
svg	image/svg+xml
sv4cpio	application/x-sv4cpio
sv4crc	application/x-sv4crc
swf	application/x-shockwave-flash
t	application/x-troff
tar	application/x-tar
tcl	application/x-tcl
tex	application/x-tex
texi	application/x-texinfo
texinfo	application/x-texinfo
tgz	application/x-compressed
tif	image/tiff
tiff	image/tiff
tr	application/x-troff
trm	application/x-msterminal
tsv	text/tab-separated-values
txt	text/plain
uls	text/iuls
ustar	application/x-ustar
vcf	text/x-vcard
vrml	x-world/x-vrml
wav	audio/x-wav
wcm	application/vnd.ms-works
wdb	application/vnd.ms-works
wks	application/vnd.ms-works
wmf	application/x-msmetafile
wps	application/vnd.ms-works
wri	application/x-mswrite
wrl	x-world/x-vrml
wrz	x-world/x-vrml
xaf	x-world/x-vrml
xbm	image/x-xbitmap
xla	application/vnd.ms-excel
xlc	application/vnd.ms-excel
xlm	application/vnd.ms-excel
xls	application/vnd.ms-excel
xlt	application/vnd.ms-excel
xlw	application/vnd.ms-excel
xof	x-world/x-vrml
xpm	image/x-xpixmap
xwd	image/x-xwindowdump
z	application/x-compress
zip	application/zip

2、导出方法还有：

    public FileResult ExportExcel()  
    {  
        var sbHtml = new StringBuilder();  
        sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");  
        sbHtml.Append("<tr>");  
        var lstTitle = new List<string> { "编号", "姓名", "年龄", "创建时间" };  
        foreach (var item in lstTitle)  
        {  
            sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);  
        }  
        sbHtml.Append("</tr>");  
      
        for (int i = 0; i < 1000; i++)  
        {  
            sbHtml.Append("<tr>");  
            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i);  
            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>屌丝{0}号</td>", i);  
            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", new Random().Next(20, 30) + i);  
            sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", DateTime.Now);  
            sbHtml.Append("</tr>");  
        }  
        sbHtml.Append("</table>");  
      
        //第一种:使用FileContentResult  
        byte[] fileContents = Encoding.Default.GetBytes(sbHtml.ToString());  
        return File(fileContents, "application/ms-excel", "fileContents.xls");  
      
        //第二种:使用FileStreamResult  
        var fileStream = new MemoryStream(fileContents);  
        return File(fileStream, "application/ms-excel", "fileStream.xls");  
      
        //第三种:使用FilePathResult  
        //服务器上首先必须要有这个Excel文件,然会通过Server.MapPath获取路径返回.  
        var fileName = Server.MapPath("~/Files/fileName.xls");  
        return File(fileName, "application/ms-excel", "fileName.xls");  
    }  

导出文件名有问题可以看这里

http://blog.csdn.net/denghejing/article/details/60753205