Unable to save to same excel sheet incrementing rows C#

Question

0.00/5 (No votes)

See more:

Hi,

I want to create an excel sheet report wherein my 1st row will remain same.1st time when I create the excel it works fine.I used save as method.Its saved in shared format.The data should be added each time to incremented rows.

I used a static variable to increment row number.Where file is newly created I set this variable to 2 and next time it checks if file exists and increments.But the incrementing and data addition is not happening.Please help.

What I have tried:

My code:

C#

public static int count;

private void button2_Click(object sender, EventArgs e)
{
//created Excel application workbook and sheet

if(File.Exists(@"C:\Repot.xls"))
{
 workbook = xlApp.Workbooks.Open(@"C:\Repot.xls");
count+=1;
sheet.Cells[count,"A"] = value1;
sheet.Cells[count,"B"] = value2;
sheet.Cells[count,"C"] = value3;
sheet.Cells[count,"D"] = value4;
sheet.Cells[count,"E"] = value5;
sheet.Cells[count,"F"] = value6;
.
.
.
//I have some 21 columns to fill
.
.
xlApp.DisplayAlerts = false;
workbook.SaveAs(@"C:\Repot.xls",Excel.XlFileFormat.xlWorkbookNormal,misValue,misValue,misValue,misValue,Excel.XlSaveAsAccessMode.xlShared,misValue,misValue,misValue,misValue,misValue);
}
else
{
sheet.Name = "X";
sheet.Cells[1,"A"] = "Date";
sheet.Cells[1,"B"] = "Time";
sheet.Cells[1,"C"] = "Y1";
sheet.Cells[1,"D"] = "Y2";
sheet.Cells[1,"E"] = "Y3";
sheet.Cells[1,"F"] = "Y4";
.
.
.



sheet.Cells[2,"A"] = value1;
sheet.Cells[2,"B"] = value2;
sheet.Cells[2,"C"] = value3;
sheet.Cells[2,"D"] = value4;
sheet.Cells[2,"E"] = value5;
sheet.Cells[2,"F"] = value6;
.
.
.
workbook.SaveAs(@"C:\Repot.xls",Excel.XlFileFormat.xlWorkbookNormal,misValue,misValue,misValue,misValue,Excel.XlSaveAsAccessMode.xlShared,misValue,misValue,misValue,misValue,misValue);

count=2;
}
workbook.Close(true);
xlApp.Quit();

Posted 1-May-18 19:38pm

Member 13688117

Updated 1-May-18 20:13pm

Maciej Los

v2

Add a Solution

2 solutions

Solution 2

A count variable stores some value as long as programme is executing...

So, if you would like to read/write its value, you can use:
1. Range in different worksheet

C#

//before you save document
workbook.Worksheets[2].Range["A1"] = count;

//after opening
count = workbook.Worksheets[2].Range["A1"];

or
2. CustomDocumentProperty See: How to: Create and Modify Custom Document Properties[^]

[EDIT]
3. Recommended method:
You may use Range.End[^] or Range.SpecialCells method (Microsoft.Office.Interop.Excel)[^] to detect last row or column.

C#

Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = sheet.get_Range("A1", last);

int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;

I would avoid of using UsedRange. I explained it in comment to my answer.

Posted 1-May-18 20:14pm

Maciej Los

Updated 1-May-18 21:20pm

v4

Comments

Patrice T 2-May-18 2:26am

I think the OP only need to retrieve the number of used rows, which is known by excel as part of UsedRange.

Maciej Los 2-May-18 3:07am

UsedRange may return wrong number of rows. Imagine, you can have 2 objects (tables) near each other, where first one can have 10 rows and second 300. You want to add data to first object. I think, you know now, what value return UsedRange.Rows.Count...
Cheers,
Maciej

Patrice T 2-May-18 3:16am

I agree with you, but here, it is a single table.

Maciej Los 2-May-18 3:17am

Even single table may cause several problems. Believe me, i have a lot of experience with VBA.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Accepted Answer · 2018-05-01T20:13:00

Quote:
I used a static variable to increment row number.Where file is newly created I set this variable to 2 and next time it checks if file exists and increments.But the incrementing and data addition is not happening.Please help.

Your logic is wrong.
When you open an existing excel workbook, you need to get the number of used rows in the worksheet and then add 1.

C#

workbook = xlApp.Workbooks.Open(@"C:\Repot.xls");
sheet= // you need to define sheet here
count=sheet.usedRange.Rows+1; // you need to get the number of rows here
sheet.Cells[count,"A"] = value1;