Posts Tagged Application
Hello all. I had a production manager wanting an excel spreadsheet mailed as the body of the email. As some of you know the code generated by excel to produce the email is pretty crazy. But as a result, it showed up fine in Outlook and Android but it did not show the gridlines on the spreadsheet. So this code is based on the excellent work by Ron DeBruin over at http://www.rondebruin.nl/win/s1/outlook/bmail3.htm . I did a replacement for the HTML Range in this manner and the grid lines did appear. And the manager was happy.
Sub Mail_Selection_Range_Outlook_Body()
‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
‘Don’t forget to copy the function RangetoHTML in the module.
‘Working in Excel 2000-2016
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
‘MsgBox Cells(5, 9).Value
Set rng = Nothing
On Error Resume Next
‘Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
‘You can also use a fixed range if you want
‘Set rng = Sheets(“YourSheet”).Range(“D4:D12”).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox “The selection is not a range or the sheet is protected” & _
vbNewLine & “please correct and try again.”, vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.BodyFormat = olFormatHTML
.To = “you@you.com”
.CC = “”
.BCC = “”
.Subject = “Testing Purchase Order Email To Steve”
.HTMLBody = RangetoHTML(rng)
Replace .HTMLBody, “border-left:none”, “border-left:solid;border-width: 1px;border-color:black”
Replace .HTMLBody, “border-right:none”, “border-right:solid;border-width: 1px;border-color:black”
Replace .HTMLBody, “border-bottom:none”, “border-bottom:solid;border-width: 1px;border-color:black”
Replace .HTMLBody, “border-top:none”, “border-bottom:solid;border-width: 1px;border-color:black”
.Send
‘or use .Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Android, Application, Body, BodyFormat, Cells, CreateItem, CreateObject, DeBruin, Display, Email, EnableEvents, error, Excel, Exit, False, GoTo, Grid, Hello, HTML, HTMLBody, Issues, Mail_Selection_Range_Outlook_Body, manager, manner, module, MsgBox, Object, Order, OutApp, Outlook, OutMail, production, Purchase, Range, RangetoHTML, Replace, Resume, rondebruin, Selection, Send, sheet, Sheets, SpecialCells, spreadsheet, Steve, Subject, Tips, True, Value, Width, xlCellTypeVisible, YourSheet
What’s this you say? A new blog entry? If you haven’t completely deserted me …. well I don’t know what’s wrong with you! jk. But anyway here is the latest. I am working for a national hotel chain in Memphis, TN. I got married, moved to New Jersey and am we are now separated. Yes it is a long story and no I am not going to bore you with the details!
Before I go any further, I actually write very little in vb.net anymore. The language is awesome. It’s just that very few people want that skill set now and if you want to be employed you need to write in C#. It’s just the way it is. However in this particular task, I found vb.net to be the tool of choice here. The reason is there is no C# equivalent to VB’s implicit ‘once only’ variable initialization within loops. I am not sure why that is but that is the case here. This code snippet is ideal when you want to allow the end user to dictate how a given control is to look or operate. Yes in my example I am using an Infragistic control so if you copy and paste this do NOT email me complaining about broken references please! I hope you find the code useful and thank you for showing up!
Public Shared Sub SaveControl(ByVal StateName As String, ByVal ParamArray Controls As Control())
Dim section As String = “”
Dim appName As String = StringType.FromObject(Interaction.IIf((StringType.StrCmp(Application.ProductName, “”, False) = 0), “VSDesigner”, Application.ProductName))
Dim control As Control
For Each control In Controls
If (Not control.FindForm Is Nothing) Then
Dim form As Form
If (StringType.StrCmp((StateName & “”), “”, False) > 0) Then
section = String.Concat(New String() { control.FindForm.Name, “.”, control.Name, “.”, StateName })
Else
section = (control.FindForm.Name & “.” & control.Name)
End If
If TypeOf control Is Form Then
form = DirectCast(control, Form)
If (form.WindowState = FormWindowState.Normal) Then
Interaction.SaveSetting(appName, form.Name, “Top”, StringType.FromInteger(form.Top))
Interaction.SaveSetting(appName, form.Name, “Left”, StringType.FromInteger(form.Left))
Interaction.SaveSetting(appName, form.Name, “Height”, StringType.FromInteger(form.Height))
Interaction.SaveSetting(appName, form.Name, “Width”, StringType.FromInteger(form.Width))
End If
Interaction.SaveSetting(appName, form.Name, “WindowState”, StringType.FromInteger(CInt(form.WindowState)))
ElseIf TypeOf control Is Splitter Then
form = control.FindForm
Dim splitter As Splitter = DirectCast(control, Splitter)
Interaction.SaveSetting(appName, section, “SplitPosition”, StringType.FromInteger(splitter.SplitPosition))
ElseIf TypeOf control Is FilterListView Then
Dim view2 As FilterListView = DirectCast(control, FilterListView)
form = view2.FindForm
Interaction.SaveSetting(appName, section, “View”, StringType.FromInteger(CInt(view2.ListView.View)))
Dim header As ColumnHeader
For Each header In view2.ListView.Columns
Interaction.SaveSetting(appName, section, (“Column” & StringType.FromInteger(header.Index)), StringType.FromInteger(header.Width))
Next
Dim filter As FilterListViewFilter
For Each filter In view2.Filters
Dim tag As UserProfileSetting
If (filter.Tag Is Nothing) Then
tag = Context.User.Settings.NewItem
Dim setting2 As UserProfileSetting = tag
setting2.Type = UserProfileSettingTypeEnum.FilterListView
setting2.Name = section
setting2.Description = filter.Name
setting2.Value = filter.Filter
setting2 = Nothing
filter.Tag = tag
ElseIf filter.Deleted Then
tag = DirectCast(filter.Tag, UserProfileSetting)
If Context.User.Settings.Contains(tag) Then
Context.User.Settings.Remove(tag)
End If
Else
tag = DirectCast(filter.Tag, UserProfileSetting)
tag.Value = filter.Filter
End If
Next
Context.User.Settings.Save
ElseIf (TypeOf control Is ListView Or TypeOf control Is SortListView) Then
form = control.FindForm
Dim view As ListView = DirectCast(control, ListView)
Interaction.SaveSetting(appName, section, “View”, StringType.FromInteger(CInt(view.View)))
Dim header As ColumnHeader
For Each header In view.Columns
Interaction.SaveSetting(appName, section, (“Column” & StringType.FromInteger(header.Index)), StringType.FromInteger(header.Width))
Next
ElseIf TypeOf control Is TabControl Then
form = control.FindForm
Dim control2 As TabControl = DirectCast(control, TabControl)
Interaction.SaveSetting(appName, section, “View”, StringType.FromInteger(control2.SelectedIndex))
ElseIf TypeOf control Is TextBox Then
form = control.FindForm
Dim box3 As TextBox = DirectCast(control, TextBox)
Interaction.SaveSetting(appName, section, “Text”, box3.Text)
ElseIf TypeOf control Is ComboBox Then
form = control.FindForm
Dim box As ComboBox = DirectCast(control, ComboBox)
If (box.DropDownStyle = ComboBoxStyle.DropDown) Then
Interaction.SaveSetting(appName, section, “Text”, box.Text)
End If
Interaction.SaveSetting(appName, section, “SelectedIndex”, StringType.FromInteger(box.SelectedIndex))
ElseIf TypeOf control Is CheckBox Then
form = control.FindForm
Dim box2 As CheckBox = DirectCast(control, CheckBox)
Interaction.SaveSetting(appName, section, “Checked”, StringType.FromBoolean(box2.Checked))
ElseIf TypeOf control Is DataGrid Then
form = control.FindForm
Dim grid As DataGrid = DirectCast(control, DataGrid)
Dim num As Integer = 0
Dim style As DataGridTableStyle
For Each style In grid.TableStyles
Dim num2 As Integer = 0
Dim style2 As DataGridColumnStyle
For Each style2 In style.GridColumnStyles
Interaction.SaveSetting(appName, (section & StringType.FromInteger(num) & “.” & StringType.FromInteger(num2)), “Width”, StringType.FromInteger(style2.Width))
num2 += 1
Next
num += 1
Next
ElseIf TypeOf control Is KeyedDropDown Then
form = control.FindForm
Dim down As KeyedDropDown = DirectCast(control, KeyedDropDown)
If (down.DropDownStyle = ComboBoxStyle.DropDown) Then
Interaction.SaveSetting(appName, section, “Text”, down.Text)
End If
Interaction.SaveSetting(appName, section, “SelectedIndex”, StringType.FromInteger(down.SelectedIndex))
ElseIf TypeOf control Is UltraGrid Then
Dim grid2 As UltraGrid = DirectCast(control, UltraGrid)
Dim enumerator2 As BandEnumerator = grid2.DisplayLayout.Bands.GetEnumerator
Do While enumerator2.MoveNext
Dim current As UltraGridBand = enumerator2.Current
Dim enumerator As ColumnEnumerator = current.Columns.GetEnumerator
Do While enumerator.MoveNext
Dim column As UltraGridColumn = enumerator.Current
Interaction.SaveSetting(appName, section, (“Hidden.” & StringType.FromInteger(current.Index) & “.” & column.Key), StringType.FromBoolean(column.Hidden))
Interaction.SaveSetting(appName, section, (“Width.” & StringType.FromInteger(current.Index) & “.” & column.Key), StringType.FromInteger(column.Width))
Loop
Loop
End If
End If
Next
End Sub
Facebook
Application, appName, BandEnumerator, Bands, c#, CheckBox, CInt, column, ColumnEnumerator, ColumnHeader, columns, ComboBox, ComboBoxStyle, Concat, Contains, Context, Control, Controls, Current, DataGrid, DataGridColumnStyle, DataGridTableStyle, Description, DirectCast, DisplayLayout, DropDown, DropDownStyle, Else, ElseIf, enumerator, example, False, Filter, FilterListView, FilterListViewFilter, Filters, FindForm, Form, FormWindowState, FromBoolean, FromInteger, FromObject, GetEnumerator, Grid, GridColumnStyles, haven, header, Hidden, hotel, index, Infragistics, Integer, Interaction, KeyedDropDown, Left, ListView, Loop, Memphis, MoveNext, Name, New Jersey, NewItem, Normal, ParamArray, ProductName, Public, references, Remove, Save, SaveControl, SelectedIndex, Settings, skill, snippet, SortListView, SplitPosition, Splitter, StateName, StrCmp, StringType, TabControl, TableStyles, task, Text, TextBox, tool, Type, TypeOf, UltraGrid, UltraGridBand, UltraGridColumn, user, UserProfileSettingTypeEnum, Value, vb.net, View, VSDesigner, Width, WindowState
I apologize that it has been a while. I have been extremely busy and in the time that I have been away I went through treatment for a medical issue. Needless to say I had other issues to deal with other than this blog. But I will try to publish more frequently. Today’s topic was a question from another developer which was to have have a Linq and IList sort dynamically. So long as you don’t force execution by using ToList, you can keep tacking on to your query until you are ready to actually run it. This is because much of LINQ uses “deferred execution”. For example, you could do something similar to:
var query = apps.AsQueryable()
.Select
(Application => new
{
ID = Application.ApplicationId,
Force = Application.Force.Description,
Function = Application.BusinessFunction.Description,
Category = Application.Category.Description,
SubCategory = Application.SubCategory.Description,
Name = Application.ApplicationName,
Description = Application.ApplicationDescription
}
).Take(10);
switch (sortType)
{
case “ID”:
query = query.Orderby(Application => Application.ApplicationId);
break;
case “Force”:
query = query.Orderby(Application => Application.Force);
break;
}
IList tempList = query.ToList();
As always it has been great getting your all feedback while I was gone. Thank You!
.NET, Application, ApplicationDescription, ApplicationId, ApplicationName, AsQueryable, BusinessFunction, c#, cancer, Category, Description, example, execution, feedback, Force, FUNCTION, IList, Linq, Name, Orderby, Runtime, Select, SubCategory, Take, ToList, topic
This weekend found myself knee deep in a crisis that my friend who had migrated from SharePoint 2003 to 2007 (there were reasons he couldn’t go to 2010). Simply put the migration from SharePoint 2003 to 2007 had broken his application (tracking program that submitted data to a 2003 SharePoint List) because in SharePoint 2007 you can’t do this while not on the actual server if you have “Web Page Security Validation” enabled. So for the code below you have to have SPWeb.AllowUnsafeUpdates = true; . Obviously not the ideal solution for my friend but we didn’t have time to screw around. Here is what I did….
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Xml;
using System.ServiceModel;
using System.Net;
namespace Trigger_Tracker
{
public partial class Form1 : Form
{//form move on click and drag
bool FormMoving;
Point initialPoint;
TriggerTrackerPictureBox frmPicture;
public Form1()
{//form move on click and drag
InitializeComponent();
comboBox1.SelectedIndex = 0;
FormMoving = false;
frmPicture = new TriggerTrackerPictureBox();
frmPicture.localForm = this;
frmPicture.Owner = this;
frmPicture.Show();
frmPicture.Width = 68;
frmPicture.Height = 65;
SetPositionOfPictureForm();
}
private void SetPositionOfPictureForm()
{
frmPicture.Top = this.Top + 26;
frmPicture.Left = this.Left + 87;
}
private void TrackerButton(object sender, EventArgs e)
{
string listGUID = “A93E1A7E-67D0-4D7D-A4ED-803D7DFE684B”;
string viewGUID = “6B2F3EF2-4B0C-41E1-B87E-0C3185B587DD”;
//string viewGUID2 = “6B2F3EF2-4B0C-41E1-B87E-0C3185B587DD”;
int ItemCounter = 1;
ServiceList.Lists listService = new ServiceList.Lists();
// RetentionLists.ListsSoapClient listService = new RetentionLists.ListsSoapClient();
//////
listService.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
//listService.ChannelFactory.Credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
XmlNode activeItemData = listService.GetListItems(listGUID, viewGUID, null, null, “100”, null);
XmlDocument xDoc = new XmlDocument();
string tmpString = activeItemData.InnerXml.Replace(“\r\r”, “”);
xDoc.LoadXml(tmpString);
XmlNamespaceManager nsManager = new XmlNamespaceManager(xDoc.NameTable);
nsManager.AddNamespace(“z”, “#RowsetSchema”);
nsManager.AddNamespace(“rs”, “urn:schemas-microsoft-com:rowset”);
XmlNodeList xNode = xDoc.SelectNodes(“/rs:data/z:row”, nsManager);
foreach (XmlNode tmpNode in xNode)
ItemCounter++;
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(“<Method ID='” + ItemCounter + “‘ Cmd=’New’>”);
strBuilder.Append(“<Field Name=’Attachments’>” + “0” + “</Field>”);
strBuilder.Append(“<Field Name=’Title’>” + PolicyNumber.Text + “</Field>”);
strBuilder.Append(“<Field Name=’Reason’>” + comboBox1.Text + “</Field>”);
strBuilder.Append(“</Method>”);
string strBatch = strBuilder.ToString();
XmlDocument newDoc = new XmlDocument();
XmlElement newElement = newDoc.CreateElement(“Batch”);
newElement.SetAttribute(“OnError”, “Continue”);
newElement.SetAttribute(“ViewName”, viewGUID);
newElement.InnerXml = strBatch;
XmlNode returnNode = listService.UpdateListItems(listGUID, newElement);
this.comboBox1.Text = “Please Select….”;
this.PolicyNumber.Text = “”;
this.PolicyNumber.Mask = “0000000000”;
comboBox1.Focus();
}
public void Form1_MouseUp(object sender, MouseEventArgs e)
{//form move on click and drag
FormMoving = false;
}
public void Form1_MouseMove(object sender, MouseEventArgs e)
{//form move on click and drag
if (FormMoving)
{
if ((Left + e.X – initialPoint.X) <= 0)
Left = 0;
else if ((Right + e.X – initialPoint.X) >= Screen.PrimaryScreen.Bounds.Right)
Left = Screen.PrimaryScreen.Bounds.Right – Width;
else
Left = Left + e.X – initialPoint.X;
if ((Top + e.Y – initialPoint.Y) <= 0)
Top = 0;
else if ((Bottom + e.Y – initialPoint.Y) >= Screen.PrimaryScreen.Bounds.Bottom)
Top = Screen.PrimaryScreen.Bounds.Bottom – Height;
else
Top = Top + e.Y – initialPoint.Y;
}
SetPositionOfPictureForm();
}
public void Form1_MouseDown(object sender, MouseEventArgs e)
{//form move on click and drag
FormMoving = true;
initialPoint = new Point(e.X, e.Y);
}
public void pictureBox1_MouseDown(object sender, MouseEventArgs e)
{//form move on click and drag
FormMoving = true;
initialPoint = new Point(e.X, e.Y);
}
private void pictureBox1_MouseMove(object sender, MouseEventArgs e)
{
}
private void pictureBox1_MouseHover(object sender, EventArgs e)
{
}
public void label1_MouseHover(object sender, EventArgs e)
{
FormMoving = false;
}
public void label1_MouseUp(object sender, MouseEventArgs e)
{
FormMoving = false;
}
public void Form1_MouseHover(object sender, EventArgs e)
{
this.Opacity = 1;
}
public void Form1_MouseLeave(object sender, EventArgs e)
{
if(!PolicyNumber.Focused)
this.Opacity = .25;
}
public void pictureBox1_MouseLeave(object sender, EventArgs e)
{
this.Opacity = 1;
}
public void pictureBox1_MouseHover_1(object sender, EventArgs e)
{
this.Opacity = 1;
}
public void Form1_Click(object sender, EventArgs e)
{
comboBox1.Focus();
}
}
}
.NET Framework, 2003, 2010, activeItemData, AddNamespace, AllowUnsafeUpdates, Append, Application, Attachments, Batch, Bottom, Bounds, c#, ChannelFactory, ClientCredential, Collections, ComponentModel, Continue, CreateElement, CredentialCache, Credentials, crisis, csharp, Data, DefaultNetworkCredentials, EventArgs, Field, Focus, Form, Forms, friend, Generic, GetListItems, InitializeComponent, initialPoint, InnerXml, ItemCounter, knee, Left, Linq, List, listGUID, Lists, listService, ListsSoapClient, LoadXml, Mask, method, migration, MouseEventArgs, Name, NameTable, nsManager, OnError, Owner, PAGE, Point, PolicyNumber, Reason, Replace, RetentionLists, RowsetSchema, schemas, Select, SelectedIndex, SelectNodes, sender, server, Service, ServiceList, ServiceModel, SetAttribute, SetPositionOfPictureForm, SharePoint, solution, SPWeb, StringBuilder, System, Text, Title, TrackerButton, TriggerTrackerPictureBox, Trigger_Tracker, UpdateListItems, Validation, viewGUID, ViewName, Web Page Security, Width, Windows, xDoc, XmlDocument, XmlElement, XmlNamespaceManager, XmlNode, XmlNodeList, xNode
I have been fooling around with Visio automation since attempting to help a friend with a project that did so. In the course of this I did this code snippet to convert the Visio page to an image. The code loops through and takes each page and converts it to an image. Pretty basic. I have not posted in a while and my apologies to those of you who were wondering….
Here is the code….
Sub SaveAsImage()
‘ creates an invisible Visio instance, opens a document, then
‘ saves all pages in the document as jpg images using
‘ page name and page number as file name
Dim vsoApp As Visio.Application
Dim vsoDoc As Visio.Document
Dim PathName As String, jpgName As String
Dim pg As Visio.Page
Set vsoApp = CreateObject(“Visio.InvisibleApp”)
‘ SET PATH/FILENAME BELOW TO VSD ON YOUR SYSTEM
Set vsoDoc = vsoApp.Documents.Open(“c:\TEST\test.vsd”)
PathName = vsoApp.Documents(1).Path ‘ Set pathname to that of first document
For Each pg In vsoApp.ActiveDocument.Pages
jpgName = PathName & Format(pg.Index, “0#”) & ” ” & pg.Name & “.jpg”
pg.Export jpgName
Next
vsoDoc.Close
vsoApp.Quit
Set vsoDoc = Nothing
Set vsoApp = Nothing
End Sub
.NET, ActiveDocument, apologies, Application, automation, Close, Convert, CreateObject, csharp, Document, Export, Format, friend, Images, index, instance, Name, PAGE, Pages, PathName, Quit, snippet, Visio, vsoApp, vsoDoc
This is a C# adaptation of the code I wrote to write a datatable to a Microsoft Word document table for vb.net . But that doesn’t really begin to tell the story here. In vb.net we have been accustomed to being allowed to leave parameters empty when automating the creation of a table in Microsoft Word. C# has not permitted me that luxury which to be honest is probably a better code practice. So prepare to meet the Type.Missing object! In addition the default item that we learned to love/hate in Visual Basic for Applications (VBA) code years ago also is not used in C#. Finally in declaring the range object for the table it became an opportunity to use the new dynamic reference type keyword, which was designed for such situations. Check out this video on the subject which is quite excellent.
http://channel9.msdn.com/Shows/Going+Deep/Inside-C-40-dynamic-type-optional-parameters-more-COM-friendly/player?w=512&h=288
For these reasons you will see key differences between the two sets of code. Don’t forget to import Microsoft Word as a COM reference and do your import statements. As always feel free to comment or email. Have a great day!
using Office = Microsoft.Office.Core;
using Word = Microsoft.Office.Interop.Word;
public void CreateWordTableWithDataTable(DataTable dt)
{
int RowCount = dt.Rows.Count; int ColumnCount = dt.Columns.Count;
Object[,] DataArray = new object[RowCount + 1, ColumnCount + 1];
//int RowCount = 0; int ColumnCount = 0;
int r = 0;
for (int c = 0; c <= ColumnCount – 1; c++)
{
DataArray[r, c] = dt.Columns[c].ColumnName;
for (r = 0; r <= RowCount – 1; r++)
{
DataArray[r, c] = dt.Rows[r][c];
} //end row loop
} //end column loop
Word.Document oDoc = new Word.Document();
oDoc.Application.Visible = true;
oDoc.PageSetup.Orientation = Word.WdOrientation.wdOrientLandscape;
dynamic oRange = oDoc.Content.Application.Selection.Range;
String oTemp = “”;
for (r = 0; r <= RowCount – 1; r++)
{
for (int c = 0; c <= ColumnCount – 1; c++)
{
oTemp = oTemp + DataArray[r, c] + “\t”;
}
}
oRange.Text = oTemp;
object Separator = Word.WdTableFieldSeparator.wdSeparateByTabs;
object Format = Word.WdTableFormat.wdTableFormatWeb1;
object ApplyBorders = true;
object AutoFit = true;
object AutoFitBehavior = Word.WdAutoFitBehavior.wdAutoFitContent;
oRange.ConvertToTable(ref Separator,
ref RowCount, ref ColumnCount, Type.Missing, ref Format,
ref ApplyBorders, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, ref AutoFit, ref AutoFitBehavior,
Type.Missing);
oRange.Select();
oDoc.Application.Selection.Tables[1].Select();
oDoc.Application.Selection.Tables[1].Rows.AllowBreakAcrossPages = 0;
oDoc.Application.Selection.Tables[1].Rows.Alignment = 0;
oDoc.Application.Selection.Tables[1].Rows[1].Select();
oDoc.Application.Selection.InsertRowsAbove(1);
oDoc.Application.Selection.Tables[1].Rows[1].Select();
//gotta do the header row manually
for (int c = 0; c <= ColumnCount – 1; c++)
{
oDoc.Application.Selection.Tables[1].Cell(1, c + 1).Range.Text = dt.Columns[c].ColumnName;
}
oDoc.Application.Selection.Tables[1].Rows[1].Select();
oDoc.Application.Selection.Cells.VerticalAlignment = Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
}
Facebook
.NET, .NET Framework, adaptation, addition, Alignment, AllowBreakAcrossPages, Application, Applications, ApplyBorders, Array, AutoFit, AutoFitBehavior, Basic, Cell, Cells, column, ColumnCount, ColumnName, columns, Content, ConvertToTable, Count, creation, csharp, DataTable, Document, Dynamic, Format, header, InsertRowsAbove, Interop, item, Microsoft, Object, Office, Orientation, PageSetup, parameters, Range, reference, RowCount, Rows, Select, Selection, Separator, statements, Table, Tables, Text, Type, vb.net, VerticalAlignment, Visible, Visual, WdAutoFitBehavior, WdCellVerticalAlignment, WdOrientation, WdTableFieldSeparator, WdTableFormat, Word, Write
Again I am rewriting some of the more popular entries in this blog as C#. It’s good exercise for me and good for you because you get the code another way. Well at least that’s the theory! This entry writes to the header and footer of a document in Microsoft Word The vb.net version was here. The only downside to all of this is that this is only tested against Office 2010. The old version I had Office 2000, XP and 2003 on my machine. Essentially however it is the same code. If you have any questions of course please feel free to leave a comment or email me.
public void WriteHeaderandFooterinWordDocument()
{
Word.Document oDoc = new Word.Document();
oDoc.Application.Visible = true;
oDoc.Content.Application.ActiveWindow.ActivePane.View.SeekView = Word.WdSeekView.wdSeekCurrentPageHeader;// = Convert.ToInt32(Word.WdSeekView.wdSeekCurrentPageHeader);
oDoc.Content.Application.Selection.TypeText(“Martens “);
oDoc.Content.Application.Selection.Fields.Add(oDoc.Content.Application.Selection.Range, Word.WdFieldType.wdFieldEmpty, “PAGE”);
oDoc.Content.Application.ActiveWindow.ActivePane.View.SeekView = Word.WdSeekView.wdSeekMainDocument;
oDoc.Content.Application.ActiveWindow.ActivePane.View.SeekView = Word.WdSeekView.wdSeekCurrentPageFooter;
oDoc.Content.Application.Selection.TypeText(“Martens”);
oDoc.Content.Application.ActiveWindow.ActivePane.View.SeekView = Word.WdSeekView.wdSeekMainDocument;
}
I’m on Facebook
.NET, .NET Framework, ActivePane, Application, Content, Convert, csharp, Document, Facebook, Fields, Footer, header, Machine, Martens, Microsoft, Office, PAGE, Range, SeekView, Selection, TypeText, vb.net, version, View, Visible, WdFieldType, wdSeekCurrentPageHeader, WdSeekView, Word, Write
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
}
}
My Facebook Link
.NET, .NET Framework, ActiveSheet, ActiveWorkbook, Application, Array, c#, Cells, column, ColumnName, columns, Count, Data, DataSet, DataTable, Excel, From, header, Import, instance, Interop, manner, method, Microsoft, Office, Range, reference, Resize, Rows, self, situations, spreadsheet, spreadsheets, statements, TableName, Tables, Value, vb.net, Visible, Workbooks, Worksheet, Write, zero
Hello! It has been a long time since my last blog post and I am happy to be getting back to this. I have missed writing this blog more than you have missed me I guarantee it!
That being said, now that this blog has resumed there will be a few changes. The landscape has changed a bit since my last blog entry. While I will still focus primarily on vb.net code I will also be including what I am working on that is WPF and C# related as well as other .NET related topics. Why? Simply because as my skillset has expanded I wanted to share what I have learned and am learning with you as well. I hope you won’t mind!
Today’s topic revolves around getting images from Microsoft Office Document Imaging (MODI). As you recall I have done a lot of Office automation over the years and I was asked to do this for a friend. As I said, it is good to be back!
add a reference -> Com -> Microsoft Office Document Imaging
Private Sub ConvertMdi2Tif(ByVal ModiFilePath As String)
Dim miDoc As New MODI.Document
Dim myViewer As New MODI.MiDocView
Dim myImg As MODI.Image
Try
Dim Folder As New DirectoryInfo(ModiFilePath)
pbReportGenerate.Minimum = 0
pbReportGenerate.Maximum = Folder.GetFiles(“*.MDI”).Length
pbReportGenerate.Step = 1
pbReportGenerate.Value = pbReportGenerate.Minimum
For Each File As FileInfo In Folder.GetFiles(“*.MDI”)
miDoc.Create(File.FullName)
myViewer.Document = miDoc
For i As Long = 0 To miDoc.Images.Count – 1
Dim tempDoc As New MODI.Document
myImg = miDoc.Images(i)
tempDoc.Create()
tempDoc.Images.Add(myImg, Nothing)
tempDoc.SaveAs(TIFPath & “\” + GetFileNameWithoutExtension(File.FullName) & i & “.Tif”, MiFILE_FORMAT.miFILE_FORMAT_TIFF_LOSSLESS, MiCOMP_LEVEL.miCOMP_LEVEL_HIGH)
tempDoc.Close()
tempDoc = Nothing
Exit For
Next
pbReportGenerate.PerformStep()
Application.DoEvents()
Next
miDoc.Close()
miDoc = Nothing
myViewer.Document = Nothing
myViewer = Nothing
Catch ex As Exception
MsgBox(ex.Message)
End Try
GC.Collect()
End Sub
Private Sub ConvertTif2Bmp(ByVal TIFFilePath As String)
Dim Folder As New DirectoryInfo(TIFFilePath)
Dim index As Long = 0
pbReportGenerate.Minimum = 0
pbReportGenerate.Maximum = Folder.GetFiles(“*.TIF”).Length
pbReportGenerate.Step = 1
pbReportGenerate.Value = pbReportGenerate.Minimum
For Each File As FileInfo In Folder.GetFiles(“*.TIF”)
GC.WaitForPendingFinalizers()
Dim streamBinary As New FileStream(File.FullName, FileMode.Open)
Dim g As System.Drawing.Image = System.Drawing.Image.FromStream(streamBinary)
Dim imgOutput As New Bitmap(g, g.Width, g.Height)
Dim qualityEncoder As Encoder = Encoder.Quality
Dim ratio As EncoderParameter = New EncoderParameter(qualityEncoder, 40)
Dim codecParams As New EncoderParameters(1)
codecParams.Param(0) = ratio
‘bmp.Save(fileName, jpegCodecInfo, codecParams)
Dim encoder1 As ImageCodecInfo = GetEncoderInfo(“image/bmp”)
imgOutput.Save(BMPPath + “\” + GetFileNameWithoutExtension(File.FullName) + “.” & System.Drawing.Imaging.ImageFormat.Bmp.ToString, encoder1, codecParams)
streamBinary.Close()
streamBinary = Nothing
g.Dispose()
g = Nothing
imgOutput.Dispose()
imgOutput = Nothing
pbReportGenerate.PerformStep()
Application.DoEvents()
Next
GC.Collect()
End Sub
My Facebook
WordPress Tags: vb.net,Images,Microsoft,Office,Document,MODI,automation,friend,reference,MiDocView,Image,Folder,DirectoryInfo,Minimum,Maximum,GetFiles,Length,Step,Value,File,FileInfo,Create,FullName,Long,Count,SaveAs,TIFPath,GetFileNameWithoutExtension,MiFILE_FORMAT,MiCOMP_LEVEL,Close,Exit,PerformStep,Application,DoEvents,Catch,Exception,MsgBox,Message,Collect,TIFFilePath,index,WaitForPendingFinalizers,FileStream,FileMode,Open,System,FromStream,Bitmap,Width,Encoder,ratio,EncoderParameter,EncoderParameters,Param,Save,ImageCodecInfo,GetEncoderInfo,BMPPath,ImageFormat,Dispose,blog,miDoc,myViewer,myImg,pbReportGenerate,tempDoc,streamBinary,imgOutput,qualityEncoder,codecParams
Application, automation, Bitmap, blog, BMPPath, Catch, Close, codecParams, Collect, Count, Create, DirectoryInfo, Dispose, Document, DoEvents, Encoder, EncoderParameter, EncoderParameters, Exception, Exit, File, FileInfo, FileMode, FileStream, Folder, friend, FromStream, FullName, GetEncoderInfo, GetFileNameWithoutExtension, GetFiles, Image, ImageCodecInfo, ImageFormat, Images, imgOutput, index, Length, Long, Maximum, Message, MiCOMP_LEVEL, Microsoft, miDoc, MiDocView, MiFILE_FORMAT, Minimum, MODI, MsgBox, myImg, myViewer, Office, Open, Param, pbReportGenerate, PerformStep, qualityEncoder, ratio, reference, Save, SaveAs, Step, streamBinary, System, tempDoc, TIFFilePath, TIFPath, Value, vb.net, WaitForPendingFinalizers, Width
Recent Comments