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);
-
- WorkbookPart wbp = ssd.AddWorkbookPart();
- wbp.Workbook = new Workbook();
-
-
- WorksheetPart wsp = wbp.AddNewPart < WorksheetPart > ();
- wsp.Worksheet = new Worksheet(new SheetData());
-
- Sheets sht = ssd.WorkbookPart.Workbook.AppendChild < Sheets > (new Sheets());
-
- Sheet sheet = new Sheet()
- {
- Id = ssd.WorkbookPart.
-
- GetIdOfPart(wsp), SheetId = 1, Name = "mySheet"
- };
- sht.Append(sheet);
- Worksheet worksheet = new Worksheet();
- SheetData sheetData = new SheetData();
-
- Row row = new Row();
- Cell[] cell = new Cell[columncount];
-
- 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();
-
- {
- 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;
-
- IEnumerable < Sheet > sheets = document.WorkbookPart.Workbook.GetFirstChild < Sheets > ().Elements < Sheet > ().Where(s = > s.Name == "mySheet");
-
- if (sheets == null)
- {
- throw new ArgumentException("sheetName");
- }
- else
- {
-
- string sheetss = sheets.First().Id.Value;
-
-
- WorksheetPart worksheetPart = (WorksheetPart) document.WorkbookPart.GetPartById(sheetss);
-
- SheetData sheetData = worksheetPart.Worksheet.GetFirstChild < SheetData > ();
- for (int i = 0; i < rowcount; i++)
- {
-
-
- string[] columnheadname = new string[]
- {
- "A", "B", "C", "D", "E", "F", "G", "H", "I", "J"
- };
-
- Row row = new Row();
-
- Cell[] cell = new Cell[columncount];
- for (int j = 0; j < columncount; j++)
- {
-
- 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