Click here to Skip to main content
14,606,277 members
Rate this:
Please Sign up or sign in to vote.
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:

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
Updated 1-May-18 20:13pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
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;
   
v3
Comments
Member 13688117 2-May-18 2:28am
   
Thank you so much for the logic.
Patrice T 2-May-18 2:38am
   
You are welcome.
Member 13688117 2-May-18 2:29am
   
It worked fine.
Rate this:
Please Sign up or sign in to vote.

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
//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.

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.
   
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.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100