这个命令需要至少两行的源数据的 [英] This command requires at least two rows of source data
问题描述
我收到此错误：
此命令至少需要两行的源数据。仅在一个行上的选择，不能使用该命令。请尝试以下操作：
- 如果您使用的是先进的过滤器，选择单元格区域至少包含两行数据。然后再次单击高级筛选命令。
- 我要创建一个数据透视表，键入单元格引用或选择的范围包括至少两行数据
间歇性地在这条线的code：
xlWorkBook.RefreshAll（）;
有两个工作表。其中有一个数据透视表和一个拥有原始数据。有时只存在一个行的数据。对于多行数据code以上行的总是工作;然而，只有1行数据，code以上的有时的作品，有时我得到上述错误信息。
除此之外，包含数据透视表的工作表的不可以刷新;不过，如果我重新打开该文件，它还不刷新，除非我明确地手动刷新。
这是怎么回事呢？为什么我只收到此错误的有时候？的
感谢你这么多的指点。
如果在所有有帮助的，我在内的整个方式：
私人无效SortandCreateFile（字符串列，字符串email，字符串emailStartPos）{
字符串replacetext =;
尝试 {
变种valueRange = xlWorkSheet.get_Range（柱+ emailStartPos，柱+ range.Rows.Count.ToString（））;
VAR deleteRange = valueRange;
xlApp.Visible = FALSE;
INT startpos = 0;
INT endPos = 0;
布尔foundStart = FALSE;
Excel.Range RNG = xlWorkSheet.get_Range（柱+1，列+ range.Rows.Count.ToString（））;
字符串tempstring =D;
INT INTemailStartPos = Convert.ToInt16（emailStartPos）;
对于（INT RCNT = INTemailStartPos; RCNT＆LT; = rng.Count; RCNT ++）{
Excel.Range细胞=（Excel.Range）RNG [RCNT，1];
尝试 {
如果（cell.Value2！= NULL）
tempstring = cell.Value2.ToString（）;
其他 {
startpos = RCNT;
releaseObject（细胞）; /////////
打破;
}
}
赶上（例外EE）
{
的MessageBox.show（ee.ToString（））;
}
//抓住从列链接texdtbox文
Excel.Range rngLinkColumn;
Excel.Range replacetextcell = NULL;
如果（FormControls.ColumnLink.Length大于0）{
rngLinkColumn = xlWorkSheet.get_Range（FormControls.ColumnLink +1，FormControls.ColumnLink + range.Rows.Count.ToString（））;
replacetextcell =（Excel.Range）rngLinkColumn [RCNT，1];
}
//查找电子邮件
如果（cell.Value2.ToString（）==电子邮件）{
//我们发现我们想要的电子邮件的起始位置！
//这将告诉我们的数据，从开始的这行
startpos = RCNT;
如果（FormControls.ColumnLink.Length大于0）
replacetext = replacetextcell.Value2.ToString（）;
releaseObject（细胞）; /////////
打破;
}
releaseObject（细胞）;
}
INT foundstartminusONE = startpos - 1;
INT rngcount = rng.Count + INTemailStartPos;
//删除一切从顶部，直到我们所需要的电子邮件地址的行
如果（startpos！= INTemailStartPos）{
deleteRange = xlWorkSheet.get_Range（柱+ INTemailStartPos.ToString（）+：+CF+ foundstartminusONE.ToString（），Type.Missing）;
deleteRange = deleteRange.EntireRow;
deleteRange.Delete（Excel.XlDeleteShiftDirection.xlShiftUp）;
}
对于（INT RCNT = INTemailStartPos; RCNT＆LT; = rng.Count; RCNT ++）{
Excel.Range细胞=（Excel.Range）RNG [RCNT，1];
尝试 {
如果（cell.Value2！= NULL）
tempstring = cell.Value2.ToString（）;
其他 {
endPos = RCNT - 1;
releaseObject（细胞）; ////////
打破;
}
}
赶上（例外EE）{
//MessageBox.Show（ee.ToString（））;
}
//查找电子邮件
如果（cell.Value2.ToString（）！=电子邮件）{
//我们发现其中最后一个电子邮件地址，是我们需要
//这是那里的问题发生，我认为与删除最后一行
endPos = RCNT;
releaseObject（细胞）; ////////
打破;
}
releaseObject（细胞）;
}
//删除所有的东西，我们需要的电子邮件地址后，
如果（endPos！= 0）{
deleteRange = xlWorkSheet.get_Range（柱+ endPos +：+CF+ rngcount.ToString（），Type.Missing）;
deleteRange = deleteRange.EntireRow;
deleteRange.Delete（Excel.XlDeleteShiftDirection.xlShiftUp）;
}
//当用户打开Excel文件，我们要重点在这里
VAR rangehome = xlWorkSheet.get_Range（FormControls.FocusOn，FormControls.FocusOn）;
xlWorkSheet.Activate（）;
rangehome.Select（）;
字符串文件名= xlWorkBook.Path + @\+电子邮件+的.xlsx;
串fileSubstring = filename.Substring（0，filename.IndexOf（）XLSX。）;
。串randomfileString = Guid.NewGuid（）的ToString（N）子串（0，10）+的.xlsx。
字符串targetfilenameRename = fileSubstring + randomfileString;
//（（Excel.Worksheet）this.Application.ActiveWorkbook.Sheets [FormControls.WorksheetFocus]）启动（）。
//（（Excel.Worksheet）Excel.Application.ActiveWorkbook.Sheets [1]）激活（）。
Excel.Worksheet xlWorkSheetFocus =（Excel.Worksheet）xlWorkBook.Worksheets.get_Item（FormControls.WorksheetFocus）;
xlWorkSheetFocus.Activate（）;
xlWorkBook.SaveAs（targetfilenameRename，Excel.XlFileFormat.xlWorkbookDefault，Type.Missing，Type.Missing，
假的，假的，Excel.XlSaveAsAccessMode.xlNoChange，
Type.Missing，Type.Missing，Excel.XlSaveConflictResolution.xlLocalSessionChanges，Type.Missing，Type.Missing）;
尝试 {
xlWorkBook.RefreshAll（）;
}
抓住 { }
xlWorkBook.Save（）;
字符串的TargetFile = xlWorkBook.Path + @\+ FormControls.FileName + -
+ email.Substring（0，email.IndexOf（@））+的.xlsx;
System.IO.File.Copy（targetfilenameRename，的TargetFile，真正的）;
绳体= FormControls.eMailBody;
体= body.Replace（％replacetext％，replacetext）;
//在体内代替％replacetext％
串targetfileSubstring = targetfile.Substring（0，targetfile.IndexOf（）XLSX。）;
。串randomString = Guid.NewGuid（）的ToString（N）子串（0，10）+XLSX。;
字符串targetfileRename = targetfileSubstring + randomString;
而（真）{
尝试 {
SendEmail（的TargetFile，电子邮件，FormControls.eMailSubject，身体，FormControls.eMailFrom）;
}
赶上（例外EE）{
的MessageBox.show（ee.ToString（））;
继续;
}
// 一切都很好
打破;
}
releaseObject（valueRange）;
releaseObject（deleteRange）;
File.Copy（的TargetFile，targetfileRename，真正的）;
}
赶上（例外五）{
的MessageBox.show（e.ToString（））;
}
最后 {
// DisposeMe（）;
//释放所有的COM的RCW。
//将releaseObject只是做什么，如果传递null，
//所以不需要检查，以找出哪些需要被释放。
//将最终运行在所有的情况下，即使有例外
//在尝试。
//注：通过由裁判，因此后记xlWorkSheet将
//计算为NULL。请参阅releaseObject。
releaseObject（范围）;
releaseObject（xlWorkSheet）;
releaseObject（xlWorkBook）;
//将退出在做最后因为我们总是
//想戒烟。它比释放的RCW没有什么不同。
如果（xlApp！= NULL）{
xlApp.Quit（）;
}
releaseObject（xlApp）;
}
}
我可以复制这个错误与数据透视表的唯一方法是通过尝试创建一个过一个没有列标题的范围，就像在截图来自Stephan1010的答案。
在 GETPIVOTDATA 的Excel函数，数据透视字段的名称提及到（ = GETPIVOTDATA（EmailAddress的$ A $ 3）） ;因此，它是有道理的，不允许的数据源，将不会有他们。
解决的办法是将转动过的的ListObject 代替范围 - 在Excel中，当您选择，说，范围 $ A $ 1：$ C $ 1 和格式表的（从功能区），该表的结果将跨越 $ A $ 1：$ C $ 2 ;第一行的内容变为列标题，第二行是有效的，空的记录。有趣的是，发生这种情况（2排跨度），无论你是否不勾选我的表有标题复选框（该数据将被移动到第一行，该表将包含默认列1的 - 列2 - 如果清除该复选框Column3头文件）
在换句话说，的ListObject 是总是的一个有效的数据源的数据透视表，而范围可能没有包含足够的行。此外，如果你没有列标题，并创建与范围内的数据透视表 $ A $ 1：$ C $ 2 ，在记录 $ A $ 1 ：$ C $ 1 将被用作列标题，这意味着第一个记录丢失
从code你提供我将presume数据透视表已经是present并连接到某些[命名？]范围内的模板工作簿包含宏。打开你的范围到表可能是微不足道的，从功能区中选择的格式表的。然后，你可以有code这样的删除所有不必要的行，同时仍保持一个有效的数据源的数据透视表：
公共无效DeleteExtraTableRows（字符串EMAILADDRESS，Excel.ListObject表）
{
尝试
{
变种rowIndex位置= 0;
VAR wasDeleted = FALSE;
而（rowIndex位置＆LT; = table.ListRows.Count）
{
如果rowIndex位置++（wasDeleted！）;
VAR行= table.ListRows [rowIndex位置]
变种范围=（Excel.Range）row.Range.Cells [1，1];
VAR值= range.Value2;
如果（值= NULL和放大器;！＆安培;！string.Equals（EMAILADDRESS，value.ToString（）））
{
row.Delete（）;
wasDeleted = TRUE;
}
}
}
赶上（例外五）
{
的MessageBox.show（e.Message +\ñ\ N+ e.StackTrace）;
}
}
还有一种可能性，即电子邮件是从来没有在发现环路的如果（cell.Value2.ToString（）==电子邮件）的条件，这将最终删除从范围中的所有行 - 即使唯一的区别是在同单元格值的最后一个额外的空间。有了上面code，即使所有电子邮件地址被删除的数据源仍然是一个有效的用于数据透视表将连接到它。
编辑：
在Excel中打开一个范围到的ListObject 通过选择有问题的范围和点击的格式如表的功能区按钮，从首页的标签。另外，您可以创建一个像这样的：
VAR范围=（（Excel.Range）（worksheet.Range [worksheet.Cells [1,1]，worksheet.Cells [3,1]））;
无功表= worksheet.ListObjects.Add（SourceType中：Excel.XlListObjectSourceType.xlSrcRange，来源：范围，
XlListObjectHasHeaders：Excel.XlYesNoGuess.xlYes）;
table.TableStyle =TableStyleMedium3;
在code，可以使用 ListObjects 属性来访问所有的 ListObjects 工作表：
VAR工作表=（Excel.Worksheet）Globals.ThisAddIn.Application.ActiveSheet;
无功表= worksheet.ListObjects;
然后，您可以访问特定的ListObject /表几种不同的方式：
VAR为myTable =表[1];
VAR为myTable = tables.Item [表1];
VAR为myTable = tables.OfType＆LT; Excel.ListObject＆GT;（）FirstOrDefault（T =＆GT; t.Name ==表1）;
由于行从表中添加的，实际的范围是指将相应扩大;使用 myTable.Range 访问范围的问题。
I am getting this error:
This command requires at least two rows of source data. You cannot use the command on a selection in only one row. Try the following:
- If you're using an advanced filter, select a range of cells that contains at least two rows of data. Then click the Advanced Filter command again.
- I you're creating a PivotTable, type a cell reference or select a range that includes at least two rows of data
intermittently on this line of code:
xlWorkBook.RefreshAll();
There are two worksheets. One has a pivot table and one has raw data. Sometimes there is only one row of data. For multiple rows of data the line of code above always works; however, for only 1 row of data, the code above sometimes works, and sometimes I get the error message above.
In addition to this, the worksheet containing the pivot table is not refreshed; however, if I re-open the file, it also does not refresh, unless I explicitly refresh it manually.
What is going on here? Why am I getting this error only sometimes?
Thank you so much for your guidance.
if at all helpful, i am including the entire method:
private void SortandCreateFile(string column, string email, string emailStartPos) {
string replacetext = "";
try {
var valueRange = xlWorkSheet.get_Range(column + emailStartPos, column + range.Rows.Count.ToString());
var deleteRange = valueRange;
xlApp.Visible = false;
int startpos = 0;
int endPos=0;
bool foundStart = false;
Excel.Range rng = xlWorkSheet.get_Range(column + "1", column + range.Rows.Count.ToString());
string tempstring = "d";
int INTemailStartPos = Convert.ToInt16(emailStartPos);
for (int rCnt = INTemailStartPos; rCnt <= rng.Count; rCnt++) {
Excel.Range cell = (Excel.Range)rng[rCnt, 1];
try {
if (cell.Value2 != null)
tempstring = cell.Value2.ToString();
else {
startpos = rCnt;
releaseObject(cell); /////////
break;
}
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
//grab the text from column link texdtbox
Excel.Range rngLinkColumn;
Excel.Range replacetextcell=null;
if (FormControls.ColumnLink.Length > 0) {
rngLinkColumn = xlWorkSheet.get_Range(FormControls.ColumnLink + "1", FormControls.ColumnLink + range.Rows.Count.ToString());
replacetextcell = (Excel.Range)rngLinkColumn[rCnt, 1];
}
//locate email
if (cell.Value2.ToString() == email ) {
//we found the starting position of the email we want!
//this will tell us which row of data to start from
startpos = rCnt;
if (FormControls.ColumnLink.Length > 0)
replacetext = replacetextcell.Value2.ToString();
releaseObject(cell); /////////
break;
}
releaseObject(cell);
}
int foundstartminusONE = startpos - 1;
int rngcount = rng.Count + INTemailStartPos;
//delete everything from the top UNTIL the row of the email address that we need
if (startpos != INTemailStartPos) {
deleteRange = xlWorkSheet.get_Range(column + INTemailStartPos.ToString() + ":" + "CF" + foundstartminusONE.ToString(), Type.Missing);
deleteRange = deleteRange.EntireRow;
deleteRange.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
for (int rCnt = INTemailStartPos; rCnt <= rng.Count; rCnt++) {
Excel.Range cell = (Excel.Range)rng[rCnt, 1];
try {
if (cell.Value2 != null )
tempstring = cell.Value2.ToString();
else {
endPos = rCnt - 1;
releaseObject(cell);////////
break;
}
}
catch (Exception ee) {
//MessageBox.Show(ee.ToString());
}
//locate email
if (cell.Value2.ToString() != email ) {
//we found where the last email address is that we need
//this is where the issue is occurring i think with the deleting the last row
endPos = rCnt;
releaseObject(cell);////////
break;
}
releaseObject(cell);
}
//delete all the stuff AFTER the email address that we need
if (endPos != 0) {
deleteRange = xlWorkSheet.get_Range(column + endPos + ":" + "CF" + rngcount.ToString(), Type.Missing);
deleteRange = deleteRange.EntireRow;
deleteRange.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
//when the user opens the excel file, we want the focus to be here
var rangehome = xlWorkSheet.get_Range(FormControls.FocusOn, FormControls.FocusOn);
xlWorkSheet.Activate();
rangehome.Select();
string filename = xlWorkBook.Path + @"\" + email + ".xlsx";
string fileSubstring = filename.Substring(0, filename.IndexOf(".xlsx"));
string randomfileString = Guid.NewGuid().ToString("N").Substring(0, 10) + ".xlsx";
string targetfilenameRename = fileSubstring + randomfileString;
//((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[FormControls.WorksheetFocus]).Activate();
//((Excel.Worksheet)Excel.Application.ActiveWorkbook.Sheets[1]).Activate();
Excel.Worksheet xlWorkSheetFocus = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(FormControls.WorksheetFocus);
xlWorkSheetFocus.Activate();
xlWorkBook.SaveAs(targetfilenameRename, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
try {
xlWorkBook.RefreshAll();
}
catch { }
xlWorkBook.Save();
string targetfile = xlWorkBook.Path + @"\" + FormControls.FileName + " - "
+ email.Substring(0, email.IndexOf("@")) + ".xlsx";
System.IO.File.Copy(targetfilenameRename, targetfile, true);
string body = FormControls.eMailBody;
body = body.Replace("%replacetext%", replacetext);
//replace %replacetext% in body
string targetfileSubstring = targetfile.Substring(0, targetfile.IndexOf(".xlsx"));
string randomString = Guid.NewGuid().ToString("N").Substring(0, 10)+".xlsx";
string targetfileRename = targetfileSubstring+randomString;
while (true) {
try {
SendEmail(targetfile, email, FormControls.eMailSubject, body,FormControls.eMailFrom);
}
catch (Exception ee) {
MessageBox.Show(ee.ToString());
continue;
}
// all is good
break;
}
releaseObject(valueRange);
releaseObject(deleteRange);
File.Copy(targetfile, targetfileRename, true);
}
catch (Exception e) {
MessageBox.Show(e.ToString());
}
finally {
//DisposeMe();
// Release all COM RCWs.
// The "releaseObject" will just "do nothing" if null is passed,
// so no need to check to find out which need to be released.
// The "finally" is run in all cases, even if there was an exception
// in the "try".
// Note: passing "by ref" so afterwords "xlWorkSheet" will
// evaluate to null. See "releaseObject".
releaseObject(range);
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
// The Quit is done in the finally because we always
// want to quit. It is no different than releasing RCWs.
if (xlApp != null) {
xlApp.Quit();
}
releaseObject(xlApp);
}
}
The only way I could replicate this error with a pivot table was by attempting to create one off a range that didn't have column headers, just like on the screenshot from Stephan1010's answer.
In the GetPivotData Excel function, pivot fields are referred to by their names (=GETPIVOTDATA("EmailAddress",$A$3)); thus, it makes sense to disallow a data source that wouldn't have them.
The solution would be to pivot over a ListObject instead of a Range - in Excel when you select, say, range $A$1:$C$1 and format as table (from the Ribbon), the table that results will span $A$1:$C$2; the contents of the first row becomes the column headers and the second row is a valid, empty record. Interesting to note that this happens (the 2-row span) regardless of whether or not you check the "My table has headers" checkbox (the data will be moved to the first row and the table will contain default "Column1"-"Column2"-"Column3" headers if the checkbox is cleared).
In other words, a ListObject is always a valid data source for a pivot table, while a Range may not contain enough rows. Also if you don't have column headers and you create a pivot table with range $A$1:$C$2, the record at $A$1:$C$1 will be used as column headers, which means that first record is lost.
From the code you have supplied I would presume the pivot table is already present and connected to some [named?] range in a template workbook that contains the macro. Turning your range into a table might be as trivial as selecting format as table from the Ribbon. And then you could have code like this to remove all unnecessary rows while still keeping a valid data source for the pivot table:
public void DeleteExtraTableRows(string emailAddress, Excel.ListObject table)
{
try
{
var rowIndex = 0;
var wasDeleted = false;
while (rowIndex <= table.ListRows.Count)
{
if (!wasDeleted) rowIndex++;
var row = table.ListRows[rowIndex];
var range = (Excel.Range)row.Range.Cells[1, 1];
var value = range.Value2;
if (value != null && !string.Equals(emailAddress, value.ToString()))
{
row.Delete();
wasDeleted = true;
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message + "\n\n" + e.StackTrace);
}
}
There is also a possibility that the email is never found in the loop's if (cell.Value2.ToString() == email ) condition, which would end up deleting all rows from your range - even if the only difference is an extra space at the end of the in-cell value. With the above code, even if all email addresses get deleted the data source remains a valid one for a pivot table that would be connected to it.
EDIT:
In Excel you turn a Range into a ListObject by selecting the range in question and clicking the Format as table Ribbon button, from the Home tab. Alternatively you can create one like this:
var range = ((Excel.Range)(worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[3, 1]]));
var table = worksheet.ListObjects.Add(SourceType: Excel.XlListObjectSourceType.xlSrcRange, Source: range,
XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);
table.TableStyle = "TableStyleMedium3";
In code, you can access all ListObjects on a worksheet using the ListObjects property:
var worksheet = (Excel.Worksheet) Globals.ThisAddIn.Application.ActiveSheet;
var tables = worksheet.ListObjects;
Then, you can access a specific ListObject /table with several different ways:
var myTable = tables[1];
var myTable = tables.Item["Table1"];
var myTable = tables.OfType<Excel.ListObject>().FirstOrDefault(t => t.Name == "Table1");
As rows are added from the table, the actual range it refers to will be expanded accordingly; use myTable.Range to access the range in question.
这篇关于这个命令需要至少两行的源数据的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
