这个是导出数据的类。
特别说明:导出时需要有一个只有一行标题列名行的excel文件。
public class CExcelWrite
{
private OleDbConnection m_oleconn;
private OleDbCommand m_olecomm;
/// <summary>
/// 准备开始写excel文件
/// </summary>
/// <param name="colcount">被写Excel文件列数</param>
/// <param name="modfilename">模板文件路径</param>
/// <param name="destfilename">被写文件路径</param>
public void BeginWrite(int colcount,string modfilename,string destfilename)
{
if (System.IO.File.Exists(destfilename))
System.IO.File.Delete(destfilename);
System.IO.File.Copy(modfilename, destfilename);
string connstr = string.Format(
"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=no';Data Source={0}",
destfilename);
string commstr = "";
string commstr1 = "";
m_oleconn = new OleDbConnection(connstr);
m_oleconn.Open();
m_olecomm=new OleDbCommand();
m_olecomm.Connection = m_oleconn;
commstr = "insert into [Sheet1$](";
commstr1 = "values(";
for (int i = 1; i <= colcount; i++)
{
commstr += "F" + i.ToString();
commstr1 += "?";
if (i < colcount)
{
commstr += ",";
commstr1 += ",";
}
}
commstr += ")";
commstr1 += ")";
m_olecomm.CommandText = commstr + commstr1;
for (int i = 1; i <= colcount; i++)
m_olecomm.Parameters.Add("@Col" + i.ToString(), OleDbType.VarChar, 250);
}
/// <summary>
/// 写入一行数据
/// </summary>
/// <param name="outdata"></param>
public bool WriteLine(string[] outdata)
{
if (m_oleconn!=null && outdata != null && outdata.Length > 0)
{
for (int i = 0; i < outdata.Length; i++)
{
m_olecomm.Parameters[i].Value = outdata[i];
}
m_olecomm.ExecuteNonQuery();
return true;
}
return false;
}
/// <summary>
/// 结束写入数据
/// </summary>
public void EndWrite()
{
if (m_oleconn != null)
{
m_olecomm.Dispose();
m_oleconn.Close();
m_oleconn = null;
m_olecomm = null;
}
}
~CExcelWrite()
{
EndWrite();
}
}
使用方法
private void btnoutexcel_Click(object sender, EventArgs e)
{
if (sFDExcel.ShowDialog() == DialogResult.OK)
{
if (dGVmszh.Rows.Count > 0)
{
string[] coldata = new string[7];
CExcelWrite cew = new CExcelWrite();
//初始化
cew.BeginWrite(7, Application.StartupPath + "//ModelFile.xls", sFDExcel.FileName);
foreach (DataGridViewRow dgvr in dGVmszh.Rows)
{
int j = 0;
for (int i = 1; i <= 3; i++,j++)
coldata[j] = dgvr.Cells[i].Value.ToString();
for (int i = 5; i <= 8; i++,j++)
coldata[j] = dgvr.Cells[i].Value.ToString();
//写入一行
cew.WriteLine(coldata);
}
//结束写入
cew.EndWrite();
}
}
}