Excel表格交互

引用

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelRead
{
    class Program
    {
        static void Main(string[] args)
        {
            //【1】找到对应Excel
            FileInfo file = new FileInfo(@"E:课程录制Revit二次开发进阶课程测试文件测试.xlsx");
           
            //【2】读取Excel文件
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage excelPackage=new ExcelPackage(file))
            {
                //得到指定的Sheet
                ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["Sheet1"];
                //读取第一行，第一列的值
                object a = excelWorksheet.Cells[4, 1].Value;
                Console.WriteLine(a);
                string b = GetMergeValue(excelWorksheet, 4, 1);
                Console.WriteLine(b);
            }

        }

        /// 

        /// 得到Excel表格中合并的单元格
        /// 
        /// 
        /// 
        /// 
        /// 
        public static string GetMergeValue(ExcelWorksheet wSheet, int row, int column)
        {
            string range = wSheet.MergedCells[row, column];
            if (range == null)
            {
                if (wSheet.Cells[row, column].Value != null)
                    return wSheet.Cells[row, column].Value.ToString();
                else
                    return "";
            }

            object value =
                wSheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;
            if (value != null)
                return value.ToString();
            else
                return "";
        }
    }
}

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace ExcelWrite
{
    class Program
    {
        static void Main(string[] args)
        {

            //Excel文件所在的地址
            FileInfo fileInfo = new FileInfo(@"E:课程录制Revit二次开发进阶课程测试文件测试.xlsx");
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
            {
                //指定需要写入的sheet名
                ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["Sheet1"];

                //比如修改第一行,第二列的值为 0
                excelWorksheet.Cells[1, 2].Value = 0;

                //然后保存即可
                excelPackage.Save();
            }
        }
    }
}

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelCreate
{
    class Program
    {
        static void Main(string[] args)
        {
            //  Create();

            Copy();
        }

        private static void Copy()
        {
            string originalExcel = @"E:课程录制Revit二次开发进阶课程测试文件测试.xlsx";
            string aimExcel = @"E:课程录制Revit二次开发进阶课程测试文件复制后.xlsx";
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            File.Copy(originalExcel, aimExcel);
        }

        private static void Create()
        {
            string newExcelPath = @"E:课程录制Revit二次开发进阶课程测试文件新表.xlsx";
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            FileInfo filePath = new FileInfo(newExcelPath);

            using (ExcelPackage ExcelPkg = new ExcelPackage(filePath))
            {
                ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");

                ExcelWorksheet wsSheet2 = ExcelPkg.Workbook.Worksheets.Add("Sheet2");
                ExcelPkg.SaveAs(filePath);
            }
        }
    }
}

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelPractice
{
    class Program
    {
        static void Main(string[] args)
        {
            string orignalExcelPath = @"E:课程录制Revit二次开发进阶课程测试文件初始表格.xlsx";
            FileInfo file = new FileInfo(orignalExcelPath);
            using (ExcelPackage excelPackage = new ExcelPackage(file))
            {
                ExcelWorksheet originalExcelWorksheet = excelPackage.Workbook.Worksheets["Sheet1"];
                for (int i = 1; i < originalExcelWorksheet.Dimension.Rows+1; i++)
                {
                    if (string.IsNullOrEmpty(Convert.ToString(originalExcelWorksheet.Cells[i, 1].Value)))
                    {
                        continue;
                    }
                    object cellValue = originalExcelWorksheet.Cells[i, 1].Value;

                    string filePath = Path.Combine(@"E:课程录制Revit二次开发进阶课程测试文件生成文件", $"{cellValue}.xlsx");

                    GenereateExcel(filePath, cellValue);
                }

            }
        }

        static void GenereateExcel(string SaveExcelPath, object saveValue)
        {
            FileInfo fileInfo = new FileInfo(SaveExcelPath);
            using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
            {
                //指定需要写入的sheet名

                excelPackage.Workbook.Worksheets.Add("Sheet1");

                ExcelWorksheet saveExcelWorkSheet = excelPackage.Workbook.Worksheets["Sheet1"];
                saveExcelWorkSheet.Cells[1, 1].Value = saveValue;

                //然后保存即可
                excelPackage.Save();


            }
        }
    }
}

Class1:

using Autodesk.Revit.Attributes;
using Autodesk.Revit.DB;
using Autodesk.Revit.UI;
using Autodesk.Revit.UI.Selection;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 导出明细表WPF
{
    /// 

    /// 输出明细表，这里标题也包括了，这里其实是可以做一个关于输入表格的WPF跳窗；
    /// 
    [Transaction(TransactionMode.Manual)]
    public class Class1 : IExternalCommand
    {
        public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
        {
            Document document = commandData.Application.ActiveUIDocument.Document;
            //获取项目中的一张门明细表
            FilteredElementCollector collector = new FilteredElementCollector(document);

          ListvSName = collector.OfClass(typeof(ViewSchedule)).ToElements().ToList().ConvertAll(m=>(m as ViewSchedule).Name);
           
            MainWindow mainWindow=new MainWindow(vSName);
            mainWindow.ShowDialog();
            if (mainWindow.Isclicked==false)
            {
                return Result.Cancelled;
            }

            foreach (ViewSchedule vs in collector.OfClass(typeof(ViewSchedule)))
            {
                if (vs.Name == mainWindow.combox1.SelectedItem.ToString())
                {
                    //【1】找到对应Excel
                    FileInfo fileInfo = new FileInfo(mainWindow.fileName);
                   
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//版权权限
                    try
                    {
                        using (ExcelPackage excelPackage = new ExcelPackage(fileInfo))
                        {
                            //指定需要写入的sheet名
                            ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["Sheet1"];

                            //获取数据的行列数
                            int rows, cols;
                            TableSectionData data = vs.GetTableData().GetSectionData(SectionType.Body);
                            rows = data.NumberOfRows;
                            cols = data.NumberOfColumns;
                            //导入数据
                            //导出标题
                            object a = vs.GetCellText(SectionType.Header, 0, 0);
                            excelWorksheet.Cells[1, 1, 1, cols].Merge = true;
                            excelWorksheet.Cells[1, 1].Value = a;
                            //导出数据主体内容
                            for (int i = 0; i < rows; i++)
                            {
                                for (int j = 0; j < cols; j++)
                                {
                                    excelWorksheet.Cells[i + 2, j + 1].Value = vs.GetCellText(SectionType.Body, i, j);
                                    //然后保存即可
                                    excelPackage.Save();
                                }
                            }
                        }
                        TaskDialog.Show("信息提示", "输出成功");
                    }
                    catch 
                    {
                      TaskDialog.Show("信息提示","请检查所选Excel文件是否处于关闭状态");
                    }
     
                }
            }
            
            return Result.Succeeded;
        }
    }
}

    
        
            
                
            
            
                选择导出文件
            
            
            
                确定导出
            
            
        

    

using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace 导出明细表WPF
{
    /// 

    /// MainWindow.xaml 的交互逻辑
    /// 
    public partial class MainWindow : Window
    {
        public MainWindow(List vSName)
        {
            InitializeComponent();
            combox1.ItemsSource = vSName; 
        }
        public string fileName { get; set; }
        public bool Isclicked { get; set; } = false;
        private void btn1_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel文件|*.xlsx";
            if (fileDialog.ShowDialog()==true)
            {
               fileName = fileDialog.FileName;//读取属性获得打开路径文件名
            }
        }
        public string viewName { get; set; }
        private void btn2_Click(object sender, RoutedEventArgs e)
        {
            Isclicked = true;
            this.Close();
        }

        private void combox1_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            viewName = combox1.SelectedItem.ToString();
        }
    }
}

using Autodesk.Revit.Attributes;
using Autodesk.Revit.DB;
using Autodesk.Revit.DB.Structure;
using Autodesk.Revit.UI;
using Autodesk.Revit.UI.Selection;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 导入数据
{
   //读取文本钻孔灌注桩的数据，创建桩族实例，并对部分参数进行设置
    [Transaction(TransactionMode.Manual)]
    class Class1 : IExternalCommand
    {
        public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
        {
            UIDocument uidoc = commandData.Application.ActiveUIDocument;
            Document doc = uidoc.Document;
            Level level =doc.ActiveView.GenLevel;

            //查找名称包含混凝土圆形桩的familysymbol
            //新建收集器
            FilteredElementCollector fsymbol = new FilteredElementCollector(doc);
            //过滤
    Listfslist=fsymbol.OfClass(typeof(FamilySymbol)).OfCategory(BuiltInCategory.OST_StructuralColumns).ToElements().ToList().ConvertAll(m=>m as FamilySymbol);
            List nameList = fslist.ConvertAll(m => (m.FamilyName +" "+ m.Name));

            MainWindow mainWindow = new MainWindow(nameList);//实例化主窗口
            mainWindow.ShowDialog();//展示界面
            if (mainWindow.isClicked == false)
            {
                return Result.Cancelled;
            }
            string fsNameSelect = mainWindow.combox.SelectedItem.ToString();//需要注意得到的数据格式，要不然会出现报错
            //获取桩族
            IEnumerable fsyms = from elem in fsymbol
                                              let type = elem as FamilySymbol
                                              where fsNameSelect.Contains(type.FamilyName)//选择名称是否包含该族类型名称
                                              where fsNameSelect.Contains(type.Name)//选择名称是否包含该族名称
                                              select type;
            //获取桩的familysymbol
            FamilySymbol fs = fsyms.First();

            //【1】找到对应Excel
            FileInfo file = new FileInfo(mainWindow.fileName);

            //【2】读取Excel文件
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage excelPackage = new ExcelPackage(file))
            {
                //得到指定的Sheet
                ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets["Sheet1"];

                //获取表格数据的列和行数量
                int colms, rows;
                colms = excelWorksheet.Dimension.Columns;
                rows = excelWorksheet.Dimension.Rows;

                //桩数据集合
                List pilelist = new List();
                PileParameter pileParameter = new PileParameter();
                //创建并开启事务
                Transaction transaction = new Transaction(doc, "新建桩"); transaction.Start();
                //激活族
                if (!fs.IsActive)
                {
                    fs.Activate();
                }
                for (int i =0; i  

    
        
            
            
        
        
            
            
            
        
        选择族类型
        
        打开文件路径
        读取文件
        开始读取信息

    


 
using Autodesk.Revit.DB;
using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace 导入数据
{
    /// 

    /// MainWindow.xaml 的交互逻辑
    /// 
    public partial class MainWindow : Window
    {
        public MainWindow(List nameList)
        {
            InitializeComponent();
            this.combox.ItemsSource =nameList;
        }
        public string fileName { get; set; }
        public bool isClicked { get; set; } = false;

        private void btn1_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel文件|*.xlsx";
            if (fileDialog.ShowDialog() == true)
            {
                fileName = fileDialog.FileName;//读取属性获得打开路径文件名
            }
        }

        private void btn2_Click(object sender, RoutedEventArgs e)
        {
            isClicked = true;
            this.Close();
        }

 
    }
}

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 导入数据
{
  public  class PileParameter
    {
        public double X { get; set; }
        public double Y{ get; set; }
    }
}