无法保存到相同的Excel工作表递增行C＃ [英] Unable to save to same excel sheet incrementing rows C#

问题描述

我想创建一个excel表报告，其中我的第一行将保持不变。第一次创建excel时，它运行正常。我使用过保存为方法。它以共享格式保存。每次都要添加数据到增量行。



我使用静态变量来增加行号。文件是新创建我将此变量设置为2，下次检查文件是否存在并递增。但是没有发生递增和数据添加。请帮助。



我尝试过：



我的代码：

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();

推荐答案

引用：

我使用静态变量来增加行号。新创建的文件我将此变量设置为2，下次检查文件是否存在并递增。但是没有发生递增和数据添加。请帮助。

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.

您的逻辑错误。

当您打开现有的Excel工作簿时，您需要获取工作表中使用的行数，然后添加1 。

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;

一个 count 变量只要程序正在执行就存储一些值...



所以，如果你想读/写它的值，你可以使用：

1.不同工作表中的范围

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"];

或

2. CustomDocumentProperty 请参阅：如何：创建和修改自定义文档属性 [ ^ ]





3。推荐方法：

您可以使用 Range.End [ ^ ]或 Range.SpecialCells方法（Microsoft.Office.Interop.Excel） [ ^ ]检测最后一行或一列。

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


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;

我会避免使用UsedRange。我在评论中解释了这一点。

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

这篇关于无法保存到相同的Excel工作表递增行C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！