How to update specific columns in an excel file and get new one?

Sujeet Raman

Hi,

i have an excel file with around 1000(may be more in future) rows and 5 cololums like

Age, Name,Address,ID,School i have 1000 data in excel sheet

what i have to do this I need to update Name, ID,School (only 3) coloumns with new random data or new values.I dont have that new values stored anywer.How we will automate this process in c#.Please help me

15 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ganesan C5y ago
Accepted answer
Hi, Try this to get last filled row.
static int LastRowTotal(Excel.Worksheet sheet)
{
   Excel.Range lastCell = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
   return lastCell.Row;
}
Sujeet Raman5y ago
How to copy, Edit ,save an excel as a new file in a specific Location in C# using excel interop? i have method which will edit the existing same excel.But i want to copy the original excel file and edit 2 colums and save that as a diffrent location with any name.

so i should have the original one and new one with edited.Please suggest an efficient way
Sujeet Raman5y ago
this methode not taking

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // excel sheet
Excel.Range xlRange = xlWorksheet.UsedRange;
LastRowTotal(xlWorksheet);//not taking
Ganesan C5y ago
1. LastRowTotal(xlWorksheet); // Pass your sheet which are used in old code.(Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];)
3. For DOB ,
4. xlWorksheet.Cells[i, 5] = "Your random mm/dd values" + "/" + xlWorksheet.Cells[i, 5];

Sujeet Raman5y ago

thanks i couldnt find any solution if i want to attach some text or number to cell value what i will do?

suppose i have DOB column. value is only 1989. dob colomn is in 5th column. i need to attach random mm/dd in to that year. I will generate random mm/dd but how can i attach?

what i have to pass LastRowTotal("what i will pass")

static int LastRowTotal(Excel.Worksheet sheet)
{
Excel.Range lastCell = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
return lastCell.Row;
}

Ganesan C5y ago

It means already some of the rows are filled with data and you want to count those rows right?

Sujeet Raman5y ago

what i meant is when i given path it should automatically calculate the rowcount?

int rowCount value dynamicaly calculate

Ganesan C5y ago

Hi, your row count also dynamic? then pass rowcount as parameter to this function and use it.

private void WriteExcel(string fileName, int rowCount)
{
}
// Call like WriteExcel("filepath", 1000);

Sujeet Raman5y ago

how to take total row count dynamically here??

Ganesan C5y ago

Then, your for loop will start from 2,

for (int i = 2; i <= totalRows; i++)
{
xlWorksheet.Cells[i, 2] = RandomString(5); // update 2nd Column
xlWorksheet.Cells[i, 4] = RandomString(5); // update 4th Column
xlWorksheet.Cells[i, 5] = RandomString(5); // update 5th Column
}

Sujeet Raman5y ago

How to skip coloumn headers here? My coloumn headers also eduted

Ganesan C5y ago

Hi,

Try below code.

using System;
using System.Linq;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsFormsApp11
{
public class ExcelOperation
{
private void WriteExcel(string fileName)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; //Here your excel sheet. I am using sheet 1
Excel.Range xlRange = xlWorksheet.UsedRange;
int totalRows = 1000;
for (int i = 1; i <= totalRows; i++)
{
xlWorksheet.Cells[i, 2] = RandomString(5); // update 2nd Column
xlWorksheet.Cells[i, 4] = RandomString(5); // update 4th Column
xlWorksheet.Cells[i, 5] = RandomString(5); // update 5th Column
}
xlApp.Visible = false;
xlApp.UserControl = false;
xlWorkbook.Save();
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
// Get random string which length is 5. You can modify the length of the string.
public static string RandomString(int length)
{
Random random = new Random();
const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
return new string(Enumerable.Repeat(chars, length)
.Select(s => s[random.Next(s.Length)]).ToArray());
}
}
}

Add below reference to your project before going to code (The reference will display only if you have installed excel on your machine).

Sujeet Raman5y ago

I have converted an excel to csv using this.But how can update?you have any links for me for the same task? i am completly new to this.

Uehara Ayaka5y ago

Hi, you can use Spire.XLS for .NET to access a specified worksheet in an Excel document, loop through the cells within a specific column, and then apply a random value to a certain cell.

Shweta Lodha5y ago

You can do it using VBA.