export to excel in asp.net using openxml

Question

Hello everyone i am working on a project which requires me to export some data to excel on a button click using open xml.Here is the class i am using for exporting to excel:

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using _14junexcel.Account;

namespace _14junexcel
{
    public class CreateExcelOpen
    {
        public void BuildWorkbook1(string filename)
        {
            string sFile = @"D:\\ExcelOpenXmlWithImageAndStyles.xlsx";
            if (File.Exists(sFile))
            {
                File.Delete(sFile);
            }
            BuildWorkbook(sFile);
        }

        private static void BuildWorkbook(string filename)
        {
            try
            {
                using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart wbp = xl.AddWorkbookPart();
                    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                    Workbook wb = new Workbook();
                    FileVersion fv = new FileVersion();
                    fv.ApplicationName = "Microsoft Office Excel";
                    Worksheet ws = new Worksheet();
                    SheetData sd = new SheetData();

                    WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
                    wbsp.Stylesheet = CreateStylesheet();
                    wbsp.Stylesheet.Save();

                    string sImagePath = @"C:\\Users\\Public\\Pictures\\Sample Pictures\\Jellyfish.jpg";
                    DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
                    ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
                    using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
                    {
                        imgp.FeedData(fs);
                    }

                    NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
                    nvdp.Id = 1025;
                    nvdp.Name = "Picture 1";
                    nvdp.Description = "polymathlogo";
                    DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
                    picLocks.NoChangeAspect = true;
                    picLocks.NoChangeArrowheads = true;
                    NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
                    nvpdp.PictureLocks = picLocks;
                    NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
                    nvpp.NonVisualDrawingProperties = nvdp;
                    nvpp.NonVisualPictureDrawingProperties = nvpdp;

                    DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
                    stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

                    BlipFill blipFill = new BlipFill();
                    DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
                    blip.Embed = dp.GetIdOfPart(imgp);
                    blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
                    blipFill.Blip = blip;
                    blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
                    blipFill.Append(stretch);

                    DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
                    DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
                    offset.X = 0;
                    offset.Y = 0;
                    t2d.Offset = offset;
                    Bitmap bm = new Bitmap(sImagePath);
                    //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
                    //http://stackoverflow.com/questions/1341930/pixel-to-centimeter
                    //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
                    DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                    extents.Cx = (long)bm.Width * (long)((float)0 / bm.HorizontalResolution);
                    extents.Cy = (long)bm.Height * (long)((float)0 / bm.VerticalResolution);
                    bm.Dispose();
                    t2d.Extents = extents;
                    ShapeProperties sp = new ShapeProperties();
                    sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
                    sp.Transform2D = t2d;
                    DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
                    prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
                    prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
                    sp.Append(prstGeom);
                    sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
                    picture.NonVisualPictureProperties = nvpp;
                    picture.BlipFill = blipFill;
                    picture.ShapeProperties = sp;

                    Position pos = new Position();
                    pos.X = 0;
                    pos.Y = 0;
                    Extent ext = new Extent();
                    ext.Cx = extents.Cx;
                    ext.Cy = extents.Cy;
                    AbsoluteAnchor anchor = new AbsoluteAnchor();
                    anchor.Position = pos;
                    anchor.Extent = ext;
                    anchor.Append(picture);
                    anchor.Append(new ClientData());
                    WorksheetDrawing wsd = new WorksheetDrawing();
                    wsd.Append(anchor);
                    Drawing drawing = new Drawing();
                    drawing.Id = dp.GetIdOfPart(imgp);
                    wsd.Save(dp);

                    UInt32 index;
                    Random rand = new Random();

                    sd.Append(CreateHeader(2));
                    sd.Append(CreateColumnHeader(4));

                    for (index = 5; index < 6; ++index)
                    {
                        sd.Append(CreateContent(index, ref rand));
                    }

                    ws.Append(sd);
                    ws.Append(drawing);
                    wsp.Worksheet = ws;
                    wsp.Worksheet.Save();
                    Sheets sheets = new Sheets();
                    Sheet sheet = new Sheet();
                    sheet.Name = "Sheet1";
                    sheet.SheetId = 1;

                    sheet.Id = wbp.GetIdOfPart(wsp);
                    sheets.Append(sheet);
                    wb.Append(fv);
                    wb.Append(sheets);

                    xl.WorkbookPart.Workbook = wb;
                    xl.WorkbookPart.Workbook.Save();
                    //xl.WorkbookPart.Workbook.Save();
                    xl.Close();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                Console.ReadLine();
            }
        }


        private static Stylesheet CreateStylesheet()
        {
            Stylesheet ss = new Stylesheet();

            Fonts fts = new Fonts();
            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();
            ftn.Val = StringValue.FromString("Calibri");
            FontSize ftsz = new FontSize();
            ftsz.Val = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ftn = new FontName();
            ftn.Val = StringValue.FromString("Palatino Linotype");
            ftsz = new FontSize();
            ftsz.Val = DoubleValue.FromDouble(18);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);

            Fills fills = new Fills();
            Fill fill;
            PatternFill patternFill;
            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.None;
            fill.PatternFill = patternFill;
            fills.Append(fill);

            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.Gray125;
            fill.PatternFill = patternFill;
            fills.Append(fill);

            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.Solid;
            patternFill.ForegroundColor = new ForegroundColor();
            patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
            patternFill.BackgroundColor = new BackgroundColor();
            patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
            fill.PatternFill = patternFill;
            fills.Append(fill);

            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);

            Borders borders = new Borders();
            Border border = new Border();
            border.LeftBorder = new LeftBorder();
            border.RightBorder = new RightBorder();
            border.TopBorder = new TopBorder();
            border.BottomBorder = new BottomBorder();
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);

            border = new Border();
            border.LeftBorder = new LeftBorder();
            border.LeftBorder.Style = BorderStyleValues.Thin;
            border.RightBorder = new RightBorder();
            border.RightBorder.Style = BorderStyleValues.Thin;
            border.TopBorder = new TopBorder();
            border.TopBorder.Style = BorderStyleValues.Thin;
            border.BottomBorder = new BottomBorder();
            border.BottomBorder.Style = BorderStyleValues.Thin;
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);



            CellStyleFormats csfs = new CellStyleFormats();
            CellFormat cf = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            csfs.Append(cf);
            csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);

            uint iExcelIndex = 164;
            NumberingFormats nfs = new NumberingFormats();
            CellFormats cfs = new CellFormats();

            cf = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cfs.Append(cf);

            NumberingFormat nfDateTime = new NumberingFormat();
            nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
            nfs.Append(nfDateTime);

            NumberingFormat nf4decimal = new NumberingFormat();
            nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
            nfs.Append(nf4decimal);

            // #,##0.00 is also Excel style index 4
            NumberingFormat nf2decimal = new NumberingFormat();
            nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
            nfs.Append(nf2decimal);

            // @ is also Excel style index 49
            NumberingFormat nfForcedText = new NumberingFormat();
            nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfForcedText.FormatCode = StringValue.FromString("@");
            nfs.Append(nfForcedText);
            //Alignment
            Alignment align = new Alignment(){Horizontal = HorizontalAlignmentValues.General,Vertical=VerticalAlignmentValues.Center};
            //wraptext
           // Alignment align1 = new Alignment(){Horizontal=HorizontalAlignmentValues.CenterContinuous,Vertical=VerticalAlignmentValues.Center};


            // index 1
            cf = new CellFormat();
            cf.NumberFormatId = nfDateTime.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;

            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 2
            cf = new CellFormat();
            cf.NumberFormatId = nf4decimal.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 3
            cf = new CellFormat();
            cf.NumberFormatId = nf2decimal.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 4
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 5
            // Header text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 1;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 6
            // column text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 1;
            cf.FillId = 1;
            cf.BorderId = 1;
            cf.FormatId = 1;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 7
            // coloured 2 decimal text
            cf = new CellFormat();
            cf.NumberFormatId = nf2decimal.NumberFormatId;
            cf.FontId = 0;
            //cf.FillId = 2;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId =0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            //cf.Append(align);
            cf.Append(align);

            // index 8
            // coloured column text
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 0;
            //cf.FillId = 2;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
            cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);

            ss.Append(nfs);
            ss.Append(fts);
            ss.Append(fills);
            ss.Append(borders);
            ss.Append(csfs);
            ss.Append(cfs);

            CellStyles css = new CellStyles();
            CellStyle cs = new CellStyle();
            cs.Name = StringValue.FromString("Normal");
            cs.FormatId = 0;
            cs.BuiltinId = 0;
            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
            ss.Append(css);

            DifferentialFormats dfs = new DifferentialFormats();
            dfs.Count = 0;
            ss.Append(dfs);

            TableStyles tss = new TableStyles();
            tss.Count = 0;
            tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
            tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
            ss.Append(tss);

            return ss;
        }

        private static Row CreateHeader(UInt32 index)
        {
            Row r = new Row();
            r.RowIndex = index;
            Cell c = new Cell();
            c.DataType = CellValues.String;
            c.StyleIndex = 5;
            c.CellReference = "A" + index.ToString();
            c.CellValue = new CellValue("REPORT");
            r.Append(c);
            return r;
        }

        private static Row CreateColumnHeader(UInt32 index)
        {
            Row r = new Row();
            r.RowIndex = index;

            Cell c;
            c = new Cell();
            c.DataType = CellValues.String;
            c.StyleIndex = 7;
            c.CellReference = "A" + index.ToString();
            c.CellValue = new CellValue("Retail Customer Group");
            r.Append(c);

            c = new Cell();
            c.DataType = CellValues.String;
            c.StyleIndex = 7;
            c.CellReference = "B" + index.ToString();
            c.CellValue = new CellValue("Product Promotion Group");
            r.Append(c);    
            return r;
        }

        private static Row CreateContent(UInt32 index, ref Random rd)
        {
            Row r = new Row();
            r.RowIndex = index;

            Cell c;
            c = new Cell();
            c.StyleIndex = 7;
            c.CellReference = "A" + index.ToString();
            //c.CellValue = new CellValue(rd.Next(1000000000).ToString());
            c.CellValue = new CellValue("12334");
            r.Append(c);

            DateTime dtEpoch = new DateTime(1900, 1, 1, 0, 0, 0, 0);
            DateTime dt = dtEpoch.AddDays(rd.NextDouble() * 100000.0);
            TimeSpan ts = dt - dtEpoch;
            double fExcelDateTime;
            // Excel has "bug" of treating 29 Feb 1900 as valid
            // 29 Feb 1900 is 59 days after 1 Jan 1900, so just skip to 1 Mar 1900
            if (ts.Days >= 59)
            {
                fExcelDateTime = ts.TotalDays + 2.0;
            }
            else
            {
                fExcelDateTime = ts.TotalDays + 1.0;
            }
            c = new Cell();
            c.StyleIndex = 7;
            c.CellReference = "B" + index.ToString();
            c.CellValue = new CellValue("124515");
            //c.CellValue = new CellValue(fExcelDateTime.ToString());
            r.Append(c);


            return r;
        }
    }
}

and this is the function for button click :

protected void Button1_Click(object sender, EventArgs e)
        {
            string qwe = TextBox1.Text;
            string ert = TextBox2.Text;
            string filename = @"D:\\ExcelOpenXmlWithImageAndStyles.xlsx";
            CreateExcelOpen exp = new CreateExcelOpen();
            exp.BuildWorkbook1(filename);
        }

here are my requirements:

1.I need to pass the values of two textboxes to the two cells(which now contain values 12334 and 124515).

2. The heading column does not adjust itself to accomodate the values which i have provided(Retail customer group etc).Please suggest the modifications required in this class to enable the autofit.

Thank you.

score 1 · Accepted Answer · answered Jun 18 '12 at 11:42

1

In Your CreateContent method you have hard coded values like:

c.CellValue = new CellValue("12334");

change it with values of text boxes that you want to show.

for setting width of headers, you will have to add columns along with Cells as you need columns to define the width, following article will be of some help for you:

http://catalog.codeproject.com/script/Articles/ArticleVersion.aspx?aid=371203&av=543408

answered Jun 18 '12 at 11:42

Imran Balouch

2,170
1
18
37

hi Imran thanks for your reply in the link you provided there is just a mention of setting the column width but no code is provided.I am a bit confused actually as to where to put code for column in my class.Can u please guide me.I am a newbie at this so please bear with me.Thank you – nitinvertigo Jun 18 '12 at 12:46
No worries my friend, we are here to help each other, have a look at this link, I hope it will make every thing clear to you: http://social.msdn.microsoft.com/Forums/sa/oxmlsdk/thread/28aae308-55cb-479f-9b58-d1797ed46a73 If you still find any problem let us know. – Imran Balouch Jun 18 '12 at 12:52
Hi imran thanks the code is working perfectly but i am facing a weird problem(It was there before only).If i enter numbers in textboxes the resultant excel file opens without any problems but if i enter characters it opens with an error message that "excel found unreadable content".The resultant excel file is correct without any problems but i don't want that error message to come.Can u tell me how to avoid it.Thanks for your help. – nitinvertigo Jun 18 '12 at 14:01
Basically in CreateContent method you are using c.StyleIndex = 7; if you check your method CreateStylesheet() the index 7 is for decimal format numbers, if you need to put string in a cell than index 4,5,6 and 8 are defined to use text, so if your data is numeric use c.StyleIndex = 7; otherwise use any of 4,5,6 or 8 indexes. – Imran Balouch Jun 18 '12 at 14:14
Its a very bad issue and once it also tortured me for a time, did you get through it? – Imran Balouch Jun 19 '12 at 06:56
Hello imran,sorry changing the styleindex value didn't affect the error message(the style of the text is changing though).So i am still trying to change the parameters of the indexes.Anyhow thanks for your suggestion. – nitinvertigo Jun 19 '12 at 07:04
This issue is related to styling, as excel is applying a style on the data but the received data is not in that format, so excel shows the message and when we say OK repair it, it removes the style from the cells and put our provided data in the cells. – Imran Balouch Jun 19 '12 at 07:36
ya imran that's what i thought too.Is there a workaround for this? – nitinvertigo Jun 19 '12 at 09:27
the only solution that I know is what I told you already, try one more thing, in your method CreateColumnHeader you are setting column index as 7, try to set it like 4,5,6 or 8. – Imran Balouch Jun 19 '12 at 09:32

score 1 · Answer 2 · answered Apr 10 '14 at 17:39

1

I wrote my own export to Excel writer. It is fast and allows for substantial formatting of the cells. You can review it at

https://openxmlexporttoexcel.codeplex.com/

I hope it helps.

answered Apr 10 '14 at 17:39

Steve

61
6

score 0 · Answer 3 · answered Feb 11 '13 at 23:25

I've spent a lot of time googling OpenXml related questions. I finally found a solution to my problems by combining OpenXml features with Excel VBA. So, I have Excel - macro enabled file (xlsm) as template for my project. Whatever logic is hard to implement in OpenXml I moved to VBA. For AutoFit for example it is as easy as:

Sub Workbook_Open() ActiveSheet.Columns.AutoFit End Sub

Hope it may help.

export to excel in asp.net using openxml

3 Answers3