Excel Application Creation in C#

Last week I wanted to create an excel application using C#, and after doing couple of research, I was able to complete that. Below I have included couple of code snippets which was very usefull for me.

Before doing anything you need to add the "Microsoft.Office.Interop.Excel" reference to your project.

private static Application _application;
private static Workbook _workbook;
private static Worksheet _worksheet;
private static Range _workSheetRange;
private static Sheets _excelSheets;

1. Code to create the excel workbook and save it in hard drive

public void CreateExcelWorkBook()
{
_application = new Application();
_application.Visible = false; // will make the excel book opened for you
_workbook = _application.Workbooks.Add(1);

DateTime currentDate = System.DateTime.Now;
string name = currentDate.Year.ToString() + currentDate.Month.ToString() + currentDate.Day.ToString() + "_" +
currentDate.ToLocalTime().Hour + currentDate.ToLocalTime().Minute + ".xls";

StringBuilder builder = new StringBuilder();

string locationName = "C:\data\"+ name;

_workbook.SaveAs(locationName, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}

2. Save the workbook

public void SaveWorkSheet()
{
_workbook.Save();
}

After doing couple of changes to the workbook, you need to save it to update the created the workbook. Othervise none of your work will be updated.

3. Open the worksheet

public void OpenWorkSheet()
{
_application.Visible = true;
}

This will oipen the workbook, and make it visible to the customer.

4. Create a new worksheet.

public void ChangeWorkSheet(int sheetNumber, string name)
{
_worksheet =
(Worksheet) _excelSheets.Add(_excelSheets[sheetNumber], Type.Missing, Type.Missing, Type.Missing);
_worksheet.Name = name;
}

When you need to create a worksheet, you always needs to give the sheetNumber as 1. And you can also chnage the sheet name according to your requirement.

5. Create Headers

public void CreateHeaders(int rowNumber, int colNumber, string headerText, string cell1, string cell2, int mergeColumns, bool isBold, int columnWidth, int fontSize, int option)
{
_worksheet.Cells[rowNumber, colNumber] = headerText;
_workSheetRange = _worksheet.get_Range(cell1, cell2);
if(option ==2)
{
_workSheetRange.HorizontalAlignment = Constants.xlCenter;
_workSheetRange.NumberFormat = "@";
}
_workSheetRange.Merge(mergeColumns);
_workSheetRange.Font.Name = "Arial";
_workSheetRange.Font.Size = fontSize;
_workSheetRange.Font.Bold = isBold;
_workSheetRange.ColumnWidth = columnWidth;
_workSheetRange.Font.Color = System.Drawing.Color.Black.ToArgb();
}

For example if you want to display something you need to give below details

CreateHeaders(1, 1, "Header 1", "A1", "A1", 0, true, 10, 12, 1 );

This will display, Header 1 in A1 and A1 cell.

If you want cells to be merged, then

CreateHeaders(1, 1, "Header 1", "A1", "B1", 2, true, 10, 12, 1 );

This will display, Header 1 in A1 and B1 cell, and both cells will be merged.


6. Add data

public void AddData(int rowNumber, int columnNumber, string data, string cell1, string cell2, string cellformat)
{
_worksheet.Cells[rowNumber, columnNumber] = data;
_workSheetRange = _worksheet.get_Range(cell1, cell2);
_workSheetRange.NumberFormat = cellformat;
}

You can add data as per the earlier header creating method.

If you all wne through all these things, you will identify, there is one thing which is called "NumberFormat". Now you might be wondering what we need to include for the number format, actually there are several number formats that can be used for this.

DecimalNumberFormat = "###,###,###,##0.00";
TextFormat ="@";

PercentageNumberFormat = "#,##0%"; // this will truncate the decimal places and display with "%" sign
PercentageNumberFormat2 = "#,##0.00%"; // this will display the decimal places as it is with "%" sign

Set Custom Paper Size as Crystal Report's Paper Size

Yesterday urgently I wanted to write a code to set a crystal reports page size into a custom page size. I checked every where but there were very little code to be found. Finally I managed to do it from the following code.

System.Drawing.Printing.PrintDocument doctoprint = new System.Drawing.Printing.PrintDocument();
doctoprint.PrinterSettings.PrinterName = "Microsoft XPS Document Writer";

for (int i = 0; i < papername ="="" the="" custom="" page="" name="">)
{
rawKind =
Convert.ToInt32(
doctoprint.PrinterSettings.PaperSizes[i].GetType().GetField("kind",
System.Reflection.
BindingFlags.NonPublic |
System.Reflection.
BindingFlags.Instance).
GetValue(doctoprint.PrinterSettings.PaperSizes[i]));
//crPrintOut.PrintOptions.PaperSize = rawKind;
_report.PrintOptions.PaperSize = (CrystalDecisions.Shared.PaperSize) rawKind;

break;
}
}

Insert a field to content type in SharePoint 2007

Today I had a problem in adding a field to a content type. I searched on everything but couldn't find out any solution. When trying out many things, finally got the answer.

When you want to add a field to a content type, first we need to add that field to the web, then only we can add the field to the content type, and finally to the list which has the content type.

Here is the code that I used for this.

public static void CreateSpField(SPWeb web, string contentTypeName, string fieldName, string fieldType, string listName)
{
web.AllowUnsafeUpdates = true;

web.ParentWeb.Fields.Add(fieldName, SPFieldType.DateTime, false);

SPContentType contentType = web.ParentWeb.ContentTypes[contentTypeName];
SPFieldLink fieldLink = new SPFieldLink(web.ParentWeb.Fields[fieldName]);
contentType.FieldLinks.Add(fieldLink);

contentType.Update();
web.ParentWeb.Update();

SPList list = web.Lists[listName];
list.Fields.Add(web.ParentWeb.Fields[fieldName]);
list.Update();

web.AllowUnsafeUpdates = false;
}