CODEWITHSUNDEEP
c#exceldatagridviewinterop
Eddysanoli
Eddysanoli

Reputation: 793

Unable to Save Changes to Excel File (C# /)

I've been trying to modify an Excel file called "Hoja de Resultados" adding three different variables each time (Puntuacion = Score, Fecha = Date, Hora = Time) inside of Visual Studio (C#). The excel sheet is linked to a DataGridView window which updates everytime the values are changed. The problem is, when there are less than 3 rows on the excel sheet, the program stops modifying the XLSX and sometimes it throws a seemingly random exception on "Program.cs".

string Direccion = "G:\\Archivos\\Programación\\Visual Basic\\UVG\\Microcontroladores - Proyecto Final Comunicación Serial\\Hoja de Resultados.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook Hoja = ExcelApp.Workbooks.Open(Direccion);
Microsoft.Office.Interop.Excel.Worksheet HojaActiva = ExcelApp.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

Excel.Range Rango = HojaActiva.UsedRange;
int Posicion = Rango.Rows.Count;
int NuevaPosicion = Posicion + 1;
HojaActiva.Cells[NuevaPosicion, 1] = Puntuacion;
HojaActiva.Cells[NuevaPosicion, 2] = Fecha;
HojaActiva.Cells[NuevaPosicion, 3] = Hora;

Hoja.Saved = true;
ExcelApp.DisplayAlerts = false;
ExcelApp.ScreenUpdating = false;
ExcelApp.Visible = false;
ExcelApp.UserControl = false;
ExcelApp.Interactive = false;

Hoja.Close(true, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(Hoja);
Marshal.ReleaseComObject(HojaActiva);
ExcelApp.Quit();

Sorry if it's partially in Spanish. But for some guidance:

  • Hoja = Workbook
  • HojaActiva = ActiveWorkbook

Upvotes: 1

Views: 2072

Answers (1)

Nik
Nik

Reputation: 1870

The reason that your Excel file is not updating is because you're not saving it anywhere in code. To solve your saving problem,

Change this line:

Hoja.Saved = true;

To

Hoja.Save();

When you write Hoja.Saved = true;, it tells Excel that there are no changes to be saved, whether that is the case or not, and that it can safely exit. Thus it does not actually save anything. You have to call the Save() method explicitly.

Another possible issue in your code is if the active range does not begin at the very top. The line int NuevaPosicion = Posicion + 1; assumes that your input starts on row one, which may not be the case. I would recommend changing that line to this:

//This way it doesn't matter where the range starts.
// Rows.Row returns the row on which the active range begins.
int NuevaPosicion = Posicion + Rango.Rows.Row;

Now new data will always be appended to the end of the sheet.

Other than the two points above, I cannot replicate your problem with less than 3 rows on the excel sheet, the code you posted seems to run with no issues under all conditions I could think of. Try making the change above and see if that solves your other issues. If not, please update your question to describe the seemingly random exception on "Program.cs" a bit more explicitly.

Upvotes: 1

Related Questions