Archive for July, 2011
Write Data From DataTable To Excel With an Array Using C#
Posted by Kelly's Chronicles in C# on July 29, 2011
This is actually a redo of code written in vb.net a while back that dumps data from a datatable in a dataset into an excel spreadsheet in an effective and fast manner. I am always getting asked how to do this in C# so I decided to put that up today. Yes, I do C# too! Just didn’t want to admit it….
It’s pretty self explanatory. Don’t forget to clean up your excel instance when done. If you have any questions please feel free to send me an email.
Import an excel reference and at the top of your code you need your import statements. Then simply pass your dataset and datatable (granted you could just pass the datatable – I had my reasons for passing the dataset too at the time) to the method. The array is declared as an object because sometimes the compiler has to deal with unforeseen data issues that might arise and it has been effective for me to do let it handle those situations as they arise. I hope you find this useful.
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
public void CreateSpreadSheetFromDataSet(DataSet ds, DataTable dt)
{
Excel.Application Excel = new Excel.Application();
Excel.Visible = true;
Excel.Worksheet WSheet = new Excel.Worksheet();
Excel.Workbooks.Add();
WSheet = Excel.ActiveWorkbook.ActiveSheet;
int rows = ds.Tables[dt.TableName].Rows.Count;
int columns = ds.Tables[dt.TableName].Columns.Count;
int r = 0; int c = 0;
object[,] DataArray = new object[rows + 1, columns + 1];
for (c = 0; c <= columns – 1; c++)
{
DataArray[r, c] = ds.Tables[dt.TableName].Columns[c].ColumnName;
for (r = 0; r <= rows – 1; r++)
{
DataArray[r, c] = ds.Tables[dt.TableName].Rows[r][c];
} //end row loop
} //end column loop
//actually write array to Excel Spreadsheet
WSheet.Range[“A2”].Resize[rows, columns].Value = DataArray;
//write header row to spreadsheet
int DataTableColumnCounter;
int ExcelColumnCounter = 1; //excel spreadsheets start at 1 when counting columns not zero!
for (DataTableColumnCounter = 0; DataTableColumnCounter <= ds.Tables[dt.TableName].Columns.Count – 1; DataTableColumnCounter++)
{
WSheet.Cells[1, ExcelColumnCounter].Value = ds.Tables[dt.TableName].Columns[DataTableColumnCounter].ColumnName;
ExcelColumnCounter = ExcelColumnCounter + 1; //moving to next column
}
}
Create a Share on Remote Machine using WMI with vb.net
Posted by Kelly's Chronicles in .NET on July 28, 2011
This code was used to create a share on a remote machine a while back for the purpose of automating a function that had previously required a network guy at the company to actually touch the machine which of course they didn’t like to do (it required work on their part). But it was ok. I didn’t mind as it wasn’t that hard. WMI makes it easy to do.
I have been involved with a series of technical discussions with some folks at various companies lately. My most embarrassing moment yesterday was when I stumbled on my words when asked about polymorphism. I actually used the words “parallel universe” in conjunction with this! Can you imagine? I got to think better on my feet. But its all good. Have a great day!
Public Sub CreateShare(ByVal strShareName As String, ByVal strPath As String, ByVal computername As String, ByVal shareinfo As String)
Try
Dim objSWbemServices As Object
Dim objSWbemObject As Object
Dim colSWbemObject As Object
Dim intRet As Integer
Dim blnExists As Boolean
Dim objSWbem As Object
objSWbemServices = GetObject(“winmgmts://” + computername + “/root/cimv2”)
colSWbemObject = objSWbemServices.InstancesOf(“Win32_Share”)
For Each objSWbem In colSWbemObject
If (objSWbem.name = strShareName) Then
blnExists = True
Exit For
Else
blnExists = False
End If
Next
If (blnExists = False) Then
objSWbemObject = objSWbemServices.Get(“Win32_Share”)
intRet = objSWbemObject.Create(strPath, strShareName, 0, 25, shareinfo)
System.Console.WriteLine(“Share has been created on : ” + computername)
System.Console.WriteLine(“ShareName : ” + ShareName + ” SharePath : ” + SharePath + ” ShareInfo : ” + shareinfo)
Else
System.Console.WriteLine(“Share is already present on computer : ” + computername)
System.Console.WriteLine(“ShareName : ” + ShareName + ” SharePath : ” + SharePath + ” ShareInfo : ” + shareinfo)
End If
Catch ex As Exception
System.Console.Write(“Error occured while trying to create shares on remote pc : ” + computername + vbCrLf + “Check if you have the necessary rights and/or that the pc is turned on.” + vbCrLf + ex.ToString)
System.Console.WriteLine(“ShareName : ” + ShareName + ” SharePath : ” + SharePath + ” ShareInfo : ” + shareinfo)
End Try
End Sub
Recent Comments