Question

求c#读取excel中数据的方法

EXCEL中的格式是这样的：A B C D1 2 3 412 3 4 23要在C#程序中建立一个结构数组将以上信息读到结构数组中

Answer 1

在组件里引用office的excel的dll下面这个方法很强大,但是用起来也通过一个一个的读 private string _connectString = " Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source={0} "; /// <summary>
/// 读取Excel某个单元的数据
/// </summary>
/// <param name="sheetname"></param>
/// <param name="path"></param>
/// <param name="xcell"></param>
/// <param name="ycell"></param>
/// <returns></returns>
public string ReaderCellData(string sheetname, string path, int xcell, int ycell)
{
string val = "";
Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = false; Microsoft.Office.Interop.Excel.WorkbookClass workbook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(path, //Environment.CurrentDirectory+
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value); object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet datasheet = null;
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) //取出指定的sheet
{
if (sheet.Name == sheetname)
{
datasheet = sheet;
break;
}
} //取得某个单元格xcell, ycell
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)datasheet.Cells[xcell, ycell];
val = range.Value2.ToString(); app.Quit();
app = null; return val;
} /// <summary>
/// 根据列名和工作表Sheet名读取Excel
///
/// </summary>
/// <param name="fullPath">excel的绝对路径</param>
/// <param name="cols">读取excel的列</param>
/// <param name="sheetName">工作表名sheet</param>
/// <returns>DataTable</returns>
public DataTable ReadExcelDataByCol(string fullPath,string []cols,string sheetName)
{
//string xlsPath = Server.MapPath("~/app_data/somefile.xls"); // 绝对物理路径
//data source为文件的绝对路径
string connStr = string.Format(_connectString, fullPath);
string colStr = ""; //合成所查询的column
for (int i=0; i<cols.Length;i++)
{
if (i == cols.Length - 1)
{
colStr += cols[i];
}
else
{
colStr += string.Format(" {0},",cols[i]);
}
}
// 查询语句
string sql = string.Format("SELECT {0} FROM [{1}$]",colStr,sheetName); DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
da.Fill(ds); // 填充DataSet return ds.Tables[0];
}

Answer 2

这东西很简单，引用office的excel的dll然后打开这个excel文件，就跟读取一个二维表一样容易。具体代码就不写了 百度一搜一大堆。