NPOI DLL下载地：http://npoi.codeplex.com/releases
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Topevery.DUM.PublicServer.Manager
{
public partial class ToiletListImport : BasePage
{
private static readonly string SavePath = System.Configuration.ConfigurationSettings.AppSettings["SavePath"];
private int _editId;
public int EditId
{
get
{
_editId = );
return _editId;
}
set
{
_editId = value;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
//SetCondition();
}
}
protected void btnImport_Click(object o, EventArgs e)
{
//
string Msg = "";
;
;
//保存文件
string filePath = string.Format("~/{0}", SavePath);
filePath = Path.Combine(filePath, string.Format("{0}_{1}", UserObject.UserID.ToString(), fulOffLineData.FileName));
string strPhysicalPath = Server.MapPath(filePath);
fulOffLineData.PostedFile.SaveAs(strPhysicalPath);
{
IWorkbook workBook = null;
ISheet sheet = null;
IRow row = null;
try
{
//加载文档
using (FileStream fileStream = new FileStream(strPhysicalPath, FileMode.Open))
{
//不同格式实例化不同工作薄
if (strPhysicalPath.EndsWith(".xls"))//2003版本及以上
{
workBook = new HSSFWorkbook(fileStream);
}
else if (strPhysicalPath.EndsWith(".xlsx"))//2007版本及以上
{
workBook = new XSSFWorkbook(fileStream);
}
}
//加载指定工作薄
sheet = workBook.GetSheetAt();
//加载表头
IRow headerRow = sheet.GetRow();
ICell cellHeader = null;
ICell cell = null;
int cellCount = headerRow.LastCellNum;
DateTime dtTmpTime = DateTime.Now;
string Code;
string Address;
string regionid;
string ToiletType;
string area;
string ManSquatting;
string WomanSquatting;
string ManagerPersonTel;
string Jingdu;
string Weidu;
string ManagerPerson;
string ClearPerson;
string SuperviseTel;
string opentype;
string Remark;
string status;
string strTmpTime;
string strCreTime;
//遍历
; i <= sheet.LastRowNum; i++)
{
;
try
{
row = sheet.GetRow(i);
Toilet entity = new Toilet();
PcInfo para = new PcInfo();
)
{
para = PcInfoLogic.GetEntity(EditId);
}
else
{
para = new PcInfo();
}
//公厕编号
Code = row.GetCell().ToString();
if (!string.IsNullOrEmpty(Code))
{
para.Name = Code;
entity.Code = Code;
}
//详细位置
Address = row.GetCell().ToString();
if (!string.IsNullOrEmpty(Address))
{
entity.Address = Convert.ToString(Address);
para.Address = Convert.ToString(Address);
}
//区域名称返回区域编号
regionid = row.GetCell().ToString();
if (!string.IsNullOrEmpty(regionid))
{
entity.RegionID = Convert.ToInt32(RegionID(regionid));
}
//类型
ToiletType = row.GetCell().ToString();
if (!string.IsNullOrEmpty(ToiletType))
{
entity.ToiletType = ToiletID(ToiletType);
}
//面积
area = row.GetCell().ToString();
if (!string.IsNullOrEmpty(area))
{
entity.Area = ToiletID(area);
}
//男蹲位数
ManSquatting = row.GetCell().ToString();
if (!string.IsNullOrEmpty(ManSquatting))
{
entity.ManSquatting = Convert.ToInt16(ManSquatting);
}
//女蹲位数
WomanSquatting = row.GetCell().ToString();
if (!string.IsNullOrEmpty(WomanSquatting))
{
entity.WomanSquatting = Convert.ToInt16(WomanSquatting);
}
//经度
Jingdu = Convert.ToString(row.GetCell());
if (!string.IsNullOrEmpty(Jingdu))
{
entity.Jingdu = Jingdu;
}
//维度
Weidu = Convert.ToString(row.GetCell());
if (!string.IsNullOrEmpty(Weidu))
{
entity.Weidu = Weidu;
}
//建设时间
strTmpTime = Convert.ToString(row.GetCell());
if (!string.IsNullOrEmpty(strTmpTime))
{
if (DateTime.TryParse(strTmpTime, out dtTmpTime))
{
entity.BuildTime = dtTmpTime;
}
}
//开门时间
strCreTime = Convert.ToString(row.GetCell());
if (!string.IsNullOrEmpty(strCreTime))
{
if (DateTime.TryParse(strCreTime, out dtTmpTime))
{
entity.DbCreateDate = dtTmpTime;
}
}
//管理人
ManagerPerson = Convert.ToString(row.GetCell());
if (!string.IsNullOrEmpty(ManagerPerson))
{
entity.ManagerPerson = ManagerPerson;
}
//管理人员电话
ManagerPersonTel = Convert.ToString(row.GetCell());
if (!string.IsNullOrEmpty(ManagerPersonTel))
{
entity.ManagerPersonTel = ManagerPersonTel;
}
//保洁人员
ClearPerson = Convert.ToString(row.GetCell());
if (!string.IsNullOrEmpty(ClearPerson))
{
entity.ClearPerson = ClearPerson;
}
//监督电话
SuperviseTel = Convert.ToString(row.GetCell());
if (!string.IsNullOrEmpty(SuperviseTel))
{
entity.SuperviseTel = SuperviseTel;
para.Phone = SuperviseTel;
}
//开放状态
opentype = OpenTypeID(row.GetCell().ToString());
if (!string.IsNullOrEmpty(opentype))
{
entity.OpenType = Convert.ToInt32(opentype);
}
//影像URL
Remark = Convert.ToString(row.GetCell().ToString());
if (!string.IsNullOrEmpty(Remark))
{
entity.Remark = Remark;
para.Desc = Remark;
}
//建设状态
status = StatusID(row.GetCell().ToString());
if (!string.IsNullOrEmpty(status))
{
entity.Status = Convert.ToInt32(status);
}
)
{
PcInfoLogic.Update(para);
entity = ToiletLogic.GetEntity(EditId);
}
else
{
para.DbCreateDate = DateTime.Now;
para.DbLastUpdateDate = DateTime.Now;
para.DbCreateID = UserObject.UserID;
para.MapData = new Microsoft.SqlServer.Types.SqlGeometry();
//公众服务信息表
PcInfoId = (int)PcInfoLogic.Insert(para);
entity.ID = PcInfoId;
}
)
{
entity.DbCreateDate = DateTime.Now;
entity.DbCreateID = UserObject.UserID;
//厕所表
ToiletLogic.Insert(entity);
}
else
{
ToiletLogic.Update(entity);
}
iAddedCount++;
}
catch { iFaileCount++; }
}
//回收资源
cellHeader = null;
headerRow = null;
}
catch (Exception ex)
{
}
finally
{
if (row != null)
row = null;
if (sheet != null)
sheet = null;
if (workBook != null)
{
//workBook.Clear();
workBook = null;
}
}
}
//删除文件
File.Delete(strPhysicalPath);
//返回处理结果
//关闭窗口并刷新父窗口
base.WriteClientScript(string.Format("jUIDialog('导入成功{0}条,失败{1}条!{2}');", iAddedCount, iFaileCount, Msg) + "try{$(document).ready(function(){RelashParent();});}catch(e){}");
}
//根据公厕名称获取公厕编号
public static string RegionID(string RegionName )
{
DataTable ds = ToiletLogic.RegionID(RegionName);
][].ToString();;
}
//根据类型名称获取类型编号
public static int ToiletID(string ToiletName)
{
;
if (ToiletName=="移动厕所")
{
ToiletID= ;
}
else if (ToiletName=="一类水冲公厕")
{
ToiletID = ;
}
else
{
ToiletID = ;
}
return ToiletID;
}
//根据建设状态返回建设编号
public static string StatusID(string StatusName)
{
";
if (StatusName == "建成")
{
StatusID = ";
}
else
{
StatusID = ";
}
return StatusID;
}
//根据开放状态返回建设编号
public static string OpenTypeID(string OpenTypeName)
{
";
if (OpenTypeName == "开放")
{
OpenTypeID =";
}
else
{
OpenTypeID =";
}
return OpenTypeID;
}
}
}
HttpPostedFile file = FileUpload1.PostedFile;
string fileExt = Path.GetExtension(file.FileName);
DataTable dt = new DataTable();
int mon = Convert.ToInt32(drpMonth.SelectedValue);
int year = Convert.ToInt32(drpYear.SelectedValue);
int times = DateTime.DaysInMonth(year, mon);
if (fileExt != ".xls" && fileExt != ".xlsx")
{
}
else
{
try
{
dt = ExcelToDatatable(file.InputStream).Select("c_grid is not null").CopyToDataTable();
}
catch (Exception ex)
{
throw ex;
//ClientScript.RegisterStartupScript(this.GetType(), Guid.NewGuid().ToString(), "<script language='javascript' type='text/javascript'>alert('读取数据出错，请检查文件格式是否正确后重试！');</script>");
}
}
private DataTable ExcelToDatatable(Stream inStream)
{
DataTable dt = new DataTable();
int mon =Convert.ToInt32(drpMonth.SelectedValue);
int year = Convert.ToInt32(drpYear.SelectedValue);
int times = DateTime.DaysInMonth(year, mon);
;
using (HSSFWorkbook workbook = new HSSFWorkbook(inStream))
{
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt();
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
;
rows.MoveNext();//第一行没用
rows.MoveNext();//第二行没用
n++;
string strGrid = "";
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
)//定义列的行
{
List<DataColumn> columnList = new List<DataColumn>() {
new DataColumn("c_grid"),
new DataColumn("c_name"),
new DataColumn("c_date"),
new DataColumn("c_1"),
new DataColumn("c_2"),
new DataColumn("c_3"),
new DataColumn("c_4"),
new DataColumn("c_5"),
new DataColumn("c_6"),
new DataColumn("c_7"),
new DataColumn("c_8"),
new DataColumn("c_9"),
new DataColumn("c_10"),
new DataColumn("c_11"),
new DataColumn("c_12"),
new DataColumn("c_13"),
new DataColumn("c_14"),
new DataColumn("c_15"),
new DataColumn("c_16"),
new DataColumn("c_17"),
new DataColumn("c_18"),
new DataColumn("c_19"),
new DataColumn("c_20"),
new DataColumn("c_21"),
new DataColumn("c_22"),
new DataColumn("c_23"),
new DataColumn("c_24"),
new DataColumn("c_25"),
new DataColumn("c_26"),
new DataColumn("c_27"),
new DataColumn("c_28"),
new DataColumn("c_29"),
new DataColumn("c_30"),
new DataColumn("c_31")
};
foreach (DataColumn column in columnList)
{
dt.Columns.Add(column);
}
}
else
{
DataRow dtRow = dt.NewRow();
//string rValue = "";
; i <= dayCount; i++)
{
HSSFCell cell = (HSSFCell)row.GetCell(i);
)
{
if (cell == null)
{
dtRow[] = strGrid;
}
else
{
if (!string.IsNullOrEmpty(cell.ToString()))
{
strGrid = cell.ToString();
}
dtRow[] = strGrid;
}
}
else
{
if (cell == null)
{
dtRow[i - ] = "";
}
else
{
dtRow[i - ] = cell.ToString();
}
}
}
dt.Rows.Add(dtRow);
}
n++;
}
}
return dt;
}