第四章：工作表与单元格操作
4.1 单元格数据读写
4.1.1 基本读写操作
using unvell.ReoGrid;public class CellBasicOperations
{private Worksheet sheet;public CellBasicOperations(ReoGridControl grid){sheet = grid.CurrentWorksheet;}/// <summary>/// 写入数据的多种方式/// </summary>public void WriteDataExamples(){// 方式1：使用字符串地址sheet["A1"] = "Hello ReoGrid";// 方式2：使用数字索引 [row, col]sheet[0, 1] = 12345;// 方式3：使用CellPositionsheet.SetCellData(new CellPosition("C1"), DateTime.Now);// 方式4：使用行列索引sheet.SetCellData(0, 3, true);// 方式5：指定数据格式sheet.SetCellData(0, 4, "123.45", CellDataFormatFlag.Number);}/// <summary>/// 读取数据的多种方式/// </summary>public void ReadDataExamples(){// 方式1：使用字符串地址object value1 = sheet["A1"];// 方式2：使用数字索引object value2 = sheet[0, 1];// 方式3：使用GetCellDataobject value3 = sheet.GetCellData(0, 2);// 方式4：获取Cell对象var cell = sheet.GetCell(0, 3);if (cell != null){object data = cell.Data;string text = cell.DisplayText;}}
}
4.1.2 类型转换与处理
using System;
using unvell.ReoGrid;public class CellDataTypeOperations
{private Worksheet sheet;public CellDataTypeOperations(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 获取字符串类型数据/// </summary>public string GetStringValue(string address){var cell = sheet.GetCell(new CellPosition(address));return cell?.Data?.ToString() ?? string.Empty;}/// <summary>/// 获取数值类型数据/// </summary>public double? GetNumericValue(string address){var cell = sheet.GetCell(new CellPosition(address));if (cell != null && cell.Data != null){if (double.TryParse(cell.Data.ToString(), out double result)){return result;}}return null;}/// <summary>/// 获取日期类型数据/// </summary>public DateTime? GetDateTimeValue(string address){var cell = sheet.GetCell(new CellPosition(address));if (cell != null && cell.Data is DateTime dt){return dt;}return null;}/// <summary>/// 获取布尔类型数据/// </summary>public bool? GetBooleanValue(string address){var cell = sheet.GetCell(new CellPosition(address));if (cell != null && cell.Data is bool b){return b;}return null;}
}
4.1.3 批量数据操作
using System.Collections.Generic;
using unvell.ReoGrid;public class BatchDataOperations
{private Worksheet sheet;public BatchDataOperations(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 批量写入二维数组数据/// </summary>public void WriteBatch2DArray(int startRow, int startCol, object[,] data){int rows = data.GetLength(0);int cols = data.GetLength(1);for (int r = 0; r < rows; r++){for (int c = 0; c < cols; c++){sheet[startRow + r, startCol + c] = data[r, c];}}}/// <summary>/// 批量写入列表数据/// </summary>public void WriteListData<T>(int startRow, int startCol, List<T> dataList){var properties = typeof(T).GetProperties();// 写入表头for (int i = 0; i < properties.Length; i++){sheet[startRow, startCol + i] = properties[i].Name;}// 写入数据for (int row = 0; row < dataList.Count; row++){var item = dataList[row];for (int col = 0; col < properties.Length; col++){var value = properties[col].GetValue(item);sheet[startRow + row + 1, startCol + col] = value;}}}/// <summary>/// 批量读取区域数据/// </summary>public object[,] ReadRangeData(string range){var rangePos = new RangePosition(range);object[,] data = new object[rangePos.Rows, rangePos.Cols];for (int r = 0; r < rangePos.Rows; r++){for (int c = 0; c < rangePos.Cols; c++){data[r, c] = sheet[rangePos.Row + r, rangePos.Col + c];}}return data;}/// <summary>/// 从对象列表读取数据/// </summary>public List<T> ReadToList<T>(int startRow, int startCol, int rowCount) where T : new(){var list = new List<T>();var properties = typeof(T).GetProperties();for (int r = 0; r < rowCount; r++){var item = new T();for (int c = 0; c < properties.Length; c++){var cell = sheet.GetCell(startRow + r, startCol + c);if (cell != null){var prop = properties[c];object value = Convert.ChangeType(cell.Data, prop.PropertyType);prop.SetValue(item, value);}}list.Add(item);}return list;}
}
4.2 数据格式化
4.2.1 内置格式
using unvell.ReoGrid;
using unvell.ReoGrid.Data;public class DataFormatting
{private Worksheet sheet;public DataFormatting(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 设置数字格式/// </summary>public void SetNumberFormat(string range){sheet.SetRangeDataFormat(range, CellDataFormatFlag.Number);}/// <summary>/// 设置货币格式/// </summary>public void SetCurrencyFormat(string range){sheet.SetRangeDataFormat(range, CellDataFormatFlag.Currency);}/// <summary>/// 设置百分比格式/// </summary>public void SetPercentFormat(string range){sheet.SetRangeDataFormat(range, CellDataFormatFlag.Percent);}/// <summary>/// 设置日期格式/// </summary>public void SetDateFormat(string range){sheet.SetRangeDataFormat(range, CellDataFormatFlag.DateTime);}/// <summary>/// 设置时间格式/// </summary>public void SetTimeFormat(string range){sheet.SetRangeDataFormat(range, CellDataFormatFlag.DateTime, new DateTimeDataFormatter.DateTimeFormatArgs{Format = "HH:mm:ss"});}
}
4.2.2 自定义格式
using unvell.ReoGrid;
using unvell.ReoGrid.Data;public class CustomFormatting
{private Worksheet sheet;public CustomFormatting(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 自定义数字格式/// </summary>public void SetCustomNumberFormat(string range, string pattern){// 例如: "#,##0.00" 表示千位分隔符和两位小数sheet.SetRangeDataFormat(range, CellDataFormatFlag.Number,new NumberDataFormatter.NumberFormatArgs{Pattern = pattern});}/// <summary>/// 自定义日期格式/// </summary>public void SetCustomDateFormat(string range, string format){// 例如: "yyyy-MM-dd HH:mm:ss"sheet.SetRangeDataFormat(range, CellDataFormatFlag.DateTime,new DateTimeDataFormatter.DateTimeFormatArgs{Format = format});}/// <summary>/// 电话号码格式/// </summary>public void SetPhoneFormat(string range){sheet.SetRangeDataFormat(range, CellDataFormatFlag.Text,new TextDataFormatter.FormatArgs{Pattern = "(000) 000-0000"});}
}
4.3 单元格样式
4.3.1 字体样式
using System.Drawing;
using unvell.ReoGrid.Data;public class FontStyling
{private Worksheet sheet;public FontStyling(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 设置字体基本属性/// </summary>public void SetFontStyle(string range){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.FontName | PlainStyleFlag.FontSize | PlainStyleFlag.FontColor,FontName = "微软雅黑",FontSize = 12,FontColor = Color.Blue});}/// <summary>/// 设置粗体/// </summary>public void SetBold(string range, bool bold = true){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.FontBold,Bold = bold});}/// <summary>/// 设置斜体/// </summary>public void SetItalic(string range, bool italic = true){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.FontItalic,Italic = italic});}/// <summary>/// 设置下划线/// </summary>public void SetUnderline(string range, bool underline = true){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.FontUnderline,Underline = underline});}/// <summary>/// 设置删除线/// </summary>public void SetStrikethrough(string range, bool strikethrough = true){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.FontStrikethrough,Strikethrough = strikethrough});}
}
4.3.2 对齐方式
using unvell.ReoGrid.Data;public class AlignmentStyling
{private Worksheet sheet;public AlignmentStyling(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 设置水平对齐/// </summary>public void SetHorizontalAlign(string range, ReoGridHorAlign align){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.HorizontalAlign,HAlign = align});}/// <summary>/// 设置垂直对齐/// </summary>public void SetVerticalAlign(string range, ReoGridVerAlign align){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.VerticalAlign,VAlign = align});}/// <summary>/// 设置文本换行/// </summary>public void SetTextWrap(string range, bool wrap = true){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.TextWrap,TextWrap = wrap ? TextWrapMode.WordBreak : TextWrapMode.NoWrap});}
}
4.3.3 背景和边框
using System.Drawing;
using unvell.ReoGrid.Data;
using unvell.ReoGrid.Graphics;public class BackgroundAndBorderStyling
{private Worksheet sheet;public BackgroundAndBorderStyling(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 设置背景色/// </summary>public void SetBackgroundColor(string range, Color color){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.BackColor,BackColor = color});}/// <summary>/// 设置全边框/// </summary>public void SetBorder(string range, Color color, BorderLineStyle style){sheet.SetRangeStyles(range, new WorksheetRangeStyle{Flag = PlainStyleFlag.Border,Border = new RangeBorderStyle{Left = new BorderStyle { Color = color, Style = style },Top = new BorderStyle { Color = color, Style = style },Right = new BorderStyle { Color = color, Style = style },Bottom = new BorderStyle { Color = color, Style = style }}});}/// <summary>/// 设置外边框/// </summary>public void SetOutsideBorder(string range, Color color, BorderLineStyle style){sheet.SetRangeBorders(new RangePosition(range), BorderPositions.Outside,new BorderStyle { Color = color, Style = style });}/// <summary>/// 设置内边框/// </summary>public void SetInsideBorder(string range, Color color, BorderLineStyle style){sheet.SetRangeBorders(new RangePosition(range), BorderPositions.Inside,new BorderStyle { Color = color, Style = style });}
}
4.4 单元格合并
4.4.1 合并操作
using unvell.ReoGrid;public class CellMerging
{private Worksheet sheet;public CellMerging(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 合并单元格/// </summary>public void MergeCells(string range){sheet.MergeRange(range);}/// <summary>/// 取消合并/// </summary>public void UnmergeCells(string range){sheet.UnmergeRange(range);}/// <summary>/// 检查是否已合并/// </summary>public bool IsMerged(string address){var pos = new CellPosition(address);var cell = sheet.GetCell(pos);return cell != null && cell.IsMergedCell;}/// <summary>/// 获取合并区域/// </summary>public RangePosition GetMergedRange(string address){var pos = new CellPosition(address);var cell = sheet.GetCell(pos);if (cell != null && cell.IsMergedCell){return cell.MergeRange;}return RangePosition.Empty;}
}
4.5 行列操作
4.5.1 插入和删除
using unvell.ReoGrid;public class RowColumnOperations
{private Worksheet sheet;public RowColumnOperations(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 插入行/// </summary>public void InsertRow(int row, int count = 1){sheet.InsertRows(row, count);}/// <summary>/// 删除行/// </summary>public void DeleteRow(int row, int count = 1){sheet.DeleteRows(row, count);}/// <summary>/// 插入列/// </summary>public void InsertColumn(int col, int count = 1){sheet.InsertColumns(col, count);}/// <summary>/// 删除列/// </summary>public void DeleteColumn(int col, int count = 1){sheet.DeleteColumns(col, count);}
}
4.5.2 隐藏和显示
using unvell.ReoGrid;public class RowColumnVisibility
{private Worksheet sheet;public RowColumnVisibility(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 隐藏行/// </summary>public void HideRows(int row, int count = 1){sheet.HideRows(row, count);}/// <summary>/// 显示行/// </summary>public void ShowRows(int row, int count = 1){sheet.ShowRows(row, count);}/// <summary>/// 隐藏列/// </summary>public void HideColumns(int col, int count = 1){sheet.HideColumns(col, count);}/// <summary>/// 显示列/// </summary>public void ShowColumns(int col, int count = 1){sheet.ShowColumns(col, count);}
}
4.5.3 尺寸调整
using unvell.ReoGrid;public class RowColumnSizing
{private Worksheet sheet;public RowColumnSizing(Worksheet worksheet){sheet = worksheet;}/// <summary>/// 设置行高/// </summary>public void SetRowHeight(int row, ushort height, int count = 1){sheet.SetRowsHeight(row, count, height);}/// <summary>/// 设置列宽/// </summary>public void SetColumnWidth(int col, ushort width, int count = 1){sheet.SetColumnsWidth(col, count, width);}/// <summary>/// 自动调整行高/// </summary>public void AutoFitRow(int row){sheet.AutoFitRowHeight(row);}/// <summary>/// 自动调整列宽/// </summary>public void AutoFitColumn(int col){sheet.AutoFitColumnWidth(col);}/// <summary>/// 自动调整所有列宽/// </summary>public void AutoFitAllColumns(){for (int col = 0; col < sheet.ColumnCount; col++){sheet.AutoFitColumnWidth(col);}}
}
4.6 本章小结
本章详细介绍了ReoGrid中工作表和单元格的各种操作，包括数据读写、格式化、样式设置、单元格合并以及行列操作等核心功能。
📚 下一章预告
第五章将学习数据类型与格式化的高级应用。
继续学习第五章！