public System.Data.DataTable GetData(string pSheetName, int intGetMaxRowColIndex = 1, int intGetMaxColRowIndex = 1, int intbeginRow = 1)
{
int eachRow = 50000;
System.Data.DataTable dt = null;
ws = wb.Worksheets[pSheetName];
ws.Activate();
Range rngBegin, rngEnd, rngCell;
int intmaxRowFromSpecialCol = ws.Cells[ws.Rows.Count, intGetMaxRowColIndex].End(XlDirection.xlUp).row;
int intmaxColFromSpecialCol = ws.Cells[intGetMaxColRowIndex, ws.Columns.Count].End(XlDirection.xlToLeft).column;
int colNum = ws.UsedRange.CurrentRegion.Columns.Count;
int rowNum = ws.UsedRange.CurrentRegion.Rows.Count;
if (intmaxRowFromSpecialCol > rowNum)
{
rowNum = intmaxRowFromSpecialCol;
}
if (intmaxColFromSpecialCol > colNum)
{
colNum = intmaxColFromSpecialCol;
}
int beginRow = intbeginRow;
for (int endRow = 0; endRow < rowNum;)
{
endRow = (endRow + eachRow) > rowNum ? rowNum : (endRow + eachRow);
rngBegin = ws.Cells[beginRow, 1];
rngEnd = ws.Cells[endRow, colNum];
rngCell = ws.Range[rngBegin, rngEnd];
rngCell.Select();
object[,] obj = (object[, ])rngCell.Value;
if (dt == null)
{
dt = ObjectHelper.ObjectToDataTable(obj);
}
else
{
System.Data.DataTable tempDT = dt.Clone();
tempDT = ObjectHelper.ObjectToDataTable(obj, false, tempDT);
foreach (DataRow item in tempDT.Rows)
{
dt.ImportRow(item);
}
}
Marshal.FinalReleaseComObject(rngBegin);
Marshal.FinalReleaseComObject(rngEnd);
Marshal.FinalReleaseComObject(rngCell);
beginRow = beginRow + eachRow;
}
return(dt);
}
public System.Data.DataTable GetData(string pSheetName)
{
int eachRow = 50000;
System.Data.DataTable dt = null;
ws = wb.Worksheets[pSheetName];
Range rngBegin, rngEnd, rngCell;
int colNum = ws.UsedRange.CurrentRegion.Columns.Count;
int rowNum = ws.UsedRange.CurrentRegion.Rows.Count;
int beginRow = 1;
for (int endRow = 0; endRow < rowNum;)
{
endRow = (endRow + eachRow) > rowNum ? rowNum : (endRow + eachRow);
rngBegin = ws.Cells[beginRow, 1];
rngEnd = ws.Cells[endRow, colNum];
rngCell = ws.Range[rngBegin, rngEnd];
object[,] obj = (object[, ])rngCell.Value;
if (dt == null)
{
dt = ObjectHelper.ObjectToDataTable(obj);
}
else
{
System.Data.DataTable tempDT = dt.Clone();
tempDT = ObjectHelper.ObjectToDataTable(obj, false, tempDT);
foreach (DataRow item in tempDT.Rows)
{
dt.ImportRow(item);
}
}
Marshal.FinalReleaseComObject(rngBegin);
Marshal.FinalReleaseComObject(rngEnd);
Marshal.FinalReleaseComObject(rngCell);
beginRow = beginRow + eachRow;
}
return(dt);
}