使用Excel文件模版导入导出数据

使用Excel文件模版导入导出数据

使用原始的ASP.NET OleDb驱动程序，这使得ASP.NET应用程序即使部署在未安装Excel的服务器上也同样能支持导入和导出校准的Excel格式文件。新建一个“Microsoft Excel 工作表”，在工作表的第一行设置好需要的字段，然后选中整个表，点右键选择“设置单元格格式”，在“数字”标签的“分类”列表选择“文本”，点击“确定” 并保存文件。将单元格格式设置为“文本”主要是为了防止日后导入数据时某些数据格式无法被识别。

有了这个模板，我们就可以开始写代码导出数据了，名为“demo.xls”，包含三个列：“ID”、“姓名”和“生日”。

 

导出数据（标准的Excel文件）：

// 根据模板文件创建副本

string filePath = Server.MapPath("~/" + Guid.NewGuid().ToString() + ".xls");

File.Copy(Server.MapPath("~/demo.xls"), filePath);

// 使用OleDb驱动程序连接到副本

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");

using (conn)

{

　　conn.Open();

　　// 增加记录

　　OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$]([ID], [姓名], [生日]) VALUES(@Id, @Name, @Birthday)", conn);

　　cmd.Parameters.AddWithValue("@Id", "1");

　　cmd.Parameters.AddWithValue("@Name", "Hsu Yencheng");

　　cmd.Parameters.AddWithValue("@Birthday", "1981-10-13");

　　cmd.ExecuteNonQuery();

}

// 输出副本的二进制字节流

Response.ContentType = "application/ms-excel";

Response.AppendHeader("Content-Disposition", "attachment;filename=info.xls");

Response.BinaryWrite(File.ReadAllBytes(filePath));

// 删除副本

File.Delete(filePath);

 

导出数据：

  string filePath = Server.MapPath("~/info.xls");

  OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",

            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");

  DataTable dt = new DataTable();

  da.Fill(dt);

        public DataTable  GetDataFromExcel(string filepath)
        {
            try
            {
                string ext = Path.GetExtension(filepath).ToLower();
                string strConn;
                if (ext== ".xls")
                {
                     strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
                }
                else if (ext== ".xlsx")
                {
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
                }
                else
                {
                    throw new Exception("filepath输入错误！");
                }

                OleDbDataAdapter da = new OleDbDataAdapter("SELECT 工号,姓名,考核结果 FROM [Sheet1$]", strConn);
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
            catch 
            {
                throw;
            }
        }

 

如果您要导入或导出的是Excel 2007文件（*.xlsx），那么您需要将连接字符串改为（可参考：http://www.connectionstrings.com/）：
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=info.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

 

上面的方法是可行,但有个问题:执行效率很低,超过255行的数据导出时,特别的慢(在网上查得是MS的限制).解决方法是: 将INSERT部分不使用参数模式,而直接使用串拼接成INSERT的SQL语句,执行起来速度很快,导出时也很快,超过255时也很快,我仅测试过用 SQL串语句生成并导出2000条,文件大小有3M,速度是不慢的,完成可以接受.而使用原方法,在256条时就感觉到慢了.

另外:OLEDB模式下可以先用CREATE TABLE生成SHEET1$这种EXCEL工作表,然后再INSERT数据,证实是可行的.这样模板文件就只要一个空白的EXCEL文档了.灵活性高很多.