下载：
http://pan.baidu.com/s/1boTpT5l
使用方法：
导入：
使用 ReadToDataTable方法
导出：
NPOIExcel.ExcelManager manger1 = new ExcelManager(this.textBox2.Text, ExcelManager.DealType.exportExcel);
manger1.SetDataSource(dt, ); //设置标题行
manger1.SetRangeStyle("A1", "D1")
.SetCellMerge()
.SetFontColor(NPOIStyle.NPOIColor.Blue)
.SetBackgroundColor(NPOIStyle.NPOIColor.Yellow)
.SetFontBold()
.SetFontItalic()
.SetFontSize()
.SetCellText("2016 这里是标题")
.SetEnd();
// 设置数据
manger1.SetRangeStyle("a2", "d5")
.SetFontName("微软雅黑")
.SetFontSize()
.SetHorizontalAlignment(NPOIStyle.Alignment.Left)
.SetBorderAll()
.SetEnd();
//设置尾行
manger1.SetRangeStyle("e8", "f9")
.SetCellMerge()
.SetFontColor(NPOIStyle.NPOIColor.Red)
.SetFontItalic()
.SetFontSize()
.SetCellText("2016 签名")
.SetBorderBottom()
.SetBorderRight()
.SetEnd(); //导出文件
manger1.ExportExcelFile();
源码：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util; namespace NPOIExcel
{
public class Point
{
public int X { get; set; }
public int Y { get; set; }
}
public class Range
{
public int FIRSTROW { get; set; }
public int LASTROW { get; set; }
public int FIRSTCOL { get; set; }
public int LASTCOL { get; set; }
} public class ExcelManager
{
#region 属性 private string inPutPath { get; set; }
private string outPutPath { get; set; }
private IWorkbook workbook { get; set; }
private ISheet sheet { get; set; } #endregion #region 初始化
//public ExcelManager() { }
/// <summary>
/// 创建一个用于导入，导出 数据的管理类
/// </summary>
/// <param name="Path">文件路径</param>
/// <param name="type">设置路径为导入或导出</param>
public ExcelManager(string Path, DealType type)
{
//设置导入文件的路径,workBook
if (type == DealType.importExcel)
{
inPutPath = Path;
using (FileStream file = new FileStream(inPutPath, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(file);
}
}
if (type == DealType.exportExcel)
{
outPutPath = Path;
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet("sheet1");
} } /// <summary>
/// 对Excel的操作类型
/// </summary>
public enum DealType
{
importExcel,
exportExcel
}
#endregion #region "基础样式设置---2016-05-17 fengylc add "
/// <summary>
/// 设置范围内的样式
/// </summary>
/// <param name="startPoint">开始的单元格：例-A1</param>
/// <param name="endPoint">结束的单元格：例-B1</param>
/// <returns>样式</returns>
public NPOIStyle SetRangeStyle(string startPoint, string endPoint)
{
return new NPOIStyle(workbook, sheet, startPoint, endPoint);
} #endregion #region 导入
/// <summary>
/// 读取Excel到DataTable,默认第一行为列名
/// </summary>
/// <param name="headRowCount">表头的行数，从表头下一行开始是数据</param>
/// <returns>DataTable</returns>
public DataTable ReadExcelToDataTable(int headRowCount = )
{ using (FileStream file = new FileStream(inPutPath, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(file);
} sheet = workbook.GetSheetAt();
DataTable dtl = new DataTable(sheet.SheetName.Trim()); IRow headRow = sheet.GetRow(); int columnCount = headRow.Cells.Count; for (int index = ; index <= columnCount - ; index++)
{
if (headRow.Cells[index].Equals(null))
dtl.Columns.Add(string.Empty, typeof (string));
else
{
if(CellType.Numeric==headRow.Cells[index].CellType)
dtl.Columns.Add(headRow.Cells[index].NumericCellValue.ToString().Trim(), typeof(string));
else
dtl.Columns.Add(headRow.Cells[index].StringCellValue.Trim(), typeof(string));
} } for (int x = headRowCount; x <= sheet.LastRowNum; x++)
{
IRow contentRow = sheet.GetRow(x);
if (contentRow.Equals(null))
continue;
else
{
DataRow dr = dtl.NewRow();
bool isEmpty = true; for (int y = ; y <= columnCount - ; y++)
{
if (contentRow.GetCell(y).Equals(null))
{
dr[y] = string.Empty;
}
else
{
ICell contentCell = contentRow.GetCell(y);
switch (contentCell.CellType)
{
case CellType.Blank:
dr[y] = string.Empty;
break;
case CellType.Boolean:
break;
case CellType.Error:
break;
case CellType.Formula:
dr[y] = contentCell.StringCellValue.Trim();
break;
case CellType.Numeric:
{
if (DateUtil.IsCellDateFormatted(contentCell))
dr[y] = contentCell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
else
dr[y] = contentCell.NumericCellValue.ToString().Trim();
}
break;
case CellType.String:
dr[y] = contentCell.StringCellValue.Trim();
break;
case CellType.Unknown:
break; }
isEmpty = string.IsNullOrEmpty(dr[y].ToString().Trim()) && isEmpty;
}
} //非全空添加行
if (!isEmpty) dtl.Rows.Add(dr);
}
}
return dtl;
}
#endregion #region 导出
/// <summary>
/// 设置数据源
/// </summary>
/// <param name="dt">数据</param>
/// <param name="headEmptyLineCount">最上方留出的空行</param>
public void SetDataSource(DataTable dt, int headEmptyLineCount = )
{ try
{
int TotalNum = dt.Rows.Count + headEmptyLineCount + ;// （加上 空行 ） //添加数据
addData(TotalNum, headEmptyLineCount + , sheet, dt);
//标题设置
for (int i = ; i < dt.Columns.Count; i++)
{
sheet.GetRow(headEmptyLineCount).GetCell(i).SetCellValue(dt.Columns[i].ColumnName);
} }
catch (Exception ex)
{
throw ex;
} }
/// <summary>
/// 导出文件
/// </summary>
/// <param name="fileName">文件名，不指定为随机生成</param>
public void ExportExcelFile(string fileName = "")
{
Random ran = new Random();
if (string.IsNullOrEmpty(fileName))
{
fileName = "\\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ran.Next(, ).ToString() + ".xlsx";
}
else
{
fileName = "\\" + fileName + ".xlsx";
} string CreatePath = outPutPath + fileName;
FileStream NewFile = new FileStream(CreatePath, FileMode.Create);
workbook.Write(NewFile);
}
private static void addData(int TotalNum, int headCount, ISheet sheet, DataTable dt)
{
//添加数据
for (int i = ; i < TotalNum; i++)
{
IRow row = sheet.CreateRow(i); for (int j = ; j < dt.Columns.Count; j++)
{
row.CreateCell(j);
if (i >= headCount)
{
try
{
double cellData = double.Parse(dt.Rows[i - headCount][j].ToString());
row.CreateCell(j).SetCellValue(cellData);
}
catch
{
string cellData = dt.Rows[i - headCount][j].ToString();
if (cellData.Trim().Length > && cellData.Substring(, ) == "'")
{ cellData = cellData.Substring(, cellData.Length - ); }
row.CreateCell(j).SetCellValue(cellData);
}
}
}
}
}
#endregion
}
/// <summary>
/// 设置样式
/// </summary>
public class NPOIStyle
{
public Range range { get; set; }
public ISheet sheet { get; set; }
public ICellStyle cellStyle { get; set; }
public IFont cellFontStyle { get; set; } public NPOIStyle(IWorkbook workbook, ISheet sheet, string startPoint, string endPoint)
{
cellStyle = workbook.CreateCellStyle();
cellFontStyle = workbook.CreateFont();
this.sheet = sheet;
range = GetRange(startPoint, endPoint); } public enum Alignment
{
Center, Fill, Left, Right, Bottom, Top
}
public enum NPOIColor : short
{
Black = , Brown = , OliveGreen = , DarkGreen = , DarkTeal = ,
DarkBlue = , Indigo = , Grey80Percent = , DarkRed = , Orange = ,
DarkYellow = , Green = , Teal = , Blue = , BlueGrey = ,
Grey50Percent = , Red = , LightOrange = , Lime = , SeaGreen = ,
Aqua = , LightBlue = , Violet = , Grey40Percent = , Pink = ,
Gold = , Yellow = , BrightGreen = , Turquoise = , SkyBlue = ,
Plum = , Grey25Percent = , Rose = , Tan = , LightYellow = ,
LightGreen = , LightTurquoise = , PaleBlue = , Lavender = ,
White = , CornflowerBlue = , LemonChiffon = , Maroon = ,
Orchid = , Coral = , RoyalBlue = , LightCornflowerBlue = , Automatic =
}
/// <summary>
/// 把Excel列名字母转换为 数字坐标
/// </summary>
/// <param name="character">Excel列名</param>
/// <returns>转换后的数字</returns>
public static int Asc(string character)
{
character = character.ToUpper();
char[] arry = character.ToCharArray();
int total = ;
for (int i = ; i < character.Length; i++)
{
total += (Convert.ToInt32(arry[i]) - ) + * i;
}
return total;
}
/// <summary>
/// 把‘A1’类型转换成Point类型
/// </summary>
/// <param name="point">字符点</param>
/// <returns>Point</returns>
public static Point GetPoint(string point)
{
Point newPoint = new Point();
char[] arry = point.ToCharArray(); int index = ;
for (int i = ; i < point.Length; i++)
{
if (Char.IsNumber(arry[i]))
{
index = i;
break;
}
}
newPoint.Y = Asc(point.Substring(, index));
newPoint.X = int.Parse(point.Substring(index, point.Length - index)) - ;
return newPoint;
}
/// <summary>
/// 根据两个点确定一个范围
/// </summary>
/// <param name="startPosition">Excel上的开始点</param>
/// <param name="endPosition">Excel上的结束点</param>
/// <returns>范围</returns>
public Range GetRange(string startPosition, string endPosition)
{
Range range = new Range();
Point start = GetPoint(startPosition);
Point end = GetPoint(endPosition); range.FIRSTROW = start.X;
range.LASTROW = end.X;
range.FIRSTCOL = start.Y;
range.LASTCOL = end.Y; for (int row = range.FIRSTROW; row <= range.LASTROW; row++)
{
if (sheet.GetRow(row) == null) sheet.CreateRow(row); for (int col = range.FIRSTCOL; col <= range.LASTCOL; col++)
{
if (sheet.GetRow(row).GetCell(col) == null) sheet.GetRow(row).CreateCell(col);
}
} return range;
}
/// <summary>
/// 设置样式的必要设置：设置样式的范围
/// </summary>
/// <param name="startPoint">范围起始点</param>
/// <param name="endPoint">范围结束点</param>
/// <returns>设置样式的范围</returns>
public NPOIStyle SetRange(string startPoint, string endPoint)
{
this.range = GetRange(startPoint, endPoint);
return this;
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="startPosition">合并开始的单元格</param>
/// <param name="endPosition">合并结束的单元格</param>
/// <returns></returns>
public NPOIStyle SetCellMerge()
{
//把excel单元格带字母坐标 转换为 数字坐标
CellRangeAddress cellRangeAddress = new CellRangeAddress(range.FIRSTROW, range.LASTROW, range.FIRSTCOL, range.LASTCOL);
this.sheet.AddMergedRegion(cellRangeAddress);
return this;
}
/// <summary>
/// 设置字体大小
/// </summary>
/// <param name="size">字体大小:9,10,11...</param>
/// <returns>Manager</returns>
public NPOIStyle SetFontSize(short size)
{
cellFontStyle.FontHeightInPoints = size;
return this;
}
/// <summary>
/// 设置sheet页面上列的宽
/// </summary>
/// <param name="size"></param>
/// <returns></returns>
public NPOIStyle SetSheetColumnWidthSize(short size)
{
sheet.DefaultColumnWidth = size;
return this;
}
/// <summary>
/// 设置字体样式
/// </summary>
/// <param name="FontName">字体:宋体,黑体...</param>
/// <returns>Manager</returns>
public NPOIStyle SetFontName(string FontName)
{
cellFontStyle.FontName = FontName;
return this;
}
/// <summary>
/// 设置字体加粗
/// </summary>
/// <returns>Manager</returns>
public NPOIStyle SetFontBold()
{
cellFontStyle.Boldweight = (short)FontBoldWeight.Bold;
return this;
}
/// <summary>
/// 设置字体下划线
/// </summary>
/// <returns>Manager</returns>
public NPOIStyle SetFontUnderline()
{
cellFontStyle.Underline = FontUnderlineType.Single;
return this;
}
/// <summary>
/// 设置字体下倾斜
/// </summary>
/// <returns>Manager</returns>
public NPOIStyle SetFontItalic()
{
cellFontStyle.IsItalic = true;
return this;
}
/// <summary>
/// 设置字体水平对齐
/// </summary>
/// <returns>Manager</returns>
public NPOIStyle SetHorizontalAlignment(Alignment alignment)
{
switch (alignment)
{
case Alignment.Center:
cellStyle.Alignment = HorizontalAlignment.Center;
break;
case Alignment.Fill:
cellStyle.Alignment = HorizontalAlignment.Fill;
break;
case Alignment.Left:
cellStyle.Alignment = HorizontalAlignment.Left;
break;
case Alignment.Right:
cellStyle.Alignment = HorizontalAlignment.Right;
break;
default:
break;
}
return this;
}
/// <summary>
/// 设置字体垂直对齐
/// </summary>
/// <returns>Manager</returns>
public NPOIStyle SetVerticalAlignment(Alignment alignment)
{
switch (alignment)
{
case Alignment.Center:
cellStyle.VerticalAlignment = VerticalAlignment.Center;
break;
case Alignment.Fill:
cellStyle.VerticalAlignment = VerticalAlignment.Justify;
break;
case Alignment.Top:
cellStyle.VerticalAlignment = VerticalAlignment.Top;
break;
case Alignment.Bottom:
cellStyle.VerticalAlignment = VerticalAlignment.Bottom;
break;
default:
break;
}
return this;
}
/// <summary>
/// 设置字体颜色
/// </summary>
/// <param name="FontName">字体:宋体,黑体...</param>
/// <returns>Manager</returns>
public NPOIStyle SetFontColor(NPOIColor color)
{
cellFontStyle.Color = (short)color;
return this;
}
/// <summary>
/// 设置单元格背景色
/// </summary>
/// <param name="color"></param>
/// <returns></returns>
public NPOIStyle SetBackgroundColor(NPOIColor color)
{
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.FillForegroundColor = (short)color;
return this;
}
/// <summary>
/// 设置单元格的文字
/// </summary>
/// <param name="position"></param>
/// <param name="text"></param>
public NPOIStyle SetCellText(string text)
{
sheet.GetRow(range.FIRSTROW).GetCell(range.FIRSTCOL).SetCellValue(text);
return this;
}
/// <summary>
/// 设置边框（Range内每个单元格的四边）
/// </summary>
/// <returns></returns>
public NPOIStyle SetBorderAll()
{
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
return this;
}
public NPOIStyle SetBorderBottom()
{
cellStyle.BorderBottom = BorderStyle.Thin;
return this;
}
public NPOIStyle SetBorderLeft()
{
cellStyle.BorderLeft = BorderStyle.Thin;
return this;
}
public NPOIStyle SetBorderRight()
{
cellStyle.BorderRight = BorderStyle.Thin;
return this;
}
public NPOIStyle SetBorderTop()
{
cellStyle.BorderTop = BorderStyle.Thin;
return this;
} /// <summary>
/// 样式设置结束，调用此方法生效
/// </summary>
public void SetEnd()
{
for (int i = range.FIRSTROW; i <= range.LASTROW; i++)
{
for (int j = range.FIRSTCOL; j <= range.LASTCOL; j++)
{
cellStyle.SetFont(cellFontStyle);
sheet.GetRow(i).GetCell(j).CellStyle = cellStyle;
}
}
}
}
}