使用NPOI导出Excel模板

导出Excel包含的功能：

1.多表头导出最多支持到三行，表头格式说明

• 相邻父列头之间用’#’分隔,父列头与子列头用空格(’ ‘)分隔,相邻子列头用逗号分隔(‘,’)

• 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名

• 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值

• 三行时请注意：列头要重复

2.添加表头标题功能
3.添加序号功能
4.根据数据设置列宽

---

实体类

NPOIModel.cs

using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Web;namespace NPOIDemo.Model{    /// <summary>    /// 实体类    /// </summary>    public class NPOIModel    {        /// <summary>        /// 数据源        /// </summary>        public DataTable dataSource { get; private set; }        /// <summary>        /// 要导出的数据列数组        /// </summary>        public string[] fileds { get; private set; }        /// <summary>        /// 工作薄名称数组        /// </summary>        public string sheetName { get; private set; }        /// <summary>        /// 表标题        /// </summary>        public string tableTitle { get; private set; }        /// <summary>        /// 表标题是否存在 1：存在 0：不存在        /// </summary>        public int isTitle { get; private set; }        /// <summary>        /// 是否添加序号        /// </summary>        public int isOrderby { get; private set; }        /// <summary>        /// 表头        /// </summary>        public string headerName { get; private set; }        /// <summary>        /// 取得列宽        /// </summary>        public int[] colWidths { get; private set; }        /// <summary>        /// 构造函数        /// </summary>        /// <param name="dataSource">数据来源 DataTable</param>        /// <param name="filed">要导出的字段，如果为空或NULL，则默认全部</param>         /// <param name="sheetName">工作薄名称</param>        /// <param name="headerName">表头名称 如果为空或NULL，则默认数据列字段        /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',')        /// 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名         /// 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值         /// 三行时请注意：列头要重复        /// </param>        /// <param name="tableTitle">表标题</param>         /// <param name="isOrderby">是否添加序号 0：不添加 1：添加</param>        public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle = null, int isOrderby = 0)        {            if (!string.IsNullOrEmpty(filed))            {                this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);                // 移除多余数据列                for (int i = dataSource.Columns.Count - 1; i >= 0; i--)                {                    DataColumn dc = dataSource.Columns[i];                    if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper()))                    {                        dataSource.Columns.Remove(dataSource.Columns[i]);                    }                }                // 列索引                int colIndex = 0;                // 循环排序                for (int i = 0; i < dataSource.Columns.Count; i++)                {                    // 获取索引                    colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper());                    // 设置下标                    dataSource.Columns[i].SetOrdinal(colIndex);                }            }            else            {                this.fileds = new string[dataSource.Columns.Count];                for (int i = 0; i < dataSource.Columns.Count; i++)                {                    this.fileds[i] = dataSource.Columns[i].ColumnName;                }            }            this.dataSource = dataSource;            if (!string.IsNullOrEmpty(sheetName))                this.sheetName = sheetName;            if (!string.IsNullOrEmpty(headerName))                this.headerName = headerName;            else                this.headerName = string.Join("#", this.fileds);            if (!string.IsNullOrEmpty(tableTitle))            {                this.tableTitle = tableTitle;                this.isTitle = 1;            }            // 取得数据列宽 数据列宽可以和表头列宽比较，采取最长宽度              colWidths = new int[this.dataSource.Columns.Count];            foreach (DataColumn item in this.dataSource.Columns)                colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;            // 循环比较最大宽度            for (int i = 0; i < this.dataSource.Rows.Count; i++)            {                for (int j = 0; j < this.dataSource.Columns.Count; j++)                {                    int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length;                    if (intTemp > colWidths[j])                        colWidths[j] = intTemp;                }            }            if (isOrderby > 0)            {                this.isOrderby = isOrderby;                this.headerName = "序号#" + this.headerName;            }        }        /// <summary>        /// 获取列名下标        /// </summary>        /// <param name="colName">列名称</param>        /// <returns></returns>        private int GetColIndex(string colName)        {            for (int i = 0; i < this.fileds.Length; i++)            {                if (colName == this.fileds[i])                    return i;            }            return 0;        }    }}

NPOIHeader.cs

using System;using System.Collections.Generic;using System.Linq;using System.Web;namespace NPOIDemo.Model{    /// <summary>    /// 表头构建类    /// </summary>    public class NPOIHeader    {        /// <summary>        /// 表头        /// </summary>        public string headerName { get; set; }        /// <summary>        /// 起始行        /// </summary>        public int firstRow { get; set; }        /// <summary>        /// 结束行        /// </summary>        public int lastRow { get; set; }        /// <summary>        /// 起始列        /// </summary>        public int firstCol { get; set; }        /// <summary>        /// 结束列        /// </summary>        public int lastCol { get; set; }        /// <summary>        /// 是否跨行        /// </summary>        public int isRowSpan { get; private set; }        /// <summary>        /// 是否跨列        /// </summary>        public int isColSpan { get; private set; }        /// <summary>        /// 外加行        /// </summary>        public int rows { get; set; }        public NPOIHeader() { }        /// <summary>        /// 构造函数        /// </summary>        /// <param name="headerName">表头</param>        /// <param name="firstRow">起始行</param>        /// <param name="lastRow">结束行</param>        /// <param name="firstCol">起始列</param>        /// <param name="lastCol">结束列</param>        /// <param name="rows">外加行</param>        /// <param name="cols">外加列</param>        public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0)        {            this.headerName = headerName;            this.firstRow = firstRow;            this.lastRow = lastRow;            this.firstCol = firstCol;            this.lastCol = lastCol;            // 是否跨行判断            if (firstRow != lastRow)                isRowSpan = 1;            if (firstCol != lastCol)                isColSpan = 1;            this.rows = rows;        }    }}

NPOIHelper.cs辅助类

using System;using System.Linq;using System.Web;using System.IO;using NPOI;using NPOI.SS.Util;using NPOI.HSSF.Util;using NPOI.SS.UserModel;using NPOI.HSSF.UserModel;using System.Data;using System.Collections.Generic;using System.Text;using NPOIDemo.Model;namespace NPOIDemo{    /// <summary>    /// Excel导出    /// </summary>    public class NPOIHelper    {        #region 初始化        /// <summary>        /// 声明 HSSFWorkbook 对象        /// </summary>        private static HSSFWorkbook _workbook;        /// <summary>        /// 声明 HSSFSheet 对象        /// </summary>        private static HSSFSheet _sheet;        #endregion        /// <summary>        /// Excel导出        /// </summary>        /// <param name="fileName">文件名称 如果为空或NULL，则默认“新建Excel.xls”</param>        /// <param name="list"></param>        /// <param name="method">导出方式 1：WEB导出（默认）2：按文件路径导出</param>        /// <param name="filePath">文件路径 如果WEB导出，则可以为空；如果按文件路径导出，则默认桌面路径</param>        public static void Export(string fileName, IList<NPOIModel> list, int method = 1, string filePath = null)        {            // 文件名称            if (!string.IsNullOrEmpty(fileName))            {                if (fileName.IndexOf('.') == -1)                    fileName += ".xls";                else                    fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls";            }            else                fileName = "新建Excel.xls";            // 文件路径            if (2 == method && string.IsNullOrEmpty(filePath))                filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);            // 调用导出处理程序            Export(list);            // WEB导出            if (1 == method)            {                System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";                //设置下载的Excel文件名                System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));                using (MemoryStream ms = new MemoryStream())                {                    //将工作簿的内容放到内存流中                    _workbook.Write(ms);                    //将内存流转换成字节数组发送到客户端                    System.Web.HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());                    System.Web.HttpContext.Current.Response.End();                    _sheet = null;                    _workbook = null;                }            }            else if (2 == method)            {                using (FileStream fs = File.Open(filePath, FileMode.Append))                {                    _workbook.Write(fs);                    _sheet = null;                    _workbook = null;                }            }        }        /// <summary>        /// 导出方法实现        /// </summary>        /// <param name="list"></param>        private static void Export(IList<NPOIModel> list)        {            #region 变量声明            // 初始化            _workbook = new HSSFWorkbook();            // 声明 Row 对象            IRow _row;            // 声明 Cell 对象            ICell _cell;            // 总列数            int cols = 0;            // 总行数            int rows = 0;            // 行数计数器            int rowIndex = 0;            // 单元格值            string drValue = null;            #endregion            foreach (NPOIModel model in list)            {                // 工作薄命名                if (model.sheetName != null)                    _sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);                else                    _sheet = (HSSFSheet)_workbook.CreateSheet();                // 获取数据源                DataTable dt = model.dataSource;                // 初始化                rowIndex = 0;                // 获取总行数                rows = GetRowCount(model.headerName);                // 获取总列数                cols = GetColCount(model.headerName);                // 循环行数                foreach (DataRow row in dt.Rows)                {                    #region 新建表，填充表头，填充列头，样式                    if (rowIndex == 65535 || rowIndex == 0)                    {                        if (rowIndex != 0)                            _sheet = (HSSFSheet)_workbook.CreateSheet();                        // 构建行                        for (int i = 0; i < rows + model.isTitle; i++)                        {                            _row = _sheet.GetRow(i);                            // 创建行                            if (_row == null)                                _row = _sheet.CreateRow(i);                            for (int j = 0; j < cols; j++)                                _row.CreateCell(j).CellStyle = bodyStyle;                        }                        // 如果存在表标题                        if (model.isTitle > 0)                        {                            // 获取行                            _row = _sheet.GetRow(0);                            // 合并单元格                            CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));                            _sheet.AddMergedRegion(region);                            // 填充值                            _row.CreateCell(0).SetCellValue(model.tableTitle);                            // 设置样式                            _row.GetCell(0).CellStyle = titleStyle;                            // 设置行高                            _row.HeightInPoints = 20;                        }                        // 取得上一个实体                        NPOIHeader lastRow = null;                        IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);                        // 创建表头                        foreach (NPOIHeader m in hList)                        {                            var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);                            if (data.Count() > 0)                            {                                lastRow = data.First();                                if (m.headerName == lastRow.headerName)                                    m.firstCol = lastRow.firstCol;                            }                            // 获取行                            _row = _sheet.GetRow(m.firstRow);                            // 合并单元格                            CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);                            _sheet.AddMergedRegion(region);                            // 填充值                            _row.CreateCell(m.firstCol).SetCellValue(m.headerName);                        }                        // 填充表头样式                        for (int i = 0; i < rows + model.isTitle; i++)                        {                            _row = _sheet.GetRow(i);                            for (int j = 0; j < cols; j++)                            {                                _row.GetCell(j).CellStyle = bodyStyle;                                //设置列宽                                _sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 256);                            }                        }                        rowIndex = (rows + model.isTitle);                    }                    #endregion                    #region 填充内容                    // 构建列                    _row = _sheet.CreateRow(rowIndex);                    foreach (DataColumn column in dt.Columns)                    {                        // 添加序号列                        if (1 == model.isOrderby && column.Ordinal == 0)                        {                            _cell = _row.CreateCell(0);                            _cell.SetCellValue(rowIndex - rows);                            _cell.CellStyle = bodyStyle;                        }                        // 创建列                        _cell = _row.CreateCell(column.Ordinal + model.isOrderby);                        // 获取值                        drValue = row[column].ToString();                        switch (column.DataType.ToString())                        {                            case "System.String"://字符串类型                                _cell.SetCellValue(drValue);                                _cell.CellStyle = bodyStyle;                                break;                            case "System.DateTime"://日期类型                                DateTime dateV;                                DateTime.TryParse(drValue, out dateV);                                _cell.SetCellValue(dateV);                                _cell.CellStyle = dateStyle;//格式化显示                                break;                            case "System.Boolean"://布尔型                                bool boolV = false;                                bool.TryParse(drValue, out boolV);                                _cell.SetCellValue(boolV);                                _cell.CellStyle = bodyStyle;                                break;                            case "System.Int16"://整型                            case "System.Int32":                            case "System.Int64":                            case "System.Byte":                                int intV = 0;                                int.TryParse(drValue, out intV);                                _cell.SetCellValue(intV);                                _cell.CellStyle = bodyRightStyle;                                break;                            case "System.Decimal"://浮点型                            case "System.Double":                                double doubV = 0;                                double.TryParse(drValue, out doubV);                                _cell.SetCellValue(doubV);                                _cell.CellStyle = bodyRightStyle;                                break;                            case "System.DBNull"://空值处理                                _cell.SetCellValue("");                                break;                            default:                                _cell.SetCellValue("");                                break;                        }                    }                    #endregion                    rowIndex++;                }            }        }        #region 辅助方法        /// <summary>        /// 表头解析        /// </summary>        /// <remarks>        /// author:zhujt        /// create date:2015-9-10 19:24:51        /// </remarks>        /// <param name="header">表头</param>        /// <param name="rows">总行数</param>        /// <param name="addRows">外加行</param>        /// <param name="addCols">外加列</param>        /// <returns></returns>        private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows)        {            // 临时表头数组            string[] tempHeader;            string[] tempHeader2;            // 所跨列数            int colSpan = 0;            // 所跨行数            int rowSpan = 0;            // 单元格对象            NPOIHeader model = null;            // 行数计数器            int rowIndex = 0;            // 列数计数器            int colIndex = 0;            //             IList<NPOIHeader> list = new List<NPOIHeader>();            // 初步解析            string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries);            // 表头遍历            for (int i = 0; i < headers.Length; i++)            {                // 行数计数器清零                rowIndex = 0;                // 列数计数器清零                colIndex = 0;                // 获取所跨行数                rowSpan = GetRowSpan(headers[i], rows);                // 获取所跨列数                colSpan = GetColSpan(headers[i]);                // 如果所跨行数与总行数相等，则不考虑是否合并单元格问题                if (rows == rowSpan)                {                    colIndex = GetMaxCol(list);                    model = new NPOIHeader(headers[i],                        addRows,                        (rowSpan - 1 + addRows),                        colIndex,                        (colSpan - 1 + colIndex),                        addRows);                    list.Add(model);                    rowIndex += (rowSpan - 1) + addRows;                }                else                {                    // 列索引                    colIndex = GetMaxCol(list);                    // 如果所跨行数不相等，则考虑是否包含多行                    tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);                    for (int j = 0; j < tempHeader.Length; j++)                    {                        // 如果总行数=数组长度                        if (1 == GetColSpan(tempHeader[j]))                        {                            if (j == tempHeader.Length - 1 && tempHeader.Length < rows)                            {                                model = new NPOIHeader(tempHeader[j],                                    (j + addRows),                                    (j + addRows) + (rows - tempHeader.Length),                                    colIndex,                                    (colIndex + colSpan - 1),                                    addRows);                                list.Add(model);                            }                            else                            {                                model = new NPOIHeader(tempHeader[j],                                        (j + addRows),                                        (j + addRows),                                        colIndex,                                        (colIndex + colSpan - 1),                                        addRows);                                list.Add(model);                            }                        }                        else                        {                            // 如果所跨列数不相等，则考虑是否包含多列                            tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);                            for (int m = 0; m < tempHeader2.Length; m++)                            {                                // 列索引                                colIndex = GetMaxCol(list) - colSpan + m;                                if (j == tempHeader.Length - 1 && tempHeader.Length < rows)                                {                                    model = new NPOIHeader(tempHeader2[m],                                        (j + addRows),                                        (j + addRows) + (rows - tempHeader.Length),                                        colIndex,                                        colIndex,                                        addRows);                                    list.Add(model);                                }                                else                                {                                    model = new NPOIHeader(tempHeader2[m],                                            (j + addRows),                                            (j + addRows),                                            colIndex,                                            colIndex,                                            addRows);                                    list.Add(model);                                }                            }                        }                        rowIndex += j + addRows;                    }                }            }            return list;        }        /// <summary>        /// 获取最大列        /// </summary>        /// <param name="list"></param>        /// <returns></returns>        private static int GetMaxCol(IList<NPOIHeader> list)        {            int maxCol = 0;            if (list.Count > 0)            {                foreach (NPOIHeader model in list)                {                    if (maxCol < model.lastCol)                        maxCol = model.lastCol;                }                maxCol += 1;            }            return maxCol;        }        /// <summary>        /// 获取表头行数        /// </summary>        /// <param name="newHeaders">表头文字</param>        /// <returns></returns>        private static int GetRowCount(string newHeaders)        {            string[] ColumnNames = newHeaders.Split(new char[] { '@' });            int Count = 0;            if (ColumnNames.Length <= 1)                ColumnNames = newHeaders.Split(new char[] { '#' });            foreach (string name in ColumnNames)            {                int TempCount = name.Split(new char[] { ' ' }).Length;                if (TempCount > Count)                    Count = TempCount;            }            return Count;        }        /// <summary>        /// 获取表头列数        /// </summary>        /// <param name="newHeaders">表头文字</param>        /// <returns></returns>        private static int GetColCount(string newHeaders)        {            string[] ColumnNames = newHeaders.Split(new char[] { '@' });            int Count = 0;            if (ColumnNames.Length <= 1)                ColumnNames = newHeaders.Split(new char[] { '#' });            Count = ColumnNames.Length;            foreach (string name in ColumnNames)            {                int TempCount = name.Split(new char[] { ',' }).Length;                if (TempCount > 1)                    Count += TempCount - 1;            }            return Count;        }        /// <summary>        /// 列头跨列数        /// </summary>        /// <remarks>        /// author:zhujt        /// create date:2015-9-9 09:17:34        /// </remarks>        /// <param name="newHeaders">表头文字</param>        /// <returns></returns>        private static int GetColSpan(string newHeaders)        {            return newHeaders.Split(',').Count();        }        /// <summary>        /// 列头跨行数        /// </summary>         /// <remarks>        /// author:zhujt        /// create date:2015-9-9 09:17:14        /// </remarks>        /// <param name="newHeaders">列头文本</param>        /// <param name="rows">表头总行数</param>        /// <returns></returns>        private static int GetRowSpan(string newHeaders, int rows)        {            int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length;            // 如果总行数与当前表头所拥有行数相等            if (rows == Count)                Count = 1;            else if (Count < rows)                Count = 1 + (rows - Count);            else                throw new Exception("表头格式不正确！");            return Count;        }        #endregion        #region 单元格样式        /// <summary>        /// 数据单元格样式        /// </summary>        private static ICellStyle bodyStyle        {            get            {                ICellStyle style = _workbook.CreateCellStyle();                style.Alignment = HorizontalAlignment.Center; //居中                style.VerticalAlignment = VerticalAlignment.Center;//垂直居中                 style.WrapText = true;//自动换行                // 边框                style.BorderBottom = BorderStyle.Thin;                style.BorderLeft = BorderStyle.Thin;                style.BorderRight = BorderStyle.Thin;                style.BorderTop = BorderStyle.Thin;                // 字体                IFont font = _workbook.CreateFont();                font.FontHeightInPoints = 10;                font.FontName = "宋体";                style.SetFont(font);                return style;            }        }        /// <summary>        /// 数据单元格样式        /// </summary>        private static ICellStyle bodyRightStyle        {            get            {                ICellStyle style = _workbook.CreateCellStyle();                style.Alignment = HorizontalAlignment.Right; //居中                style.VerticalAlignment = VerticalAlignment.Center;//垂直居中                 style.WrapText = true;//自动换行                // 边框                style.BorderBottom = BorderStyle.Thin;                style.BorderLeft = BorderStyle.Thin;                style.BorderRight = BorderStyle.Thin;                style.BorderTop = BorderStyle.Thin;                // 字体                IFont font = _workbook.CreateFont();                font.FontHeightInPoints = 10;                font.FontName = "宋体";                style.SetFont(font);                return style;            }        }        /// <summary>        /// 标题单元格样式        /// </summary>        private static ICellStyle titleStyle        {            get            {                ICellStyle style = _workbook.CreateCellStyle();                style.Alignment = HorizontalAlignment.Center; //居中                style.VerticalAlignment = VerticalAlignment.Center;//垂直居中                 style.WrapText = true;//自动换行                 IFont font = _workbook.CreateFont();                font.FontHeightInPoints = 14;                font.FontName = "宋体";                font.Boldweight = (short)FontBoldWeight.Bold;                style.SetFont(font);                return style;            }        }        /// <summary>        /// 日期单元格样式        /// </summary>        private static ICellStyle dateStyle        {            get            {                ICellStyle style = _workbook.CreateCellStyle();                style.Alignment = HorizontalAlignment.Center; //居中                style.VerticalAlignment = VerticalAlignment.Center;//垂直居中                 style.WrapText = true;//自动换行                // 边框                style.BorderBottom = BorderStyle.Thin;                style.BorderLeft = BorderStyle.Thin;                style.BorderRight = BorderStyle.Thin;                style.BorderTop = BorderStyle.Thin;                // 字体                IFont font = _workbook.CreateFont();                font.FontHeightInPoints = 10;                font.FontName = "宋体";                style.SetFont(font);                IDataFormat format = _workbook.CreateDataFormat();                style.DataFormat = format.GetFormat("yyyy-MM-dd");                return style;            }        }        #endregion    }}

DemoTest.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DemoTest.aspx.cs" Inherits="NPOIDemo.DemoTest" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server">    <title></title></head><body>    <form id="form1" runat="server">        <div>            <asp:Button runat="server" ID="btn_Export" Text="导出" OnClick="btn_Export_Click" />        </div>    </form></body></html>

DemoTest.aspx.cs

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using NPOIDemo.Model;namespace NPOIDemo{    public partial class DemoTest : System.Web.UI.Page    {        protected void Page_Load(object sender, EventArgs e)        {        }        protected void btn_Export_Click(object sender, EventArgs e)        {            DataTable dt = CreateDataTable();            IList<NPOIModel> list = new List<NPOIModel>();            list.Add(new NPOIModel(dt, "name;sex;workyear;position;hiredate;skill;hobby;avg", "测试1",                "姓名#性别#工作情况 工作年限,职位#入职日期#其他信息 能力 技能,爱好#其他信息 年龄", "测试1"));            dt = CreateDataTable();            list.Add(new NPOIModel(dt, null, "测试2", null, "测试2"));            //dt = FarmerList();            //list.Add(new NPOIModel(dt, "rowid;tname;vname;Name;IdNumber;education;political;mz;Household;HouseholdType;Phone;Domicile", "测试3",            //    "所属乡镇 序号,乡镇,行政村#所在村 基本信息 姓名,身份证号,文化程度,政治面貌,民族,户籍,户籍性质,手机,现居住地", "崇明县农业从业人员信息统计表"));            NPOIHelper.Export(null, list);        }        //private DataTable FarmerList() {        //    string sql = @"SELECT TOP 20 ROW_NUMBER()OVER(ORDER BY a.Id) rowid,b.Name tname,c.Name vname,a.Name,a.IdNumber,d.Name education,e.Name political,        //                '汉族' mz,a.Household,a.HouseholdType,a.Phone,Domicile FROM dbo.tb_FarmerInfo a        //                LEFT JOIN dbo.View_Area b ON b.id=a.CurrentArea        //                LEFT JOIN dbo.View_Area c ON c.id=a.InCome        //                LEFT JOIN dbo.tb_BaseData d ON d.Id=a.Education        //                LEFT JOIN dbo.tb_BaseData e ON e.Id=a.Political        //                WHERE a.InCome IS NOT NULL";        //    return DbHelperSQL.Query(sql).Tables[0];        //}        private DataTable CreateDataTable()        {            DataTable dt = new DataTable();            DataColumn col = new DataColumn("name", typeof(string));            dt.Columns.Add(col);            col = new DataColumn("sex", typeof(string));            dt.Columns.Add(col);            col = new DataColumn("avg", typeof(int));            dt.Columns.Add(col);            col = new DataColumn("mobilephone", typeof(decimal));            dt.Columns.Add(col);            col = new DataColumn("workyear", typeof(int));            dt.Columns.Add(col);            col = new DataColumn("position", typeof(string));            dt.Columns.Add(col);            col = new DataColumn("skill", typeof(string));            dt.Columns.Add(col);            col = new DataColumn("hobby", typeof(string));            dt.Columns.Add(col);            col = new DataColumn("hiredate", typeof(DateTime));            dt.Columns.Add(col);            DataRow rw = dt.NewRow();            rw["name"] = "卡尔";            rw["sex"] = "男";            rw["avg"] = 20;            rw["mobilephone"] = 15800034430;            rw["workyear"] = 3;            rw["position"] = "程序员";            rw["skill"] = ".net,oracel,sqlserver,html5,css3";            rw["hobby"] = "打篮球，运动";            rw["hiredate"] = DateTime.Now;            dt.Rows.Add(rw);            rw = dt.NewRow();            rw["name"] = "玫瑰";            rw["sex"] = "女";            rw["avg"] = 23;            rw["mobilephone"] = 17800034430;            rw["workyear"] = 4;            rw["position"] = "美工";            rw["skill"] = "ps";            rw["hobby"] = "唱歌，跳舞，做饭";            rw["hiredate"] = DateTime.Now;            dt.Rows.Add(rw);            rw = dt.NewRow();            rw["name"] = "boss";            rw["sex"] = "男";            rw["avg"] = 45;            rw["mobilephone"] = 13800039980;            rw["workyear"] = 20;            rw["position"] = "老总";            rw["skill"] = "人际关系，企业管理";            rw["hobby"] = "爬山，摄影，跑步，游泳";            rw["hiredate"] = DateTime.Now;            dt.Rows.Add(rw);            return dt;        }    }}

运行结果如图: