public static DataSet ImportExcelDataset(string key)
{
ICacheManager cacheManager = new MemoryCacheManager();
return(cacheManager.Get <DataSet>(key));
}
public static DataSet GetDataSet(string path, string fileName, ref string errorMessage, ref string sessionKey)
{
ICacheManager cacheManager = new MemoryCacheManager();
DataSet ds = new DataSet();
PagingDataTable dt = null;
try {
string inputFileName = System.IO.Path.Combine(path, fileName);
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(inputFileName, false)) {
//Access the main Workbook part, which contains data
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart worksheetPart = null;
List <Sheet> sheets = workbookPart.Workbook.Descendants <Sheet>().ToList();
foreach (var ss in sheets)
{
dt = new PagingDataTable();
dt.TableName = ss.Name;
worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
if (worksheetPart != null)
{
string relationshipId = sheets.First().Id.Value;
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
if (rows.ToArray().Count() > 0)
{
foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add(GetCellValue(myWorkbook, cell));
}
int rowIndex = 0;
foreach (Row row in rows) //this will also include your header row...
{
if (rowIndex > 0)
{
DataRow tempRow = dt.NewRow();
int columnIndex = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
cellColumnIndex--; //zero based index
if (columnIndex < cellColumnIndex)
{
do
{
try {
tempRow[columnIndex] = ""; //Insert blank data here;
} catch { }
columnIndex++;
}while (columnIndex < cellColumnIndex);
}
try {
tempRow[columnIndex] = GetCellValue(myWorkbook, cell);
} catch { }
columnIndex++;
}
bool isAllColumnBlank = true;
foreach (DataColumn col in dt.Columns)
{
if (string.IsNullOrEmpty(Convert.ToString(tempRow[col.ColumnName])) == false)
{
isAllColumnBlank = false;
break;
}
}
if (isAllColumnBlank == false)
{
dt.Rows.Add(tempRow);
}
}
rowIndex++;
}
dt.Columns.Add(new DataColumn {
DataType = typeof(int),
//AutoIncrement = true,
//AutoIncrementSeed = 1,
//AutoIncrementStep = 1,
ColumnName = "RowNumber",
//AllowDBNull = false,
});
dt.Columns.Add(new DataColumn {
ColumnName = "ImportError",
});
rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
row["RowNumber"] = rowIndex;
rowIndex++;
}
ds.Tables.Add(dt);
}
}
}
}
Guid guid = System.Guid.NewGuid();
sessionKey = string.Format(EXCELDATABASE_BY_KEY, guid);
cacheManager.Set(sessionKey, ds, 120);
} catch (Exception ex) {
errorMessage = ex.Message.ToString();
} finally {
UploadFileHelper.DeleteFile("TempPath", fileName);
}
return(ds);
}
