个人网站:友书小说网(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;
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;
}
}
}
}
如有问题,请加我QQ:631931078或352167311