save grid view data to excel

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);
}

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);
}