使用OpenXml和C#从区域中获取所有行/单元格值的步骤如下:
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
public static List<List<string>> GetValuesFromRange(string filePath, string sheetName, string range)
{
List<List<string>> values = new List<List<string>>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
if (sheet != null)
{
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string[] rangeParts = range.Split(':');
string startCellReference = rangeParts[0];
string endCellReference = rangeParts[1];
IEnumerable<Cell> cells = sheetData.Descendants<Cell>()
.Where(c => string.Compare(c.CellReference.Value, startCellReference) >= 0 &&
string.Compare(c.CellReference.Value, endCellReference) <= 0);
foreach (Cell cell in cells)
{
string cellValue = GetCellValue(cell, workbookPart);
string cellReference = cell.CellReference.Value;
int rowIndex = GetRowIndex(cellReference);
int columnIndex = GetColumnIndex(cellReference);
if (values.Count <= rowIndex)
{
values.Add(new List<string>());
}
while (values[rowIndex].Count < columnIndex)
{
values[rowIndex].Add("");
}
values[rowIndex].Add(cellValue);
}
}
}
return values;
}
private static string GetCellValue(Cell cell, WorkbookPart workbookPart)
{
string cellValue = "";
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
SharedStringTablePart sharedStringTablePart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (sharedStringTablePart != null)
{
SharedStringItem sharedStringItem = sharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(cell.CellValue.Text));
if (sharedStringItem != null)
{
cellValue = sharedStringItem.Text.Text;
}
}
}
else
{
cellValue = cell.CellValue?.Text ?? "";
}
return cellValue;
}
private static int GetRowIndex(string cellReference)
{
string rowIndex = Regex.Replace(cellReference, "[^0-9]+", "");
return int.Parse(rowIndex) - 1;
}
private static int GetColumnIndex(string cellReference)
{
string columnName = Regex.Replace(cellReference, "[^A-Z]+", "");
int columnIndex = 0;
int factor = 1;
for (int i = columnName.Length - 1; i >= 0; i--)
{
columnIndex += (columnName[i] - 'A' + 1) * factor;
factor *= 26;
}
return columnIndex - 1;
}
string filePath = "路径/文件名.xlsx";
string sheetName = "工作表名称";
string range = "起始单元格:结束单元格";
List<List<string>> values = GetValuesFromRange(filePath, sheetName, range);
foreach (List<string> row in values)
{
foreach (string cellValue in row)
{
Console.Write(cellValue + "\t");
}
Console.WriteLine();
}
以上代码将打开指定的Excel文件,选择指定的工作表和区域,并将区域中的所有行/单元格值存储在一个二维列表中。然后,可以根据需要对这些值进行进一步处理或输出。
注意:在使用OpenXml SDK时,需要确保Excel文件的格式是OpenXml格式(.xlsx)。
领取专属 10元无门槛券
手把手带您无忧上云