Excel 导入 导出 Microsoft

 

 

导出：

        private void exportExcel()
        {
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                Application.DoEvents();
                Application.DoEvents();
                this.gridControl1.ExportToXlsx(saveFileDialog1.FileName);
                Application.DoEvents();
                Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
                appExcel.DisplayAlerts = false;
                //DisplayAlerts 属性设置成 False，就不会出现这种警告。                
                Microsoft.Office.Interop.Excel.Workbook workbook = appExcel.Workbooks.Open(saveFileDialog1.FileName,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);//打开Excel             

                Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;//实例表格  
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[1];//第一个表格
                worksheet.Columns["A", Type.Missing].ColumnWidth = 14;
                worksheet.Columns["B", Type.Missing].ColumnWidth = 5;
                worksheet.Columns["C", Type.Missing].ColumnWidth = 12;
                worksheet.Columns["D", Type.Missing].ColumnWidth = 10;
                worksheet.Columns["E", Type.Missing].ColumnWidth = 12;
                worksheet.Columns["F", Type.Missing].ColumnWidth = 16;
                worksheet.Columns["G", Type.Missing].ColumnWidth = 18;
                worksheet.Columns["H", Type.Missing].ColumnWidth = 7;
                worksheet.Columns["I", Type.Missing].ColumnWidth = 7;
                worksheet.Columns["J", Type.Missing].ColumnWidth = 7;
                worksheet.Columns["K", Type.Missing].ColumnWidth = 7;
                worksheet.Columns["L", Type.Missing].ColumnWidth = 10;
                worksheet.Columns["M", Type.Missing].ColumnWidth = 7;

                worksheet.Columns["N", Type.Missing].ColumnWidth = 7;
                worksheet.Columns["O", Type.Missing].ColumnWidth = 5;
                worksheet.Columns["P", Type.Missing].ColumnWidth = 5;
                worksheet.Columns["Q", Type.Missing].ColumnWidth = 12;
                worksheet.Columns["R", Type.Missing].ColumnWidth = 12;
                worksheet.Columns["S", Type.Missing].ColumnWidth = 5;
                worksheet.Columns["T", Type.Missing].ColumnWidth = 5;
                worksheet.Columns["U", Type.Missing].ColumnWidth = 5;
                worksheet.Columns["V", Type.Missing].ColumnWidth = 5;
                worksheet.Columns["W", Type.Missing].ColumnWidth = 10;
                worksheet.Columns["X", Type.Missing].ColumnWidth = 10;
                worksheet.Columns["Y", Type.Missing].ColumnWidth = 10;
                worksheet.Columns["Z", Type.Missing].ColumnWidth = 8;
                worksheet.Columns["AA", Type.Missing].ColumnWidth = 10;
                worksheet.Columns["AB", Type.Missing].ColumnWidth = 8;
                worksheet.Columns["AC", Type.Missing].ColumnWidth = 10;
                worksheet.Columns["AD", Type.Missing].ColumnWidth = 8;
                worksheet.Columns["AE", Type.Missing].ColumnWidth = 8;
                worksheet.Columns["AF", Type.Missing].ColumnWidth = 12;
                worksheet.Columns["AG", Type.Missing].ColumnWidth = 15;
           

                ///   worksheet.Cells[9,"A"].value ="asd";
                //  Microsoft.Office.Interop.Excel.Range firstColumn = worksheet.get_Range("A1");
                //Range firstColumn = (Range)xlWorkSheet.Columns[0];
                // firstColumn.EntireColumn.AutoFit();

                workbook.Save();
                workbook.Close();
                appExcel.Quit();
                appExcel = null;
                GC.Collect();






            }
        }

 

2014-10-1814:07:48

 

导入：

    
        private void Btn_Open_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "Excel文件|*.xlsx;*.xls";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                
                    this.TXT_FileName.Text = dlg.FileName;
                    Excel.Application xlApp = new Excel.Application();
                    try
                    {
                    Excel.Workbook workbook = xlApp.Workbooks.Open(dlg.FileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
                    int n = workbook.Worksheets.Count;
                    CB_Sheet.Items.Clear();
                    for (int i = 0; i < n; i++)
                    {
                        CB_Sheet.Items.Add(((Excel.Worksheet)workbook.Worksheets[i + 1]).Name);
                    }
                   
                    preqexcel(dlg.FileName);
                }
                finally
                {
                    xlApp.Workbooks.Close();
                    xlApp.Quit();
                    xlApp = null;
                    GC.Collect();
                    this.overpricerowindex.Clear();
                }
            }
        }
    private void preqexcel(string path)
        {   
            string strConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                path + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
            }
            catch (Exception error)
            {
                MessageBox.Show("Microsoft.ACE.OLEDB.12.0驱动程序丢失或损坏，请重新安装ODBC驱动!", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
              string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = string.Format( "select * from [{0}$]",this.CB_Sheet.Text);
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            this.gridView1.Columns.Clear();
            this.gridControl1.DataSource = ds.Tables[0];
           
           
            conn.Close();
            this.gridView1.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
            this.gridView1.VertScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
            foreach (DevExpress.XtraGrid.Columns.GridColumn gcol in this.gridView1.Columns)
            {
                gcol.MinWidth = 40;
                
            }
          
        }