Exemple #1
0
        public KTable readTable(Boolean withTag = defaultWithTag)
        {

            if (withTag) ms.Position += 1;

            KTable table = new KTable();
            DataTable dt = new DataTable();

            table.dataTable = dt;

            int primaryKeyIndex = binReader.ReadInt32();

            int rowNum = binReader.ReadInt32();
            int columnNum = binReader.ReadInt32();

            String[] header = new String[columnNum];

            int row = 0;
            int column = 0;

            while (column < columnNum)
            {
                String columnName = readString(false);
                header[column] = columnName;
                dt.Columns.Add(columnName, typeof(String));
                column++;
            }

            table.header = header;
            table.primaryKeyIndex = primaryKeyIndex;

            while (row < rowNum)
            {

                column = 0;

                DataRow dr = dt.NewRow();

                while (column < columnNum)
                {


                    dr[column] = readString(false);

                    column++;

                }

                dt.Rows.Add(dr);

                row++;

            }

            return table;

        }
Exemple #2
0
        static private KTable processSheet(OleDbConnection conn, String sheetName, String prefix_primaryKey, String prefix_IgnoreLine, String prefix_IgnoreColumn, Boolean ignoreBlank)
        {

            string query = "SELECT   *   FROM  [" + sheetName + "]";


            OleDbCommand oleCommand = new OleDbCommand(query, conn);
            OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
            DataSet myDataSet = new DataSet();

            //   将   Excel   的[Sheet1]表内容填充到   DataSet   对象 
            oleAdapter.Fill(myDataSet, sheetName);


            DataTable dt = myDataSet.Tables[0];


            if (dt.Rows.Count == 1 && dt.Columns.Count == 1)
            {
                return null;
            }


            //while (true)
            //{
            //    //忽略行
            //    if (isPrefix(dt.Rows[0][0].ToString(), prefix_IgnoreLine))
            //    {

            //        dt.Rows.RemoveAt(0);
            //    }
            //    else
            //    {
            //        break;
            //    }
            //}

            Object[] header = new Object[dt.Rows[0].ItemArray.Length];
            dt.Rows[0].ItemArray.CopyTo(header, 0);

            if (ignoreBlank)
            {
                ///消除空白行
                int maxColumn = header.Length;
                int row = dt.Rows.Count - 1;
                while (row >= 0)
                {
                    Boolean hasData = false;
                    DataRow dataRow = dt.Rows[row];
                    int column = 0;
                    while (column < maxColumn)
                    {
                        if (dataRow[column].ToString() != "")
                        {
                            hasData = true;
                            break;
                        }
                        column++;
                    }

                    if (!hasData)
                    {
                        dt.Rows.RemoveAt(row);
                    }

                    row--;
                }
            }

            int i = dt.Rows.Count - 1;
            while (i >= 0)
            {
                //忽略行
                if (isPrefix(dt.Rows[i][0].ToString(), prefix_IgnoreLine))
                {
                    dt.Rows.RemoveAt(i);
                }
                i--;
            }

            header = new Object[dt.Rows[0].ItemArray.Length];
            dt.Rows[0].ItemArray.CopyTo(header, 0);


            i = header.Length - 1;
            while (i >= 0)
            {
                //忽略列
                if (isPrefix(header[i].ToString(), prefix_IgnoreColumn))
                {
                    dt.Columns.RemoveAt(i);
                }
                i--;
            }

            header = new Object[dt.Rows[0].ItemArray.Length];
            dt.Rows[0].ItemArray.CopyTo(header, 0);

            if (ignoreBlank)
            {
                ///消除空白列
                int column = header.Length - 1;
                while (column >= 0)
                {
                    if (header[column].ToString() == "")
                    {
                        dt.Columns.RemoveAt(column);
                    }
                    column--;
                }

                header = new String[dt.Rows[0].ItemArray.Length];
                dt.Rows[0].ItemArray.CopyTo(header, 0);

            }


            ///去掉表头
            dt.Rows.RemoveAt(0);

            KTable sheet = new KTable();

            ///查找主键并赋值
            i = 0;
            while (i < header.Length)
            {
                String head = header[i].ToString();

                dt.Columns[i].ColumnName = head;

                if (isPrefix(head, prefix_primaryKey))
                {
                    head = head.Substring(prefix_primaryKey.Length);
                    sheet.primaryKeyIndex = i;
                    header[i] = head;
                    break;
                }
                i++;
            }

            sheet.name = sheetName.Substring(0, sheetName.Length - 1);
            sheet.header = (String[])header;
            sheet.dataTable = dt;

            //给DataTable设置主键,用于后面的合并表
            try
            {
                dt.PrimaryKey = new DataColumn[] { dt.Columns[sheet.primaryKeyIndex] };
            }
            catch
            {
                throw new Exception("表" + sheet.name + "的主键值有重复");
            }

            return sheet;

        }
Exemple #3
0
        public void writeTable(KTable table, Boolean withTag = defaultWithTag)
        {

            if (withTag) binWriter.Write(KDataFormat.TABLE);

            binWriter.Write(table.primaryKeyIndex);

            DataTable dt = table.dataTable;

            binWriter.Write(dt.Rows.Count);
            binWriter.Write(dt.Columns.Count);

            String[] header = table.header;

            int i = 0;
            int len = header.Length;
            while (i < len)
            {
                writeString(header[i]);
                i++;
            }

            int rowNum = dt.Rows.Count;
            int columnNum = dt.Columns.Count;

            int row = 0;
            while (row < rowNum)
            {
                int column = 0;
                while (column < columnNum)
                {

                    writeString(dt.Rows[row][column].ToString());
                    column++;
                }

                row++;
            }

        }
Exemple #4
0
        static KTable[] doExport(String source, String prefix_primaryKey, String prefix_IgnoreSheet, String prefix_IgnoreLine, String prefix_IgnoreColumn, Boolean ignoreBlank, Boolean merge)
        {

            //HDR参数:YES忽视第一行;NO包括第一行
            String ConnStr = "Provider=Microsoft.Ace.OleDB.12.0;Data Source=" + source + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1;'";

            OleDbConnection conn = new OleDbConnection(ConnStr);
            conn.Open();

            ArrayList sheets = new ArrayList();

            String[] sheetNames = getSheetName(conn);

            //KTable[] list = new KTable[sheetNames.Length];

            foreach (String sheetName in sheetNames)
            {

                //忽略表
                if (isPrefix(sheetName, prefix_IgnoreSheet))
                    continue;

                KTable excelSheet = processSheet(conn, sheetName, prefix_primaryKey, prefix_IgnoreLine, prefix_IgnoreColumn, ignoreBlank);
                if (excelSheet != null)
                {
                    sheets.Add(excelSheet);
                }

            }

            KTable[] tables = (KTable[])sheets.ToArray(Type.GetType("KLib.KTable"));

            if (merge)
            {

                KTable mainSheet = tables[0];
                mainSheet.name = Path.GetFileNameWithoutExtension(source);


                for (int i = 1; i < tables.Length; i++)
                {
                    try
                    {
                        mainSheet.dataTable.Merge(tables[i].dataTable);
                    }
                    catch
                    {
                        throw new Exception("文件" + Path.GetFileName(source) + "执行合并子表操作时出错\r\n原因:子表的主键名不一致");
                    }
                }

                tables = new KTable[] { mainSheet };

            }

            conn.Close();
            conn.Dispose();

            return tables;

        }