首先用Vs自带的Nuget包管理器下载并安装npoi 2.0,如果没有NuGet程序包选项，在菜单-->工具-->扩展管理器中搜索NuGet。

然后写一个方法读取DataTable中的内容，并输出到MemoryStream中

这是我的NPOIHelper

    1 using System;    2 using System.Collections.Generic;    3 using System.Linq;    4 using System.Web;    5 using System.Data;    6 using System.IO;    7 using NPOI.HSSF.UserModel;    8 using NPOI.HPSF;    9 using System.Text;   10 using NPOI.SS.Util;   11 using NPOI.SS.UserModel;   12    13 /// <summary>   14 ///NPOIHelper 的摘要说明   15 /// </summary>   16 public static class NPOIHelper   17 {   18 public static HSSFWorkbook workbook;    19 public static void CloseWorkBook()   20 {   21 workbook = null;   22 }   23 public static void OpenWorkBook() {   24 workbook = new HSSFWorkbook();   25 }   26 /// <summary>   27 /// DataTable导出到Excel的MemoryStream   28 /// </summary>   29 /// <param name="dtSource">源DataTable</param>   30 /// <param name="strHeaderText">表头文本</param>   31 public static void Export(DataTable dtSource, string strHeaderText) {   32    33 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();   34    35 #region 右击文件 属性信息   36 {   37 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();   38 dsi.Company = "北京通信科技有限公司";   39 workbook.DocumentSummaryInformation = dsi;   40    41 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();   42 //si.Author = "文件作者信息"; //填加xls文件作者信息   43 //si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息   44 //si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息   45 //si.Comments = "作者信息"; //填加xls文件作者信息   46 //si.Title = "标题信息"; //填加xls文件标题信息   47 //si.Subject = "主题信息";//填加文件主题信息   48 si.CreateDateTime = DateTime.Now;   49 workbook.SummaryInformation = si;   50 }   51 #endregion   52    53 HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();   54 HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();   55 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");   56    57 HSSFCellStyle tdStyle = (HSSFCellStyle)workbook.CreateCellStyle();   58 //设置单元格边框    59 tdStyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中   60 tdStyle.BorderBottom = BorderStyle.THIN;   61 tdStyle.BorderLeft = BorderStyle.THIN;   62 tdStyle.BorderRight = BorderStyle.THIN;   63 tdStyle.BorderTop = BorderStyle.THIN;   64 //取得列宽   65 int[] arrColWidth = new int[dtSource.Columns.Count];   66 foreach (DataColumn item in dtSource.Columns) {   67 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;   68 }   69 for (int i = 0; i < dtSource.Rows.Count; i++) {   70 for (int j = 0; j < dtSource.Columns.Count; j++) {   71 int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;   72 if (intTemp > arrColWidth[j]) {   73 arrColWidth[j] = intTemp;   74 }   75 }   76 }   77 int rowIndex = 0;   78 foreach (DataRow row in dtSource.Rows) {   79 #region 新建表，填充表头，填充列头，样式   80 if (rowIndex == 65535 || rowIndex == 0) {   81 if (rowIndex != 0) {   82 sheet = (HSSFSheet)workbook.CreateSheet();   83 }   84    85 #region 表头及样式   86 {   87 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);   88 headerRow.HeightInPoints = 25;   89 headerRow.CreateCell(0).SetCellValue(strHeaderText);   90    91 HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();   92 headStyle.Alignment = HorizontalAlignment.CENTER;   93 HSSFFont font = (HSSFFont)workbook.CreateFont();   94    95 font.FontHeightInPoints = 20;   96 font.Boldweight = 700;   97 headStyle.SetFont(font);   98 headerRow.GetCell(0).CellStyle = headStyle;   99 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));  100 //headerRow.Dispose();  101 }  102 #endregion  103   104   105 #region 列头及样式  106 {  107 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);  108 HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();  109 headStyle.Alignment = HorizontalAlignment.CENTER;  110 HSSFFont font = (HSSFFont)workbook.CreateFont();  111 //设置单元格边框   112 headStyle.BorderBottom = BorderStyle.THIN;  113 headStyle.BorderLeft = BorderStyle.THIN;  114 headStyle.BorderRight = BorderStyle.THIN;  115 headStyle.BorderTop = BorderStyle.THIN;  116   117 font.FontHeightInPoints = 10;  118 font.Boldweight = 700;  119 headStyle.SetFont(font);  120 foreach (DataColumn column in dtSource.Columns) {  121 headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);  122 headerRow.GetCell(column.Ordinal).CellStyle = headStyle;  123   124 //设置列宽  125 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);  126 }  127 //headerRow.Dispose();  128 }  129 #endregion  130   131 rowIndex = 2;  132 }  133 #endregion  134   135   136 #region 填充内容  137 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);  138 foreach (DataColumn column in dtSource.Columns) {  139 HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);  140 newCell.CellStyle = tdStyle;  141   142 string drValue = row[column].ToString();  143 switch (column.DataType.ToString()) {  144 case "System.String"://字符串类型  145 newCell.SetCellValue(drValue);  146 break;  147 case "System.DateTime"://日期类型  148 DateTime dateV;  149 DateTime.TryParse(drValue, out dateV);  150 newCell.SetCellValue(dateV);  151   152 newCell.CellStyle = dateStyle;//格式化显示  153 break;  154 case "System.Boolean"://布尔型  155 bool boolV = false;  156 bool.TryParse(drValue, out boolV);  157 newCell.SetCellValue(boolV);  158 break;  159 case "System.Int16"://整型  160 case "System.Int32":  161 case "System.Int64":  162 case "System.Byte":  163 int intV = 0;  164 int.TryParse(drValue, out intV);  165 newCell.SetCellValue(intV);  166 break;  167 case "System.Decimal"://浮点型  168 case "System.Double":  169 double doubV = 0;  170 double.TryParse(drValue, out doubV);  171 newCell.SetCellValue(doubV);  172 break;  173 case "System.DBNull"://空值处理  174 newCell.SetCellValue("");  175 break;  176 default:  177 newCell.SetCellValue("");  178 break;  179 }  180   181 }  182 #endregion  183   184 rowIndex++;  185 }  186 using (MemoryStream ms = new MemoryStream()) {  187 workbook.Write(ms);  188 }  189 }  190   191 public static MemoryStream Export2(DataTable dt, string p) {  192 Export(dt,p);  193 MemoryStream ms = new MemoryStream();  194 ISheet sheet=workbook.GetSheet("Sheet1");  195 int FirstRow=2;  196 int LastRow=sheet.LastRowNum;  197 int Start=0;  198 int End=0;  199 string temp = "";  200 HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();  201 dateStyle.Alignment = HorizontalAlignment.RIGHT;  202 dateStyle.VerticalAlignment = VerticalAlignment.CENTER;  203 for (int i = FirstRow; i < LastRow; i++) {  204 for (int j = 1; j < 5; j++) {  205 if (j == 1)  206 {  207 IRow row=sheet.GetRow(i);  208 if (row == null) continue; //没有数据的行默认是null  209 if (row.GetCell(j) == null){continue;} //同理，没有数据的单元格都默认是null  210 ICell cell=row.GetCell(j);  211 string cellText=cell.StringCellValue;  212 if (cellText == temp)//上下行相等，记录要合并的最后一行  213 {  214 End = i;  215 }  216 else//上下行不等，记录  217 {  218 if (Start != End) {  219 for (int n = Start; n < End; n++) {  220 ICell tempcell=sheet.GetRow(n).GetCell(2);  221 tempcell.SetCellValue(""+( End-Start+1 ));  222 tempcell.CellStyle = dateStyle;  223 }  224 for (int m = 1; m < 5; m++) {  225 CellRangeAddress region = new CellRangeAddress(Start, End, m,m);  226 sheet.AddMergedRegion(region);  227 }  228   229 }  230 Start = i;  231 End = i;  232 temp = cellText;  233 }  234 }  235   236 }  237 }  238 workbook.Write(ms);  239 return ms;  240 }  241 }    NPOIHelper

使用方法是: