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