/// <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; } }