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