/// <summary>
/// EXCEL导入功能集合类
/// 作者:Zuowenjun
/// 日期:2016/1/15
/// </summary>
public sealed class Import
{
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
ISheet sheet = null;
int sheetIndex = -1;
if (int.TryParse(sheetName, out sheetIndex))
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径,可传空值</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(string excelFilePath, string sheetName, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = Common.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
return ToDataTable(stream, sheetName, headerRowIndex, isCompatible);
}
}
#region 整治任务导入
public static DataTable ToDataTableAgency(string excelFilePath, string sheetName, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = Common.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
return ToDataTableAgency(stream, sheetName, headerRowIndex, isCompatible);
}
}
public static DataTable ToDataTableAgency(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
ISheet sheet = null;
int sheetIndex = -1;
DataTable table = null;
int num = workbook.NumberOfSheets;
if (num > 2)
{
for (int i = 2; i <= num - 2; i++)
{
sheet = workbook.GetSheetAt(i);
if (i == 2)
{
table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
}
else
{
if (table != null)
{
DataTable tv = Common.GetDataTableFromSheet(sheet, headerRowIndex);
table.Merge(tv);
}
}
}
}
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
#endregion
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataSet</returns>
public static DataSet ToDataSet(Stream excelFileStream, int headerRowIndex, bool isCompatible)
{
DataSet ds = new DataSet();
IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
ds.Tables.Add(table);
}
excelFileStream.Close();
workbook = null;
return ds;
}
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径。可传空值</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ToDataSet(string excelFilePath, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = Common.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
return ToDataSet(stream, headerRowIndex, isCompatible);
}
}
public static List<string> GetNumberOfSheets(string excelFilePath)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
List<string> listName = new List<string>();
IWorkbook workbook = Common.CreateWorkbook(false, stream);
int count = workbook.NumberOfSheets;
for (int i = 0; i < count; i++)
{
listName.Add(workbook.GetSheetName(i));
}
return listName;
}
}
/// <summary>
/// 处理映射关系
/// </summary>
public static Dictionary<string, string> LoadMapping(string mappingXML)
{
Dictionary<string, string> mappingDic = new Dictionary<string, string>();
XmlDocument xmldoc = new XmlDocument();
xmldoc.Load(mappingXML);
XmlNodeList list = xmldoc.SelectNodes("//Field");
for (int i = 0; i < list.Count; i++)
{
XmlNode node = list[i];
string excelCoumn = node.Attributes["excelCoumn"].Value;
string column = node.Attributes["column"].Value;
mappingDic.Add(excelCoumn, column);
}
return mappingDic;
}
}
/// EXCEL导入功能集合类
/// 作者:Zuowenjun
/// 日期:2016/1/15
/// </summary>
public sealed class Import
{
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
ISheet sheet = null;
int sheetIndex = -1;
if (int.TryParse(sheetName, out sheetIndex))
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径,可传空值</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(string excelFilePath, string sheetName, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = Common.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
return ToDataTable(stream, sheetName, headerRowIndex, isCompatible);
}
}
#region 整治任务导入
public static DataTable ToDataTableAgency(string excelFilePath, string sheetName, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = Common.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
return ToDataTableAgency(stream, sheetName, headerRowIndex, isCompatible);
}
}
public static DataTable ToDataTableAgency(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
ISheet sheet = null;
int sheetIndex = -1;
DataTable table = null;
int num = workbook.NumberOfSheets;
if (num > 2)
{
for (int i = 2; i <= num - 2; i++)
{
sheet = workbook.GetSheetAt(i);
if (i == 2)
{
table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
}
else
{
if (table != null)
{
DataTable tv = Common.GetDataTableFromSheet(sheet, headerRowIndex);
table.Merge(tv);
}
}
}
}
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
#endregion
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <param name="isCompatible">是否为兼容模式</param>
/// <returns>DataSet</returns>
public static DataSet ToDataSet(Stream excelFileStream, int headerRowIndex, bool isCompatible)
{
DataSet ds = new DataSet();
IWorkbook workbook = Common.CreateWorkbook(isCompatible, excelFileStream);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
ds.Tables.Add(table);
}
excelFileStream.Close();
workbook = null;
return ds;
}
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径。可传空值</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ToDataSet(string excelFilePath, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = Common.GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
return ToDataSet(stream, headerRowIndex, isCompatible);
}
}
public static List<string> GetNumberOfSheets(string excelFilePath)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
List<string> listName = new List<string>();
IWorkbook workbook = Common.CreateWorkbook(false, stream);
int count = workbook.NumberOfSheets;
for (int i = 0; i < count; i++)
{
listName.Add(workbook.GetSheetName(i));
}
return listName;
}
}
/// <summary>
/// 处理映射关系
/// </summary>
public static Dictionary<string, string> LoadMapping(string mappingXML)
{
Dictionary<string, string> mappingDic = new Dictionary<string, string>();
XmlDocument xmldoc = new XmlDocument();
xmldoc.Load(mappingXML);
XmlNodeList list = xmldoc.SelectNodes("//Field");
for (int i = 0; i < list.Count; i++)
{
XmlNode node = list[i];
string excelCoumn = node.Attributes["excelCoumn"].Value;
string column = node.Attributes["column"].Value;
mappingDic.Add(excelCoumn, column);
}
return mappingDic;
}
}