DataExcel控件读取和保存excel xlsx 格式文件

需要引用NPOI库  https://github.com/dotnetcore/NPOI

调用Read 函数将excel读取到dataexcel控件

调用Save 函数将dataexcel控件文件保存为excel文件

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;namespace Feng.DataTool
{public class ExcelTools{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){if (gridrow.Index < 1)continue;IRow row = sheet1.CreateRow(gridrow.Index-1);row.Height = (short)(gridrow.Height * 20);foreach (Feng.Excel.Interfaces.IColumn column in grid.Columns){if (column.Index < 1)continue;Feng.Excel.Interfaces.ICell gridcell = gridrow[column];if (gridcell == null)continue;ushort width = (ushort)((column.Width) / 7 * 256);sheet1.SetColumnWidth(column.Index-1, width);ICell cell = row.CreateCell(column.Index-1);string text = gridcell.Text;cell.SetCellValue(text);bool bottomlinestylevisible = false;bool leftlinestylevisible = false;bool rightlinestylevisible = false;bool toplinestylevisible = false;bool isstrikeout = false;bool isbold = 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, isstrikeout, isbold, fontheightinpoints, fontname, alignment, verticalalignment);cell.CellStyle = style;}}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);stream.Close();}public static int GetHAlignment(StringAlignment alignment){switch (alignment){case StringAlignment.Near:return 1; case StringAlignment.Center:return 2;case StringAlignment.Far:return 3;default:break;}return 0;}public static int GetVAlignment(StringAlignment alignment){switch (alignment){case StringAlignment.Near:return 0;case StringAlignment.Center:return 1;case StringAlignment.Far:return 2;default:break;}return 0;}public class CellStyleTemp{public 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, int alignment, int verticalalignment){ foreach (CellStyleTemp item in styles){ if (item.BottomLineStyleVisible != bottomlinestylevisible){continue;}double upfontheightinpoints = fontheightinpoints + 0.1;double downfontheightinpoints = fontheightinpoints - 0.1;if (!((item.FontHeightInPoints > downfontheightinpoints) && (item.FontHeightInPoints < upfontheightinpoints))){continue;}if (item.FontName != fontname){continue;}if (item.IsBold != isbold){continue;}if (item.IsStrikeout != isstrikeout){continue;}if (item.LeftLineStyleVisible != leftlinestylevisible){continue;}if (item.RightLineStyleVisible != rightlinestylevisible){continue;}if (item.TopLineStyleVisible != toplinestylevisible){continue;}if (item.Alignment != alignment){continue;}if (item.VerticalAlignment != verticalalignment){continue;}return item.style;}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, isstrikeout, isbold, fontheightinpoints, fontname, alignment, verticalalignment);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, int alignment, int verticalalignment){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;}if (TopLineStyleVisible){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;if (IsStrikeout){font2.IsStrikeout = true;}if (IsBold){font2.IsBold = true;}font2.FontHeightInPoints = FontHeightInPoints;font2.FontName = FontName;style.SetFont(font2);return style;}public static void Read(Feng.Excel.DataExcel grid, string file){FileStream stream = new FileStream(file, FileMode.Open);HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);stream.Close();ISheet sheet = hssfworkbook.GetSheetAt(0);int rowcount = sheet.LastRowNum;bool hassetcolumnwidth = false;for (int rowindex = 0; rowindex < rowcount; rowindex++){IRow row = sheet.GetRow(rowindex);if (row == null)continue;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);if (cell == null)continue;Feng.Excel.Interfaces.ICell gridcell = grid.GetCell(gridrow.Index, gridcolumn.Index);gridcell.BorderStyle = new Excel.Styles.CellBorderStyle();try{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;}}catch (Exception ex){Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);}try{IFont font2 = cell.CellStyle.GetFont(hssfworkbook);System.Drawing.FontStyle fontstyle = System.Drawing.FontStyle.Regular;if (font2.IsStrikeout){fontstyle = fontstyle | System.Drawing.FontStyle.Strikeout;}if (font2.IsBold){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);}catch (Exception ex){Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);}try{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;}}catch (Exception ex){ Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);}try{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;}}catch (Exception ex){Feng.Utils.TraceHelper.WriteTrace("SysTools", "ExcelTools", "Read", ex);}}}List<CellRangeAddress> listmer = sheet.MergedRegions;if (listmer != null){foreach (CellRangeAddress item in listmer){grid.MergeCell(item.FirstRow + 1, item.FirstColumn + 1, item.LastRow + 1, item.LastColumn + 1);}}}}
}