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