private static DataTable ExcelToDataTable(string fileName, string SheetName)
{
// Open file and return as Stream
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
using (System.IO.FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (ExcelDataReader.IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
//excelReader.IsFirstRowAsColumnNames = true;
//Return as dataset
// DataSet result = excelReader.AsDataSet();
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get all the tables
DataTableCollection table = result.Tables;
// store it in data table
DataTable resultTable = table[SheetName];
//excelReader.Dispose();
//excelReader.Close();
// return
return(resultTable);
}
}
}
private static DataTable ExcelToDataTable(string fileName, string sheetName)
{
// Open file and return as Stream
using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get all the tables
var table = result.Tables;
// store it in data table
var resultTable = table[sheetName];
return(resultTable);
}
}
}
static void Main(string[] args)
{
string path = System.Environment.CurrentDirectory; //获取当前目录
//在当前目录创建存放csv和txt格式文件的文件夹
string csvDir = path + "\\csv";
string txtDir = path + "\\txt";
if (!Directory.Exists(csvDir))
{
Directory.CreateDirectory(csvDir);
}
else
{
Directory.Delete(csvDir, true);
Directory.CreateDirectory(csvDir);
}
if (!Directory.Exists(txtDir))
{
Directory.CreateDirectory(txtDir);
}
else
{
Directory.Delete(txtDir, true);
Directory.CreateDirectory(txtDir);
}
//获取当前目录下所有文件，查找到xlsx文件
DirectoryInfo dir = new DirectoryInfo(path);
FileInfo[] fileInfos = dir.GetFiles(); //获取当前目录下的所有文件
//遍历目录下所有excel文件，将其名称存放到excelFileList。将其全路径存放到excelFullPath
ArrayList excelFileList = new ArrayList();
ArrayList excelFullPath = new ArrayList();
for (int i = 0; i < fileInfos.Length; ++i)
{
if (fileInfos[i].Name.EndsWith(".xlsx") || fileInfos[i].Name.EndsWith(".xls"))
{
excelFileList.Add(fileInfos[i].ToString());
excelFullPath.Add(path + "\\" + fileInfos[i].ToString());
}
}
for (int i = 0; i < excelFullPath.Count; ++i)
{
//Console.WriteLine("excelFileList==="+ excelFileList.Count+"|");
//Console.WriteLine("excelFullPath===" + excelFullPath.Count + "|");
//Console.WriteLine(excelFullPath[i]);
FileStream stream = File.Open(excelFullPath[i].ToString(), FileMode.Open, FileAccess.Read);
IExcelDataReader excelDataReader = excelFullPath[i].ToString().Contains(".xlsx") ? ExcelReaderFactory.CreateOpenXmlReader(stream) : ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelDataReader.AsDataSet();
excelDataReader.IsFirstRowAsColumnNames = true;
//DataTable at = result.Tables[0];
//Console.WriteLine("DataTable=" + at.Rows[0].ItemArray[0].ToString());
//遍历表格中数据，按行写入csv文件和txt文件
FileStream temStreamCsv = new FileStream(csvDir + "\\" + excelFileList[i].ToString().Replace(".xlsx", ".csv"), FileMode.OpenOrCreate, FileAccess.ReadWrite);
FileStream temStreamTxt = new FileStream(txtDir + "\\" + excelFileList[i].ToString().Replace(".xlsx", ".txt"), FileMode.OpenOrCreate, FileAccess.ReadWrite);
StreamWriter sw = new StreamWriter(temStreamCsv);
StreamWriter swTxt = new StreamWriter(temStreamTxt, Encoding.UTF8);
for (int ii = 0; ii < result.Tables[0].Rows.Count; ++ii)
{
string rowStr = String.Empty;
for (int j = 0; j < result.Tables[0].Columns.Count; ++j)
{
if (j != result.Tables[0].Columns.Count - 1)
{
rowStr += result.Tables[0].Rows[ii][j].ToString() + ",";
}
else
{
rowStr += result.Tables[0].Rows[ii][j].ToString();
}
}
sw.WriteLine(rowStr);
swTxt.WriteLine(rowStr);
}
Console.WriteLine(excelFileList[i]);
Console.WriteLine("表格行数：" + result.Tables[0].Rows.Count);
Console.WriteLine("转换OK了！！");
Console.WriteLine("---------------------------------------------------------------------");
sw.Close();
swTxt.Close();
temStreamCsv.Close();
temStreamTxt.Close();
excelDataReader.Close();
stream.Close();
}
Console.ReadKey();
}
static void Main(string[] args)
{
string
currentDirectory = System.Reflection.Assembly.GetExecutingAssembly().Location;
if (currentDirectory.IndexOf("bin") != -1)
{
currentDirectory = currentDirectory.Substring(0, currentDirectory.LastIndexOf("bin", currentDirectory.Length - 1));
}
string
fileName = Path.Combine(currentDirectory, "test2.xls");
if (!File.Exists(fileName))
{
return;
}
DataSet
dataSet = new DataSet();
Stream stream = null;
IExcelDataReader excelDataReader = null;
try
{
stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
//excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelDataReader = ExcelReaderFactory.CreateBinaryReader(stream);
excelDataReader.IsFirstRowAsColumnNames = true;
dataSet = excelDataReader.AsDataSet();
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + (eException.InnerException != null && !string.IsNullOrEmpty(eException.InnerException.Message) ? "InnerException.Message" + eException.InnerException.Message + Environment.NewLine : string.Empty) + "StackTrace:" + Environment.NewLine + eException.StackTrace);
return;
}
finally
{
if (stream != null)
{
stream.Dispose();
}
if (excelDataReader != null)
{
excelDataReader.Dispose();
}
}
if (dataSet == null || dataSet.Tables.Count == 0)
{
return;
}
foreach (DataRow row in dataSet.Tables[0].Rows)
{
foreach (DataColumn column in dataSet.Tables[0].Columns)
{
Type columnType = column.DataType;
Type valueType = !row.IsNull(column.ColumnName) ? row[column.ColumnName].GetType() : DBNull.Value.GetType();
if (columnType != valueType)
{
Console.WriteLine("{0} != {1}", columnType, valueType);
}
}
}
}
