excel2003:
//读取Excel表并返回一个DataSet
public static DataSet ExcelToDataSet(string filePath, string sheetName)
{
DataSet dataSet = new DataSet();
string connstr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" +
filePath + ";Extended Properties='Excel 8.0; HDR=YES;IMEX=1'";//通过ODBC连接数据的字符串,并将一个具体的Excel文件路径传入
string sqlStr = string.Format("select * from [{0}$]", sheetName);
//读取Excel文件中的某一页,页名称根据sheetName变量传入
OleDbConnection conn = new OleDbConnection(connstr);
conn.Open();
OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, conn);
oda.Fill(dataSet);
conn.Close();
return dataSet;
}
if (strExcel == ".xls")
{
DataSet ds = PageHelper.ExcelToDataSet(strPath, "Sheet1");
List<FileDirectory> files = DataSetToIList<FileDirectory>(ds, 0) as List<FileDirectory>;
int count = files.Count;
int all = ds.Tables[0].Rows.Count;
files.ForEach(f =>
{
service.Save(f);
});
rptList.ReBind();
if (!(count == all))
throw new JUnitCommonException("总共有" + all + "条档案,成功上传" + count + "条档案。 请检查" + PageHelper.rows
+ PageHelper.areas + PageHelper.units + PageHelper.haveareas);
}
else
{
throw new JUnitCommonException("文件格式不对,只允许上传.xls格式的文件");
}
//把DataSet转换成IList<T>泛型
public IList<FileDirectory> DataSetToIList<T>(DataSet dataSet, int index)
{
List<FileDirectory> ts = new List<FileDirectory>();
DataTable dt = dataSet.Tables[index];//得到DataSet下的某一张表
PageHelper.rows = "";
PageHelper.areas = "";
PageHelper.units = "";
PageHelper.haveareas = "";
for (int i = 0; i < dt.Rows.Count; i++)//循环数据表dataTable中的每一行
{
FileDirectory t = Activator.CreateInstance<FileDirectory>();//通过反射实例化一个对象
Area dist = new Area(), comp = new Area();
if (dt.Rows[i][0].IsNotNull() && dt.Rows[i][2].IsNotNull()
&& dt.Rows[i][4].IsNotNull() && dt.Rows[i][5].IsNotNull()
&& dt.Rows[i][6].IsNotNull() && dt.Rows[i][7].IsNotNull()
&& dt.Rows[i][10].IsNotNull() && dt.Rows[i][11].IsNotNull())
{
using (AreaService service = new AreaService())
{
dist = service.GetModel(Area.F.AreaName == dt.Rows[i][10]);
if (dist.IsNotNull())
comp = service.GetModel(Area.F.AreaName == dt.Rows[i][11] & Area.F.ParentId == dist.AreaId);
}
t.fileNum = dt.Rows[i][0].ToString();
t.head = dt.Rows[i][1].ToString();
t.serial = dt.Rows[i][2].ToString();
t.statuteDate = dt.Rows[i][3].ToString().ToDateTime().IsNotNull()
? ExtensionMethods.ToString(dt.Rows[i][3].ToString().ToDateTime(), "yyyy-MM-dd") : dt.Rows[i][3].ToString(); ;
t.title = dt.Rows[i][4].ToString();
t.storageLife = dt.Rows[i][5].ToString();
t.carrier = dt.Rows[i][6].ToString();
t.category = dt.Rows[i][7].ToString();
t.annexName = "~/downloads/Directory/" + dt.Rows[i][8].ToString();
t.relevantPerson = dt.Rows[i][9].ToString();
t.creater = PageHelper.CurrentUser.MemberId;
if (dist.IsNotNull())
{
if (PageHelper.CurrentUser.AreaIds.Contains(dist.AreaId.ToString()))
{
t.district = dist.AreaId;
if (comp.IsNotNull())
{
t.company = comp.AreaId;
ts.Add(t);
}
else
PageHelper.units += string.Format(" 第" + (i + 1) + "行第12列(L)" + dt.Rows[i][10] + "下不存在单位:" + dt.Rows[i][11] + "; ");
}
else
PageHelper.haveareas += string.Format(" 该用户没有" + dt.Rows[i][10] + "的权限; ");
}
else
PageHelper.areas += string.Format(" 第" + (i + 1) + "行第11列(K)" + "区域:" + dt.Rows[i][10] + "不存在; ");
}
else
PageHelper.rows += dt.Rows[i][0].IsNull() ? string.Format(" 第{0}行第{1}列({2}); ", i + 1, "1", "A")
: dt.Rows[i][2].IsNull() ? string.Format(" 第{0}行第{1}列({2}); ", i + 1, "3", "C")
: dt.Rows[i][4].IsNull() ? string.Format(" 第{0}行第{1}列({2}); ", i + 1, "5", "E")
: dt.Rows[i][5].IsNull() ? string.Format(" 第{0}行第{1}列({2}); ", i + 1, "6", "F")
: dt.Rows[i][6].IsNull() ? string.Format(" 第{0}行第{1}列({2}); ", i + 1, "7", "G")
: dt.Rows[i][7].IsNull() ? string.Format(" 第{0}行第{1}列({2}); ", i + 1, "8", "H")
: dt.Rows[i][10].IsNull() ? string.Format(" 第{0}行第{1}列({2}); ", i + 1, "11", "K")
: dt.Rows[i][11].IsNull() ? string.Format(" 第{0}行第{1}列({2}); ", i + 1, "12", "L") : " 档号、文件序号、文件标题、保管期限、载体、类别、区域、单位这些字段都不能为空...";
}
return ts;
}
xml文件:
DataContext db = new DataContext();
List<Infomantion> listinfo = new List<Infomantion>();
int count = 0;
string news = FileHelper.ReadTextFile(Server.MapPath("/Config/news.xml"), Encoding.UTF8);
XmlDocument doc = new XmlDocument();
doc.Load(Server.MapPath("/Config/news.xml"));
XmlNode xn = doc.SelectSingleNode("DajNews");
XmlNodeList xnList = xn.ChildNodes;
foreach (XmlNode xm in xnList)
{
XmlElement xe = (XmlElement)xm;
Infomantion model = new Infomantion
{
Categroys = xe.GetAttribute("NewsTypeID").ToInt(),
Title = xe.GetAttribute("Title"),
Pictures = xe.GetAttribute("ObjectUrl"),
Author = xe.GetAttribute("Author"),
SubTitle = xe.GetAttribute("NewsSource"),//来源
Content = xe.GetAttribute("Content"),
PublishTime = xe.GetAttribute("IssuedDate").ToDateTime().Value,
CreateTime = xe.GetAttribute("VerifyDate").ToDateTime().Value,
TitlePicture = xe.GetAttribute("TitlePic"),
OrderBy = xe.GetAttribute("OrderId").ToDecimal(),
Counts = xe.GetAttribute("ClickedCount").ToInt(),
//LastAdmin = xe.GetAttribute("UserID").ToInt(),
};
List<Infomantion> list = InfomantionService.DefaultService.GetList(Infomantion.F.Content == model.Content | Infomantion.F.Title==model.Title).ToList();
if (list.Count > 0) { }
else
listinfo.Add(model);
}
listinfo.ForEach(l =>
{
l.Save(db);
count++;
});
MessageBox.Show("共导入" + count + "条数据");