HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Buffer = true; // HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"; HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=ExportGridviewData.xlsx"); HttpContext.Current.Response.Charset = ""; MemoryStream ms = new MemoryStream(); using (var excelSpreadSheet = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var excelworkbookPart = excelSpreadSheet.AddWorkbookPart(); excelSpreadSheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); excelSpreadSheet.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); //Add a stylesheet to support the formatting // Stylesheet DocumentFormat.OpenXml.Packaging.WorkbookStylesPart workbookStylesPart; workbookStylesPart = excelSpreadSheet.WorkbookPart.AddNewPart<DocumentFormat.OpenXml.Packaging.WorkbookStylesPart>(); workbookStylesPart.Stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet(); workbookStylesPart.Stylesheet.Save(); AddBasicStyles(excelSpreadSheet); uint sheetId = 1; foreach (System.Data.DataTable dataTable in pExportedDataSet.Tables) { var sheetPart = excelSpreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets excelSheets = excelSpreadSheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>(); string relationshipId = excelSpreadSheet.WorkbookPart.GetIdOfPart(sheetPart); if (excelSheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0) { sheetId = excelSheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet excelsheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = dataTable.TableName }; excelSheets.Append(excelsheet); DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); List<String> dspColumns = new List<string>(); foreach (DataColumn HZLstcolumn in dataTable.Columns) { dspColumns.Add(HZLstcolumn.ColumnName); DocumentFormat.OpenXml.Spreadsheet.Cell exlcell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); //Headers are strings exlcell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; //Write out the string exlcell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(HZLstcolumn.ColumnName); headerRow.AppendChild(exlcell); } sheetData.AppendChild(headerRow); foreach (DataRow exlDataRow in dataTable.Rows) { DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (String HZExlcol in dspColumns) { DocumentFormat.OpenXml.Spreadsheet.Cell rowCell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); rowCell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; rowCell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(exlDataRow[HZExlcol].ToString()); newRow.AppendChild(rowCell); } sheetData.AppendChild(newRow); } //added for Merge //end added for meger } } ms.WriteTo(HttpContext.Current.Response.OutputStream); //HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); HttpContext.Current.Response.Close(); System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)