Excel + C# - Let's Play with it!

Programar no Office está cada vez melhor, graças as bibliotecas OpenXML. Vamos brincar um pouco com a criação e manipulação de um arquivo Excel.

Primeiro vamos baixar a biblioteca OpenXML (2.0) e adicioná-la no Visual Studio

Baixe o instalador Open XML SDK 2.0 for Microsoft Office. Baixe os dois arquivos [msi], sendo que usaremos no VS o arquivo OpenXMLSDKv2.msi .

Eu já expliquei um pouco o uso desta tecnologia aqui.

Vamos então criar um novo projeto no VS (CONSOLE Application) e adicionar a referência da biblioteca [DocumentFormat.OpenXml] como ilustrado na figura abaixo



Para criar o arquivo Excel, utilize a seguinte função especificada abaixo. Não esqueça antes as seguintes referências no código:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

private static void CriarTemplate(string template)
{
 using (var doc = SpreadsheetDocument.Create(template, SpreadsheetDocumentType.Workbook))
 {
  doc.AddWorkbookPart().AddNewPart<WorksheetPart>().Worksheet = new Worksheet(new SheetData());

  WorkbookStylesPart stylesPartDef = doc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
  stylesPartDef.Stylesheet = GenerateStyleSheet();
  stylesPartDef.Stylesheet.Save();

  doc.WorkbookPart.Workbook =
    new Workbook(
   new Sheets(
     new Sheet
     {
      Id = doc.WorkbookPart.GetIdOfPart(doc.WorkbookPart.WorksheetParts.First()),
      SheetId = 1,
      Name = "Sheet1"
     }));

  string relId = doc.WorkbookPart.Workbook.Descendants<Sheet>().First().Id;
  var wSheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(relId);

  //var mergeCells = new MergeCells();
  //wSheetPart.Worksheet.InsertAfter(mergeCells, wSheetPart.Worksheet.Elements<SheetData>().First());
 }
}

Faça a chamada do método CriarTemplate para criar o arquivo excel

static void Main(string[] args)
{
 string template = @"C:\TEMP\Test.xlsx";
 CriarTemplate(template);
}

Com isto já podemos manipular o template. Vamos abrí-lo e criar um conteúdo simples na célula [A1]

private static void ManipularTemplate(string template)
{
 using (SpreadsheetDocument documento = SpreadsheetDocument.Open(template, true))
 {
  var wBookPart = documento.WorkbookPart;
  string relId = wBookPart.Workbook.Descendants<Sheet>().First().Id;
  var wSheetPart = (WorksheetPart)wBookPart.GetPartById(relId);

  // CRIA A LINHA 1 COM MERGE DE COLUNAS (1 a 10)...
  var rowIndex = 1;
  var row = new Row() { RowIndex = UInt32.Parse(rowIndex.ToString()) };
  row.Append(CreateCell(1, rowIndex, 0, CellValues.String, "LINHA 1"));
  wSheetPart.Worksheet.First().AppendChild(row);

  //fnMergeCells(wSheetPart, rowIndex, 1, 10);

  wSheetPart.Worksheet.Save();
 }
}

Adicione as classes de apoio, para criação da célula, merge de células e conversão de número de coluna em letras.

private static Cell CreateCell(int colIndex, int rowIndex, int styleIndex, CellValues cellValues, string cellValueText)
{
 var cell = new Cell() { CellReference = getCellReference(colIndex) + rowIndex.ToString(), StyleIndex = (UInt32Value)(UInt32)styleIndex, DataType = cellValues };
 var cellValue = new CellValue();
 cellValue.Text = cellValueText;
 cell.Append(cellValue);
 return cell;
}

private static void fnMergeCells(WorksheetPart wSheetPart, int rowIndex, int de_Min, int ate_Max)
{
 var mergeCell = new MergeCell() { Reference = new StringValue(getCellReference(de_Min) + rowIndex.ToString() + ":" + getCellReference(ate_Max + 1) + rowIndex.ToString()) };

 var mergeCells = wSheetPart.Worksheet.Elements<MergeCells>().First();
 mergeCells.Append(mergeCell);

 wSheetPart.Worksheet.Save();
}

private static StringValue getCellReference(int colIndex)
{
 string columnString = "";
 decimal columnNumber = colIndex;
 while (columnNumber > 0)
 {
  decimal currentLetterNumber = (columnNumber - 1) % 26;
  char currentLetter = (char)(currentLetterNumber + 65);
  columnString = currentLetter + columnString;
  columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
 }
 return columnString;
}

private static Stylesheet GenerateStyleSheet()
{
 var styleSheet = new Stylesheet();

 var fonts = new Fonts();
 var fontDef = new Font(
      new FontSize() { Val = 11 },
      new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
      new FontName() { Val = "Calibri" });

 fonts.Append(fontDef);

 var fills = new Fills(
     new Fill(
      new PatternFill() { PatternType = PatternValues.None }
     ),
     new Fill(
      new PatternFill() { PatternType = PatternValues.Gray125 }
     ),
  // BLUE COLOR
     new Fill(
      new PatternFill(
       new ForegroundColor() { Theme = (UInt32Value)4U, Tint = 0.39997558519241921D },
       new BackgroundColor() { Indexed = (UInt32Value)64U }
      ) { PatternType = PatternValues.Solid }
     )
    );

 var borders = new Borders(
      new Border(new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder())
      );

 // CellFormats IS THE CLASS USED IN THE PROPERTIES [StyleIndex] FOR ALL OBJECTS
 var cellFormats = new CellFormats(
  // StyleIndex[0] - Default
       new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 },
  // StyleIndex[1] - FONT BLUE
       new CellFormat() { FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true }
       );

 styleSheet.Append(fonts);
 styleSheet.Append(fills);
 styleSheet.Append(borders);
 styleSheet.Append(cellFormats);

 return styleSheet;
}

Efetue a chamada do método ManipularTemplate

static void Main(string[] args)
{
 string template = @"C:\TEMP\Test.xlsx";
 CriarTemplate(template);
 ManipularTemplate(template);
}

Não se esqueça de adicionar outra referência importante no projeto, a dll WindowsBase



Execute o projeto e terá como resultado (o projeto cria o arquivo em c:\temp)



Agora vamos trabalhar com merge e estilo de células. Vamos efetuar o merge das 10 primeiras células e pintá-la de azul. Primeiro, altere o método [Manipular Template] e altere a linha de

row.Append(CreateCell(1, rowIndex, 0, CellValues.String, "LINHA 1"));

PARA 

row.Append(CreateCell(1, rowIndex, 1, CellValues.String, "LINHA 1"));

Note que a diferença está no terceiro valor do parâmetro da função [CreateCell]. Ela indica qual StyleIndex usar. Para que serve o StyleIndex? Justamente para indicar qual Estilo aplicar na célula. Onde ficam estes estilos? Quando criamos o método [CriarTemplate] já especificamos os estilos na criação do arquivo, na linha

stylesPartDef.Stylesheet = GenerateStyleSheet();
stylesPartDef.Stylesheet.Save();

Analise o método GenerateStyleSheet. Nele criamos todos os estilos necessários como tipo de fonte, preenchimento e borda. Porém ainda precisamos reunir todos estes estilos para aplicar na StyleIndex das células. Por exemplo, eu preciso de um fonte Bold, da célula com preenchimento azul, da célula com borda, etc. Como fazer isto? Apenas use a classe [CellFormats]. Ela junta tudo para você, de forma que você pode criar quantas CellFormats forem necessárias. No nosso exemplo criamos os seguintes CellFormats

var cellFormats = new CellFormats(
 // StyleIndex[0] - Default
      new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 },
 // StyleIndex[1] - FONT BLUE
      new CellFormat() { FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true }
      );

Vamos aplicar agora o recurso de merge. Entre no método [ManipularTemplate] e habilite a seguinte linha comentada

fnMergeCells(wSheetPart, rowIndex, 1, 10);

Entre também no método [CriarTemplate] e habilite as linhas

var mergeCells = new MergeCells();
wSheetPart.Worksheet.InsertAfter(mergeCells, wSheetPart.Worksheet.Elements<SheetData>().First());

E o resultado agora



That's It!
[]s

>> VS.NET 2010 - Snippet Designer Plugin

Realmente facilita a "vida" ter a possibilidade de inserção de um código template complexo e longo com apenas uma chamada de [Shortcut no Code View] do Visual Studio. Este conceito é muito usado e se chama [Code Snippet].

Basicamente para criar um [fragmento de código] efetuarmos os seguintes passos:

1 - Criamos um arquivo XML;
2 - Primeiro adicionamos o Elemento CodeSnippets;
3 - Logo após adicionamos a seção Header;
4 - Depois adicionamos mais um elemento (<Snippet>) que conterá o Código Template;
5 - E por fim, salvamos o arquivo com a extensão .SNIPPET, no diretório de snippets no Visual Studio, geralmente em [C:\Users\[UserName]\Documents\Visual Studio 2010\Code Snippets\Visual C#\My Code Snippets].

Segue exemplo dos passos seguidos acima:

<CodeSnippets
xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CodeSnippet Format="1.0.0">
<Header>
<Title>
My Snippet
</Title>
</Header>
<Snippet>
<Code Language="CSharp">
<![CDATA[MessageBox.Show("Hello World");]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

Porém, para facilitar ainda mais a vida do desenvolvedor, segue a dica do [Snippet Designer] que aumenta mais ainda a produtividade de criação de snippets, usando a IDE do Visual Studio.

Por exemplo, uma vez que você criou um código reutilizável, e gostaria de transformá-lo em um Snippet, com este Plugin basta selecionar seu código e exportá-lo como um snippet, como mostra a imagem abaixo:



Existem mais recursos interessantes, que estão documentados no link para baixar o Plugin.

>> CRM 4.0 - Manipulação de Anexos (SDK)

A versão 4.0 do Dynamics CRM simplificou para nós, desenvolvedores, o uso de diversos recursos do SDK. Um deles é a busca e manipulação de Anexos nas entidades do produto.

Para este caso, note que em cada Anotação (relacionada a uma entidade do CRM) existe um campo chamado documentbody que armazena o conteúdo do arquivo anexado pelo usuário (BASE64 Format).

Para demonstrar isto, segue função genérica (C#) que retorna as Anotações (de uma determinada Entidade) que contém anexos. A função retorna o conteúdo de cada anexo (pelo processo de Decoding do .NET).

P.S.: Lembrando que a função consegue ler o conteúdo de anexos no formato do CRM (BASE64). Arquivos com formatos diferentes precisam de tratamentos específicos para leitura. Exemplo, os arquivos do Office2007, como xlsx (http://michaelmalloy.blogspot.com/2008/04/c-read-excel-2007-xlsx-files.html).

Chamada da função que busca as Anotações:

BusinessEntityCollection notes = getEntityNotes(new Guid("272526EA-ED6B-43CC-A8DC-A2965405A463"));

foreach (BusinessEntity be in notes.BusinessEntities)
{
annotation annot = be as annotation;
if (!string.IsNullOrEmpty(annot.documentbody))
{
string attachBody = DecodeByteArryToString(annot.mimetype, annot.documentbody);
}
}

Função que busca as Anotações da Entidade:

public static BusinessEntityCollection getEntityNotes(Guid entityId)
{
// Set up the CRM Service.
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0;
token.OrganizationName = "OrgName";
CrmService service = new CrmService();
service.Url = "http://localhost:5555/mscrmservices/2007/crmservice.asmx";
service.CrmAuthenticationTokenValue = token;
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

BusinessEntityCollection notes = null;

ConditionExpression caseCondition = new ConditionExpression();
caseCondition.AttributeName = "objectid";
caseCondition.Operator = ConditionOperator.Equal;
caseCondition.Values = new object[] { entityId };

FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = new ConditionExpression[] { caseCondition };

QueryExpression query = new QueryExpression();
query.EntityName = EntityName.annotation.ToString();
query.ColumnSet = new AllColumns();
query.Criteria = filter;

RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();
retrieve.Query = query;

RetrieveMultipleResponse retrieved = (RetrieveMultipleResponse)service.Execute(retrieve);
notes = retrieved.BusinessEntityCollection;

return notes;
}

public static string DecodeByteArryToString(string mimeType, string documentbody)
{
Byte[] ByteArry = System.Convert.FromBase64String(documentbody);
Decoder byteArryDecoder = null;

if (mimeType.IndexOf("text/") >= 0)
{
byteArryDecoder = Encoding.UTF7.GetDecoder();
}
else
{
byteArryDecoder = Encoding.Unicode.GetDecoder();
}

int charCount = byteArryDecoder.GetCharCount(ByteArry, 0, ByteArry.Length);
char[] bodyChars = new Char[charCount];
int charsDecodedCount = byteArryDecoder.GetChars(ByteArry, 0, ByteArry.Length, bodyChars, 0);
return new string(bodyChars);
}

>> Busca do ID de um Web Site no IIS

Segue código Template para encontrar o ID de um determinado projeto Web no IIS:

<< C# - VS.NET 2005 - Console Application >>

using System.DirectoryServices;
using System;

public class IISAdmin
{
public static void GetWebsiteID(string websiteName)
{
DirectoryEntry w3svc = new DirectoryEntry("IIS://localhost/w3svc");

foreach(DirectoryEntry de in w3svc.Children)
{
if(de.SchemaClassName == "IIsWebServer" && de.Properties["ServerComment"][0].ToString() == websiteName)
{
Console.Write(de.Name);
}

}

}
public static void Main()
{
GetWebsiteID("Default Web Site");
}

}

>> .NET 2.0 - Chamada Web Services - Sem "Add Web References"

Segue exemplo de código para chamada a Web Services sem a necessidade de adição da referência web no projeto.

Basta passar para a função "CallWebServices" a URL, o nome da função e os parâmetros do Web Services.

Exemplo de uso da função:

using System.Xml;
using System.Collections;
using System.Collections.Generic;
using System.Text;

List<'Hashtable> oParams = new List<'Hashtable>();
Hashtable ht1 = new Hashtable();
ht1.Add("paramName", "productid");
ht1.Add("paramValue", "413D080B-09A7-DB11-89C7-0016356BE094");
oParams.Add(ht1);

string xml = CallWebServices("http://localhost/MyWS/product.asmx", "GetProductPriceList", oParams);

public static string CallWebServices(string url, string functionName, List<'Hashtable> oParams)
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
request.Credentials = System.Net.CredentialCache.DefaultCredentials;
request.Headers.Add("SOAPAction", "\"http://tempuri.org/" + functionName + "\"");
request.Method = "POST";
request.ContentType = "text/xml; charset=utf-8";
request.Accept = "text/xml";
request.Timeout = 10000;
Stream requestStream = request.GetRequestStream();
string soapEnvelope = "";
soapEnvelope += " soapEnvelope += " xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"";
soapEnvelope += " xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"";
soapEnvelope += " xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\">";
soapEnvelope += " ";
soapEnvelope += " <" + functionName + " xmlns=\"http://tempuri.org/\">";
if (oParams.Count > 0)
{
Boolean hashListOk = (oParams[0].Contains("paramName") && oParams[0].Contains("paramValue"));
if (!hashListOk) return string.Empty;
foreach (Hashtable ht in oParams)
{
soapEnvelope += "<" + ht["paramName"].ToString() + ">" + ht["paramValue"].ToString() + "";
}
}
soapEnvelope += " ";
soapEnvelope += " ";
soapEnvelope += " ";
// Convert the string into a byte array.
ASCIIEncoding encoder = new ASCIIEncoding();
byte[] ByteArray = encoder.GetBytes(soapEnvelope);
// Write data to the stream.
requestStream.Write(ByteArray, 0, ByteArray.Length);
requestStream.Flush();
requestStream.Close();
StreamReader esr = null;
string result = string.Empty;
try
{
esr = new StreamReader(request.GetResponse().GetResponseStream());
result = esr.ReadToEnd();
}
catch (System.Web.Services.Protocols.SoapException ex)
{
string x = ex.Detail.InnerText;
}
catch (Exception ex)
{
string x = ex.Message;
}
return result;
}