上传excel+验证是否excel+解析+校验数据

来源：互联网发布：hash源码编辑：程序博客网时间：2024/06/11 12:23

个人网站：友书小说网（http://laiyoushu.com）绿色纯净无广告，欢迎大家前来看小说

using System;

using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data.SqlClient;
using System.Text;
using Sjune.Model;
using Sjune.BLL;
using System.Collections;
using System.Data.Odbc;
using NPOI.HPSF;
using NPOI.HSSF.Util;

namespace Sjune.Web.program.ashx
{
/// <summary>
/// UpLoadTemplate 的摘要说明
/// </summary>
public class UpLoadTemplate : IHttpHandler
{
public static string path = HttpContext.Current.Request.PhysicalApplicationPath;
protected HSSFWorkbook hssfworkbook;
public void ProcessRequest(HttpContext context)
{
context.Response.Clear();
//context.Response.ContentType = "text/plain";
context.Response.ContentType = "text/html";
HttpFileCollection postedFile = context.Request.Files;
if (postedFile != null && postedFile.Count > 0)
{
try
{
string savepath = "";
string tempPath = "";
string timeDay = DateTime.Now.ToString("yyyyMMdd");
tempPath = System.Web.HttpContext.Current.Server.MapPath("/Upload/" + timeDay + "/");
savepath = tempPath;
string filename = System.IO.Path.GetFileName(postedFile[0].FileName);
string sExtension = filename.Substring(filename.LastIndexOf('.'));
if (!System.IO.Directory.Exists(savepath))
System.IO.Directory.CreateDirectory(savepath);
string sNewFileName = Utils.GetRamCode();
postedFile[0].SaveAs(savepath + sNewFileName + sExtension);
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("{\"Status\":\"");
sb.Append("/Upload/" + timeDay + "/" + sNewFileName + sExtension);
sb.Append("\"}");

//开始校验是否为excel
string url = tempPath + sNewFileName + ".xls";
string excelUrl = url;
try
{
FileStream fsyz = new FileStream(url, FileMode.Open, FileAccess.Read);
byte[] b = new byte[4];
string temstr = "";
//将文件流读取的文件写入到字节数组
if (Convert.ToInt32(fsyz.Length) > 0)
{
fsyz.Read(b, 0, 4);
fsyz.Close();
for (int i = 0; i < b.Length; i++)
{
temstr += Convert.ToString(b[i], 16);
}
}
//判断上传文件的头文件是否是excel格式
if (temstr.ToUpper() == "D0CF11E0")
{
//开始校验文件中的数据
StringBuilder sbr = new StringBuilder();
List<ModelExcel> list = new List<ModelExcel>();
list.Clear();
try
{
using (FileStream fs = File.OpenRead(url)) //打开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 (j > 0)
{
if (row != null)
{
ModelExcel lu = new ModelExcel();
if (row.GetCell(0) != null)
{
lu.Sign_Code = row.GetCell(0).ToString();
}
if (row.GetCell(1) != null)
{
lu.Counts = (row.GetCell(1).ToString()).ToInt();
}
if (row.GetCell(2) != null)
{
lu.Unit = row.GetCell(2).ToString();
}
list.Add(lu);
}
}
}
}

}
}
catch (Exception ex)
{
context.Response.Write("{\"status\" : \"error\",\"msg\": \"" + ex.Message + "\"}");
}
BLL.Order_Depot_BLL bll = new BLL.Order_Depot_BLL();
var ds = bll.GetList(" 1=1").Tables[0].Select();
//keylist数据库中数据
List<string> keyList = ds.Select(p => p["ITNBR"].ToString()).ToList();
List<ErrorModelExcel> meList = new List<ErrorModelExcel>();
meList.Clear();
bool Error = false;
for (int i = 0; i < list.Count; i++)
{
ErrorModelExcel lu = new ErrorModelExcel();
if (!keyList.Contains(list[i].Sign_Code))
{
lu.Sign_Code = list[i].Sign_Code.ToString() + "(Error 不存在该产品)";
Error = true;
}
else
{
lu.Sign_Code = list[i].Sign_Code.ToString();
}
if (list[i].Unit != "箱" && list[i].Unit != "个")
{
lu.Unit = list[i].Unit + "(Error 单位只能为‘箱’或‘个’)";
Error = true;
}
else
{
lu.Unit = list[i].Unit;
}
if (list[i].Counts <= 0)
{
lu.Counts = list[i].Counts.ToString() + "(Error 数量不能小、等于0)";
Error = true;
}
else
{
lu.Counts = list[i].Counts.ToString();
}
meList.Add(lu);
}

if (Error)
{
//生成表头
InitializeWorkbook();
ISheet ErrorSheet1 = hssfworkbook.CreateSheet("错误详情");

var color = NPOI.HSSF.Util.HSSFColor.RED.index;

IRow ErrorRows1 = ErrorSheet1.CreateRow(0);
ErrorRows1.CreateCell(0).SetCellValue("产品识别码");
ErrorRows1.CreateCell(1).SetCellValue("数量");
ErrorRows1.CreateCell(2).SetCellValue("单位");

IWorkbook wb = new HSSFWorkbook();
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OLIVE_GREEN.RED.index;

//循环填充内容
for (int k = 0; k < meList.Count; k++)
{
IRow rows = ErrorSheet1.CreateRow((k + 1));
rows.CreateCell(0).SetCellType(CellType.STRING);
rows.CreateCell(0).SetCellValue(meList[k].Sign_Code);
rows.CreateCell(1).SetCellType(CellType.STRING);
rows.CreateCell(1).SetCellValue(meList[k].Counts);
rows.CreateCell(2).SetCellType(CellType.STRING);
rows.CreateCell(2).SetCellValue(meList[k].Unit);
}

//var ErrorUrl = @"c://Error" + Utils.GetRamCode() + ".xls";
var ErrorUrl = @"c://ErrorData/ErrorExcel" + Utils.GetRamCode() + ".xls";
//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
using (FileStream fs = File.OpenWrite(ErrorUrl))
{
hssfworkbook.Write(fs);
}
Error = false;
list.Clear();
meList.Clear();
ErrorUrl = ErrorUrl.Substring(ErrorUrl.IndexOf("ta/") + 3);
context.Response.Write(JsonHelper.ObjectToJSON(new { Status = ErrorUrl }));
}
else
{
//验证通过，回传状态及路径信息
context.Response.Write(sb.ToString());
}
}
else
{
File.Delete(excelUrl);
//文件格式验证不通过，回传状态信息
context.Response.Write(JsonHelper.ObjectToJSON(new { Status = "请上传正确的Excel格式文件！" }));
}

}
//头文件解析失败，不是excel格式，回传相应信息
catch (Exception)
{
File.Delete(excelUrl);
//文件格式验证不通过，回传状态信息
context.Response.Write(JsonHelper.ObjectToJSON(new { Status = "请上传正确的Excel格式文件！" }));
}

}
catch (Exception ex)
{
context.Response.Write("{\"status\" : \"error\",\"msg\": \"" + ex.Message + "\"}");
}
}
}
protected void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
}

public class ErrorModelExcel
{
public string Sign_Code { get; set; }
public string Special_Code { get; set; }
public string Counts { get; set; }
public string Unit { get; set; }
}

public bool IsReusable
{
get
{
return false;
}
}
}
}

0 0