/// <summary>
/// 读取Excel流到DataSet
/// </summary>
/// <param name="stream">Excel流</param>
/// <param name="dict">字典参数，key：sheet名，value：列头所在行号，-1表示没有列头</param>
/// <returns>Excel中的数据</returns>
public static DataSet ImportExceltoDs(Stream stream, Dictionary<string, int> dict)
{
try
{
DataSet ds = new DataSet();
IWorkbook wb;
using (stream)
{
wb = WorkbookFactory.Create(stream);
}
foreach (string key in dict.Keys)
{
DataTable dt = new DataTable();
ISheet sheet = wb.GetSheet(key);
dt = ImportDt(sheet, dict[key], true);
dt.TableName = key;
ds.Tables.Add(dt);
}
return ds;
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <returns></returns>
static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader)
{
DataTable table = new DataTable();
IRow headerRow;
int cellCount;
try
{
if (HeaderRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(HeaderRowIndex);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
IRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.String:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
throw;
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
throw;
}
}
}
catch (Exception exception)
{
throw;
}
return table;
}
/// <summary>
/// 读取CSV文件
/// </summary>
public static DataTable DarazReadCSV(Stream fs)
{
try
{
DataTable dt = new DataTable();
StreamReader sr = new StreamReader(fs, Encoding.UTF8);
//记录每次读取的一行记录
string strLine = null;
//记录每行记录中的各字段内容
//string[] arrayLine = null;
//分隔符
//string[] separators = { "," };
//判断，若是第一次，建立表头
bool isFirst = true;
string[] aryline;
//逐行读取CSV文件
while ((strLine = sr.ReadLine()) != null)
{
strLine = strLine.Trim();//去除头尾空格
if (isFirst)
{
aryline = strToAry(strLine);//请注意：此处变了
}
else
{
MatchCollection mc = Regex.Matches(strLine, "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)");
aryline = new string[mc.Count];
for (int i = 0; i < mc.Count; i++)
{
aryline[i] = mc[i].Groups[0].Value;
}
}
int dtColumns = aryline.Length;//列的个数
if (isFirst) //建立表头
{
for (int i = 0; i < dtColumns; i++)
{
dt.Columns.Add(aryline[i]);//每一列名称
}
isFirst = false;
}
else //表内容
{
//设置变量num,获取i的最大值
int y = 0;
DataRow dataRow = dt.NewRow();//新建一行
for (int j = 0; j < dtColumns; j++)
{
y = j;
if (j == dtColumns) break;
dataRow[j] = aryline[j];
}
if (y != dtColumns && dataRow != null)
{
dt.Rows.Add(dataRow);//添加一行
}
}
}
sr.Close();
fs.Close();
return dt;
}
catch (Exception ex)
{
throw;
}
}
}
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>返回datatable</returns>
public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
{
DataTable dataTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int startRow = 0;
try
{
using (fs = File.OpenRead(filePath))
{
// 2007版本
if (filePath.IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook(fs);
// 2003版本
else if (filePath.IndexOf(".xls") > 0)
workbook = new HSSFWorkbook(fs);
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
dataTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0);//第一行
int cellCount = firstRow.LastCellNum;//列数
//构建datatable的列
if (isColumnName)
{
startRow = 1;//如果第一行是列名，则从第二行开始读取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null) continue;
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
//case CellType.Blank:
// dataRow[j] = "";
// break;
//case CellType.Numeric:
// short format = cell.CellStyle.DataFormat;
// //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
// if (format == 14 || format == 31 || format == 57 || format == 58)
// {
// if (string.IsNullOrEmpty(cell.StringCellValue)) dataRow[j] = cell.NumericCellValue;
// dataRow[j] = cell.DateCellValue;
// }
// else {
// dataRow[j] = cell.NumericCellValue;
// }
// break;
//case CellType.String:
// dataRow[j] = cell.StringCellValue;
// break;
case CellType.String:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
return dataTable;
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
return null;
}
}
#region
/// <summary>
/// 导入Excel得到 DataTable
/// </summary>
public static DataTable ImportExceltoDt(Stream stream, int HeaderRowIndex=0)
{
IWorkbook wb;
using (stream)
{
wb = WorkbookFactory.Create(stream);
}
ISheet sheet = wb.GetSheetAt(0);
DataTable table = new DataTable();
IRow headerRow;
int cellCount;
try
{
headerRow = sheet.GetRow(HeaderRowIndex);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
IRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.String:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
throw;
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
throw;
}
}
}
catch (Exception exception)
{
throw;
}
return table;
}
浙公网安备 33010602011771号