/** * Compute width of a single cell * * @param cell the cell whose width is to be calculated * @param defaultCharWidth the width of a single character * @param formatter formatter used to prepare the text to be measured * @param useMergedCells whether to use merged cells * @return the width in pixels or -1 if cell is empty */ public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells) { ISheet sheet = cell.Sheet; IWorkbook wb = sheet.Workbook; IRow row = cell.Row; int column = cell.ColumnIndex; int colspan = 1; for (int i = 0; i < sheet.NumMergedRegions; i++) { CellRangeAddress region = sheet.GetMergedRegion(i); if (ContainsCell(region, row.RowNum, column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and move on to the next. return(-1); } cell = row.GetCell(region.FirstColumn); colspan = 1 + region.LastColumn - region.FirstColumn; } } ICellStyle style = cell.CellStyle; CellType cellType = cell.CellType; IFont defaultFont = wb.GetFontAt((short)0); Font windowsFont = IFont2Font(defaultFont); // for formula cells we compute the cell width for the cached formula result if (cellType == CellType.Formula) { cellType = cell.CachedFormulaResultType; } IFont font = wb.GetFontAt(style.FontIndex); double width = -1; using (Bitmap bmp = new Bitmap(1, 1)) using (Graphics g = Graphics.FromImage(bmp)) { if (cellType == CellType.String) { IRichTextString rt = cell.RichStringCellValue; String[] lines = rt.String.Split("\n".ToCharArray()); for (int i = 0; i < lines.Length; i++) { String txt = lines[i] + defaultChar; //AttributedString str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.length()); windowsFont = IFont2Font(font); if (rt.NumFormattingRuns > 0) { // TODO: support rich text fragments } width = GetCellWidth(defaultCharWidth, colspan, style, width, txt, g, windowsFont, cell); } } else { String sval = null; if (cellType == CellType.Numeric) { // Try to get it formatted to look the same as excel try { sval = formatter.FormatCellValue(cell, dummyEvaluator); } catch (Exception) { sval = cell.NumericCellValue.ToString(); } } else if (cellType == CellType.Boolean) { sval = cell.BooleanCellValue.ToString().ToUpper(); } if (sval != null) { String txt = sval + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.length()); windowsFont = IFont2Font(font); width = GetCellWidth(defaultCharWidth, colspan, style, width, txt, g, windowsFont, cell); } } } return(width); }
protected bool IsTextEmpty(ICell cell) { string value; switch (cell.CellType) { case CellType.String: // XXX: enrich value = cell.RichStringCellValue.String; break; case CellType.Formula: switch (cell.CachedFormulaResultType) { case CellType.String: IRichTextString str = cell.RichStringCellValue as IRichTextString; if (str == null || str.Length <= 0) { return(false); } value = str.ToString(); break; case CellType.Numeric: ICellStyle style = cell.CellStyle as ICellStyle; if (style == null) { return(false); } value = (_formatter.FormatRawCellContents(cell.NumericCellValue, style.DataFormat, style.GetDataFormatString())); break; case CellType.Boolean: value = cell.BooleanCellValue.ToString(); break; case CellType.Error: value = ErrorEval.GetText(cell.ErrorCellValue); break; default: value = string.Empty; break; } break; case CellType.Blank: value = string.Empty; break; case CellType.Numeric: value = _formatter.FormatCellValue(cell); break; case CellType.Boolean: value = cell.BooleanCellValue.ToString(); break; case CellType.Error: value = ErrorEval.GetText(cell.ErrorCellValue); break; default: return(true); } return(string.IsNullOrEmpty(value)); }
private async Task ResumeAfterPrompt(IDialogContext context, IAwaitable <IEnumerable <Attachment> > result) { context.UserData.SetValue(ContextConstants.UserId, context.Activity.From.Id); try { //Connecting to storage var storageAccount = CloudStorageAccount.Parse(ConfigurationManager.AppSettings["AzureWebJobsStorage"]); var tableClient = storageAccount.CreateCloudTableClient(); CloudTable eventTable = tableClient.GetTableReference("Event"); eventTable.CreateIfNotExists(); //Get the Attachment var message = await result; var msg = context.MakeMessage(); msg.Type = ActivityTypes.Typing; await context.PostAsync(msg); var excelName = ""; string destinationContainer = "surveytemplates"; CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient(); var blobContainer = blobClient.GetContainerReference(destinationContainer); blobContainer.CreateIfNotExists(); String newFileName = ""; //Receiving the attachment foreach (Attachment a in message) { try { excelName = a.Name; using (HttpClient httpClient = new HttpClient()) { var responseMessage = await httpClient.GetAsync(a.ContentUrl); var contentLenghtBytes = responseMessage.Content.Headers.ContentLength; var fileByte = await httpClient.GetByteArrayAsync(a.ContentUrl); //Set file name newFileName = context.UserData.GetValue <string>(ContextConstants.UserId) + "_" + DateTime.Now.ToString("MMddyyyy-hhmmss") + "_" + excelName; string sourceUrl = a.ContentUrl; // Set the permissions so the blobs are public. //ON HOLD BlobContainerPermissions permissions = new BlobContainerPermissions { PublicAccess = BlobContainerPublicAccessType.Blob }; blobContainer.SetPermissions(permissions); var newBlockBlob = blobContainer.GetBlockBlobReference(newFileName); try { using (var memoryStream = new System.IO.MemoryStream(fileByte)) { newBlockBlob.UploadFromStream(memoryStream); newBlockBlob.Properties.ContentType = a.ContentType; } newBlockBlob.SetProperties(); } catch (Exception ex) { await context.PostAsync("Something went wrong with uploading, please type \'/ce\' to try again."); context.Done(this); } } } catch (Exception e) { await context.PostAsync("Something went wrong with uploading, please type \'/ce\' to try again."); context.Done(this); } } try { //Opening the Excel File CloudBlockBlob blob = blobContainer.GetBlockBlobReference(newFileName); //Check for unique entity DateTime thisDay = DateTime.Today; String today = FormatDate(thisDay.ToString("d")); var eventCode = GenerateCode(); //After checking that there are no duplicates in generated codes EventEntity eventEntity = new EventEntity(context.UserData.GetValue <string>(ContextConstants.UserId), eventCode); using (HttpClient httpClient = new HttpClient()) { var fileByte = await httpClient.GetByteArrayAsync(blob.Uri.ToString()); try { XSSFWorkbook wb; XSSFSheet sh; using (var memoryStream = new MemoryStream(fileByte)) { wb = new XSSFWorkbook(memoryStream); } sh = (XSSFSheet)wb.GetSheetAt(0); //Check event start date and event name if (sh.GetRow(2).GetCell(1) != null && sh.GetRow(2).GetCell(1).ToString() != "" && sh.GetRow(1).GetCell(1) != null && sh.GetRow(1).GetCell(1).ToString() != "") { //DateTime dateTime; DataFormatter dataFormatter = new DataFormatter(); var date = dataFormatter.FormatCellValue(sh.GetRow(2).GetCell(1)); int totalDays = 1; var error = false; DateTime sDate; DateTime eDate; //Try to parse date in the specified format if (ParseDate(date, thisDay)) { String[] split = date.Split(new Char[] { '/', '-' }); date = split[0].ToString() + '/' + split[1].ToString() + '/' + split[2]; eventEntity.EventStartDate = date; DateTime.TryParseExact(dataFormatter.FormatCellValue(sh.GetRow(2).GetCell(1)), new string[] { "dd/MM/yyyy", "dd-MM-yyyy" }, CultureInfo.InvariantCulture, DateTimeStyles.None, out sDate); //Check Event End Date if (sh.GetRow(3).GetCell(1) != null && sh.GetRow(3).GetCell(1).ToString() != "") { date = dataFormatter.FormatCellValue(sh.GetRow(3).GetCell(1)); if (ParseDate(date, sDate)) { split = date.Split(new Char[] { '/', '-' }); date = split[0].ToString() + '/' + split[1].ToString() + '/' + split[2]; eventEntity.EventEndDate = date; DateTime.TryParseExact(dataFormatter.FormatCellValue(sh.GetRow(3).GetCell(1)), new string[] { "dd/MM/yyyy", "dd-MM-yyyy" }, CultureInfo.InvariantCulture, DateTimeStyles.None, out eDate); totalDays = (eDate - sDate).Days + 1; if (totalDays > 31) { error = true; await context.PostAsync("Sorry, we currently do not support events that have more than 31 days."); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } else { error = true; await context.PostAsync("Please check your excel file and input an end date in the format specified(dd-mm-yyyy or dd/mm/yyyy) and not before today's date!"); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } else { eventEntity.EventEndDate = eventEntity.EventStartDate; } //End of Checking Event's End Date //Check Attendance Code 1 Time var startTime = dataFormatter.FormatCellValue(sh.GetRow(4).GetCell(1)); var endTime = dataFormatter.FormatCellValue(sh.GetRow(5).GetCell(1)); var code1 = new List <string>(); var code2 = new List <string>(); if (sh.GetRow(4).GetCell(1) != null && sh.GetRow(4).GetCell(1).ToString() != "" && sh.GetRow(5).GetCell(1) != null && sh.GetRow(5).GetCell(1).ToString() != "") { if (ParseTime(startTime, endTime)) { eventEntity.AttendanceCode1StartTime = startTime; eventEntity.AttendanceCode1EndTime = endTime; for (int i = 0; i < totalDays; i++) { //Generate unique attendanceCode1 code1.Add(GenerateCode()); } } else { error = true; await context.PostAsync("Please check your excel file and valid Attendance Code 1 Start/End Time in the format specified(2359). The end time should not be before the start time."); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } else if (sh.GetRow(4).GetCell(1).ToString() == "" && sh.GetRow(5).GetCell(1).ToString() == "") { eventEntity.AttendanceCode1StartTime = ""; eventEntity.AttendanceCode1EndTime = ""; } else { error = true; await context.PostAsync("Please check your excel file and valid Attendance Code 1 Start/End Time in the format specified(2359). The end time should not be before the start time."); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } //End of Attendance Code 1 Time //Check Attendance Code 2 Time startTime = dataFormatter.FormatCellValue(sh.GetRow(6).GetCell(1)); endTime = dataFormatter.FormatCellValue(sh.GetRow(7).GetCell(1)); if (sh.GetRow(6).GetCell(1) != null && sh.GetRow(6).GetCell(1).ToString() != "" && sh.GetRow(7).GetCell(1) != null && sh.GetRow(7).GetCell(1).ToString() != "") { if (ParseTime(startTime, endTime)) { eventEntity.AttendanceCode2StartTime = startTime; eventEntity.AttendanceCode2EndTime = endTime; for (int i = 0; i < totalDays; i++) { //Generate unique attendanceCode2 code2.Add(GenerateCode()); } } else { error = true; await context.PostAsync("Please check your excel file and valid Attendance Code 2 Start/End Time in the format specified(2359). The end time should not be before the start time."); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } else { eventEntity.AttendanceCode2StartTime = ""; eventEntity.AttendanceCode2EndTime = ""; } //End of Attendance Code 2 Time //Check Survey End Date if (sh.GetRow(8).GetCell(1) != null && sh.GetRow(8).GetCell(1).ToString() != "") { date = dataFormatter.FormatCellValue(sh.GetRow(8).GetCell(1)); if (ParseDate(date, sDate)) { split = date.Split(new Char[] { '/', '-' }); date = split[0] + '/' + split[1] + '/' + split[2]; eventEntity.SurveyEndDate = date; } else//Input date is not in the specified format { error = true; await context.PostAsync("Please check your excel file and input a Survey End Date in the format specified(dd-mm-yyyy or dd/mm/yyyy) and not before today's date!"); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } else { if (eventEntity.EventEndDate != "") { eventEntity.SurveyEndDate = eventEntity.EventEndDate; } else { eventEntity.SurveyEndDate = eventEntity.EventStartDate; } } //End of checking Survey End Date //Check Survey End Time endTime = dataFormatter.FormatCellValue(sh.GetRow(9).GetCell(1)); if (sh.GetRow(9).GetCell(1) != null && sh.GetRow(9).GetCell(1).ToString() != "") { if (ParseTime("0000", endTime)) { eventEntity.SurveyEndTime = endTime; } else { error = true; await context.PostAsync("Please check your excel file and your Survey End Time in the format specified(2359)."); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } else { eventEntity.SurveyEndTime = "2359"; } //End of Attendance Code 2 Time //Start of Anonymity if (sh.GetRow(11).GetCell(1) != null && sh.GetRow(11).GetCell(1).ToString() != "" && (sh.GetRow(11).GetCell(1).ToString() == "Y" || sh.GetRow(11).GetCell(1).ToString() == "N" || sh.GetRow(11).GetCell(1).ToString() == "Yes" || sh.GetRow(11).GetCell(1).ToString() == "No")) { eventEntity.Anonymous = (sh.GetRow(11).GetCell(1).ToString()); } else { error = true; await context.PostAsync("Please check your excel file and input either Y or N for Anonymity."); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } //End of Anonymity //Start of Email if (sh.GetRow(12).GetCell(1) != null && sh.GetRow(12).GetCell(1).ToString() != "") { try { System.Net.Mail.MailAddress mail = new System.Net.Mail.MailAddress(sh.GetRow(12).GetCell(1).ToString()); eventEntity.Email = (sh.GetRow(12).GetCell(1).ToString()); } catch (FormatException) { error = true; await context.PostAsync("Please check your excel file and input a valid Email address or remove the field."); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } else { eventEntity.Email = ""; } //End of Email //If the file got no error parsing all the values if (!error) { //Setting eventEntity variables eventEntity.Description = dataFormatter.FormatCellValue(sh.GetRow(10).GetCell(1)); eventEntity.EventName = (sh.GetRow(1).GetCell(1).ToString()); if (sh.GetRow(13).GetCell(1).ToString() != "") { eventEntity.Password = Sha256(sh.GetRow(13).GetCell(1).ToString()); } else { eventEntity.Password = ""; } var survey = new List <QuestionEntity>(); QuestionEntity question = new QuestionEntity(); eventEntity.SurveyCode = GenerateCode(); //retrieving the questions for (int i = 35; i <= sh.LastRowNum; i++) { var answerList = new List <string>(); if (sh.GetRow(i).GetCell(0) != null && sh.GetRow(i).GetCell(0).ToString() != "") { int x = 1; question = new QuestionEntity(eventEntity.SurveyCode, (i - 34).ToString()); question.QuestionText = (sh.GetRow(i).GetCell(0).ToString()); //Retrieving the answer list while (sh.GetRow(i).GetCell(x) != null && sh.GetRow(i).GetCell(x).ToString() != "") { answerList.Add(sh.GetRow(i).GetCell(x).ToString()); x++; } } else { break; } question.AnswerList = JsonConvert.SerializeObject(answerList); survey.Add(question); } //No question is found in excel file if (survey.Count == 0) { await context.PostAsync("Your excel file does not contain any question, please check again! (Read the guidelines in the provided template)"); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } else//Questions were found in excel file, process inserting of event { eventEntity.Survey = JsonConvert.SerializeObject(survey); for (int i = 0; i < totalDays; i++) { if (code1.Count != 0) { eventEntity.AttendanceCode1 = code1[i]; } if (code2.Count != 0) { eventEntity.AttendanceCode2 = code2[i]; } string code = ""; while (true) { code = Guid.NewGuid().ToString().Substring(0, 8); TableOperation retrieveOperation = TableOperation.Retrieve <EventEntity>(context.UserData.GetValue <string>(ContextConstants.UserId), code); TableResult retrievedResult = eventTable.Execute(retrieveOperation); if (retrievedResult.Result == null) { break; } } eventEntity.RowKey = code; eventEntity.Day = (i + 1).ToString(); TableOperation insertOperation1 = TableOperation.InsertOrMerge(eventEntity); eventTable.Execute(insertOperation1); } await context.PostAsync(msg); string eventMsg = "'" + eventEntity.EventName + "' has been created, with a total of " + survey.Count + " questions. \n\n"; eventMsg += "Date: " + eventEntity.EventStartDate + " to " + eventEntity.EventEndDate + "\n\n"; eventMsg += "Attendance Code 1: \n\n"; for (int i = 0; i < code1.Count; i++) { eventMsg += "Day " + (i + 1) + ": " + (code1[i] == null ? "-" : code1[i]) + "\n\n"; } eventMsg += "\n\n" + "Attendance Code 2: \n\n"; for (int i = 0; i < code2.Count; i++) { eventMsg += "Day " + (i + 1) + ": " + (code2[i] == null ? "-" : code2[i]) + "\n\n"; } eventMsg += "\n\n" + "Survey Code: " + eventEntity.SurveyCode + "\n\n\n\n" + "Type /Event to manage your event. Thank you and have a great day ahead!"; await context.PostAsync(eventMsg); bool a = SharedFunction.EmailEvent(eventEntity, context.UserData.GetValue <string>(ContextConstants.Name), survey, code1, code2); context.Done(this); } } } else//Input date is not in the specified format { await context.PostAsync("Please check your excel file and input a date in the format specified(dd-mm-yyyy or dd/mm/yyyy) and not before today's date!"); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } else { await context.PostAsync("Please check your excel file to see if you have inputted an Event Name and Event Start Date!"); await context.PostAsync(msg); await context.PostAsync("Talk to me again if you require my assistance."); context.Done(this); } } catch (Exception ex) { await context.PostAsync("Sorry there is an error trying to open your file, please type \'/ce\' to try again."); context.Done(this); } } } catch (Exception e) { await context.PostAsync("Sorry there is an error trying to open your file, please type \'/ce\' to try again."); context.Done(this); } } catch (TooManyAttemptsException e) { } }
public List <string> GetListDataFromFile(string pathFile, string fileName, string importer) { FileStream fs = new FileStream(pathFile, FileMode.Open); // Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(fs); // Get first/desired sheet from the workbook ISheet sheet = workbook.GetSheetAt(0); // Prepare variable List <string> result = new List <string>(); DataFormatter formatter = new DataFormatter(); JObject jsonObject = new JObject(); JObject jsonObjectTemp = null; JObject finalObj = null; List <CellRangeAddress> listMergedRegion = new List <CellRangeAddress>(); int maxMergedRegionRowLength = GetMaxMergedRegionRowLength(sheet, listMergedRegion); ICell parentCell = null; string parentStringCellValue = string.Empty; string currStringCellValue = string.Empty; int currRowIndex = 0; int currColIndex = 0; bool isReadHeader = true; string jsonBluePrint = string.Empty; // Use to set value by key when read data after read header SortMergedRegions(listMergedRegion, true); SortMergedRegions(listMergedRegion, false); AddMergeRegionToJSON(sheet, maxMergedRegionRowLength, listMergedRegion, jsonObject, 0, string.Empty); // Start reading int lastRowNum = sheet.LastRowNum; for (int i = 0; i <= lastRowNum; i++) { IRow row = sheet.GetRow(i); if (row == null) { continue; } // For each row, iterate through all the columns List <ICell> cells = row.Cells; if (!string.Empty.Equals(jsonBluePrint)) { jsonObjectTemp = JObject.Parse(jsonBluePrint); } foreach (ICell cell in cells) { if (isReadHeader) { if (cell.CellType != CellType.Blank) { // Reading header -> ICell always string currStringCellValue = cell.StringCellValue; if ("(1)".Equals(currStringCellValue)) { isReadHeader = false; finalObj = new JObject { { Keywords.NAME_SURVEY, fileName }, { Keywords.IMPORTER, importer }, { Keywords.CREATED_AT, DateTime.Now.ToString(Configs.DATETIME_FORMAT, CultureInfo.InvariantCulture) }, { Keywords.DATA, jsonObject } }; jsonBluePrint = JsonConvert.SerializeObject(finalObj); break; } currRowIndex = cell.RowIndex; currColIndex = cell.ColumnIndex; if (currRowIndex < _numberAddedRow) { // These cell in row was added continue; } if (currRowIndex != 0) { parentCell = GetParentCell(sheet, currRowIndex, currColIndex); } if (!jsonObject.ContainsKey(currStringCellValue)) { if (parentCell != null && parentCell.CellType != CellType.Blank) { parentStringCellValue = parentCell.StringCellValue; } AddKeyToJsonObject(jsonObject, parentStringCellValue, currStringCellValue, false); } } } else { SetValueToJSONBluePrint(jsonObjectTemp, formatter.FormatCellValue(cell), 0); } } if (jsonObjectTemp != null) { result.Add(JsonConvert.SerializeObject(jsonObjectTemp)); } } fs.Close(); return(result); }
/** * Compute width of a single cell * * @param cell the cell whose width is to be calculated * @param defaultCharWidth the width of a single character * @param formatter formatter used to prepare the text to be measured * @param useMergedCells whether to use merged cells * @return the width in pixels */ public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells) { ISheet sheet = cell.Sheet; IWorkbook wb = sheet.Workbook; IRow row = cell.Row; int column = cell.ColumnIndex; int colspan = 1; for (int i = 0; i < sheet.NumMergedRegions; i++) { CellRangeAddress region = sheet.GetMergedRegion(i); if (ContainsCell(region, row.RowNum, column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and move on to the next. return(-1); } cell = row.GetCell(region.FirstColumn); colspan = 1 + region.LastColumn - region.FirstColumn; } } ICellStyle style = cell.CellStyle; CellType cellType = cell.CellType; // for formula cells we compute the cell width for the cached formula result if (cellType == CellType.Formula) { cellType = cell.CachedFormulaResultType; } IFont font = wb.GetFontAt(style.FontIndex); Font windowsFont = IFont2Font(font); //AttributedString str; //TextLayout layout; double width = -1; using (Graphics g = Graphics.FromHwnd(IntPtr.Zero)) { if (cellType == CellType.String) { IRichTextString rt = cell.RichStringCellValue; String[] lines = rt.String.Split("\n".ToCharArray()); for (int i = 0; i < lines.Length; i++) { String txt = lines[i] + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.length()); if (rt.NumFormattingRuns > 0) { // TODO: support rich text fragments } //layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ //AffineTransform trans = new AffineTransform(); //trans.concatenate(AffineTransform.getRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.concatenate( // AffineTransform.getScaleInstance(1, fontHeightMultiple) // ); double angle = style.Rotation * 2.0 * Math.PI / 360.0; SizeF sf = MeasureString(g, txt, windowsFont); double x1 = Math.Abs(sf.Height * Math.Sin(angle)); double x2 = Math.Abs(sf.Width * Math.Cos(angle)); double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) + cell.CellStyle.Indention); //width = Math.Max(width, // ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) + // cell.getCellStyle().getIndention()); } else { //width = Math.Max(width, // ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) + // cell.getCellStyle().getIndention()); double w = Math.Round(MeasureString(g, txt, windowsFont).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) + cell.CellStyle.Indention); } } } else { String sval = null; if (cellType == CellType.Numeric) { // Try to get it formatted to look the same as excel try { sval = formatter.FormatCellValue(cell, dummyEvaluator); } catch (Exception) { sval = cell.NumericCellValue.ToString(); } } else if (cellType == CellType.Boolean) { sval = cell.BooleanCellValue.ToString().ToUpper(); } if (sval != null) { String txt = sval + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.length()); //layout = new TextLayout(str.getIterator(), fontRenderContext); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is added by the standard Excel autosize. */ //AffineTransform trans = new AffineTransform(); //trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0)); //trans.concatenate( // AffineTransform.getScaleInstance(1, fontHeightMultiple) // ); //width = Math.max(width, // ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) + // cell.getCellStyle().getIndention()); double angle = style.Rotation * 2.0 * Math.PI / 360.0; SizeF sf = MeasureString(g, txt, windowsFont); double x1 = sf.Height * Math.Sin(angle); double x2 = sf.Width * Math.Cos(angle); double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) + cell.CellStyle.Indention); } else { //width = Math.max(width, // ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) + // cell.getCellStyle().getIndention()); double w = Math.Round(MeasureString(g, txt, windowsFont).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) + cell.CellStyle.Indention); } } } } return(width); }
//取出excel資料 public ChartData ExtractFile(List <FileDetail> details, string connId) { ChartData chartData = new ChartData(); string fileName = ""; if (details != null) { CalculateFileSize(details); var task = Task.Factory.StartNew(() => { foreach (var detail in details) { FileStream fs = null; IWorkbook workbook = null; ISheet sheet = null; chartData.Type = "LinkChart"; ImportHelper.KeyLineNodeID = 1; fileName = detail.OriginFileName; DataFormatter format = new DataFormatter(CultureInfo.CurrentCulture); try { using (fs = File.OpenRead(detail.PhysicalPath)) { if (detail.Extend == ".xlsx") { workbook = new XSSFWorkbook(fs); } ///舊excel if (detail.Extend == ".xls") { workbook = new HSSFWorkbook(fs); } if (workbook != null) { sheet = workbook.GetSheetAt(0); if (sheet != null && sheet.LastRowNum > 0) { //找first row,並依照關鍵字ImportHelper.FileTypeList() 找資料類型 FindRow(sheet, detail); ImportHelper.Count += detail.StartRow; for (int i = detail.StartRow; i <= sheet.LastRowNum; i++) { NodePair pair = null; ImportHelper.Count++; IRow row = sheet.GetRow(i); string node1ID, node2ID, linkTxt; switch (detail.FileType) { case "電話通聯": node1ID = format.FormatCellValue(row.GetCell(1)); node2ID = format.FormatCellValue(row.GetCell(2)); pair = MapNodePair(node1ID, node2ID); pair.Node1.Data.Category = "phone"; pair.Node2.Data.Category = "phone"; pair.Link.Text = "1"; pair.Link.Arrow2 = true; pair.Link.Data.LinkDatas.Add(new LinkData() { DateTime = format.FormatCellValue(row.GetCell(3)), Period = format.FormatCellValue(row.GetCell(4)), ComType = format.FormatCellValue(row.GetCell(0)), IMEI = format.FormatCellValue(row.GetCell(5)), BaseStation = format.FormatCellValue(row.GetCell(7)) }); //存到資料庫 if (detail.ImportDB) { SaveToDatabase(sheet.GetRow(i)); } break; case "成員名冊": node1ID = format.FormatCellValue(row.GetCell(0)); node2ID = format.FormatCellValue(row.GetCell(1)); pair = MapNodePair(node1ID, node2ID); pair.Link.Text = ""; break; default: break; } if (pair != null) { AddNodeLink(chartData, pair); } } if (detail.ImportDB) { recRep.SaveChanges(); } } } } } catch (Exception e) { if (fs != null) { fs.Close(); } } } }); float ratio = 0; Action UpdateProgress = () => { if (ImportHelper.Total != 0) { ratio = (float)((double)ImportHelper.Count / (double)ImportHelper.Total); FileUploaderHub.UpdateProcess(connId, fileName, ratio * 100); } }; while (!task.IsCompleted && !task.IsCanceled && !task.IsFaulted) { UpdateProgress(); Thread.Sleep(200); } UpdateProgress(); if (task.IsFaulted) { FileUploaderHub.UpdateProcess(connId, "-", 0, string.Join("|", task.Exception.InnerExceptions.Select(o => o.Message).ToArray())); } } //FileUploaderHub.UpdateProcess(connId, "HAHA", 0, "-"); return(chartData); }
public TreeNodeCollection Parse(string filePath) { var treeView = new TreeView(); IWorkbook workbook = null; var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read); if (filePath.IndexOf(".xlsx") > 0) { workbook = new XSSFWorkbook(fs); } else if (filePath.IndexOf(".xls") > 0) { workbook = new HSSFWorkbook(fs); } var formatter = new DataFormatter(); var cellsList = new List <Cell>(); var sheetsNumber = workbook.NumberOfSheets; sheetsNumber = 2; var linesList = new List <Line>(); for (var sheetIndex = 0; sheetIndex < sheetsNumber; sheetIndex++) { var sheet = workbook.GetSheetAt(sheetIndex); if (sheet != null) { _treeRowsList = new List <TreeNode>(); var rowCount = sheet.LastRowNum; // This may not be valid row count. // If first row is table head, i starts from 1 for (var rowNum = 0; rowNum < rowCount; rowNum++) { _treeCellsList = new List <TreeNode>(); var curRow = sheet.GetRow(rowNum); if (curRow == null) { rowCount = rowNum - 1; break; } var elementsList = new List <Element>(); foreach (var cell in curRow.Cells) { if (cell != null) { // 1. AddCell var cellFont = cell.CellStyle.GetFont(workbook); var cellObject = new Cell { column = cell.ColumnIndex, row = cell.RowIndex, sheet = sheetIndex, style = new Style { alignment = cell.CellStyle.Alignment, backgroundColor = null, font = cellFont.FontName, fontSize = cellFont.FontHeight, foregroundColor = null, isBold = cellFont.IsBold, isItalic = cellFont.IsItalic, isUnderline = cellFont.Underline != null ? true : false, isStrikeout = cellFont.IsStrikeout, verticalAlignment = cell.CellStyle.VerticalAlignment } }; //var cellNodesList = new List<TreeNode>(); //cellNodesList.Add(new TreeNode(nameof(cell.RowIndex) + " = " + cell.RowIndex)); //cellNodesList.Add(new TreeNode(nameof(cell.ColumnIndex) + " = " + cell.ColumnIndex)); //cellNodesList.Add( // new TreeNode(nameof(cell.CellStyle.Alignment) + " = " + cell.CellStyle.Alignment)); //cellNodesList.Add(new TreeNode(nameof(cell.IsMergedCell) + " = " + cell.IsMergedCell)); //cellNodesList.Add(new TreeNode(nameof(cellFont.FontName) + " = " + cellFont.FontName)); //cellNodesList.Add(new TreeNode(nameof(cellFont.Color) + " = " + cellFont.Color)); //cellNodesList.Add(new TreeNode(nameof(cellFont.IsBold) + " = " + cellFont.IsBold)); //cellNodesList.Add(new TreeNode(nameof(cellFont.IsItalic) + " = " + cellFont.IsItalic)); //cellNodesList.Add( // new TreeNode(nameof(cellFont.FontHeight) + " = " + cellFont.FontHeight)); var formatCellValue = formatter.FormatCellValue(cell); //cellNodesList.Add(new TreeNode( // nameof(formatCellValue) + " = " + formatCellValue)); cellObject.displayContent = formatCellValue; string cellContent = null; if (cell.CellType == CellType.String) { //cellNodesList.Add(new TreeNode( // nameof(cell.RichStringCellValue) + " = " + cell.RichStringCellValue)); //cellNodesList.Add( // new TreeNode(nameof(cell.StringCellValue) + " = " + cell.StringCellValue)); cellContent = cell.RichStringCellValue.ToString(); cellObject.cellType = Model.CellType.String; } else if (cell.CellType == CellType.Numeric) { var myCell = cell; //cellNodesList.Add(new TreeNode( // nameof(cell.NumericCellValue) + " = " + cell.NumericCellValue)); cellContent = cell.NumericCellValue.ToString(); cellObject.cellType = Model.CellType.Numeric; } else if (cell.CellType == CellType.Formula) { //cellNodesList.Add( // new TreeNode(nameof(cell.CellFormula) + " = " + cell.CellFormula)); cellContent = cell.CellFormula; cellObject.cellType = Model.CellType.Formula; } else if (cell.CellType == CellType.Boolean) { //cellNodesList.Add( // new TreeNode(nameof(cell.BooleanCellValue) + " = " + cell.BooleanCellValue)); cellContent = cell.BooleanCellValue.ToString(); cellObject.cellType = Model.CellType.Boolean; } cellObject.content = cellContent; //cellNodesList.Add(new TreeNode(nameof(cell.CellComment) + " = " + cell.CellComment)); //var styleNode = new TreeNode("Cell : " + cell.ColumnIndex, cellNodesList.ToArray()); //_treeCellsList.Add(styleNode); var element = new Element { cell = cellObject }; elementsList.Add(element); cellsList.Add(cellObject); } } var line = new Line { ElementGroupsList = new List <ElementGroup> { new ElementGroup { elementsList = elementsList } } }; linesList.Add(line); } SerializeObject(linesList); //var treeNodeRow = new TreeNode("Row " + rowNum, _treeCellsList.ToArray()); //_treeRowsList.Add(treeNodeRow); } // Ajout des feuilles à la listView var treeNode = new TreeNode("Sheet " + sheetIndex, _treeRowsList.ToArray()); treeView.Nodes.Add(treeNode); } return(treeView.Nodes); }
private string GetCellValueAsString(ICell cell) { DataFormatter dataFormatter = new DataFormatter(); return(dataFormatter.FormatCellValue(cell, new HSSFFormulaEvaluator(cell.Sheet.Workbook))); }
public static T GetValue <T>(this IRow row, int columnIndex) { var type = typeof(T); var cell = row.GetCell(columnIndex, MissingCellPolicy.RETURN_NULL_AND_BLANK); // null-handling if (cell == null) { if (type.IsNullableType() || type == typeof(string)) { return((T)(object)null); } else { throw new NotSupportedException($"Can't assign null value into '{type.FullName}'!"); } } // string handling if (type == typeof(string)) { // regular string entry if (cell.CellType == CellType.String) { return((T)(object)cell.StringCellValue); } // formula with string result if (cell.CellType == CellType.Formula && cell.CachedFormulaResultType == CellType.String) { return((T)(object)cell.StringCellValue); } // format value as string var formatter = new DataFormatter(); var formattedValue = formatter.FormatCellValue(cell); /* * // replace non-breakign space with regular space (nbsp were uses with numeric formatting) * formattedValue = formattedValue.Replace(Convert.ToChar(160), ' '); */ return((T)(object)formattedValue); } // fetching other value types if (type.GetNonNullableType() == typeof(char)) { return((T)(object)(char)cell.NumericCellValue); } else if (type.IsNumeric()) { return((T)ChangeTypeEx(cell.NumericCellValue, type)); } else if (type.IsDateTime()) { return((T)(object)cell.DateCellValue); } else if (type.IsTimeSpan()) { return((T)(object)TimeSpan.FromDays(cell.NumericCellValue)); } else if (type.IsBool()) { return((T)(object)cell.BooleanCellValue); } else { throw new NotSupportedException($"Can't handle type'{type.FullName}'!"); } }
public void TestGetFormattedCellValueHSSFCell() { // Valid date formats -- cell values should be date formatted & not "555.555" Row row = wb.GetSheetAt(0).GetRow(0); IEnumerator it = row.GetCellEnumerator(); log("==== VALID DATE FORMATS ===="); while (it.MoveNext()) { Cell cell = (Cell)it.Current; log(formatter.FormatCellValue(cell)); // should not be equal to "555.555" Assert.IsTrue(!"555.555".Equals(formatter.FormatCellValue(cell))); // should contain "Jul" in the String string result = formatter.FormatCellValue(cell); Assert.IsTrue(result.IndexOf("Jul") > -1 || result.IndexOf("七月") > -1); } // Test number formats row = wb.GetSheetAt(0).GetRow(1); it = row.GetCellEnumerator(); log("\n==== VALID NUMBER FORMATS ===="); while (it.MoveNext()) { Cell cell = (Cell)it.Current; log(formatter.FormatCellValue(cell)); // should not be equal to "1234567890.12345" Assert.IsTrue(!"1234567890.12345".Equals(formatter.FormatCellValue(cell))); } // Test bad number formats row = wb.GetSheetAt(0).GetRow(2); it = row.GetCellEnumerator(); log("\n==== INVALID NUMBER FORMATS ===="); while (it.MoveNext()) { Cell cell = (Cell)it.Current; log(formatter.FormatCellValue(cell)); // should be equal to "1234567890.12345" Assert.AreEqual("1234567890.12345", formatter.FormatCellValue(cell)); } // Test Zip+4 format row = wb.GetSheetAt(0).GetRow(3); Cell cell2 = row.GetCell(0); log("\n==== ZIP FORMAT ===="); log(formatter.FormatCellValue(cell2)); Assert.AreEqual("12345-6789", formatter.FormatCellValue(cell2)); // Test phone number format row = wb.GetSheetAt(0).GetRow(4); cell2 = row.GetCell(0); log("\n==== PHONE FORMAT ===="); log(formatter.FormatCellValue(cell2)); Assert.AreEqual("(555) 123-4567", formatter.FormatCellValue(cell2)); // Test SSN format row = wb.GetSheetAt(0).GetRow(5); cell2 = row.GetCell(0); log("\n==== SSN FORMAT ===="); log(formatter.FormatCellValue(cell2)); Assert.AreEqual("444-55-1234", formatter.FormatCellValue(cell2)); // null Test-- null cell should result in empty String Assert.AreEqual(formatter.FormatCellValue(null), ""); // null Test-- null cell should result in empty String Assert.AreEqual(formatter.FormatCellValue(null), ""); }
public IActionResult Import() { if (!AuthenticationController.CheckToken(HttpContext, out string _)) { return(Unauthorized()); } IFormFile file = Request.Form.Files[0]; using (var stream = new MemoryStream()) { // copy file into memory file.CopyTo(stream); // reset stream position stream.Position = 0; // create excel file abstraction over memory bytes var excelFile = new XSSFWorkbook(stream); var itemTypesTable = excelFile.GetSheet("ItemTypes"); var itemsTable = excelFile.GetSheet("Items"); var headerRow = itemTypesTable.GetRow(0).Cells; List <int> itemTypesColumnIndices = new List <int>(headerRow.Count) { headerRow.Where(cell => cell.StringCellValue == "Name").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "UPC").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "ImageURL").First().ColumnIndex }; headerRow = itemsTable.GetRow(0).Cells; List <int> itemsColumnIndices = new List <int>(headerRow.Count) { headerRow.Where(cell => cell.StringCellValue == "Id").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "ItemTypeUPC").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "OrderNumberToSeller").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "OrderNumberToBuyer").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "QRCode").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "ItemStatus").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "PricePaidBySeller").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "TaxPaidBySeller").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "ShippingCostToSeller").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "ShippingCostToBuyer").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "Fees").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "OtherExpenses").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "ShippingPaidByBuyer").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "PricePaidByBuyer").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "TimeStampOrdered").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "TimeStampReceived").First().ColumnIndex, headerRow.Where(cell => cell.StringCellValue == "TimeStampSold").First().ColumnIndex }; using var db = new DatabaseAccess(); // clear table db.ItemTypes.RemoveRange(db.ItemTypes); DataFormatter formatter = new DataFormatter(); var getStringValue = new Func <ICell, string>(cell => { var val = formatter.FormatCellValue(cell); return(string.IsNullOrEmpty(val) ? null : val); }); for (int i = 1; i < itemTypesTable.PhysicalNumberOfRows; i++) { var row = itemTypesTable.GetRow(i); var itemType = new ItemType { Name = getStringValue(row.GetCell(itemTypesColumnIndices[0])), UPC = long.Parse(formatter.FormatCellValue(row.GetCell(itemTypesColumnIndices[1]))), ImageURL = getStringValue(row.GetCell(itemTypesColumnIndices[2])) }; db.ItemTypes.Add(itemType); } // clear table db.Items.RemoveRange(db.Items); for (int i = 1; i < itemsTable.PhysicalNumberOfRows; i++) { var row = itemsTable.GetRow(i); var extractDate = new Func <ICell, DateTime>(cell => { if (cell.CellType == CellType.Numeric && cell.NumericCellValue != -1) { return(cell.DateCellValue); } else { return(new DateTime()); } }); var item = new Item { Id = 0, ItemTypeUPC = long.Parse(formatter.FormatCellValue(row.GetCell(itemsColumnIndices[1]))), OrderNumberToSeller = getStringValue(row.GetCell(itemsColumnIndices[2])), OrderNumberToBuyer = getStringValue(row.GetCell(itemsColumnIndices[3])), QRCode = getStringValue(row.GetCell(itemsColumnIndices[4])), ItemStatus = (ItemStatus)Enum.Parse(typeof(ItemStatus), row.GetCell(itemsColumnIndices[5]).StringCellValue), PricePaidBySeller = (decimal)row.GetCell(itemsColumnIndices[6]).NumericCellValue, TaxPaidBySeller = (decimal)row.GetCell(itemsColumnIndices[7]).NumericCellValue, ShippingCostToSeller = (decimal)row.GetCell(itemsColumnIndices[8]).NumericCellValue, ShippingCostToBuyer = (decimal)row.GetCell(itemsColumnIndices[9]).NumericCellValue, Fees = (decimal)row.GetCell(itemsColumnIndices[10]).NumericCellValue, OtherExpenses = (decimal)row.GetCell(itemsColumnIndices[11]).NumericCellValue, ShippingPaidByBuyer = (decimal)row.GetCell(itemsColumnIndices[12]).NumericCellValue, PricePaidByBuyer = (decimal)row.GetCell(itemsColumnIndices[13]).NumericCellValue, TimeStampOrdered = extractDate(row.GetCell(itemsColumnIndices[14])), TimeStampReceived = extractDate(row.GetCell(itemsColumnIndices[15])), TimeStampSold = extractDate(row.GetCell(itemsColumnIndices[16])), }; db.Items.Add(item); } db.SaveChanges(); } return(Accepted()); }
/// <summary> /// /// </summary> /// <returns></returns> public ActionResult ExcelImportData() { int SuccessCount = 0; int ErrorCount = 0; string virtualPath = ""; string fullFileName = ""; string error = string.Empty; //读取上传文件 System.Web.HttpPostedFileBase pstFile = Request.Files["file"]; if (pstFile != null) { //上传文件 if (UploadifyFile(pstFile, ref virtualPath, ref fullFileName)) { #region 读取Excel内容 IWorkbook workbook = null; FileStream fs = null; string fileName = fullFileName; ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; #region 读取Excel 导入到系统中 try { fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls") > 0) // 2003版本 { workbook = new HSSFWorkbook(fs); } sheet = workbook.GetSheet("Sheet1"); if (sheet == null) //如果没有找到指定的sheetName对应的sheet ,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 //列名的集合 List <string> TitleList = new List <string>(); //导入薪资Excel的最后一列加上状态 ICell resultCell = firstRow.CreateCell(cellCount + 1); resultCell.SetCellValue("导入结果"); #region 第一行是否是DataTable的列名 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue.Trim(); if (cellValue != null) { //添加到集合列中 DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); TitleList.Add(cellValue); } } } //最后一列显示导入结果状态 DataColumn lastColumn = new DataColumn("结果"); data.Columns.Add(lastColumn); startRow = sheet.FirstRowNum + 1; #endregion #region 导入内容 //获取当前用户ID string currentPerson = SystemInfo.CurrentUserId; //记录薪资项目和对应的值 Dictionary <string, string> dicList = new Dictionary <string, string>(); #region 循环所有行数 //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } if (row.FirstCellNum > 0) { continue; //不是从第一个单元格开始 } DataRow dataRow = data.NewRow(); //清除项目 dicList.Clear(); error = string.Empty; ContractEntity entity = new ContractEntity(); List <ContractServiceEntity> serviceEntityList = new List <ContractServiceEntity>(); try { #region 每行的列 for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null { string CellValue = string.Empty; #region 获取单元格的值 switch (row.GetCell(j).CellType) { case CellType.Blank: case CellType.Error: case CellType.Unknown: break; case CellType.Boolean: CellValue = row.GetCell(j).BooleanCellValue ? "1" : "0"; break; case CellType.Formula: //单元格函数 CellValue = row.GetCell(j).CellFormula; if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { DataFormatter formatter = new DataFormatter(); CellValue = formatter.FormatCellValue(row.GetCell(j)); } else { CellValue = row.GetCell(j).NumericCellValue.ToString(); } break; case CellType.Numeric: //数字类型 if (DateUtil.IsCellDateFormatted(row.GetCell(j))) { DataFormatter formatter = new DataFormatter(); CellValue = formatter.FormatCellValue(row.GetCell(j)); } else { CellValue = row.GetCell(j).NumericCellValue.ToString(); } break; case CellType.String: //字符串 CellValue = row.GetCell(j).StringCellValue; break; default: break; } #endregion dataRow[j] = CellValue; #region 务类型 if (TitleList[j] == "服务类型") { string[] strArr = CellValue.Split(new char[] { ',', ';', '^', '|', ',' }, StringSplitOptions.RemoveEmptyEntries); if (strArr.Length > 0) { for (int q = 0; q < strArr.Length; q++) { var type = strArr[q]; var productInfo = itemCache.GetDataItemList("Client_ProductInfo").FirstOrDefault(s => s.ItemName == type); if (productInfo != null) { ContractServiceEntity serviceEntity = new ContractServiceEntity(); serviceEntity.ServiceTypeId = productInfo.ItemDetailId; serviceEntity.ServiceType = productInfo.ItemName; serviceEntity.ContractId = ""; serviceEntity.Create(); serviceEntityList.Add(serviceEntity); //合同编码的前置 if (string.IsNullOrWhiteSpace(entity.ContractCode)) { var dataItem = itemCache.GetDataItem(serviceEntity.ServiceTypeId); if (dataItem != null) { entity.ContractCode = dataItem.ItemValue; } } } } } if (serviceEntityList.Count < 1) { error = "服务类型数据有误"; break; } var serviceList = serviceEntityList.Select(s => s.ServiceType); entity.ServiceType = string.Join(",", serviceList); } #endregion error = SetEntityValue(entity, TitleList[j], CellValue.Trim()); if (!string.IsNullOrWhiteSpace(error)) { break; } } } #endregion if (error == string.Empty) { string moduleCode = string.Empty; if (entity.ContractType == 21) { moduleCode = "10007"; } else { moduleCode = "10008"; } contractbll.AddForm(entity, serviceEntityList, "", moduleCode); error = "导入成功"; SuccessCount++; } else { ErrorCount++; } } catch (Exception ex) { error = ex.Message; ErrorCount++; } dataRow[cellCount] = error; data.Rows.Add(dataRow); ICell cell = row.CreateCell(cellCount + 1); cell.SetCellValue(error); } #endregion #endregion } ViewBag.ResultImportData = data; } catch (Exception ex) { Response.Write("批量导入客户异常1: " + ex.Message); error = "批量导入客户异常: " + ex.Message; } finally { if (fs != null) { fs.Close(); } fs = null; } #endregion try { using (FileStream fileWrite = new FileStream(fullFileName, FileMode.Create)) { workbook.Write(fileWrite); } } catch (Exception ex) { } #endregion } } error = "导入数据不存在,请返回批量导入页面!"; ViewBag.Error = error; ViewBag.Url = virtualPath; ViewBag.ErrorCount = ErrorCount; ViewBag.SuccessCount = SuccessCount; return(View("ExportError")); }
private static Classificacoes ReadClassificacoes(ISheet sheet, int numValores) { int startCol = 15; //Ler Cabecalho dos tipos doc IRow rowCabecalho = sheet.GetRow(1); string[] cabecalhoTiposDoc = new string[40]; //Diz o tipo de documento por coluna (indexado pela coluna) for (int col = startCol; col <= startCol + 20; col++) { var cell = rowCabecalho.GetCell(col); if (cell == null) { cabecalhoTiposDoc[col] = null; break; } cabecalhoTiposDoc[col] = cell.StringCellValue; } Classificacoes classificacoes = new Classificacoes(); //Lê as contas ContasParaValor contasBase0 = ReadContasParaValor(sheet.GetRow(2), startCol, cabecalhoTiposDoc, "Base0"); ContasParaValor contasBase1 = ReadContasParaValor(sheet.GetRow(3), startCol, cabecalhoTiposDoc, "Base1"); ContasParaValor contasBase2 = ReadContasParaValor(sheet.GetRow(4), startCol, cabecalhoTiposDoc, "Base2"); ContasParaValor contasBase3 = ReadContasParaValor(sheet.GetRow(5), startCol, cabecalhoTiposDoc, "Base3"); ContasParaValor contasIva1 = ReadContasParaValor(sheet.GetRow(6), startCol, cabecalhoTiposDoc, "Iva1"); ContasParaValor contasIva2 = ReadContasParaValor(sheet.GetRow(7), startCol, cabecalhoTiposDoc, "Iva2"); ContasParaValor contasIva3 = ReadContasParaValor(sheet.GetRow(8), startCol, cabecalhoTiposDoc, "Iva3"); ContasParaValor contasRetIva = ReadContasParaValor(sheet.GetRow(9), startCol, cabecalhoTiposDoc, "RetIva"); ContasParaValor contasTotal = ReadContasParaValor(sheet.GetRow(10), startCol, cabecalhoTiposDoc, "Total"); classificacoes.contasPorValor = new ContasParaValor[] { contasBase0, contasBase1, contasBase2, contasBase3, contasIva1, contasIva2, contasIva3, contasRetIva, contasTotal }; //Lê os diarios codigos de documento para cada tipo de documento for (int rowIdx = 12; rowIdx <= sheet.LastRowNum; rowIdx++) { IRow row = sheet.GetRow(rowIdx); if (row != null) //Se a linha tiver dados continua { string tipoDoc = row.GetCell(14).StringCellValue; if (tipoDoc == null || tipoDoc.Trim().Length == 0) { break; } //Obtem o codigo de documento e dirio para este tipoDoc string codigoDoc = df.FormatCellValue(row.GetCell(15)); string diario = df.FormatCellValue(row.GetCell(16)); classificacoes.diarioECodigoDocPorTipoDoc.Add(tipoDoc, new Tuple <string, string>(diario, codigoDoc)); } } return(classificacoes); }
/// <summary> /// Reads consumption-compilant portions of Data from the underlaying data-source /// </summary> /// <returns>an IEnumerable of the base-set</returns> protected override IEnumerable <IDictionary <string, ExcelDataRecord> > ReadData() { IWorkbook book = OpenWorkbook(sourceFile); bool foundSheet = false; for (int i = 0; i < book.NumberOfSheets; i++) { string msg; ISheet sheet = book.GetSheetAt(i); if ((ImportWorkSheetDecider.Decide(sheet.SheetName, DecisionMethod.Simple, out msg) & (DecisionResult.Acceptable | DecisionResult.Success)) != DecisionResult.None) { foundSheet = true; yield return (new Dictionary <string, ExcelDataRecord> { { "SheetName", new ExcelDataRecord { FormattedText = sheet.SheetName, RawData = sheet.SheetName } } }); DataFormatter f = new DataFormatter(); foreach (IRow row in sheet) { if (row != null) { Dictionary <string, ExcelDataRecord> vals = new Dictionary <string, ExcelDataRecord>(); foreach (ICell cell in row.Cells) { XLNumber num = cell.ColumnIndex; object value = GetCellValue(cell, cell.CellType); if (value != null) { vals.Add(num.ToString(), new ExcelDataRecord { RawData = value, FormattedText = f.FormatCellValue(cell) }); } else if (readNullData) { vals.Add(num.ToString(), new ExcelDataRecord { RawData = null, FormattedText = "" }); } } string origin = $"{sheet.SheetName}::{row.RowNum}"; vals.Add("$origin", new ExcelDataRecord { FormattedText = origin, RawData = origin }); yield return(vals); } } } } if (!foundSheet) { LogParserEvent(null, $@"No sheet has met the configured Constraints. Settings for sheet-search: {ImportWorkSheetDecider}", ParserEventSeverity.Warning, null); } }
//將excel 存到list 給controller public AttachSingleViewModel ShowExcelFile(Attachment attach) { FileStream fs = null; //NPOI IWorkbook workbook = null; ISheet sheet = null; IRow row = null; ICell cell = null; DataFormatter dataFormatter = new DataFormatter(CultureInfo.CurrentCulture); //插入詳細資訊 AttachSingleViewModel viewModel = new AttachSingleViewModel() { AttachDetail = attach }; //2維列表 AttachXmlViewModel xmlViewModel = null; AttachXmlRow rowViewModel = null; //path var physicalPath = HttpContext.Current.Server.MapPath(attach.AttachmentPath); var path = Path.Combine(physicalPath, attach.AttachmentName); //圖片檔 Regex regex = new Regex(@"\.(jpg|jpeg|png|gif)$", RegexOptions.IgnoreCase); if (regex.IsMatch(attach.AttachmentType)) { viewModel.AttahcFilePath = Path.Combine(attach.AttachmentPath, attach.AttachmentName); } try { //開啟現有檔案來讀取,存到資料串流 using (fs = File.OpenRead(path)) { //資料串流存到NPOI物件 //新excel if (attach.AttachmentType == ".xlsx") { workbook = new XSSFWorkbook(fs); } ///舊excel if (attach.AttachmentType == ".xls") { workbook = new HSSFWorkbook(fs); } if (workbook != null) { sheet = workbook.GetSheetAt(0); //NPOI sheet xmlViewModel = new AttachXmlViewModel(); if (sheet != null) { int rowCount = sheet.LastRowNum; if (rowCount > 20) { rowCount = 20; } if (rowCount > 0) { IRow firstRow = sheet.GetRow(0); //NPOI row int cellCount = firstRow.LastCellNum; for (int i = 0; i < rowCount; i++) { row = sheet.GetRow(i); if (row == null) { continue; } rowViewModel = new AttachXmlRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { cell = row.GetCell(j); //NPOI column if (cell == null) { rowViewModel.XmlRow.Add(""); } else { rowViewModel.XmlRow.Add(dataFormatter.FormatCellValue(cell)); } } //插入列表 row xmlViewModel.Table.Add(rowViewModel); } } } } //插入整個列表 viewModel.AttachFile = xmlViewModel; return(viewModel); } } catch (Exception e) { if (fs != null) { fs.Close(); } return(viewModel); } }
// /** // * Drawing context to measure text // */ //private static FontRenderContext fontRenderContext = new FontRenderContext(null, true, true); /** * Compute width of a column and return the result * * @param sheet the sheet to calculate * @param column 0-based index of the column * @param useMergedCells whether to use merged cells * @return the width in pixels */ public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells) { //AttributedString str; //TextLayout layout; IWorkbook wb = sheet.Workbook; DataFormatter formatter = new DataFormatter(); IFont defaultFont = wb.GetFontAt((short)0); //str = new AttributedString((defaultChar)); //copyAttributes(defaultFont, str, 0, 1); //layout = new TextLayout(str.Iterator, fontRenderContext); //int defaultCharWidth = (int)layout.Advance; Font font = IFont2Font(defaultFont); int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width; DummyEvaluator dummyEvaluator = new DummyEvaluator(); double width = -1; using (Bitmap bmp = new Bitmap(2048, 100)) { Graphics g = Graphics.FromImage(bmp); //rows: bool skipthisrow = false; for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext();) { IRow row = (IRow)it.Current; ICell cell = row.GetCell(column); if (cell == null) { continue; } int colspan = 1; for (int i = 0; i < sheet.NumMergedRegions; i++) { CellRangeAddress region = sheet.GetMergedRegion(i); if (ContainsCell(region, row.RowNum, column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and Move on to the next. //continue rows; skipthisrow = true; } cell = row.GetCell(region.FirstColumn); colspan = 1 + region.LastColumn - region.FirstColumn; } } if (skipthisrow) { continue; } ICellStyle style = cell.CellStyle; Chama.Utils.NPOI.SS.UserModel.IFont font1 = wb.GetFontAt(style.FontIndex); CellType cellType = cell.CellType; // for formula cells we compute the cell width for the cached formula result if (cellType == CellType.FORMULA) { cellType = cell.CachedFormulaResultType; } if (cellType == CellType.STRING) { IRichTextString rt = cell.RichStringCellValue; String[] lines = rt.String.Split("\n".ToCharArray()); for (int i = 0; i < lines.Length; i++) { String txt = lines[i] + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.Length); font = IFont2Font(font1); if (rt.NumFormattingRuns > 0) { // TODO: support rich text fragments } //layout = new TextLayout(str.Iterator, fontRenderContext); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is Added by the standard Excel autosize. */ double angle = style.Rotation * 2.0 * Math.PI / 360.0; //AffineTransform trans = new AffineTransform(); //trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.Concatenate( //AffineTransform.GetScaleInstance(1, fontHeightMultiple) //); SizeF sf = g.MeasureString(txt, font); double x1 = Math.Abs(sf.Height * Math.Sin(angle)); double x2 = Math.Abs(sf.Width * Math.Cos(angle)); double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); //width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); } else { //width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } } } else { String sval = null; if (cellType == CellType.NUMERIC) { // Try to Get it formatted to look the same as excel try { sval = formatter.FormatCellValue(cell, dummyEvaluator); } catch (Exception) { sval = cell.NumericCellValue.ToString("F", CultureInfo.InvariantCulture); } } else if (cellType == CellType.BOOLEAN) { sval = cell.BooleanCellValue.ToString().ToUpper(); } if (sval != null) { String txt = sval + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.Length); //layout = new TextLayout(str.Iterator, fontRenderContext); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but * is Added by the standard Excel autosize. */ double angle = style.Rotation * 2.0 * Math.PI / 360.0; //AffineTransform trans = new AffineTransform(); //trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.Concatenate( //AffineTransform.GetScaleInstance(1, fontHeightMultiple) //); //width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); SizeF sf = g.MeasureString(txt, font); double x1 = sf.Height * Math.Sin(angle); double x2 = sf.Width * Math.Cos(angle); double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven); width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } else { //width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven); width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } } } } } return(width); }