AutoCAD.NET 二次开发 –CAD 中导入 Excel 数据

1、需求:将 Excel 表格中的数据导入到 CAD 中,演示代码实现读取表格单元格的数据,然后在命令栏输出

2、思路:用一个 ExcelTools 类,通过添加 COM 引用操作 Excel 表格,简单实现表格读取的功能;

3、环境:AutoCAD 2018/Office 2016/vs2017

4、步骤

第一步:新建 ExcelTools 类,添加 COM 引用:MicroSoft Excel 16.0 Ojbect Library(Excel 2016)

第二步:引入命名空间:Microsoft.Office.Interop.Excel;

第三步:实现三个简单方法:

public void OpenExcel(string fileName) // 打开表格

public string ReadCell(int sheetID, int row, int col)// 根据工作簿的 ID 来读取单元格

public string ReadCell(string sheetName, int row, int col)// 根据工作簿的名称来读取单元格

public void ColseExcel()// 关闭表格

第四步:获取当前 CAD 文档的 Editor 命令栏,将读取的表格单元格数据输出到命令栏

具体代码如下:

using System.Reflection;
using MyExcel = Microsoft.Office.Interop.Excel;

namespace ImportExcelToCAD {
    public class ExcelToos
    {
        private MyExcel.Application excel;
        private MyExcel.Workbook workbook;
        private MyExcel.Worksheet worksheet;
        private object Nothing = Missing.Value;
        /// <summary>
        /// 打开工作表
        /// </summary>
        /// <param name="fileName"></param>
        public void OpenExcel(string fileName)
        {
            excel=new MyExcel.Application();
            workbook = excel.Workbooks.Open(fileName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,
                Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
        }
        /// <summary>
        /// 根据sheetID来读取指定的单元格
        /// </summary>
        /// <param name="sheetId"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public string ReadCell(int sheetId, int row, int col)
        {
            worksheet = (MyExcel.Worksheet) workbook.Worksheets[sheetId];
            string value = ((MyExcel.Range) worksheet.Cells[row, col]).Text.ToString();
            return value;
        }
        /// <summary>
        /// 根据sheetName来读取指定的单元格
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        public string ReadCell(string sheetName, int row, int col)
        {
            worksheet = (MyExcel.Worksheet)workbook.Worksheets[sheetName];
            string value = ((MyExcel.Range)worksheet.Cells[row, col]).Text.ToString();
            return value;
        }

        public void ColseExcel()
        {
            workbook.Close(false,Nothing,Nothing);
            workbook = null;
            excel.Quit();
            excel = null;
        }
    }
}

主程序代码

using Autodesk.AutoCAD.Runtime;
using System.Windows.Forms;

namespace ImportExcelToCAD {
    class Class1 {
        [CommandMethod("ETC")]
        public void ImportExcelData()
        {
            string fileName = "";
            OpenFileDialog ofd=new OpenFileDialog();
            ofd.Filter = "Excel文件|*.xls,*.xlsx";
            ofd.Title = "选择要导入的Excel文件";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                fileName = ofd.FileName;
            }
            else
            {
                return;
            }

            var doc = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument;
            var ed = doc.Editor;
            try
            {
                var tool=new ExcelToos();
                tool.OpenExcel(fileName);
                string data = tool.ReadCell("Sheet1", 1, 1);
                tool.ColseExcel();
                ed.WriteMessage("从表格中读取的数据为:"+data);
            }
            catch (Autodesk.AutoCAD.Runtime.Exception e)
            {
                Autodesk.AutoCAD.ApplicationServices.Application.ShowAlertDialog("错误:"+e.Message);
            }
        }
    }
}

Excel 操作知识点:

1. Workbooks.Open 方法

Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended,

Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

描述:打开一个工作簿

参数:一般只需要定义 FileName 参数,其他参数全部缺省,参数具体含义请参考 MicroSoft Excel 开发者文档

2. Workbook.Close 方法

Close(SaveChanges, Filename, RouteWorkbook)

描述:关闭工作簿

参数

SaveChanges:是否保存 true/false

Filename:要保存的文件名

RouteWorkbook:是否发送工作簿。一般设置缺省

190102 对代码进行升级,能够实现Excel表格的数据插入到CAD中 但表格样式尚需要完善

using System.Windows.Forms;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Geometry;
using Autodesk.AutoCAD.Runtime;
using ImportExcelToCAD;
using Application = Autodesk.AutoCAD.ApplicationServices.Core.Application;

[assembly: CommandClass(typeof(Commands))]

/// <summary>
/// 将Excel表格插入到CAD中
/// </summary>
namespace ImportExcelToCAD
{
    public class Commands
    {
        [CommandMethod("ETC")]
        public void ImportExcelData()
        {
            var fileName = "";
            var ofd = new OpenFileDialog();
            ofd.Filter = "Excel文件|*.xls;*.xlsx";
            ofd.Title = "选择要导入的Excel文件";
            if (ofd.ShowDialog() == DialogResult.OK)
                fileName = ofd.FileName;
            else
                return;

            var doc = Application.DocumentManager.MdiActiveDocument;
            var ed = doc.Editor;
            var cadTable = new Table();
            cadTable.SetSize(10, 4); //设置表格大小
            try
            {
                var tool = new ExcelToos();
                tool.OpenExcel(fileName);
                for (var i = 0; i < 4; i++)
                for (var j = 0; j < 10; j++)
                {
                    var data = tool.ReadCell("Sheet1", 1 + j, 1 + i);
                    cadTable.Cells[j, i].TextString = data;
                    //ed.WriteMessage("\n"+data);
                }

                tool.ColseExcel();
                InsertTable(cadTable);
            }
            catch (Exception e)
            {
                Application.ShowAlertDialog("错误:" + e.Message);
            }
        }

        private void InsertTable(Table table)
        {
            var doc = Application.DocumentManager.MdiActiveDocument;
            var db = doc.Database;
            var ed = doc.Editor;
            table.GenerateLayout();
            var tj = new TableJig(table);
            var pr = ed.Drag(tj);
            if (pr.Status != PromptStatus.OK) return;

            using (var tr = db.TransactionManager.StartTransaction())
            {
                var btr = db.CurrentSpaceId.GetObject(OpenMode.ForWrite) as BlockTableRecord;
                btr.AppendEntity(table);
                tr.AddNewlyCreatedDBObject(table, true);
                tr.Commit();
            }
        }

        private class TableJig : EntityJig
        {
            private readonly Table m_table;
            private Point3d m_insertPt;

            public TableJig(Entity entity) : base(entity)
            {
                m_table = entity as Table;
                m_insertPt = Point3d.Origin;
            }

            protected override SamplerStatus Sampler(JigPrompts jp)
            {
                var jo = new JigPromptPointOptions("");
                jo.Message = "\n拾取表格插入点";
                var pr = jp.AcquirePoint(jo);
                if (pr.Status == PromptStatus.OK)
                {
                    if (m_insertPt == pr.Value) return SamplerStatus.NoChange;

                    m_insertPt = pr.Value;
                    return SamplerStatus.OK;
                }

                return SamplerStatus.Cancel;
            }

            protected override bool Update()
            {
                m_table.Position = m_insertPt;
                return true;
            }
        }
    }
}