private void btnOut_Click(object sender, EventArgs e)
{
if (listView1.Items.Count == 0)
{
return;
}
saveFileDialog1.Filter = "Excel File|*.xls";
saveFileDialog1.Title = "数据导出";
this.saveFileDialog1.FileName = FormArg.Name;
string path = string.Empty;
if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
{
path = saveFileDialog1.FileName;
ListViewExport.ExportToExcel(listView1, path, "1", true);
if (ListViewExport.IsExistsExcel())
{
if (DialogResult.Yes == MessageBox.Show("是否打开该文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1))
{
ListViewExport.OpenExcel(path);
}
}
}
}
/// <summary>
/// 将ListView中的数据导出到指定的Excel文件中
/// </summary>
/// <param name="listView">System.Windows.Forms.ListView,指定要导出的数据源</param>
/// <param name="destFileName">指定目标文件路径</param>
/// <param name="tableName">要导出到的表名称</param>
/// <param name="overWrite">指定是否覆盖已存在的表</param>
/// <returns>导出的记录的行数</returns>
public static int ExportToExcel(ListView listView, string destFileName, string tableName, bool overWrite)
{
//得到字段名
string szFields = "";
string szValues = "";
for (int i = 0; i < listView.Columns.Count; i++)
{
szFields += "[" + listView.Columns[i].Text + "],";
}
szFields = szFields.TrimEnd(',');
//定义数据连接
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = ListViewExport.GetConnectionString(destFileName);
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandType = CommandType.Text;
//打开数据库连接
try
{
connection.Open();
}
catch
{
throw new Exception("目标文件路径错误。");
}
//创建数据库表
try
{
command.CommandText = ListViewExport.GetCreateTableSql("[" + tableName + "]", szFields.Split(','));
command.ExecuteNonQuery();
}
catch (Exception ex)
{
//如果允许覆盖则删除已有数据
if (overWrite)
{
try
{
command.CommandText = "DROP TABLE [" + tableName + "]";
command.ExecuteNonQuery();
command.CommandText = ListViewExport.GetCreateTableSql("[" + tableName + "]", szFields.Split(','));
command.ExecuteNonQuery();
}
catch (Exception ex1)
{
throw ex1;
}
}
else
{
throw ex;
}
}
try
{
//循环处理数据------------------------------------------
int recordCount = 0;
for (int i = 0; i < listView.Items.Count; i++)
{
szValues = "";
for (int j = 0; j < listView.Columns.Count; j++)
{
if (j >= listView.Items[i].SubItems.Count)
{
szValues += "'',";
}
else
{
szValues += "'" + listView.Items[i].SubItems[j].Text + "',";
}
}
szValues = szValues.TrimEnd(',');
//组合成SQL语句并执行
string szSql = "INSERT INTO [" + tableName + "](" + szFields + ") VALUES(" + szValues + ")";
command.CommandText = szSql;
recordCount += command.ExecuteNonQuery();
}
connection.Close();
return(recordCount);
}
catch (Exception ex)
{
throw ex;
}
}