C#读取Excel及序列化、反序列化
using System;
using System.IO;
using System.Collections.Generic;
using System.Runtime.Serialization.Formatters.Binary;
using System.Data;
using System.Data.OleDb;
namespace ClientExcelTableManager
{
class Program
{
public static Dictionary DataDefineList = new Dictionary();
private static bool switchMakeTable = true;
static void Main(string[] args)
{
string currentDir = Environment.CurrentDirectory;
string outPath = currentDir + "/output";
Dictionary paths = new Dictionary();
if (switchMakeTable)
{
if (Directory.Exists(outPath))
{
ClearDirectory(outPath);
}
else
{
Directory.CreateDirectory(outPath);
}
FileInfo[] _tableNames = new DirectoryInfo(currentDir).GetFiles();
for (int i = 0; i < _tableNames.Length; i++)
{
if (!_tableNames[i].Name.Contains(".meta"))
{
if (_tableNames[i].Name.Contains(".xlsx"))
{
string key = _tableNames[i].Name.Replace(".xlsx", "");
if (!paths.ContainsKey(key))
{
paths.Add(key, _tableNames[i].FullName);
}
}
else if (_tableNames[i].Name.Contains(".xls"))
{
string key = _tableNames[i].Name.Replace(".xls", "");
if (!paths.ContainsKey(key))
{
paths.Add(key, _tableNames[i].FullName);
}
}
}
}
DataDefineList.Clear();
foreach (string key in paths.Keys)
{
DataSet _dataset = ToDataTable(paths[key]);
if (_dataset != null)
{
for (int i = 0; i < _dataset.Tables.Count; i++)
{
if (_dataset.Tables[i].TableName.ToLower().Contains("filterdatabase") || _dataset.Tables[i].TableName.ToLower().Contains("sheet")) { continue; }
if (_dataset.Tables[i].Rows.Count > 0)
{
List kickOutList = new List();
#region DataDefine
string _key0 = _dataset.Tables[i].TableName.Replace("$", "");
object[] _titles = _dataset.Tables[i].Rows[0].ItemArray;
int kk = 0;
for (int k = 0; k < _titles.Length; k++)
{
if (_titles[k].ToString().Contains("#")) { kickOutList.Add(k); continue; }
if (!string.IsNullOrEmpty(_titles[k].ToString()))
{
string _addKey = key + "_" + _key0 + "_" + _titles[k].ToString();
if (!DataDefineList.ContainsKey(_addKey)) { DataDefineList.Add(_addKey, kk); }
}
kk++;
}
#endregion
int rowCount = _dataset.Tables[i].Rows.Count;
int columnCount = _dataset.Tables[i].Columns.Count;
string[,] SingleSheetData = new string[rowCount, columnCount];
for (int j = 0; j < _dataset.Tables[i].Rows.Count; j++)
{
int kkk = 0;
for (int k = 0; k < _dataset.Tables[i].Rows[j].ItemArray.Length; k++)
{
bool kickout = false;
for (int l = 0; l < kickOutList.Count; l++)
{
if (k == kickOutList[l]) { kickout = true; continue; }
}
if (kickout == false)
{
SingleSheetData[j, kkk] = _dataset.Tables[i].Rows[j].ItemArray[k].ToString();
kkk++;
}
}
}
string newTableName = "t" + key + "_" + _key0;
SerializeTable(newTableName, outPath + "/" + newTableName + ".bytes", SingleSheetData);
}
else
{
continue;
}
}
}
}
CreateDataDefine(outPath);
if (paths.Count == 0) { Console.WriteLine("no tables."); }
}
else
{
FileInfo[] _files = new DirectoryInfo(outPath).GetFiles();
for (int i = 0; i < _files.Length; i++)
{
DisSerializeTable(_files[i].FullName);
}
Console.ReadLine();
}
}
public static void ClearDirectory(string path)
{
if (Directory.Exists(path))
{
DirectoryInfo[] _di = new DirectoryInfo(path).GetDirectories();
for (int i = 0; i < _di.Length; i++)
{
ClearDirectory(_di[i].FullName);
Directory.Delete(_di[i].FullName);
}
FileInfo[] _files = new DirectoryInfo(path).GetFiles();
for (int i = 0; i < _files.Length; i++)
{
File.Delete(_files[i].FullName);
}
}
}
public static void SerializeTable(string _tableName, string _path, string[,] _data)
{
Stream fstream = new FileStream(_path, FileMode.Create, FileAccess.ReadWrite);
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(fstream, _data);
Console.WriteLine(_tableName + " Done!");
}
public static void DisSerializeTable(string _path)
{
if (_path.Contains(".bytes") && !_path.Contains(".meta"))
{
Stream fstream = new FileStream(_path, FileMode.Open, FileAccess.Read);
BinaryFormatter bf = new BinaryFormatter();
fstream.Position = 0;
string[,] _alldatas = (string[,])bf.Deserialize(fstream);
int rowCount = _alldatas.GetLength(0);
int columnCount = _alldatas.GetLength(1);
for (int i = 0; i < rowCount; i++)
{
if (i > 3) break;
string _str = "";
for (int j = 0; j < columnCount; j++)
{
_str += _alldatas[i, j] + "|";
}
Console.WriteLine(_str);
}
}
}
public static DataSet ToDataTable(string filePath)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileType)) return null;
//if (fileType == ".xls")
//{
// connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
//}
//else
//{
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
//}
string sql_F = "select * from [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
DataSet ds = new DataSet();
try
{
conn = new OleDbConnection(connStr);
conn.Open();
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem, SheetName);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
Console.WriteLine("Exception! \r" + ex);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds;
}
public static void CreateDataDefine(string _out)
{
string _text = "//This file is generated by tools!\r//Please do not modify this file!\r//RedDefine 2016-11-09.\r\rpublic class DataDefine \r{\r";
if (DataDefineList.Count > 0)
{
foreach (string key in DataDefineList.Keys)
{
_text += "\t public const int " + key + " = " + DataDefineList[key] + ";\r";
}
}
_text += "\r}";
File.WriteAllText(_out + "/DataDefine.cs", _text, System.Text.Encoding.Default);
}
}
}
