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
Sunday, 28 December 2014
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)
Round UP - select Round(20.485000,2)
Round DOWN - select Round(20.485000,2,1)
Saturday, 27 December 2014
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
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
Wednesday, 24 December 2014
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
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
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
Tuesday, 23 December 2014
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();
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();
Monday, 22 December 2014
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>
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;
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
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
Saturday, 20 December 2014
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
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
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
Wednesday, 17 December 2014
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:
Tuesday, 16 December 2014
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);
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)
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
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
Monday, 15 December 2014
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
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
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
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
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
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
Thursday, 11 December 2014
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{}
}
}
}
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
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
Tuesday, 28 October 2014
Monday, 27 October 2014
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>
Monday, 20 October 2014
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,
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;
Cannot convert type 'DocumentFormat.OpenXml.UInt32Value' to 'int
Replace
int stIndex = (int)(cellWidth.StyleIndex);
With
UInt32 stIndex = (UInt32)(cellWidth.StyleIndex);
int sIndex = (int)stIndex;
Thursday, 16 October 2014
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'));
Execute the following
SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo.NameOfTheStoredProcedure'));
Tuesday, 14 October 2014
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.
Monday, 13 October 2014
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();">
Sunday, 13 July 2014
Thursday, 26 June 2014
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);
Tuesday, 24 June 2014
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();
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();
Thursday, 19 June 2014
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();
Thursday, 5 June 2014
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();
Monday, 2 June 2014
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();
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();
Wednesday, 28 May 2014
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();
}
Monday, 26 May 2014
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;
Replace
row1.RowIndex = (UInt32Value)i
with
row1.RowIndex =(UInt32)i;
Thursday, 22 May 2014
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
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();
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 :)
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.
Thursday, 15 May 2014
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);
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);
Tuesday, 13 May 2014
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
Subscribe to:
Posts (Atom)