Adding comments to cells using Interop.Excel
Before I showed how to create an excel document programmatically.
It is very easy to add comments to the cells. There is one thing you should know first. Comments are not added to a range of cells. They are only added to the 1 cell in a range.
If you have a range extending from B1 to C5, the comment is added to cell B1.
You would think you can simple add a comment to the range you are adding your value with. That is not so and I could find nowhere that this is mentioned (If Microsoft documented their stuff well, we would not need these blogs).
Adding to my previous example, I created 2 new methods to handle comments. This time I excluded the 'end' cell:
public void AddCommentToCell(int x, int y, string comment)
{
if (x < 1) x = 1;
if (y < 1) y = 1;
string start = excelColumnLetter(x - 1) + y.ToString();
AddCommentToCell(start, comment);
}
public void AddCommentToCell(string start, string comment)
{
Range range = (Range)sheet.get_Range(start, missing);
range.ClearComments();
range.AddComment(comment);
}
These methods are used as such
// First create the cell xl.AddContentToCell(x, y, 1, rowSpan, text); // Then add the comment xl.AddCommentToCell(x, y, comment);
This examples shows adding the comment. You can see that I must create a new range for the comment. The 'end' must be 'missing':
string start = "A1";
string end = "C3";
Range range = (Range)sheet.get_Range(start, end);
range.MergeCells = true;
range.WrapText = true;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
range.Borders.LineStyle = XlLineStyle.xlDash;
range.Borders.Weight = XlBorderWeight.xlMedium;
range.Borders.Color = 0x060606;
range.Interior.Color = 0xffff8080;
range.Value2 = "Content for the cell";
range.ColumnWidth = width;
range.RowHeight = height;
Range cRange = (Range)sheet.get_Range(start, System.Reflection.Missing.Value);
cRange.AddComment("hello there");
Using external libraries in a SSIS Script Component
I found myself needing to encrypt a column of data in a SSIS package.
I added a Script Component that would take one field from the database and return the same field encrypted.
The library that does the encrypting was not written in Visual Studio. It was created in Power Basic I think.
Normally, you would just import the library using DLLImport and run the method you need. Like this:
private const string MYENCRYPTOR = @"C:\Encrypt.dll"; [DllImport(MYENCRYPTOR, EntryPoint = "ENCRYPTMYID", SetLastError = true, CharSet = CharSet.Ansi)] private static extern string EncryptMyId(int myId); ... ... ... string encryptedId = EncryptMyId(id);
This works in a normal project, but for some reason I do not know, it will not work in an SSIS package.
To fix this, I created a Console Application wrapper for the encrypt method.
The console application accepts the ID as an input variable and returns the encrypted ID as a string.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Diagnostics;
namespace ExcryptThis
{
class Program
{
private const string MYENCRYPTOR = @"C:\Encrypt.dll";
[DllImport(MYENCRYPTOR, EntryPoint = "ENCRYPTMYID", SetLastError = true, CharSet = CharSet.Ansi)]
private static extern string EncryptMyId(int myId);
static void Main(string[] args)
{
string sId;
if (args.Count<string>() > 0)
{
sId = args[0];
try
{
int id = Convert.ToInt32(sId);
string encryptedId = EncryptMyId(id);
Console.Write(encryptedId);
}
catch (Exception ex) { }
}
}
}
}
Note: be sure to use a Console.Write and not a .WriteLine or you will add a "\r\n" at the end of the returned value.
Then, in the SSIC Script Component, I call the console application as I described in my previous post.
Wrapped in the console application it runs fine.
Run Command Line program from C#
There are some situations where you will need to run one program from another. To run a simple program from withing C#, use System.Diagnostics.Process:
//Create process System.Diagnostics.Process pProcess = new System.Diagnostics.Process(); //strCommand is path and file name of command to run pProcess.StartInfo.FileName = @"C:\MyProgram.exe"; //strCommandParameters are parameters to pass to program pProcess.StartInfo.Arguments = "10"; pProcess.StartInfo.UseShellExecute = false; //do not show the console window pProcess.StartInfo.CreateNoWindow = true; //Set output of program to be written to process output stream pProcess.StartInfo.RedirectStandardOutput = true; //Optional - this does not work within SSIS //pProcess.StartInfo.WorkingDirectory = @"C:\"; //Start the process pProcess.Start(); //Get program output string strOutput = pProcess.StandardOutput.ReadToEnd(); //Wait for process to finish pProcess.WaitForExit(); Console.WriteLine(strOutput);
Export to Excel in C#
There are many ways to export to Excel from C#. There are even more packages you can buy that do the same.
Instead of buying a package, wrote myself a very basic class to do this. This class can be expanded to allow for a lot more functionality. This is a good start.
What does it use?
You will need references to Microsoft.Office.Interop.Excel (I used version 2), System.IO (for saving the file), and off course, System (for the Garbage Collection).
Open the document
Create the file, then create the workbooks, then the workbook, and then the sheet in that workbook.
private string excelFileName;
private Microsoft.Office.Interop.Excel.Application xls;
private Workbooks workBooks;
private Workbook workBook;
private Worksheet sheet;
object missing = System.Reflection.Missing.Value;
private void Open()
{
xls = new Microsoft.Office.Interop.Excel.Application();
workBooks = xls.Workbooks;
workBook = workBooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workBook.Worksheets;
sheet = (Worksheet)sheets.get_Item(1);
}
Saving the file
After saving the workbook, close and quit and do cleanup.
public void Save()
{
// First delete the file - for not appending
if (File.Exists(excelFileName))
File.Delete(excelFileName);
// Very basic settings
workBook.SaveAs(excelFileName, XlFileFormat.xlWorkbookDefault, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
workBook.Close(missing, missing, missing);
xls.Workbooks.Close();
xls.Application.Quit();
xls.Quit();
Cleanup();
}
Garbage collection and cleanup
You have to do your own cleaning up when working with Interop.Excel.
Null everything out and then do garbage collection.
private void Cleanup()
{
xls = null;
workBook = null;
sheet = null;
workBooks = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
Selecting a cell
When you reference a call or a range of cells, you start with the sheet you want to select the cells from.
You reference cell with the numeric values y,x (not x,y).
Range range1 = (Range)sheet.Cells[3, 1]; // 3 cells from the top and 1 cell from the left Range range2 = (Range)sheet.Cells[2, 5]; // 2 cells from the top and 5 cells from the left
Selecting a range of cells
A range is selected using alpha numeric values: 'A1' - top left, 'B2' - 2nd column and 2nd row
It is easy to convert numerical values to the Excel Alpha value using the method below.
NOTE: When converting from numeric to alpha, 0 = A, 1 = B, etc...
Range range1 = (Range)sheet.get_Range("A1", "A4"); // first 4 rows in the first column
Range range2 = (Range)sheet.get_Range("C4", "D5"); // for cells - 2 across and 2 down, starting at C4
// Range starting at 2,2 and extending to 4,4
string start = excelColumnLetter(2 - 1) + "2".ToString();
string end = excelColumnLetter(4 - 1) + "4".ToString();
Range range3 = (Range)sheet.get_Range(start, end);
// NOTE: When converting from numeric to alpha, 0 = A, 1 = B, etc...
private string excelColumnLetter(int intCol)
{
int intFirstLetter = ((intCol) / 26) + 64;
int intSecondLetter = (intCol % 26) + 65;
char letter1 = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
return string.Concat(letter1, (char)intSecondLetter).Trim();
}
Adding content to a cell or range of cells
Create a 'Range' using the 'sheet' you are working on.
Add the content to the 'Value2' property.
There are a lot of visual setting you can use.
A handy chart for the hex colors can be found here.
The method below expects the alpha/numeric value for start and end: "A1", "C44", etc.
public void AddContentToCell(string start, string end, string content, int height, int width)
{
Range range = (Range)sheet.get_Range(start, end);
range.Value2 = content;
range.MergeCells = true;
range.WrapText = true;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
range.Borders.LineStyle = XlLineStyle.xlDash;
range.Borders.Weight = XlBorderWeight.xlMedium;
range.Borders.Color = 0x060606;
range.Interior.Color = 0xffff8080;
range.ColumnWidth = width;
range.RowHeight = height;
}
Overloads for the AddContent to Cell method
The first overload allows you to select a cell using x and y instead of the alpha/numeric cell reference.
The 2nd overload allows you to select a cell using x and y and use row span and column span.
public void AddContentToCell(int x, int y, string content)
{
if (x < 1) x = 1;
if (y < 1) y = 1;
string start = excelColumnLetter(x - 1) + y.ToString();
string end = start;
AddContentToCell(start, end, content, 50, 30);
}
public void AddContentToCell(int x, int y, int columnspan, int rowspan, string content)
{
if (x < 1) x = 1;
if (y < 1) y = 1;
if (columnspan < 1) columnspan = 1;
if (rowspan < 1) rowspan = 1;
string start = excelColumnLetter(x - 1) + y.ToString();
string end = excelColumnLetter(x + columnspan - 2) + (y + rowspan - 1).ToString();
int width = 30/columnspan;
int height = 50/rowspan;
AddContentToCell(start, end, content, height, width);
}
How to use it?
To use all this, set the file name and call the Open() method. Add stuff to the cells and call the Save() method!
What next?
Next would be to allow us to specify color, borders, etc.
Debugging SSIS Script Component
MS chose not to allow breakpoints in the script of the Script Component. Now, debugging must be done by other more primitive methods. Here are a list of tips to help you out:
- MessageBox.Show(): Luckily, the MessageBox works. Add some Message Boxes to your code to see what happens.
- Data Viewer: In the Data Flow Path Editor (double click the green arrow connecting your boxes in the Data Flow) allows you to adda Data View. If you add one before and after your Script Component, you can see what data went into the script editor, and what came out.
Double click the arrow before your Script Component and Select Data Viewers from the list. Click 'Add' then choose Grid. Click the 'Grid' tab to select the columns you want in the grid.
When you run the package, it will pop up a grid and wait for you to continue the flow.
- Fire methods: The fire methods (FireProgress, FireInformation, FireError, etc) allow you to output information to the Output window during run time and to the Progress tab during run time. Only FireProgress outputs to the Progress tab. The others output to the Output window.
Below is an example:
public override void Input0_ProcessInputRow(Input0Buffer Row) { bool c = true; // Output to the Progress tab ComponentMetaData.FireProgress("Input0_ProcessInputRow()", 0, 0, 1, "Row: " + Row.ID, ref c); // Info item displayed in the Output window ComponentMetaData.FireInformation(0, "Input0_ProcessInputRow()", "Row: " + Row.ID, null, 0, ref c); if (Row.ID != null) { try { Row.IDEncrypted = EncryptThis((int)Row.ID); } catch (Exception ex) { // Error item displayed in the Output window ComponentMetaData.FireError(411, "Input0_ProcessInputRow()", "Could not encrypt ID: " + Row.ID, string.Empty, 0, out c); } } }
Custom Attributes
If you would like to adorn your classes with some custom meta-data, Custom Attributes are the way to go.
First, start with your class.
public class MyDumbClass
{
public MyDumbClass()
{
// do nothing
}
}
Now we want to say some things about this class... like, is it a good class?
We create an attribute where we can specify this info.
Create a class that inherits from 'Attribute'.
Add some fields to the class.
public class MySpecialAttribute : Attribute
{
public string GoodAdvice { get; set; }
public bool ThisClassIsGood { get; set; }
}
Now you can find in your intellesense the "MySpecial" attribute.
[MySpecial(ThisClassIsGood = true, GoodAdvice="Do no harm")]
public class MyDumbClass
{
public MyDumbClass()
{
// do nothing
}
}
The attribute name, as you can see, is the name of the attribute class without the 'Attribute' text.
Because there can never be to much good in the world, you may wish to add the attribute several times.
Doing so will give you an error unless you specify the attribute can be used multiple times.
On the Attribute class, add an AttributeUsage attribute:
[AttributeUsage(AttributeTargets.Class, AllowMultiple=true)]
public class MySpecialAttribute : Attribute
{
public string GoodAdvice { get; set; }
public bool ThisClassIsGood { get; set; }
}
We chose 'AttributeTarget.Class' because we are using it on a class. If you want the attribute used on a field, or method or something else, you can specify AttributeTargets.Method or whatever you need.
Serialization to XML with CDATA tags
Sometimes you need CDATA tags around complex text in your destination XML.
There is no build in way of doing so (that I have found).
I found this nifty CdataWrapper class on Marc Gravell's blog. Thanks Marc!
Here is the basic class.
public sealed class CDataWrapper : IXmlSerializable
{
// implicit to/from string
public static implicit operator string(CDataWrapper value)
{
return value == null ? null : value.Value;
}
public static implicit operator CDataWrapper(string value)
{
return value == null ? null : new CDataWrapper
{
Value =
value
};
}
public System.Xml.Schema.XmlSchema GetSchema()
{
return null;
}
// "" => <Node/>
// "Foo" => <Node><![CDATA[Foo]]></Node>
public void WriteXml(XmlWriter writer)
{
if (!string.IsNullOrEmpty(Value))
{
writer.WriteCData(Value);
}
}
// <Node/> => ""
// <Node></Node> => ""
// <Node>Foo</Node> => "Foo"
// <Node><![CDATA[Foo]]></Node> => "Foo"
public void ReadXml(XmlReader reader)
{
if (reader.IsEmptyElement)
{
Value = "";
}
else
{
reader.Read();
switch (reader.NodeType)
{
case XmlNodeType.EndElement:
Value = ""; // empty after all...
break;
case XmlNodeType.Text:
case XmlNodeType.CDATA:
Value = reader.ReadContentAsString();
break;
default:
throw new InvalidOperationException("Expected text/cdata");
}
}
}
// underlying value
public string Value { get; set; }
public override string ToString()
{
return Value;
}
}
To use it, you must change your DataMember from String to CDataWrapper, make it private and give it a public property.
This:
[DataMember]
pubblic string Answer { get; set; }
Becomes this:
pubblic string Answer { get; set; }
[DataMember(Name="Answer", EmitDefaultValue=false)]
private CDataWrapper AnswerCDATA
{
get { return Answer; }
set { Answer = value; }
}
Then the serialization is done with one line:
. . . XElement xml = Microsoft.ServiceModel.Web.SerializationExtensions.ToXml(object); .
So simple!!!!
More on Object Serialization to XML
It seems there are hundreds of ways to serialize object.
Here are just a few:
This one now seems very clumbsy. You pass in the object and the object type. It returns a string. I can't see the point of using this one any more now that I have found the next one.
public static string ContractObjectToXml<T>(T obj)
{
DataContractSerializer dataContractSerializer = new DataContractSerializer(obj.GetType());
String text;
using (MemoryStream memoryStream = new MemoryStream())
{
dataContractSerializer.WriteObject(memoryStream, obj);
byte[] data = new byte[memoryStream.Length];
Array.Copy(memoryStream.GetBuffer(), data, data.Length);
text = Encoding.UTF8.GetString(data);
}
return text;
}
This one I have started using is the most simple I have seen so far.
. . . XElement xml = Microsoft.ServiceModel.Web.SerializationExtensions.ToXml(object); .
This one returns a stream. It gives you a bit more control over what is shown and included in the XML.
public static Stream Serialize(Object obj)
{
MemoryStream memoryStream = new MemoryStream();
DataContractSerializer dcSerializer = new DataContractSerializer(obj.GetType());
XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
xmlWriterSettings.Encoding = new UTF8Encoding(false);
xmlWriterSettings.ConformanceLevel = ConformanceLevel.Document;
xmlWriterSettings.Indent = true;
//xmlWriterSettings.OmitXmlDeclaration = true;
using (XmlWriter xWriter = XmlWriter.Create(memoryStream, xmlWriterSettings))
{
dcSerializer.WriteObject(xWriter, obj);
xWriter.Flush();
//return Encoding.UTF8.GetString(memoryStream.ToArray());
memoryStream.Position = 0;
return memoryStream;
}
}
SSIS package deployment and passwords
Something to remember when deploying your SSIS package:
The server will most likely run your package under a different user than the user you used to create the package. This causes a problem with the 'ProtectionLevel'.
The default ProtectionLevel is EncryptSensitiveWithUserKey. During deployment, the user key will not be the same as yours. You need the same keys to encrypt and decrypt the connection strings and stuff like that.
This will give you the error: Failed to decrypt protected XML node "DTS:Password"
This error will only occure when you try to deploy, not while testing locally.
To fix this, change the ProtectionLevel to 'EncryptSensitiveWithPassword'.
ProtectionLevel is found in the properties of the 'Package Explorer' tab.
Then, when you create your job in the SQL Server Agent, you must enter your password.
This is done in the 'Steps' page:
- Under Type select SQL Server Integration Service Package
- Under Run as select SQL Server Agent Service Account
- Under the General tab and Package, select the path to your package (in the bid folder of your project or where ever you put it)
- Click Configurations or any other tab and you will then be prompted to enter your password in a popup.
GET and POST with JavaScript – with XMLHttpRequest
This is an example on how to make a POST call to a WCF service using JavaScript. Notice the XML to send is passed thru in the 'send' method. The XML type must be sent if you are sending XML.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<script type="text/javascript">
var url = 'http://example.com/myservice';
var xml = '<?xml version="1.0" encoding="utf-8" ?><Root xmlns="http://schemas.datacontract.org/2004/07/MyServiceDomain"><Name>John</Name><Language>UK</Langguage></Root>';
function getHTTPObject() {
if (typeof XMLHttpRequest != 'undefined') {
return new XMLHttpRequest();
}
try {
return new ActiveXObject("Msxml2.XMLHTTP");
} catch (e) {
try {
return new ActiveXObject("Microsoft.XMLHTTP");
} catch (e) { }
}
return false;
}
function useHttpResponse() {
if (http.readyState == 4) {
var textout = http.responseText;
doSomething(textout);
}
}
function doSomething(textout) {
alert(textout);
}
var http = getHTTPObject();
http.open("POST", url, true);
http.setRequestHeader("Content-type", "text/xml");
http.onreadystatechange = useHttpResponse;
http.send(xml);
</script>
</head>
<body>
</body>
</html>
NOTE: You cannot make calls to services or files outside your local domain. If you wish to call a service from another domain (for example a Yahoo or Google service), you must create a proxy page that calls the external service. Your JavaScript will call your proxy page.
Here is an example of a GET call. Notice that the URL and the request parameters are sent thru in the 'open' method. The 'send' method has no arguments.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<script type="text/javascript">
var url = 'http://example.com/myservice.php?name=John&Language=UK';
function getHTTPObject() {
if (typeof XMLHttpRequest != 'undefined') {
return new XMLHttpRequest();
}
try {
return new ActiveXObject("Msxml2.XMLHTTP");
} catch (e) {
try {
return new ActiveXObject("Microsoft.XMLHTTP");
} catch (e) { }
}
return false;
}
function useHttpResponse() {
if (http.readyState == 4) {
var textout = http.responseText;
doSomething(textout);
}
}
function doSomething(textout) {
alert(textout);
}
var http = getHTTPObject();
http.open("GET", url, true);
http.onreadystatechange = useHttpResponse;
http.send();
</script>
</head>
<body>
</body>
</html>