Ejemplo n.º 1
0
        /**
         * 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);
        }
Ejemplo n.º 2
0
        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));
        }
Ejemplo n.º 3
0
        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)
            {
            }
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        /**
         * 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);
        }
Ejemplo n.º 6
0
        //取出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);
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
        private string GetCellValueAsString(ICell cell)
        {
            DataFormatter dataFormatter = new DataFormatter();

            return(dataFormatter.FormatCellValue(cell, new HSSFFormulaEvaluator(cell.Sheet.Workbook)));
        }
Ejemplo n.º 9
0
        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}'!");
            }
        }
Ejemplo n.º 10
0
        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());
        }
Ejemplo n.º 12
0
        /// <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);
            }
        }
Ejemplo n.º 15
0
        //將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);
            }
        }
Ejemplo n.º 16
0
        // /**
        // * 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);
        }