C# 中读取excel文件

来源：互联网发布：南风知我意2 全文阅读编辑：程序博客网时间：2024/04/28 20:55

方法一：

if (System.IO.Path.GetExtension(FilePath).ToUpper() == ".XLS" || System.IO.Path.GetExtension(FilePath).ToUpper() == ".XLSX")//03版excel 或者07版excel

{

using(FileStream file = new FileStream(FilePath,FileMode.Open,FileAccess.Read))

{

try

{

//根据路径通过已存在的excel来创建HSSFWorkbook

HSSFWorkbook workbook = new HSSFWorkbook(file); //file为文件的全路径+文件名

//获取excel的第一个sheet

ISheet sheet = workbook.GetSheet("Sheet1");

DataTable table = new DataTable();

//获取sheet的首行

IRow headerRow = sheet.GetRow(0);

//一行最后一个方格的编号即总的列数

int cellCount = headerRow.LastCellNum;

//编制表头

table.Columns.Add("StuNo");

table.Columns.Add("StuName");

table.Columns.Add("StuSex");

table.Columns.Add("StuAge");

//获取总的行数

int rowCount = sheet.LastRowNum;

for(int i = (sheet.FirstRowNum+1);i<sheet.LastRowNum;i++)

{

IRow row = sheet.GetRow(i);

DataRow dataRow = table.NewRow();

//循环所有列

for(int j=row.FirstCellNum;j<cellCount;j++)

{

dataRow[0] = row.GetCell(j).ToString();

}

table.Rows.Add(dataRow);

}

//将table中的数据放入数据库中

string connStr = Utils.GetConStr();

SqlConnection conn = new SqlConnection(connStr);

SqlDataAdapter da = new SqlDataAdapter("select StuNo,StuName,StuSex,StuAge from StudentInfos");

SqlCommandBuilder cmb = new SqlCommandBuilder(da);

da.Update(table);

}

catch(Exception e)

{

MessageBox.Show(e.Message);

}

方法二：

string conStr = “"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AFileName + ";Extended Properties=Excel 8.0";

OleDbConnection myConn = new OleDbConnection(conStr);

string strCom = "SELECT * FROM [sheet1$]";

myConn.Open();

OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);

DataSet myDataSet = new DataSet();

myCommand.Fill(myDataSet, "[sheet1$]");

myConn.Close();

DataTable dt = myDataSet.Tables[0];