C# Excel导入导出以及其他相关操作

详情请查看：http://www.cnblogs.com/lingyun_k/archive/2005/07/12/191740.aspx

这里我只描述我学到的一部分知识

主要包括3个代码快：

1.读取excel文件中的数据

2.将DataGridView控件中的数据导出到Excel，其他数据列表控件的操作本质一样

3.打开指定Excel文件并将数据导入到指定Excel文件中保存

1.  读取Excel  
          private DataSet GetExcel(string Path)
        {
            if (arratlist != null)
            {
                arratlist.Clear();
            }
            OleDbConnection objConn = null;
            DataSet data = new DataSet();
            try
            {
                string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Path + ";Extended Properties='Excel 8.0'";
                //;HDR=Yes;IMEX=1'";
                objConn = new OleDbConnection(strConn);
                objConn.Open();
                System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                tableName = schemaTable.Rows[0][2].ToString().Trim();
                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    arratlist.Add(schemaTable.Rows[i][2].ToString().TrimStart('/'').Trim('/'', '$'));//读取品牌列表
                }
                arratlist.Sort();
                string strSql = "Select   *   From   [" + tableName + "]";

                OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                OleDbDataAdapter sqlada = new OleDbDataAdapter();
                sqlada.SelectCommand = objCmd;
                sqlada.Fill(data);
                objConn.Close();
            }
            catch (Exception ex)
            {
                objConn.Close();
                MessageBox.Show(ex.Message);
            }
            return data;
        }

 2.将dataGridView中的数据导出到Excel
        private void button1_Click(object sender, EventArgs e)
        {
            Excel.ApplicationClass excel = new Excel.ApplicationClass();
            Excel.Workbooks workbooks = excel.Workbooks;
            Excel._Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Sheets sheets = workbook.Worksheets;
            Excel._Worksheet worksheet = (Excel._Worksheet)sheets.get_Item(1);
          
           

            int rowIndex = 1;
            int colIndex = 1;

            System.Data.DataTable dt = sysdt;

            int row_cnt = dataGridResult.Rows.Count;
            int col_cnt = dataGridResult.Columns.Count;

            //   适用于用DataGridTableStyle自定义DataGrid时。    
            for (int j = 0; j < col_cnt; j++)
            {
                excel.Cells[rowIndex, j + 1] = dataGridResult.Columns[j].HeaderText;
            }

            rowIndex++;
            //同样方法处理数据    
            for (int row = 0; row < row_cnt; row++)
            {
                colIndex = 1;
                for (int col = 0; col < col_cnt; col++)
                {
                    excel.Cells[rowIndex, colIndex] = dataGridResult[col, row].Value;
                    colIndex++;
                }
                rowIndex++;
            }

            //设置单元格数字内容显示格式    

            worksheet.Name = tableName.TrimEnd('$');
            Excel.Range range;

            range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[row_cnt + 1, 1]);
            range.NumberFormat = "0000";   //这里是因为需要把0012等在Excel上正确显示，默认会变成12,按需要可以忽略.  
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;     //第一列居中  

            //range = worksheet.get_Range(worksheet.Cells[0, 0], worksheet.Cells[row_cnt + 1, col_cnt]);

            //   内框及外框    
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);

            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;

            excel.Columns.AutoFit();

            excel.Visible = true;
        }

3.保存文件并退出excel

 private void saveExcel(object sender, EventArgs e)
        {
            //输出Excel文件并退出
            try
            {
                Excel.Application app = new Excel.ApplicationClass();
                app.Visible = true;
                templetFile = filePath;//需要打开的excel文件的路径，套用代码请自行修改
                outputFile = "F://12345.xls";//保存文件路径
                Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing, missing, missing, missing, missing,
                                   missing, missing, missing, missing, missing, missing, missing,missing,missing);

                //得到WorkSheet对象
                Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

                workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing);
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook = null;
                app = null;

                GC.Collect();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }