Export GridView Using OpenXML Part 2





0/5 (0 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.
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