/// <summary>
/// 将excel数据导入到datatable
/// </summary>
/// <param name="dt">带结构的空表</param>
/// <param name="fileName">数据文件</param>
/// <returns>数据表</returns>
public Tuple <DataTable, List <string> > ImportExcelTuple(string fileName)
{
DataTable infoDt = null;
ArrayList dataArray = null;
try
{
var data = new ExcelOperationHelper().ReadWorkbook(fileName);
if (data == null)
{
return(null);
}
//获取数据
dataArray = data.Item1;
List <string> li = data.Item2;
//数据格式转换
infoDt = this.GetDataTable(dataArray);
return(new Tuple <DataTable, List <string> >(infoDt, li));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 数据格式转换(datatable to arraylist)
/// </summary>
/// <param name="info">导出配置信息</param>
/// <param name="dt">导出数据</param>
/// <returns>格式转换后数据</returns>
private ArrayList GetDataArray(SheetInfo info, DataTable dt)
{
ArrayList array = new ArrayList();
// 添加：扩展支持 表标题显示 李鹏飞 2014-03-31 开始
// 表标题
info.TableCaption = dt.TableName;
// 添加：扩展支持 表标题显示 李鹏飞 2014-03-31 结束
int count = 0;
int rowIndex = 0;
int colIndex = 0;
foreach (DataRow row in dt.Rows)
{
List <CellInfo> list = new List <CellInfo>();
foreach (CellInfo item in info.ColInfos)
{
rowIndex = int.Parse(item.YPosition) + count - 1;
colIndex = ExcelOperationHelper.NameToIndex(item.XPosition);
CellInfo m = new CellInfo();
m.XPosition = colIndex.ToString();
m.YPosition = rowIndex.ToString();
m.ColumnName = item.ColumnName;
m.DataType = item.DataType;
if (dt.Columns.Contains(item.ColumnName) == true)
{
if (row[item.ColumnName] != System.DBNull.Value)
{
m.Value = row[item.ColumnName].ToString();
}
else
{
m.Value = "";
}
}
else
{
m.Value = "";
}
list.Add(m);
}
array.Add(list);
count++;
if (info.RecordCount == "1" && count == 1)
{
break;
}
}
return(array);
}
/// <summary>
/// 将excel数据导入到datatable
/// </summary>
/// <param name="info">配置信息</param>
/// <param name="dt">带结构的空表</param>
/// <param name="fileName">数据文件</param>
/// <returns>数据表</returns>
public DataTable ImportExcel(SheetInfo info, DataTable dt, string fileName)
{
DataTable infoDt = null;
ArrayList dataArray = null;
try
{
//获取数据
dataArray = new ExcelOperationHelper().ReadWorkbook(info, fileName);
//数据格式转换
infoDt = this.GetDataTable(info, dt, dataArray);
return(infoDt);
}
catch (Exception ex)
{
throw ex;
}
}