Export GridView Using OpenXML: Part 2

Hi, guys in my previous I have shown how to export the Gridview into Document using OPENXML.

Now in this article I am gone to show how to export the grid view into Excel using OPENXML.

Open a new solution in your IDE.

Create XML file which help you to know the Structure of the excel document. like below:

Now import the Following the Namespace:

using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using System.IO.Packaging;

Now create a Excel template dynamically according to the column available in the Gridview. Use the Belo code:

saveFileDialog1.ShowDialog();
if (saveFileDialog1.FileName != "")
{
int columncount = dataGridView1.Columns.Count;
string filepath = saveFileDialog1.FileName.ToString();
SpreadsheetDocument ssd = SpreadsheetDocument.Create(filepath + ".xlsx", SpreadsheetDocumentType.Workbook);
//Add work part to the Document
WorkbookPart wbp = ssd.AddWorkbookPart();
wbp.Workbook = new Workbook();
//add work sheet to the work part
WorksheetPart wsp = wbp.AddNewPart < WorksheetPart > ();
wsp.Worksheet = new Worksheet(new SheetData());
// add sheets
Sheets sht = ssd.WorkbookPart.Workbook.AppendChild < Sheets > (new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = ssd.WorkbookPart.
// create an new sheet
GetIdOfPart(wsp), SheetId = 1, Name = "mySheet"
};
sht.Append(sheet);
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
//create a new row, cell
Row row = new Row();
Cell[] cell = new Cell[columncount];
// the below used to create temple of the existing gridview
for (int i = 0; i < columncount; i++)
{
string[] columnhead = new string[columncount];
string[] columnheadname = new string[]
{
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"
};
columnhead[i] = dataGridView1.Columns[i].HeaderText.ToString();
cell[i] = new Cell();
//passing the cell value
{
CellReference = columnheadname[0].ToString(), DataType = CellValues.String,
CellValue = new CellValue(columnhead[i])
};
row.Append(cell[i]);
}
}
sheetData.Append(row);
worksheet.Append(sheetData);
wsp.Worksheet = worksheet;
wbp.Workbook.Save();
ssd.Close();
exceldata(filepath);

After creating the Excel now pass the Gridview values one by one by using the Below Code.

public void exceldata(String docName)
{
int rowcount = dataGridView1.Rows.Count;
int columncount = dataGridView1.Columns.Count;
using(SpreadsheetDocument document = SpreadsheetDocument.Open(docName + ".xlsx", true))
{
WorkbookPart wbPart = document.WorkbookPart;
// check whether the sheet is exist or not
IEnumerable < Sheet > sheets = document.WorkbookPart.Workbook.GetFirstChild < Sheets > ().Elements < Sheet > ().Where(s = > s.Name == "mySheet");
if (sheets == null)
{
throw new ArgumentException("sheetName");
}
else
{
//get the ID of the sheet
string sheetss = sheets.First().Id.Value;
// get the workpartsheet of the exesting data
WorksheetPart worksheetPart = (WorksheetPart) document.WorkbookPart.GetPartById(sheetss);
//get the sheet data of the exsting data
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild < SheetData > ();
for (int i = 0; i < rowcount; i++)
{
//Assign the column name dynamically using array
string[] columnheadname = new string[]
{
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"
};
//create a row
Row row = new Row();
//create the cell dynamically using array
Cell[] cell = new Cell[columncount];
for (int j = 0; j < columncount; j++)
{
// get the value in the grid view
string data1 = dataGridView1.Rows[i].Cells[j].Value.ToString();
cell[j] = new Cell()
{
CellReference = columnheadname[0].ToString(), DataType = CellValues.String,
CellValue = new CellValue(data1)
};
row.Append(cell[j]);
}
sheetData.Append(row);
}
worksheetPart.Worksheet.Save();
}

Design the Excel according to your wish.

Have fun!!!
Happy coding

8 Comments

Join the conversation! Your thoughts help the community grow.

Bhuvanesh Mohankumar10y ago
Good one...
Arul R10y ago
Nice share
Santhakumar Munuswamy11y ago
Nice article. Thanks for sharing
Pankaj Kumar Choudhary11y ago
Nice Explain Sir.....
Rakesh11y ago
Nice one
Gowtham K11y ago
Good one
Rajeesh Menoth11y ago
Good One
Karthikeyan K11y ago
Good one sir...Thanks for share