public static void SavePoExcel(List<po> poList, List<List<PoItemContentAndState>> poitemsListList)
{
if (poList.Count() != poitemsListList.Count())
{
MessageBox.Show("Internal Error. Please send the log file to the Author");
Logger.Error(poList.Count() + "," + poitemsListList.Count());
return;
}
FileStream file;
try
{
file = new FileStream(@"PoTemplate.dll", FileMode.Open, FileAccess.Read);
}
catch (Exception)
{
MessageBox.Show("Please check the PoTemplate.dll.");
return;
}
hssfworkbook = new HSSFWorkbook(file);
WriteDsiInfo();
for (int i = 0; i < poList.Count(); i++)
{
ISheet sheet = hssfworkbook.CloneSheet(0);
FillThePoSheet(sheet, poList[i], poitemsListList[i]);
hssfworkbook.SetSheetName(3 + i, "PO" + i.ToString());
}
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
WriteToFile();
}
public FileResult Generate(SearchEntity searchEntity)
{
int num;
var arrayData = this.GetArrayData(searchEntity, out num);
var str = "根基对账单";
using (var stream = System.IO.File.OpenRead(base.Server.MapPath(string.Format("~/Excel/Market/{0}.xls", str))))
{
var xlBook = new HSSFWorkbook(stream);
this.GenerateM1(arrayData, xlBook);
this.GenerateM2(arrayData, xlBook);
this.GenerateM3(arrayData, xlBook);
if (xlBook.NumberOfSheets > 3)
{
xlBook.RemoveSheetAt(0);
xlBook.RemoveSheetAt(0);
xlBook.RemoveSheetAt(0);
xlBook.FirstVisibleTab = 0;
}
var str3 = string.Format("{0}{1:yyMMddHHmmss}.xls", str, DateTime.Now);
var path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3);
if (System.IO.File.Exists(path))
{
str3 = str3 + Guid.NewGuid().ToString() + ".xls";
path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3);
}
using (var stream2 = System.IO.File.OpenWrite(path))
{
xlBook.Write(stream2);
}
return this.File(path, "application/vnd.ms-excel", str3);
}
}
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 void TestActiveSheetAfterDelete_bug40414()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet0 = wb.CreateSheet("Sheet0");
NPOI.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1");
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2");
NPOI.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3");
NPOI.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4");
// Confirm default activation/selection
ConfirmActiveSelected(sheet0, true);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, false);
ConfirmActiveSelected(sheet4, false);
wb.SetActiveSheet(3);
wb.SetSelectedTab(3);
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet3, true);
ConfirmActiveSelected(sheet4, false);
wb.RemoveSheetAt(3);
// after removing the only active/selected sheet, another should be active/selected in its place
if (!sheet4.IsSelected)
{
throw new AssertionException("identified bug 40414 a");
}
if (!sheet4.IsActive)
{
throw new AssertionException("identified bug 40414 b");
}
ConfirmActiveSelected(sheet0, false);
ConfirmActiveSelected(sheet1, false);
ConfirmActiveSelected(sheet2, false);
ConfirmActiveSelected(sheet4, true);
sheet3 = sheet4; // re-align local vars in this Test case
// Some more cases of removing sheets
// Starting with a multiple selection, and different active sheet
wb.SetSelectedTabs(new int[] { 1, 3, });
wb.SetActiveSheet(2);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
ConfirmActiveSelected(sheet3, false, true);
// removing a sheet that is not active, and not the only selected sheet
wb.RemoveSheetAt(3);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet1, false, true);
ConfirmActiveSelected(sheet2, true, false);
// removing the only selected sheet
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, false, false);
ConfirmActiveSelected(sheet2, true, true);
// The last remaining sheet should always be active+selected
wb.RemoveSheetAt(1);
ConfirmActiveSelected(sheet0, true, true);
}
public void Bug56325()
{
HSSFWorkbook wb;
FileInfo file = HSSFTestDataSamples.GetSampleFile("56325.xls");
Stream stream = new FileStream(file.FullName, FileMode.Open, FileAccess.ReadWrite);
try
{
POIFSFileSystem fs = new POIFSFileSystem(stream);
wb = new HSSFWorkbook(fs);
}
finally
{
stream.Close();
}
Assert.AreEqual(3, wb.NumberOfSheets);
wb.RemoveSheetAt(0);
Assert.AreEqual(2, wb.NumberOfSheets);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(2, wb.NumberOfSheets);
wb.RemoveSheetAt(0);
Assert.AreEqual(1, wb.NumberOfSheets);
wb.RemoveSheetAt(0);
Assert.AreEqual(0, wb.NumberOfSheets);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(0, wb.NumberOfSheets);
}
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 });
}
