如何在asp.net中从用户给出的excel表中找到空行并将其删除

Question

public async Task<List<IndiaCIT>> Import(IFormFile file)
{
var list = new List<IndiaCIT>();
using (var stream = new MemoryStream())
{
await file.CopyToAsync(stream);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package=new ExcelPackage(stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
var rowcount = worksheet.Dimension.Rows;


for (int row = 1; row <= rowcount; row++)
{

list.Add(new IndiaCIT {
NameCH = worksheet.Cells[row, 1].Value.ToString().Trim(),
City= worksheet.Cells[row, 2].Value.ToString().Trim(),
Age = worksheet.Cells[row, 3].Value.ToString().Trim(),

});

}
}
}
return list;
}

这是控制器代码，在模型类中声明了列名，并将其用作控制器中的IndiaCIT列表，。我希望空行被删除

Answer 1

这可能会有所帮助：对于工作表中的每一行，检查每列中的单元格是否为空。如果是，请删除它。

var rowcount = worksheet.Dimension.Rows;
var maxColums = worksheet.Dimension.Columns;
for(int row = rowcount; row > 0; row--)
{
bool isRowEmpty = true;
for(int column = 1; column <= maxColumns; column++)
{
var cellEntry = worksheet.Cells[row, column].Value.ToString();
if(!string.IsNullOrEmpty(cellEntry)
{
isRowEmpty = false;
break;
}
}
if(!isRowEmpty)
continue;
else
worksheet.DeleteRow(row);
}

Answer 2

我通常使用Spire.Xls来处理此类问题。它对我有用。

public IActionResult Test() {
//init workbook
Workbook workbook = new Workbook();
// load file
workbook.LoadFromFile("11.xlsx");
// get the first sheet
Worksheet sheet = workbook.Worksheets[0];
//delete blank row 
for (int i = sheet.Rows.Count() - 1; i >= 0; i--)
{
if (sheet.Rows[i].IsBlank)
{
sheet.DeleteRow(i + 1); 
}
}
//delete blank column
for (int j = sheet.Columns.Count() - 1; j >= 0; j--)
{
if (sheet.Columns[j].IsBlank)
{
sheet.DeleteColumn(j + 1); 
}
}
//save as a new xls file
workbook.SaveToFile("new.xlsx", ExcelVersion.Version2016);
return Ok();
}