예제 #1
0
파일: Program.cs 프로젝트: useada/ARPG-Game
        private static void ReadTablesUseOLEDB(List <ExcelTable> outTables, string[] files, string jsonDir)
        {
            #region readTable and Data
            foreach (var filename in files)
            {
                Console.WriteLine("gen:" + filename);
                string strCon = string.Format(
                    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'",
                    filename);
                var bastTableName = "__Base$";
                //__Base$
                OleDbConnection myConn = new OleDbConnection(strCon);
                string          strCom = string.Format(" SELECT * FROM [{0}]", bastTableName);
                myConn.Open();
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
                var ds = new DataSet();
                myCommand.Fill(ds);

                var exTables = new List <ExcelTable>();

                #region Table
                for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    var table = new Libs.EX.ExcelTable();
                    var row   = ds.Tables[0].Rows[i];

                    table.TableName   = row[0].ToString();
                    table.ClassName   = row[1].ToString();
                    table.FileName    = row[2].ToString();
                    table.Description = row[3].ToString();

                    exTables.Add(table);
                }
                #endregion

                foreach (var table in exTables)
                {
                    var selectStr = string.Format("SELECT * FROM [{0}$]", table.TableName);
                    myCommand = new OleDbDataAdapter(selectStr, myConn);
                    ds        = new DataSet();
                    myCommand.Fill(ds);
                    #region Read col
                    var dataTable = new DataTable();
                    for (var col = 1; col < ds.Tables[0].Columns.Count; col++)
                    {
                        try
                        {
                            var colnum = new Libs.EX.ExcelTableCol();
                            colnum.ColIndex = col - 1;
                            colnum.Comment  = ds.Tables[0].Rows[2][col].ToString();
                            colnum.Name     = ds.Tables[0].Rows[0][col].ToString();
                            colnum.Type     = ds.Tables[0].Rows[1][col].ToString();
                            if (string.IsNullOrEmpty(colnum.Name))
                            {
                                continue;
                            }
                            table.Cols.Add(colnum);
                            var coln = ds.Tables[0].Columns[col];

                            dataTable.Columns.Add(new DataColumn
                            {
                                AllowDBNull = true,
                                ColumnName  = coln.ColumnName
                            });
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("错误了!", "一般出现这个情况都是因为存在影藏列!" + ex.ToString());
                            return
                            ;
                        }
                    }
                    #endregion
                    #region Read row
                    for (var i = 3; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (ds.Tables[0].Rows[i][1] == null)
                        {
                            continue;
                        }
                        var row = dataTable.NewRow();
                        for (var col = 1; col < ds.Tables[0].Columns.Count && col < dataTable.Columns.Count + 1; col++)
                        {
                            row[col - 1] = ds.Tables[0].Rows[i][col];
                        }

                        dataTable.Rows.Add(row);
                    }
                    #endregion
                    #region Save Json File
                    var json = Libs.EX.ExcelTool.GetExcelData(table, dataTable);
                    System.IO.File.WriteAllText(System.IO.Path.Combine(jsonDir, table.FileName), json);
                    #endregion
                    outTables.Add(table);
                }
                myConn.Close();
            }
            #endregion
        }
예제 #2
0
파일: Program.cs 프로젝트: useada/ARPG-Game
        private static void ReadTables(List <ExcelTable> outTables, string[] files, string jsonDir)
        {
            #region readTable and Data
            foreach (var filePath in files)
            {
                FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

                //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                //...
                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                //...
                //3. DataSet - The result of each spreadsheet will be created in the result.Tables
                //DataSet result = excelReader.AsDataSet();
                //...
                //4. DataSet - Create column names from first row
                excelReader.IsFirstRowAsColumnNames = false;
                DataSet result = excelReader.AsDataSet();

                var _base    = result.Tables["__Base"];
                var exTables = new List <ExcelTable>();

                #region Table
                for (var i = 0; i < _base.Rows.Count; i++)
                {
                    var table = new Libs.EX.ExcelTable();
                    var row   = _base.Rows[i];

                    table.TableName   = row[0].ToString();
                    table.ClassName   = row[1].ToString();
                    table.FileName    = row[2].ToString();
                    table.Description = row[3].ToString();

                    exTables.Add(table);
                }
                #endregion
                foreach (var table in exTables)
                {
                    #region Read col
                    var data      = result.Tables[table.TableName];
                    var dataTable = new DataTable();
                    for (var col = 1; col < data.Columns.Count; col++)
                    {
                        try
                        {
                            var colnum = new Libs.EX.ExcelTableCol();
                            colnum.ColIndex = col - 1;
                            colnum.Comment  = (string)data.Rows[2][col];
                            colnum.Name     = data.Rows[0][col].ToString();
                            colnum.Type     = data.Rows[1][col].ToString();
                            if (string.IsNullOrEmpty(colnum.Name))
                            {
                                continue;
                            }
                            table.Cols.Add(colnum);
                            var coln = data.Columns[col];

                            dataTable.Columns.Add(new DataColumn
                            {
                                AllowDBNull = true,
                                ColumnName  = coln.ColumnName
                            });
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("错误了!", "一般出现这个情况都是因为存在影藏列!" + ex.ToString());
                            return
                            ;
                        }
                    }
                    #endregion
                    #region Read row
                    for (var i = 3; i < data.Rows.Count; i++)
                    {
                        if (data.Rows[i][1] == null)
                        {
                            continue;
                        }
                        var row = dataTable.NewRow();
                        for (var col = 1; col < data.Columns.Count && col < dataTable.Columns.Count + 1; col++)
                        {
                            row[col - 1] = data.Rows[i][col];
                        }

                        dataTable.Rows.Add(row);
                    }
                    #endregion
                    #region Save Json File
                    var json = Libs.EX.ExcelTool.GetExcelData(table, dataTable);
                    System.IO.File.WriteAllText(System.IO.Path.Combine(jsonDir, table.FileName), json);
                    #endregion
                    outTables.Add(table);
                }
                //myConn.Close();


                excelReader.Close();
            }
            #endregion
        }