OpenXml - How to create Excle file with formula cell - step by step

Watch this example on youtube:



Just copy and paste - to see how to create Excel file with formula in memory using ASP.NET:

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



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

            Row r = new Row();
            r.RowIndex = (UInt32)1;
            Cell c = new Cell() { CellReference = "A1" };
            c.DataType = CellValues.Number;
            c.CellValue = new CellValue("10");
            r.Append(c);

            Cell c2 = new Cell() { CellReference = "B1" };
            c2.DataType = CellValues.Number;
            c2.CellValue = new CellValue("30");
            r.Append(c2);

            Cell cFormula = new Cell() { CellReference = "C1" };
            CellFormula cf = new CellFormula();
            cf.Text = "SUM(A1:B1)";
            cFormula.Append(cf);
            r.Append(cFormula);
            sd.Append(r);
            ws.Append(sd);

            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "some name";
            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();

            Response.Clear();
            byte[] dt = fs.ToArray();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=fileName.xlsx"));
            Response.BinaryWrite(dt);
            Response.End();
        }

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

Cannot convert type int to DocumentFormat.OpenXml.UInt32Value

Replace
row1.RowIndex = (UInt32Value)i
with
row1.RowIndex =(UInt32)i;

How to fix error message Exception message: Could not load file or assembly 'DocumentFormat.OpenXml,

    Exception message: Could not load file or assembly 'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

Add the following file to bin folder
DocumentFormat.OpenXml.dll

How to fix error - The type or namespace name Package could not be found (are you missing a using directive or an assembly reference?)

Watch this example on YouTube:


add

using System.IO.Packaging;

and now you can execute code like:
            MemoryStream l_memStrm = new MemoryStream(512);

            Package l_memPack = Package.Open(l_memStrm, FileMode.Create, FileAccess.ReadWrite);
            SpreadsheetDocument l_package = SpreadsheetDocument.Create(l_memPack, SpreadsheetDocumentType.Workbook);
            var workbookPart = l_package.AddWorkbookPart();

How to fix error - Could not load file or assembly 'DocumentFormat.OpenXml' or one of its dependencies.

Error Message:
Could not load file or assembly 'DocumentFormat.OpenXml' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.






In my case I installed OpenXml version 2.5 on Visual Studio 2008.  To fix it:
install older version that supports .net3.5 (or upgrade visual studio to newer version :)

ASP.NET - How to create Excel file in memory (on the fly)

Watch this example on YouTube:

1. First download (and inatall) OpenXml from http://www.microsoft.com/en-ca/download/details.aspx?id=30425
2. Add references
 - WindowsBase
 - DocumentFormat.OpenXml

3. Add new webform
4. Add button to the form
5. Add the following using
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;

6. Add the following on click method:

protected void Unnamed1_Click(object sender, EventArgs e)
        {
            MemoryStream ms = new MemoryStream();
            SpreadsheetDocument sd = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            WorkbookPart wbp = sd.AddWorkbookPart();
            wbp.Workbook = new Workbook();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            wsp.Worksheet = new Worksheet(new SheetData());
            Sheets st = sd.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            Sheet sts = new Sheet()
            {
                Id = sd.WorkbookPart.GetIdOfPart(wsp),
                SheetId = 1,
                Name = "anyName"
            };
            st.Append(sts);
            wbp.Workbook.Save();
            sd.Close();
            string strFileName = "TestName.xlsx";

            Response.Clear();
            byte[] dataByte = ms.ToArray();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filenale={0}", strFileName));
            Response.BinaryWrite(dataByte);
            Response.End();
        }

7. That's it, now when user clicks the button - program will create xmls file and will provide user with options to open it or save it to local computer.

ASP.NET - How to create TreeView with checkboxes programmatically

Watch this example on YouTube:
            TreeView tv = new TreeView();
            tv.ID = "TV1";
            tv.CollapseAll();
            tv.ExpandAll();
            tv.ShowCheckBoxes = TreeNodeTypes.All;  //ADD CHECKBOXES

            TreeNode tn = new TreeNode();
            tn.Text = "Node1";

            TreeNode tn2 = new TreeNode();
            tn2.Text = "Node2";

            TreeNode tnGrand = new TreeNode();
            tnGrand.Text = "grandchild";
            tn2.ChildNodes.Add(tnGrand);

            tv.Nodes.Add(tn);
            tv.Nodes.Add(tn2);
            form1.Controls.Add(tv);

ASP.NET - How to cretae TreeView programmatically

- Create TreeView programmatically, add nodes, etc

Watch this example on YouTube



            TreeView tv = new TreeView();
            tv.ID = "treeID";
            tv.ExpandDepth = 1;
            tv.ExpandAll();

            TreeNode tn = new TreeNode();
            tn.Text = "Node";
            tn.Value = "n1";
            tn.Expanded = true;

            TreeNode tnChild1 = new TreeNode();
            tnChild1.Text = "Child";
            tn.ChildNodes.Add(tnChild1);

            TreeNode tnChild2 = new TreeNode();
            tnChild2.Text = "Child2";
            tn.ChildNodes.Add(tnChild2);

            TreeNode tnGrandChild = new TreeNode();
            tnGrandChild.Text = "GrandChild";
            tnChild2.ChildNodes.Add(tnGrandChild);

            tv.Nodes.Add(tn);
            form1.Controls.Add(tv);

Samsung Galaxy - How to add new language to keyboard and how to switch between languages

Watch this example on YouTube

1. go to settings  ( you can also select settings icon on your keyboard)

2. select My device, than select Language and input

3. select Samsung keyboard

4. select input languages

5.  Select desired language (it might take a while to download and install it)

6.Now open any program that is using keyboard (for instance browser)
   Now you have an option to switch between languages

7. I selected Ukrainian - note that keyboard changed

VB.NET 2008 - How to add Global.asax file in simple 5 steps

1. Add Global.asax

2. Add new class, name it Global.asax.vb and follow instructions (place it in App_Code folder)


3. Modify Global.asax add the following <%@ Application Language="VB" CodeBehind="~/App_Code/Global.asax.vb" Inherits="Global" %>

4. Modify Global.asax.vb add the following Inherits System.Web.HttpApplication, change name to [Global]

5. Add Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) to Global.asax.vb

.NET - How to fix error Message 'Sys.WebForms.PageRequestManager' is null or not an object

Error message: Message 'Sys.WebForms.PageRequestManager' is null
or
 not an object or 0x800a138f - JavaScript runtime error: Unable to get property 'PageRequestManager' of undefined or null reference


watch on YouTube:
while trying to execute the following code: Sys.WebForms.PageRequestManager.getInstance().add_beginRequest(doSomething);

To fix it:

 In web.config replace  
   <system.web>
    <xhtmlConformance mode="Legacy" />
with 
  <system.web>
    <xhtmlConformance mode="Transitional" />