Posted By

denakitan on 10/20/12

Tagged

Versions (?)

Last Edited at 10/20/12 04:21am

Statistics

Viewed 1279 times

Favorited by 1 user(s)

Related snippets

.NET - C# - Interop - Excel - Utility/Wrapper Class

/ Published in: C#

Utility class that abstracts the creation of a Excel spreadsheet through Interop, and helps on adding and format its contents.

Highlighted TextExpand

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Drawing;
using System.Globalization;
using Microsoft.Office.Interop.Excel;

namespace ExcelWrapper
{
    public class Spreadsheet
    {
        private Application excelApplication;
        private Workbook workbook;
        private Worksheet worksheet;
        private Window mainWindow;

private Dictionary<string, XlPattern> namePatternMap;
        private Dictionary<string, XlLineStyle> nameLineStyleMap;
        private Dictionary<string, XlBorderWeight> nameBorderWeightMap;
        private Dictionary<string, XlHAlign> nameHAlignMap;
        private Dictionary<string, XlVAlign> nameVAlignMap;

public Spreadsheet(bool displayGridLines)
        {
            Initialize();

excelApplication = new Application();

// passing xlWBATWorksheet from XlWBATemplate enumeration as parameter
            // based on Workbooks.Add Method (http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.add%28v=office.11%29.aspx)
            workbook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

mainWindow = workbook.Windows[1];
            mainWindow.DisplayGridlines = displayGridLines;

// index starts on 1
            worksheet = (Worksheet)workbook.Worksheets[1];
        }

private void Initialize()
        {
            namePatternMap = new Dictionary<string, XlPattern>();
            namePatternMap.Add("25% Gray", XlPattern.xlPatternGray25);

nameLineStyleMap = new Dictionary<string, XlLineStyle>();
            nameLineStyleMap.Add("Continuous", XlLineStyle.xlContinuous);
            nameLineStyleMap.Add("Dot", XlLineStyle.xlDot);

nameBorderWeightMap = new Dictionary<string, XlBorderWeight>();
            nameBorderWeightMap.Add("Thin", XlBorderWeight.xlThin);
            nameBorderWeightMap.Add("HairLine", XlBorderWeight.xlHairline);

nameHAlignMap = new Dictionary<string, XlHAlign>();
            nameHAlignMap.Add("Center", XlHAlign.xlHAlignCenter);

nameVAlignMap = new Dictionary<string, XlVAlign>();
            nameVAlignMap.Add("Center", XlVAlign.xlVAlignCenter);
        }

public void Show()
        {
            excelApplication.Visible = true;
        }

public void Save(string path, string entity, DateTime date)
        {
            workbook.Saved = true; 
            workbook.SaveCopyAs(path);
        }

public void Close()
        {
            workbook.Close(true, Type.Missing, Type.Missing); 
            workbook = null;
            excelApplication.Quit();
            excelApplication = null;
        }

public void SetZoom(int zoomPercentage)
        {
            mainWindow.Zoom = zoomPercentage;
        }

public void FreezePanes(int splitRow, int scrollRow, int splitColumn, int scrollColumn)
        {
            mainWindow.SplitRow = splitRow;
            mainWindow.ScrollRow = scrollRow;
            mainWindow.SplitColumn = splitColumn;
            mainWindow.ScrollColumn = scrollColumn;
            mainWindow.FreezePanes = true;
        }

public void SetFont(string startCell, string endCell, string family, int size)
        {
            Range range = worksheet.get_Range(startCell, endCell);
            range.Font.Name = family;
            range.Font.Size = size;
        }

public void SetFontStyle(string startCell, string endCell, bool bold, bool underline, string color)
        {
            Range range = worksheet.get_Range(startCell, endCell);
            range.Font.Bold = bold;
            range.Font.Underline = underline;
            range.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(color));            
        }

public void SetBackgroundColor(string startCell, string endCell, string color)
        {
            Range range = worksheet.get_Range(startCell, endCell);
            range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(color));            
        }

public void SetNumberFormat(string startCell, string endCell, string numberFormat)
        {
            Range range = worksheet.get_Range(startCell, endCell);
            range.NumberFormat = numberFormat;
        }

public void SetPattern(string startCell, string endCell, string pattern)
        {
            Range range = worksheet.get_Range(startCell, endCell);

try
            {
                XlPattern xlPattern = namePatternMap[pattern];
                range.Interior.Pattern = xlPattern;
            }
            catch
            {
                range.Interior.Pattern = XlPattern.xlPatternSolid;
            }
        }

public void InsertPicture(string path, float left, float top, float width, float height)
        {
            worksheet.Shapes.AddPicture(path, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue,
                left, top, width, height);
        }

public void SetText(string cell, string text)
        {
            Range range = worksheet.get_Range(cell, cell);
            range.Cells[1,1] = text;
        }

public void SetFormula(string cell, string formula)
        {
            Range range = worksheet.get_Range(cell, cell);
            range.Formula = formula;
        }

public void SetBorderAround(string startCell, string endCell, string lineStyle, string borderWeight, string borderColorName)
        {
            Range range = worksheet.get_Range(startCell, endCell);

try
            {
                object borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(borderColorName));
                range.BorderAround(nameLineStyleMap[lineStyle], nameBorderWeightMap[borderWeight], XlColorIndex.xlColorIndexAutomatic, borderColor);
            }
            catch
            {
                object borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName("Black"));
                range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, borderColor);
            }
        }

public void SetBorderInternal(string startCell, string endCell, string lineStyle, string borderWeight, string borderColorName)
        {
            Range range = worksheet.get_Range(startCell, endCell);

try
            {
                object borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName(borderColorName));
                range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = nameLineStyleMap[lineStyle];
                range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = nameBorderWeightMap[borderWeight];
                range.Borders[XlBordersIndex.xlInsideHorizontal].Color = borderColor;
                range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = nameLineStyleMap[lineStyle];
                range.Borders[XlBordersIndex.xlInsideVertical].Weight = nameBorderWeightMap[borderWeight];
                range.Borders[XlBordersIndex.xlInsideVertical].Color = borderColor;
            }
            catch
            {
                object borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromName("Black"));
                range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, borderColor);
                range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlDot;
                range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlHairline;
                range.Borders[XlBordersIndex.xlInsideHorizontal].Color = borderColor;
                range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlDot;
                range.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlHairline;
                range.Borders[XlBordersIndex.xlInsideVertical].Color = borderColor;
            }
        }

public void SetHorizontalAlignment(string startCell, string endCell, string hAlign)
        {
            Range range = worksheet.get_Range(startCell, endCell);

try
            {
                range.HorizontalAlignment = nameHAlignMap[hAlign];
            }
            catch
            {
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            }
        }

public void SetVerticalAlignment(string startCell, string endCell, string vAlign)
        {
            Range range = worksheet.get_Range(startCell, endCell);

try
            {
                range.VerticalAlignment = nameVAlignMap[vAlign];
            }
            catch
            {
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            }
        }

public void AutoFitRow(string cell)
        {
            Range range = worksheet.get_Range(cell, cell);
            range.EntireRow.AutoFit();
        }

public void AutoFitColumn(string cell)
        {
            Range range = worksheet.get_Range(cell, cell);
            range.EntireColumn.AutoFit();
        }

public void SetRowHeight(string cell, float height)
        {
            Range range = worksheet.get_Range(cell, cell);
            range.RowHeight = height;
        }

public void SetColumnWidth(string cell, float width)
        {
            Range range = worksheet.get_Range(cell, cell);
            range.ColumnWidth = width;
        }
    }
}

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.