调用Read 函数将excel读取到dataexcel控件
调用Save 函数将dataexcel控件文件保存为excel文件
using NPOI.HSSF.UserModel;
using System.Collections.Generic;
using System.Windows.Forms;
public static void Save(Feng.Excel.DataExcel grid, string file)
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
List<CellStyleTemp> cellstyletemplist = new List<CellStyleTemp>();
foreach (Feng.Excel.Interfaces.IRow gridrow in grid.Rows)
IRow row = sheet1.CreateRow(gridrow.Index-1);
row.Height = (short)(gridrow.Height * 20);
foreach (Feng.Excel.Interfaces.IColumn column in grid.Columns)
Feng.Excel.Interfaces.ICell gridcell = gridrow[column];
ushort width = (ushort)((column.Width) / 7 * 256);
sheet1.SetColumnWidth(column.Index-1, width);
ICell cell = row.CreateCell(column.Index-1);
string text = gridcell.Text;
bool bottomlinestylevisible = false;
bool leftlinestylevisible = false;
bool rightlinestylevisible = false;
bool toplinestylevisible = false;
bool isstrikeout = false;
double fontheightinpoints = 29;
string fontname = string.Empty;
if (gridcell.BorderStyle != null)
if (gridcell.BorderStyle.BottomLineStyle != null)
if (gridcell.BorderStyle.BottomLineStyle.Visible)
bottomlinestylevisible = true;
if (gridcell.BorderStyle.LeftLineStyle != null)
if (gridcell.BorderStyle.LeftLineStyle.Visible)
leftlinestylevisible = true;
if (gridcell.BorderStyle.RightLineStyle != null)
if (gridcell.BorderStyle.RightLineStyle.Visible)
rightlinestylevisible = true;
if (gridcell.BorderStyle.TopLineStyle != null)
if (gridcell.BorderStyle.TopLineStyle.Visible)
toplinestylevisible = true;
isstrikeout = gridcell.Font.Strikeout;
isbold = gridcell.Font.Bold;
fontheightinpoints = gridcell.Font.Size;
fontname = gridcell.Font.Name;
int alignment = GetHAlignment(gridcell.HorizontalAlignment);
int verticalalignment = GetVAlignment(gridcell.VerticalAlignment);
ICellStyle style = GetCellStyle(hssfworkbook, cellstyletemplist
, bottomlinestylevisible, leftlinestylevisible
, rightlinestylevisible, toplinestylevisible
, fontheightinpoints, fontname
, alignment, verticalalignment);
foreach (Feng.Excel.Interfaces.IMergeCell item in grid.MergeCells)
sheet1.AddMergedRegion(new CellRangeAddress(item.MinCell.Row.Index-1,item.MaxRowIndex - 1, item.MinCell.Column.Index - 1, item.MaxColumnIndex - 1));
//sheet1.AddMergedRegion(new CellRangeAddress(2, 7, 2, 7));
FileStream stream = new FileStream(file, FileMode.Create);
hssfworkbook.Write(stream);
public static int GetHAlignment(StringAlignment alignment)
case StringAlignment.Near:
case StringAlignment.Center:
case StringAlignment.Far:
public static int GetVAlignment(StringAlignment alignment)
case StringAlignment.Near:
case StringAlignment.Center:
case StringAlignment.Far:
public class CellStyleTemp
public bool BottomLineStyleVisible { get; set; }
public bool LeftLineStyleVisible { get; set; }
public bool RightLineStyleVisible { get; set; }
public bool TopLineStyleVisible { get; set; }
public bool IsStrikeout { get; set; }
public bool IsBold { get; set; }
public double FontHeightInPoints { get; set; }
public string FontName { get; set; }
public int Alignment { get; set; }
public int VerticalAlignment { get; set; }
public ICellStyle style { get; set; }
public static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, List<CellStyleTemp> styles
, bool bottomlinestylevisible, bool leftlinestylevisible
, bool rightlinestylevisible, bool toplinestylevisible
, bool isstrikeout, bool isbold
, double fontheightinpoints, string fontname
foreach (CellStyleTemp item in styles)
if (item.BottomLineStyleVisible != bottomlinestylevisible)
double upfontheightinpoints = fontheightinpoints + 0.1;
double downfontheightinpoints = fontheightinpoints - 0.1;
if (!((item.FontHeightInPoints > downfontheightinpoints) && (item.FontHeightInPoints < upfontheightinpoints)))
if (item.FontName != fontname)
if (item.IsBold != isbold)
if (item.IsStrikeout != isstrikeout)
if (item.LeftLineStyleVisible != leftlinestylevisible)
if (item.RightLineStyleVisible != rightlinestylevisible)
if (item.TopLineStyleVisible != toplinestylevisible)
if (item.Alignment != alignment)
if (item.VerticalAlignment != verticalalignment)
CellStyleTemp cellStyleTemp = new CellStyleTemp();
cellStyleTemp.BottomLineStyleVisible = bottomlinestylevisible;
cellStyleTemp.FontHeightInPoints = fontheightinpoints;
cellStyleTemp.FontName = fontname;
cellStyleTemp.IsBold = isbold;
cellStyleTemp.IsStrikeout = isstrikeout;
cellStyleTemp.LeftLineStyleVisible = leftlinestylevisible;
cellStyleTemp.RightLineStyleVisible = rightlinestylevisible;
cellStyleTemp.TopLineStyleVisible = toplinestylevisible;
cellStyleTemp.Alignment = alignment;
cellStyleTemp.VerticalAlignment = verticalalignment;
ICellStyle style = CreateCellStyle(hssfworkbook
, bottomlinestylevisible, leftlinestylevisible
, rightlinestylevisible, toplinestylevisible
, fontheightinpoints, fontname, alignment
cellStyleTemp.style = style;
styles.Add(cellStyleTemp);
return cellStyleTemp.style;
public static ICellStyle CreateCellStyle(HSSFWorkbook hssfworkbook
, bool BottomLineStyleVisible, bool LeftLineStyleVisible
, bool RightLineStyleVisible, bool TopLineStyleVisible
, bool IsStrikeout, bool IsBold
, double FontHeightInPoints, string FontName
ICellStyle style = hssfworkbook.CreateCellStyle();
if (BottomLineStyleVisible)
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
if (LeftLineStyleVisible)
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
if (RightLineStyleVisible)
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.Alignment = (NPOI.SS.UserModel.HorizontalAlignment)alignment;
style.VerticalAlignment = (NPOI.SS.UserModel.VerticalAlignment)verticalalignment;
IFont font2 = hssfworkbook.CreateFont();
font2.Color = HSSFColor.Black.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = FontHeightInPoints;
font2.FontName = FontName;
public static void Read(Feng.Excel.DataExcel grid, string file)
FileStream stream = new FileStream(file, FileMode.Open);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
ISheet sheet = hssfworkbook.GetSheetAt(0);
int rowcount = sheet.LastRowNum;
bool hassetcolumnwidth = false;
for (int rowindex = 0; rowindex < rowcount; rowindex++)
IRow row = sheet.GetRow(rowindex);
Feng.Excel.Interfaces.IRow gridrow = grid.GetRow(rowindex + 1);
gridrow.Height = row.Height / 20;
short columncount = row.LastCellNum;
for (short columnindex = 0; columnindex < columncount; columnindex++)
Feng.Excel.Interfaces.IColumn gridcolumn = grid.GetColumn(columnindex + 1);
int width = sheet.GetColumnWidth(columnindex);
gridcolumn.Width = (width / 256) * 7;
ICell cell = row.GetCell(columnindex);
Feng.Excel.Interfaces.ICell gridcell = grid.GetCell(gridrow.Index, gridcolumn.Index);
gridcell.BorderStyle = new Excel.Styles.CellBorderStyle();
if (cell.CellType == CellType.String)
gridcell.Value = cell.StringCellValue;
if (cell.CellType == CellType.Numeric)
gridcell.Value = cell.NumericCellValue;
if (cell.CellType == CellType.Boolean)
gridcell.Value = cell.BooleanCellValue;
if (cell.CellType == CellType.Unknown)
gridcell.Value = cell.StringCellValue;
if (cell.CellStyle.BorderBottom == NPOI.SS.UserModel.BorderStyle.Thin)
gridcell.BorderStyle.BottomLineStyle = new Excel.Styles.LineStyle();
gridcell.BorderStyle.BottomLineStyle.Visible = true;
if (cell.CellStyle.BorderLeft == NPOI.SS.UserModel.BorderStyle.Thin)
gridcell.BorderStyle.LeftLineStyle = new Excel.Styles.LineStyle();
gridcell.BorderStyle.LeftLineStyle.Visible = true;
if (cell.CellStyle.BorderRight == NPOI.SS.UserModel.BorderStyle.Thin)
gridcell.BorderStyle.RightLineStyle = new Excel.Styles.LineStyle();
gridcell.BorderStyle.RightLineStyle.Visible = true;
if (cell.CellStyle.BorderTop == NPOI.SS.UserModel.BorderStyle.Thin)
gridcell.BorderStyle.TopLineStyle = new Excel.Styles.LineStyle();
gridcell.BorderStyle.TopLineStyle.Visible = true;
Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);
IFont font2 = cell.CellStyle.GetFont(hssfworkbook);
System.Drawing.FontStyle fontstyle = System.Drawing.FontStyle.Regular;
fontstyle = fontstyle | System.Drawing.FontStyle.Strikeout;
fontstyle = fontstyle | System.Drawing.FontStyle.Bold;
font2.FontHeightInPoints = gridcell.Font.Size;
font2.FontName = gridcell.Font.Name;
gridcell.Font = new System.Drawing.Font(font2.FontName, (float)font2.FontHeightInPoints, fontstyle);
Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);
if (cell.CellStyle.Alignment == NPOI.SS.UserModel.HorizontalAlignment.Center)
gridcell.HorizontalAlignment = StringAlignment.Center;
if (cell.CellStyle.Alignment == NPOI.SS.UserModel.HorizontalAlignment.Left)
gridcell.HorizontalAlignment = StringAlignment.Near;
if (cell.CellStyle.Alignment == NPOI.SS.UserModel.HorizontalAlignment.Right)
gridcell.HorizontalAlignment = StringAlignment.Far;
Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);
if (cell.CellStyle.VerticalAlignment == NPOI.SS.UserModel.VerticalAlignment.Center)
gridcell.VerticalAlignment = StringAlignment.Center;
if (cell.CellStyle.VerticalAlignment == NPOI.SS.UserModel.VerticalAlignment.Top)
gridcell.VerticalAlignment = StringAlignment.Near;
if (cell.CellStyle.VerticalAlignment == NPOI.SS.UserModel.VerticalAlignment.Bottom)
gridcell.VerticalAlignment = StringAlignment.Far;
Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);
List<CellRangeAddress> listmer = sheet.MergedRegions;
foreach (CellRangeAddress item in listmer)
grid.MergeCell(item.FirstRow + 1, item.FirstColumn + 1, item.LastRow + 1, item.LastColumn + 1);