导入Excel
private void button1_Click(object sender, EventArgs e)
{
//this.openFileDialog1.ShowDialog();
OpenFileDialog openfile = new OpenFileDialog();
openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*";
textBox1.Text = openfile.FileName;
if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
ExcelToDS(openfile.FileName);
}
public void ExcelToDS(string path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
DataTable table1 = new DataTable();
myCommand.Fill(table1);
DataColumn result = new DataColumn();
result.DataType = System.Type.GetType("System.String");
result.ColumnName = "对比结果";
table1.Columns.Add(result);
dataGridView2.DataSource = table1;
}
**********************************************************************************************************
(二)导出到Excel
private void button2_Click(object sender, EventArgs e)
{
if (!ExportDataGridView(dataGridView1, true))
{
MessageBox.Show("表格中没有数据,无法导出数据!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public bool ExportDataGridView(DataGridView dgv, bool isShowExcel)
{
if (dgv.Rows.Count == 0)
return false;
//建立excel对象
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcel;
//生成字段名称
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
//填充数据
for (int i = 0; i < dgv.RowCount - 1; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
}
return true;
}