// ----------------------32位机
//注释说明
//ExclePath 为Excel路径 批号 是指Excel文件中某一列必填项
public static DataSet GetDataTableForExcel(String ExclePath)
{
string strCon = String.Empty;
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExclePath + "; Extended Properties='Excel 8.0;IMEX=1';";
OleDbConnection olecon = new OleDbConnection(strCon);
olecon.Open();
DataTable dt = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string tableName = dt.Rows[][].ToString().Trim();
OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [" + tableName + "] Where 批号 is not null", strCon);// Where 条件根据实际情况进行更改
DataSet myds = new DataSet();
try
{
myda.Fill(myds);
}
catch { myds = null; }
olecon.Close();
return myds;
}
//-----------------------------64位机
/// <summary>
/// 把Excel里的数据转换为DataTable,应用引用的com组件：Microsoft.Office.Interop.Excel.dll 读取EXCEL文件
/// </summary>
/// <param name="filenameurl">物理路径</param>
/// <param name="sheetIndex">sheet名称的索引</param>
/// <param name="splitstr">如果是已存在列，则自定义添加的字符串</param>
/// <returns></returns>
public static DataTable ExecleToDataSet(string filenameurl, int sheetIndex, string splitstr)
{
//
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
bool isEqual = false;//不相等
ArrayList columnArr = new ArrayList();//列字段表
DataSet myDs = new DataSet();
DataTable xlsTable = myDs.Tables.Add("show");
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//lauch excel application
if (excel != null)
{
excel.Visible = false;
excel.UserControl = true;
// 以只读的形式打开EXCEL文件
wb = excel.Workbooks.Open(filenameurl, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//取得第一个工作薄
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(sheetIndex);
//取得总记录行数(包括标题列)
int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = ws.UsedRange.Cells.Columns.Count;//得到列数
DataRow dr;
for (int i = ; i <= columnsint; i++)
{
//判断是否有列相同
if (i >= )
{
int r = ;
for (int k = ; k <= i - ; k++)//列从第一列到第i-1列遍历进行比较
{
if (((Microsoft.Office.Interop.Excel.Range)ws.Cells[, i]).Text.ToString() == ((Microsoft.Office.Interop.Excel.Range)ws.Cells[, k]).Text.ToString())
{
//如果该列的值等于前面列中某一列的值
xlsTable.Columns.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[, i]).Text.ToString() + splitstr + (r + ).ToString(), typeof(string));
columnArr.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[, i]).Text.ToString() + splitstr + (r + ).ToString());
isEqual = true;
r++;
break;
}
else
{
isEqual = false;
continue;
}
}
if (!isEqual)
{
xlsTable.Columns.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[, i]).Text.ToString(), typeof(string));
columnArr.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[, i]).Text.ToString());
}
}
else
{
xlsTable.Columns.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[, i]).Text.ToString(), typeof(string));
columnArr.Add(((Microsoft.Office.Interop.Excel.Range)ws.Cells[, i]).Text.ToString());
}
}
for (int i = ; i <= rowsint; i++)
{
dr = xlsTable.NewRow();
for (int j = ; j <= columnsint; j++)
{
dr[columnArr[j - ].ToString()] = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[i, j]).Text.ToString();
}
xlsTable.Rows.Add(dr);
}
}
excel.Quit();
excel = null;
Dispose(ws, wb);
return xlsTable;
}