public DataSet ExceltoDataSet(string path)
{
string strType = path.Substring(path.IndexOf(".") + 1);
string strConn = "";
if (strType == "xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
}
else if (strType == "xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds =new DataSet();
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
string tableName = schemaTable.Rows[i][2].ToString().Trim();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "Select * From [" + tableName + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, tableName);
}
conn.Close();
return ds;
}
private DataSet ExcelToDataSet(string path)
{
string[] s = new string[50];
string filename = path;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook;
Microsoft.Office.Interop.Excel.Worksheet worksheet;
object oMissing = System.Reflection.Missing.Value;//相当null
workbook = excel.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
System.Data.DataTable dt = new System.Data.DataTable();
//for (int k = 1; k <= workbook.Worksheets.Count; k++)
//{
//worksheet = (Worksheet)workbook.Worksheets[k];
worksheet = (Worksheet)workbook.Worksheets[1];
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
//s = new string[colCount];
Microsoft.Office.Interop.Excel.Range range1;
for (int i = 0; i < colCount; i++)
{
Microsoft.Office.Interop.Excel.Range range;
range = worksheet.Range[worksheet.Cells[2, i + 1], worksheet.Cells[2, i + 1]];
if (range.Value2 == null || range.Value2.ToString() == "")
continue;
dt.Columns.Add(range.Value2.ToString());
}
for (int j = 1; j <= rowCount; j++)//rowCount
{
try
{
DataRow dr = dt.NewRow();
Microsoft.Office.Interop.Excel.Range range;
range = worksheet.Range[worksheet.Cells[j, 1], worksheet.Cells[j, 1]];
int int1;
if (range.Value2 != null)
{
if (int.TryParse(range.Value2.ToString(), out int1))
{
for (int i = 0; i < colCount; i++)
{
//range1 = worksheet.get_Range(worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]);
range1 = worksheet.Range[worksheet.Cells[j, i + 1], worksheet.Cells[j, i + 1]];
//range1 = worksheet.get_Range(worksheet.Cells[j, i + 1] as Microsoft.Office.Interop.Excel.Range, worksheet.Cells[j, i + 1] as Microsoft.Office.Interop.Excel.Range);
if (range1.Value2 != null)
{
if (range1.NumberFormatLocal == "yyyy-m-d")
{
if (range1.Value2.ToString() == "")
{
dr[i] = "";
}
else
{
DateTime dtime1 = DateTime.FromOADate(double.Parse(range1.Value2.ToString()));
dr[i] = dtime1.ToString("yyyy-MM-dd");
//dr[i] = DateTime.Parse(range1.Value2.ToString());
}
}
else if (range1.NumberFormatLocal == "yyyy\"年\"m\"月\";@")
{
if (range1.Value2.ToString() == "")
{
dr[i] = "";
}
else
{
DateTime dtime2 = DateTime.FromOADate(double.Parse(range1.Value2.ToString()));
dr[i] = dtime2.ToString("yyyy年MM月");
//dr[i] = DateTime.FromOADate(double.Parse(range1.Value2.ToString()));
}
}
else
{
dr[i] = range1.Value2.ToString();
}
}
}
string buscode = dr[4].ToString();
dt.Rows.Add(dr);
}
}
}