Scenario:
image.png (1.3 KB)
There are two excels i.e., excel1 and excel 2, it has different columns in each excel. I want to replace column header name so that I compare data.
For eg: if header name in sheet 1 is name then replace it with name (in excel 2 ) & if column header is Ext replace it with Extention and so on. Please suggest to write the code in csharp
// Adding the code here
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Spire.Xls;
namespace ExcelSheetReader
{
class Program1
{
static void Main(string[] args)
{
// Provide the path to your Excel file
string filePath = @“C:\Users\tom\Documents\sample.xlsx”;
try
{
// Open the Excel file using a FileStream
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
// Create an instance of the Excel workbook
IWorkbook workbook = new XSSFWorkbook(fs); // For .xlsx files
// Get the first sheet from the workbook
ISheet sheet = workbook.GetSheetAt(0); // You can specify the sheet index or name
// Iterate through the rows and cells to read the data
for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
{
IRow row = sheet.GetRow(rowIndex);
if (row != null)
{
for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++)
{
ICell cell = row.GetCell(cellIndex);
if (cell != null)
{
// Get the cell value as a string
string cellValue = cell.ToString();
// Do something with the cell value (e.g., print it)
Console.Write(cellValue + "\t");
}
}
Console.WriteLine(); // Move to the next row
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
// Get Excel Column Name
private string GetExcelColumnName(int columnNumber)
{
int dividend = columnNumber;
string columnName = String.Empty;
int modulo;
while (dividend > 0)
{
modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend - modulo) / 26);
}
return columnName;
}