/// <summary> /// Fill data sheet of parameters /// </summary> public void FillParamValues(List <PrintPropertiesValue> printPropertiyValues) { SharedStringTablePart shareStringPart; if (spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0) { shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First(); } else { shareStringPart = spreadSheet.WorkbookPart.AddNewPart <SharedStringTablePart>(); } foreach (Row rowParam in worksheetPartParams.Worksheet.GetFirstChild <SheetData>().Elements <Row>()) { Cell refCellA = rowParam.Elements <Cell>().Where(c => c.CellReference.Value == "A" + rowParam.RowIndex).FirstOrDefault(); if (refCellA == null) { break; } else { Cell refCellB = CheckEmptyCell(rowParam, refCellA, "B"); PrintPropertiesValue printPropertiesValue = printPropertiyValues.FirstOrDefault(x => (x.TypeProperty == GetCellValue(refCellA) & (x.PropertyCode == GetCellValue(refCellB)))); string PropertyValue = printPropertiesValue == null ? string.Empty : printPropertiesValue.Value; if (!string.IsNullOrEmpty(PropertyValue)) { Cell refCellC = CheckEmptyCell(rowParam, refCellB, "C"); Cell refCellD = CheckEmptyCell(rowParam, refCellC, "D"); if (GetCellValue(refCellB) == "FactoryNumber") { if (!string.IsNullOrEmpty(PropertyValue)) { ProcessFactoryNumber(PropertyValue); } } int index = InsertSharedStringItem(PropertyValue, shareStringPart); refCellD.CellValue = new CellValue(index.ToString()); refCellD.DataType = new EnumValue <CellValues>(CellValues.SharedString); } } } this.ProcessHiddenDimensionsBackground(); worksheetPartParams.Worksheet.Save(); //defective converting? RecalcRefCellValues(); workbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; workbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; workbookPart.Workbook.Save(); spreadSheet.Close(); }
public async Task <System.Net.Http.HttpResponseMessage> GenerateExcelPreview(int materialLotID) { Contract.Ensures(Contract.Result <Task <HttpResponseMessage> >() != null); // get print property entities var printPropertyEntities = await this.GetNamedEntities(cPrintPropertiesEntityName); if (printPropertyEntities == null) { return(this.Request.CreateResponse(HttpStatusCode.NotFound)); } Type printPropertyType = printPropertyEntities.GetType().GetGenericArguments()[0]; //Type relevantType = null; //DbContext.TryGetRelevantType(cPrintPropertiesEntityName, out relevantType); var expressionPP = Expression <DynamicEntity, int>(printPropertyType.GetProperty("MaterialLotID"), materialLotID); var materialLotPPEntities = Where(printPropertyEntities, expressionPP, printPropertyType); // get print file entities var printFileEntities = await this.GetNamedEntities(cPrintFileEntityName); if (printFileEntities == null) { return(this.Request.CreateResponse(HttpStatusCode.NotFound)); } //if (((IQueryable<DynamicEntity>)printFileEntities).Count() == 0) // return this.Request.CreateResponse(HttpStatusCode.NotFound); Type printFileType = printFileEntities.GetType().GetGenericArguments()[0]; var expressionPF = Expression <DynamicEntity, int>(printFileType.GetProperty("MaterialLotID"), materialLotID); var materialLotPFEntities = Where(printFileEntities, expressionPF, printFileType); if (((IQueryable <DynamicEntity>)materialLotPFEntities).Count() == 0) { return(this.Request.CreateResponse(HttpStatusCode.NotFound)); } // get the media resource stream from the entity DynamicEntity printFileObj = ((IQueryable <DynamicEntity>)materialLotPFEntities).First(); var dataPropInfo = printFileObj.GetType().GetProperty("Data"); if (!(dataPropInfo.GetValue(printFileObj) is byte[] templateBytes)) { return(this.Request.CreateResponse(HttpStatusCode.NotFound)); } string fileSaveLocation = Path.GetTempFileName(); File.WriteAllBytes(fileSaveLocation, templateBytes); #region Excel Work List <PrintPropertiesValue> printPropertyValues = new List <PrintPropertiesValue>(); foreach (var item in materialLotPPEntities) { var typeProperty = item.GetType().GetProperty("TypeProperty"); var codeProperty = item.GetType().GetProperty("PropertyCode"); var valueProperty = item.GetType().GetProperty("Value"); string typePropertyValue = typeProperty.GetValue(item) as string; string codePropertyValue = codeProperty.GetValue(item) as string; string valuePropertyValue = valueProperty.GetValue(item) as string; PrintPropertiesValue printPropertiesValue = new PrintPropertiesValue { TypeProperty = typePropertyValue, PropertyCode = codePropertyValue, Value = valuePropertyValue }; printPropertyValues.Add(printPropertiesValue); } LabelTemplate labelTemplate = new LabelTemplate(fileSaveLocation); labelTemplate.FillParamValues(printPropertyValues); #endregion #region PNG Generation var namePropInfo = printFileObj.GetType().GetProperty("Name"); var nameValue = namePropInfo.GetValue(printFileObj) as string; string outputFileName = Path.Combine(Path.GetTempPath(), nameValue + ".png"); try { //ConvertToPngAspose(fileSaveLocation, outputFileName); xlsConverter.Program.ConvertNoRotate(fileSaveLocation, outputFileName); } catch (Exception exception) { DynamicLogger.Instance.WriteLoggerLogError("GenerateExcelPreview (materialLotID=" + materialLotID + ")", exception); return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, exception)); } if (File.Exists(fileSaveLocation)) { File.Delete(fileSaveLocation); } #endregion var bytes = File.ReadAllBytes(outputFileName); File.Delete(outputFileName); var stream = new MemoryStream(bytes); if (stream == null) { return(this.Request.CreateResponse(HttpStatusCode.NotFound)); } var mediaNameStr = Path.GetFileName(outputFileName); var mediaTypeStr = "image/png"; var mediaType = new MediaTypeHeaderValue(mediaTypeStr); // get the range and stream media type var range = this.Request.Headers.Range; HttpResponseMessage response; if (range == null) { // if the range header is present but null, then the header value must be invalid if (this.Request.Headers.Contains("Range")) { return(this.Request.CreateErrorResponse(HttpStatusCode.RequestedRangeNotSatisfiable, "GenerateExcelPreview")); } // if no range was requested, return the entire stream response = this.Request.CreateResponse(HttpStatusCode.OK); response.Headers.AcceptRanges.Add("bytes"); response.Content = new StreamContent(stream); response.Content.Headers.ContentType = mediaType; response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); mediaNameStr = System.Web.HttpUtility.UrlEncode(mediaNameStr); response.Content.Headers.ContentDisposition.FileName = mediaNameStr; return(response); } var partialStream = EnsureStreamCanSeek(stream); response = this.Request.CreateResponse(HttpStatusCode.PartialContent); response.Headers.AcceptRanges.Add("bytes"); try { // return the requested range(s) response.Content = new ByteRangeStreamContent(partialStream, range, mediaType); } catch (InvalidByteRangeException exception) { DynamicLogger.Instance.WriteLoggerLogError("GenerateExcelPreview (materialLotID=" + materialLotID + ")", exception); response.Dispose(); return(Request.CreateErrorResponse(exception)); } // change status code if the entire stream was requested if (response.Content.Headers.ContentLength.Value == partialStream.Length) { response.StatusCode = HttpStatusCode.OK; } return(response); }