c#操作excel全攻略(导入导出) excel表增删改以及常见问题的处理

最新推荐文章于 2022-04-22 11:42:39 发布

纯净水o

最新推荐文章于 2022-04-22 11:42:39 发布

阅读量1.4k

点赞数 1

文章标签： excel

本文链接：https://blog.csdn.net/xuminghui382/article/details/46386193

版权

操作平台:

win7 64位 + office2007(64)位 + vs2010 + sql server 2008

操作时产生错误的处理方案

操作错误一导入excel错误：外部表不是预期的格式解决方案

解决方法：

环境：win7+iis7+Office2007

在asp.net网站中导出Excel文件后，再把文件导入到数据库中。读取Excel文件时，打开连接出错。

错误为：外部表不是预期的格式

解决：检查了一下，导出的Excel是标准文件不是html，没错，Excel文件正常。

调试代码，创建连接对象oleDbConnection也正常，但在conn.Open()打开链接时出错。

仔细看了下链接字符串，检查出了错误，Excel版本问题，Exce连接字符串版本是office2003的，更改为Excel2007版本则正常导入。

总结规则如下：

using System.Data.OleDb;
using System.Data;

 publicvoid ReadExcelFiless()
        {
            //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Server.MapPath("ExcelFiles/MyExcelFile.xls") + ";Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("ExcelFiles/Mydata2007.xlsx") + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
//备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
//      "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            adp.Fill(ds, "Book1");
            this.GridView1.DataSource = ds.Tables["Book1"].DefaultView;
            this.GridView1.DataBind(); 
        }

操作错误2

：未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序解决办法

通常的解决方法重新安装office2007

在论坛中提到过此问题造成这种错误通常发生在sv2010 安装在office2007之后所以在安装完sv2010之后在重新安装office2007即可解决这种类似的问题

datagridview空间导入与导出到excel

一下是实现读取excel的最简模式

using System.Data.OleDb;

private void button1_Click(object sender, EventArgs e)

{

try

{

string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=e:\\Book1.xlsx;Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)

//备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。

// "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。

OleDbConnection conn = new OleDbConnection(strConn);

OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]", conn);

DataSet ds = new DataSet();

adp.Fill(ds, "Book1");

dataGridView1.DataSource = ds.Tables["Book1"];

}

catch (Exception err)

{

MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",

MessageBoxButtons.OK, MessageBoxIcon.Information);

}

也就是按下一个按键可以将 e:\\Book1.xlsx; 这个excel文件导入到软件的dataGridView1表中显示出来

将datagridview中的数据导出到excel中另外 [Sheet1$] 代表那个表格 123

也可写成[123$]必须加'$'

从datagridview导入到excel中

第一种方案将数据导入到excel中之后会运行excel然后将excel自定义保存路径

private void button4_Click(object sender, EventArgs e)

{

if (dataGridView1.Rows.Count == 0)

return;

//建立Excel对象

bool isShowExcle = true ;

Excel.Application excel = new Excel.Application();

excel.Application.Workbooks.Add(true);

excel.Visible = isShowExcle;//导入到excel之后会显示以导出的excel表格如果为false则不显示

//生成字段名称

for (int i = 0; i < dataGridView1.ColumnCount; i++)

{

excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;

}

//填充数据

for (int i = 0; i < dataGridView1.RowCount - 1; i++)

{

for (int j = 0; j < dataGridView1.ColumnCount; j++)

{

if (dataGridView1[j, i].ValueType == typeof(string))

{

excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();

}

else

{

excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();

}

第二种方案

#region DateGridView导出到csv格式的Excel
/// <summary>
/// 常用方法，列之间加\t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。
/// </summary>
/// <remarks>
/// using System.IO;
/// </remarks>
/// <param name="dgv"></param>
private void DataGridViewToExcel(DataGridView dgv)
{
    SaveFileDialog dlg = new SaveFileDialog();
    dlg.Filter = "Execl files (*.xls)|*.xls";
    dlg.FilterIndex = 0;
    dlg.RestoreDirectory = true;
    dlg.CreatePrompt = true;
    dlg.Title = "保存为Excel文件";

    if (dlg.ShowDialog() == DialogResult.OK)
    {
        Stream myStream;
        myStream = dlg.OpenFile();
        StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
        string columnTitle = "";
        try
        {
            //写入列标题
            for (int i = 0; i < dgv.ColumnCount; i++)
            {
                if (i > 0)
                {
                    columnTitle += "\t";
                }
                columnTitle += dgv.Columns[i].HeaderText;
            }
            sw.WriteLine(columnTitle);

            //写入列内容
            for (int j = 0; j < dgv.Rows.Count - 1; j++)
            {
                string columnValue = "";
                for (int k = 0; k < dgv.Columns.Count; k++)
                {
                    if (k > 0)
                    {
                        columnValue += "\t";
                    }
                    if (dgv.Rows[j].Cells[k].Value == null)
                        columnValue += "";
                    else
                        columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
                }
                sw.WriteLine(columnValue);
            }
            sw.Close();
            myStream.Close();
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
        }
        finally
        {
            sw.Close();
            myStream.Close();
        }
    }
}
#endregion

这种方式的好处是单纯的文本操作在操作过程中不会涉及到excel相关和API函数所以也不会产生诸如错误1和错误2的相关问题

操作方便稳定安全

说明这种导入数据的办法只针对于xls后缀的数据文件如果是xlsx后缀的文件的则会显示数据错误无法打开

另外在打开xls时会显示

解决方法

修改注册表解决：

1、打开注册表编辑器
方法：开始 -> 运行 -> 输入regedit -> 确定
2、找到注册表子项
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
3、在右侧空白处点击鼠标右键，选择“新建 -> DWORD值(D)”，输入“ExtensionHardening”点击确定。
4、用鼠标右键点击ExtensionHardening，然后单击“修改（M）”，在数值数据中填写“0”即可确定。
5、关闭注册表编辑器，再次打开xls文件看看是不是提示是不是不见了？
小知识：ExtensionHardening设置的值的数据设置：
    * 0：不检查文件扩展名和文件类型并绕过该函数的警告消息。
    * 1：检查文件扩展名和文件类型。如果它们不匹配会显示警告消息。
    * 2：检查文件扩展名和文件类型。如果它们不匹配不要打开该文件。

c# 利用sql语句对excel进行操作

利用sql语句导入到datagridview中

private void button1_Click(object sender, EventArgs e)

{

string strCon = "Provider=Microsoft.Ace.OleDb.12.0;data source=e:\\123.xls;Extended Properties='Excel 12.0; HDR=Yes; IMEX=2'";

DataSet ds;

OleDbConnection con = new OleDbConnection(strCon);

string sql = "select * from[123$]";

con.Open();

OleDbDataAdapter adptr = new OleDbDataAdapter(sql, con);

ds = new DataSet();

adptr.Fill(ds, "[123$]");//update方法可以更新数据库

con.Close();

dataGridView1.DataMember = "[123$]";

dataGridView1.DataSource = ds;//不用绑定Bind

}

e:\\123.xls是我文件的地址

123是我的表的名字

利用sql语句增加一条数据

先贴代码

private void button1_Click(object sender, EventArgs e)

{

try

{

string strCon = "Provider=Microsoft.Ace.OleDb.12.0;data source=e:\\123.xls;Extended Properties='Excel 12.0; HDR=Yes; IMEX=2'";

DataSet ds;

OleDbConnection con = new OleDbConnection(strCon);

string sql = "insert into [123$](序号) values(1000)";

con.Open();

OleDbCommand cmd = new OleDbCommand(sql, con);

cmd.ExecuteNonQuery();

con.Close();

}

catch(Exception ex)

{

MessageBox.Show(ex.ToString());

}

运行之后会出现如下错误问题

解决方法

参数HDR的值：

HDR=Yes，这代表第一行是标题，不做为数据使用，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
参数Excel 8.0 对于Excel 97以上到2003版本都用Excel 8.0，2007或2010的都用Extended Properties=Excel 12.0

IMEX ( IMport EXport mode )设置
　　IMEX 有三种模式：
　　0 is Export mode
　　1 is Import mode
　　2 is Linked mode (full update capabilities)
　　我这里特别要说明的就是 IMEX 参数了，因为不同的模式代表著不同的读写行为：
　　当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
　　当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
　　当 IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
意义如下:
0 ---输出模式;
1---输入模式;
2----链接模式(完全更新能力)

按照以上描述，上面的连接字符串应该是可以读取，插件记录的

但是事实并非如此，当执行Insert Into语句时却出现异常：“操作必须使用一个可更新的查询”！

注意是c# WinForm程序，不是Web应用程序；如果是Web应用程序，那需要添加IIS_IUSRS或IIS_Service用户的目录访问权限；

还是去搜索看看别人是怎么解决的吧，但是看遍了别人解决问题的方法，到我这里就是测试不通过！

猜测还是IMEX值的问题，改为1不行，那就改为0

接着又测试将IMEX设置为4或10，结果都没问题，唯独1和2不行。

之后改为

HDR=Yes; IMEX=0'";

就可以执行操作了

利用sql修改excel一条语句

private void button1_Click(object sender, EventArgs e)

{

try

{

string strCon = "Provider=Microsoft.Ace.OleDb.12.0;data source=e:\\123.xls;Extended Properties='Excel 12.0; HDR=Yes; IMEX=0'";

DataSet ds;

OleDbConnection con = new OleDbConnection(strCon);

string sql = "update [123$] set 序号 = 20000 where 序号 = 2000";

con.Open();

OleDbCommand cmd = new OleDbCommand(sql, con);

cmd.ExecuteNonQuery();

con.Close();

}

catch(Exception ex)

{

MessageBox.Show(ex.ToString());

}

会增加语句之后修改一条语句就简单的多了修改一下相应的语句就好了

纯净水o

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
c#操作excel全攻略(导入导出) excel表增删改以及常见问题的处理

操作平台: win7 64位 +office2007(64)位 + vs2010 + sql server 2008操作时产生错误的处理方案操作错误一导入excel错误：外部表不是预期的格式解决方案解决方法：环境：win7+iis7+Office2007 在asp.net网站中导出Excel文件后，再把文件导入到数据库中
复制链接

扫一扫