using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System.Data;
using System.Web;
using System.Text.RegularExpressions;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string fileName = "C:\\Users\\hankham\\Desktop\\临时文件\\2018年度需求清单20171227-风险.xlsx";
DataTable dt = GetDataTable(fileName);
int row = dt.Rows.Count;
int columns = dt.Columns.Count;
Console.WriteLine(row);
Console.WriteLine(columns);
Console.WriteLine(dt.Rows[7380][8].ToString());
//string fileName = "C:\\Users\\hankham\\Desktop\\临时文件\\新建文件夹\\行情文件\\cmcMarketValue_bond.csv";
//Program pr = new Program();
//DataTable dt = pr.ImportCsvToDataTable(fileName);
//int row = dt.Rows.Count;
//int columns = dt.Columns.Count;
//Console.WriteLine(row);
//Console.WriteLine(columns);
//Console.WriteLine(columns);
//Console.WriteLine(dt.Rows[8][8].ToString());
}
public static DataTable GetDataTable(string filepath)
{
var dt = new DataTable("xls");
if (filepath.Last() == 's')
{
dt = ImportExcelFileOld(filepath);
}
else
{
dt = ImportExcelFileNew(filepath);
}
return dt;
}
public static DataTable ImportExcelFileOld(string filePath)
{
try
{
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
int num = sheet.GetRow(0).LastCellNum;
for (int j = 0; j < num; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (num < row.LastCellNum)
{
throw new Exception("最大列数不能大于表格起始列数");
}
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
catch (Exception e)
{
throw e;
}
}
public static DataTable ImportExcelFileNew(string filePath)
{
try
{
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);
ISheet sheet = xssfworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
int num = sheet.GetRow(0).LastCellNum;
for (int j = 0; j < num; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (num < row.LastCellNum)
{
throw new Exception("最大列数不能大于表格起始列数");
}
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Stream读取.csv文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public DataTable ImportCsvToDataTable(string filePath)
{
FileStream fs = null;
StreamReader sr = null;
try
{
DataTable dt = new DataTable();
fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
sr = new StreamReader(fs, System.Text.Encoding.Default);
//记录每次读取的一行记录
string strLine = "";
//记录每行记录中的各字段内容
string[] aryLine;
//标示列数
int columnCount = 0;
int firstCount = 0;
Boolean first = true;
//逐行读取CSV中的数据
while ((strLine = sr.ReadLine()) != null)
{
aryLine = strLine.Split(',');
columnCount = aryLine.Length;
if (first)
{
for (int j = 0; j < columnCount; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
first = false;
firstCount = columnCount;
}
else
{
DataRow dr = dt.NewRow();
if (firstCount < columnCount)
{
throw new Exception("最大列数不能大于表格起始列数");
}
for (int j = 0; j < columnCount - 1; j++)
{
string str = aryLine[j];
dr[j] = str;
}
dt.Rows.Add(dr);
}
}
return dt;
}
catch (Exception e)
{
throw e;
}
finally
{
sr.Close();
fs.Close();
}
}
}
}
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System.Data;
using System.Web;
using System.Text.RegularExpressions;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string fileName = "C:\\Users\\hankham\\Desktop\\临时文件\\2018年度需求清单20171227-风险.xlsx";
DataTable dt = GetDataTable(fileName);
int row = dt.Rows.Count;
int columns = dt.Columns.Count;
Console.WriteLine(row);
Console.WriteLine(columns);
Console.WriteLine(dt.Rows[7380][8].ToString());
//string fileName = "C:\\Users\\hankham\\Desktop\\临时文件\\新建文件夹\\行情文件\\cmcMarketValue_bond.csv";
//Program pr = new Program();
//DataTable dt = pr.ImportCsvToDataTable(fileName);
//int row = dt.Rows.Count;
//int columns = dt.Columns.Count;
//Console.WriteLine(row);
//Console.WriteLine(columns);
//Console.WriteLine(columns);
//Console.WriteLine(dt.Rows[8][8].ToString());
}
public static DataTable GetDataTable(string filepath)
{
var dt = new DataTable("xls");
if (filepath.Last() == 's')
{
dt = ImportExcelFileOld(filepath);
}
else
{
dt = ImportExcelFileNew(filepath);
}
return dt;
}
public static DataTable ImportExcelFileOld(string filePath)
{
try
{
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
int num = sheet.GetRow(0).LastCellNum;
for (int j = 0; j < num; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (num < row.LastCellNum)
{
throw new Exception("最大列数不能大于表格起始列数");
}
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
catch (Exception e)
{
throw e;
}
}
public static DataTable ImportExcelFileNew(string filePath)
{
try
{
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);
ISheet sheet = xssfworkbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
int num = sheet.GetRow(0).LastCellNum;
for (int j = 0; j < num; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
if (num < row.LastCellNum)
{
throw new Exception("最大列数不能大于表格起始列数");
}
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Stream读取.csv文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public DataTable ImportCsvToDataTable(string filePath)
{
FileStream fs = null;
StreamReader sr = null;
try
{
DataTable dt = new DataTable();
fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
sr = new StreamReader(fs, System.Text.Encoding.Default);
//记录每次读取的一行记录
string strLine = "";
//记录每行记录中的各字段内容
string[] aryLine;
//标示列数
int columnCount = 0;
int firstCount = 0;
Boolean first = true;
//逐行读取CSV中的数据
while ((strLine = sr.ReadLine()) != null)
{
aryLine = strLine.Split(',');
columnCount = aryLine.Length;
if (first)
{
for (int j = 0; j < columnCount; j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
first = false;
firstCount = columnCount;
}
else
{
DataRow dr = dt.NewRow();
if (firstCount < columnCount)
{
throw new Exception("最大列数不能大于表格起始列数");
}
for (int j = 0; j < columnCount - 1; j++)
{
string str = aryLine[j];
dr[j] = str;
}
dt.Rows.Add(dr);
}
}
return dt;
}
catch (Exception e)
{
throw e;
}
finally
{
sr.Close();
fs.Close();
}
}
}
}