string sql = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\loglist.xls;Extended Properties=\"Excel 8.0\"";
OleDbConnection conn = new OleDbConnection(sql);
OleDbCommand cmd = new OleDbCommand("select * from loglist",conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd); //读取Excel表格
DataSet ds = new DataSet();
da.Fill(ds);
ds.WriteXML();这个方法
OleDbConnection conn = new OleDbConnection(sql);
OleDbCommand cmd = new OleDbCommand("select * from loglist",conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd); //读取Excel表格
DataSet ds = new DataSet();
da.Fill(ds);
ds.WriteXML();这个方法
/// 把Excel文档转换为Xml文档
/// </summary>
/// <param name="excelPath"></param>
/// <param name="xmlPath"></param>
public void changeExcelToXml(string excelPath, string xmlPath)
{
Object Nothing = System.Reflection.Missing.Value;
ApplicationClass excelApp;
Workbook excelBook;
excelApp = new ApplicationClass();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
excelApp.AlertBeforeOverwriting = false;
excelApp.AskToUpdateLinks = false;
//
try
{
excelBook = excelApp.Workbooks.Open(excelPath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
excelBook.SaveAs(xmlPath, XlFileFormat.xlXMLSpreadsheet, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlNoChange, Nothing, Nothing, Nothing, Nothing, Nothing);
}
finally
{
//close
if (excelBook != null)
{
excelBook.Close(Nothing, Nothing, Nothing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook);
excelBook = null;
}
if (excelApp != null)
{
excelApp.Application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
} }
这句话压根就能用呀
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
getDate();
getXml();
}
public DataSet getDate()
{
string sql = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\sample.xls;Extended Properties=\"Excel 8.0\"";
OleDbConnection conn = new OleDbConnection(sql);
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", conn);//工作表名就是Excel显示区下面的工作区名
OleDbDataAdapter da = new OleDbDataAdapter(cmd); //读取Excel表格
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public void getXml()
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("test.xml"));
DataTable dt1 = ds.Tables[0];
DataTable dt2 = getDate().Tables[0];
for (int i = 0; i < dt2.Rows.Count; i++)
{
DataRow dr = dt1.NewRow();
dr[0] = dt2.Rows[i][0];
dr[1] = dt2.Rows[i][1].ToString();
dt1.Rows.Add(dr);
}
ds.WriteXml(Server.MapPath("test.xml")); }
}test.xml<?xml version="1.0" standalone="yes"?>
<test>
<t>
<a>1</a>
<b>2</b>
</t>
<t>
<a>2</a>
<b>b</b>
</t>
<t>
<a>3</a>
<b>c</b>
</t>
<t>
<a>4</a>
<b>d</b>
</t>
<t>
<a>5</a>
<b>e</b>
</t>
</test>
sample.xls文件内容
1 a
2 b
3 c
4 d
5 e
protected void Page_Load(object sender, EventArgs e)
{
//getDate();没有用,调试时用了.忘记删除了.
getXml();
}