How to export data grid to open office excel?

Question

0.00/5 (No votes)

See more:

Below is my code that export the data grid to MS excel. Instead of MS excel, I need to export data to open office excel. I looked at couple of posts but did not get any help.
Thank You!

protected void ExportToExcel(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");          
            Response.ContentType = "application/vnd.ms-excel";
           // Response.ContentType = "text/csv";
            using (StringWriter sw = new StringWriter())
            {
                HtmlTextWriter hw = new HtmlTextWriter(sw);
                //To Export all pages
                gvreport.AllowPaging = false;
                this.BindGrid();
                gvreport.HeaderRow.BackColor = Color.White;
                foreach (TableCell cell in gvreport.HeaderRow.Cells)
                {
                    cell.BackColor = gvreport.HeaderStyle.BackColor;
                }
                foreach (GridViewRow row in gvreport.Rows)
                {
                    row.BackColor = Color.White;
                    foreach (TableCell cell in row.Cells)
                    {
                        if (row.RowIndex % 2 == 0)
                        {
                            cell.BackColor = gvreport.AlternatingRowStyle.BackColor;
                        }
                        else
                        {
                            cell.BackColor = gvreport.RowStyle.BackColor;
                        }
                        cell.CssClass = "textmode";
                    }
                }

 

                gvreport.RenderControl(hw);

 

                //style to format numbers to string

 

                string style = @"<style> .textmode { } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }

 

        }

What I have tried:

I looked at couple of posts but did not get any help.
Thank You!

Posted 20-May-20 9:04am

Member 13910513

Updated 20-May-20 21:39pm

Add a Solution

Comments

Maciej Los 21-May-20 3:29am

Above code does NOT export data to Excel, but to html format!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2020-05-20T20:45:00

Solution 1

I would have a look at the example here AODL example 1 - Apache OpenOffice Wiki[^] which requires (iirc) NuGet Gallery | AODL 1.2.0.1[^]

Posted 20-May-20 20:45pm

Garth J Lancaster

Comments

Maciej Los 21-May-20 3:54am

Interesting alternative ;)

Richard Deeming 21-May-20 12:38pm

Probably worth following the links through to the AODL website:
OpenDocument 4 all tools and api's[^]

Looks like the version on NuGet is outdated, and missing various features and bug-fixes. That would tally with the fact that the NuGet release notes stop at December 2005, and the SourceForge project hasn't been updated since February 2006.

The site linked from there for the updated version now redirects to LibreOffice. That page mentions a version based on JDK 8, which suggests that the new version doesn't support .NET any more. The latest download there seems to be from April 2017.

Maciej Los 21-May-20 16:12pm

Very valuable information!

Maciej Los · Answer 2 · 2020-05-20T21:40:00

You have to install Open XML SDK 2.5 for Office | Microsoft Docs[^]
Tehn you have to read MSDN documentation (which contains examples): Spreadsheets (Open XML SDK) | Microsoft Docs[^]

Quote:

Sample Code

The CreateSpreadsheetWorkbook method shown here can be used to create a basic Excel document, a workbook with one sheet named "mySheet". To call it in your program, you can use the following code example that creates a file named "Sheet2.xlsx" in the public documents folder.

C#

CreateSpreadsheetWorkbook(@"c:\Users\Public\Documents\Sheet2.xlsx")

Notice that the file name extension, .xlsx, matches the type of file specified by the SpreadsheetDocumentType.Workbook parameter in the call to the Create method.

Following is the complete sample code

C#

public static void CreateSpreadsheetWorkbook(string filepath)
{
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
        AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.
        GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
    sheets.Append(sheet);

    workbookpart.Workbook.Save();

    // Close the document.
    spreadsheetDocument.Close();
}