.net 操作xsl

...{
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("../xls_bang/bang.xls")+";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
    OleDbConnection oleCon=new OleDbConnection(strCon);
    OleDbDataAdapter oleDA=new OleDbDataAdapter("select * from [Sheet1$]",oleCon);
    DataSet ds=new DataSet();
    oleDA.Fill(ds);
    dgBang.DataSource=ds;
    dgBang.DataBind();
}

 首先需要对XLS文件进行[名称定义]，具体操作：用鼠标在sheet中选择一个表格区域，然后点击菜单[插入]-->[名称]-->[定义]即可，输入一个名字，如tbl_test。这样就定义了一个类似于数据库中table表名了。
 接下来，我们开始用vs2003开始编辑了。代码如下：

 ...{
//连接XLS文件的字符串
 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDestFile + ";Extended Properties=Excel 8.0";
 OleDbConnection conn = new OleDbConnection(strConn);
 conn.Open();
 OleDbDataAdapter da = new OleDbDataAdapter("select * from tbl_test",conn);//tbl_test就是我们先前定义的名称
 OleDbCommandBuilder cmb = new OleDbCommandBuilder(da);
 da.InsertCommand = cmb.GetInsertCommand();
 DataSet ds = new DataSet("dsname");
 da.Fill(ds);
 //更新ds对象
 string[] strRowCount = ...{"val1","val2","val3"};
 ds.Tables[0].Rows.Add(strRowCount);

 da.Update(ds);
 ds.AcceptChanges();
   
 da.Dispose();
 conn.Close();
}

 

 

方法2

 依赖Interop.Excel.dll组件

Excel.Application excel = new Excel.ApplicationClass();
            try
            ...{
                object option = Type.Missing;
                Excel._Workbook book = excel.Workbooks.Open(templatePath, option, option, option,
                    option,    option,    option, option, option, option, option,    option, option, option, option);
                Excel.Sheets sheets = (Excel.Sheets) book.Worksheets;
                Excel._Worksheet sheet = (Excel._Worksheet) sheets.get_Item(1);


                // Heading.
                int startRow = _startRow;
                AddInfo("Start generate xsl  ");
                foreach(Object obj in pickCol)
                ...{
                    Pick pick = obj as Pick;
                
                    for (int i=0; i<_outOrder.Count; i++)
                    ...{
                        FieldInfo field = _outOrder[i] as FieldInfo;
                        SetCellValue(sheet, ((char)('A'+i)).ToString() + startRow.ToString(), field.GetValue(pick) as String);
                    }

                    startRow++;
                }

                AddInfo("Save result to " + outFile);

                string fileTemp = outFile.Substring(0, outFile.Length - 4) + "_"  + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
                File.Delete(fileTemp);
                book.SaveAs(fileTemp, option, option, option, option, option,    
                    Excel.XlSaveAsAccessMode.xlExclusive,
                    option, option, option, option, option);
                excel.Workbooks.Close();

                File.Delete(outFile);
                File.Move(fileTemp, outFile);
            
            }
            finally
            ...{
                excel.Quit();
            }

    private void SetCellValue(Excel._Worksheet sh, string cellName, string val)
        ...{
            Excel.Range range = sh.get_Range(cellName, Type.Missing);
            range.Value2 = val;
        }
        private string GetCellValue(Excel._Worksheet sh, string cellName)
        ...{
            string s = "";
            Excel.Range range = sh.get_Range(cellName, Type.Missing);
            if (range.Value2 == null)
                s = "";
            else
                s = range.Value2.ToString().Trim();
            return s;

        }