【Revit 二次开发】读写excel文件 支持xls文件 无论类内部的属性如何定义 泛型使用

 

 

写入操作：

  /// <summary>
        ///  将类序列 写入excel文件 支持xls文件 无论类内部的属性如何定义
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="path">excel文件保存位置</param>
        /// <param name="schemaWalls">类列表</param>
        /// <returns></returns>
        public static string WritingComplexExcel1<T>( string path ,List<T> schemaWalls  )
        {
            string savePath = null;
           
            //获得所有列名
            List<string> columnNameList = schemaWalls[0].GetType().GetProperties().Select(t => t.Name).ToList();
            List<PropertyInfo> columnPropList = schemaWalls[0].GetType().GetProperties().Select(t => t).ToList();
            

            HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿  
            workbook2003.CreateSheet("Sheet1");  //新建1个Sheet工作表
            HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet("Sheet1"); //获取名称为Sheet1的工作表
            for (int i = 0; i < schemaWalls.Count; i++)
            {
                IRow row = SheetOne.CreateRow(i);
                for (int j = 0; j < columnPropList.Count; j++)
                {
                    // string val = schemaWalls[i].GetValue(columnNameList[j]).ToString();
                    string val = columnPropList[j].GetValue(schemaWalls[i]).ToString();
                  //  MessageBox.Show(val);
                    row.CreateCell(j).SetCellValue(val);
                }
            }
            if (!File.Exists(path))
            {
                savePath = path;
                FileStream file2003 = new FileStream(savePath, FileMode.Create);
                workbook2003.Write(file2003);
                file2003.Close();
                workbook2003.Close();
            }
            else
            {
                File.Delete(path);
                savePath = path;
                FileStream file2003 = new FileStream(savePath, FileMode.Create);
                workbook2003.Write(file2003);
                file2003.Close();
                workbook2003.Close();
            }
            return savePath;
        }

读取操作： 使用之前使用反射获取类内部属性

/// <summary>
/// 从excel读取数据并存入dataset表中
/// </summary>
/// <param name="path">excel文件路径</param>
/// <param name="cloumnNames">属性名称序列</param>
/// <returns></returns>
        public static DataSet ReadingComplexExcel1( string path , List<string> cloumnNames )
        {
            DataSet ds = new DataSet();
            FileStream fs = File.OpenRead(@path);  //打开EXCEL文件
            {
                IWorkbook wk = null;
                if (path.Contains("xls"))
                {
                    wk = new HSSFWorkbook(fs);      //把文件信息写入wk 
                    //MessageBox.Show("若程序出错，请将EXCEL保存为2007版本以上");
                }
                else if (path.Contains("xlsx"))
                {
                    wk = new XSSFWorkbook(fs);
                    //MessageBox.Show("若程序出错，请将EXCEL保存为2007版本以下");
                }
                else
                {
                    MessageBox.Show("请选择Excel文件");
                }
                //---------------------
                for (int i = 0; i <= 0; i++)    //遍历文件中的表总数
                {
                    ISheet sheet = wk.GetSheetAt(i);  //读取当前表数据
                    DataTable dt = new DataTable(sheet.SheetName);
                    foreach (string cloumnName in cloumnNames)
                    {
                        dt.Columns.Add(cloumnName, typeof(String));
                    }
                    //dt.Columns.Add("paraname", typeof(String));
                    //dt.Columns.Add("paravalue", typeof(String));
                    for (int j = 0; j <= sheet.LastRowNum; j++)  //当前表总行数
                    {
                        //每次开始遍历表时刷新列表
                        //List_T List_T = new List_T();
                        dt.Rows.Add();
                        //DataRow drow = dt.NewRow();
                        IRow row = sheet.GetRow(j);
                        if (row != null)
                        {
                            ICell cell0 = row.GetCell(0);   //读取第一列数据(参数名)
                            if (cell0.ToString().Contains("null"))
                            { break; }
                            else
                            { dt.Rows[j][cloumnNames[0]] = cell0.ToString(); }

                            for (int k = 1; k < cloumnNames.Count; k++)
                            {
                                ICell cellk = row.GetCell(k);
                                if (cellk != null)
                                { dt.Rows[j][cloumnNames[k]] = cellk.ToString(); }
                                else
                                { dt.Rows[j][cloumnNames[k]] = "NA"; }
                            }
                            //ICell cell1 = row.GetCell(1);   //读取第二列数据(参数值)
                            //if (cell1 != null)
                            //{ dt.Rows[j]["paravalue"] = cell1.ToString(); }
                            //else
                            //{ dt.Rows[j]["paravalue"] = "NA"; }
                        }
                    }
                    //将表格放入dataset
                    ds.Tables.Add(dt);
                }
            }

            return ds;
        }