<div id="import" runat="server" visible="false">
Step1:<asp:HyperLink ID="HyperLink1" NavigateUrl="~/CommonTemplate/设备清单模版.xlsx" runat="server">下载模版</asp:HyperLink><br />
Step2:
<asp:FileUpload ID="FileUpload1" ToolTip="选择文件" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="上传" OnClick="btnUpload_Click" />
<asp:Label ID="lbsuccess" runat="server"></asp:Label>
<asp:LinkButton ID="lbfail" runat="server" OnClick="lbfail_Click" ForeColor="Red"></asp:LinkButton><br />
</div>
/// <summary>
/// 上传EXCEL
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string path = Server.MapPath("UploadFile");
string fileName = path + "\\" + Guid.NewGuid().ToString("N") + FileUpload1.FileName;
string serverFilename = "";
if (FileUpload1.FileName.ToLower().IndexOf(".xlsx") > 0)
{
serverFilename = "xlsx";
}
else
{
if (FileUpload1.FileName.ToLower().IndexOf(".xls") > 0 && FileUpload1.FileName.EndsWith("xls"))
{
serverFilename = "xls";
}
else
{
//提示
Page.RegisterClientScriptBlock("TS", "<script>jError('必须是Excel格式文件!', { TimeShown: 1000, VerticalPosition: 'center' });;</script>");
return;
}
}
FileUpload1.PostedFile.SaveAs(fileName); //上传文件到服务器端
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties='Excel 8.0;HDR=YES;'";
System.Data.DataTable dt = new System.Data.DataTable();
if (serverFilename == "xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties='Excel 12.0;HDR=YES'";
}
if (serverFilename == "xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties='Excel 8.0;HDR=YES;'";
}
OleDbConnection conn = new OleDbConnection(strConn);
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [sheet1$]", strConn);
adapter.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
DataTable errorData = GetData("1");
DataTable successData = GetData("2");
for (int i = 0; i < dt.Rows.Count; i++) //循环导入的数据
{
if (!string.IsNullOrEmpty(dt.Rows[i]["名称"].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[i]["型号"].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[i]["数量"].ToString().Trim()) && !string.IsNullOrEmpty(dt.Rows[i]["单价"].ToString().Trim()))
{
//检查盘点数量的数据格式
if (YzNum(dt.Rows[i]["数量"].ToString().Trim()))
{
if (!YzPrice(dt.Rows[i]["单价"].ToString().Trim()))
{
GetErrorData(dt, errorData, i, "单价必须为正数");
}
else
{
GetErrorData(dt, successData, i, "");
}
}
else
{
GetErrorData(dt, errorData, i, "数量必须为正整数");
}
}
else //3个元素中有一个为NULL
{
GetErrorData(dt, errorData, i, "数据不全");
}
}
Session["ErrorData"] = errorData;
//Session["SuccessData"] = successData;
if (IPB.SaveIntegratedEquipmentList(successData, ViewState["pid"].ToString()))
{
ShowControl();
//BindGVData();
Page.RegisterClientScriptBlock("TS", "<script>jError('导入成功', { TimeShown: 1000, VerticalPosition: 'center' });;</script>");
}
else
{
Page.RegisterClientScriptBlock("TS", "<script>jError('导入失败!', { TimeShown: 1000, VerticalPosition: 'center' });;</script>");
}
lbsuccess.Text = "成功" + (dt.Rows.Count - errorData.Rows.Count) + "条";
if (errorData != null && errorData.Rows.Count > 0)
{
lbfail.Text = "数据错误：" + errorData.Rows.Count + "条";
}
conn.Close();
}
else
{
Page.RegisterClientScriptBlock("TS", "<script>jError('Exece表没有数据!', { TimeShown: 1000, VerticalPosition: 'center' });;</script>");
conn.Close();
}
}
else
{
//提示
Page.RegisterClientScriptBlock("TS", "<script>alert('请选择Excel格式文件');</script>");
}
}
/// <summary>
/// 判断一个数是否为正整数
/// </summary>
/// <param name="content"></param>
/// <returns></returns>
private bool YzNum(string content)
{
bool y = true;
try
{
int.Parse(content);
if (int.Parse(content) < 0) //非正数
{
y = false;
}
if (content.IndexOf('.') >= 0) //是小数
{
y = false;
}
}
catch
{
y = false;
}
return y;
}
/// <summary>
/// 验证价格
/// </summary>
/// <param name="content"></param>
/// <returns></returns>
private bool YzPrice(string content)
{
bool y = true;
try
{
float.Parse(content);
if (float.Parse(content) < 0) //非正数
{
y = false;
}
}
catch
{
y = false;
}
return y;
}
/// <summary>
/// 返回错误表列
/// </summary>
/// <returns></returns>
private DataTable GetData(string bz)
{
#region 错误数据标题
DataTable dtError = new DataTable();
dtError.Columns.Add("名称");
dtError.Columns.Add("型号");
dtError.Columns.Add("描述");
dtError.Columns.Add("数量");
dtError.Columns.Add("单价");
if (bz == "1")
{
dtError.Columns.Add("错误信息");
}
else
{
dtError.Columns.Add("");
}
#endregion
return dtError;
}
/// <summary>
/// 将错误的信息添加到表中
/// </summary>
/// <param name="dt">读入到系统中的数据</param>
/// <param name="dtError">错误表数据</param>
/// <param name="rowNum">当前行数</param>
/// <param name="errorDesc">错误描述</param>
/// <returns>返回错误表最新数据</returns>
private DataTable GetErrorData(DataTable dt, DataTable dtError, int rowNum, string errorDesc)
{
DataRow dr = dtError.NewRow();
for (int m = 0; m < dt.Columns.Count; m++)
{
dr[m] = dt.Rows[rowNum][m].ToString();
}
dr[dt.Columns.Count] = errorDesc;
dtError.Rows.Add(dr); //把失败的行添加到新的数据表中
return dtError;
}
protected void lbfail_Click(object sender, EventArgs e)
{
if (Session["ErrorData"] != null)
{
DrawExcel((DataTable)Session["ErrorData"]);
}
}
/// <summary>
/// 导出HTML格式的数据
/// </summary>
/// <param name="pDataTable"></param>
private void DrawExcel(System.Data.DataTable pDataTable)
{
int tRowCount = pDataTable.Rows.Count;
int tColumnCount = pDataTable.Columns.Count;
Response.Expires = 0;
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=ErrorData.xls");
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
//Response.Write("<style type=\"text/css\">.format{mso-number-format:\'\@\';}<\style>");
Response.Write("<Table borderColor=black border=1>");
Response.Write("\n");
Response.Write("\n <TR>");
for (int i = 0; i < tColumnCount; i++) //画数据标题栏
{
Response.Write("\n <TD bgcolor = #ffcd00 style=\"height: 30;font-weight:bold;\" align=\"center\" >");
Response.Write(pDataTable.Columns[i].ColumnName);
Response.Write("\n </TD>");
}
Response.Write("\n </TR>");
for (int j = 0; j < tRowCount; j++)
{
Response.Write("\n <TR>");
for (int k = 0; k < tColumnCount; k++)
{
Response.Write("\n <TD align=\"right\" style='vnd.ms-excel.numberformat:@'>");
Response.Write(pDataTable.Rows[j][k].ToString());
Response.Write("\n </TD>");
}
Response.Write("\n </TR>");
}
Response.Write("</Table>");
Response.End();
}
