c# - 将 datagridview 数据导出到 Excel 工作表
问题描述
我正在做一个项目,我需要将 datagridview 数据导出到 Excel 表中。我的 datagridview 有 38 列和大约 120 行(单词之间有空格),数据是从 datagridview 输入的。当我运行应用程序时,我得到这个异常,“对象引用未设置为对象的实例。”。这是我的代码块:
私人无效exportbtn_Click(对象发送者,EventArgs e)
if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.AutoFit();
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
MessageBox.Show("Data Exported Successfully");
}
ExcelApp.ActiveWorkbook.SaveAs("");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Visible = true;
ExcelApp.Quit();
}
我将datagridview第一列(Facillity)填充为:
ArrayList row = new ArrayList();
row.Add("Techiman Holy Family Hospital"); dataGridView1.Rows.Add(row.ToArray());
row = new ArrayList();
row.Add("Opoku Agyeman Hospital"); dataGridView1.Rows.Add(row.ToArray());
row = new ArrayList();
row.Add("Kintampo Municipal Hospital"); dataGridView1.Rows.Add(row.ToArray());
此外,我不想在导出到 excel 的任何时候选择新的 excel 工作簿。我想将所有工作表保存在同一个工作簿中。有没有办法编辑以下代码来实现:
saveFileDialog1.InitialDirectory = ("C:");
saveFileDialog1.Title = "Save as Excel File";
saveFileDialog1.FileName = "";
saveFileDialog1.Filter = "Excel Files(2003)|*.xls| Excel Files(2007)|*.xlsx";
请问有什么帮助吗?谢谢。
解决方案
首先,我建议你可以参考下面的代码来给你的datagridview添加数据。
private void Form1_Load(object sender, EventArgs e)
{
DataTable table = new DataTable();
table.Columns.Add("Hospital");
table.Columns.Add("Name");
table.Columns.Add("Age");
table.Columns.Add("Address");
table.Columns.Add("Id");
table.Columns.Add("Sex");
table.Rows.Add("Techiman Holy Family Hospital", "test11", "22","home","1001","Man");
table.Rows.Add("Techiman Holy Family Hospital", "test2", "23", "home", "1001", "Man");
table.Rows.Add("Opoku Agyeman Hospital", "test8", "22", "home", "1001", "Man");
table.Rows.Add("Opoku Agyeman Hospital", "test6", "26", "home", "1001", "Man");
table.Rows.Add("Kintampo Municipal Hospital", "test21", "22", "home", "1001", "Man");
table.Rows.Add("Kintampo Municipal Hospita", "test12", "22", "home", "1001", "Man");
dataGridView1.DataSource = table;
}
其次,解决问题的关键是我们需要更改dataGridView1.Rows.Count
为,dataGridView1.Rows.Count-1
因为datagirdview会自动生成一个空行。
最后,这是一个您可以参考的代码示例。
private void button1_Click(object sender, EventArgs e)
{
saveFileDialog1.InitialDirectory = ("E:");
saveFileDialog1.Title = "Save as Excel File";
saveFileDialog1.FileName = "";
saveFileDialog1.Filter = "Excel Files(2003)|*.xls| Excel Files(2007)|*.xlsx";
if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
{
Excel.Application ExcelApp = new Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.AutoFit();
Excel.Worksheet sheet = ExcelApp.ActiveSheet;
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
sheet.Cells[1, i+1] = dataGridView1.Columns[i].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count-1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
sheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
MessageBox.Show("Data Exported Successfully");
sheet.SaveAs(saveFileDialog1.FileName);
ExcelApp.Visible = true;
ExcelApp.Quit();
}
}
推荐阅读
- r - 为什么我会收到警告消息“在 plot.xy(xy, type, ...) 中:强制引入的 NAs”?
- postgresql - 多对多关系导致 FOREIGN KEY VIOLATION 变为可连接
- java - chrome启动时如何解决此错误
- r - 使用文本标签在 R 中创建一维图
- flutter - 运行 MyApp 之前的 Flutter 加载设置
- javascript - 雪花字符串替换javascript udf
- laravel - 如何在 Laravel (OctoberCMS) 查询中设置值增量?
- python - 如何从基于日期时间列的值增加的熊猫数据框中过滤行?
- python - 如何在 django 中发布带有空字段的数据?
- python - 划分为pandas sql