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