“名为'客户会议ID'的列已属于此数据表。” ASP .NET中的错误，即将数据下载到excel格式 [英] "A column named 'client meeting ID' already belongs to this datatable." error in ASP .NET whlie downloading data into excel format
本文介绍了“名为'客户会议ID'的列已属于此数据表。” ASP .NET中的错误，即将数据下载到excel格式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
"A column named 'Client Meeting ID' already belongs to this DataTable."}
当我尝试从以下位置下载数据时出现
错误数据库转换为Excel格式。
error when I am trying to Download Data from Database into Excel Format.
protected void btnmeeting_Click(object sender, EventArgs e)
{
ResultsData1.Clear();
SqlConnection con211 = new SqlConnection(connstring);
SqlCommand cmd21 = new SqlCommand("USP_Report", con211);
cmd21.CommandType = CommandType.StoredProcedure;
cmd21.Parameters.AddWithValue("@mode", 99);
cmd21.Parameters.AddWithValue("@userid", Session["UserId"].ToString());
SqlDataAdapter da = new SqlDataAdapter(cmd21);
DataTable dt = new DataTable();
dt.Clear();
da.Fill(dt);
DataTableReader reader = new DataTableReader(dt);
int c = 0;
bool firstTime = true;
DataTable dtSchema = new DataTable();
dtSchema.Clear();
dtSchema = reader.GetSchemaTable();
var listCols = new List<DataColumn>();
if (dtSchema != null)
{
try
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
var column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
ResultsData1.Columns.Add(column);
}
}
catch (Exception ex)
{
Response.Write(ex);
}
}
它在
It Giving Error at
<pre>ResultsData1.Columns.Add(column);
我尝试过：
我创建了两个全局变量
What I have tried:
I have Created Two Global Variables as
static private int rowsPerSheet = 200;
static private DataTable ResultsData1 = new DataTable();
现在点击按钮点击事件我有以下代码
Now on Button Click Event I have Following Code
protected void btnmeeting_Click(object sender, EventArgs e)
{
ResultsData1.Clear();
SqlConnection con211 = new SqlConnection(connstring);
SqlCommand cmd21 = new SqlCommand("USP_Report", con211);
cmd21.CommandType = CommandType.StoredProcedure;
cmd21.Parameters.AddWithValue("@mode", 99);
cmd21.Parameters.AddWithValue("@userid", Session["UserId"].ToString());
SqlDataAdapter da = new SqlDataAdapter(cmd21);
DataTable dt = new DataTable();
dt.Clear();
da.Fill(dt);
DataTableReader reader = new DataTableReader(dt);
int c = 0;
bool firstTime = true;
DataTable dtSchema = new DataTable();
dtSchema.Clear();
dtSchema = reader.GetSchemaTable();
var listCols = new List<DataColumn>();
if (dtSchema != null)
{
try
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
var column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
ResultsData1.Columns.Add(column);
}
}
catch (Exception ex)
{
Response.Write(ex);
}
}
// Call Read before accessing data.
while (reader.Read())
{
DataRow dataRow = ResultsData1.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[(listCols[i])] = reader[i];
}
ResultsData1.Rows.Add(dataRow);
c++;
if (c == rowsPerSheet)
{
c = 0;
ExportToOxml(firstTime);
ResultsData1.Clear();
firstTime = false;
}
}
if (ResultsData1.Rows.Count > 0)
{
ExportToOxml(firstTime);
ResultsData1.Clear();
}
// Call Close when done reading.
reader.Close();
}
我还创建了下载文件的功能
此功能下载数据200每张表格上的行
I have also Created function to download file as
This function download data 200 row on each sheet
private static void ExportToOxml(bool firstTime)
{
const string fileName = @"E:\MyExcel1.xlsx";
//Delete the file if it exists.
if (firstTime && File.Exists(fileName))
{
File.Delete(fileName);
}
uint sheetId = 1; //Start at the first sheet in the Excel workbook.
if (firstTime)
{
//This is the first time of creating the excel file and the first sheet.
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var bold1 = new Bold();
CellFormat cf = new CellFormat();
// Add Sheets to the Workbook.
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add Header Row.
var headerRow = new Row();
foreach (DataColumn column in ResultsData1.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData1.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData1.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
ResultsData1.Clear();
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
else
{
// Open the Excel file that we created before, and start to add sheets to it.
var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);
var workbookpart = spreadsheetDocument.WorkbookPart;
if (workbookpart.Workbook == null)
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
if (sheets.Elements<Sheet>().Any())
{
//Set the new sheet id
sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;
}
else
{
sheetId = 1;
}
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add the header row here.
var headerRow = new Row();
foreach (DataColumn column in ResultsData1.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData1.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData1.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
ResultsData1.Clear();
}
}
推荐答案
您的存储过程，USP_Report，返回两列名为Client Meeting ID。
ADataTable不能包含两个具有相同名称的列。
修复存储过程，使返回的列名称唯一。
或者修改你的代码以检查重复的列并在必要时重命名：
Your stored procedure,USP_Report, is returning two columns called "Client Meeting ID".
ADataTablecannot contain two columns with the same name.
Fix your stored procedure so that the returned column names are unique.
Or fix your code to check for duplicate columns and rename them if necessary:
foreach (DataRow drow in dtSchema.Rows)
{
string rawColumnName = Convert.ToString(drow["ColumnName"]);
int duplicateCount = 1;
string columnName = rawColumnName;
while (ResultsData1.Columns.Contains(columnName))
{
columnName = rawColumnName + " (" + duplicateCount + ")";
duplicateCount++;
}
...
注意：你也要去如果多个用户尝试同时导出数据，则会遇到问题，因为所有请求都将尝试写入同一文件。您最终可能会丢失文件，或者来自不同用户请求的数据会混淆在同一文件中。
你应该看看是否有一个选项可以在内存中创建文件而不将其保存到磁盘。如果没有，那么你应该为每个用户使用一个随机文件，并确保在使用 TransmitFile / WriteFile 将其发送给用户。
编辑：问题是 DataTable 存储在 static 字段中。这意味着来自每个用户的每个请求将操作相同的 DataTable 实例。由于多个用户同时访问该网站，您可能会遇到更加模糊和混乱的错误，因为用户的更改会相互覆盖。
删除 static 字段，而是使用局部变量。你需要将它作为参数传递给 ExportToOxml 方法。
NB: You're also going to run into problems if multiple users try to export the data at the same time, since all requests will be trying to write to the same file. You could end up with the file disappearing, or data from different users' requests getting mixed up in the same file.
You should see if there's an option to create the file in memory, without saving it to disk. If there isn't, then you should use a random file for each user, and be sure to delete it after using TransmitFile / WriteFile to send it to the user.
The problem is that the
DataTable is being stored in a static field. That means the every request from every user is going to be manipulating the same DataTable instance. With multiple users accessing the site at the same time, you're likely to get much more obscure and confusing errors, as users' changes overwrite each other.Remove the
static field, and use a local variable instead. You'll need to pass it into the ExportToOxml method as a parameter.private static void ExportToOxml(bool firstTime, DataTable ResultsData1)
{
...
}
protected void btnmeeting_Click(object sender, EventArgs e)
{
DataTable ResultsData1 = new DataTable();
...
}
（关于文件名冲突的评论仍然有效。）
这篇关于“名为'客户会议ID'的列已属于此数据表。” ASP .NET中的错误，即将数据下载到excel格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
