C# DataGridVie 导入EXCEL 和导出 EXCEL

Question

本帖最后由 senguang145 于 2014-01-14 21:22:24 编辑

Answer 1

用NPOI啊 现成的DLL 直接调用就可以了

Answer 2

什么意思？DLL？  网上就有么？ 导入导出吗？能详细说说嘛？

Answer 3

借花献佛：
代码来自这里：http://blog.csdn.net/orichisonic/article/details/6608307
要引用：
　Microsoft.CSharp
  Microsoft.Core
  Microsoft.Office.Interop.Excel
从excel读入：
 OpenFileDialog fileDLG = new OpenFileDialog();
            fileDLG.Title = "Open Excel File";
            fileDLG.Filter = "Excel Files|*.xls;*.xlsx";
            fileDLG.InitialDirectory = @"C:\Users\...\Desktop\";
            if (fileDLG.ShowDialog() == DialogResult.OK)
            {
                string filename = System.IO.Path.GetFileName(fileDLG.FileName);
                string path = System.IO.Path.GetDirectoryName(fileDLG.FileName);               
                string ExcelFile = @path + "\\" + filename;
                if (!File.Exists(ExcelFile))
                    MessageBox.Show(String.Format("File {0} does not Exist", ExcelFile));                OleDbConnection theConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFile + ";Extended Properties=Excel 12.0;");
                theConnection.Open();
                OleDbDataAdapter theDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", theConnection);
                DataSet DS = new DataSet();
                theDataAdapter.Fill(DS, "ExcelInfo");
                dataGridView1.DataSource = DS.Tables["ExcelInfo"];
                formatDataGrid();
                MessageBox.Show("Excel File Loaded");导出为excel:
int iRows = 0;
            int iCols = 0;
            int iTrueCols = 0;
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);
            Microsoft.Office.Interop.Excel.Worksheet ws = null;            if (wb.Worksheets.Count > 0)
            {
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
            }
            else
            {
                wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
            }            if (ws != null)
            {
                ws.Name = "SheetName";                iRows = dataGridView1.Rows.Count;      //加上列头行  
                iTrueCols = dataGridView1.Columns.Count;   //包含隐藏的列，一共有多少列                  //求列数，省略Visible = false的列  
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    if (dataGridView1.Columns[i].Visible) iCols++;
                }                string[,] dimArray = new string[iRows + 1, iCols];                for (int j = 0, k = 0; j < iTrueCols; j++)
                {
                    //省略Visible = false的列  
                    if (dataGridView1.Columns[j].Visible)
                    {
                        dimArray[0, k] = dataGridView1.Columns[j].HeaderText;
                        k++;
                    }
                }                for (int i = 0; i < iRows; i++)
                {
                    for (int j = 0, k = 0; j < iTrueCols; j++)
                    {
                        //省略Visible = false的列  
                        if (dataGridView1.Columns[j].Visible)
                        {
                            if (dataGridView1.Rows[i].Cells[j].Value != null)
                                dimArray[i + 1, k] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                            else
                                dimArray[i + 1, k] = "";
                            k++;                        }                    }                }
                ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Value2 = dimArray;
                ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Bold = true;
                ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).Font.Size = 10.0;
                ws.get_Range(ws.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[iRows + 1, iCols] as Microsoft.Office.Interop.Excel.Range).RowHeight = 14.25;
                //ws.Columns[.ColumnWidth = datagridview.Columns[0].Width;  
                for (int j = 0, k = 0; j < iTrueCols; j++)
                {
                    //省略Visible = false的列  
                    if (dataGridView1.Columns[j].Visible)
                    {
                        ws.get_Range(ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range, ws.Cells[1, k + 1] as Microsoft.Office.Interop.Excel.Range).ColumnWidth = (dataGridView1.Columns[j].Width / 8.4) > 255 ? 255 : (dataGridView1.Columns[j].Width / 8.4);
                        //ws.Columns.c = datagridview.Columns[j].Width;  
                        k++;
                    }
                }
            }
            ws.SaveAs("aaa.xls");             
            app.Visible = true;

Answer 4

最佳Excel导入实践:
http://www.cnblogs.com/atao/archive/2009/10/18/1585663.html讲的很详细  用的就是NPOI