第04章-工作表与单元格操作

第四章：工作表与单元格操作

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：使用CellPosition
        sheet.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：使用GetCellData
        object 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中工作表和单元格的各种操作，包括数据读写、格式化、样式设置、单元格合并以及行列操作等核心功能。

📚 下一章预告

第五章将学习数据类型与格式化的高级应用。

---

继续学习第五章！