sexta-feira, 19 de setembro de 2014

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