public NpoiWorksheet(HSSFWorkbook book, ISheet sheet) {
sheet.ForceFormulaRecalculation = true;
Book = book;
Sheet = sheet;
Index = book.GetSheetIndex(sheet);
Name = book.GetSheetName(Index);
}
public void TestGetSheetIndex()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = (HSSFSheet)wb.CreateSheet("Sheet1");
HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet("Sheet2");
HSSFSheet sheet3 = (HSSFSheet)wb.CreateSheet("Sheet3");
HSSFSheet sheet4 = (HSSFSheet)wb.CreateSheet("Sheet4");
Assert.AreEqual(0, wb.GetSheetIndex(sheet1));
Assert.AreEqual(1, wb.GetSheetIndex(sheet2));
Assert.AreEqual(2, wb.GetSheetIndex(sheet3));
Assert.AreEqual(3, wb.GetSheetIndex(sheet4));
// remove sheets
wb.RemoveSheetAt(0);
wb.RemoveSheetAt(2);
// ensure that sheets are Moved up and Removed sheets are not found any more
Assert.AreEqual(-1, wb.GetSheetIndex(sheet1));
Assert.AreEqual(0, wb.GetSheetIndex(sheet2));
Assert.AreEqual(1, wb.GetSheetIndex(sheet3));
Assert.AreEqual(-1, wb.GetSheetIndex(sheet4));
}
public ActionResult Template(int? tagId)
{
var basicInfoSheetName = ProUploadService.BASIC_SHEET;
var moreInfoSheetName = ProUploadService.MORE_SHEET;
var supportSheetName = "不要修改";
var headerLabels = new Dictionary<string, dynamic>() {
{"商品代码",new {dataformat=0,width=10}},
{"商品名称",new {dataformat=0,width=20}},
{"描述",new {dataformat=0,width=50}},
{"吊牌价",new {dataformat=2,width=8}},
{"现价",new {dataformat=2,width=8}},
{"品牌名",new {dataformat=0,width=20}},
{ "分类名",new {dataformat=0,width=20}},
{"门店名",new {dataformat=0,width=20}},
{ "促销活动编码",new {dataformat=0,width=20}},
{ "专题编码（多个以,分割)",new {dataformat=0,width=20}},
{"可销售",new {dataformat=2,width=5}},
{"商品货号",new {dataformat=0,width=10}}
};
var workbook = new HSSFWorkbook();
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.BorderBottom = BorderStyle.THIN;
headerLabelCellStyle.BorderLeft = BorderStyle.THIN;
headerLabelCellStyle.BorderRight = BorderStyle.THIN;
headerLabelCellStyle.BorderTop = BorderStyle.THIN;
headerLabelCellStyle.WrapText = true;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
headerLabelCellStyle.SetFont(headerLabelFont);
//set support sheet
var supportSheet = workbook.CreateSheet(supportSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(supportSheet), true);
Func<int, dynamic, int> supportFill = (rowIndex, data) =>
{
var brandRow = supportSheet.CreateRow(rowIndex++);
var brandCodeCol = brandRow.CreateCell(1);
brandCodeCol.SetCellType(CellType.STRING);
brandCodeCol.SetCellValue(data.Id);
var brandNameCol = brandRow.CreateCell(2);
brandNameCol.SetCellType(CellType.STRING);
brandCodeCol.SetCellValue(data.Name);
return rowIndex;
};
int brandRowIndex = 0;
foreach (var brand in _brandRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
{
brandRowIndex = supportFill(brandRowIndex, brand);
}
int tagRowIndex = brandRowIndex;
var tagLinq = _tagRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name });
//if (tagId.HasValue)
// tagLinq = tagLinq.Where(t => t.Id == tagId.Value);
foreach (var tag in tagLinq.OrderBy(t => t.Name))
{
tagRowIndex = supportFill(tagRowIndex, tag);
}
int storeRowIndex = tagRowIndex;
foreach (var store in _storeRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
{
storeRowIndex = supportFill(storeRowIndex, store);
}
//set basic sheet
var sheet1 = workbook.CreateSheet(basicInfoSheetName);
//workbook.SetSheetOrder(basicInfoSheetName, 0);
var rowFirst = sheet1.CreateRow(0);
Action<int, string, dynamic> cellSetting = (cellindex, desc, option) =>
{
var cell = rowFirst.CreateCell(cellindex);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(desc);
cell.CellStyle = headerLabelCellStyle;
sheet1.SetColumnWidth(cellindex, option.width * 255);
var currentCellStyle = workbook.CreateCellStyle();
currentCellStyle.DataFormat = (short)option.dataformat;
sheet1.SetDefaultColumnStyle(cellindex, currentCellStyle);
};
int index = 0;
foreach (var key in headerLabels.Keys)
{
cellSetting(index++, key, headerLabels[key]);
}
//set constraint
DVConstraint brandConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B$1:$B${1}", supportSheetName, brandRowIndex));
CellRangeAddressList brandaddressList = new CellRangeAddressList(1, 1000, 5, 5);
HSSFDataValidation branddataValidation = new HSSFDataValidation(brandaddressList, brandConstaint);
branddataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(branddataValidation);
DVConstraint tagConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, brandRowIndex + 1, tagRowIndex));
CellRangeAddressList tagaddressList = new CellRangeAddressList(1, 1000, 6, 6);
HSSFDataValidation tagdataValidation = new HSSFDataValidation(tagaddressList, tagConstaint);
tagdataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(tagdataValidation);
DVConstraint storeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, tagRowIndex + 1, storeRowIndex));
CellRangeAddressList storeaddressList = new CellRangeAddressList(1, 1000, 7, 7);
HSSFDataValidation storedataValidation = new HSSFDataValidation(storeaddressList, storeConstaint);
storedataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(storedataValidation);
DVConstraint is4saleConstaint = DVConstraint.CreateExplicitListConstraint(new string[] { ProUploadService.IS_4SALE_YES, ProUploadService.IS_4SALE_NO });
CellRangeAddressList is4saleaddressList = new CellRangeAddressList(1, 1000, 10, 10);
HSSFDataValidation is4saledataValidation = new HSSFDataValidation(is4saleaddressList, is4saleConstaint);
is4saledataValidation.SuppressDropDownArrow = false;
sheet1.AddValidationData(is4saledataValidation);
//set sheet2
//create property value sheet
var propertyLinq = _propertyRepo.Get(b => b.Status != (int)DataStatus.Deleted)
.Join(Context.Set<TagEntity>(), o => o.CategoryId, i => i.Id, (o, i) => new { P = o, C = i })
.OrderBy(b => b.P.CategoryId)
.ThenBy(b => b.P.PropertyDesc);
int propertyRowIndex = storeRowIndex;
foreach (var tag in tagLinq)
{
var propertyFromIndex = propertyRowIndex;
foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id).Select(p=>new {Id = p.P.Id,Name=p.P.PropertyDesc}))
{
propertyRowIndex = supportFill(propertyRowIndex, property);
}
if (propertyRowIndex > propertyFromIndex)
{
var rName = workbook.CreateName();
rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, propertyFromIndex + 1, propertyRowIndex);
rName.NameName = tag.Name;
}
foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id))
{
int valueFromIndex = propertyRowIndex;
foreach (var pvalue in _valueRepo.Get(b => b.Status != (int)DataStatus.Deleted && b.PropertyId == property.P.Id).Select(p => new { Id = p.Id, Name = p.ValueDesc }))
{
propertyRowIndex = supportFill(propertyRowIndex, pvalue);
}
if (propertyRowIndex > valueFromIndex)
{
var rName = workbook.CreateName();
rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, valueFromIndex + 1, propertyRowIndex);
rName.NameName = string.Format("{0}_{1}", property.C.Name, property.P.PropertyDesc);
}
}
}
var moreheaderLabels = new Dictionary<string, dynamic>() {
{"商品代码",new {dataformat=0,width=10}},
{"属性名",new {dataformat=0,width=20}},
{"属性值",new {dataformat=0,width=10}}
};
var sheet2 = workbook.CreateSheet(moreInfoSheetName);
var morerowFirst = sheet2.CreateRow(0);
int moreCellIndex = 0;
foreach (var key in moreheaderLabels.Keys)
{
var cell = morerowFirst.CreateCell(moreCellIndex);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(key);
cell.CellStyle = headerLabelCellStyle;
sheet2.SetColumnWidth(moreCellIndex, moreheaderLabels[key].width * 255);
var currentCellStyle = workbook.CreateCellStyle();
currentCellStyle.DataFormat = (short)moreheaderLabels[key].dataformat;
sheet2.SetDefaultColumnStyle(moreCellIndex, currentCellStyle);
moreCellIndex++;
}
for (int i = 3; i <= 11; i++)
{
var cell = morerowFirst.CreateCell(i);
cell.SetCellType(CellType.STRING);
cell.SetCellValue(string.Empty);
cell.CellStyle = headerLabelCellStyle;
sheet2.SetColumnWidth(i, 10 * 255);
}
//set merge cell for last cell
sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 2, 11));
//set constraint
DVConstraint codeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$A${1}:$A${2}", basicInfoSheetName, 2, 1000));
CellRangeAddressList codeaddressList = new CellRangeAddressList(1, 1000, 0, 0);
HSSFDataValidation codedataValidation = new HSSFDataValidation(codeaddressList, codeConstaint);
codedataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(codedataValidation);
for (int i = 1; i < 1000; i++)
{
//create hide helper cell
sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)&\"_\"&$B${0}",i+1,basicInfoSheetName),
new CellRangeAddress(i,i,12,12));
//create hide helper cell
sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)", i + 1, basicInfoSheetName),
new CellRangeAddress(i, i, 13, 13));
sheet2.SetColumnHidden(12, true);
sheet2.SetColumnHidden(13, true);
DVConstraint pConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($N${0})", i + 1)); // DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, storeRowIndex + 1, propertyRowIndex));
CellRangeAddressList paddressList = new CellRangeAddressList(i,i, 1, 1);
HSSFDataValidation pdataValidation = new HSSFDataValidation(paddressList, pConstaint);
pdataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(pdataValidation);
//set constraint
DVConstraint pvConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($M${0})", i + 1));
CellRangeAddressList pvaddressList = new CellRangeAddressList(i, i, 2, 11);
HSSFDataValidation pvdataValidation = new HSSFDataValidation(pvaddressList, pvConstaint);
pvdataValidation.SuppressDropDownArrow = false;
sheet2.AddValidationData(pvdataValidation);
}
workbook.SetActiveSheet(workbook.GetSheetIndex(sheet1));
var ms = new MemoryStream();
workbook.Write(ms);
ms.Position = 0;
var downloadName = tagId.HasValue ? string.Format("商品上传模版-{0}.xls", tagId.Value) : "商品上传模块.xls";
return File(ms, "application/vnd.ms-excel", downloadName);
}
public void TestBackupRecord()
{
HSSFWorkbook wb = new HSSFWorkbook();
for (int i = 0; i < 10; i++)
{
HSSFSheet s = (HSSFSheet)wb.CreateSheet("Sheet " + i);
InternalSheet sheet = s.Sheet;
}
// Check the initial order
Assert.AreEqual(0, wb.GetSheetIndex("Sheet 0"));
Assert.AreEqual(1, wb.GetSheetIndex("Sheet 1"));
Assert.AreEqual(2, wb.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wb.GetSheetIndex("Sheet 3"));
Assert.AreEqual(4, wb.GetSheetIndex("Sheet 4"));
Assert.AreEqual(5, wb.GetSheetIndex("Sheet 5"));
Assert.AreEqual(6, wb.GetSheetIndex("Sheet 6"));
Assert.AreEqual(7, wb.GetSheetIndex("Sheet 7"));
Assert.AreEqual(8, wb.GetSheetIndex("Sheet 8"));
Assert.AreEqual(9, wb.GetSheetIndex("Sheet 9"));
// Change
wb.Workbook.SetSheetOrder("Sheet 6", 0);
wb.Workbook.SetSheetOrder("Sheet 3", 7);
wb.Workbook.SetSheetOrder("Sheet 1", 9);
// Check they're currently right
Assert.AreEqual(0, wb.GetSheetIndex("Sheet 6"));
Assert.AreEqual(1, wb.GetSheetIndex("Sheet 0"));
Assert.AreEqual(2, wb.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wb.GetSheetIndex("Sheet 4"));
Assert.AreEqual(4, wb.GetSheetIndex("Sheet 5"));
Assert.AreEqual(5, wb.GetSheetIndex("Sheet 7"));
Assert.AreEqual(6, wb.GetSheetIndex("Sheet 3"));
Assert.AreEqual(7, wb.GetSheetIndex("Sheet 8"));
Assert.AreEqual(8, wb.GetSheetIndex("Sheet 9"));
Assert.AreEqual(9, wb.GetSheetIndex("Sheet 1"));
// Read it in and see if it is correct.
MemoryStream baos = new MemoryStream();
wb.Write(baos);
MemoryStream bais = new MemoryStream(baos.ToArray());
HSSFWorkbook wbr = new HSSFWorkbook(bais);
Assert.AreEqual(0, wbr.GetSheetIndex("Sheet 6"));
Assert.AreEqual(1, wbr.GetSheetIndex("Sheet 0"));
Assert.AreEqual(2, wbr.GetSheetIndex("Sheet 2"));
Assert.AreEqual(3, wbr.GetSheetIndex("Sheet 4"));
Assert.AreEqual(4, wbr.GetSheetIndex("Sheet 5"));
Assert.AreEqual(5, wbr.GetSheetIndex("Sheet 7"));
Assert.AreEqual(6, wbr.GetSheetIndex("Sheet 3"));
Assert.AreEqual(7, wbr.GetSheetIndex("Sheet 8"));
Assert.AreEqual(8, wbr.GetSheetIndex("Sheet 9"));
Assert.AreEqual(9, wbr.GetSheetIndex("Sheet 1"));
// Now get the index by the sheet, not the name
for (int i = 0; i < 10; i++)
{
NPOI.SS.UserModel.ISheet s = wbr.GetSheetAt(i);
Assert.AreEqual(i, wbr.GetSheetIndex(s));
}
}
public ActionResult DownloadResult(string ontologyCode, string fileName)
{
OntologyDescriptor ontology;
if (!AcDomain.NodeHost.Ontologies.TryGetOntology(ontologyCode, out ontology))
{
throw new ValidationException("非法的本体码" + ontologyCode);
}
string dirPath = Server.MapPath("~/Content/Import/Excel/" + ontology.Ontology.Code + "/" + AcSession.Account.Id);
string fullName = Path.Combine(dirPath, fileName);
if (!System.IO.File.Exists(fullName))
{
throw new ValidationException("下载的文件不存在" + fullName);
}
// 操作Excel
FileStream fs = System.IO.File.OpenRead(fullName);
IWorkbook workbook = new HSSFWorkbook(fs);//从流内容创建Workbook对象
fs.Close();
ISheet sheet = workbook.GetSheet(ResultSheetName);
var sheetIndex = workbook.GetSheetIndex(sheet);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
if (i != sheetIndex)
{
workbook.RemoveSheetAt(i);
}
}
sheetIndex = workbook.GetSheetIndex("Failed");
if (sheetIndex >= 0)
{
workbook.RemoveSheetAt(sheetIndex);
}
ISheet failedSheet = workbook.CreateSheet("Failed");
if (sheet.LastRowNum == 2)
{
throw new ValidationException("没有待导入数据");
}
int rowIndex = 0;
IRow headRow0 = sheet.GetRow(rowIndex);
var columnWidthDic = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var cell = headRow0.Cells[i];
columnWidthDic.Add(cell.SafeToStringTrim(), sheet.GetColumnWidth(i));
}
IRow failedRow0 = failedSheet.CreateRow(rowIndex);
var cells = headRow0.Cells;
for (int i = 0; i < cells.Count; i++)
{
var cell = failedRow0.CreateCell(i);
cell.CellStyle = cells[i].CellStyle;
cell.SetCellValue(cells[i].SafeToStringTrim());
}
rowIndex++;
IRow headRow1 = sheet.GetRow(rowIndex);
IRow failedRow1 = failedSheet.CreateRow(rowIndex);
cells = headRow1.Cells;
for (int i = 0; i < cells.Count; i++)
{
var cell = failedRow1.CreateCell(i);
cell.CellStyle = cells[i].CellStyle;
cell.SetCellValue(cells[i].SafeToStringTrim());
}
rowIndex++;
IRow headRow2 = sheet.GetRow(rowIndex);
IRow failedRow2 = failedSheet.CreateRow(rowIndex);
cells = headRow2.Cells;
for (int i = 0; i < cells.Count; i++)
{
var cell = failedRow2.CreateCell(i);
cell.CellStyle = cells[i].CellStyle;
cell.SetCellValue(cells[i].SafeToStringTrim());
}
failedSheet.CreateFreezePane(0, 3, 0, 3);
rowIndex++;
int resultFailedRowIndex = rowIndex;
int stateCodeIndex = -1;
int localEntityIdIndex = -1;
int infoValueKeysIndex = -1;
int infoIdKeysIndex = -1;
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var value = headRow0.GetCell(i).SafeToStringTrim();
if (CommandColHeader.StateCode.Equals(value, StringComparison.OrdinalIgnoreCase))
{
stateCodeIndex = i;
break;
}
}
if (stateCodeIndex < 0)
{
throw new ValidationException("目标Excel中没有头为$StateCode的列");
}
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var value = headRow0.GetCell(i).SafeToStringTrim();
if (CommandColHeader.LocalEntityId.Equals(value, StringComparison.OrdinalIgnoreCase))
{
localEntityIdIndex = i;
break;
}
}
if (localEntityIdIndex < 0)
{
throw new ValidationException("目标Excel中没有头为$LocalEntityID的列");
}
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var value = headRow0.GetCell(i).SafeToStringTrim();
if (CommandColHeader.InfoValueKeys.Equals(value, StringComparison.OrdinalIgnoreCase))
{
infoValueKeysIndex = i;
break;
}
}
if (infoValueKeysIndex < 0)
{
throw new ValidationException("目标Excel中没有头为$InfoValueKeys的列");
}
for (int i = 0; i < headRow0.Cells.Count; i++)
{
var value = headRow0.GetCell(i).SafeToStringTrim();
if (CommandColHeader.InfoIdKeys.Equals(value, StringComparison.OrdinalIgnoreCase))
{
infoIdKeysIndex = i;
break;
}
}
if (infoIdKeysIndex < 0)
{
throw new ValidationException("目标Excel中没有头为$InfoIDKeys的列");
}
string infoValueKeys = headRow1.GetCell(infoValueKeysIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(infoValueKeys))
{
throw new ValidationException("$InfoValueKeys单元格无值");
}
string infoIdKeys = headRow1.GetCell(infoIdKeysIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(infoIdKeys))
{
throw new ValidationException("$InfoIDKeys单元格无值");
}
var selectKeys = new List<string>();
string[] keys = infoIdKeys.Split(',');
if (keys == null || keys.Length == 0)
{
throw new ValidationException("$InfoIDKeys单元格内的值格式错误");
}
selectKeys.AddRange(keys);
keys = infoValueKeys.Split(',');
if (keys == null || keys.Length == 0)
{
throw new ValidationException("$InfoValueKeys单元格内的值格式错误");
}
selectKeys.AddRange(keys);
var entityIDs = new List<string>();
var selectElements = new OrderedElementSet();
foreach (var key in selectKeys)
{
if (!ontology.Elements.ContainsKey(key))
{
throw new ValidationException("Excel文件的$InfoValueKeys单元格内有非法的本体元素码" + key);
}
if (ontology.Elements[key].Element.IsEnabled != 1)
{
continue;
}
selectElements.Add(ontology.Elements[key]);
}
if (ontology.Elements.ContainsKey("LoginName"))
{
if (!selectElements.Contains(ontology.Elements["LoginName"]))
{
selectElements.Add(ontology.Elements["LoginName"]);
}
}
for (int i = rowIndex; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
if (row != null)
{
var stateCodeStr = row.GetCell(stateCodeIndex).SafeToStringTrim();
if (!string.IsNullOrEmpty(stateCodeStr))
{
int stateCode;
if (!int.TryParse(stateCodeStr, out stateCode))
{
throw new AnycmdException("文件" + fullName + "行中有意外的状态码");
}
if (stateCode >= 200 && stateCode < 300)
{
var cell = row.GetCell(localEntityIdIndex);
entityIDs.Add(cell.SafeToStringTrim());
}
if (stateCode < 200 || stateCode >= 300)
{
IRow resultRow = failedSheet.CreateRow(resultFailedRowIndex);
for (int j = 0; j < headRow0.Cells.Count; j++)
{
var cell = resultRow.CreateCell(j);
var oldCell = row.GetCell(j);
if (oldCell != null)
{
cell.CellStyle = oldCell.CellStyle;
cell.SetCellValue(oldCell.SafeToStringTrim());
}
}
resultFailedRowIndex++;
}
}
sheet.RemoveRow(row);
}
rowIndex++;
}
sheet.RemoveRow(headRow0);
sheet.RemoveRow(headRow1);
sheet.RemoveRow(headRow2);
workbook.SetSheetName(workbook.GetSheetIndex(sheet), "Success");
#region Success 数据
rowIndex = 0;
var headRow = sheet.CreateRow(rowIndex);
sheet.CreateFreezePane(0, 1, 0, 1);
rowIndex++;
ICellStyle helderStyle = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 14;
helderStyle.SetFont(font);
helderStyle.BorderBottom = BorderStyle.Thin;
helderStyle.BorderLeft = BorderStyle.Thin;
helderStyle.BorderRight = BorderStyle.Thin;
helderStyle.BorderTop = BorderStyle.Thin;
helderStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
helderStyle.FillPattern = FillPattern.SolidForeground;
int cellIndex = 0;
foreach (var element in selectElements)
{
ICell cell = headRow.CreateCell(cellIndex, CellType.String);
sheet.SetColumnHidden(cellIndex, hidden: false);
if (element.IsCodeValue)
{
cell.SetCellValue(element.Element.Name + "码");
}
else
{
cell.SetCellValue(element.Element.Name);
}
if (!string.IsNullOrEmpty(element.Element.Description))
{
//添加批注
IDrawing draw = sheet.CreateDrawingPatriarch();
IComment comment = draw.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 8));//里面参数应该是指示批注的位置大小吧
comment.String = new HSSFRichTextString(element.Element.Description);//添加批注内容
comment.Author = AcDomain.NodeHost.Nodes.ThisNode.Name;//添加批注作者
cell.CellComment = comment;//将之前设置的批注给定某个单元格
}
cell.CellStyle = helderStyle;
if (columnWidthDic.ContainsKey(element.Element.Code) && columnWidthDic[element.Element.Code] > 0)
{
sheet.SetColumnWidth(cellIndex, columnWidthDic[element.Element.Code]);
}
else if (element.Element.Width > 0)
{
sheet.SetColumnWidth(cellIndex, element.Element.Width * 256 / 5);
}
if (element.IsCodeValue)
{
cellIndex++;
ICell nameCell = headRow.CreateCell(cellIndex, CellType.String);
sheet.SetColumnHidden(cellIndex, hidden: false);
nameCell.SetCellValue(element.Element.Name + "名");
nameCell.CellStyle = helderStyle;
if (columnWidthDic.ContainsKey(element.Element.Code) && columnWidthDic[element.Element.Code] > 0)
{
sheet.SetColumnWidth(cellIndex, columnWidthDic[element.Element.Code]);
}
else if (element.Element.Width > 0)
{
sheet.SetColumnWidth(cellIndex, element.Element.Width * 256 / 5);
}
}
cellIndex++;
}
if (entityIDs.Count > 0)
{
DataTuple infoValues = ontology.EntityProvider.GetList(ontology, selectElements, entityIDs);
foreach (var record in infoValues.Tuples)
{
var row = sheet.CreateRow(rowIndex);
int j = 0;
for (int col = 0; col < infoValues.Columns.Count; col++)
{
var element = infoValues.Columns[col];
var item = record[col];
ICell cell = row.CreateCell(j, CellType.String);
cell.SetCellValue(item.ToString());
if (element.IsCodeValue)
{
j++;
ICell nameCell = row.CreateCell(j, CellType.String);
nameCell.SetCellValue(element.TranslateValue(item.ToString()));
}
j++;
}
rowIndex++;
}
}
#endregion
var filestream = new MemoryStream(); //内存文件流(应该可以写成普通的文件流)
workbook.Write(filestream); //把文件读到内存流里面
const string contentType = "application/vnd.ms-excel";
fileName = fileName.Substring(0, fileName.Length - 36 - ".xls".Length);
fileName += "_result";
if (Request.Browser.Type.IndexOf("IE", StringComparison.OrdinalIgnoreCase) > -1)
{
fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
}
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
Response.Clear();
return new FileContentResult(filestream.GetBuffer(), contentType);
}
public ActionResult Import(string ontologyCode)
{
if (string.IsNullOrEmpty(ontologyCode))
{
throw new ValidationException("未传入本体码");
}
OntologyDescriptor ontology;
if (!AcDomain.NodeHost.Ontologies.TryGetOntology(ontologyCode, out ontology))
{
throw new ValidationException("非法的本体码" + ontologyCode);
}
string message = "";
if (Request.Files.Count == 0)
{
throw new ValidationException("错误:请上传文件!");
}
HttpPostedFileBase file = Request.Files[0];
if (file == null)
{
throw new ValidationException("错误:请上传文件!");
}
string fileName = file.FileName;
if (string.IsNullOrEmpty(fileName) || file.ContentLength == 0)
{
message = "错误:请上传文件!";
}
else
{
bool isSave = true;
string fileType = fileName.Substring(fileName.LastIndexOf('.')).ToLower();
fileName = fileName.Substring(0, fileName.Length - fileType.Length);
if (file.ContentLength > 1024 * 1024 * 10)
{
message = "错误:文件大小不能超过10M!";
isSave = false;
}
else if (fileType != ".xls")
{
message = "错误:文件上传格式不正确,请上传.xls格式文件!";
isSave = false;
}
if (isSave)
{
string dirPath = Server.MapPath("~/Content/Import/Excel/" + ontology.Ontology.Code + "/" + AcSession.Account.Id);
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
string fullName = Path.Combine(dirPath, fileName + Guid.NewGuid().ToString() + fileType);
file.SaveAs(fullName);
int successSum = 0;
int failSum = 0;
try
{
FileStream fs = System.IO.File.OpenRead(fullName);
IWorkbook workbook = new HSSFWorkbook(fs);//从流内容创建Workbook对象
fs.Close();
ICellStyle failStyle = workbook.CreateCellStyle();
ICellStyle successStyle = workbook.CreateCellStyle();
failStyle.BorderBottom = BorderStyle.Thin;
failStyle.BorderLeft = BorderStyle.Thin;
failStyle.BorderRight = BorderStyle.Thin;
failStyle.BorderTop = BorderStyle.Thin;
failStyle.FillForegroundColor = HSSFColor.LightOrange.Index;
failStyle.FillPattern = FillPattern.SolidForeground;
successStyle.BorderBottom = BorderStyle.Thin;
successStyle.BorderLeft = BorderStyle.Thin;
successStyle.BorderRight = BorderStyle.Thin;
successStyle.BorderTop = BorderStyle.Thin;
successStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
successStyle.FillPattern = FillPattern.SolidForeground;
ISheet sheet = null;
// 工作表sheet的命名规则是：本体码 或 本体名 或 ‘工作表’
var sheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase) {
ontology.Ontology.Code, ontology.Ontology.Name, "工作表","Failed","失败的","Sheet1"
};
foreach (var sheetName in sheetNames)
{
if (sheet != null)
{
break;
}
int dataSheetIndex = workbook.GetSheetIndex(sheetName);
if (dataSheetIndex >= 0)
{
sheet = workbook.GetSheetAt(dataSheetIndex);
}
}
if (sheet == null)
{
System.IO.File.Delete(fullName);
throw new ValidationException("没有名称为'" + ontology.Ontology.Code + "'或'" + ontology.Ontology.Name + "'或'工作表'的sheet");
}
int sheetIndex = workbook.GetSheetIndex(sheet);
workbook.SetSheetName(sheetIndex, ResultSheetName);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
if (i != sheetIndex)
{
workbook.RemoveSheetAt(i);
}
}
if (sheet.LastRowNum == 2)
{
System.IO.File.Delete(fullName);
throw new ValidationException("没有待导入数据");
}
int rowIndex = 0;
IRow headRow1 = sheet.GetRow(rowIndex);
rowIndex++;
IRow headRow2 = sheet.GetRow(rowIndex);
rowIndex++;
IRow headRow3 = sheet.GetRow(rowIndex);
rowIndex++;
#region 提取列索引 这些字段在Excel模板上对应前缀为“$”的列。
int actionCodeIndex = -1,
localEntityIdIndex = -1,
descriptionIndex = -1,
eventReasonPhraseIndex = -1,
eventSourceTypeIndex = -1,
eventStateCodeIndex = -1,
eventSubjectCodeIndex = -1,
infoIdKeysIndex = -1,
infoValueKeysIndex = -1,
isDumbIndex = -1,
timeStampIndex = -1,
ontologyCodeIndex = -1,
reasonPhraseIndex = -1,
requestTypeIndex = -1,
serverTicksIndex = -1,
stateCodeIndex = -1,
versionIndex = -1;
string implicitMessageType = string.Empty,
implicitVerb = string.Empty,
implicitOntology = string.Empty,
implicitVersion = string.Empty,
implicitInfoIdKeys = string.Empty,
implicitInfoValueKeys = string.Empty;
bool implicitIsDumb = false;
for (int i = 0; i < headRow1.Cells.Count; i++)
{
string value = headRow1.GetCell(i).SafeToStringTrim();
string implicitValue = headRow2.GetCell(i).SafeToStringTrim();
if (value != null)
{
value = value.ToLower();
if (value == CommandColHeader.Verb.ToLower())
{
actionCodeIndex = i;
implicitVerb = implicitValue;
}
else if (value == CommandColHeader.LocalEntityId.ToLower())
{
localEntityIdIndex = i;
}
else if (value == CommandColHeader.Description.ToLower())
{
descriptionIndex = i;
}
else if (value == CommandColHeader.EventReasonPhrase.ToLower())
{
eventReasonPhraseIndex = i;
}
else if (value == CommandColHeader.EventSourceType.ToLower())
{
eventSourceTypeIndex = i;
}
else if (value == CommandColHeader.EventStateCode.ToLower())
{
eventStateCodeIndex = i;
}
else if (value == CommandColHeader.EventSubjectCode.ToLower())
{
eventSubjectCodeIndex = i;
}
else if (value == CommandColHeader.InfoIdKeys.ToLower())
{
infoIdKeysIndex = i;
implicitInfoIdKeys = implicitValue;
}
else if (value == CommandColHeader.InfoValueKeys.ToLower())
{
infoValueKeysIndex = i;
implicitInfoValueKeys = implicitValue;
}
else if (value == CommandColHeader.IsDumb.ToLower())
{
isDumbIndex = i;
bool isDumb;
if (!bool.TryParse(implicitValue, out isDumb))
{
System.IO.File.Delete(fullName);
throw new ApplicationException("IsDumb值设置不正确");
}
implicitIsDumb = isDumb;
}
else if (value == CommandColHeader.TimeStamp.ToLower())
{
timeStampIndex = i;
}
else if (value == CommandColHeader.Ontology.ToLower())
{
ontologyCodeIndex = i;
implicitOntology = implicitValue;
}
else if (value == CommandColHeader.ReasonPhrase.ToLower())
{
reasonPhraseIndex = i;
}
else if (value == CommandColHeader.MessageId.ToLower())
{
}
else if (value == CommandColHeader.MessageType.ToLower())
{
requestTypeIndex = i;
implicitMessageType = implicitValue;
}
else if (value == CommandColHeader.ServerTicks.ToLower())
{
serverTicksIndex = i;
}
else if (value == CommandColHeader.StateCode.ToLower())
{
stateCodeIndex = i;
}
else if (value == CommandColHeader.Version.ToLower())
{
versionIndex = i;
implicitVersion = implicitValue;
}
}
}
#endregion
int responsedSum = 0;
var commands = new Dictionary<int, Message>();
#region 检测Excel中的每一行是否合法
for (int i = rowIndex; i <= sheet.LastRowNum; i++)
{
// 检测合法性的进度，未展示进度条
var percent = (decimal)(((decimal)100 * i) / sheet.LastRowNum);
var row = sheet.GetRow(i);
if (row != null)
{
string infoIdKeys = row.GetCell(infoIdKeysIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(infoIdKeys))
{
infoIdKeys = implicitInfoIdKeys;
}
string infoValueKeys = row.GetCell(infoValueKeysIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(infoValueKeys))
{
infoValueKeys = implicitInfoValueKeys;
}
var infoIdCodes = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
var infoValueCodes = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
if (infoIdKeys != null)
{
foreach (var item in infoIdKeys.Split(','))
{
infoIdCodes.Add(item);
}
}
if (infoValueKeys != null)
{
foreach (var item in infoValueKeys.Split(','))
{
infoValueCodes.Add(item);
}
}
var infoId = new List<KeyValue>();
var infoValue = new List<KeyValue>();
for (int j = 0; j < headRow1.Cells.Count; j++)
{
var elementCode = headRow1.GetCell(j).SafeToStringTrim();
if (!string.IsNullOrEmpty(elementCode) && elementCode[0] != '$')
{
var value = row.GetCell(j).SafeToStringTrim();
if (infoIdCodes.Contains(elementCode))
{
infoId.Add(new KeyValue(elementCode, value));
}
if (infoValueCodes.Contains(elementCode))
{
infoValue.Add(new KeyValue(elementCode, value));
}
}
}
if (infoId.Count == 0 || infoId.All(a => string.IsNullOrEmpty(a.Value)))
{
continue;
}
bool isDumb;
string isDumbValue = row.GetCell(isDumbIndex).SafeToStringTrim();
if (!string.IsNullOrEmpty(isDumbValue))
{
if (!bool.TryParse(isDumbValue, out isDumb))
{
throw new ApplicationException("IsDumb值设置不正确");
}
}
else
{
isDumb = implicitIsDumb;
}
string actionCode = row.GetCell(actionCodeIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(actionCode))
{
actionCode = implicitVerb;
}
ontologyCode = row.GetCell(ontologyCodeIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(ontologyCode))
{
ontologyCode = implicitOntology;
}
var version = row.GetCell(versionIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(version))
{
version = implicitVersion;
}
var requestType = row.GetCell(requestTypeIndex).SafeToStringTrim();
if (string.IsNullOrEmpty(requestType))
{
requestType = implicitMessageType;
}
int eventStateCode = 0;
if (!string.IsNullOrEmpty(row.GetCell(eventStateCodeIndex).SafeToStringTrim()))
{
if (!int.TryParse(row.GetCell(eventStateCodeIndex).SafeToStringTrim(), out eventStateCode))
{
throw new ApplicationException("eventStateCode值设置错误");
}
}
if (!string.IsNullOrEmpty(row.GetCell(timeStampIndex).SafeToStringTrim()))
{
long timeStamp = 0;
if (!long.TryParse(row.GetCell(timeStampIndex).SafeToStringTrim(), out timeStamp))
{
throw new ApplicationException("timeStamp值设置错误");
}
}
responsedSum++;
var ticks = DateTime.UtcNow.Ticks;
var command = new Message()
{
IsDumb = isDumb,
Verb = actionCode,
MessageId = Guid.NewGuid().ToString(),
Ontology = ontologyCode,
Version = version,
MessageType = requestType,
TimeStamp = DateTime.UtcNow.Ticks,
Body = new BodyData(infoId.ToArray(), infoValue.ToArray())
{
Event = new EventData
{
ReasonPhrase = row.GetCell(eventReasonPhraseIndex).SafeToStringTrim(),
SourceType = row.GetCell(eventSourceTypeIndex).SafeToStringTrim(),
Status = eventStateCode,
Subject = row.GetCell(eventSubjectCodeIndex).SafeToStringTrim()
}
}
};
var credential = new CredentialData
{
ClientType = ClientType.Node.ToName(),
CredentialType = CredentialType.Token.ToName(),
ClientId = AcDomain.NodeHost.Nodes.ThisNode.Node.Id.ToString(),
Ticks = ticks,
UserName = AcSession.Account.Id.ToString()
};
command.Credential = credential;
commands.Add(i, command);
}
}
if (responsedSum == 0)
{
throw new ValidationException("没有可导入行");
}
else
{
foreach (var command in commands)
{
// 检测合法性的进度，未展示进度条
var percent = (decimal)(((decimal)100 * command.Key) / commands.Count);
var result = AnyMessage.Create(HecpRequest.Create(AcDomain, command.Value), AcDomain.NodeHost.Nodes.ThisNode).Response();
if (result.Body.Event.Status < 200)
{
throw new ValidationException(string.Format("{0} {1} {2}", result.Body.Event.Status, result.Body.Event.ReasonPhrase, result.Body.Event.Description));
}
var row = sheet.GetRow(command.Key);
var stateCodeCell = row.CreateCell(stateCodeIndex);
var reasonPhraseCell = row.CreateCell(reasonPhraseIndex);
var descriptionCell = row.CreateCell(descriptionIndex);
var serverTicksCell = row.CreateCell(serverTicksIndex);
var localEntityIdCell = row.CreateCell(localEntityIdIndex);
if (result.Body.Event.Status < 200 || result.Body.Event.Status >= 300)
{
failSum++;
stateCodeCell.CellStyle = failStyle;
reasonPhraseCell.CellStyle = failStyle;
descriptionCell.CellStyle = failStyle;
}
else
{
stateCodeCell.CellStyle = successStyle;
reasonPhraseCell.CellStyle = successStyle;
descriptionCell.CellStyle = successStyle;
successSum++;
}
stateCodeCell.SetCellValue(result.Body.Event.Status);
reasonPhraseCell.SetCellValue(result.Body.Event.ReasonPhrase);
descriptionCell.SetCellValue(result.Body.Event.Description);
serverTicksCell.SetCellValue(DateTime.Now.ToString());
if (result.Body.InfoValue != null)
{
var idItem = result.Body.InfoValue.FirstOrDefault(a => a.Key.Equals("Id", StringComparison.OrdinalIgnoreCase));
if (idItem != null)
{
localEntityIdCell.SetCellValue(idItem.Value);
}
}
}
var newFile = new FileStream(fullName, FileMode.Create);
workbook.Write(newFile);
newFile.Close();
}
#endregion
}
catch (OfficeXmlFileException)
{
System.IO.File.Delete(fullName);
throw new ValidationException("暂不支持Office2007及以上版本的Excel文件");
}
}
}
TempData["Message"] = message;
return this.RedirectToAction("Import", new { ontologyCode });
}
