"Столбец с именем" идентификатор встречи клиента "уже принадлежит этому datatable." ошибка в ASP .Чистая бочках скачивания данных в формате Excel
"A column named 'Client Meeting ID' already belongs to this DataTable."}
ошибка, когда я пытаюсь загрузить данные из базы данных в формат Excel.
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);
}
}
Это дает ошибку при
<pre>ResultsData1.Columns.Add(column);
Что я уже пробовал:
Я создал две глобальные переменные как
static private int rowsPerSheet = 200;
static private DataTable ResultsData1 = new DataTable();
Теперь на событии нажатия кнопки у меня есть следующий код
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 строк на каждый лист
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();
}
}
Richard MacCutchan
Какую часть этого сообщения об ошибке вы не понимаете?
Suvendu Shekhar Giri
интересно то же самое :P