面代码演示了如何将ListView中的数据导出到Excel的方法,例子代码中还包括了一些编程中的其它小方法,比如:
1)文件的拷贝复制方法
2)文件属性的修改
3)ListView控件Columns的遍历
4)ListView控件Items的遍历
5)foreach语句的使用
6)SaveFileDialog的使用
public
void
DealExcelOut()
... {
String path = Application.StartupPath;
String path1 = path + "/tmp.xls";
String path2 = path + "/tmp2.xls";
File.Copy(path1, path2, true);
File.SetAttributes(path2, FileAttributes.Normal);
String strconn = "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" + path2 + "; Extended Properties = Excel 8.0";
OleDbConnection cn = new OleDbConnection(strconn);
OleDbCommand cmd = new OleDbCommand();
String cmdstr;
//根据ListView创建VPN表
try
...{
cn.Open();
cmd.Connection = cn;
cmdstr = "Create Table VPN (";
foreach (ColumnHeader ch in listView_Main.Columns)
...{
cmdstr += ch.Text + " TEXT,";
}
cmdstr = cmdstr.Remove(cmdstr.Length - 1);
cmdstr += ")";
cmd.CommandText = cmdstr;
cmd.ExecuteNonQuery();
}
catch (Exception)
...{
MessageBox.Show("读取Excel模板文件错误!");
}
//导出数据
try
...{
long cols = listView_Main.Columns.Count;
foreach (ListViewItem lvi in listView_Main.Items)
...{
cmdstr = "Insert Into VPN Values (";
for (long i = 0; i < cols; i++)
cmdstr += "'" + lvi.SubItems[(Int32)i].Text + "',";
cmdstr = cmdstr.Remove(cmdstr.Length - 1);
cmdstr += ")";
cmd.CommandText = cmdstr;
cmd.ExecuteNonQuery();
}
cn.Close();
//显示FileSave对话框,复制临时文件到指定文件
DialogResult r = saveFileDialog_Excel.ShowDialog();
if (r == DialogResult.OK)
...{
File.Copy(path2, saveFileDialog_Excel.FileName, true);
}
File.Delete(path2);
}
catch (Exception)
...{
MessageBox.Show("访问Excel文件错误!");
}
}
... {
String path = Application.StartupPath;
String path1 = path + "/tmp.xls";
String path2 = path + "/tmp2.xls";
File.Copy(path1, path2, true);
File.SetAttributes(path2, FileAttributes.Normal);
String strconn = "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" + path2 + "; Extended Properties = Excel 8.0";
OleDbConnection cn = new OleDbConnection(strconn);
OleDbCommand cmd = new OleDbCommand();
String cmdstr;
//根据ListView创建VPN表
try
...{
cn.Open();
cmd.Connection = cn;
cmdstr = "Create Table VPN (";
foreach (ColumnHeader ch in listView_Main.Columns)
...{
cmdstr += ch.Text + " TEXT,";
}
cmdstr = cmdstr.Remove(cmdstr.Length - 1);
cmdstr += ")";
cmd.CommandText = cmdstr;
cmd.ExecuteNonQuery();
}
catch (Exception)
...{
MessageBox.Show("读取Excel模板文件错误!");
}
//导出数据
try
...{
long cols = listView_Main.Columns.Count;
foreach (ListViewItem lvi in listView_Main.Items)
...{
cmdstr = "Insert Into VPN Values (";
for (long i = 0; i < cols; i++)
cmdstr += "'" + lvi.SubItems[(Int32)i].Text + "',";
cmdstr = cmdstr.Remove(cmdstr.Length - 1);
cmdstr += ")";
cmd.CommandText = cmdstr;
cmd.ExecuteNonQuery();
}
cn.Close();
//显示FileSave对话框,复制临时文件到指定文件
DialogResult r = saveFileDialog_Excel.ShowDialog();
if (r == DialogResult.OK)
...{
File.Copy(path2, saveFileDialog_Excel.FileName, true);
}
File.Delete(path2);
}
catch (Exception)
...{
MessageBox.Show("访问Excel文件错误!");
}
}