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 static void ApplyRangeFormat(Excel.ListColumns cols, List <string> formats)
{
int i = 0;
foreach (Excel.ListColumn col in cols)
{
if (i >= formats.Count)
{
return;
}
col.Range.NumberFormat = formats[i++];
}
}
private DataTable GetExcelTable(string sheetName, string tableName)
{
DataTable dt = null;
XL.Application xlApp = new XL.Application();
XL.Workbook wb = xlApp.Workbooks.Open(Path);
XL.Worksheet ws = (XL.Worksheet)wb.Sheets[sheetName];
XL.ListObject lo = ws.ListObjects[tableName];
XL.ListColumns listColumns = lo.ListColumns;
XL.ListRows listRows = lo.ListRows;
wb.Close();
return(dt);
}
private void LoadExcelTable()
{
string workBookName = @"B:\Publish\SupportTools_Visio\TestData.xlsx";
string workSheetName = "Sheet2";
string tableName = "tbl_Data";
XL.Application xlApp = new XL.Application();
XL.Workbook wb = xlApp.Workbooks.Open(workBookName);
XL.Worksheet ws = wb.Sheets[workSheetName];
XL.ListObject lo = ws.ListObjects[tableName];
XL.ListColumns listColumns = lo.ListColumns;
XL.ListRows listRows = lo.ListRows;
VisioHlp.DisplayInWatchWindow(string.Format("{0}\n", tableName));
foreach (XL.ListColumn col in listColumns)
{
VisioHlp.DisplayInWatchWindow(col.Name);
}
tableName = "tbl_Data2";
lo = ws.ListObjects[tableName];
listColumns = lo.ListColumns;
listRows = lo.ListRows;
VisioHlp.DisplayInWatchWindow(string.Format("{0}\n", tableName));
foreach (XL.ListColumn col in listColumns)
{
VisioHlp.DisplayInWatchWindow(col.Name);
}
foreach (XL.ListRow row in listRows)
{
VisioHlp.DisplayInWatchWindow(row.ToString());
}
wb.Close();
}
/// <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);
}