internal string addProductionColumn(Excel.Worksheet WS, Excel.ListObject LO, string dataListName)
{
string colName = "";
string formula = "";
Excel.ListObject dataList = ExcelHelpers.GetListObject(WS, dataListName);
object[,] headers = dataList.HeaderRowRange.Value2 as object[, ];
string dttbl = dataListName;
string dtyearcol = ExcelHelpers.GetListColumn(dataList, EnPIResources.yearColName).Name;
string yearcol = ExcelHelpers.GetListColumn(LO, EnPIResources.yearColName).Name;
int i = headers.GetLowerBound(0);
for (int j = headers.GetLowerBound(1); j <= headers.GetUpperBound(1); j++)
{
if (DataHelper.isProduction(headers[i, j].ToString()))
{
formula += "SUM(" + dttbl + ExcelHelpers.CreateValidFormulaName(headers[i, j].ToString()) + " " +
DataHelper.RowRangebyMatch(dttbl, dtyearcol, "[[#This Row]," + yearcol + "]", dataSheetName)
+ ")" + "+";
}
}
if (formula != "")
{
Excel.ListColumn newCol = LO.ListColumns.Add(2);
colName = "Production";
newCol.Name = colName;
newCol.DataBodyRange.Formula = "=" + formula.Substring(0, formula.Length - 1);
newCol.DataBodyRange.Style = "Comma [0]";
}
return(colName);
}
internal void addSumColumn(params string[] prepend)
{
string colName = Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName");
string formula = "=";
string format = "General";
string prefix = "";
if (prepend.Length > 0)
{
prefix = prepend[0];
colName = Globals.ThisAddIn.rsc.GetString("totalAdjValuesColName");
}
foreach (Utilities.EnergySource es in DS.EnergySources)
{
if (es.Name != Globals.ThisAddIn.rsc.GetString("unadjustedTotalColName"))
{
formula += Utilities.ExcelHelpers.CreateValidFormulaName(prefix + es.Name) + "+";
if (Utilities.ExcelHelpers.GetListColumn(SourceObject, es.Name) != null)
{
format = Utilities.ExcelHelpers.GetListColumn(SourceObject, es.Name).DataBodyRange.NumberFormat.ToString();
}
}
}
if (formula.LastIndexOf("+") > 0)
{
formula = formula.Substring(0, formula.LastIndexOf("+"));
}
Excel.ListColumn newcol = Utilities.ExcelHelpers.AddListColumn(AdjustedData, colName);
newcol.Range.get_Offset(1, 0).get_Resize(newcol.Range.Rows.Count - 1, 1).Value2 = formula;
newcol.Range.get_Offset(1, 0).NumberFormat = format ?? "General";
}
public static void SetYear(Excel.ListObject LO, Excel.Range Sel, int YearNo)
{
Excel.ListColumn yearCol = GetListColumn(LO, EnPIResources.yearColName);
if (yearCol == null)
{
yearCol = AddListColumn(LO, EnPIResources.yearColName, 1);
}
// the selection
int firstRow = Sel.Row;
// if there are header rows in the selection or the selection starts above the header row, return
// we don't know where we are on the sheet relative to the list object
if (Sel.ListHeaderRows > 0 || firstRow < LO.HeaderRowRange.Row)
{
return;
}
// get the corresponding range in the year column
Excel.Range yearRows = yearCol.Range.get_Offset(firstRow - LO.HeaderRowRange.Row, 0).get_Resize(Sel.Rows.Count, 1);
yearRows.Value2 = YearNo;
yearRows.NumberFormat = "@";
}
public static Excel.ListColumn GetListColumn(Excel.ListObject LO, String ColName)
{
Excel.ListColumn thisColumn = null;
if (ColName == null || LO == null)
{
return(null);
}
string thisName = ColName.Replace("\\", "/");
foreach (Excel.ListColumn i in LO.ListColumns)
{
if (i.Name.ToLower() == thisName.ToLower())
{
thisColumn = i;
}
}
//if (thisColumn == null)
//{
// thisColumn = AddListColumn(LO, thisName, Pos);
//}
return(thisColumn);
}
public static Excel.Range getYearRange(Excel.ListObject LO, string year)
{
Excel.ListColumn LCyear = GetListColumn(LO, EnPIResources.yearColName);
int first = 0;
int last = 0;
int j = 0;
if (LCyear != null)
{
foreach (Excel.Range row in LCyear.Range.Rows)
{
j += 1;
if (row.Value2.ToString() == year && first == 0)
{
first = j;
}
if (row.Value2.ToString() == year)
{
last = j;
}
}
}
if (first != 0)
{
return(LO.Range.get_Offset(first - 1, 0).get_Resize(last - (first - 1), LO.ListColumns.Count));
}
else
{
return(null);
}
}
private void ExecuteInternal()
{
var workbookContext = App.Context.GetActiveWorkbookContext();
Excel.Workbook workbook = workbookContext.Workbook;
Excel.Worksheet worksheet = ExcelHelper.WorksheetAdd(workbook);
ExcelHelper.WorksheetActivate(worksheet);
string name = ListObjectHelper.NewTestDataName(workbook);
worksheet.Name = name;
Excel.ListObject listObject = ListObjectHelper.AddListObject(worksheet);
listObject.Name = name;
listObject.ListColumns[1].Name = "Column1";
listObject.ListColumns[1].Range.EntireColumn.AutoFit();
for (int i = 2; i < 10; i++)
{
Excel.ListColumn listColumn = listObject.ListColumns.Add();
listColumn.Name = string.Format(
CultureInfo.CurrentCulture,
"Column{0}",
i);
listColumn.Range.EntireColumn.AutoFit();
}
ListObjectHelper.AddRows(listObject, DEFAULT_ROW_COUNT, false);
ListObjectHelper.SelectCell(listObject, 2, 1);
}
private void AddTableColumn(System.Data.DataTable dtt, Excel.ListColumn listColumn)
{
Type columnType = typeof(string);
switch (listColumn.ListDataFormat.Type)
{
case Excel.XlListDataType.xlListDataTypeNumber:
columnType = typeof(decimal);
break;
case Excel.XlListDataType.xlListDataTypeCurrency:
columnType = typeof(decimal);
break;
case Excel.XlListDataType.xlListDataTypeCheckbox:
columnType = typeof(bool);
break;
case Excel.XlListDataType.xlListDataTypeDateTime:
columnType = typeof(DateTime);
break;
default:
break;
}
dtt.Columns.Add(listColumn.Name, columnType);
}
private void updateRowFYFormula(int Intervals, Excel.ListColumn LC, int rowStart)
{
int j = 1, i = 0;
foreach (Excel.Range row in LC.Range.Rows)
{
if (i > rowStart)
{
row.Formula = "=" + "\"" + "FY" + j + "\"";
}
if (i > 0 && i < rowStart)
{
row.Formula = "";
}
if (i == Intervals + rowStart)
{
j += 1;
i = 0;
rowStart = 0;
}
i += 1;
}
}
private void btnRun_Click(object sender, EventArgs e)
{
try
{
Excel.Worksheet thisSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;
Excel.ListObject thisList = ExcelHelpers.GetListObject(thisSheet);
int orgCount = thisList.ListColumns.Count;
foreach (string t in this.checkedListBox1.CheckedItems)
{
int lcCount = thisList.ListColumns.Count;
int position = this.checkedListBox1.Items.IndexOf(t) + (lcCount - orgCount) + 2;
string colName = t + "(" + this.comboBox2.SelectedItem.ToString() + ")";
string formula = "=" + ExcelHelpers.CreateValidFormulaName(t) + "*" + this.textBox1.Text + "*" + this.textBox2.Text;
string stylename = "Comma";
Excel.ListColumn newColumn = thisList.ListColumns.Add(position);
newColumn.Name = colName;
newColumn.DataBodyRange.Formula = formula;
newColumn.DataBodyRange.Style = stylename;
//set old column property to not show up in regression/actual options
}
this.checkedListBox1.Items.Clear();
AddColumnNames();
}
catch
{
//raise error
}
}
public ProtoContact(Excel.ListColumns columns, Excel.ListRow data)
{
IEnumerator columnEnumerator = columns.GetEnumerator();
StringBuilder address = new StringBuilder();
foreach (Excel.Range c in data.Range)
{
columnEnumerator.MoveNext();
Excel.ListColumn col = (Excel.ListColumn)columnEnumerator.Current;
switch (col.Name)
{
case "First":
First = ((String)c.Value ?? "").Trim();
break;
case "Last":
Last = ((String)c.Value ?? "").Trim();
break;
case "Birthday":
Birthday = c.Value;
break;
case "Home":
HomePhone = ((String)c.Value ?? "").Trim();
break;
case "Cell":
CellPhone = ((String)c.Value ?? "").Trim();
break;
case "Email":
Email = ((String)c.Value ?? "").Trim();
break;
case "Work Email":
WorkEmail = ((String)c.Value ?? "").Trim();
break;
case "Address 1":
Address1 = ((String)c.Value ?? "").Trim();
address.Append(Address1);
break;
case "Address 2":
Address2 = ((String)c.Value ?? "").Trim();
if (Address2 != "")
{
address.Append('\n');
address.Append(Address2);
}
break;
default:
throw new Exception("Unknown Table Column " + col.Name);
}
}
Address = address.ToString();
}
private void updateCalenderYear(Excel.ListObject LO, Excel.ListColumn LC, int rowStart)
{
Excel.Range LCDate = ExcelHelpers.GetListColumn(LO, "Date").Range;
if (LCDate == null)
{
LCDate = ExcelHelpers.AddListColumn(LO, "Date", 1).Range;
}
updateRowCYFormula(LO, LC, rowStart);
}
/// <summary>
/// Check to see if there is a column in the table
/// </summary>
/// <param name="tbl"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static bool IsValidListColumn(Excel.ListObject tbl, string columnName)
{
try
{
Excel.ListColumn col = tbl.ListColumns[columnName];
return(true);
}
catch (Exception)
{
return(false);
}
}
public static string GetListColumnName(Excel.ListObject LO, String ColName)
{
Excel.ListColumn thisColumn = null;
thisColumn = GetListColumn(LO, ColName);
if (thisColumn == null)
{
return(null);
}
return(thisColumn.Name);
}
public static Excel.ListColumn AddColumn(Excel.ListObject listObject, string columnName)
{
if (null == listObject)
{
throw new ArgumentNullException("listObject");
}
if (string.IsNullOrWhiteSpace(columnName))
{
throw new ArgumentNullException("columnName");
}
Excel.ListColumn listColumn = listObject.ListColumns.Add();
listColumn.Name = columnName;
listColumn.Range.EntireColumn.AutoFit();
return(listColumn);
}
internal void addAdjustedEnergyIntensity()
{
if (DS.ProductionVariables != null)
{
string formula = "";
for (int j = 0; j < DS.ProductionVariables.Count(); j++)
{
formula += SubtotalColumnFormula("SUM", DS.ProductionVariables[j].ToString(), yr(), AdjustedDataSheet.Name) + "+";
}
if (formula != "")
{
Excel.ListColumn newCol = SummaryData.ListColumns.Add(2);
newCol.Name = Globals.ThisAddIn.rsc.GetString("adjustedEnergyIntensName");;
newCol.DataBodyRange.Value2 = "=" + that() + "/" + formula.Substring(0, formula.Length - 1);
newCol.DataBodyRange.Style = "Comma [0]";
}
}
}
private void updateRowCYFormula(Excel.ListObject LO, Excel.ListColumn LC, int rowStart)
{
int i = 0;
foreach (Excel.Range row in LC.Range.Rows)
{
if (i > rowStart)
{
string addrStr = dateaddr.ToString().Substring(1, dateaddr.ToString().IndexOf("$", 1) - 1).ToString() + (i + 1);
row.Formula = "=YEAR([Date])";
}
if (i > 0 && i <= rowStart)
{
row.Formula = "";
}
i += 1;
}
}
static public Excel.ListObject AddSourceSumColumn(Excel.ListObject LO)
{
Excel.ListColumn sum = GetListColumn(LO, EnPIResources.totalSourceColName);
if (sum == null)
{
sum = AddListColumn(LO, EnPIResources.totalSourceColName, 0);
}
string strTotal = "";
foreach (Excel.ListColumn col in LO.ListColumns)
{
string colNameclean = ExcelHelpers.CreateValidFormulaName(col.Name);
if (DataHelper.targetTable((object[, ])col.Range.Value2) == Constants.COLUMNTAG_DVS)
{
strTotal += colNameclean + "+";
}
}
sum.Range.Formula = "= " + strTotal.Substring(0, strTotal.Length - 1);
return(LO);
}
private void updateFiscialYear(string interval, Excel.ListColumn LC, int rowStart)
{
//string formula = "=";
//TFS Ticket : 70984
if (interval == Constants.INTERVAL_TYPE_DAILY)
{
//formula = formula + "\"" + "FY1" + "\"";
//LC.DataBodyRange.Formula = formula;
updateRowFYFormula(Constants.INTERVAL_TYPE_DAYS_COUNT, LC, rowStart);
}
if (interval == Constants.INTERVAL_TYPE_MONTHLY)
{
updateRowFYFormula(Constants.INTERVAL_TYPE_MONTH_COUNT, LC, rowStart);
}
if (interval == Constants.INTERVAL_TYPE_WEEKLY)
{
updateRowFYFormula(Constants.INTERVAL_TYPE_WEEK_COUNT, LC, rowStart);
}
}
public void AddPingColumn()
{
try
{
Excel.ListColumn colResults;
if (ErrorHandler.IsValidListObject())
{
Excel.ListObject tbl = Globals.ThisAddIn.Application.ActiveCell.ListObject;
if (ErrorHandler.IsValidListColumn(tbl, Properties.Settings.Default.Ping_Results))
{
colResults = tbl.ListColumns[Properties.Settings.Default.Ping_Results];
}
else
{
colResults = tbl.ListColumns.Add();
colResults.Name = Properties.Settings.Default.Ping_Results;
}
if (ErrorHandler.IsValidListColumn(tbl, Properties.Settings.Default.Ping_ServerName))
{
Excel.ListColumn colServer = tbl.ListColumns[Properties.Settings.Default.Ping_ServerName];
for (int r = 1; r <= tbl.ListRows.Count; r++)
{
if (colServer.DataBodyRange.Rows[r].EntireRow.Hidden == false)
{
Excel.Range cellResult = colResults.DataBodyRange.Cells[1].Offset(r - 1, 0);
Excel.Range cellServer = colServer.DataBodyRange.Cells[1].Offset(r - 1, 0);
cellResult.Value = Ribbon.GetPingResult(cellServer.Value.ToString());
}
}
}
}
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
}
public static object[,] getYearArray(Excel.ListObject LO, ArrayList years)
{
Excel.ListColumn LCyear = GetListColumn(LO, EnPIResources.yearColName);
int j = 0;
object[,] LOyears = null;
if (LCyear != null)
{
foreach (Excel.Range row in LCyear.Range.Rows)
{
string tmp1 = row.Value2.ToString();
if (years.Contains(row.Value2.ToString()))
{
LOyears = DataHelper.arrayAppend(LOyears, LO.ListRows[j].Range.Value2 as object[, ]);
}
j++;
}
}
return(LOyears);
}
/// <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);
}
internal void addEIColumn(Excel.Worksheet WS, Excel.ListObject LO, string dataListName)
{
string formula = "";
string newname = rsc.GetString("buildingEnPIColName");
string tbl = LO.Name;
string bsqfcol = rsc.GetString("buildingSQFColName");
string colhat = rsc.GetString("totalAdjValuesColName");
string that; //adjusted current year energy consumption
string bsqf; //building square feet
that = tbl + "[[#This Row]," + ExcelHelpers.CreateValidFormulaName(colhat) + "]";
bsqf = tbl + "[[#This Row]," + ExcelHelpers.CreateValidFormulaName(bsqfcol) + "]";
formula = "=IFERROR(" + that + "/" + bsqf + ",0)";
if (bsqfcol != "")
{
Excel.ListColumn newCol = LO.ListColumns.Add(missing);
newCol.Name = newname;
newCol.DataBodyRange.Formula = formula;
newCol.DataBodyRange.Style = "Comma";
}
}
static public Excel.ListColumn AddListColumn(Excel.ListObject LO, String ColName, params Int32[] Pos)
{
Excel.ListColumn thisColumn = null;
if (Pos.Length > 0 && Pos[0] > 0)
{
thisColumn = LO.ListColumns.Add(Pos);
}
if (Pos.Length > 0 && Pos[0] < 0)
{
return(thisColumn);
}
if (Pos.Length == 0 || Pos[0] == 0)
{
thisColumn = LO.ListColumns.Add(missing);
}
if (LO.ListColumns[1].Name == "Column1")
{
LO.ListColumns[1].Name = "Date";
}
for (int i = 1; i < LO.ListColumns.Count; i++)
{
if (LO.ListColumns[i].Name == ColName)
{
thisColumn.Name = ColName + "1";
}
else
{
thisColumn.Name = ColName;
}
}
thisColumn.Range.BorderAround(Excel.XlLineStyle.xlLineStyleNone, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexNone, missing);
return(thisColumn);
}
public void CreateRdgFile()
{
try
{
string quote = ((char)34).ToString();
string previousGroup = string.Empty;
string currentGroup = string.Empty;
if (ErrorHandler.IsValidListObject())
{
var script = new StringBuilder()
.AppendLine("<?xml version=" + quote + "1.0" + quote + " encoding=" + quote + "UTF-8" + quote + "?>")
.AppendLine("<RDCMan programVersion=" + quote + "2.7" + quote + " schemaVersion=" + quote + "3" + quote + ">")
.AppendLine("<file>")
.AppendLine("<credentialsProfiles />")
.AppendLine("<properties>")
.AppendLine("<expanded>True</expanded>")
.AppendLine("<name>" + Scripts.AssemblyInfo.Title + "</name>")
.AppendLine("</properties>");
Excel.ListObject tbl = Globals.ThisAddIn.Application.ActiveCell.ListObject;
Excel.ListColumn colServer = tbl.ListColumns[Properties.Settings.Default.Rdg_ServerName];
Excel.ListColumn colDesc = tbl.ListColumns[Properties.Settings.Default.Rdg_Description];
Excel.ListColumn colComment = tbl.ListColumns[Properties.Settings.Default.Rdg_Comment];
Excel.ListColumn colServerGroup = tbl.ListColumns[Properties.Settings.Default.Rdg_ServerGroup];
for (int r = 1; r <= tbl.ListRows.Count; r++)
{
if (colServer.DataBodyRange.Rows[r].EntireRow.Hidden == false)
{
Excel.Range cellServer = colServer.DataBodyRange.Cells[1].Offset(r - 1, 0);
Excel.Range cellDesc = colDesc.DataBodyRange.Cells[1].Offset(r - 1, 0);
Excel.Range cellComment = colComment.DataBodyRange.Cells[1].Offset(r - 1, 0);
Excel.Range cellServerGroup = colServerGroup.DataBodyRange.Cells[1].Offset(r - 1, 0);
currentGroup = cellServerGroup.Value;
if (currentGroup != previousGroup)
{
script.AppendLine("<group>");
script.AppendLine("<properties>");
script.AppendLine("<expanded>True</expanded>");
script.AppendLine("<name>" + currentGroup + "</name>");
script.AppendLine("</properties>");
}
script.AppendLine("<server>");
script.AppendLine("<properties>");
script.AppendLine("<name>" + cellServer.Value + "</name>");
script.AppendLine("<displayName>" + cellServer.Value + " (" + cellDesc.Value + ")</displayName>");
script.AppendLine("<comment>" & cellComment.Value & "</comment>");
script.AppendLine("</properties>");
script.AppendLine("</server>");
if (currentGroup != colServerGroup.DataBodyRange.Cells[1].Offset(r, 0))
{
script.AppendLine("</group>");
}
}
previousGroup = currentGroup;
}
script.AppendLine("</file>");
script.AppendLine("<connected />");
script.AppendLine("<favorites />");
script.AppendLine("<recentlyUsed />");
script.AppendLine("</RDCMan>");
System.IO.File.WriteAllText(Properties.Settings.Default.Rdg_FileName, script.ToString());
}
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
}
/// <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>
/// 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);
}