...
{
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对象
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
string[] strRowCount = ...{"val1","val2","val3"};
ds.Tables[0].Rows.Add(strRowCount);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
da.Update(ds);
ds.AcceptChanges();
da.Dispose();
conn.Close();
}
方法2
依赖Interop.Excel.dll组件
Excel.Application excel = new
Excel.ApplicationClass();
try
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{
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);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
// Heading.
int startRow = _startRow;
AddInfo("Start generate xsl ");
foreach(Object obj in pickCol)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Pick pick = obj as Pick;
for (int i=0; i<_outOrder.Count; i++)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
FieldInfo field = _outOrder[i] as FieldInfo;
SetCellValue(sheet, ((char)('A'+i)).ToString() + startRow.ToString(), field.GetValue(pick) as String);
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
startRow++;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
AddInfo("Save result to " + outFile);
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
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();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
File.Delete(outFile);
File.Move(fileTemp, outFile);
}
finally
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{
excel.Quit();
}
private
void
SetCellValue(Excel._Worksheet sh,
string
cellName,
string
val)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{
Excel.Range range = sh.get_Range(cellName, Type.Missing);
range.Value2 = val;
}
private
string
GetCellValue(Excel._Worksheet sh,
string
cellName)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{
string s = "";
Excel.Range range = sh.get_Range(cellName, Type.Missing);
if (range.Value2 == null)
s = "";
else
s = range.Value2.ToString().Trim();
return s;
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
}