最近在项目上有需求要自定义导出列，想起以前用过的NPOI做过的导入导出就拿来做了些修改然后记录一下，声明全局静态方法：

1.导入excel到DataTable

        /// <summary>
        /// 将excel导入到datatable
        /// </summary>
        /// <param name="filePath">excel路径</param>
        /// <param name="isColumnName">第一行是否是列名</param>
        /// <returns>返回datatable</returns>
        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 1;//从下标为1的行开始填充
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet，也能循环读取每个sheet
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum + 1;//+1 包括列名的行，总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行
                                int cellCount = firstRow.LastCellNum;//列数
                                //构建datatable的列
                                if (isColumnName)
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }
                                //填充行（要读取列名则i<rowCount 否则i <= rowCount）
                                for (int i = startRow; i < rowCount; ++i) 
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception ex)
            {
                LogRecord.LogCatch(ex, "导入excel到DataTable");//写日志
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }

2.导出DataTable到Excel（自定义列）

        /// <summary>  
        /// 批量导出需要导出的列表
        /// <param name="reqModel">查询条件</param>
        /// <param name="excelColunms">自定义的列名</param>
        /// </summary>  
        /// <returns></returns>  
        public FileResult UserExport(ReqQueryUsers reqModel, string excelColunms)
        {
            try
            {
                //获取数据
                var userlist = _bUser.SelectUsersExcel(reqModel);
                //创建Excel文件的对象  
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                //添加一个sheet  
                NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
                NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
                //可以设置各种样式字体颜色背景等，这里就不设置
                if (excelColunms != "")
                {
                    string[] colunmslist = excelColunms.Split(',');
                    //设置列名
                    for (var i = 0; i < colunmslist.Count(); i++)
                    {
                        row1.CreateCell(i).SetCellValue(colunmslist[i].Trim());
                    }
                }
                //遍历列表
                for (int i = 0; i < userlist.Count; i++)
                {
                    NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                    for (var item = 0; item < row1.Cells.Count(); item++)
                    {
                            //填充数据，根据实际情况处理       
                            //rowtemp.CreateCell(item).SetCellValue(userlist[i].UserName.ToString());    
                    }
                }
                // 写入到客户端   
                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);
            }
            catch (Exception ex)
            {
                LogRecord.LogCatch(ex,"自定义导出excel");
                throw ex;
            }
        }

延伸阅读

1. 使用码云和VS托管本地代码
2. C#阿里云服务器发送QQ邮件