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); }