c# - 找到一个特定的单元格值并在之后删除行excel c#
问题描述
我正在开发一个读取 excel 文件并在工作表中搜索特定值的程序,比如说 sheetname =“sheet3”。
之后,程序将查看特定列,比如列“D”,并在整个列中搜索数据值。如果数据值存在,则包含该数据值的整行将被删除/隐藏。
目前,我已经设法从一列的excel文件中获取数据值,但无法继续使用代码。我已经尝试了大约 3 天,但无济于事。我将不胜感激任何帮助!
这是我目前尝试过的代码:
using System.IO;
using System.Linq;
using System;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
namespace Test
{
class Program
{
static void Main(string[] args)
{
const string fileName =
@"D:\test.xlsx";
// Retrieve the value in cell D2.
string value = GetCellValue(fileName, "Sheet3", "D2");
}
// Retrieve the value of a cell, given a file name, sheet name,
// and address name.
public static string GetCellValue(string fileName,
string sheetName,
string addressName)
{
string value = null;
// Open the spreadsheet document for read-only access.
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(fileName, false))
{
// Retrieve a reference to the workbook part.
WorkbookPart wbPart = document.WorkbookPart;
// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
Where(s => s.Name == sheetName).FirstOrDefault();
// Throw an exception if there is no sheet.
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
// Retrieve a reference to the worksheet part.
WorksheetPart wsPart =
(WorksheetPart)(wbPart.GetPartById(theSheet.Id));
// Use its Worksheet property to get a reference to the cell
// whose address matches the address you supplied.
Cell theCell = wsPart.Worksheet.Descendants<Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell does not exist, return an empty string.
if (theCell != null)
{
value = theCell.InnerText;
// If the cell represents an integer number, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and
// Booleans individually. For shared strings, the code
// looks up the corresponding value in the shared string
// table. For Booleans, the code converts the value into
// the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the
// shared strings table.
var stringTable =
wbPart.GetPartsOfType<SharedStringTablePart>()
.FirstOrDefault();
// If the shared string table is missing, something
// is wrong. Return the index that is in
// the cell. Otherwise, look up the correct text in
// the table.
if (stringTable != null)
{
value =
stringTable.SharedStringTable
.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
}
return value;
}
}
}
PS 我只尝试过 OpenXML,因为我要使用的服务器没有安装任何 Microsoft Excel 或任何其他软件。因此,我无法使用 Microsoft.Office.Interop.Excel 来完成此任务。请就此事提供任何帮助。谢谢你。
解决方案
我建议您使用 NPOI 库而不是 OpenXML。NPOI 是一个开源库,无需安装任何办公软件即可读写 Microsoft Office 文档。它是 Java POI 库的 .NET 端口。您可以在网上找到许多示例。
以下 C# 控制台程序使用 NPOI 来执行您所要求的操作。
using System;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
public class Program
{
public static void Main(string[] args)
{
const string FileName = @"d:\test.xlsx";
IWorkbook workbook = OpenWorkBook(FileName);
ISheet sheet = workbook.GetSheet("Sheet3");
HideRows(sheet);
DeleteRows(sheet);
SaveWorkBook(workbook, FileName);
Console.WriteLine("Done. Press any key");
Console.ReadKey();
}
private static IWorkbook OpenWorkBook(string workBookName)
{
using (FileStream file = new FileStream(workBookName, FileMode.Open, FileAccess.Read))
{
return new XSSFWorkbook(file);
}
}
private static void HideRows(ISheet sheet)
{
for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
{
IRow row = sheet.GetRow(rowIndex);
if (row == null) continue; // Completely unused row returns null
ICell cell = row.GetCell(3); // 0-based column index: 0=column A, 1=B, etc
if (cell != null && cell.StringCellValue == "HideMe")
{
row.Hidden = true;
}
}
}
private static void DeleteRows(ISheet sheet)
{
// When deleting we must iterate rows in reverse sequence
for (int rowIndex = sheet.LastRowNum; rowIndex >= 0; rowIndex--)
{
IRow row = sheet.GetRow(rowIndex);
if (row == null) continue;
ICell cell = row.GetCell(3);
if (cell != null && cell.StringCellValue == "DeleteMe")
{
// use ShiftRows to actually delete the row:
sheet.ShiftRows(row.RowNum+1, sheet.LastRowNum, -1);
}
}
}
private static void SaveWorkBook(IWorkbook workbook, string workBookName)
{
string newFileName = Path.ChangeExtension(workBookName, "new.xlsx");
using (FileStream file = new FileStream(newFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(file);
}
string backupFileName = Path.ChangeExtension(workBookName, "bak.xlsx");
File.Replace(newFileName, workBookName, backupFileName);
}
}
推荐阅读
- bash - Unix bash:在exel文件中获取正确的行数
- xml - PowerShell XML 函数问题
- gcc - Bitbake 尝试从不可用的 URL 获取源,但它们失败了
- c++ - 在 C++ 中创建指向 HWND 指针的指针以创建子 webview
- visual-studio-code - 如何在 Julia 中开发包时调试单元测试
- google-apps-script - Google Apps 脚本 (GAS) 是否可以访问 Gmail 电子邮件模板?
- excel - Selenium 错误:错误:0_SeleniumError 元素不可交互
- java - Flutter platfrom 频道在调用 aar 意图时给出 IllegalStateException
- python - 为什么不在 f 字符串前面添加反斜杠使其成为转义序列
- java - 在 Spring Kafka 中使用另一个消费者的值触发一个 Kafka 消费者