导入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.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 = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells = dgv[j, i].Value.ToString();
}
}
}
return true;
}
更加复杂的:
首先要引入一个.dll文件,此文件从office中获得,如果安装了.net2005,那么请重新再安装office2003,在安装过程中,选择“选择应用程序的高级自定义”,对“编程”项打钩,安装完成后即可获得此dll文件,在com中将此dll文件引入,文件名为“微软exccel 11.0......”
引入此dll后,导入这些包:
using System.Reflection;//用于missing.value
using System.IO;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
先放上最基础的从datagridview导excel的代码
private void button1_Click(object sender, EventArgs e)
{
#region 验证可操作性
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel) return;
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{ return; }
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在,如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Microsoft.Office.Interop.Excel.Application objExcel = null;
Microsoft.Office.Interop.Excel.Workbook objWorkbook = null;
Microsoft.Office.Interop.Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i++)
{
if (dgv.Columns.Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = dgv.Columns.HeaderText.Trim();
displayColumnsCount++;
}
}
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row < dgv.RowCount; row++)
{
//tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)//防止dgv中有单元格没填数据而抛错
{
try
{
//row+2表示从excel的第二行开始填入datagridview中的数据
objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
}
catch (Exception)
{
}
}
//往后递进一格
displayColumnsCount++;
}
}
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
转载于:https://www.cnblogs.com/freedom831215/archive/2009/10/03/1577625.html