Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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...................
Posted 20-Dec-12 5:36am
Comments
DinoRondelly at 20-Dec-12 11:57am
   
Did you add the reference to your solution? right click your solution click add reference select the .Net tab and scroll down look Microsoft.Office.Interop.Excel and add it.
Ashok19r91d at 21-Dec-12 3:29am
   
+5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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("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(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);
}
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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("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(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);
}
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 442
1 sanket saxena 355
2 Abhinav S 268
3 OriginalGriff 250
4 CPallini 205
0 Sergey Alexandrovich Kryukov 8,497
1 OriginalGriff 4,850
2 Peter Leow 3,839
3 Maciej Los 3,535
4 Er. Puneet Goel 3,107


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 1 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid