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

        }

        /// <summary>
        /// 得到Excel表格中合并的单元格
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        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
{
    /// <summary>
    /// 输出明细表，这里标题也包括了，这里其实是可以做一个关于输入表格的WPF跳窗；
    /// </summary>
    [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);

          List<string>vSName = 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;
        }
    }
}

<Window
    x:Class="导出明细表WPF.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:local="clr-namespace:导出明细表WPF"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    Title="输出明细表"
    Width="350"
    Height="200"
    FontSize="24"
    ResizeMode="NoResize"
    WindowStartupLocation="CenterScreen"
    mc:Ignorable="d">
    <Grid>
        <StackPanel>
            <StackPanel Grid.RowSpan="2" Margin="10">
                <ComboBox
                    Name="combox1"
                    SelectedIndex="0"
                    SelectionChanged="combox1_SelectionChanged"
                    Text="选择明细表" />
            </StackPanel>
            <Button
                Name="btn1"
                Margin="10"
                Click="btn1_Click">
                选择导出文件
            </Button>
            
            <Button
                Name="btn2"
                Margin="10"
                Click="btn2_Click">
                确定导出
            </Button>
            <Button Content="Button"/>
        </StackPanel>

    </Grid>
</Window>

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
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow(List<string> 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);
            //过滤
    List<FamilySymbol>fslist=fsymbol.OfClass(typeof(FamilySymbol)).OfCategory(BuiltInCategory.OST_StructuralColumns).ToElements().ToList().ConvertAll(m=>m as FamilySymbol);
            List<string> 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<FamilySymbol> 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<PileParameter> pilelist = new List<PileParameter>();
                PileParameter pileParameter = new PileParameter();
                //创建并开启事务
                Transaction transaction = new Transaction(doc, "新建桩"); transaction.Start();
                //激活族
                if (!fs.IsActive)
                {
                    fs.Activate();
                }
                for (int i =0; i <rows; i++)
                {
        
               //数据读取
                    pileParameter.X = Convert.ToDouble(excelWorksheet.Cells[i+1, 1].Value)/304.8;
                    pileParameter.Y = Convert.ToDouble(excelWorksheet.Cells[i+1, 2].Value)/304.8;//注意数据格式的问题
                    XYZ point = new XYZ(pileParameter.X, pileParameter.Y, 0);
                    //放置桩族
                    FamilyInstance fi = doc.Create.NewFamilyInstance(point, fs,level,StructuralType.Column);
               }
                transaction.Commit();
            }     
            TaskDialog.Show("信息提示", "桩基础生成完成");
            return Result.Succeeded;
        }
    }
}

<Window x:Class="导入数据.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:导入数据"
        mc:Ignorable="d"
        Title="MainWindow" Height="200" Width="250" FontSize="16">
    <Grid>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="0.6*"></ColumnDefinition>
            <ColumnDefinition></ColumnDefinition>
        </Grid.ColumnDefinitions>
        <Grid.RowDefinitions>
            <RowDefinition></RowDefinition>
            <RowDefinition></RowDefinition>
            <RowDefinition></RowDefinition>
        </Grid.RowDefinitions>
        <TextBlock HorizontalAlignment="Center" VerticalAlignment="Center">选择族类型</TextBlock>
        <ComboBox Name="combox" Grid.Column="1" Margin="10"></ComboBox>
        <Button Grid.Column="1" Grid.Row="1" Name="btn1" Margin="10" Click="btn1_Click">打开文件路径</Button>
        <TextBlock Grid.Row="1" Margin="10" HorizontalAlignment="Center" VerticalAlignment="Center">读取文件</TextBlock>
        <Button Grid.ColumnSpan="2" Grid.Row="2" Name="btn2" Margin="10" Click="btn2_Click">开始读取信息</Button>

    </Grid>
</Window>

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 导入数据
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow(List<string> 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; }
    }
}