NopI read excel

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Ctrip.HotelReservation.Common.Utility
{
public class ExcelHelper
{

public static MemoryStream CreateExcel(DataTable SheetTable, DataTable Data)
{
HSSFWorkbook excel = new HSSFWorkbook();

ICellStyle cellStyle = excel.CreateCellStyle();
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//cellStyle.FillPattern = FillPattern.AltBars;

cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
cellStyle.FillPattern = FillPattern.LeastDots;

 

MemoryStream ms = new MemoryStream();
for (int i = 0; i < SheetTable.Rows.Count;i++)
{
//创建Sheet
ISheet sheet = excel.CreateSheet(SheetTable.Rows[i]["SheetName"].ToString());

DataSource(sheet);

DataRow[] dr = Data.Select("SheetName='" + SheetTable.Rows[i]["SheetName"].ToString() + "'");

IRow row = null;
if (dr.Count()>0)
{
row = sheet.CreateRow(i);
}

for (int j = 0; j < dr.Count(); j++)
{
ICell cell = row.CreateCell(j);
SetComment(cell,"我试一下");

cell.CellStyle = cellStyle;
cell.SetCellValue(dr[j]["ColumnName"].ToString());

}
}

excel.Write(ms);

return ms;
}

public static void SetComment(ICell Cell,string commentmessage)
{
IDrawing patr = Cell.Sheet.CreateDrawingPatriarch();

int colindex = Cell.ColumnIndex;

int rowindex = Cell.RowIndex;

//第3行结束
HSSFComment comment = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, colindex + 2, rowindex + 2, colindex + 4, rowindex + 6)) as HSSFComment;
comment.Row = rowindex;
comment.Column = colindex;

comment.String = new HSSFRichTextString(commentmessage);
comment.Author = "Kibon";
comment.Visible = true;
}

public static void DataSource(ISheet sheet)
{
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 0, 0);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });

HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}

public static Sheetinfo GetExcelBySheetName(IWorkbook _workbook,
string sheetName,
int HeaderStart,
List<string> ColumnName,
List<string> Description,
List<string> ExcelPos)
{
Sheetinfo sheetinfo = new Sheetinfo();
sheetinfo.SheetName = sheetName;
ISheet sheet = _workbook.GetSheet(sheetName);

if (sheet != null)
{
var firstRow = sheet.GetRow(HeaderStart);
if (firstRow == null)
return sheetinfo;

sheetinfo.DataInfo = new List<DataInfo>();

for (int i = 1; i <= ExcelPos.Count(); i++)
{
IRow row = sheet.GetRow(HeaderStart+i);
//没有数据的行默认是null　　
if (row == null ) continue;

if (row.Cells!=null && row.Cells.Count>0 && string.IsNullOrEmpty(row.Cells[0].ToString().Trim())) continue;

DataInfo datainfo = new DataInfo();
datainfo.DataID = i;
datainfo.HotelCode = row.GetCell(0, MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString();
datainfo.ColumnInfo = new List<ColumnInfo>();

for (int j = 0; j < ColumnName.Count; j++)
{

ColumnInfo columninfo = new ColumnInfo();
columninfo.ColumnName = ColumnName[j];
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
columninfo.ColumnValue = row.GetCell(j, MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString();
}
else
{
columninfo.ColumnValue = "";
}
datainfo.ColumnInfo.Add(columninfo);

}
sheetinfo.DataInfo.Add(datainfo);
}
}
return sheetinfo;
}

public static Sheetinfo GetExcelBySheetName(string PathFile, string sheetName,
int ExcelDataRowIndex,
List<string> ColumnName,
List<string> Description,
List<string> ExcelPos)
{
Sheetinfo sheetinfo = new Sheetinfo();
sheetinfo.SheetName = sheetName;

using(var fs = new FileStream(PathFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IWorkbook _workbook = WorkbookFactory.Create(fs);

GetExcelBySheetName(_workbook, sheetName, ExcelDataRowIndex, ColumnName, Description, ExcelPos);

}
return sheetinfo;
}

public static List<Sheetinfo> GetExcel(MemoryStream ms,ExcelTemplate excelTemplate)
{
IWorkbook _workbook = WorkbookFactory.Create(ms);

List<Sheetinfo> Result = new List<Sheetinfo>();
for (int i = 0; i < excelTemplate.SheetTemplate.Count; i++)
{
Sheetinfo sheetinfo = new Sheetinfo();
ISheet sheet = _workbook.GetSheet(excelTemplate.SheetTemplate[i].SheetName);

if (sheet == null)
{
sheetinfo.Message = new List<string>() { "当前Sheet不存在!" };
sheetinfo.ResultCode = "F";
break;
}

List<string> Message = CheckVersion(sheet, excelTemplate.SheetTemplate[i].Description, excelTemplate.SheetTemplate[i].ExcelPos, excelTemplate.SheetTemplate[i].ExcelTitleRowIndex);
if (Message.Count()==0)
{
sheetinfo = GetExcelBySheetName(_workbook,
excelTemplate.SheetTemplate[i].SheetName,
excelTemplate.SheetTemplate[i].ExcelDataRowIndex,
excelTemplate.SheetTemplate[i].ColumnName,
excelTemplate.SheetTemplate[i].Description,
excelTemplate.SheetTemplate[i].ExcelPos);
Result.Add(sheetinfo);
}

else
{
sheetinfo.Message = Message;
sheetinfo.ResultCode = "F";
Result.Add(sheetinfo);
}

}

return Result;
}

private static List<string> CheckVersion(ISheet Sheet, List<string> DBColumnDescriptionName, List<string> DBPos, int ExcelTitleRowIndex)
{
IRow row = Sheet.GetRow(ExcelTitleRowIndex);
List<string> Message = new List<string>();
for (int i = 0; i < DBColumnDescriptionName.Count; i++)
{
if (row.GetCell(int.Parse(DBPos[i].ToString()), MissingCellPolicy.RETURN_NULL_AND_BLANK) == null)
{
Message.Add("当前Excel版本不正确,SheetName:[" + Sheet.SheetName + "]" + " [" + DBColumnDescriptionName[i] + "]无此列!");
continue;
}

if (DBColumnDescriptionName[i] != row.GetCell(int.Parse(DBPos[i].ToString()), MissingCellPolicy.RETURN_NULL_AND_BLANK).ToString().Trim())
{
Message.Add("当前Excel版本不正确,SheetName:[" + Sheet.SheetName + "]" + " [" + DBColumnDescriptionName[i] + "]不正确!");
continue;
}
}

return Message;
}
}

public class ExcelTemplate
{
public List<SheetTemplate> SheetTemplate
{
get;
set;
}
}

public class SheetVersionTemplate
{

/// <summary>
/// Excel 表头的开始行数
/// </summary>
public int ExcelDataRowIndex
{
get;
set;
}

/// <summary>
/// Excel 表头检查从第几行开始
/// </summary>
public int ExcelTitleRowIndex
{
get;
set;
}

/// <summary>
/// Sheet 名称
/// </summary>
public string SheetName
{
get;
set;
}

/// <summary>
/// Sheet 版本号
/// </summary>
public int SheetVersion
{
get;
set;
}

}

public class SheetTemplate
{
/// <summary>
/// Sheet 名称
/// </summary>
public string SheetName
{
get;
set;
}

/// <summary>
/// Excel 表头的开始行数
/// </summary>
public int ExcelDataRowIndex
{
get;
set;
}

/// <summary>
/// Excel 表头检查从第几行开始
/// </summary>
public int ExcelTitleRowIndex
{
get;
set;
}
/// <summary>
/// DB对应的列Code
/// </summary>
public List<string> ColumnName
{
get;
set;
}

/// <summary>
/// Excel显示的列名称
/// </summary>
public List<string> Description
{
get;
set;
}

/// <summary>
/// Excel 对应的顺序号
/// </summary>
public List<string> ExcelPos
{
get;
set;
}
}

 

public class ExcelReq
{
public int SheetVersion
{
get;
set;
}

public List<Sheetinfo> Sheetinfo
{
get;
set;
}
}

public class Sheetinfo
{
public string SheetName
{
get;
set;
}

public List<DataInfo> DataInfo
{
get;
set;
}

private string _ResultCode="T";
public string ResultCode
{
get{return _ResultCode;}
set{_ResultCode = value;}
}

public List<string> Message
{
get;
set;
}
}

public class DataInfo
{
public int DataID
{
get;
set;
}

public string HotelCode
{
get;
set;
}

public List<ColumnInfo> ColumnInfo
{
get;
set;
}
}

public class ColumnInfo
{
public string ColumnName
{
get;
set;
}

public string ColumnValue
{
get;
set;
}
}

}

///use

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Ctrip.HotelReservation.Common.Utility;
using Ctrip.HotelReservation.DAL;
using Ctrip.HotelReservation.DB;
using Ctrip.HotelReservation.Entities;
using Ctrip.HotelReservation.Interface;
using Ctrip.HotelReservation.SOA2;
using Newtonsoft.Json;

namespace Ctrip.HotelReservation.Biz
{
public class ExcelOperateBiz
{

public static ExcelMessage GetExcel(string GroupID,MemoryStream ms)
{
ExcelReq req = new ExcelReq();

//读取所有Sheet数据
IHtlInputRequest InputRequest = new HtlInputRequestDB();
List<SheetVersionTemplate> SheetData = InputRequest.GetSheetALL();

ExcelTemplate exTemplate = new ExcelTemplate();
exTemplate.SheetTemplate = new List<SheetTemplate>();

List<string> SheetName = new List<string>();
for (int i = 0; i < SheetData.Count; i++)
{
req.SheetVersion = SheetData[i].SheetVersion;

//跟据SheetID,版本号 得到列名称
SheetTemplate stTemplate = InputRequest.GetDataBySheet2(SheetData[i].SheetVersion, SheetData[i].SheetName);
stTemplate.SheetName = SheetData[i].SheetName;
stTemplate.ExcelDataRowIndex = SheetData[i].ExcelDataRowIndex;
stTemplate.ExcelTitleRowIndex = SheetData[i].ExcelTitleRowIndex;
exTemplate.SheetTemplate.Add(stTemplate);
}
req.Sheetinfo = ExcelHelper.GetExcel(ms, exTemplate);

//检查所有Sheet 的 Column是否正确
var flgCount = (from n in req.Sheetinfo where n.ResultCode == "F" select n).Count();
ExcelMessage message = new ExcelMessage();
if (flgCount == 0)
{

#region 拆分子任务
List<DataInfo> DataInfo = (from n in req.Sheetinfo
where n.SheetName == "母酒店"
select n.DataInfo).First();
List<string> Hotelcode = (from n in DataInfo select n.ColumnInfo[0].ColumnValue).ToList();

List<ExcelReq> SubTaskReq = new List<ExcelReq>();

ExcelReq subTaskData = null;
foreach (string t in Hotelcode)
{
subTaskData = new ExcelReq();
subTaskData.SheetVersion = SheetData[0].SheetVersion;
subTaskData.Sheetinfo = new List<Sheetinfo>();
foreach (Sheetinfo n in req.Sheetinfo)
{
Sheetinfo SubTaskSheetinfo = new Sheetinfo();
SubTaskSheetinfo.SheetName = n.SheetName;

if (n.DataInfo != null)
{
var SubTaskData = (from k in n.DataInfo where k.HotelCode.Trim() == t.Trim() select k);

if (SubTaskData.Count() > 0)
{
SubTaskSheetinfo.DataInfo = SubTaskData.ToList();
subTaskData.Sheetinfo.Add(SubTaskSheetinfo);
}
}

}

//检查解析Excel 数据是否正确
if (subTaskData.Sheetinfo.Count != SheetData.Count)
{
message.ResultCode = ResultCode.Fail;

message.Message = new List<string>() { "Excel 版本不正确，请重新下载版本!" };
return message;
}

SubTaskReq.Add(subTaskData);
}
#endregion

#region 插入主任务
//var settings = new JsonSerializerSettings();
string Data = JsonConvert.SerializeObject(req.Sheetinfo);

Ihtlbookinggevent bookingevent = new MySQL_htlbookinggevent();
int Uploadid = bookingevent.Ins_htl_userupload(GroupID, Data);
#endregion

List<int> ProcessID = new List<int>();

#region 插入子任务
if (SubTaskReq.Count() > 0)
{
int id = bookingevent.Get_MaxSubtaskid();

bookingevent.ins_Subtaskid(id, SubTaskReq.Count());

for (int i = id; i < SubTaskReq.Count + id; i++)
{
string SubTaskData = JsonConvert.SerializeObject(SubTaskReq[i - id]);
int iProcessID = bookingevent.ins_Subtask(Uploadid, i + 1, SubTaskReq[i - id].SheetVersion, SubTaskData);

ProcessID.Add(iProcessID);
}
}

#endregion

//#region 调用接口
//#if DEBUG
//for (int i = 0; i < SubTaskReq.Count; i++)
//{
// IProductInputApproval Req = new ProductInputApproval();

// Req.GetRequest(GroupID, SubTaskReq[i]);
//}
//#endif

message.ResultCode = ResultCode.Sucess;
message.SubtaskID = ProcessID;

//#endregion

}
else
{

message.ResultCode = ResultCode.Fail;

message.Message = new List<string>() { "Excel 版本不正确，请重新下载版本!" };

return message;
}

return message;
}

}
}

 

//page ashx

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using Ctrip.HotelReservation.Biz;
using Ctrip.HotelReservation.Common.Utility;
using Ctrip.HotelReservation.Entities;
using Newtonsoft.Json;
using Ctrip.HotelReservation.Activity.WebControl;
using System.Web.SessionState;

namespace Ctrip.HotelReservation.Activity.Ajax
{
/// <summary>
/// Summary description for ExcelUpload
/// </summary>
public class ExcelUpload : IHttpHandler, IRequiresSessionState
{

public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";//这里很关键，虽然前台数据类型是json，但这里一定要写html
SessionInfo info = context.Session["fishLogin"] as SessionInfo;
if (info == null)
{
context.Response.Write(new { ErrorCode = (int)AjaxErrorCode.NotLoginIn }.ToString());
return;
}

//context.Response.Write("Hello World");
string fileName, fileExtension;//文件名
//HttpFileCollection files = HttpContext.Current.Request.Files
HttpPostedFile file = context.Request.Files["file"];
fileName = System.IO.Path.GetFileName(file.FileName);
fileExtension = System.IO.Path.GetExtension(fileName).ToLower();
int FileLen = file.ContentLength;
Byte[] files = new Byte[FileLen];
Stream sr = file.InputStream;//创建数据流对象
sr.Read(files, 0, FileLen);
sr.Close();

using (MemoryStream ms = new MemoryStream(files, 0, files.Length))
{
ExcelMessage msg = ExcelOperateBiz.GetExcel(info.GroupID, ms);

string SubTaskData = JsonConvert.SerializeObject(msg);

context.Response.Write(SubTaskData);
}
}

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