Click here to Skip to main content
13,198,482 members (61,762 online)
Click here to Skip to main content
Add your own
alternative version

Stats

3.6K views
1 bookmarked
Posted 28 Aug 2015

Export GridView Using OpenXML Part 2

, 28 Aug 2015
Rate this:
Please Sign up or sign in to vote.

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.

[__strong__]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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mohammed Ibrahim.L
Software Developer (Junior)
India India
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171020.1 | Last Updated 28 Aug 2015
Article Copyright 2015 by Mohammed Ibrahim.L
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid