/// <summary>
/// Get Data from excel file
/// </summary>
/// <param name="stream">upload file stream</param>
/// <param name="fieldsMapping">Field Mapping keys</param>
/// <param name="batchId">Execute Batch Id</param>
/// <param name="s_fileName">Upload File Name</param>
/// <param name="curDate">Execute Date</param>
/// <returns></returns>
public static System.Data.DataTable ReadUploadExcel(Stream stream, int startRowNum, List <MappingKey> fields)
{
System.Data.DataTable dtSource = new System.Data.DataTable();
try
{
#region Read Data
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, false))
{
// Get Workbook Part of Spread Sheet Document
WorkbookPart workbookPart = doc.WorkbookPart;
IEnumerable <Sheet> sheets = workbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string firstSheetId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(firstSheetId);
Worksheet workSheet = worksheetPart.Worksheet;
// Get Data in Excel file
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rowcollection = sheetData.Descendants <Row>();
List <Row> rows = rowcollection.ToList();
int eRowsCount = rows.Count();
List <ExcelField> excelFields = getExcelFields(doc, rows[startRowNum - 2]);
List <ExcelField> sInsertFields = new List <ExcelField>();
#region DataTable 结构定义
foreach (ExcelField e_field in excelFields)
{
MappingKey mk = fields.Where(fm => (fm.Key.ToUpper() == e_field.CellField.ToUpper() && fm.DBField != null)).FirstOrDefault();
if (mk != null)
{
DataColumn dc = new DataColumn(mk.DBField);
dtSource.Columns.Add(dc);
e_field.DBField = mk.DBField;
e_field.fType = mk.fType;
sInsertFields.Add(e_field);
}
}
#endregion
// Add rows into DataTable
for (int i = startRowNum - 1; i < eRowsCount; i++)
{
DataRow dRow = dtSource.NewRow();
IEnumerable <Cell> row = rows[i].Descendants <Cell>();
foreach (Cell cell in row)
{
string sValue = GetValueOfCell(doc, cell);
StringValue sv = cell.CellReference;
if (sv != null)
{
string sCellReference = RemoveNumFromCellRef(sv.ToString(), (i + 1).ToString().Length);
ExcelField eField = sInsertFields.Where(sIF => sIF.CellReference == sCellReference && sCellReference != "").FirstOrDefault();
if (eField != null)
{
if (eField.fType == FieldDataType.Date || eField.fType == FieldDataType.DateAndString)
{
dRow[eField.DBField] = ConvertToDate(sValue);
}
else if (eField.fType == FieldDataType.Time)
{
dRow[eField.DBField] = ConvertToTime(sValue);
}
else if (eField.fType == FieldDataType.Float)
{
dRow[eField.DBField] = ConvertToFloat(sValue);
}
else
{
dRow[eField.DBField] = sValue;
}
}
}
}
//The current For loop will be terminated if the product code is empty.
MappingKey pCodeKey = fields.Where(mk => mk.DBField == "ContactCode").FirstOrDefault();
if (pCodeKey == null)
{
continue;
}
if (dRow[pCodeKey.DBField] == null || string.IsNullOrWhiteSpace(dRow[pCodeKey.DBField].ToString()))
{
continue;
}
// Add other field value
dtSource.Rows.Add(dRow);
}
}
#endregion
}
catch (System.Exception ex)
{
//DTCLog.WriteLog("ReadMMXExcel :" + ex.Message);
}
return(dtSource);
}
//public static void ExtendColumns(System.Data.DataTable dt, TemplateType temp, int batchId, string fileName, SessionUser user)
//{
// try
// {
// DateTime curDate = DateTime.Now;
// //扩展DB列
// if (temp == TemplateType.MMX)
// {
// //AddExtendedMMXColumns(dt);
// dt.SetColumnDefaultValue("Batch_ID", batchId);
// dt.SetColumnDefaultValue("Load_Date", curDate);
// }
// else
// {
// //AddExtendedOtherColumns(dt);
// if (temp == TemplateType.MLPMMX)
// {
// dt.SetColumnDefaultValue(Util.Col_Name_MMX_ProductInfoOwner, user.UserName);
// }
// dt.SetColumnDefaultValue("BatchID", batchId);
// dt.SetColumnDefaultValue("LoadDate", curDate);
// dt.SetColumnDefaultValue("SheetName", temp.ToString());
// }
// dt.SetColumnDefaultValue("FlatFileName", fileName);
// dt.SetColumnDefaultValue("LoadUser", user.UserId);
// }
// catch (Exception ex)
// {
// throw ex;
// }
//}
/// <summary>
/// Read Data from selected excel file into DataTable
/// </summary>
/// <param name="filename">Excel File Path</param>
/// <returns></returns>
public static System.Data.DataTable ReadExcelFromStream(Stream stream, List <MappingKey> mKeys, string s_table_name, int tIndex)
{
// Initialize an instance of DataTable
System.Data.DataTable dt = new System.Data.DataTable(s_table_name);
try
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
{
// Get Workbook Part of Spread Sheet Document
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = workbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string firstSheetId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(firstSheetId);
Worksheet workSheet = worksheetPart.Worksheet;
//WorksheetPart wsPart = workbookPart.WorksheetParts.FirstOrDefault();
// Get Data in Excel file
//SheetData sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rowcollection = sheetData.Descendants <Row>();
List <Row> rows = rowcollection.ToList();
UInt32Value eRowsCount = (uint)rows.Count();
if (eRowsCount <= tIndex + 1)
{
return(dt);
}
else
{
Row _eRow = rows[tIndex];
foreach (Cell cell in _eRow.Descendants <Cell>())
{
// Get Cell Column Index
string sCellName = GetValueOfCell(spreadsheetDocument, cell);
if (!string.IsNullOrEmpty(sCellName))
{
dt.Columns.Add(sCellName);
MappingKey mKey = mKeys.Where(key => key.Key.ToUpper() == sCellName.ToUpper()).FirstOrDefault();
if (mKey != null)
{
mKey.CallReference = GetCellReference(cell.CellReference);
}
}
else
{
break;
}
}
}
int colsCount = dt.Columns.Count;
// Add rows into DataTable
for (int i = tIndex + 1; i < eRowsCount; i++)
{
DataRow temprow = dt.NewRow();
foreach (Cell cell in rows[i].Descendants <Cell>())
{
string sValue = GetValueOfCell(spreadsheetDocument, cell);
StringValue sv = cell.CellReference;
if (sv != null)
{
string sCellReference = RemoveNumFromCellRef(sv.ToString(), (i + 1).ToString().Length);
MappingKey mKey = mKeys.Where(k => k.CallReference == sCellReference && sCellReference != "").FirstOrDefault();
if (mKey != null)
{
if (mKey.fType == FieldDataType.Date)
{
temprow[mKey.Key] = ConvertToDate(sValue);
}
else if (mKey.fType == FieldDataType.Time)
{
temprow[mKey.Key] = ConvertToTime(sValue);
}
else
{
temprow[mKey.Key] = sValue;
}
}
}
}
//Add the row to DataTable
dt.Rows.Add(temprow);
}
}
return(dt);
}
catch (IOException ex)
{
throw new IOException(ex.Message);
}
}
