110,533
社区成员
发帖
与我相关
我的任务
分享
<?xml version="1.0" encoding="utf-8"?>
<table>
<info 字段1="aa" 字段2="bb" 字段3="cc" />
</table>
DataSet dt = new DataSet();
dt.ReadXml("t.xml");
string s1 = dt.Tables["info"].Rows[0]["字段1"].ToString();
string s2 = dt.Tables["info"].Rows[0]["字段2"].ToString();
string s3 = dt.Tables["info"].Rows[0]["字段3"].ToString();
这是读取XML数据写到XLS里的,
Excel.Application excel = new Excel.Application();
int rowIndex = 1;
int colIndex = 0;
excel.Application.Workbooks.Add(true);
excel.AlertBeforeOverwriting = false;
DataSet ds = new DataSet();
ds.ReadXml("test.xml");
System.Data.DataTable table = ds.Tables[0];
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.ActiveWorkbook.SaveAs(System.Windows.Forms.Application.StartupPath+"\\test.XLS", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, true);
excel.Quit();
excel = null;
//从XML读取要入库的字段
DataSet ds = new DataSet();
ds.ReadXml("test.xml");
string s1 = ds.Tables["info"].Rows[0]["字段1"].ToString();
string s2 = ds.Tables["info"].Rows[0]["字段2"].ToString();
string s3 = ds.Tables["info"].Rows[0]["字段3"].ToString();
//读取XLS的数据,写到ds中
string namefile = System.Windows.Forms.Application.StartupPath + "\\test.XLS";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + namefile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
strExcel = "select * from [sheet1$]";
OleDbDataAdapter oadapter = new OleDbDataAdapter(strExcel, strConn);
DataSet ds0 = new DataSet();
oadapter.Fill(ds0, "table1");
//根据XML中的取的字段对应读取ds中的数据
string name = ds0.Tables[0].Rows[0][s1].ToString();
string no = ds0.Tables[0].Rows[0][s2].ToString();
string old = ds0.Tables[0].Rows[0][s3].ToString();
//写入到数据库中
string connstring = "server=.;uid=sa;password=sa;database=test";
SqlConnection con = new SqlConnection(connstring);
con.Open();
string sql = "insert into student values('" + name + "','" + no + "','" + old + "')";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();