Open XML Export datatable to excel directly on disk

Question

I want to export datatable to xlsx directly to my disk instead of giving destination path and saving file on server.

I have following function :

private void ExportToCSVFileOpenXML(DataTable dt, string destination)
    {
        DataSet ds = new DataSet();
        DataTable dtCopy = new DataTable();
        dtCopy = dt.Copy();
        ds.Tables.Add(dtCopy);
        try 
        {


            var workbook = SpreadsheetDocument.Create(Server.MapPath("~/" + destination.Replace("/","").Replace(":","")), DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);

            {           

                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                foreach (System.Data.DataTable table in ds.Tables)
                {

                    var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    uint sheetId = 1;
                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List<String> columns = new List<string>();
                    foreach (System.Data.DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);

                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }


                    sheetData.AppendChild(headerRow);

                    foreach (System.Data.DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }

                }
            }



                }
        catch (Exception)
        {

            throw;
        }
    }

How can I directly export this to disk instead of saving on server by giving destination path as :

var workbook = SpreadsheetDocument.Create(Server.MapPath("~/" + destination.Replace("/","").Replace(":","")), DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);

Please help me.

Answer 1

Alex answer lead me to correct answer.

I did following thing :

private byte[] ExportToCSVFileOpenXML(DataTable dt)
    {
        DataSet ds = new DataSet();
        DataTable dtCopy = new DataTable();
        dtCopy = dt.Copy();
        ds.Tables.Add(dtCopy);
        try 
        {
            byte[] returnBytes = null;
            MemoryStream mem = new MemoryStream();
            var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
            {
                var workbookPart = workbook.AddWorkbookPart();
                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
                foreach (System.Data.DataTable table in ds.Tables)
                {
                    var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                    var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                    sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                    DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                    string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                    uint sheetId = 1;
                    if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                    {
                        sheetId =
                            sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                    sheets.Append(sheet);

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                    List<String> columns = new List<string>();
                    foreach (System.Data.DataColumn column in table.Columns)
                    {
                        columns.Add(column.ColumnName);

                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }


                    sheetData.AppendChild(headerRow);

                    foreach (System.Data.DataRow dsrow in table.Rows)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
                            newRow.AppendChild(cell);
                        }

                        sheetData.AppendChild(newRow);
                    }

                }
            }
            workbook.WorkbookPart.Workbook.Save();
            workbook.Close();

            returnBytes = mem.ToArray();

            return returnBytes;
                }
        catch (Exception)
        {

            throw;
        }
    }

Called this function as:

protected void hlInCorrectRecords_Click(object sender, EventArgs e)
    {
        lblmsg.Text = "";
        DataSet dsDtUploadedSummary = (DataSet)ViewState["dsDtUploadedSummary"];
        if (dsDtUploadedSummary.Tables.Count > 0)
        {
            DataTable dtFreshRecords = dsDtUploadedSummary.Tables[4];
            if (dtFreshRecords.Rows.Count > 0 && dtFreshRecords != null)
            {                
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                string filename = @"IncorrectRecordsUploaded_" + DateTime.Now.ToString();                
                Response.AddHeader("Content-Disposition", "inline;filename=" + filename.Replace("/", "").Replace(":", "")+".xlsx");
                Response.BinaryWrite(ExportToCSVFileOpenXML(dtFreshRecords));                                
                Response.Flush();
                Response.End();
            }
            else
            {
                lblmsg.Text = "No Data To Export";
            }
        }
        else
        {
            lblmsg.Text = "No Data To Export";
        }
    }

Thanks Alex. I am using plain ASP.NET not MVC

Answer 2

SpreadsheetDocument.Create accepts a stream, string, or package as its first argument so we can just use a MemoryStream to create the workbook in memory and return a byte array.

It should be something like this:

public byte[] ExportToCSVFileOpenXML(DataTable dt)
    {
        byte[] returnBytes = null;
        using (MemoryStream mem = new MemoryStream())
        {
            var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);

            // your code

            workbook.WorkbookPart.Workbook.Save();
            workbook.Close();

            returnBytes = mem.ToArray();
        }

        return returnBytes;
    }

Once you have a byte array passing it as a file should be quite easy.

If you are using MVC it should be something like this in your controler:

return File(ExportToCSVFileOpenXML(aTable), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "export.xlsx");