C# - How to fix error - 'ConfigurationManager' does not exist in the current context

Watch this example on YouTube

Error when executing the following code:
    string ConfigValue = ConfigurationManager.AppSettings["MyKey"];
            MessageBox.Show(ConfigValue);

App.config looks like this:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <appSettings>
      <add key="MyKey" value="MyValue"/>
    </appSettings>
</configuration>

To fix it:
add new reference - system.configuration.dll
add new "using" - using System.Configuration

MSSQL - Example how to round number down and up

Watch this example on YouTube




Round UP - select Round(20.485000,2)

Round DOWN - select Round(20.485000,2,1)

Excel - Macro - MSSQL - Simple CRUD example

Watch this example on YouTube

Sub Test()
    Dim myConnection As ADODB.Connection
    Dim myCommand As ADODB.Command
    Set myConnection = New ADODB.Connection
   
    myConnection.ConnectionString = "Driver={SQL Server native Client 11.0}; Server=(LocalDB)\v11.0;AttachDBFileName=C:\Users\livingroomadmin\TEST.mdf;Trusted_Connection=Yes;"
    myConnection.ConnectionTimeout = 15
    myConnection.Open
   
    Set myCommand = New ADODB.Command
    myCommand.ActiveConnection = myConnection
   
    qCreate = "Create Table TestTable " & _
      "(ID int, " & _
      " Name nVarchar(20) Null, " & _
      " TransDate datetime Null)"
    myCommand.CommandText = qCreate
    myCommand.Execute
   
    qInsert = "Insert Into TestTable Values (1, 'Frank', '1/1/2015')"
    myCommand.CommandText = qInsert
    myCommand.Execute
   
    sUpdate = "Update TestTable " & _
      " Set Name = 'Bob', TransDate = '1/1/2016' Where ID = 1"
    myCommand.CommandText = sUpdate
    myCommand.Execute
   
    sDrop = "Drop Table TestTable"
    myCommand.CommandText = sDrop
    myCommand.Execute
      
End Sub

Excel - Macro - MS Access - Simple CRUD example

Watch this example on YouTube

Sub connectToDB()
    Dim myConnection As ADODB.Connection
    Dim myCommand As ADODB.Command
   
    Set myConnection = New ADODB.Connection
   
    myConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\ExcelTest\TestDB.accdb;Jet OLEDB:Engine Type=5; Persist Security Info=False;"
    myConnection.ConnectionTimeout = 30
    myConnection.Open
   
    Set myCommand = New ADODB.Command
    myCommand.ActiveConnection = myConnection
   
    strSqlCreate = "CREATE TABLE MyNewTest " & _
    " (ID number, " & _
    " Name text Null, " & _
    " DateName text Null)"
    myCommand.CommandText = strSqlCreate
    myCommand.Execute
   
    strInsertCommand = "INSERT INTO MyNewTest Values(1, 'Frank', '1/1/2014')"
    myCommand.CommandText = strInsertCommand
    myCommand.Execute
   
    strUpdateCommand = "UPDATE MyNewTest Set Name = 'Bob', DateName = '1/1/2015' where ID = 1"
    myCommand.CommandText = strUpdateCommand
    myCommand.Execute
   
    strDeleteCommand = "Delete from MyNewTest"
    myCommand.CommandText = strDeleteCommand
    myCommand.Execute
   
    strDropCommand = "Drop Table MyNewTest"
    myCommand.CommandText = strDropCommand
    myCommand.Execute
   
 End Sub

Excel - Macro - How to fix error: Compile error: User-defined type not defined

Watch this example on YouTube

I am getting this error while executing the following code:
Sub test()
    Dim con As ADODB.Connection
End Sub
To fix it
go to tools
go to References
select Microsoft ActiveX Data Object 2.8(or other) library

How to fix error An object reference is required for the non-static field, method, or property 'System.DateTime.ToShortTimeString()'

Watch this example on YouTube

 To fix the error:
An object reference is required for the non-static field, method, or property 'System.DateTime.ToShortTimeString()'  

Replace
   string d = DateTime.ToShortDateString();
   string t = DateTime.ToShortTimeString();
With
  string d = DateTime.Today.ToShortDateString();
  string t = DateTime.Now.ToShortTimeString();

How to fix error: Control 'ToolkitScriptManager1' of type 'ToolkitScriptManager' must be placed inside a form tag with runat=server.

watch this example on YouTube

How to fix error:
Control 'ToolkitScriptManager1' of type 'ToolkitScriptManager' must be placed inside a form tag with runat=server.

Replace
         <asp:ToolkitScriptManager ID="ToolkitScriptManager2" runat="server">
        </asp:ToolkitScriptManager>
    <form id="form1" runat="server">
    <div>

with
    <form id="form1" runat="server">
    <div>
         <asp:ToolkitScriptManager ID="ToolkitScriptManager2" runat="server">
        </asp:ToolkitScriptManager>

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

Watch this example on YouTube:

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

Microsfot Excel - Macro - How to delete duplicate rows

Watch this example on YouTube

Sub DeleteDuplicated()
    Dim Rng As Range
    Dim ColumnCounter As Integer
   
    Set Rng = ActiveSheet.UsedRange.Rows
   
    For r = Rng.Rows.Count To 1 Step -1
        ColumnCounter = 0
        For Col = Rng.Columns.Count To 1 Step -1
            If Application.WorksheetFunction.CountIf(Rng.Columns(Col), Rng.Cells(r, Col)) > 1 Then
                ColumnCounter = ColumnCounter + 1
            End If
        Next Col
       
        If ColumnCounter = Rng.Columns.Count Then
            Rng.Rows(r).EntireRow.Delete
        End If
    Next r
End Sub

Microsfot Excel - Macro - How to make file read only

Watch this example on YouTube

The following code will make file read only

Sub TestFile()
    Dim FileName As String
    Dim FileObject As Object
    Dim FileSystem As Object
   
    FileName = "C:\Temp\ExcelTest\ReadOnly.txt"
    Set FileObject = CreateObject("Scripting.FileSystemObject")
    Set FileSystem = FileObject.GetFile(FilePath:=FileName)
    FileSystem.Attributes = 1
  
End Sub

Microsfot Excel - Macro - How to delete file

Watch this example on YouTube

Simple example that shows how to delete file using vba

The following code will delete file

Sub DeleteFile()
    Dim FileSystem
    Dim FileName As String
    FileName = "C:\Temp\ExcelTest\DelteMe.txt"
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    If FileSystem.FileExists(FileName) Then
        FileSystem.DeleteFile FileName, True
    Else
        MsgBox "Display Error", vbInformation, "Error"
    End If
End Sub

Microsfot Excel - Macro - How to create Pivot Table

Watch this example on YouTube

Here is my data in columns A, B, C















 The following macro
Sub test()
    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
   
    Set ws = Worksheets.Add
    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Sheet1!R1C1:R7C3")
    Set pt = pc.CreatePivotTable(ws.Range("A2"))
   
    With pt
        With .PivotFields("Name")
            .Orientation = xlRowField
            .Position = 1
        End With
       
        .AddDataField .PivotFields("Salary"), "Salary Total", xlSum
    End With
End Sub

will create this pivot table:

How to fix error - The type initializer for 'System.Data.SqlClient.SqlConnection' threw and exception

Ensure app.config is properly formatted.
In my case - I had to comment out the following
<configSections>
</configSections>

ASP.NET - how to open mail program on client computer

Watch this example on YouTube

The following code will open mail program on client's computer and will populate email, subject and body with some values:
   Page.ClientScript.RegisterStartupScript(this.GetType(), "some name",
                "parent.location='mailto:name@name.com?Subject=some subject&body=Line One%Line Two'", true);

Microsfot Excel - Macro - Simple example - How to create Pivot Table

Watch this example on YouTube

Example uses macro recorder

Here is my data (column A,B,C)

Name	Month	Salary
Frank	Jan	20
Frank	Feb	22
Frank	Mar	33
Bob	Jan	30
Bob	Feb	35
Bob	Mar	35

and here is my simple macro:

 Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:C7").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R7C3", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet5!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Salary"), "Sum of Salary", xlSum
End Sub

Microsfot Excel - Macro - For loop and If statement example

Watch this example on YouTube


Sub test()
    Range("A1:C4").Select
    Dim selectedCell As Range
    For Each selectedCell In Selection
        If selectedCell.Value = "frank" Then
            selectedCell.Interior.Color = vbRed
        End If
    Next
End Sub

Microsfot Excel - Macro - How to display popup message in message box

Watch this example on YouTube

Sub Test()
   MsgBox "This is my message"
End Sub

Microsfot Excel - Macro - How to change multiple column width

Watch this example on YouTube


Sub test()
  Columns("D:F").ColumnWidth = 40
End Sub

Microsfot Excel - Macro - How to change column width

Watch this example on YouTube

Sub Test()
   Columns(2).ColumnWidth = 20
   Columns("C").ColumnWidth = 30
End Sub

Microsfot Excel - Macro - How to change height of multiple rows in one statment

Watch this example on YouTube:

Sub test()
  Rows("4:9").RowHeight = 50
End Sub

Microsoft Excel - Macro - How to change row height

Watch this example on YouTube


Sub test()
    Rows(3).RowHeight = 100
End Sub

Microsoft Excel - Macro that sends httpWebRequest

watch this example on YouTube


Here is the macro 
Sub RunHttpRequest()
  Dim req As Object
  Set req = CreateObject("WinHttp.WinHttpRequest.5.1")
  req.Option(0) = "Test"
  req.Open "GET", "http://localhost:64391/HttpWebResponseTest.aspx"
  req.Send
  MsgBox req.ResponseText
 
End Sub


as on video - first let's create simple web page that returns web response
this is C# code

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

namespace TestingWeb
{
    public partial class HttpWebResponseTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!IsPostBack)
                {
                    switch (Request.UserAgent.ToString().Trim())
                    {
                        case "Test":
                            {
                                Response.Write("Message from external page");
                                break;
                            }
                        default:
                            {
                                Response.Write("not valid request");
                                break;
                            }
                    }
                }
               
            }
            catch{}
        }
    }
}

Microsoft Excel Macro that displays progressing progress in application bar

Watch this example on YouTube



Sub Workbook_Open()
    For i = 1 To 100000
        Cells(i, 1) = "Some text"
        Application.StatusBar = "Processing " & Round((i / 100000 * 100), 0) & "%"
    Next

End Sub

Microsoft Excel - Macro that opens Internet Explorer and specified URL

Watch this example on YouTube:


ALT+F11 to open Macro window

Sub ShowBrowser()
  Dim IE As Object
 
  Set IE = CreateObject("InternetExplorer.Application")
  IE.Navigate "http://www.pravda.com.ua"
  IE.Visible = True
  Set IE = Nothing
End Sub

Такі красиві люди - трейлер

Error Microsoft JScript runtime error: Object expected

Watch on YouTube:

Error Microsoft JScript runtime error Object expected

Replace
<script src="<%# ResolveUrl("Scripts/jquery-1.11.0.js") %>" type="text/javascript"></script>

With
<script src="Scripts/jquery-1.11.0.js" type="text/javascript"></script>

Samsung Galaxy How to copy and paste text

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

 Cannot convert type int to DocumentFormat.OpenXML.UInt32Value


Watch this example on YouTube

Replace
RowIndex = (UInt32Value)i,
with
RowIndex = (UInt32)i,

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

Error
Cannot convert type 'DocumentFormat.OpenXml.UInt32Value' to 'int

Replace
int stIndex = (int)(cellWidth.StyleIndex);
With
UInt32 stIndex = (UInt32)(cellWidth.StyleIndex);
int sIndex = (int)stIndex;

MS SQL - Command to show code of stored procedure

Watch this example on YouTube
Execute the following
SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo.NameOfTheStoredProcedure'));

How to fix error Operation is not valid due to the current state of the object.

Watch solution on YouTube

or add the following code to web.config:
<appSettings>
    <add key="aspnet:MaxHttpCollectionKeys" value="10000"/>
  </appSettings>

default value is 1000.

ASP.Net - GridView paging and sorting validation issues

In case you are receiving the following error while paging or sorting gridview

A potentially dangerous Request.Form value was detected from the client

in event viewer
Event code: 3003
Event message: A validation error has occurred.  

Watch this on YouTube:

add the following javascript function
    <script type="text/javascript">
        function validate(){
            var result = Page_ClientValidate();
            return result;
        }
    </script>

and modify form tag, from
    <form id="form1" runat="server">
to 
    <form id="form1" runat="server" onsubmit="return validate();">

Watching Mundial 2014 in Eaton Center Toronto

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();

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