ASP.NET读写Excel文件

时间：2011-03-10 来源：孤独者

1.读取Excel文件的数据连接字符串。读取.xls格式文件的Excel文件，可设置连接字符串为：Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcel.xls;Extended Properties=Excel 8.0;。如果要读取.xlsx和.xls格式文件的Excel文件，则需要将连接字符设置为："Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyExcel.xls;Extended Properties=Excel 12.0。

2.读取Excel表格Sheet的名称。

public static string[] GetExcelSheetNames(string filePath)
{
    string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
    OleDbConnection con = new OleDbConnection(conString);
    con.Open();
    DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    con.Close();
    if (dt == null)
    {
        return null;
    }
    string[] excelSheetNames = new string[dt.Rows.Count];
    int i = 0;
    foreach (DataRow dr in dt.Rows)
    {
        excelSheetNames[i++] = dr["TABLE_NAME"].ToString();
    }
    return excelSheetNames;
}

3.读取Excel文件。

public static DataTable ReadExcel(string filePath, string conStr)
{
    string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
    OleDbConnection con = new OleDbConnection(conString);
    OleDbDataAdapter oda = new OleDbDataAdapter(conStr, con);
    DataTable dt = new DataTable();
    con.Open();
    oda.Fill(dt);
    con.Close();
    return dt;
}

4.将数据写入Excel文件。

public static void WriteExcel(string filePath, DataTable dt)
{
    if (File.Exists(filePath))
    {
        File.Delete(filePath);
    }
    else
    {
        string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
        OleDbConnection con = new OleDbConnection(conString);
        string createSql = "create table Sheet1 (";
        foreach (DataColumn dc in dt.Columns)
        {
            createSql += dc.ColumnName + " varchar,";
        }
        createSql = createSql.Substring(0, createSql.Length - 1) + ")";

        OleDbCommand cmd = new OleDbCommand(createSql, con);
        con.Open();
        cmd.ExecuteNonQuery();
        foreach (DataRow dr in dt.Rows)
        {
            string insertSql = "insert into Sheet1 values(";
            foreach (DataColumn dc in dt.Columns)
            {
                insertSql += "'" + dr[dc].ToString() + "',";
            }
            insertSql = insertSql.Substring(0, insertSql.Length - 1) + ")";
            cmd = new OleDbCommand(insertSql, con);
            cmd.ExecuteNonQuery();
        }

        con.Close();
    }
}