按照一定的规格将XML文件中的内容批量导入至数据库中
程序员文章站
2022-06-11 13:54:54
...
一、前台点击按钮
<li>
asp:Button ID="ImportMember" runat="server" CssClass="btn btn-primary" Text="导入会员" />
</li>
二、后台按钮声明、编辑点击事件(展示的代码为将表格数据写入数据库,注释掉的代码为将表格数据写入txt文档)
protected Button ImportMember;
protected override void OnInitComplete(EventArgs e)
{
base.OnInitComplete(e);
//一键导入会员
}
private void ImportMember_Click(object sender, EventArgs e)
{
StringBuilder sbr = new StringBuilder();
DataTable dt = new DataTable();
dt.Columns.Add("ID", Type.GetType("System.String"));
dt.Columns.Add("UserId", Type.GetType("System.String"));
dt.Columns.Add("UserName", Type.GetType("System.String"));
dt.Columns.Add("CommissionType", Type.GetType("System.String"));
dt.Columns.Add("Amount", Type.GetType("System.String"));
dt.Columns.Add("StarTime", Type.GetType("System.String"));
string filename = System.Web.HttpContext.Current.Server.MapPath(Globals.ApplicationPath + "/myxls.xls");
//using (FileStream fs = File.OpenRead(filename))
using (FileStream fs = File.OpenRead(@"c:/myxls.xls")) //打开myxls.xls文件
{
HSSFWorkbook wk = new HSSFWorkbook(fs); //把xls文件中的数据写入wk中
for (int i = 0; i < wk.NumberOfSheets; i++) //NumberOfSheets是myxls.xls中总共的表数
{
ISheet sheet = wk.GetSheetAt(i); //读取当前表数据
for (int j = 0; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
{
IRow row = sheet.GetRow(j); //读取当前行数据
if (row != null)
{
DataRow dr = dt.NewRow();
//sbr.Append("-------------------------------------\r\n"); //读取行与行之间的提示界限
for (int k = 0; k <= row.LastCellNum; k++) //LastCellNum 是当前行的总列数
{
ICell cell = row.GetCell(k); //当前表格
if (cell != null)
{
sbr.Append(cell.ToString()); //获取表格中的数据并转换为字符串类型
dr[k+1] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
}
}
dt.TableName = "Hishop_ceshi";
MemberDao member = new MemberDao();
//将新建的表批量高效的存入数据库中
if (!member.BulkToDB(dt))
{
IDictionary<string, string> param = new Dictionary<string, string>();
param.Add("secCommission", DateTime.Now.ToString());
param.Add("secCommissionError", "执行失败!");
//将键值对写入错误日志中
Globals.WriteLog(param, "", "", "", "SecCommission");
}
else
{
this.ShowMsg("导入成功", true);
}
//sbr.ToString();
//using (StreamWriter wr = new StreamWriter(new FileStream(@"c:/myText.txt", FileMode.Append))) //把读取xls文件的数据写入myText.txt文件中
//{
// this.ShowMsg("导入成功", true);
// wr.Write(sbr.ToString());
// wr.Flush();
//}
}
三、其他调用方法
public bool BulkToDB(DataTable dt)
{
Boolean rls = false;
if (dt.Rows.Count == 0)
{
return rls;
}
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["HidistroSqlServer"].ConnectionString);
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
bulkCopy.DestinationTableName = dt.TableName;
bulkCopy.BatchSize = dt.Rows.Count;
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
bulkCopy.WriteToServer(dt);
rls = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
if (bulkCopy != null)
bulkCopy.Close();
}
return rls;
}
四、引用Npoi.dll文件
详见“读取目标文件”中的dll文件(C:\Users\Administrator\Desktop\ASP.NET基础知识\程序笔记\读取目标文件\bin\.net 4.0)内的三个文件
上一篇: 色眼识人[转] -- yet another 心理测试
下一篇: [转帖]活了一百万次的猫