How to fix error Cannot convert type 'DocumentFormat.OpenXml.UInt32Value' to 'int'

Watch this example on YouTube


Replace
 int t = (int)(r.RowIndex);
With
UInt32 t = (UInt32)(r.RowIndex);

ASP.NET - How to create Excel file with grouping and multiple outline levels - OpenXML

Watch this example on YouTube - step by step



Please refer to previous posts to see how to download OpenXML from Microsoft Website

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.IO.Packaging;

           MemoryStream fs1 = new MemoryStream();
            SpreadsheetDocument xl = SpreadsheetDocument.Create(fs1, 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();
            int i = 1;
            int j = 1;
            for (int x = 1; x < 50; x++)
            {
                Row row1;
                Row row2;
                if (i % 5 == 0)
                {
                    row1 = new Row()
                    {
                        RowIndex = (UInt32)x,
                        Spans = new ListValue<StringValue>() { InnerText = "1:5" },
                        Hidden = false,
                        OutlineLevel = 2,
                        Collapsed = false
                    };
                }
                else
                {
                    row1 = new Row()
                    {
                        RowIndex = (UInt32)x,
                        Spans = new ListValue<StringValue>() { InnerText = "1:5" },
                        OutlineLevel = 3,
                        Collapsed = false,
                        Hidden = true
                    };
                }

                if (j % 10 == 0)
                {
                    x++;
                    row2 = new Row()
                    {
                        RowIndex = (UInt32)x,
                        Spans = new ListValue<StringValue>() { InnerText = "1:10" },
                        OutlineLevel = 1,
                        Hidden = false,
                        Collapsed = false
                    };
                    j = 0;
                    i = 0;
                }
                else
                {
                    row2 = null;
                }

                Cell cell1 = new Cell();
                cell1.DataType = CellValues.String;
                cell1.CellValue = new CellValue("Putin Khu***" + x.ToString().Trim());
                row1.Append(cell1);
                sd.Append(row1);
                if (row2 != null)
                {
                    sd.Append(row2);
                }
                i++;
                j++;
            }
            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "MySheet";
            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.Close();
            string fileName = "test.xlsx";
            Response.Clear();
            byte[] data1 = fs1.ToArray();

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
            Response.BinaryWrite(data1);
            Response.End();

ASP.NET - How to create Excel file with grouping in memory using OpenXml

Watch this example on YouTube


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.IO.Packaging;


            MemoryStream ms = new MemoryStream();
            SpreadsheetDocument sd = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            WorkbookPart wbp = sd.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();
            FileVersion fv = new FileVersion();
            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();

            SheetData sdt = new SheetData();
            for (int i = 1; i < 50; i++)
            {
                Row row1;
                if (i % 5 == 0)
                {
                    row1 = new Row()
                    {
                        //HERE CREATE GROUP
                        RowIndex = (UInt32)i,
                        Spans = new ListValue<StringValue>() { InnerText= "1:5"},
                        Height = 5,
                        DyDescent = 1.50D,
                        Hidden = false,
                        Collapsed = false
                    };
                }
                else
                {
                    row1 = new Row()
                    {
                        RowIndex = (UInt32)i,
                        Spans = new ListValue<StringValue>() { InnerText="1:5"},
                        Height = 5,
                        DyDescent = 1.50D,
                        OutlineLevel = 1,
                        Collapsed = false,
                        Hidden = true
                    };
                }
                Cell cell1 = new Cell();
                cell1.DataType = CellValues.String;
                cell1.CellValue = new CellValue("DataValue " + i.ToString());
                row1.Append(cell1);
                sdt.Append(row1);
            }
            ws.Append(sdt);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();

            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "mySheet";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);

            sd.WorkbookPart.Workbook = wb;
            sd.WorkbookPart.Workbook.Save();
            sd.Close();
            string FileName = "SomeFile.xlsx";

            Response.Clear();
            byte[] dt = ms.ToArray();

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheet.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", FileName));
            Response.BinaryWrite(dt);
            Response.End();

OpenXml - ASP.Net - How to create simple Excel file and add some text to cells - Step by step

Watch this example on YouTube

 here is the code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.IO.Packaging;


           MemoryStream ms = new MemoryStream();
            SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, 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();

           //First cell
            SheetData sd = new SheetData();
            Row r1 = new Row() { RowIndex = (UInt32Value)1u };
            Cell c1 = new Cell();
            c1.DataType = CellValues.String;
            c1.CellValue = new CellValue("some value");
            r1.Append(c1);

           // Second cell
            Cell c2 = new Cell();
            c2.CellReference = "C1";
            c2.DataType = CellValues.String;
            c2.CellValue = new CellValue("other value");
            r1.Append(c2);
            sd.Append(r1);

            //third cell
            Row r2 = new Row() { RowIndex = (UInt32Value)2u };
            Cell c3 = new Cell();
            c3.DataType = CellValues.String;
            c3.CellValue = new CellValue("some string");
            r2.Append(c3);
            sd.Append(r2);

            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "first sheet";
            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.Close();
            string fileName = "testOpenXml.xlsx";
            Response.Clear();
            byte[] dt = ms.ToArray();

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
            Response.BinaryWrite(dt);
            Response.End();

OpenXML - ASP.NET - how to create Excel document with multiple sheets on fly - Step by step

Watch this example on YouTube




Here is ready for copy and paste code:

         MemoryStream ms = new MemoryStream();
            SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            WorkbookPart wbp = xl.AddWorkbookPart();
            wbp.Workbook = new Workbook();
            Sheets sheets = xl.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

           //First Sheet
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Worksheet ws = new Worksheet();
            SheetData sd = new SheetData();
            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheet sheet = new Sheet();
            sheet.Name = "One";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);

            //Second Sheet
            WorksheetPart wsp2 = wbp.AddNewPart<WorksheetPart>();
            Worksheet ws2 = new Worksheet();
            SheetData sd2 = new SheetData();
            ws2.AppendChild(sd2);
            wsp2.Worksheet = ws2;
            wsp2.Worksheet.Save();
            Sheet sheet2 = new Sheet();
            sheet2.Name = "Two";
            sheet2.SheetId = 2;
            sheet2.Id = wbp.GetIdOfPart(wsp2);
            sheets.Append(sheet2);

            xl.Close();
            Response.Clear();
            byte[] someData = ms.ToArray();
            string filename = "myexcel.xlsx";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Dispostion", string.Format("attachment; filename={0}", filename));
            Response.BinaryWrite(someData);
            Response.End();