/// <summary>
/// Adiciona uma nova worksheet a uma workbook existente
/// </summary>
/// <param name="xWorkbook">workbook</param>
/// <param name="xWorksheet">nova worksheet</param>
private void updateWorkbook(XWorkbook xWorkbook, XWorksheet xWorksheet)
{
List <XWorksheet> xWorksheets = xWorkbook.getWorksheets();
xWorksheets.Add(xWorksheet);
xWorkbook.setWorksheets(xWorksheets);
}
private XWorksheet GetXReference(XWorkbook xWorkbook, string xreference, ref XDataTable targettable)
{
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
targettable = xWorksheet.getDataTables().FirstOrDefault(d => d.getName().Equals(xreference));
if (targettable != null)
{
return(xWorksheet);
}
}
targettable = null;
return(null);
}
/// <summary>
/// Cria as fórmulas do workbook passado como parâmetro
/// </summary>
/// <param name="eBook"></param>
/// <param name="xWorkbook"></param>
private Excel.Workbook createFormulas(Excel.Workbook eBook, XWorkbook xWorkbook)
{
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()];
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.ListObject eListObject = eListObjects[xDataTable.getName()];
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = eListColumns[sheetColumn.getName()];
if (sheetColumn.getDataContent() is XTextExp)
{
Excel.Range rng = eListColumn.DataBodyRange;
XTextExp formula = (XTextExp)sheetColumn.getDataContent();
rng.Formula = string.Format("{0}", formula.getTextSymbol());
Marshal.ReleaseComObject(rng);
}
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(eListObjects);
}
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
/// <summary>
/// Cria validações de dados nas colunas de referência a tabelas
/// </summary>
/// <param name="eBook">workbook correspondente do excel (COM)</param>
/// <param name="xWorkbook">xworkbook</param>
private Excel.Workbook createValidation(Excel.Workbook eBook, XWorkbook xWorkbook)
{
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = eBook.Sheets[xWorksheet.getName()];
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.ListObject eListObject = eListObjects[xDataTable.getName()];
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = eListColumns[sheetColumn.getName()];
string xreference = sheetColumn.getXReference();
if (xreference != null)
{
var targettable = new XDataTable();
var targetsheet = GetXReference(xWorkbook, xreference, ref targettable);
if (targettable != null)
{
var index = targettable.getKeyIndex();
eWorksheet = eBook.Sheets[targetsheet.getName()];
eListObjects = eWorksheet.ListObjects;
eListObject = eListObjects[targettable.getName()];
eListColumns = eListObject.ListColumns;
eListColumn = eListColumns[index];
string rangename = targettable.getName() + index.ToString(CultureInfo.InvariantCulture);
eBook.Names.Add(rangename, eListColumn.DataBodyRange);
Excel.Name targetName = eBook.Names.Item(rangename, Type.Missing, Type.Missing);
string nameLocal = "=" + targetName.NameLocal;
eWorksheet = eBook.Sheets[xWorksheet.getName()];
eListObjects = eWorksheet.ListObjects;
eListObject = eListObjects[xDataTable.getName()];
eListColumns = eListObject.ListColumns;
eListColumn = eListColumns[sheetColumn.getName()];
eListColumn.DataBodyRange.Validation.Add(Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertStop, Missing.Value,
nameLocal, Missing.Value);
}
Marshal.ReleaseComObject(eListColumn);
}
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(eListObjects);
}
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
/// <summary>
/// Cria a workbook e todos os seus objetos no formato do Excel (COM)
/// </summary>
/// <param name="eApp"></param>
/// <param name="xWorkbook"></param>
private Excel.Workbook createWorkbook(Excel.Application eApp, XWorkbook xWorkbook)
{
Excel.Workbook eBook = eApp.Workbooks.Add();
eBook.Title = xWorkbook.getName();
PrepareWorkbook(eBook);
int numWorksheets = 1;
foreach (XWorksheet xWorksheet in xWorkbook.getWorksheets())
{
Excel.Worksheet eWorksheet = numWorksheets == 1
? (Excel.Worksheet)eBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value)
: (Excel.Worksheet)eBook.Worksheets.Add(Missing.Value, eBook.Worksheets[numWorksheets - 1], Missing.Value, Missing.Value);
eWorksheet.Name = xWorksheet.getName();
int i = 1;
foreach (XDataTable xDataTable in xWorksheet.getDataTables())
{
Excel.Range title = eWorksheet.Cells[i, 1];
title.Value = xDataTable.getName();
Excel.Font titleFont = title.Font;
titleFont.Bold = true;
titleFont.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
Marshal.ReleaseComObject(titleFont);
Marshal.ReleaseComObject(title);
Excel.ListObjects eListObjects = eWorksheet.ListObjects;
Excel.Range bBegin = (Excel.Range)eWorksheet.Cells[++i, 1];
Excel.ListObject eListObject = eListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, bBegin, Missing.Value, Excel.XlYesNoGuess.xlNo, Missing.Value);
eListObject.Name = xDataTable.getName() ?? "";
eListObject.ShowTotals = false;
eListObject.ListRows.Add();
eListObject.ListRows.Add();
int columns = 1;
foreach (XDataTableColumn sheetColumn in xDataTable.getDataTableColumns())
{
Excel.ListColumns eListColumns = eListObject.ListColumns;
Excel.ListColumn eListColumn = columns == 1 ? eListColumns[1] : eListColumns.Add();
eListColumn.Name = sheetColumn.getName();
eListColumn.TotalsCalculation = Excel.XlTotalsCalculation.xlTotalsCalculationSum;
eListColumn.Range.EntireColumn.ColumnWidth = 14;
if (xDataTable.getDataTableColumns().Count == 1 &&
xDataTable.getDataTableColumns()[0].getDataContent() != null)
{
var datacontent = xDataTable.getDataTableColumns()[0].getDataContent();
if (datacontent is XDataArray)
{
var datacontentimpl = (XDataArray)datacontent;
var arraysymbol = datacontentimpl.getArray();
int numSymbols = 0;
foreach (var symbol in arraysymbol)
{
if (eListColumn.DataBodyRange.Count < numSymbols)
{
eListObject.ListRows.Add();
}
eListColumn.DataBodyRange[numSymbols + 1] = symbol;
numSymbols++;
}
}
}
columns++;
Marshal.ReleaseComObject(eListColumn);
Marshal.ReleaseComObject(eListColumns);
}
Marshal.ReleaseComObject(eListObject);
Marshal.ReleaseComObject(bBegin);
Marshal.ReleaseComObject(eListObjects);
}
numWorksheets++;
Marshal.ReleaseComObject(eWorksheet);
}
return(eBook);
}
/// <summary>
/// Retorna a tabela correspondente a determinado classifier
/// </summary>
/// <param name="xWorkbook">workbook onde se encontra tabela</param>
/// <param name="classifier">classifier correspondente a tabela</param>
/// <returns>Se encontrar, retorna a referência para a tabela, caso contrário, retorna null</returns>
private XDataTable getTargetTable(XWorkbook xWorkbook, CoreClassifier classifier)
{
return(xWorkbook.getWorksheets()
.SelectMany(xWorksheet => xWorksheet.getDataTables())
.FirstOrDefault(xDataTable => xDataTable.getName().Equals(classifier.getName())));
}