/// <summary>
/// Excel导入DataTable
/// </summary>
/// <param
name="strFileName"> 文件名称</param>
/// <param
name="isHead"> 是否包含表头 </param>
/// <param
name="iSheet"> Sheet</param>
/// <param
name="strErrorMessage"> 错误信息</param>
/// <param
name="iRowsIndex"> 导入的Excel的开始行 </param>
/// <returns></returns>
public static System.Data.DataTable GetDataFromExcel( string strFileName, bool isHead, int iSheet,
string strErrorMessage, int iRowsIndex)
{
if (!strFileName.ToUpper().EndsWith(".XLSX"))
{
strErrorMessage = "文件类型与系统设定不一致，请核对！" ;
return null ;
}
Microsoft.Office.Interop.Excel. Application appExcel
= new Microsoft.Office.Interop.Excel.Application ();
Microsoft.Office.Interop.Excel. Workbook workbookData;
Microsoft.Office.Interop.Excel. Worksheet worksheetData;
workbookData = appExcel.Workbooks.Open(strFileName,
System.Reflection. Missing.Value,
System.Reflection.Missing .Value,
System.Reflection.Missing.Value,
System.Reflection. Missing.Value,
System.Reflection.Missing .Value,
System.Reflection. Missing.Value,
System.Reflection.Missing .Value,
System.Reflection.Missing .Value,
System.Reflection.Missing.Value,
System.Reflection. Missing.Value,
System.Reflection.Missing .Value,
System.Reflection.Missing.Value);
worksheetData = (Microsoft.Office.Interop.Excel. Worksheet)workbookData.Sheets[iSheet];
Microsoft.Office.Interop.Excel. Range xlRang
= null ;
int iRowCount =
worksheetData.UsedRange.Cells.Rows.Count;
int iParstedRow =
0, iCurrSize = 0;
int iEachSize =
1000; //
each time you
int iColumnAccount
= worksheetData.UsedRange.Cells.Columns.Count;
int iHead =
iRowsIndex;
if (isHead)
iHead = iRowsIndex + 1;
System.Data. DataTable dt
= new System.Data.DataTable();
for (int i = 1; i
<= iColumnAccount; i++)
{
if (isHead)
dt.Columns.Add(appExcel.Cells[iRowsIndex, i].FormulaLocal);
else
dt.Columns.Add( "Columns" +
i.ToString());
}
object[,] objVal
= new object[iEachSize,
iColumnAccount];
try
{
iCurrSize = iEachSize;
while (iParstedRow
< iRowCount)
{
if ((iRowCount -
iParstedRow) < iEachSize)
iCurrSize = iRowCount - iParstedRow;
xlRang = worksheetData.get_Range( "A" +
((int )(iParstedRow
+ iHead)).ToString(), ((char)(‘A‘ +
iColumnAccount - 1)).ToString()
+ ((( int)(iParstedRow
+ iCurrSize + 1)).ToString()));
objVal = ( object[,])xlRang.Value2;
int iLength =
objVal.Length / iColumnAccount;
for (int i = 1; i <
iLength; i++)
{
DataRow dr =
dt.NewRow();
for (int j = 1; j
<= iColumnAccount; j++)
{
if (objVal[i, j]
!= null )
{
dr[j - 1] = objVal[i, j].ToString();
}
}
dt.Rows.Add(dr);
}
iParstedRow = iParstedRow + iCurrSize;
}
System.Runtime.InteropServices. Marshal.ReleaseComObject(xlRang);
xlRang = null;
}
catch (Exception ex)
{
appExcel.Quit();
strErrorMessage = ex.Message;
return null ;
}
appExcel.Quit();
return dt;
}
