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

Question

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

Ganesan C · 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; }

Ganesan C · Answer

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 { 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 Raman · Answer

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 Raman · Answer

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 C · Answer

LastRowTotal(xlWorksheet); // Pass your sheet which are used in old code.(Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];) For DOB , xlWorksheet.Cells[i, 5] = "Your random mm/dd values" + "/" + xlWorksheet.Cells[i, 5];

Sujeet Raman · Answer

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 C · Answer

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

Sujeet Raman · Answer

what i meant is when i given path it should automatically calculate the rowcount? int rowCount value dynamicaly calculate

Ganesan C · Answer

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 Raman · Answer

how to take total row count dynamically here??

Ganesan C · Answer

Then, your for loop will start from 2, for ( int i = 2; 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 Raman · Answer

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

Sujeet Raman · Answer

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 Ayaka · Answer

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 Lodha · Answer

You can do it using VBA.