web excel 导入出

 

public   class  ImportExportToExcel
     ... {
        private string strConn;

        public ImportExportToExcel()
        ...{
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        //从指定的Excel文件导入
        public DataSet ImportFromExcel(string strFileName)
        ...{
            DataSet ds = new DataSet();
            ds = doImport(strFileName);
            return ds;
        }

        //执行导入
        private DataSet doImport(string strFileName)
        ...{
            if(strFileName=="") return null;
            strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strFileName+";Extended Properties=Excel 8.0";            
            OleDbDataAdapter excelDA = new OleDbDataAdapter("select * from [Sheet1$]",this.strConn);//连接字符串
            DataSet ExcelDs = new DataSet();//建立数据集,用于存放导入Excel的数据
            try
            ...{
                excelDA.Fill(ExcelDs,"ExcelInfo");//填充DataSet
            }
            catch(Exception ee)
            ...{
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
            return ExcelDs;//返回DataSet
        }






        public void ExportToExcel(DataSet ds,string strExcelFileName)
        ...{
            if(ds.Tables.Count == 0|| strExcelFileName=="") return;
            doExport(ds,strExcelFileName);
        }

        //执行导出
        public void doExport(DataSet ds , string strExcelFileName)
        ...{
            Excel.ApplicationClass excel = new Excel.ApplicationClass();//建立Excel对象
            int rowIndex = 1;
            int colIndex=0;
            excel.Application.Workbooks.Add(true);//Excel表为添加状态
            System.Data.DataTable table = ds.Tables[0];//建立DataTable
            foreach(DataColumn col in table.Columns)//填充表头
            ...{
                colIndex++;//列索引值递增
                excel.Cells[1,colIndex] = col.ColumnName;//指定列填充数据
            }

            foreach(DataRow row in table.Rows)//填充数据
            ...{
                rowIndex++;
                colIndex=0;
                foreach(DataColumn col in table.Columns)
                ...{
                    colIndex++;
                    excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
                }
            }
            excel.Visible = false;
            //excel.Visible = true;
            excel.DisplayAlerts = false;
            excel.ActiveWorkbook.SaveAs(strExcelFileName,Excel.XlFileFormat.xlExcel7,Type.Missing,Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            excel.Quit();
            excel=null;
            GC.Collect();//垃圾回收
        }

    }