///
<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
xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook
workbookData;
Microsoft.Office.Interop.Excel.Worksheet
worksheetData;
workbookData =
xlApp.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(xlApp.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)
{
bool isTime = false;
DateTime datetime=DateTime.Now;
try
{
datetime =
Convert.ToDateTime(objVal[i, j]);
isTime = true;
}catch(Exception)
{
}
if (isTime)
{
dr[j - 1] =
datetime.ToShortDateString();
}
else
{
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)
{
xlApp.Quit();
strErrorMessage =
ex.Message;
return null;
}
xlApp.Quit();
return dt;
}
