将网格视图数据保存为excel [英] save grid view data to excel

问题描述

我正在运行一个基于窗口的程序，我在excel中保存网格视图数据。

我使用的头文件是： -



导入Microsoft.Office.Interop.Excel或

导入Excel = Microsoft.Office.Interop.Excel



但是在两个头文件我收到以下错误： -

导入Microsoft.Office.Interop.Excel中指定的命名空间或类型不包含任何公共成员或无法找到





请帮帮我...................

I am running a window based program in which I am saving grid view data in excel.
The header file i am using is:-

Imports Microsoft.Office.Interop.Excel OR
Imports Excel = Microsoft.Office.Interop.Excel

but in the both header file i am getting the following error :-
Namespace or type specified in "Imports Microsoft.Office.Interop.Excel" does not contain any public member or can not be found


please help me...................

推荐答案

请参阅下面的链接。

将Gridview数据导出到ASP.NET中的Excel [ ^ ]

Refer Below Link.
Export Gridview Data to Excel in ASP.NET[^]

你可以使用网格控件，其中循环用于在excel单元格中添加数据

但是对于庞大的数据，它的性能很慢。





private static void CreateParts（DataSet ds，SpreadsheetDocument document）

{

WorkbookPart workbookPart = document.AddWorkbookPart（）;

工作簿工作簿=新工作簿（）;

workbookPart.Workbook =工作簿;



//如果我们不添加WorkbookStylesPart，OLEDB将拒绝连接到此.xlsx文件！

WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart< workbookstylespart>（ rIdStyles）;

样式表样式表=新样式表（）;

workbookStylesPart.Stylesheet = stylesheet;



Sheets sheets = new Sheets（）;



//遍历DataSet中的每个DataTables，并为每个DataTable创建一个新的Excel工作表。

uint worksheetNumber = 1;

foreach（D atsTable dt in ds.Tables）

{

//对于你想要创建的每个工作表

string workSheetID =rId+ worksheetNumber。 ToString（）;

string worksheetName = dt.TableName;



WorksheetPart worksheetPart = workbookPart.AddNewPart< worksheetpart>（workSheetID）;

WriteDataTableToExcelWorksheet（dt，worksheetPart）;



工作表=新工作表（）{Name = worksheetName，SheetId =（UInt32Value）worksheetNumber，Id = workSheetID};

sheets.Append（sheet）;



worksheetNumber ++;

}



workbook.Append（sheet）;

}



private static void WriteDataTableToExcelWorksheet（DataTable dt ，WorksheetPart worksheetPart1）

{

工作表工作表=新工作表（）;

SheetViews sheetViews = new SheetViews（）;



SheetView sheetView = new SheetView（）{TabSelected = true，WorkbookViewId =（UInt32Value）0U};

sheetViews.Append（sheetView）;



SheetData sheetData1 = new SheetData（）;

string cellValue =;



//在我们的Excel文件中创建一个标题行，其中包含我们每个数据列的一个标题DataTable。

//

//我们还将创建一个数组，显示每列数据的类型（文本或数字），所以当我们来到写下实际的

//数据单元格，我们将知道是否要写入文本值或数字单元格值。

int numberOfColumns = dt.Columns.Count;

bool [] IsNumericColumn = new bool [numberOfColumns];



string [] excelColumnNames = new string [numberOfColumns];

for（int n = 0; n<列数; n ++）

excelColumnNames [n] = GetExcelColumnName（n）;



//

//创建我们的Excel工作表中的标题行

//

int rowIndex = 1;

行row1 = new Row（）{RowIndex =（UInt32Value） （u colInx = 0; colInx< numberOfColumns; colInx ++）
{

DataColumn col = dt.Columns [colInx] 1U};

];



AppendTextCell（excelColumnNames [colInx] +1，col.ColumnName，row1）;

IsNumericColumn [colInx] =（ col.DataType.FullName ==System.Decimal）; //例如System.String或System.Decimal

}

sheetData1.Append（row1）;





//

//现在，逐步浏览DataTable中的每一行数据......

//

double cellNumericValue = 0;

foreach（dt.Rows中的DataRow dr）

{

// ...创建一个新行，并将一行此行的数据附加到它。

++ rowIndex;

行newExcelRow = new Row（）{RowIndex =（ UInt32Value）（uint）rowIndex};



for（int colInx = 0; colInx< numberOfColumns; colInx ++）

{

cellValue = dr.ItemArray [colInx] .ToString（）;



if（IsNumericColumn [colInx]）

{

//对于numer ic单元格，确保我们的输入数据是一个数字，然后将其写入Excel文件。

cellNumericValue = 0;

double.TryParse（cellValue，out cellNumericValue） ;

cellValue = cellNumericValue.ToString（）;

AppendNumericCell（excelColumnNames [colInx] + rowIndex.ToString（），cellValue，newExcelRow）;

}

else

{

//对于文本单元格，只需将输入数据直接写入Excel文件。

AppendTextCell（excelColumnNames [colInx] + rowIndex.ToString（），cellValue，newExcelRow）;

}

}

sheetData1。追加（newExcelRow）;

}



worksheet.Append（sheetViews）;

worksheet.Append（sheetData1）;



worksheetPart1.Worksheet = worksheet;

}



private static void AppendTextCell（string cellReference，string cellStringValue，row excelRow）

{

//在我们的行中添加一个新的Excel单元格

Cell cell = new Cell（）{CellReference = cellReference，DataType = CellValues.String};

CellValue cellValue = new CellValue（）;

cellValue.Text = cellStringValue;

cell.Append（cellValue）;

excelRow.Append（cell）;

}



private static void AppendNumericCell（string cellReference，string cellStringValue，row excelRow）

{

//在我们的行中添加一个新的Excel单元格

Cell cell = new Cell（）{CellReference = cellReference};

CellValue cellValue = new CellValue（）;

cellValue.Text = cellStringValue;

cell.Append（cellValue）;

excelRow.Append（cell） ;

}



私有静态字符串GetExcelColumnName（int columnIndex）

{

//将基于零的列索引转换为Excel列引用（A，B，C .. Y，Y，AA，AB，AC ... AY，AZ，B1，B2 ..）

//

//例如GetExcelColumnName（0）应该返回A

// GetExcelColumnName（1）应该返回B

// GetExcelColumnName（25）应该返回Z

// GetExcelColumnName（26）应该返回AA

// GetExcelColumnName（27）应该返回AB

// ..等等..

//

if（columnIndex< 26）

return（（char）（''A''+ columnIndex））。ToString（）;



char firstChar =（ char）（''A''+（columnIndex / 26） - 1）;

char secondChar =（char）（''A''+（columnIndex％26））;



返回string.Format（{0} {1}，firstChar，secondChar）;

}

you can use the grid control where loop is use to add data in excel cell
but its performance is slow for huge data.


private static void CreateParts(DataSet ds, SpreadsheetDocument document)
{
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;

// If we don''t add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<workbookstylespart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;

Sheets sheets = new Sheets();

// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string workSheetID = "rId" + worksheetNumber.ToString();
string worksheetName = dt.TableName;

WorksheetPart worksheetPart = workbookPart.AddNewPart<worksheetpart>(workSheetID);
WriteDataTableToExcelWorksheet(dt, worksheetPart);

Sheet sheet = new Sheet() { Name = worksheetName, SheetId = (UInt32Value)worksheetNumber, Id = workSheetID };
sheets.Append(sheet);

worksheetNumber++;
}

workbook.Append(sheets);
}

private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart1)
{
Worksheet worksheet = new Worksheet();
SheetViews sheetViews = new SheetViews();

SheetView sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
sheetViews.Append(sheetView);

SheetData sheetData1 = new SheetData();
string cellValue = "";

// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We''ll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we''ll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];

string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
excelColumnNames[n] = GetExcelColumnName(n);

//
// Create the Header row in our Excel Worksheet
//
int rowIndex = 1;
Row row1 = new Row() { RowIndex = (UInt32Value)1U };
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];

AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, row1);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal"); // eg "System.String" or "System.Decimal"
}
sheetData1.Append(row1);


//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row''s data to it.
++rowIndex;
Row newExcelRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };

for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();

if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
cellNumericValue = 0;
double.TryParse(cellValue, out cellNumericValue);
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
sheetData1.Append(newExcelRow);
}

worksheet.Append(sheetViews);
worksheet.Append(sheetData1);

worksheetPart1.Worksheet = worksheet;
}

private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}

private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}

private static string GetExcelColumnName(int columnIndex)
{
// Convert a zero-based column index into an Excel column reference (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)
//
// eg GetExcelColumnName(0) should return "A"
// GetExcelColumnName(1) should return "B"
// GetExcelColumnName(25) should return "Z"
// GetExcelColumnName(26) should return "AA"
// GetExcelColumnName(27) should return "AB"
// ..etc..
//
if (columnIndex < 26)
return ((char)(''A'' + columnIndex)).ToString();

char firstChar = (char)(''A'' + (columnIndex / 26) - 1);
char secondChar = (char)(''A'' + (columnIndex % 26));

return string.Format("{0}{1}", firstChar, secondChar);
}

您可以使用网格控件，其中循环用于在Excel单元格中添加数据

但是对于大量数据，其性能很慢。

===

private static void CreateParts（DataSet ds，SpreadsheetDocument document）

{

WorkbookPart workbookPart = document.AddWorkbookPart（）;

工作簿工作簿=新工作簿（）;

workbookPart.Workbook =工作簿;

//如果我们不知道添加WorkbookStylesPart，OLEDB将拒绝连接到此.xlsx文件！

WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart< workbookstylespart>（rIdStyles）;

样式表样式表=新样式表（）;

workbookStylesPart.Stylesheet = stylesheet;

表格表格=新表格（）;

//循环通过我们的DataSet中的每个DataTable，并为每个DataTable创建一个新的Excel工作表。

uint worksheetNumber = 1;

foreach（数据表dt in ds.Tables）

{

//对于您要创建的每个工作表

string workSheetID =rId+ worksheetNumber.ToString（）;

string workshe etName = dt.TableName;

WorksheetPart worksheetPart = workbookPart.AddNewPart< worksheetpart>（workSheetID）;

WriteDataTableToExcelWorksheet（dt，worksheetPart）;

Sheet sheet = new Sheet（）{Name = worksheetName，SheetId =（UInt32Value）worksheetNumber，Id = workSheetID};

sheets.Append（sheet）;

worksheetNumber ++; < br $>
}

workbook.Append（sheet）;

}

private static void WriteDataTableToExcelWorksheet（DataTable dt，WorksheetPart worksheetPart1 ）

{

工作表工作表=新工作表（）;

SheetViews sheetViews = new SheetViews（）;

SheetView sheetView = new SheetView（）{TabSelected = true，WorkbookViewId =（UInt32Value）0U};

sheetViews.Append（sheetView）;

SheetData sheetData1 = new SheetData（） ;

string cellValue =;

//在我们的Excel文件中创建一个标题行，其中包含DataTab中每个数据列的一个标题le。

//我们还会创建一个数组，显示每列数据的类型（文本或数字），所以当我们来写实际的

//数据单元格，我们将知道是否要写入文本值或数字单元格值。

int numberOfColumns = dt.Columns.Count;

bool [] IsNumericColumn = new bool [numberOfColumns];

string [] excelColumnNames = new string [numberOfColumns];

for（int n = 0; n<列数; n ++）

excelColumnNames [n] = GetExcelColumnName（n）;

//在Excel工作表中创建标题行

int rowIndex = 1;

行row1 = new Row（）{RowIndex =（ UInt32Value）1U};

for（int colInx = 0; colInx< numberOfColumns; colInx ++）

{

DataColumn col = dt.Columns [colInx];

AppendTextCell（excelColumnNames [colInx] +1，col.ColumnName，row1）;

IsNumericColumn [colInx] =（col.DataType.FullName = =System.Decimal）;

}

sheetData1.Append（row1）;

//现在，逐步浏览DataTable中的每一行数据......

double cellNumericValue = 0;

foreach（dt.Rows中的DataRow dr）

{

// ...创建一个新行，并将一行此行的数据附加到它。

++ rowIndex;

行newExcelRow = new Row（）{RowIndex =（ UInt32Value）（uint）rowIndex};

for（int colInx = 0; colInx< numberOfColumns; colInx ++）

{

cellValue = dr .ItemArray [colInx] .ToString（）;

if（IsNumericColumn [colInx]）

{

//对于数字单元格，请确保我们的输入数据是一个数字，然后将其写入Excel文件。

cellNumericValue = 0;

double.TryParse（cellValue，out cellNumericValue）;

cellValue = cellNumericValue.ToString（）;

AppendNumericCell（excelColumnNames [colInx] + rowIndex.ToString（），cellValue，newExcelRow）;

}

else

{

//对于文本单元格，只需将输入数据直接写入Excel文件。

AppendTextCell（excelColumnNames [colInx] + rowIndex.ToString（），cellValue，newExcelRow）;

}

}

sheetData1.Append（newExcelRow）;

}

worksheet.Append（sheetViews）;

worksheet.Append（sheetData1）;

worksheetPart1.Worksheet = worksheet;

}

private static void AppendTextCell（string cellReference，string cellStringValue，row excelRow）

{

//在我们的行中添加一个新的Excel单元格

Cell cell = new Cell（）{CellReference = cellReference，DataType = CellValues.String};

CellValue cellValue = new CellValue（）;

cellValue.Text = cellStringValue ;

cell.Append（cellValue）;

excelRow.Append（cell）;

}

private static void AppendNumericCell（string cellReference，string cellStringValue，row excelRow）

{

//添加新的Excel单元格到我们的行

Cell cell = new Cell（）{CellReference = cellReference};

CellValue cellValue = new CellValue（）;

cellValue.Text = cellStringValue;

cell.Append（cellValue）;

excelRow.Append（cell）;

}

私有静态字符串GetExcelColumnName（int columnIndex）

{

if（columnIndex< 26）

return（（char）（''A''+ columnIndex））。ToString（）;

char firstChar =（char）（''A'' +（columnIndex / 26） - 1）;

char secondChar =（char）（''A''+（columnIndex％26））;

返回string.Format （{0} {1}，firstChar，secondChar）;

}

you can use the grid control where loop is use to add data in excel cell
but its performance is slow for huge data.
===
private static void CreateParts(DataSet ds, SpreadsheetDocument document)
{
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don''t add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<workbookstylespart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
Sheets sheets = new Sheets();
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string workSheetID = "rId" + worksheetNumber.ToString();
string worksheetName = dt.TableName;
WorksheetPart worksheetPart = workbookPart.AddNewPart<worksheetpart>(workSheetID);
WriteDataTableToExcelWorksheet(dt, worksheetPart);
Sheet sheet = new Sheet() { Name = worksheetName, SheetId = (UInt32Value)worksheetNumber, Id = workSheetID };
sheets.Append(sheet);
worksheetNumber++;
}
workbook.Append(sheets);
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart1)
{
Worksheet worksheet = new Worksheet();
SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
sheetViews.Append(sheetView);
SheetData sheetData1 = new SheetData();
string cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
// We''ll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we''ll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
excelColumnNames[n] = GetExcelColumnName(n);
// Create the Header row in our Excel Worksheet
int rowIndex = 1;
Row row1 = new Row() { RowIndex = (UInt32Value)1U };
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, row1);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal");
}
sheetData1.Append(row1);
// Now, step through each row of data in our DataTable...
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row''s data to it.
++rowIndex;
Row newExcelRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
cellNumericValue = 0;
double.TryParse(cellValue, out cellNumericValue);
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
sheetData1.Append(newExcelRow);
}
worksheet.Append(sheetViews);
worksheet.Append(sheetData1);
worksheetPart1.Worksheet = worksheet;
}
private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}
private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}
private static string GetExcelColumnName(int columnIndex)
{
if (columnIndex < 26)
return ((char)(''A'' + columnIndex)).ToString();
char firstChar = (char)(''A'' + (columnIndex / 26) - 1);
char secondChar = (char)(''A'' + (columnIndex % 26));
return string.Format("{0}{1}", firstChar, secondChar);
}

这篇关于将网格视图数据保存为excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！