Export GridView Using OpenXML Part 2

Hi, guys in my previous trick 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 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <reocrd>
    <row1>Column name</row1>
    <row2>Celldata1</row2>
    <row3>Celldata1</row3>
    </reocrd>
</data-set>

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 Below 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); //call excel write method to enter the data into the Excel

After creating the Excel template, now pass the Gridview values one by one to the excel template 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();
    }

Desing the Excel according to your wish.

Have fun!!!

Happy coding