.net mvc利用NPOI导入导出excel

1、导出Excel ：
首先引用NPOI包
（Action一定要用FileResult）

        /// <summary>
        /// 批量导出需要导出的列表
        /// </summary>
        /// <returns></returns>
        public FileResult ExportStu2()
        {
            //获取list数据
            var checkList = (from oc in db.OrganizeCustoms
                            join o in db.Organizes.DefaultIfEmpty() on oc.custom_id equals o.id
                            where oc.organize_id == 1
                            select new
                            {
                                customer_id = o.id,
                                customer_name = o.name
                            }).ToList();

            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            //貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("编号");
            row1.CreateCell(1).SetCellValue("姓名");
            //....N行

            //将数据逐步写入sheet1各个行
            for (int i = 0; i < checkList.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(checkList[i].customer_id.ToString());
                rowtemp.CreateCell(1).SetCellValue(checkList[i].customer_name.ToString());
                //....N行
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            DateTime dt = DateTime.Now;
            string dateTime = dt.ToString("yyMMddHHmmssfff");
            string fileName = "查询结果" + dateTime + ".xls";
            return File(ms, "application/vnd.ms-excel", fileName);
        }

前台直接写就可实现：

@Html.ActionLink("点击导出Excel", "ExportStu2")

这里有一篇专门介绍设置样式的文章：
http://www.cnblogs.com/puzi0315/p/3265958.html
http://blog.csdn.net/xhccom/article/details/7687264
http://blog.csdn.net/bestreally/article/details/23257851

 

2、导入Excel：
首先说一些前台吧，mvc上传注意必须加 new { enctype = "multipart/form-data" }

        @using (@Html.BeginForm("StockInExcel", "StockInList", null, FormMethod.Post, new { enctype = "multipart/form-data" }))
        {
            <div class="block-detail">
                <table>
                    <tr>
                        <td class="pop-name">
                            选择文件：
                        </td>
                        <td class="pop-value">
                            <input type="text" name="ExcelName" id="ExcelName" readonly="readonly" onclick="file.click();" />
                            <input type="file" id="file" name="file" onchange="javascript: document.getElementById('ExcelName').value = document.getElementById('file').value;" style="display:none" />
                            <input type="button" id="Choose" name="Choose" value="选择" onclick="file.click();" />
                        </td>
                    </tr>
                </table>
            </div>
            <input type="submit" id="submit" name="submit" value="开始导入" />
        }

 

后台实现：得出DataTable：

        [HttpPost]
        public void StockInExcel()
        {
            string filePath = "";
            //存入文件
            if (Request.Files["file"].ContentLength > 0)
            {
                filePath = Server.MapPath("~/Upload/") + System.IO.Path.GetFileName(Request.Files["file"].FileName);
                Request.Files["file"].SaveAs(filePath);
            }

            try
            {
                IWorkbook hssfworkbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new XSSFWorkbook(file);
                }

                NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);

                DataTable table = new DataTable();
                IRow headerRow = sheet.GetRow(0);//第一行为标题行
                int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
                int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

                //handling header.
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = GetCellValue(row.GetCell(j));
                        }
                    }
                    table.Rows.Add(dataRow);
                }
            }
            catch (Exception e)
            {
                throw e;
            }

        }

        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null) { return string.Empty; }
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }

 

独立除一个方法  ExcelToDatatable

        public DataTable ExcelToDatatable(string filePath)
        {
            try
            {
                IWorkbook workbook = null;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    if (Path.GetExtension(filePath) == ".xls")
                        workbook = new HSSFWorkbook(file);
                    else if (Path.GetExtension(filePath) == ".xlsx")
                        workbook = new XSSFWorkbook(file);
                }

                NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);

                DataTable table = new DataTable();
                IRow headerRow = sheet.GetRow(0);//第一行为标题行
                int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
                int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

                //handling header.
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = GetCellValue(row.GetCell(j));
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                return table;
            }
            catch (Exception e)
            {
                throw e;
                return null;
            }
        }

 

 

得到DataTable后，就想怎么操作就怎么操作了。