做项目的时候参考网上的资源结合现有的项目写的方法，实现不同类型的内容置为对应的格式，内容为数字则单元格数值格式，识别为日期格式则单元格格式也是日期。
实际项目中写了一个判断，如果列名带有【号】、【码】字样的则即便是纯数字也保存为文本。例如银行账号、产品编码等是纯数字但并不应该是数字格式的。
重点为单元格样式以及单元格内的文本格式等都用到了：ICellStyle ，
实例一个CellStyle写法为： ICellStyle cellStyle1 = workbook.CreateCellStyle();
使用newCell.CellStyle = cellstyle;将一个单元格设置为对应的样式，注意ICellStyle的实例在一张表中不能超过4000个，所以切记不要将CellStyle的实例写在数据填充的for循环里哦。下面的代码里的样式使用主要是每个单元格加边框，以及日期格式的固定为yyyy-mm-dd。
下载NPOI的DLL文件：点击下载
类头引用：
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.Util;
using System.Linq;
using NPOI.SS.Formula.Functions;
方法代码：
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="templateFile">模板文件</param>
/// <param name="fileName">导出的文件名</param>
public static MemoryStream ExportToMemoryStream(DataTable dtSource, string strHeaderText, string templateFile = "", string fileName = "")
{
IWorkbook workbook = null;
ISheet sheet = null;
bool isTemplateFile = false;//是否采用模板文件导出
int rowIndex = 0;
bool isExcel2007 = false;
if (!templateFile.IsNullOrEmpty() && File.Exists(templateFile))
{
string templateFilePath = templateFile;
isExcel2007 = templateFilePath.EndsWith("xlsx", StringComparison.CurrentCultureIgnoreCase);
using (FileStream file = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read))
{
if (isExcel2007)
{
workbook = new XSSFWorkbook(file);
}
else
{
workbook = new HSSFWorkbook(file);
}
}
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);
if (sheet != null)
{
isTemplateFile = true;
rowIndex = sheet.LastRowNum + 1;
}
}
}
else
{
isExcel2007 = fileName.EndsWith("xlsx", StringComparison.CurrentCultureIgnoreCase);
}
if (workbook == null)
{
if (isExcel2007)
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
}
if (sheet == null)
{
sheet = workbook.CreateSheet("Sheet1");
}
int[] arrColWidth = new int[] { };
string[] arrColDataType = new string[] { };
if (!isTemplateFile)
{
//取得列宽
arrColWidth = new int[dtSource.Columns.Count];
arrColDataType = new string[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
if (item.Caption.IsInt())
{
arrColWidth[item.Ordinal] = item.Caption.ToInt();
}
else
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
arrColDataType[item.Ordinal] = item.DataType.ToString();
}
if (dtSource.Rows.Count > 0)
{
for (int j = 0; j < dtSource.Columns.Count; j++) { if (!dtSource.Columns[j].Caption.IsInt()) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[0][j].ToString()).Length; if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
}
bool hasHeader = !strHeaderText.IsNullOrEmpty();//是否有表头
if (!isTemplateFile)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
#region 表头及样式
if (hasHeader)
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.BorderBottom = BorderStyle.Thin;
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(hasHeader ? 1 : 0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.IsLocked = true;
headStyle.SetFont(font);
ICellStyle cellStyle = workbook.CreateCellStyle();
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (arrColWidth.Length > 0)
{
sheet.SetColumnWidth(column.Ordinal, Math.Min(arrColWidth[column.Ordinal] + 1, 200) * 256);
}
}
//sheet.CreateFreezePane(0, hasHeader ? 2 : 1, 0, dtSource.Columns.Count - 1);
}
#endregion
rowIndex = hasHeader ? 2 : 1;
}
#endregion
}
int columnsCount = dtSource.Columns.Count;
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.BorderLeft = BorderStyle.Thin;
cellStyle1.BorderRight = BorderStyle.Thin;
cellStyle1.BorderTop = BorderStyle.Thin;
cellStyle1.BorderBottom = BorderStyle.Thin;
//日期格式的单元格格式
ICellStyle csDateFormat = workbook.CreateCellStyle();
csDateFormat.BorderLeft = BorderStyle.Thin;
csDateFormat.BorderRight = BorderStyle.Thin;
csDateFormat.BorderTop = BorderStyle.Thin;
csDateFormat.BorderBottom = BorderStyle.Thin;
IDataFormat dfDate = workbook.CreateDataFormat();
csDateFormat.DataFormat = dfDate.GetFormat("yyyy-mm-dd");
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < columnsCount; i++) { ICell newCell = dataRow.CreateCell(i); //IDataFormat format = workbook.CreateDataFormat(); //cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); newCell.CellStyle = cellStyle1; string drValue = row[i].ToString(); //因列都是字符串型的，所以循环Parse来确认列的对应数据格式。 DateTime dateV; double doubV = 0; //大于8字节的才被认为符合日期格式（2000-1-1），防止-1也被认为是日期 if (drValue.Length >= 8 && DateTime.TryParse(drValue, out dateV))
{
newCell.CellStyle = csDateFormat;
if (dateV == Convert.ToDateTime(null))
newCell.SetCellValue("");
else
newCell.SetCellValue(dateV);
}
else if (double.TryParse(drValue, out doubV))
{
//如果标题含有号、码字样，即使是全数字也显示文本
if ((dtSource.Columns[i] + "").IndexOf("号") >= 0 || (dtSource.Columns[i] + "").IndexOf("码") >= 0)
{
newCell.SetCellValue(drValue);
}
else
{
newCell.SetCellValue(doubV);
}
}
else
{
newCell.SetCellValue(drValue);
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
workbook = null;
sheet = null;
return ms;
}
}
