C#读表格

这里使用Open XML SDK读xml和xlsx表格

读XLSX

打开一个xlsx

static bool readResourceXlsxTable(string xlsx_path, string prefix_path, Dictionary<string, string> id_to_texture_path)
{
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(xlsx_path, false))
    {
        OpenXmlElementList childs = doc.WorkbookPart.Workbook.Sheets.ChildElements;
        OpenXmlElement sheet_element = childs.GetItem(0);
        Sheet sheet = (Sheet)sheet_element;
        Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
        SharedStringTablePart string_table_part = doc.WorkbookPart.SharedStringTablePart;
        int excel_row_count = rows.Count();
        if (excel_row_count > 0)
        {
            List<string> string_table = new List<string>();
            foreach (OpenXmlElement node in string_table_part.SharedStringTable.ChildElements)
            {
                string_table.Add(node.InnerText);
            }
            // MARK: 这里不是真正的row和column
            for (int row_index = 0; row_index < excel_row_count; row_index++)
            {
                var cells = rows.ElementAt(row_index).Descendants<Cell>().ToList();
                for(int column_index = 0; column_index < cells.Length; column_index++)
                {
                    Cell cell = cells.ElementAt(column_index);
                }
            }
        }
    }
    return true;
}

读单元格数据

值得注意的是，单元格数据是有类型的，尤其是字符串，有的是直接内嵌到cell.CellValue.InnerText中，有的是放在SharedStringTablePart中，单元格内只保留一个引用

于是我们需要先拿到共享字符串表的内容，然后用引用ID去得到真正的字符串

SharedStringTablePart string_table_part = doc.WorkbookPart.SharedStringTablePart;
List<string> string_table = new List<string>();
foreach (OpenXmlElement node in string_table_part.SharedStringTable.ChildElements)
{
    string_table.Add(node.InnerText);
}

if (cell.DataType == CellValues.SharedString)
{
    text = string_table.ElementAt(Convert.ToInt32(cell.CellValue.InnerText));
}
else
{
    text = cell.CellValue.InnerText;
}

单元格坐标

我们使用Excel打开一个表格，每个单元格是通过英文字母和数字拼成一个ID，这个是真正的坐标，存储在cell.CellReference中，我们上面的遍历，如果遇到空单元格，会出现问题（会认为空单元格不存在）

可以解析CellReference获得真正的坐标

static void GetRowAndColumn(string cellReference, out int row, out int column)
{
    row = 0;
    column = 0;
    string columnLetters = "";
    // column index
    foreach (char c in cellReference)
    {
        if (char.IsLetter(c))
        {
            columnLetters += c;
        }
        else
        {
            break;
        }
    }
    column = ColumnLettersToNumber(columnLetters);
    // row index
    int.TryParse(cellReference.Substring(columnLetters.Length), out row);
}

static int ColumnLettersToNumber(string letters)
{
    int result = 0;
    foreach (char c in letters)
    {
        result = result * 26 + (c - 'A' + 1);
    }
    return result;
}

GetRowAndColumn(cell.CellReference, out int real_row, out int real_column);

读XML

这个也可以读XML

打开一个xml

static bool readResourceXmlTable(string xml_path, string prefix_path, Dictionary<string, string> id_to_texture_path)
{
    XmlDocument xml_doc = new XmlDocument();
    try
    {
        xml_doc.Load(xml_path);
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error: {ex.Message}");
    }
    return true;
}

访问Sheet

用Excel打开一个表格，我们可以看到左下角有好多Sheet

XmlNamespaceManager ns_manager = new XmlNamespaceManager(xml_doc.NameTable);
ns_manager.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");
XmlNodeList work_sheet_nodes = xml_doc.SelectNodes("//ss:Worksheet", ns_manager);
foreach (XmlNode work_sheet_node in work_sheet_nodes)
{
    XmlAttribute name_attribute = work_sheet_node.Attributes["ss:Name"];
    if (name_attribute != null)
    {
        XmlNode table_node = work_sheet_node.SelectSingleNode("ss:Table", ns_manager);
    }
}

访问行和列

XmlNode table_node = work_sheet_node.SelectSingleNode("ss:Table", ns_manager);
XmlAttribute row_count_attribute = table_node.Attributes["ss:ExpandedRowCount"];
XmlNodeList row_nodes = table_node.SelectNodes("ss:Row", ns_manager);
foreach (XmlNode row_node in row_nodes)
{
    XmlNodeList cell_nodes = row_node.SelectNodes("ss:Cell", ns_manager);
    foreach (XmlNode column_node in cell_nodes)
    {
        
    }
}