OpenXML Excel Write Download

            MemoryStream mem = new MemoryStream();
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(mem, SpreadsheetDocumentType.Workbook);
            try
            {
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                WorksheetPart worksheetPart = workbookpart.AddNewPart();
                worksheetPart.Worksheet = new Worksheet(new SheetData());
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = "Sheet1"
                };
                sheets.Append(sheet);

                DataSet dsResult = GetExcelData();
                DataRow drRow = null;
                Cell cell = null;
                uint iRowIdx = 0;
                string[] sheetArray = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L" };
                for (int i = 0; i < dsResult.Tables[0].Rows.Count; i++)
                {
                    drRow = dsResult.Tables[0].Rows[i];
                    iRowIdx = Convert.ToUInt32(i + 1);

                    for (int j = 0; j < sheetArray.Length; j++)
                    {
                        cell = InsertCellInWorksheet(sheetArray[j], iRowIdx, worksheetPart);
                        cell.CellValue = new CellValue(drRow[j].ToString());
                        cell.DataType = new EnumValue(CellValues.String);
                    }
                }
                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();

                Response.Clear();
                Response.AddHeader("Content-Disposition", "attachment; filename=" + "EAS04030.xlsx");
                Response.AddHeader("Content-Length", mem.ToArray().Length.ToString());
                Response.ContentType = "application/vnd.ms-excel";
                Response.BinaryWrite(mem.ToArray());
                Response.Flush();
                Response.Close();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                spreadsheetDocument.Dispose();

            }

 

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

private Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements())
                {
                    if (cell.CellReference.Value.Length == cellReference.Length)
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
}