C# Npoi 实现Excel与数据库相互导入

十年河东，十年河西，莫欺少年穷！

NPOI支持对 Word 和 Excel 文件的操作！

针对 Word 的操作一般用于打印技术！说白了就是利用 Word 文件作为模板，生成各种不同的打印！具体用到的技术是：Word 关键字替换操作，Word 图片插入操作， Word 表格填充操作，Word 图表生成操作等等，在此就不一一介绍了，有兴趣的小虎斑可以参考鄙人博客：专业 web 打印组件 及 C# web项目利用docx文档作为模板~为打印专做的解决方案

针对 Excel 文件的操作一般常用的就两种：

1、将数据库数据导入至Excel

2、将 Excel 中数据导入至数据库

当然，针对Excel文件的操作也有其他优秀的组件实现，譬如：Spire.XLS，关于这个组件的运用，大家可参考我的博客：Spire.XLS，生成Excel文件、加载Excel文件

好了，说了这么多，咱们进入正题：

其实说到正题，也没什么可说的，都是一些代码，会调用就行了。

源代码如下：

using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace KMHC.Infrastructure.Excel
{
    public class ExcelHelper
    {
        #region DataTable导出至Excel
        /// <summary>
        /// 导出至Excel
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="templateName">Xls文件名 仅支持Xls扩展名</param>
        /// <returns></returns>
        public static string LoadForToExcel(DataTable dt, string templateName)
        {
            string mapPath = HttpContext.Current.Server.MapPath(VirtualPathUtility.GetDirectory("~"));
            string path = string.Format(@"{0}Templates{1}.xls", mapPath, templateName);
            GridToExcelByNPOI(dt, path);
            return path;
        }
        /// <summary>
        /// 导出至Excel具体实现 
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="strExcelFileName">文件路径</param>
        private static void GridToExcelByNPOI(DataTable dt, string strExcelFileName )
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            try
            {
                ISheet sheet = workbook.CreateSheet("Sheet1");

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);


                //用column name 作为列名
                int icolIndex = 0;
                IRow headerRow = sheet.CreateRow(0);
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 1;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in dt.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (DataColumn Colitem in dt.Columns)
                    {

                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //写Excel
                FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
                workbook.Write(file);//将Excel文件保存到项目中
                file.Flush();
                file.Close();

                //Log.WriteLog("导出Excel成功！");
            }
            catch (Exception ex)
            {
                //Log.WriteLog("导出Excel异常：", ex);
            }
            finally
            {
                workbook = null;
            }

        }
        #endregion

        #region Excel文件数据导出至DataTable
        /// <summary>
        /// Excel数据导出至DataTable
        /// </summary>
        /// <param name="templateName">文件名</param>
        /// <param name="strTableName"></param>
        /// <param name="iSheetIndex">第几个Sheet的数据</param>
        public static DataTable LoadForToDataTable(string templateName, string strTableName, int iSheetIndex)
        {
            string mapPath = HttpContext.Current.Server.MapPath(VirtualPathUtility.GetDirectory("~"));//获取项目根目录
            string path = string.Format(@"{0}Templates{1}.xls", mapPath, templateName);//Templates 构造根目录路径 Templates 是项目的一个文件夹
            return XlSToDataTable(path, strTableName, iSheetIndex);
        }
        /// <summary>
        /// Excel文件导成Datatable
        /// </summary>
        /// <param name="strFilePath">Excel文件目录地址</param>
        /// <param name="strTableName">Datatable表名</param>
        /// <param name="iSheetIndex">Excel sheet index</param>
        /// <returns></returns>
        private static DataTable XlSToDataTable(string strFilePath, string strTableName, int iSheetIndex)
        {

            string strExtName = Path.GetExtension(strFilePath);

            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(strTableName))
            {
                dt.TableName = strTableName;
            }

            if (strExtName.Equals(".xls") || strExtName.Equals(".xlsx"))
            {
                using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(file);
                    ISheet sheet = workbook.GetSheetAt(iSheetIndex);

                    //列头
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        dt.Columns.Add(item.ToString(), typeof(string));
                    }

                    //写入内容
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    while (rows.MoveNext())
                    {
                        IRow row = (HSSFRow)rows.Current;
                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }

                        DataRow dr = dt.NewRow();
                        foreach (ICell item in row.Cells)
                        {
                            switch (item.CellType)
                            {
                                case CellType.Boolean:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.Error:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.Formula:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.Boolean:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.Error:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = item.NumericCellValue;
                                            }
                                            break;
                                        case CellType.String:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.Blank:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = item.NumericCellValue;
                                    }
                                    break;
                                case CellType.String:
                                    string strValue = item.StringCellValue;
                                    if (!string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = null;
                                    }
                                    break;
                                case CellType.Unknown:
                                case CellType.Blank:
                                default:
                                    dr[item.ColumnIndex] = string.Empty;
                                    break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }

            return dt;
        }
        #endregion
    }
}

具体调用如下：

 public string Export()
        {////自己构造泛型：AllList
            //打印开始 自己构造泛型：AllList
            DataTable dt = new DataTable();
            dt.Columns.Add("所属", typeof(string));
            dt.Columns.Add("消费日期", typeof(string));
            dt.Columns.Add("消费项", typeof(string));
            dt.Columns.Add("类别", typeof(string));
            dt.Columns.Add("会员卡", typeof(string));
            dt.Columns.Add("现金", typeof(string));
            dt.Columns.Add("支付宝", typeof(string));
            dt.Columns.Add("微信", typeof(string));
            dt.Columns.Add("一卡通", typeof(string));
            foreach (var item in AllList)
            {
                DataRow dr = dt.NewRow();
                dr["所属"] = "";
                if (item.ServiceType.HasValue)
                {
                    var dicModelForService = dicListForService.FirstOrDefault(A => A.ItemCode == item.ServiceType.ToString());
                    if (dicModelForService != null)
                    {
                        dr["所属"] = dicModelForService.ItemName;
                    }
                }
                try
                {
                    dr["消费日期"] = item.FeeDate.ToString("yyyy-MM-dd");
                }
                catch
                {
                    dr["消费日期"] = "";
                }
                dr["消费项"] = item.FeeName;
                dr["类别"] = "";
                if (item.FeeType.HasValue)
                {
                    var dicModel = dicList.FirstOrDefault(A => A.ItemCode == item.FeeType.ToString());
                    if (dicModel != null)
                    {
                        dr["类别"] = dicModel.ItemName;
                    }
                }
                //
                dr["会员卡"] = item.ResidentCard.HasValue ? Convert.ToDouble(-item.ResidentCard).ToString("0.00") : "";
                dr["现金"] = item.Cash.HasValue ? Convert.ToDouble(-item.Cash).ToString("0.00") : "";
                dr["支付宝"] = item.Alipay.HasValue ? Convert.ToDouble(-item.Alipay).ToString("0.00") : "";
                dr["微信"] = item.WeChatpay.HasValue ? Convert.ToDouble(-item.WeChatpay).ToString("0.00") : "";
                dr["一卡通"] = item.AllPurposeCard.HasValue ? Convert.ToDouble(-item.AllPurposeCard).ToString("0.00") : "";
                dt.Rows.Add(dr);
            }

            return ExcelHelper.LoadForToExcel(dt, "FeeDetails");
        }

前端Action视图代码如下：

   public ActionResult Export()
        {
            IReportManageService service = IOCContainer.Instance.Resolve<IReportManageService>();
            string pth = service.Export();
            System.Web.HttpContext.Current.Response.Charset = "GB2312";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;  
            Response.AddHeader("Content-type", "application/ms-excel");
            Response.AddHeader("Accept-Ranges", "bytes");
            Response.AddHeader("Content-Length", new System.IO.FileInfo(pth).Length.ToString());
            Response.AddHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddhhssmm") + ".xls");
            Response.WriteFile(pth);
            Response.End();
            return View();
        }

以上便是所有源代码，希望大家喜欢！

截图如下：

@陈卧龙的博客