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 }); }