Excel导入导出

前期准备：
1.程序必须是X86平台上的
2.添加excel的dll文件
3.添加office的配置程序

代码
//导入Excel
private void button1_Click(object sender, EventArgs e)
{
//打开一个文件选择框
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = “Excel文件”;
ofd.FileName = “”;
ofd.Filter = “Excel文件(.xlsx)|.xlsx”;
try
{
if (ofd.ShowDialog() == DialogResult.OK)
{
string tableName = “”;
string Path = ofd.FileName;
string strConn = “Provider=Microsoft.Ace.OLEDB.12.0;Data Source=” + Path + “;Extended Properties=‘Excel 12.0;HDR=YES;IMEX=1’”;
//string strConn = “Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + Path + “;” + “Extended Properties=Excel 8.0;”;
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
tableName = schemaTable.Rows[0][2].ToString().Trim();
string strExcel = “Select * From [” + tableName + "] ";
OleDbCommand cmd = new OleDbCommand(strExcel, conn);
System.Data.DataTable excelDt = new System.Data.DataTable();
OleDbDataAdapter da = new OleDbDataAdapter(strExcel, conn);
da.Fill(excelDt);
conn.Close();
if (excelDt != null)
{
this.dataGridView1.DataSource = null;
this.dataGridView1.DataSource = excelDt;
}
}
}
catch (Exception)
{
MessageBox.Show(“导入文件时出错,文件可能正被打开”, “提示”);
}

    }

//导出execl
private void button2_Click(object sender, EventArgs e)
{
if (this.dataGridView1.Rows.Count < 1)
{
MessageBox.Show(“没有要导出的客户信息！”, “提示信息”, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//StatesBar sb = new StatesBar();
try
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Title = “不凡帝订单系统导出----荣庆物流”;
saveFileDialog1.Filter = “Excel(.xlsx)|.xlsx”;
saveFileDialog1.FileName = string.Format(“客户基本信息–{0}”, DateTime.Now.ToString(“yyyyMMdd”));
DialogResult result = saveFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
//sb.Show(“系统正在处理中…请稍候！”, true);
Microsoft.Office.Interop.Excel._Application xlapp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlbook = xlapp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet xlsheet = (Microsoft.Office.Interop.Excel.Worksheet)xlbook.Worksheets[1];
int colIndex = 0;
int RowIndex = 1;
string headInfo = “客户名称,客户编码”;
//开始写入每列的标题
foreach (var s in headInfo.Split(’,’))
{
colIndex++;
xlsheet.Cells[RowIndex, colIndex] = s;
}

                //开始写入内容 
                int RowCount = this.dataGridView1.Rows.Count;//行数
                for (int i = 0; i < RowCount; i++)
                {
                    RowIndex++;
                    xlsheet.Cells[RowIndex, 1].NumberFormatLocal = "@";
                    xlsheet.Cells[RowIndex, 1] = dataGridView1.Rows[i].Cells["UserName"].Value.ToString();
                    xlsheet.Cells[RowIndex, 2].NumberFormatLocal = "@";
                    xlsheet.Cells[RowIndex, 2] = dataGridView1.Rows[i].Cells["UserCode"].Value.ToString();
                }
                xlbook.Saved = true;
                xlbook.SaveCopyAs(saveFileDialog1.FileName);
                xlapp.Quit();
                GC.Collect();

                #region 强行杀死最近打开的Excel进程
                System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                System.DateTime startTime = new DateTime();
                int m, killId = 0;
                for (m = 0; m < excelProc.Length; m++)
                {
                    if (startTime < excelProc[m].StartTime)
                    {
                        startTime = excelProc[m].StartTime;
                        killId = m;
                    }
                }
                if (excelProc[killId].HasExited == false)
                {
                    excelProc[killId].Kill();
                }
                #endregion

                //sb.Close();
                MessageBox.Show("导出成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        catch
        {
            //sb.Close();
            MessageBox.Show("导出失败！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }