private void MontarCabecalho(IXLWorksheet worksheet, PropertyInfo[] properties)
        {
            for (var column = 0; column < properties.Length; column++)
            {
                var propriedadeRaiz = properties.GetValue(column);

                var colunaValor = ((MemberInfo)propriedadeRaiz).Name;

                var nomePropriedade = ((MemberInfo)propriedadeRaiz).CustomAttributes.FirstOrDefault(a => a.AttributeType.Name == "DisplayAttribute");

                if (nomePropriedade != null)
                {
                    var propriedadeAtributoDescricao = nomePropriedade.NamedArguments.FirstOrDefault(a => a.MemberName == "Description");
                    if (nomePropriedade != null)
                    {
                        colunaValor = propriedadeAtributoDescricao.TypedValue.Value.ToString();
                    }
                }
                var colunaNome = $"{Number2String(column, true)}{1}";

                CabecalhoFormataStylo(worksheet, colunaNome);


                worksheet.Cells(colunaNome).Value = colunaValor;
            }
        }
コード例 #2
0
        PopulationByAges GetAllDataFromWorksheet(IXLWorksheet worksheet, Dictionary <string, HeaderItem> headers, string primaryKey)
        {
            var headerCells = worksheet.Cells();

            foreach (var header in headers)
            {
                header.Value.Address = GetSpecificHeaderColumn(headerCells, header.Key);
            }

            var lastRowNumber = worksheet.LastRowUsed().Cell(headers[primaryKey].Address.ColumnNumber).Address.RowNumber;
            var lastColNumber = worksheet.LastColumnUsed().Cell(headers[primaryKey].Address.RowNumber).Address.ColumnNumber;
            var bodyCells     = worksheet.Range($"A1:{ToColumnName(lastColNumber)}{lastRowNumber}").Cells();

            var primaryRows = bodyCells.Where(i => i.Address.ColumnNumber == headers[primaryKey].Address.ColumnNumber).Select(i => i);

            StringBuilder sb = new StringBuilder();

            sb.Append("{\"contents\":[");
            foreach (var primaryRow in primaryRows)
            {
                Regex regex = new Regex("^[0-9]");
                if (regex.IsMatch(primaryRow.Value.ToString().Trim()) == false)
                {
                    continue;
                }

                sb.Append("{");

                var excelRow = primaryRow.Address.RowNumber;
                foreach (var header in headers)
                {
                    var valueObject = worksheet.Cells().Where(i => i.Address.RowNumber == excelRow &&
                                                              i.Address.ColumnNumber == headers[header.Key].Address.ColumnNumber)
                                      .Select(i => i.Value)
                                      .FirstOrDefault();
                    sb.Append($"\"{header.Key}\":\"{valueObject.ToString().Trim()}\",");
                }
                sb.ToString().Remove(sb.Length - 1, 1);
                sb.Append("},");
            }
            sb.ToString().Remove(sb.Length - 1, 1);
            sb.Append("]}");

            return(JsonConvert.DeserializeObject <PopulationByAges>(sb.ToString()));
        }
コード例 #3
0
        public void WsNamedCells()
        {
            var          wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet1");

            ws.Cell(1, 1).SetValue("Test").AddToNamed("TestCell", XLScope.Worksheet);
            ws.Cell(2, 1).SetValue("B");
            IXLCells cells = ws.Cells("TestCell, A2");

            Assert.AreEqual("Test", cells.First().GetString());
            Assert.AreEqual("B", cells.Last().GetString());
        }
コード例 #4
0
        /// <summary>
        /// Read object data from XLSX worksheet
        /// </summary>
        /// <param name="worksheet">worksheet</param>
        /// <param name="row">Row index</param>
        /// /// <param name="cellOffset">Cell offset</param>
        public virtual void ReadFromXlsx(IXLWorksheet worksheet, int row, int cellOffset = 0)
        {
            if (worksheet?.Cells() == null)
            {
                return;
            }

            foreach (var prop in _properties.Values)
            {
                prop.PropertyValue = worksheet.Row(row).Cell(prop.PropertyOrderPosition + cellOffset).Value;
            }
        }
        private static void CorpoFormataStylo(IXLWorksheet worksheet, string celulaNome)
        {
            worksheet.Cells(celulaNome).Style.Border.TopBorder      = XLBorderStyleValues.Thin;
            worksheet.Cells(celulaNome).Style.Border.TopBorderColor = XLColor.Black;

            worksheet.Cells(celulaNome).Style.Border.RightBorder      = XLBorderStyleValues.Thin;
            worksheet.Cells(celulaNome).Style.Border.RightBorderColor = XLColor.Black;

            worksheet.Cells(celulaNome).Style.Border.BottomBorder      = XLBorderStyleValues.Thin;
            worksheet.Cells(celulaNome).Style.Border.BottomBorderColor = XLColor.Black;

            worksheet.Cells(celulaNome).Style.Border.LeftBorder      = XLBorderStyleValues.Thin;
            worksheet.Cells(celulaNome).Style.Border.LeftBorderColor = XLColor.Black;
        }
コード例 #6
0
        public IList <House> GetHouses(string path)
        {
            XLWorkbook     wb          = new XLWorkbook(path);
            IXLWorksheet   sheet       = wb.Worksheets.First();
            IXLRange       RangeSheet  = sheet.RangeUsed();
            int            rightBorder = RangeSheet.LastColumn().ColumnNumber();                                   //установим правую границу данных
            int            downBorder  = RangeSheet.LastRow().RowNumber();                                         //установим нижнюю границу данных
            List <IXLCell> Xlhouses    = sheet.Cells().Where(c => c.GetValue <string>().Contains("Дом")).ToList(); //получим ячейки, в которых номера домов
            List <House>   houses      = new List <House>();

            foreach (var Xlhouse in Xlhouses)
            {
                House house = new House();
                house.Name  = Xlhouse.GetValue <string>();
                house.Flats = new List <Flat>();
                IXLCell cell = Xlhouse.CellBelow();

                while (!(cell.GetValue <string>() == "" &&
                         cell.CellBelow().GetValue <string>() == "" &&
                         cell.CellRight().GetValue <string>() == "" &&
                         cell.CellRight().CellBelow().GetValue <string>() == ""
                         ) &&
                       cell.WorksheetColumn().ColumnNumber() <= rightBorder
                       )      //рассматриваем, есть ли значение в ячейке под названием дома, а также в ближайших от нее соседей
                {
                    IXLCell cellBellowHouse = cell;
                    while (!(cell.GetValue <string>() == "" &&
                             cell.CellBelow().GetValue <string>() == ""
                             ) &&
                           cell.WorksheetRow().RowNumber() <= downBorder
                           ) //смотрим есть ли в текущей ячейке значение или той, которая находится снизу
                    {
                        if (cell.GetValue <string>().Contains("№"))
                        {
                            Flat flat = new Flat();
                            flat.Number = cell.GetValue <string>().Substring(1);
                            flat.Price  = cell.CellBelow().GetValue <string>();
                            house.Flats.Add(flat);
                        }
                        cell = cell.CellBelow();
                    }

                    cell = cellBellowHouse.CellRight(); //после того, как закончили с колонкой, переходим в следующую колонку
                                                        //самую верхнюю под номером дома
                }

                houses.Add(house);
            }

            return(houses);
            //throw new NotImplementedException();
        }
        private static void CabecalhoFormataStylo(IXLWorksheet worksheet, string colunaNome)
        {
            worksheet.Cells(colunaNome).Style.Border.TopBorder      = XLBorderStyleValues.Thin;
            worksheet.Cells(colunaNome).Style.Border.TopBorderColor = XLColor.Black;

            worksheet.Cells(colunaNome).Style.Border.RightBorder      = XLBorderStyleValues.Thin;
            worksheet.Cells(colunaNome).Style.Border.RightBorderColor = XLColor.Black;

            worksheet.Cells(colunaNome).Style.Border.BottomBorder      = XLBorderStyleValues.Thin;
            worksheet.Cells(colunaNome).Style.Border.BottomBorderColor = XLColor.Black;

            worksheet.Cells(colunaNome).Style.Border.LeftBorder      = XLBorderStyleValues.Thin;
            worksheet.Cells(colunaNome).Style.Border.LeftBorderColor = XLColor.Black;

            worksheet.Cells(colunaNome).Style.Font.Bold = true;

            worksheet.Columns().AdjustToContents();
        }
コード例 #8
0
        //TODO: Delegate convertation to item method.
        //TODO: Catch "Too many requests", add timeout and try again.
        public static void ObjToExcel(MarketplaceList items, bool open = false)
        {
            Console.WriteLine("Creating xlsx file. This can take a while.");
            XLWorkbook   workbook  = new XLWorkbook();
            IXLWorksheet worksheet = workbook.Worksheets.Add("Coins");

            worksheet.Cell(1, 1).Value = "Link";
            worksheet.Cell(1, 2).Value = "Name";
            worksheet.Cell(1, 3).Value = "Code";
            worksheet.Cell(1, 4).Value = "Color";
            worksheet.Cell(1, 5).Value = "Amount";
            worksheet.Cell(1, 6).Value = "Price";

            worksheet.Cells(true).Style.Font.SetBold();

            for (int i = 0; i < items.Count; i++)
            {
                MarketplaceItem item = items[i];

                worksheet.Cell(i + 2, 1).Hyperlink = new XLHyperlink(@item.Link);
                worksheet.Cell(i + 2, 1).Value     = @item.Link;
                worksheet.Cell(i + 2, 2).Value     = item.Name;
                worksheet.Cell(i + 2, 3).Value     = item.Code;
                worksheet.Cell(i + 2, 4).Value     = item.ColorStr;
                worksheet.Cell(i + 2, 4).Style.Fill.BackgroundColor = XLColor.FromArgb(item.ColorHex);
                worksheet.Cell(i + 2, 5).Value = item.Amount;
                worksheet.Cell(i + 2, 6).Value = item.Price;
                worksheet.Cell(i + 2, 6).Style.NumberFormat.Format = "0.00" + item.PriceCurrency;
            }

            worksheet.Columns(2, 6).AdjustToContents(10.0, 40.0);

            DateTime current = DateTime.Now;

            string saveLocation = SaveFolder + current.ToString("yyyy.MM.dd HH.mm.ss") + ".xlsx";

            Console.WriteLine("Saving your file to {0}", saveLocation);
            workbook.SaveAs(@saveLocation);

            if (open)
            {
                string           path  = Path.Combine(Environment.CurrentDirectory, saveLocation);
                ProcessStartInfo pInfo = new ProcessStartInfo(saveLocation)
                {
                    UseShellExecute = true
                };
                Process.Start(pInfo);
            }
        }
        private void MontarCorpo(GerarExcelGenericoCommand request, IXLWorksheet worksheet)
        {
            var bindingFlags = System.Reflection.BindingFlags.Instance |
                               System.Reflection.BindingFlags.NonPublic |
                               System.Reflection.BindingFlags.Public;


            var iLinha = 2;

            foreach (var item in request.ObjetoExportacao)
            {
                List <object> listValues   = item.GetType().GetFields(bindingFlags).Select(field => field.GetValue(item)).Where(value => value != null).ToList();
                var           iPropriedade = 0;

                foreach (var item1 in listValues)
                {
                    var celulaNome = $"{Number2String(iPropriedade, true)}{iLinha}";
                    CorpoFormataStylo(worksheet, celulaNome);
                    worksheet.Cells(celulaNome).Value = item1.ToString();
                    iPropriedade++;
                }
                iLinha++;
            }
        }
コード例 #10
0
        private int creteFiveReasonAllDesignt(int currentIndex, IXLWorksheet ws, DFourTable item)
        {
            int capsuleLastCellIndex;
            int capsuleStartCellIndex = currentIndex;

            ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden     1:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden1;
            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 1:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden1;

            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden     2:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden2;
            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 2:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden2;


            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden     3:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden3;
            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 3:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden3;


            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden     4:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden4;
            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 4:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden4;


            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Oluşum Neden     5:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.OlusumNeden5;
            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Yakalanama Neden 5:";
            ws.Cells("B" + currentIndex.ToString()).Value = item.YaklanamamaNeden5;

            capsuleLastCellIndex = currentIndex;
            capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, false);
            capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false);

            return(currentIndex);
        }
コード例 #11
0
        public FileStreamResult createExcellReport(string errorNo)
        {
            //Create an instance of ExcelEngine
            XLWorkbook   workbook = new XLWorkbook();
            IXLWorksheet ws       = workbook.Worksheets.Add("8D Rapor");


            var dOneTable   = db.DOneTable.Where(x => x.ErrorNo == errorNo).FirstOrDefault();
            var dTwoTable   = db.DTwoTable.Where(x => x.ErrorNo == errorNo).FirstOrDefault();
            var dThreeTable = db.DThreeTable.Where(x => x.ErrorNo == errorNo).ToList();
            var dFourTable  = db.DFourTable.Where(x => x.ErrorNo == errorNo).ToList();
            var dFiveTable  = db.DFiveTable.Where(x => x.ErrorNo == errorNo).ToList();
            var dSixTable   = db.DSixTable.Where(x => x.ErrorNo == errorNo).ToList();
            var dSevenTable = db.DSevenTable.Where(x => x.ErrorNo == errorNo).ToList();
            var dEightTable = db.DEightTable.Where(x => x.ErrorNo == x.ErrorNo).FirstOrDefault();
            var errorList   = db.ErrorListTable.Where(x => x.ErrorNo == errorNo).FirstOrDefault();

            //-----------------------------------------------------------------


            // WorkSheet Settings
            ws.Column(1).Width         = 18;
            ws.PageSetup.ShowGridlines = false; // halen gösteriyor



            // Başlık tasarımı
            int currentIndex = 1;

            currentIndex = setBigHeaderWithColor("8D Rapor", XLColor.YellowMunsell, currentIndex, ws, 3, false);

            // tablo elemanları atanmaya başlarız

            int capsuleStartCellIndex;
            int capsuleLastCellIndex;

            // 1. Kısım--------------------------------------------------
            currentIndex++;
            capsuleStartCellIndex             = currentIndex;
            ws.Cell("A" + currentIndex).Value = "Hata Numarası";
            ws.Cell("B" + currentIndex).Value = errorNo;

            currentIndex++;
            ws.Cell("A" + currentIndex).Value = "Oluşturma Tarihi";
            ws.Cell("B" + currentIndex).Value = Convert.ToDateTime(errorList.CreateDate.ToString()).ToShortDateString();

            currentIndex++;
            ws.Cell("A" + currentIndex).Value = "Kapanma Tarihi";
            ws.Cell("B" + currentIndex).Value = Convert.ToDateTime(errorList.CloseDate.ToString()).ToShortDateString();

            capsuleLastCellIndex = currentIndex;

            capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true);  // header
            capsuleDesign("B", capsuleStartCellIndex, "E", capsuleLastCellIndex, ws, false); // text

            ws.Cell("F" + capsuleStartCellIndex).Value = "Müşteri";
            ws.Cell("G" + capsuleStartCellIndex).Value = errorList.Costumer;

            capsuleDesignChangeColor("F", capsuleStartCellIndex, "F", capsuleStartCellIndex, ws, true); // Header
            capsuleDesignChangeColor("G", capsuleStartCellIndex, "H", capsuleStartCellIndex, ws, false);
            capsuleDesignChangeColor("F", capsuleStartCellIndex + 1, "H", capsuleLastCellIndex, ws, false);


            currentIndex++; // boşluk bırak
            // 2. Kısım-----------------------------------------------

            currentIndex++; // yeni ndexten başla
            capsuleStartCellIndex = currentIndex;
            ws.Cell("A" + currentIndex.ToString()).Value = "Parça Numarası ";
            ws.Cell("B" + currentIndex.ToString()).Value = errorList.PartNumber == null ? string.Empty : errorList.PartNumber.ToString();

            currentIndex++;
            ws.Cell("A" + currentIndex.ToString()).Value = "Referans Numarası";
            ws.Cell("B" + currentIndex.ToString()).Value = errorList.ReferenceNumber.ToString();

            currentIndex++;
            ws.Cell("A" + currentIndex.ToString()).Value = "Parça Adı";
            ws.Cell("B" + currentIndex.ToString()).Value = errorList.PartName == null ? string.Empty : errorList.PartName.ToString();

            currentIndex++;
            ws.Cell("A" + currentIndex.ToString()).Value = "Hata Kodu";
            ws.Cell("B" + currentIndex.ToString()).Value = errorList.ErrorCode.ToString();

            currentIndex++;
            ws.Cell("A" + currentIndex.ToString()).Value = "Hata Tanımı";
            ws.Cell("B" + currentIndex.ToString()).Value = errorList.ErrorDefine;

            currentIndex++;
            ws.Cell("A" + currentIndex.ToString()).Value = "Hata Açıklama";
            ws.Cell("B" + currentIndex.ToString()).Value = errorList.ErrorDescription;
            capsuleLastCellIndex = currentIndex;


            capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true);  // header
            capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text


            currentIndex++; // boşluk bırak
            // 3. Kısım-----------------------------------------------------
            currentIndex++;
            crateMiniHeader("D1- Ekip Üyeleri", currentIndex, ws);

            currentIndex++;
            capsuleStartCellIndex = currentIndex;
            ws.Cells("A" + currentIndex.ToString()).Value = "Takım Lideri";
            ws.Cells("B" + currentIndex.ToString()).Value = dOneTable.TeamLeader;

            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Takım Üyeleri";
            ws.Cells("B" + currentIndex.ToString()).Value = dOneTable.TeamMembers;


            capsuleLastCellIndex = currentIndex;
            capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true);  // header
            capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text



            currentIndex++; // boşluk bırak
            // 4. Kısım-----------------------------------------------------
            currentIndex++;
            crateMiniHeader("D2- Hata Tanımı", currentIndex, ws);

            currentIndex++;
            capsuleStartCellIndex = currentIndex;
            ws.Cells("A" + currentIndex.ToString()).Value = "Hata Tanımı";
            ws.Cells("B" + currentIndex.ToString()).Value = dTwoTable.ErrorDefine;

            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Açıklaması";
            ws.Cells("B" + currentIndex.ToString()).Value = dTwoTable.ErrorDescription;

            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "İşlemi Yapan";
            ws.Cells("B" + currentIndex.ToString()).Value = dTwoTable.Person;

            currentIndex++;
            ws.Cells("A" + currentIndex.ToString()).Value = "Tarih";
            ws.Cells("B" + currentIndex.ToString()).Value = dTwoTable.Date.ToShortDateString();



            capsuleLastCellIndex = currentIndex;
            capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true);  // header
            capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text



            currentIndex++; // boşluk bırak
            // 5. Kısım-----------------------------------------------------
            currentIndex++;
            crateMiniHeader("D3 - Acil Önlemler", currentIndex, ws);


            int counter = 0;

            foreach (var item in dThreeTable)
            {
                counter++;                                                             // 1. ,2.,3... Aksiyonlar
                currentIndex++;
                createActionHeader("Aksiyon " + counter.ToString(), currentIndex, ws); // aksiyon yazı başlık teması
                currentIndex++;
                capsuleStartCellIndex = currentIndex;
                ws.Cells("A" + currentIndex.ToString()).Value = "Aksiyon";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Action;


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Uygulama Tarihi";
                ws.Cells("B" + currentIndex.ToString()).Value = item.TransactionDate.ToShortDateString();


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Tarih";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Date.ToShortDateString();


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "İşlemi Yapan";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Person;


                capsuleLastCellIndex = currentIndex;
                capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true);  // header
                capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text

                currentIndex++;
            }

            currentIndex++; // boşluk bırakılır

            // 6. Kısım
            crateMiniHeader("D4 - Hata Oluşma Nedeni", currentIndex, ws);
            // Makine Alanı
            currentIndex++;
            createActionHeader("Makine", currentIndex, ws);
            foreach (var item in dFourTable)
            {
                if (item.Machine != null)
                {
                    currentIndex++;
                    ws.Cells("A" + currentIndex.ToString()).Value = item.Machine;
                    capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
                    currentIndex++;
                    currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
                }
            }

            // Methot Alanı
            currentIndex++;
            createActionHeader("Methot", currentIndex, ws);
            foreach (var item in dFourTable)
            {
                if (item.Method != null)
                {
                    currentIndex++;
                    ws.Cells("A" + currentIndex.ToString()).Value = item.Method;
                    capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
                    currentIndex++;
                    currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
                }
            }

            // Malzeme Alanı
            currentIndex++;
            createActionHeader("Malzeme", currentIndex, ws);
            foreach (var item in dFourTable)
            {
                if (item.Material != null)
                {
                    currentIndex++;
                    ws.Cells("A" + currentIndex.ToString()).Value = item.Material;
                    capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
                    currentIndex++;
                    currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
                }
            }

            // İnsan Alanı
            currentIndex++;
            createActionHeader("İnsan", currentIndex, ws);
            foreach (var item in dFourTable)
            {
                if (item.Men != null)
                {
                    currentIndex++;
                    ws.Cells("A" + currentIndex.ToString()).Value = item.Men;
                    capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
                    currentIndex++;
                    currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
                }
            }

            // Cevre Alanı
            currentIndex++;
            createActionHeader("Cevre", currentIndex, ws);
            foreach (var item in dFourTable)
            {
                if (item.Monde != null)
                {
                    currentIndex++;
                    ws.Cells("A" + currentIndex.ToString()).Value = item.Monde;
                    capsuleDesign("A", currentIndex, "H", currentIndex, ws, false);
                    currentIndex++;
                    currentIndex = creteFiveReasonAllDesignt(currentIndex, ws, item);
                }
            }


            currentIndex++;
            // 7. Kısım
            currentIndex++;
            crateMiniHeader("D5 - Öngörülen Düzletici Önlemler", currentIndex, ws);
            counter = 0;
            foreach (var item in dFiveTable)
            {
                counter++;                                                           // 1. ,2.,3... Aksiyonlar
                currentIndex++;
                createActionHeader("Önlem " + counter.ToString(), currentIndex, ws); // aksiyon yazı başlık teması
                currentIndex++;
                capsuleStartCellIndex = currentIndex;
                ws.Cells("A" + currentIndex.ToString()).Value = "Hata Tipi";
                ws.Cells("B" + currentIndex.ToString()).Value = item.ErrorType;


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Önlem";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Precautions;


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Termin Tarihi";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Deadline.ToShortDateString();


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "İşlemi Yapan";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Person;


                capsuleLastCellIndex = currentIndex;
                capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true);  // header
                capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text

                currentIndex++;
            }


            // 8. Kısım
            currentIndex++;
            crateMiniHeader("D6 - Uygulanan Düzletici Önlemler", currentIndex, ws);
            counter = 0;
            foreach (var item in dSixTable)
            {
                counter++;                                                           // 1. ,2.,3... Aksiyonlar
                currentIndex++;
                createActionHeader("Önlem " + counter.ToString(), currentIndex, ws); // aksiyon yazı başlık teması
                currentIndex++;
                capsuleStartCellIndex = currentIndex;
                ws.Cells("A" + currentIndex.ToString()).Value = "Hata Tipi";
                ws.Cells("B" + currentIndex.ToString()).Value = item.ErrorType;


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Düzeltici Önlem";
                ws.Cells("B" + currentIndex.ToString()).Value = item.MeasuresTaken;


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Uygulama Tarihi";
                ws.Cells("B" + currentIndex.ToString()).Value = item.ProcessDate.ToShortDateString();


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Kalan Zaman";
                ws.Cells("B" + currentIndex.ToString()).Value = item.TimeRemaining;


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Gecikme Sebebi";
                ws.Cells("B" + currentIndex.ToString()).Value = item.DelayReason;

                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "İşlemi Yapan";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Person;


                capsuleLastCellIndex = currentIndex;
                capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true);  // header
                capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text

                currentIndex++;
            }



            // 9. Kısım
            currentIndex++;
            crateMiniHeader("D7 - Hata Tekrarını Önleyici Tedbirler", currentIndex, ws);
            counter = 0;
            foreach (var item in dSevenTable)
            {
                counter++;                                                            // 1. ,2.,3... Aksiyonlar
                currentIndex++;
                createActionHeader("Tedbir " + counter.ToString(), currentIndex, ws); // aksiyon yazı başlık teması
                currentIndex++;
                capsuleStartCellIndex = currentIndex;
                ws.Cells("A" + currentIndex.ToString()).Value = "Tedbir";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Prevention;


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Sorumlu";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Responsible;


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Uygulama Tarihi";
                ws.Cells("B" + currentIndex.ToString()).Value = item.ProcessDate.ToShortDateString();


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Tarih";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Date.ToShortDateString();


                currentIndex++;
                ws.Cells("A" + currentIndex.ToString()).Value = "Firma";
                ws.Cells("B" + currentIndex.ToString()).Value = item.Company;



                capsuleLastCellIndex = currentIndex;
                capsuleDesign("A", capsuleStartCellIndex, "A", capsuleLastCellIndex, ws, true);  // header
                capsuleDesign("B", capsuleStartCellIndex, "H", capsuleLastCellIndex, ws, false); // text

                currentIndex++;
            }


            // Hata Kabul Veya Red
            if (dEightTable.RejectionReason == null) // hata onay
            {
                currentIndex = setBigHeaderWithColor("Hata Kabul Edildi", XLColor.LightGreen, currentIndex, ws, 3, false);
            }
            else
            {
                currentIndex = setBigHeaderWithColor("Hata Reddedildi", XLColor.Red, currentIndex, ws, 3, false);
            }

            currentIndex++;
            var imagePath = AppDomain.CurrentDomain.BaseDirectory + "images/inanSoftLogo.png";


            importPicture(ws, "G", currentIndex, imagePath);



            MemoryStream MS = new MemoryStream();

            workbook.SaveAs(MS);
            MS.Position = 0;



            return(new FileStreamResult(MS, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                FileDownloadName = errorNo + "_8DRapor.xlsx"
            });
        }
コード例 #12
0
        public void TestDelete()
        {
            // Deleting with moving cells up
            XLWorkbook    wb          = InitWorkBookForDeleteRangeTest();
            IXLWorksheet  ws          = wb.Worksheet("Test");
            IXLNamedRange parentRange = ws.NamedRange("Parent");
            IXLNamedRange childRange  = ws.NamedRange("Child");

            Assert.AreEqual(2, ws.NamedRanges.Count());
            var excelReport       = Substitute.For <object>();
            var templateProcessor = Substitute.For <ITemplateProcessor>();

            var panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, excelReport, templateProcessor)
                }
            };

            panel.Delete();

            IXLCell rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
            IXLCell rangeEndCell   = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
            IXLCell belowCell1     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
            IXLCell belowCell2     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
            IXLCell rightCell1     = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
            IXLCell rightCell2     = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
            IXLCell aboveCell1     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
            IXLCell aboveCell2     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
            IXLCell leftCell1      = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
            IXLCell leftCell2      = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");

            Assert.IsNull(rangeStartCell);
            Assert.IsNull(rangeEndCell);
            Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
            Assert.AreEqual(belowCell1, ws.Cell(6, 6));
            Assert.AreEqual(belowCell2, ws.Cell(10, 8));
            Assert.AreEqual(rightCell1, ws.Cell(7, 8));
            Assert.AreEqual(rightCell2, ws.Cell(5, 8));
            Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
            Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
            Assert.AreEqual(leftCell1, ws.Cell(7, 4));
            Assert.AreEqual(leftCell2, ws.Cell(10, 4));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            // Deleting with moving the row up
            wb          = InitWorkBookForDeleteRangeTest();
            ws          = wb.Worksheet("Test");
            parentRange = ws.NamedRange("Parent");
            childRange  = ws.NamedRange("Child");
            Assert.AreEqual(2, ws.NamedRanges.Count());

            panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, excelReport, templateProcessor)
                },
                ShiftType = ShiftType.Row,
            };
            panel.Delete();

            rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
            rangeEndCell   = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
            belowCell1     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
            belowCell2     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
            rightCell1     = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
            rightCell2     = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
            aboveCell1     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
            aboveCell2     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
            leftCell1      = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
            leftCell2      = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");

            Assert.IsNull(rangeStartCell);
            Assert.IsNull(rangeEndCell);
            Assert.IsNull(leftCell1);
            Assert.IsNull(rightCell1);
            Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
            Assert.AreEqual(belowCell1, ws.Cell(6, 6));
            Assert.AreEqual(belowCell2, ws.Cell(6, 8));
            Assert.AreEqual(rightCell2, ws.Cell(5, 8));
            Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
            Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
            Assert.AreEqual(leftCell2, ws.Cell(6, 4));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            // Deleting with moving cells left
            wb          = InitWorkBookForDeleteRangeTest();
            ws          = wb.Worksheet("Test");
            parentRange = ws.NamedRange("Parent");
            childRange  = ws.NamedRange("Child");
            Assert.AreEqual(2, ws.NamedRanges.Count());

            panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, excelReport, templateProcessor)
                },
                Type = PanelType.Horizontal
            };
            panel.Delete();

            rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
            rangeEndCell   = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
            belowCell1     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
            belowCell2     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
            rightCell1     = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
            rightCell2     = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
            aboveCell1     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
            aboveCell2     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
            leftCell1      = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
            leftCell2      = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");

            Assert.IsNull(rangeStartCell);
            Assert.IsNull(rangeEndCell);
            Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
            Assert.AreEqual(belowCell1, ws.Cell(10, 6));
            Assert.AreEqual(belowCell2, ws.Cell(10, 8));
            Assert.AreEqual(rightCell1, ws.Cell(7, 5));
            Assert.AreEqual(rightCell2, ws.Cell(5, 8));
            Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
            Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
            Assert.AreEqual(leftCell1, ws.Cell(7, 4));
            Assert.AreEqual(leftCell2, ws.Cell(10, 4));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            // Deleting with moving the column left
            wb          = InitWorkBookForDeleteRangeTest();
            ws          = wb.Worksheet("Test");
            parentRange = ws.NamedRange("Parent");
            childRange  = ws.NamedRange("Child");
            Assert.AreEqual(2, ws.NamedRanges.Count());

            panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, excelReport, templateProcessor)
                },
                Type      = PanelType.Horizontal,
                ShiftType = ShiftType.Row,
            };
            panel.Delete();

            rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
            rangeEndCell   = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
            belowCell1     = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "BelowCell_1");
            belowCell2     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
            rightCell1     = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
            rightCell2     = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
            aboveCell1     = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "AboveCell_1");
            aboveCell2     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
            leftCell1      = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
            leftCell2      = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");

            Assert.IsNull(rangeStartCell);
            Assert.IsNull(rangeEndCell);
            Assert.IsNull(aboveCell1);
            Assert.IsNull(belowCell1);
            Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
            Assert.AreEqual(belowCell2, ws.Cell(10, 5));
            Assert.AreEqual(rightCell1, ws.Cell(7, 5));
            Assert.AreEqual(rightCell2, ws.Cell(5, 5));
            Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
            Assert.AreEqual(leftCell1, ws.Cell(7, 4));
            Assert.AreEqual(leftCell2, ws.Cell(10, 4));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            // Deleting without any shift
            wb          = InitWorkBookForDeleteRangeTest();
            ws          = wb.Worksheet("Test");
            parentRange = ws.NamedRange("Parent");
            childRange  = ws.NamedRange("Child");
            Assert.AreEqual(2, ws.NamedRanges.Count());

            panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
            {
                Children = new List <IExcelPanel> {
                    new ExcelNamedPanel(childRange, excelReport, templateProcessor)
                },
                ShiftType = ShiftType.NoShift,
            };
            panel.Delete();

            rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
            rangeEndCell   = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
            belowCell1     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
            belowCell2     = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
            rightCell1     = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
            rightCell2     = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
            aboveCell1     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
            aboveCell2     = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
            leftCell1      = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
            leftCell2      = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");

            Assert.IsNull(rangeStartCell);
            Assert.IsNull(rangeEndCell);
            Assert.AreEqual(XLBorderStyleValues.None, parentRange.Ranges.ElementAt(0).FirstCell().Style.Border.TopBorder);
            Assert.AreEqual(XLBorderStyleValues.None, parentRange.Ranges.ElementAt(0).Style.Border.BottomBorder);
            Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
            Assert.AreEqual(belowCell1, ws.Cell(10, 6));
            Assert.AreEqual(belowCell2, ws.Cell(10, 8));
            Assert.AreEqual(rightCell1, ws.Cell(7, 8));
            Assert.AreEqual(rightCell2, ws.Cell(5, 8));
            Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
            Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
            Assert.AreEqual(leftCell1, ws.Cell(7, 4));
            Assert.AreEqual(leftCell2, ws.Cell(10, 4));
            Assert.AreEqual(0, ws.NamedRanges.Count());

            //wb.SaveAs("test.xlsx");
        }
コード例 #13
0
    XLWorkbook CreateXlWorkBook(DataSet ImportDs, XLWorkbook wb)
    {
        try
        {
            int Index = 0;

            foreach (DataTable dt in ImportDs.Tables)
            {
                if (dt.Rows.Count <= 0)
                {
                    dt.Columns.Add("No records", typeof(string));
                    dt.Rows.Add("No records");
                }
                if (Index % 2 != 0 || Index == 0)
                {
                    //Add DataTable as Worksheet.

                    IXLWorksheet worksheet = wb.Worksheets.Add(dt);
                    worksheet.Tables.FirstOrDefault().ShowAutoFilter = false;

                    /////      Add Header        ///
                    worksheet.Row(1).InsertRowsAbove(1);
                    worksheet.Row(2).InsertRowsAbove(1);
                    worksheet.Row(3).InsertRowsAbove(1);

                    int UsedCol = worksheet.ColumnsUsed().Count();
                    // worksheet.Row(1).Cell(UsedCol).Hyperlink.Cell.Value = "Help";
                    // worksheet.Row(1).Cell(UsedCol).Hyperlink.InternalAddress = "www.gstsaathiaccounts.in";
                    // worksheet.Row(1).Cell(UsedCol).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);


                    // Put a Blue Color in a 2 Rows Of Cells
                    foreach (var c in Enumerable.Range(1, UsedCol))
                    {
                        //worksheet.Cell(2, c).Style.Border.
                        worksheet.Cell(2, c).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
                        worksheet.Cell(2, c).Style.Font.FontColor       = XLColor.White;
                        //   worksheet.Cell(2, c).Style.Border.OutsideBorderColor = XLColor.Black;
                    }

                    // Put a Orange Color in a 4 Rows Of Cells Of Headers of Data
                    foreach (var c in Enumerable.Range(1, UsedCol))
                    {
                        worksheet.Cell(4, c).Style.Fill.BackgroundColor = XLColor.FromArgb(250, 191, 143);
                        worksheet.Cell(4, c).Style.Font.FontColor       = XLColor.Black;
                        // worksheet.Cell(4, c).Style.Border.OutsideBorderColor = XLColor.Black;
                    }

                    //  Excel Name Row 1 Cell 1;
                    //worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
                    //worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.White;
                    worksheet.Cells("A1").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 112, 192);
                    worksheet.Cells("A1").Style.Font.FontColor       = XLColor.White;

                    #region b2b
                    if (worksheet.Name == "b2b")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For B2B(4)";
                        worksheet.Cell("A2").Value     = "No. of Recipients";
                        worksheet.Cell("B2").Value     = "No. of Invoices";
                        worksheet.Cell("D2").Value     = "Total Invoice Value";
                        worksheet.Cell("J2").Value     = "Total Taxable Value";
                        worksheet.Cell("K2").Value     = "Total Cess";

                        //if (dt.Rows.Count > 0)
                        //{
                        //DataView view = new DataView(dt);
                        worksheet.Cell("A3").Value = ImportDs.Tables[2].Rows[0]["TotalParty"].ToString();
                        worksheet.Cell("B3").Value = ImportDs.Tables[2].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[2].Rows[0]["invoiceValue"].ToString();
                        worksheet.Cell("J3").Value = ImportDs.Tables[2].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("K3").Value = ImportDs.Tables[2].Rows[0]["Cess"].ToString();

                        //view.ToTable(true, "GSTIN/UIN of Recipient").Rows.Count;
                        //view.ToTable(true, "Invoice Number").Rows.Count;
                        //dt.Compute("SUM([Invoice Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                        //}
                    }
                    #endregion

                    #region b2cl
                    if (worksheet.Name == "b2cl")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For B2CL(5)";
                        worksheet.Cell("A2").Value     = "No. of Invoices";
                        worksheet.Cell("C2").Value     = "Total Invoice Value";
                        worksheet.Cell("F2").Value     = "Total Taxable Value";
                        worksheet.Cell("G2").Value     = "Total Cess";


                        //DataView view = new DataView(dt);
                        worksheet.Cell("A3").Value = ImportDs.Tables[4].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("C3").Value = ImportDs.Tables[4].Rows[0]["invoiceValue"].ToString();
                        worksheet.Cell("F3").Value = ImportDs.Tables[4].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("G3").Value = ImportDs.Tables[4].Rows[0]["Cess"].ToString();
                        //view.ToTable(true, "Invoice Number").Rows.Count;
                        //dt.Compute("SUM([Invoice Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region b2cs
                    if (worksheet.Name == "b2cs")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For B2CS(7)";

                        worksheet.Cell("D2").Value = "Total Taxable Value";
                        worksheet.Cell("E2").Value = "Total Cess";


                        //DataView view = new DataView(dt);

                        worksheet.Cell("D3").Value = ImportDs.Tables[6].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("E3").Value = ImportDs.Tables[6].Rows[0]["Cess"].ToString();
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region cdnr
                    if (worksheet.Name == "cdnr")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For CDNR(9B)";
                        worksheet.Cell("A2").Value     = "No. of Recipients";
                        worksheet.Cell("B2").Value     = "No. of Invoices";
                        worksheet.Cell("D2").Value     = "No. of Notes/Vouchers";
                        worksheet.Cell("I2").Value     = "Total Note/Refund Voucher Value";
                        worksheet.Cell("K2").Value     = "Total Taxable Value";
                        worksheet.Cell("L2").Value     = "Total Cess";



                        //DataView view = new DataView(dt);
                        worksheet.Cell("A3").Value = ImportDs.Tables[8].Rows[0]["TotalReciepent"].ToString();
                        worksheet.Cell("B3").Value = ImportDs.Tables[8].Rows[0]["AmdTotalInvoice"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[8].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("I3").Value = ImportDs.Tables[8].Rows[0]["invoiceValue"].ToString();
                        worksheet.Cell("K3").Value = ImportDs.Tables[8].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("L3").Value = ImportDs.Tables[8].Rows[0]["Cess"].ToString();

                        //view.ToTable(true, "GSTIN/UIN of Recipient").Rows.Count;
                        //view.ToTable(true, "Invoice/Advance Receipt Number").Rows.Count;
                        //view.ToTable(true, "Note/Refund Voucher Number").Rows.Count;
                        //dt.Compute("SUM([Note/Refund Voucher Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region cdnur
                    if (worksheet.Name == "cdnur")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For CDNUR(9B)";
                        worksheet.Cell("B2").Value     = "No. of Notes/Vouchers";
                        worksheet.Cell("E2").Value     = "No. of Invoices";
                        worksheet.Cell("I2").Value     = "Total Note Value";
                        worksheet.Cell("K2").Value     = "Total Taxable Value";
                        worksheet.Cell("L2").Value     = "Total Cess";


                        //DataView view = new DataView(dt);
                        worksheet.Cell("B3").Value = ImportDs.Tables[10].Rows[0]["AmdTotalInvoice"].ToString();
                        worksheet.Cell("E3").Value = ImportDs.Tables[10].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("I3").Value = ImportDs.Tables[10].Rows[0]["invoiceValue"].ToString();
                        worksheet.Cell("K3").Value = ImportDs.Tables[10].Rows[0]["Taxableval"].ToString();
                        worksheet.Cell("L3").Value = ImportDs.Tables[10].Rows[0]["Cess"].ToString();

                        // view.ToTable(true, "Note/Refund Voucher Number").Rows.Count;
                        //view.ToTable(true, "Invoice/Advance Receipt Number").Rows.Count;
                        //dt.Compute("SUM([Note/Refund Voucher Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region exp
                    if (worksheet.Name == "exp")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For EXP(6)";
                        worksheet.Cell("B2").Value     = "No. of Invoices";
                        worksheet.Cell("D2").Value     = "Total Note Value";
                        worksheet.Cell("F2").Value     = "No. of Shipping Bill";
                        worksheet.Cell("I2").Value     = "Total Taxable Value";


                        //DataView view = new DataView(dt);
                        worksheet.Cell("B3").Value = ImportDs.Tables[12].Rows[0]["TotalInvoice"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[12].Rows[0]["InvoiceValue"].ToString();
                        worksheet.Cell("F3").Value = ImportDs.Tables[12].Rows[0]["ShippedInvoice"].ToString();
                        worksheet.Cell("I3").Value = ImportDs.Tables[12].Rows[0]["Taxablevalue"].ToString();

                        //view.ToTable(true, "Invoice Number").Rows.Count;
                        //dt.Compute("SUM([Invoice Value])", string.Empty);
                        //view.ToTable(true, "Shipping Bill Number").Rows.Count;
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                    }
                    #endregion

                    #region at
                    if (worksheet.Name == "at")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For Advance Adjusted (11B)";
                        worksheet.Cell("C2").Value     = "Total Advance Received";
                        worksheet.Cell("D2").Value     = "Total Cess";


                        worksheet.Cell("C3").Value = ImportDs.Tables[14].Rows[0]["GrossReceived"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[14].Rows[0]["CessAmt"].ToString();
                        //dt.Compute("SUM([Gross Advance Received])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);
                    }
                    #endregion

                    #region atadj
                    if (worksheet.Name == "atadj")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For Advance Adjusted (11B)";
                        worksheet.Cell("C2").Value     = "Total Advance Adjusted";
                        worksheet.Cell("D2").Value     = "Total Cess";


                        worksheet.Cell("C3").Value = ImportDs.Tables[16].Rows[0]["GrossReceived"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[16].Rows[0]["CessAmt"].ToString();
                    }
                    #endregion

                    #region exemp
                    if (worksheet.Name == "exemp")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For Nil rated, exempted and non GST outward supplies (8)";

                        worksheet.Cell("B2").Value = "Total Nil Rated Supplies";
                        worksheet.Cell("C2").Value = "Total Exempted Supplies";
                        worksheet.Cell("D2").Value = "Total Non-GST Supplies";


                        //DataView view = new DataView(dt);
                        worksheet.Cell("B3").Value = ImportDs.Tables[18].Rows[0]["NillRated"].ToString();
                        worksheet.Cell("C3").Value = ImportDs.Tables[18].Rows[0]["ExmTotal"].ToString();
                        worksheet.Cell("D3").Value = ImportDs.Tables[18].Rows[0]["NonGstTotal"].ToString();

                        //dt.Compute("SUM([Nil Rated Supplies])", string.Empty);
                        //dt.Compute("SUM([Exempted (other than nil rated/non GST supply )])", string.Empty);
                        //dt.Compute("SUM([Non-GST supplies])", string.Empty);
                    }
                    #endregion

                    #region hsn
                    if (worksheet.Name == "hsn")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary For HSN(12)";
                        worksheet.Cell("A2").Value     = "No. of HSN";
                        worksheet.Cell("E2").Value     = "Total Value";
                        worksheet.Cell("F2").Value     = "Total Taxable Value";
                        worksheet.Cell("G2").Value     = "Total Integrated Tax";
                        worksheet.Cell("H2").Value     = "Total Central Tax";
                        worksheet.Cell("I2").Value     = "Total State/UT Tax";
                        worksheet.Cell("J2").Value     = "Total Cess";

                        //if (dt.Rows.Count > 0)
                        //{
                        // DataView view = new DataView(dt);
                        worksheet.Cell("A3").Value = ImportDs.Tables[20].Rows[0]["HSN"].ToString();
                        worksheet.Cell("E3").Value = ImportDs.Tables[20].Rows[0]["TotalValue"].ToString();
                        worksheet.Cell("F3").Value = ImportDs.Tables[20].Rows[0]["TotalTaxValue"].ToString();
                        worksheet.Cell("G3").Value = ImportDs.Tables[20].Rows[0]["IGSTAmt"].ToString();
                        worksheet.Cell("H3").Value = ImportDs.Tables[20].Rows[0]["CGSTAmt"].ToString();
                        worksheet.Cell("I3").Value = ImportDs.Tables[20].Rows[0]["SGSTAmt"].ToString();
                        worksheet.Cell("J3").Value = ImportDs.Tables[20].Rows[0]["Cess"].ToString();

                        //view.ToTable(true, "HSN").Rows.Count;
                        //dt.Compute("SUM([Total Value])", string.Empty);
                        //dt.Compute("SUM([Taxable Value])", string.Empty);
                        //dt.Compute("SUM([Integrated Tax Amount])", string.Empty);
                        //dt.Compute("SUM([Central Tax Amount])", string.Empty);
                        //dt.Compute("SUM([State/UT Tax Amount])", string.Empty);
                        //dt.Compute("SUM([Cess Amount])", string.Empty);

                        worksheet.Column("D").CellsUsed().Style.NumberFormat.Format = "0.00";
                        worksheet.Row(3).Cell("E").Style.NumberFormat.Format        = "0.00";
                        worksheet.Row(3).Cell("F").Style.NumberFormat.Format        = "0.00";

                        //var validE3 = worksheet.Row(3).Cell("E").DataValidation;
                        //validE3.Decimal.GetType();
                        //validE3.ErrorStyle = XLErrorStyle.Warning;
                        //validE3.ErrorTitle = "Number out of range";
                        //validE3.ErrorMessage = "This cell only allows the number 2.";

                        //worksheet.Cell("E3").Style.NumberFormat.Format = "0.00"; //"#,##0.00";
                        //worksheet.Cell("F3").Style.NumberFormat.Format = "0.00"; //"#,##0.00";
                        //worksheet.Range("D4", "D" + worksheet.RowsUsed().Count()).Style.NumberFormat.Format = "0.00";
                        //worksheet.CellsUsed("A").Style.NumberFormat.Format = "0.00";
                        //worksheet.Columns(4,6).CellsUsed().Style.NumberFormat.Format = "0.00";
                        // }
                    }
                    #endregion

                    #region docs
                    if (worksheet.Name == "docs")
                    {
                        worksheet.Row(1).Cell(1).Value = "Summary of documents issued during the tax period (13)";
                        worksheet.Cell("D2").Value     = "Total Number";
                        worksheet.Cell("E2").Value     = "Total Cancelled";


                        // DataView view = new DataView(dt);
                        worksheet.Cell("D3").Value = ImportDs.Tables[22].Rows[0]["TotalNumber"].ToString();
                        worksheet.Cell("E3").Value = ImportDs.Tables[22].Rows[0]["Cancelled"].ToString();

                        //dt.Compute("SUM([Total Number])", string.Empty);
                        //dt.Compute("SUM([Cancelled])", string.Empty);
                    }
                    #endregion
                }
                Index++;
            }
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('" + ex.Message + "')</script>");
            throw ex;
        }
        return(wb);
    }
コード例 #14
0
        private static void WriteHeaders <T>(IXLWorksheet worksheet) where T : Player
        {
            worksheet.Cell("A2").Value = "Player";
            worksheet.Cell("C1").Value = "Game";
            worksheet.Cell("C2").Value = "G";
            if (typeof(T) == typeof(PassingPlayer))
            {
                worksheet.Cell("B2").Value = "Cmp";
                worksheet.Cell("D2").Value = "Att";
                worksheet.Cell("E2").Value = "Yds";
                worksheet.Cell("F2").Value = "TD";
                worksheet.Cell("G2").Value = "Int";
                worksheet.Cell("H2").Value = "Sk";
                worksheet.Cell("I2").Value = "Team";
            }
            if (typeof(T) == typeof(KickingPlayer))
            {
                worksheet.Cells("B1, H1").Value = "Punt";
                worksheet.Cells("D1:G1").Value  = "Scor";
                worksheet.Cell("B2").Value      = "Pnt";
                worksheet.Cell("D2").Value      = "XPM";
                worksheet.Cell("E2").Value      = "XPA";
                worksheet.Cell("F2").Value      = "FGM";
                worksheet.Cell("G2").Value      = "FGA";
                worksheet.Cell("H2").Value      = "Yds";
                worksheet.Cell("I2").Value      = "Team";
            }
            if (typeof(T) == typeof(DefensePlayer))
            {
                worksheet.Cell("B1").Value = "Def";
                worksheet.Cell("B2").Value = "Int";
                worksheet.Cell("D2").Value = "Sk";

                worksheet.Cells("E1:G1").Value = "Tack";
                worksheet.Cell("E2").Value     = "Solo";
                worksheet.Cell("F2").Value     = "Ast";
                worksheet.Cell("G2").Value     = "TFL";

                worksheet.Cells("H1:I1").Value = "Def";
                worksheet.Cell("H2").Value     = "TD";
                worksheet.Cell("I2").Value     = "PD";

                worksheet.Cells("J1:M1").Value = "Fumb";
                worksheet.Cell("J2").Value     = "FR";
                worksheet.Cell("K2").Value     = "Yds";
                worksheet.Cell("L2").Value     = "TD";
                worksheet.Cell("M2").Value     = "FF";
                worksheet.Cell("N2").Value     = "Team";
            }
            if (typeof(T) == typeof(ReceivingPlayer))
            {
                worksheet.Cell("B2").Value = "Rec";
                worksheet.Cell("D2").Value = "Yds";
                worksheet.Cell("E2").Value = "TD";
                worksheet.Cell("F2").Value = "Team";
            }
            if (typeof(T) == typeof(ReturningPlayer))
            {
                worksheet.Cells("B1, D1:E1").Value = "Kick";
                worksheet.Cells("F1:H1").Value     = "Punt";

                worksheet.Cell("B2").Value = "Rt";
                worksheet.Cell("D2").Value = "Yds";
                worksheet.Cell("E2").Value = "TD";
                worksheet.Cell("F2").Value = "Ret";
                worksheet.Cell("G2").Value = "Yds";
                worksheet.Cell("H2").Value = "TD";
                worksheet.Cell("I2").Value = "Team";
            }
            if (typeof(T) == typeof(RushingPlayer))
            {
                worksheet.Cells("B1, D1:E1").Value = "Rush";
                worksheet.Cell("B2").Value         = "Att";
                worksheet.Cell("D1").Value         = "Rush";
                worksheet.Cell("D2").Value         = "Yds";
                worksheet.Cell("E1").Value         = "Rush";
                worksheet.Cell("E2").Value         = "TD";
                worksheet.Cell("F2").Value         = "Fmb";
                worksheet.Cell("G2").Value         = "Team";
            }
        }
コード例 #15
0
        public void SaveExamSheet(Object[] examGeneration, string fileName)
        {
            List <Exam> exams          = (List <Exam>)examGeneration[0];
            List <Exam> unassigned     = (List <Exam>)examGeneration[1];
            List <Exam> availableRooms = (List <Exam>)examGeneration[2];

            XLWorkbook   doc   = new XLWorkbook();
            IXLWorksheet sheet = doc.Worksheets.Add("Exam Schedule");

            sheet.Column("A").Width    = 15;
            sheet.Column("B").Width    = 60;
            sheet.Column("C").Width    = 10;
            sheet.Columns("D:I").Width = 20;

            int row = 1;

            if (unassigned.Count != 0)
            {
                sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Crimson);
                sheet.Row(row).Style.Font.SetFontColor(XLColor.White);

                sheet.Cell("A" + row).Value = "There weren't enough rooms available to fit these exams.";

                row++;
                sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Crimson);
                sheet.Row(row).Style.Font.SetFontColor(XLColor.White);
                sheet.Row(row).Style.Font.SetBold();

                sheet.Cell("A" + row).Value = "Code";
                sheet.Cell("B" + row).Value = "Course Name";
                sheet.Cell("C" + row).Value = "Section";
                sheet.Cell("D" + row).Value = "Faculty";
                foreach (Exam datarow in unassigned)
                {
                    row++;
                    sheet.Cell("A" + row).Value = datarow.Code;
                    sheet.Cell("B" + row).Value = datarow.Name;
                    sheet.Cell("C" + row).Value = datarow.Section;
                    sheet.Cell("D" + row).Value = datarow.Faculty;
                }
                row++;
                sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Crimson);
                sheet.Row(row).Style.Font.SetFontColor(XLColor.White);

                sheet.Cell("A" + row).Value = "Here's a list of available rooms.";

                row++;
                sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Crimson);
                sheet.Row(row).Style.Font.SetFontColor(XLColor.White);
                sheet.Row(row).Style.Font.SetBold();

                sheet.Cell("D" + row).Value = "Faculty";
                sheet.Cell("F" + row).Value = "Room";
                sheet.Cell("G" + row).Value = "Day";
                sheet.Cell("H" + row).Value = "Time";
                foreach (Exam datarow in availableRooms)
                {
                    row++;
                    sheet.Cell("D" + row).Value = datarow.Faculty;
                    sheet.Cell("F" + row).Value = datarow.Room;
                    sheet.Cell("G" + row).Value = datarow.Day;
                    sheet.Cell("H" + row).Value = datarow.Start + " - " + datarow.End;
                }
                row++;
            }

            sheet.Cells("A" + row + ":I" + row).Style.Fill.SetBackgroundColor(XLColor.Charcoal);
            sheet.Row(row).Style.Font.SetFontColor(XLColor.White);
            sheet.Row(row).Style.Font.SetBold();

            sheet.Cell("A" + row).Value = "Code";
            sheet.Cell("B" + row).Value = "Course Name";
            sheet.Cell("C" + row).Value = "Section";
            sheet.Cell("D" + row).Value = "Faculty";
            sheet.Cell("E" + row).Value = "Proctor";
            sheet.Cell("F" + row).Value = "Room";
            sheet.Cell("G" + row).Value = "Day";
            sheet.Cell("H" + row).Value = "Time";
            sheet.Cell("I" + row).Value = "Duration";
            foreach (Exam datarow in exams)
            {
                row++;
                sheet.Cell("A" + row).Value = datarow.Code;
                sheet.Cell("B" + row).Value = datarow.Name;
                sheet.Cell("C" + row).Value = datarow.Section;
                sheet.Cell("D" + row).Value = datarow.Faculty;
                sheet.Cell("E" + row).Value = datarow.Proctor;
                sheet.Cell("F" + row).Value = datarow.Room;
                sheet.Cell("G" + row).Value = datarow.Day;
                sheet.Cell("H" + row).Value = datarow.Start + " - " + datarow.End;
                sheet.Cell("I" + row).Value = datarow.Duration;
            }

            doc.SaveAs(AppDomain.CurrentDomain.GetData("DataDirectory").ToString() + "/sheetStorage/Exam_" + fileName);
        }