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