the complete solution with comments...
on the top of you file add:
using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using excel = Microsoft.Office.Interop.Excel;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using acApp = Autodesk.AutoCAD.ApplicationServices.Application;
your button click event:
private void button28_Click(object sender, EventArgs e)
{
MyFunction();
}the main function:
private void MyFunction()
{
string myExcelFile = @"c:\myfile.xlsx";
string myKey = "Z08";
//Get the value from the excel file (find the cell with the key, and return the value)
string myValue = GetValuesFromExcelFile(myExcelFile, myKey);
//Update the block attributes with the new value...
SetBlockAttributeValue(myKey, myValue);
}the void to set a new attribute value: (used in the main function)
private void SetBlockAttributeValue(string key, string value)
{
Document doc = acApp.DocumentManager.MdiActiveDocument;
Database db = doc.Database;
Editor ed = doc.Editor;
List<string> editInformations = new List<string>();
//Set the selection filter
TypedValue[] acTypValAr = new TypedValue[1];
acTypValAr.SetValue(new TypedValue((int)DxfCode.Start, "INSERT"), 0);
SelectionFilter acSelFtr = new SelectionFilter(acTypValAr);
//Select all blocks (INSERT) in the drawing
PromptSelectionResult acSSPrompt;
acSSPrompt = ed.SelectAll(acSelFtr);
//If the selection OK
if (acSSPrompt.Status == PromptStatus.OK)
{
//Lock the document
using (DocumentLock acLckDoc = doc.LockDocument())
{
//Add the selected blocks to a selection set
SelectionSet acSSet = acSSPrompt.Value;
using (Transaction acTrans = db.TransactionManager.StartTransaction())
{
//Check all blocks
foreach (SelectedObject acSSObj in acSSet)
{
if (acSSObj != null)
{
//Open the block reference
BlockReference br = acTrans.GetObject(acSSObj.ObjectId, OpenMode.ForRead) as BlockReference;
//Get the attribute collection
AttributeCollection attCol = br.AttributeCollection;
//Check all attributes in the block
foreach (ObjectId attId in attCol)
{
AttributeReference attRef = (AttributeReference)acTrans.GetObject(attId, OpenMode.ForWrite);
if (attRef != null)
{
//If the attribute-tag is your key...
if (attRef.Tag == key)
{
//Save informations...
editInformations.Add("Block name: " + br.Name + " | Key: " + key + " | Old Value: " + attRef.TextString + " | New Value: " + value);
//Set the new attribut value
//attRef.UpgradeOpen();
attRef.TextString = value;
//attRef.DowngradeOpen();
}
}
}//end foreach attId
}
}//end foreach acSSObj
//Apply changes...
acTrans.Commit();
}//end acTrans
acSSet.Dispose();
}// end document lock
}//end acSSPromt.Status OK
//Show informations in the command line
if (editInformations.Count > 0)
{
ed.WriteMessage("Total " + editInformations.Count.ToString() + " attributes changed" + System.Environment.NewLine);
foreach (string editinfo in editInformations)
{
ed.WriteMessage(editinfo + System.Environment.NewLine);
}
}
else
{
MessageBox.Show("No attributes found...");
}
}the void to get the value from excel file: (used in the main function)
private string GetValuesFromExcelFile(string file, string key)
{
//Start with a empty result...
string result = "";
//Set the excel application
excel.Application oex = new excel.Application();
//If excel not found, show info dialog
if (oex == null)
{
MessageBox.Show("Excel is not properly installed!!");
return result;
}
excel.Workbook owb = null;
excel.Worksheet ows = null;
//Open the excel workbook (document)
try
{
owb = oex.Workbooks.Open(file, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch
{
MessageBox.Show("Can't open file.");
oex.Quit();
try
{
if (oex != null)
Marshal.ReleaseComObject(oex);
}
catch { }
return result;
}
//Set the excel document sheet (1 = first sheet)
ows = (excel.Worksheet)owb.Worksheets[1];
bool found = false;
//Column in the sheet with the value
string valueCol = "B";
//The range to search for the key (Cell A2 - A24)
excel.Range keyCellRange = ows.get_Range("A2", "A24");
try
{
foreach (excel.Range r in keyCellRange)
{
//Check if the cell is (not) empty
if (r.Value2 != null)
{
//if the cell is your key...
if (r.Value2 == key)
{
//Get the cell with the value...
string valueCell = valueCol + r.Row.ToString();
excel.Range valueCellRange = ows.get_Range(valueCell);
//If the cell is (not) empty
if (valueCellRange.Value2 != null)
{
//Get the cell value
result = valueCellRange.Value2.ToString();
//We found something...
found = true;
//Exit the foreach loop... no reason to check more cells.
break;
}
}
}
}
if (!found)
{
//No cell with the key found
MessageBox.Show("Key not found... " + key);
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
}
finally
{
//Close the workbook and the excel application
owb.Close(true, Type.Missing, Type.Missing);
oex.Quit();
try
{
if (ows != null)
Marshal.ReleaseComObject(ows);
if (owb != null)
Marshal.ReleaseComObject(owb);
if (oex != null)
Marshal.ReleaseComObject(oex);
}
catch { }
}
return result;
}it's tested, and it works.
