NPOI使用Datatable导出到Excel

首先要引用dll

下载地址：http://pan.baidu.com/s/1dFr2m

引入命名空间：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;

这三个就好了。

 

代码一：

 /// <summary>
        /// 创建工作簿
        /// </summary>
        /// <param name="fileName">下载文件名</param>
        /// <param name="dt">数据源</param>
        public static void CreateSheet(string fileName, DataTable dt)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            //创建一个名称为Payment的工作表
            ISheet paymentSheet = workbook.CreateSheet("游戏得分用户表");

            //数据源
            DataTable tbPayment = dt;

            #region 头部标题
            IRow paymentHeaderRow = paymentSheet.CreateRow(0);

            ICell cell = paymentHeaderRow.CreateCell(0);
            cell.SetCellValue("游戏得分用户信息");
            ICellStyle style = workbook.CreateCellStyle();

            style.Alignment = HorizontalAlignment.CENTER;
            IFont font = workbook.CreateFont();
            font.FontHeight = 20 * 20;
            font.Color = HSSFColor.RED.index;
            style.SetFont(font);

            //合并单元格,以下是合并第一行五列
            paymentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4));
            cell.CellStyle = style;
            int rowsNum = 1;
            paymentHeaderRow = paymentSheet.CreateRow(rowsNum);

            paymentHeaderRow.CreateCell(0, CellType.STRING).SetCellValue("用户名");
            paymentHeaderRow.CreateCell(1, CellType.STRING).SetCellValue("手机号码");
            paymentHeaderRow.CreateCell(2, CellType.NUMERIC).SetCellValue("得分");
            paymentHeaderRow.CreateCell(3, CellType.STRING).SetCellValue("创建时间");
            //paymentHeaderRow.CreateCell(4,CellType.STRING).SetCellValue("IP");
            #endregion

            //循环添加标题
            //foreach (DataColumn column in tbPayment.Columns)
            //    paymentHeaderRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);


            // 内容
            int paymentRowIndex = 2;
            foreach (DataRow row in tbPayment.Rows)
            {
                IRow newRow = paymentSheet.CreateRow(paymentRowIndex);

                //循环添加列的对应内容
                foreach (DataColumn column in tbPayment.Columns)
                {
                    newRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                paymentRowIndex++;
            }

            //列宽自适应，只对英文和数字有效
            for (int i = 0; i <= dt.Rows.Count; i++)
            {
                paymentSheet.AutoSizeColumn(i);
            }
            //获取当前列的宽度，然后对比本列的长度，取最大值
            for (int columnNum = 0; columnNum <= dt.Rows.Count; columnNum++)
            {
                int columnWidth = paymentSheet.GetColumnWidth(columnNum) / 256;
                for (int rowNum = 1; rowNum <= paymentSheet.LastRowNum; rowNum++)
                {
                    IRow currentRow;
                    //当前行未被使用过
                    if (paymentSheet.GetRow(rowNum) == null)
                    {
                        currentRow = paymentSheet.CreateRow(rowNum);
                    }
                    else
                    {
                        currentRow = paymentSheet.GetRow(rowNum);
                    }

                    if (currentRow.GetCell(columnNum) != null)
                    {
                        ICell currentCell = currentRow.GetCell(columnNum);
                        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                        if (columnWidth < length)
                        {
                            columnWidth = length;
                        }
                    }
                }
                paymentSheet.SetColumnWidth(columnNum, columnWidth * 256);
            }

            //将表内容写入流 通知浏览器下载
            workbook.Write(ms);
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName));
            System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray()); //进行二进制流下在

            workbook = null;
            ms.Close();
            ms.Dispose();
        }

　　

代码二：

 /// <summary>
        /// List集合转变成Datatable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public DataTable ConvertToDataSet<T>(IList<T> list)
        {
            if (list == null || list.Count <= 0)
            {
                return null;
            }

            DataTable dt = new DataTable(typeof(T).Name);
            DataColumn column;
            DataRow row;
            dt.Columns.Add("UserName", typeof(string));
            dt.Columns.Add("UserPhone", typeof(string));
            dt.Columns.Add("Score", typeof(int));
            dt.Columns.Add("CreateDate", typeof(DateTime));
            //dt.Columns.Add("CreateIP", typeof(string));

            System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);

            foreach (T t in list)
            {
                if (t == null)
                {
                    continue;
                }

                row = dt.NewRow();

                for (int i = 0, j = myPropertyInfo.Length; i < j; i++)
                {
                    System.Reflection.PropertyInfo pi = myPropertyInfo[i];

                    string name = pi.Name;

                    if (dt.Columns[name] == null)
                    {
                        column = new DataColumn(name, pi.PropertyType);
                        dt.Columns.Add(column);
                    }

                    row[name] = pi.GetValue(t, null);
                }

                dt.Rows.Add(row);
            }

            return dt;
        }

　　

代码三：

  protected void btnExport_Click(object sender, EventArgs e)
        {
            var ss = ydc.GameScore.OrderByDescending(g => g.Score).GroupBy(g => g.UserPhone).Select(g => g.First()).ToList();
            List<GameModel> gList = new List<GameModel>();
            foreach (var item in ss)
            {
                GameModel gm = new GameModel();
                gm.UserName = item.UserName;
                gm.UserPhone = item.UserPhone;
                gm.Score = item.Score;
                gm.CreateDate = item.CreateDate;
                //gm.CreateIP = item.CreateIP;
                gList.Add(gm);
            }

            //导出时避免没有数据而报错
            if (gList.Count() > 0)
            {
                //导出成Excel
                CreateSheet("gameScore", ConvertToDataSet(gList));
            }
            else
            {
                StringHelper.Alert(this, "没有数据不能进行导出操作!", "");
            }

        }