Semplice esempio di VSTO Excel che utilizza un foglio di lavoro come origine dati

Penso che mi stia imbattendo in un caso di “le risposte più facili sono le più difficili da trovare” e non ho trovato nessuna ricerca che mi dia questo in modo diretto. Questo è per Excel 2010 e VS 2010 all’interno di un progetto VSTO (C #) esistente.

Ho un foglio di lavoro di Excel che contiene 4 colonne di dati che vorrei utilizzare come fonte per un DataGridView. Qualcuno può per favore fornire snippet di codice C # per (1) ottenere i dati da un particolare foglio di lavoro e popolarne un object personalizzato? (2) bind l’object (come un elenco IEnumerable) a un Datagridview e (3) alcuni frammenti per la funzionalità di aggiornamento ed eliminazione che sarebbero inerenti alla griglia e tornare al foglio di lavoro di origine.

So che sto chiedendo molto qui, ma gran parte delle informazioni sul VSTO sembra essere disgiunta e non sempre facile da trovare. Grazie!

Edit: Ottimo, ho appena notato che ho perso una grande parte della tua domanda, ottenendo aggiornamenti ed eliminazioni nel foglio di lavoro. Non ho assolutamente idea se sia ansible, ma penso che la mia soluzione sia inutile. Lo lascerò qui comunque, forse può aiutare in qualsiasi modo.


Perché hai bisogno di VSTO? Per quanto ne so, VSTO viene utilizzato per i componenti aggiuntivi di Office. Ma dal momento che vuoi mostrare i dati in un DataGridView presumo che tu abbia un’applicazione WinForms che dovrebbe semplicemente accedere a una cartella di lavoro. In questo caso puoi semplicemente aprire la cartella di lavoro usando Office Interop. Basta aggiungere un riferimento a Microsoft.Office.Interop.Excel al progetto e aggiungere un object using Microsoft.Office.Interop.Excel; dichiarazione.

La documentazione di riferimento MSDN per Excel Interop può essere trovata qui: http://msdn.microsoft.com/en-us/library/ms262200%28v=office.14%29.aspx

Ti darò la parte di Excel, forse qualcun altro può fare il resto.

Innanzitutto, apri Excel e la cartella di lavoro:

 Application app = new Application(); // Optional, but recommended if the user shouldn't see Excel. app.Visible = false; app.ScreenUpdating = false; // AddToMru parameter is optional, but recommended in automation scenarios. Workbook workbook = app.Workbooks.Open(filepath, AddToMru: false); 

Quindi in qualche modo ottenere il foglio di lavoro corretto. Hai alcune possibilità:

 // Active sheet (should be the one which was active the last time the workbook was saved). Worksheet sheet = workbook.ActiveSheet; // First sheet (notice that the first is actually 1 and not 0). Worksheet sheet = workbook.Worksheets[1]; // Specific sheet. // Caution: Default sheet names differ for different localized versions of Excel. Worksheet sheet = workbook.Worksheets["Sheet1"]; 

Quindi ottenere l’intervallo corretto. Non hai specificato come sai dove sono i dati necessari, quindi presumo che sia in colonne fisse.

 // If you also know the row count. Range range = sheet.Range["A1", "D20"]; // If you want to get all rows until the last one that has some data. Range lastUsedCell = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell); string columnName = "D" + lastUsedCell.Row; Range range = sheet.Range["A1", columnName]; 

Ottieni i valori:

 // Possible types of the return value: // If a single cell is in the range: Different types depending on the cell content // (string, DateTime, double, ...) // If multiple cells are in the range: Two dimensional array that exactly represents // the range from Excel and also has different types in its elements depending on the // value of the Excel cell (should always be that one in your case) object[,] values = range.Value; 

L’array di oggetti bidimensionale può quindi essere utilizzato come origine dati per DataGridView. Non uso WinForms da anni quindi non so se è ansible collegarlo direttamente o prima è necessario ottenere i dati in un formato specifico.

Finalmente chiudi di nuovo Excel:

 workbook.Close(SaveChanges: false); workbook = null; app.Quit(); app = null; // Yes, we really want to call those two methods twice to make sure all // COM objects AND all RCWs are collected. GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); 

Chiudere correttamente Excel dopo aver utilizzato Interop è un’attività in sé, perché è necessario assicurarsi che tutti i riferimenti agli oggetti COM siano stati rilasciati. Il modo più semplice che ho trovato per fare questo è di fare tutto il lavoro tranne aprire e chiudere Excel e la cartella di lavoro (quindi il mio primo e ultimo blocco di codice) in un metodo separato. Ciò garantisce che tutti gli oggetti COM utilizzati in tale metodo siano fuori ambito quando si chiama Quit .

AGGIORNARE:

Ho sostituito il mio metodo precedente con un codice più recente per un approccio più rapido. System.Array è un modo abbastanza efficiente e veloce per leggere e associare i dati all’eccel. Puoi scaricare la demo da questo link .


Ho sviluppato un’applicazione VSTO in Excel 2003 Workbook. Non ci sono grandi differenze in termini di syntax, quindi è ansible utilizzarlo nel 2007/2010 senza sforzi.

inserisci la descrizione dell'immagine qui

Non sapevo quale evento useresti per aprire la finestra che mostra i dati, quindi presumo che tu stia usando.

 SheetFollowHyperlink 

Userò l’object della cartella di lavoro statico dichiarato in Showdata.cs. Ecco il codice per il tuo Thisworkbook.cs

  private void ThisWorkbook_Startup(object sender, System.EventArgs e) { ShowData._WORKBOOK = this; } private void ThisWorkbook_SheetFollowHyperlink(object Sh, Microsoft.Office.Interop.Excel.Hyperlink Target) { System.Data.DataTable dTable = GenerateDatatable(); showData sh = new showData(dTable); sh.Show(); // You can also use ShowDialog() } 

Ho aggiunto un collegamento sul foglio corrente e verrà visualizzata la finestra con un datagridview.

  private System.Data.DataTable GenerateDatatable() { Range oRng = null; // It takes the current activesheet from the workbook. You can always pass any sheet as an argument Worksheet ws = this.ActiveSheet as Worksheet; // set this value using your own function to read last used column, There are simple function to find last used column int col = 4; // set this value using your own function to read last used row, There are simple function to find last used rows int row = 5; 

// lascia supporre che i suoi 4 e 5 siano restituiti dal metodo string strRange = “A1”; string andRange = “D5”;

  System.Array arr = (System.Array)ws.get_Range(strRange, andRange).get_Value(Type.Missing); System.Data.DataTable dt = new System.Data.DataTable(); for (int cnt = 1; cnt < = col; cnt++) dt.Columns.Add(cnt.Chr(), typeof(string)); for (int i = 1; i <= row; i++) { DataRow dr = dt.NewRow(); for (int j = 1; j <= col; j++) { dr[j - 1] = arr.GetValue(i, j).ToString(); } dt.Rows.Add(dr); } return dt; } 

Ecco il modulo che consentirà all'utente di visualizzare e modificare i valori. Ho aggiunto il metodo di estensione s e Chr () per convertire i numeri in alfabeti rispettivi che verranno a portata di mano.

 public partial class ShowData : Form { //use static workbook object to access Worksheets public static ThisWorkbook _WORKBOOK; public ShowData(System.Data.DataTable dt) { InitializeComponent(); // binding value to datagrid this.dataGridView1.DataSource = dt; } private void RefreshExcel_Click(object sender, EventArgs e) { Worksheet ws = ShowData._WORKBOOK.ActiveSheet as Worksheet; System.Data.DataTable dTable = dataGridView1.DataSource as System.Data.DataTable; // Write values back to Excel sheet // you can pass any worksheet of your choice in ws WriteToExcel(dTable,ws); } private void WriteToExcel(System.Data.DataTable dTable,Worksheet ws) { int col = dTable.Columns.Count; ; int row = dTable.Rows.Count; string strRange = "A1"; string andRange = "D5"; System.Array arr = Array.CreateInstance(typeof(object),5,4); for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { try { arr.SetValue(dTable.Rows[i][j].ToString(), i, j); } catch { } } } ws.get_Range(strRange, andRange).Value2 = arr; this.Close(); } public static class ExtensionMethods { static string alphabets = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; public static string Chr(this int p_intByte) { if (p_intByte > 0 && p_intByte < = 26) { return alphabets[p_intByte - 1].ToString(); } else if (p_intByte > 26 && p_intByte < = 700) { int firstChrIndx = Convert.ToInt32(Math.Floor((p_intByte - 1) / 26.0)); int scndIndx = p_intByte % 26; if (scndIndx == 0) scndIndx = 26; return alphabets[firstChrIndx - 1].ToString() + alphabets[scndIndx - 1].ToString(); } return "NA"; } } 

questo è uno dei codici più brutti che ho scritto ma funzionerà come una prova di concetto 🙂 Ho creato un esempio di cartella di lavoro come quella

 Colonna1 Colonna2 Colonna3 Colonna4
 -------------------------------------------------- ----
 Dati-1-1 Dati-2-1 Dati-3-1 Dati-4-1
 Dati 1-2 Dati 2-2 Dati 3-2 Dati 4-2
 ....

Il file Excel contiene esattamente 50 righe, questo spiega i selettori di gamma hardcoded. Dopo aver scritto quella parte di code rest è facile, basta creare un modulo, aggiungere un dataviewgrid, creare un’origine dati per MyExcelData , creare un’istanza di MyExcelData come var data = new MyExcelData(pathToExcelFile); e legarlo alla griglia.

Il codice è brutto e ha molte supposizioni ma implementa le tue esigenze. Se apri Excel e programma, puoi vedere gli aggiornamenti sulla griglia che si riflettono su Excel dopo cella modificati. la riga eliminata viene rimossa anche da Excel. poiché non sapevo se hai le chiavi primarie del tuo excel o no, ho usato l’indice delle righe come ID.

A proposito, sono davvero cattivo quando si tratta di VSTO. quindi se conosci un modo migliore apri / modifica / salva per favore avvisami.

 public class MyExcelDataObject { private readonly MyExcelData owner; private readonly object[,] realData; private int RealId; public MyExcelDataObject(MyExcelData owner, int index, object[,] realData) { this.owner = owner; this.realData = realData; ID = index; RealId = index; } public int ID { get; set; } public void DecrementRealId() { RealId--; } public string Column1 { get { return (string)realData[RealId, 1]; } set { realData[ID, 1] = value; owner.Update(ID); } } public string Column2 { get { return (string)realData[RealId, 2]; } set { realData[ID, 2] = value; owner.Update(ID); } } public string Column3 { get { return (string)realData[RealId, 3]; } set { realData[ID, 3] = value; owner.Update(ID); } } public string Column4 { get { return (string)realData[RealId, 4]; } set { realData[ID, 4] = value; owner.Update(ID); } } } public class MyExcelData : BindingList { private Application excel; private Workbook wb; private Worksheet ws; private object[,] values; public MyExcelData(string excelFile) { excel = new ApplicationClass(); excel.Visible = true; wb = excel.Workbooks.Open(excelFile); ws = (Worksheet)wb.Sheets[1]; var range = ws.Range["A2", "D51"]; values = (object[,])range.Value; AllowEdit = true; AllowRemove = true; AllowEdit = true; for (var index = 0; index < 50; index++) { Add(new MyExcelDataObject(this, index + 1, values)); } } public void Update(int index) { var item = this[index - 1]; var range = ws.Range["A" + (2 + index - 1), "D" + (2 + index - 1)]; range.Value = new object[,] { {item.Column1, item.Column2, item.Column3, item.Column4} }; } protected override void RemoveItem(int index) { var range = ws.Range[string.Format("A{0}:D{0}", (2 + index)), Type.Missing]; range.Select(); range.Delete(); base.RemoveItem(index); for (int n = index; n < Count; n++) { this[n].DecrementRealId(); } } } 

PS: mi piacerebbe usare oggetti leggeri ma aggiunge inutili complicazioni.

Quindi nell’evento Sheet1_Startup

 Excel.Range range1 = this.Range["A1", missing]; var obj = range1.Value2.ToString(); 

Dovresti quindi passare alla cella successiva

  range1 = this.Range["A2", missing]; obj = New list(range1.Value2.ToString());