Export GridView Using OpenXML: Part 2

In this article you will learn how to export a GridView using OpenXML.

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:

Create XML

Now import the Following the Namespace:

  1. using DocumentFormat.OpenXml.Spreadsheet;  
  2. using DocumentFormat.OpenXml.Packaging;  
  3. using DocumentFormat.OpenXml;  
  4. using System.IO.Packaging;  
Now create a Excel template dynamically according to the column available in the Gridview. Use the Belo code:
  1. saveFileDialog1.ShowDialog();  
  2. if (saveFileDialog1.FileName != "")   
  3. {  
  4.     int columncount = dataGridView1.Columns.Count;  
  5.     string filepath = saveFileDialog1.FileName.ToString();  
  6.     SpreadsheetDocument ssd = SpreadsheetDocument.Create(filepath + ".xlsx", SpreadsheetDocumentType.Workbook);  
  7.     //Add work part to the Document  
  8.     WorkbookPart wbp = ssd.AddWorkbookPart();  
  9.     wbp.Workbook = new Workbook();  
  10.   
  11.     //add work sheet to the work part  
  12.     WorksheetPart wsp = wbp.AddNewPart < WorksheetPart > ();  
  13.     wsp.Worksheet = new Worksheet(new SheetData());  
  14.     // add sheets   
  15.     Sheets sht = ssd.WorkbookPart.Workbook.AppendChild < Sheets > (new Sheets());  
  16.     // Append a new worksheet and associate it with the workbook.  
  17.     Sheet sheet = new Sheet()   
  18.     {  
  19.         Id = ssd.WorkbookPart.  
  20.             // create an new sheet  
  21.         GetIdOfPart(wsp), SheetId = 1, Name = "mySheet"  
  22.     };  
  23.     sht.Append(sheet);  
  24.     Worksheet worksheet = new Worksheet();  
  25.     SheetData sheetData = new SheetData();  
  26.     //create a new row, cell   
  27.     Row row = new Row();  
  28.     Cell[] cell = new Cell[columncount];  
  29.     // the below used to create temple of the existing gridview  
  30.     for (int i = 0; i < columncount; i++)   
  31.     {  
  32.         string[] columnhead = new string[columncount];  
  33.         string[] columnheadname = new string[]   
  34.         {  
  35.             "A""B""C""D""E""F""G""H""I""J"  
  36.         };  
  37.         columnhead[i] = dataGridView1.Columns[i].HeaderText.ToString();  
  38.         cell[i] = new Cell();  
  39.         //passing the cell value  
  40.         {  
  41.             CellReference = columnheadname[0].ToString(), DataType = CellValues.String,  
  42.   
  43.                 CellValue = new CellValue(columnhead[i])  
  44.         };  
  45.   
  46.         row.Append(cell[i]);  
  47.     }  
  48. }  
  49. sheetData.Append(row);  
  50. worksheet.Append(sheetData);  
  51. wsp.Worksheet = worksheet;  
  52. wbp.Workbook.Save();  
  53. ssd.Close();  
  54. exceldata(filepath);  
After creating the Excel now pass the Gridview values one by one by using the Below Code.
  1. public void exceldata(String docName)   
  2. {  
  3.     int rowcount = dataGridView1.Rows.Count;  
  4.     int columncount = dataGridView1.Columns.Count;  
  5.     using(SpreadsheetDocument document = SpreadsheetDocument.Open(docName + ".xlsx"true))   
  6.     {  
  7.         WorkbookPart wbPart = document.WorkbookPart;  
  8.         // check whether the sheet is exist or not  
  9.         IEnumerable < Sheet > sheets = document.WorkbookPart.Workbook.GetFirstChild < Sheets > ().Elements < Sheet > ().Where(s = > s.Name == "mySheet");  
  10.   
  11.         if (sheets == null)   
  12.         {  
  13.             throw new ArgumentException("sheetName");  
  14.         }   
  15.         else   
  16.         {  
  17.             //get the ID of the sheet  
  18.             string sheetss = sheets.First().Id.Value;  
  19.   
  20.             // get the workpartsheet of the exesting data  
  21.             WorksheetPart worksheetPart = (WorksheetPart) document.WorkbookPart.GetPartById(sheetss);  
  22.             //get the sheet data of the exsting data  
  23.             SheetData sheetData = worksheetPart.Worksheet.GetFirstChild < SheetData > ();  
  24.             for (int i = 0; i < rowcount; i++)   
  25.             {  
  26.                 //Assign the column name dynamically using array  
  27.   
  28.                 string[] columnheadname = new string[]   
  29.                 {  
  30.                     "A""B""C""D""E""F""G""H""I""J"  
  31.                 };  
  32.                 //create a row  
  33.                 Row row = new Row();  
  34.                 //create the cell dynamically using array  
  35.                 Cell[] cell = new Cell[columncount];  
  36.                 for (int j = 0; j < columncount; j++)   
  37.                 {  
  38.                     // get the value in the grid view  
  39.                     string data1 = dataGridView1.Rows[i].Cells[j].Value.ToString();  
  40.                     cell[j] = new Cell()   
  41.                     {  
  42.                         CellReference = columnheadname[0].ToString(), DataType = CellValues.String,  
  43.   
  44.                         CellValue = new CellValue(data1)  
  45.                     };  
  46.                     row.Append(cell[j]);  
  47.                 }  
  48.                 sheetData.Append(row);  
  49.             }  
  50.             worksheetPart.Worksheet.Save();  
  51.         }  
Design the Excel according to your wish.

Have fun!!!
Happy coding