コード例 #1
0
        public IActionResult DownloadExcelEmprestimo(RelatorioViewModel item)
        {
            if (ModelState.IsValid)
            {
                var    list        = _EmprestRep.ListarEmprestimo().Where(x => x.DataEmprestimo.ToString("MM/yyyy") == item.DataRelatorio.ToString("MM/yyyy")).ToArray();
                string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                string fileName    = $"Relatorio Emprestimos {item.DataRelatorio.ToString("MMMM-yyyy")}.xlsx";
                try
                {
                    using (var workbook = new XLWorkbook())
                    {
                        IXLWorksheet worksheet =
                            workbook.Worksheets.Add("EMPRESTIMOS");
                        worksheet.Cell("B1").Value            = $"LISTA DE EMPRESTIMOS DO MÊS {item.DataRelatorio.ToString("MMMM/yyyy")}";
                        worksheet.Cell(2, 2).Value            = "LIVRO";
                        worksheet.Cell(2, 3).Value            = "ALUNO";
                        worksheet.Cell(2, 4).Value            = "DATA EMPRESTIMO";
                        worksheet.Cell(2, 5).Value            = "DATA DEVOLUÇÃO";
                        worksheet.Row(2).Style.Font.Bold      = true;
                        worksheet.Row(2).Style.Font.FontColor = XLColor.White;
                        var rngHeaders = worksheet.Range("B2:E2");
                        rngHeaders.Style.Font.Bold            = true;
                        rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        rngHeaders.Style.Fill.BackgroundColor = XLColor.BluePigment;
                        worksheet.Columns(2, 5).AdjustToContents();
                        worksheet.Columns(2, 5).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;


                        for (int index = 1; index <= list.Count(); index++)
                        {
                            worksheet.Cell(index + 2, 2).Value = list[index - 1].Livro.Nome;
                            worksheet.Cell(index + 2, 3).Value = list[index - 1].Aluno;
                            worksheet.Cell(index + 2, 4).Value = list[index - 1].DataEmprestimo.ToString("dd/MM/yyyy");
                            if (list[index - 1].DataDevolucao.ToString("dd/MM/yyyy") == "01/01/0001")
                            {
                                worksheet.Cell(index + 2, 5).Value = "Não foi devolvido";
                            }
                            else
                            {
                                worksheet.Cell(index + 2, 5).Value = list[index - 1].DataDevolucao.ToString("dd/MM/yyyy");
                            }
                        }
                        using (var stream = new MemoryStream())
                        {
                            workbook.SaveAs(stream);
                            var content = stream.ToArray();
                            return(File(content, contentType, fileName));
                        }
                    }
                }
                catch (Exception ex)
                {
                    return(View(ex));
                }
            }
            else
            {
                return(View(nameof(Index), item));
            }
        }
コード例 #2
0
ファイル: FormMain.cs プロジェクト: Mogy/BeadsImageConverter
        /// <summary>
        ///     Infoシートにヘッダ情報を書き込む
        /// </summary>
        /// <param name="info">Infoシート</param>
        private void setInfoHeader(IXLWorksheet info)
        {
            // タイトル
            info.Cell(1, 1).SetValue(Text)
            .Style.Font.SetFontSize(20.0)
            .Font.SetBold();

            // 画像情報
            info.Cell(3, 1).SetValue("ディレクトリ");
            info.Cell(4, 1).SetValue("サイズ");
            info.Cell(5, 1).SetValue("パレット");
            info.Range(3, 1, 5, 1).Style.Font.SetBold()
            .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
            .Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
            .Border.SetInsideBorder(XLBorderStyleValues.Dashed)
            .Fill.SetBackgroundColor(XLColor.LightGray);

            // ビーズ情報
            info.Cell(7, 1).SetValue("No");
            info.Cell(7, 2).SetValue("名前");
            info.Cell(7, 3).SetValue("個数");
            info.Range(7, 1, 7, 3).Style.Font.SetBold().Fill.SetBackgroundColor(XLColor.LightGray);

            // 行列幅
            info.Columns(1, 1).Width = 16.43;
            info.Columns(2, 2).Width = 23.57;
            info.Rows(1, 1).Height   = 26.25;
        }
コード例 #3
0
        /// <summary>
        /// Sortieren der Daten
        /// Überschriften auf BOLD setzen
        /// Autofilter Aktivieren
        /// Spaltenbreite an Inhalt anpassen
        /// </summary>
        /// <param name="worksheet"></param>
        private void SortAndFormatXlsSheet(IXLWorksheet worksheet)
        {
            ////Sortieren der Daten
            var lastCellUsed        = worksheet.LastCellUsed();
            var lastCellUsedAddress = $"A2:{lastCellUsed.Address}";
            var DataRange           = worksheet.Range(lastCellUsedAddress);

            DataRange.Sort("F, C, D, E");

            //Autofilter und Spaltenbreite an Inhalt anpassen
            worksheet.RangeUsed().SetAutoFilter();

            lastCellUsed = worksheet.LastCellUsed();
            //Spaltenbreite an Inhalt anpassen
            worksheet.Columns().AdjustToContents(1, lastCellUsed.Address.RowNumber);
            worksheet.Row(1).Style.Font.SetBold();

            //Spalten als Zahl formartieren
            worksheet.Columns("F,J").AdjustToContents(1, lastCellUsed.Address.RowNumber).Style.NumberFormat.NumberFormatId = 2;

            worksheet.Range($"I2:I{lastCellUsed.Address.RowNumber}").SetDataType(XLDataType.Number);

            //Formatieren
            //Druckbereich
            //Druckeigenschaften
        }
コード例 #4
0
        private void UpdateWorksheetStyles(IXLWorksheet worksheet, int dataCount, int columnsCount, ExcelReportConfigurationDocument config)
        {
            //worksheet.Range(config.SkipRow + HEADER_ROW_COUNT + 1, config.SkipColumn + 1, config.SkipRow + HEADER_ROW_COUNT + dataCount, config.SkipColumn + 1 + columnsCount * 2)
            //         .CreateTable();

            worksheet.Columns(1, config.SkipColumn).Width = 4;
            worksheet.Columns(config.SkipColumn + 1, config.SkipColumn + 1 + columnsCount * 2).AdjustToContents(10.0, 50.0);
        }
コード例 #5
0
ファイル: ExcelWriter.cs プロジェクト: wbskyboy/ExcelHelper
        /// <summary>
        /// Adjusts all the column widths to match the content
        /// </summary>
        /// <param name="minWidth">Minimum width in twips</param>
        /// <param name="maxWidth">Maximum width in twips</param>
        public void AdjustColumnsToContent(
            double minWidth,
            double maxWidth)
        {
            var columns = _sheet.Columns();

            columns.AdjustToContents(minWidth, maxWidth);
        }
コード例 #6
0
ファイル: clsParse2XL.cs プロジェクト: jworkman119/HTC
        private IXLWorksheet formatSheet(IXLWorksheet xlWorksheet, int iRows)
        {
            IXLRange objRange = xlWorksheet.Range("A2", Convert.ToChar(64 + 12) + iRows.ToString());

            objRange.Style.Font.SetFontName("Times New Roman");
            objRange.Style.Font.SetFontSize(8);

            xlWorksheet.Columns("7:10").Style.NumberFormat.Format = "#,##0.00";

            xlWorksheet.Columns(1, 12).AdjustToContents();
            return(xlWorksheet);
        }
コード例 #7
0
        internal void GenerateSuiteSheet(TestSuite suite)
        {
            IXLWorksheet sheet = workbook.AddWorksheet(suite.Name);

            sheet.Columns("A:Z").Style.Alignment.WrapText   = true;
            sheet.Columns("A:Z").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
            sheet.Columns("A:Z").Style.Alignment.Vertical   = XLAlignmentVerticalValues.Top;
            deep = suite.GetSuitsDeep();
            row  = 1;
            MakeHeaders(sheet, suite);
            WriteSuite(sheet, suite, new List <string>());
        }
コード例 #8
0
        public IXLWorksheet ColumnWidthAndFontSize(IXLWorksheet ws, string label)
        {
            switch (label)
            {
            case "task":
            {
                ws.Columns("A").Width = 3;
                //No列の幅
                ws.Columns("B").Width = 3;
                //タイプ列の幅
                ws.Columns("C").Width = 5;
                //内容、詳細列の幅
                ws.Columns("D:E").Width = 64;
                //完了判断列の幅
                ws.Columns("F").Width = 30;
                //備考列の幅
                ws.Columns("G").Width = 40;
                //担当者、日付列の幅
                ws.Columns("H:J").Width = 15;

                //文字サイズ指定
                ws.Columns("A:K").Style.Font.FontSize = 9;
                break;
            }
            }

            return(ws);
        }
コード例 #9
0
        void SetTableHeader(int month, int year, IXLWorksheet ws)
        {
            var allDates = AllDatesInMonth(year, month);

            if (allDates.Count() == 28)
            {
                ws.Columns("34:36").Delete();
            }
            if (allDates.Count() == 29)
            {
                ws.Columns("35:36").Delete();
            }
            if (allDates.Count() == 30)
            {
                ws.Columns("AJ").Delete();
            }
            int column = 6;

            foreach (DateTime dtime in AllDatesInMonth(year, month))
            {
                ws.Cell(6, column).Style.DateFormat.SetFormat("dd/MM");
                ws.Cell(6, column).Value = dtime;
                switch (dtime.DayOfWeek)
                {
                case DayOfWeek.Monday:
                    ws.Cell(7, column).Value = "T2"; break;

                case DayOfWeek.Tuesday:
                    ws.Cell(7, column).Value = "T3"; break;

                case DayOfWeek.Wednesday:
                    ws.Cell(7, column).Value = "T4"; break;

                case DayOfWeek.Thursday:
                    ws.Cell(7, column).Value = "T5"; break;

                case DayOfWeek.Friday:
                    ws.Cell(7, column).Value = "T6"; break;

                case DayOfWeek.Saturday:
                    ws.Cell(7, column).Value = "T7"; break;

                case DayOfWeek.Sunday:
                    ws.Cell(7, column).Value = "CN"; break;
                }
                column++;
            }
        }
コード例 #10
0
        private void ExportExcel(DataTable dt, string fileame)
        {
            string FileSavePath = ConfigurationManager.AppSettings["ExcelSavePath"].ToString();
            string tempFile     = System.IO.Path.Combine(FileSavePath, fileame);

            using (XLWorkbook wb = new XLWorkbook())
            {
                IXLWorksheet ws = wb.Worksheets.Add("Export Data");
                int          z  = 1;
                foreach (DataColumn column in dt.Columns)
                {
                    ws.Cell(1, z).Value                      = column.ColumnName;
                    ws.Cell(1, z).Style.Font.Bold            = true;
                    ws.Cell(1, z).Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
                    ws.Cell(1, z).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    z += 1;
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ws.Cell(i + 2, j + 1).Value = dt.Rows[i][j];
                        ws.Cell(i + 2, j + 1).Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
                        ws.Cell(i + 2, j + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                        ws.Cell(i + 2, 6).Style.DateFormat.Format        = "dd-MMM-yyyy hh:mm:ss";
                    }
                }
                ws.Columns().AdjustToContents();
                wb.SaveAs(tempFile);
                Console.WriteLine(tempFile);
            }
        }
コード例 #11
0
        public byte[] GenerateReport(List <object> itemList, GridConfigurationBase gridConfig)
        {
            List <GridColumnBase> columnList = gridConfig.GridColumnBases.Where(x => !x.KeyColumn).ToList();

            this.Columns = GetColumns(columnList.Count());

            using (var workbook = new XLWorkbook())
            {
                IXLWorksheet ws = workbook.Worksheets.Add("Info General");

                CreateHeader(columnList, ws);

                foreach (object obj in itemList)
                {
                    int file = itemList.IndexOf(obj) + 2;
                    foreach (GridColumnBase cName in columnList)
                    {
                        int column = columnList.IndexOf(cName);
                        var value  = GridColumnBase.GetKeyValue(obj, cName.PropertyInfo);
                        ws.Cell($"{Columns[column]}{file}")
                        .Value = value;
                    }
                }

                ws.Columns().AdjustToContents();


                using (var ms = new MemoryStream())
                {
                    workbook.SaveAs(ms);
                    return(ms.ToArray());
                }
            }
        }
コード例 #12
0
        internal void SaveMainStatistics(XLWorkbook workbook)
        {
            IXLWorksheet worksheet = workbook.Worksheets.Add("Общая статистика");

            worksheet.Cell(1, 1).Value = "№";
            MainMethods.SetStyleHeader(worksheet.Cell(1, 1));

            foreach (ColumnMainStatistics itemColumns in _columns)
            {
                worksheet.Cell(1, itemColumns.Id + 1).Value = itemColumns.Description;
                MainMethods.SetStyleHeader(worksheet.Cell(1, itemColumns.Id + 1));
            }


            for (int i = 0; i < TournamentReplays.Count; i++)
            {
                worksheet.Cell(i + 2, 1).Value = i.ToString();

                foreach (ColumnMainStatistics itemColumns in _columns)
                {
                    worksheet.Cell(i + 2, itemColumns.Id + 1).Value = GetValueReplay(TournamentReplays[i], itemColumns);
                }
            }

            worksheet.Columns(_columns[0].Id + 1, _columns.Last().Id + 1).AdjustToContents();
        }
コード例 #13
0
        private static int DrawDataTable <T>(this IXLWorksheet worksheet, IEnumerable <T> data, int rowNumberStart = 1, int colNumberStart = 1) where T : class, new()
        {
            var colNumber = colNumberStart;
            var rowNumber = rowNumberStart;

            var first     = data.FirstOrDefault();
            var dataProps = GetColumnList(first?.GetType(), null);

            dataProps.ForEach(p =>
            {
                var cell = worksheet.Cell(rowNumber, colNumber++);
                DrawHeaderCell(cell, p.Key);
            });

            data.ForEach(item =>
            {
                rowNumber++;
                colNumber = 1;
                dataProps.ForEach(p =>
                {
                    var cell = worksheet.Cell(rowNumber, colNumber++);
                    DrawDataCell(cell, p.Value.GetValue(item), p.Value.GetCustomAttribute <ExportDisplayAttribute>());
                });
            });

            worksheet.Columns(colNumberStart, dataProps.Count).AdjustToContents();

            return(rowNumber);
        }
コード例 #14
0
        /// <summary>
        /// Allows you to write excel files in .xlsx format. Repository: https://github.com/MarcinMichnik-HiQ/Frends.Office
        /// </summary>
        /// <param name="input"></param>
        /// <returns>Returns JToken.</returns>
        public static JToken WriteExcelFile(WriteExcelFileInput input)
        {
            JToken      taskResponse = JToken.Parse("{}");
            IXLWorkbook workbook     = new XLWorkbook();
            DataTable   dataTable;

            try
            {
                dataTable = input.CsvToDataTable();
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to build DataTable from csv.", ex);
            }

            IXLWorksheet mainWorksheet = workbook.Worksheets.Add(dataTable, "Default");

            // Adjust rows and columns to text length
            mainWorksheet.Rows().AdjustToContents();
            mainWorksheet.Columns().AdjustToContents();

            try
            {
                workbook.SaveAs(input.path);
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to save the file.", ex);
            }

            taskResponse["message"]  = "The file has been written correctly.";
            taskResponse["filePath"] = input.path;

            return(taskResponse);
        }
コード例 #15
0
 /// <summary>
 /// Сохраняет отчет в файл.
 /// </summary>
 private bool SaveFile()
 {
     try
     {
         // Экземпляр книги.
         using (XLWorkbook wb = new XLWorkbook())
         {
             foreach (var report in Reports)
             {
                 // Вкладка с названием по ОСП отчета с датой.
                 IXLWorksheet ws = wb.Worksheets.Add($"{report.OspName}");
                 // Вставить таблицу.
                 ws.Cell(1, 1).InsertTable(GetTable(report));
                 // Установить автоподбор ширины столбцов.
                 ws.Columns().AdjustToContents();
             }
             // Сохранения файла.
             wb.SaveAs(Filepath);
         }
         return(true);
     }
     catch
     {
         return(false);
     }
 }
コード例 #16
0
 public void RemoveEmptyHeaders(Visibility visibility, int celIndx, IXLWorksheet worksheet)
 {
     if (!visibility.Equals(Visibility.Visible))
     {
         worksheet.Columns(celIndx, celIndx).Delete();
     }
 }
コード例 #17
0
        private void InitializeWorksheet(IXLWorksheet worksheet)
        {
            void SetColumn(string columnIndex, string columnName, float columnWidth)
            {
                worksheet.Cell(columnIndex).Value = columnName;
                worksheet.Column(columnIndex[0] - 'A' + 1).Width = columnWidth; // 'A' -> 1  ;  'C' -> 3
            }

            SetColumn("A1", "Poster", 8.11f);
            SetColumn("B1", "AnimeName", 36.8f);
            SetColumn("C1", "Rating", 8.11f);
            SetColumn("D1", "StudioName", 16.5f);
            SetColumn("E1", "Status", 12.8f);
            SetColumn("F1", "AgeRating", 40.7f);
            SetColumn("G1", "Type", 25f);
            SetColumn("H1", "Description", 19.2f);
            SetColumn("I1", "Source", 10.8f);
            SetColumn("J1", "Season", 10.1f);
            SetColumn("K1", "Genres", 10.4f);

            worksheet.Row(1).Style.Font.Bold = true;
            worksheet.Columns(1, 11).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
            worksheet.Column(8).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
            worksheet.Column(11).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
            worksheet.Range("A1", "K1").Style.Fill.BackgroundColor = XLColor.FromArgb(112, 173, 71);
        }
コード例 #18
0
        private static void CreateTable(IEnumerable <Purchase> purchases, IXLWorksheet sheet)
        {
            int currentRow = 1;

            foreach (var purchase in purchases)
            {
                int tableRowBegin = currentRow;

                OutDate(sheet, ref currentRow, purchase);
                CreateHeader(sheet, currentRow++);

                var pItems       = purchase.GetPurchaseItems().OrderBy(x => x.Type).ThenBy(x => x.GetTotal);
                int rowDataBegin = currentRow;
                foreach (var item in pItems)
                {
                    OutGood(sheet, item, currentRow++);
                }

                OutPurchaseSum(sheet, ref currentRow, rowDataBegin);

                EditRange(sheet, currentRow, tableRowBegin, rowDataBegin);
            }

            sheet.Columns().AdjustToContents();
        }
コード例 #19
0
ファイル: ExcelOut.cs プロジェクト: vamsitp/perfx
        private IXLWorksheet CreateStatsSheet <T>(IXLWorkbook wb, IEnumerable <T> results, double durationSla, double sizeSla)
        {
            IXLWorksheet wsStats = wb.Worksheets.Add("Perfx_Stats");
            var          stats   = results.GetStats();

            var statsDataTable = this.ToDataTable(stats.AsEnumerable());
            var statsTable     = wsStats.Cell(1, 1).InsertTable(statsDataTable, "Stats");

            statsTable.Theme = XLTableTheme.TableStyleLight8;

            var statsRowCount = (statsDataTable.Rows.Count + 1).ToString();

            var durations = wsStats.Range("B2:I" + statsRowCount);

            SetFormat(durations, durationSla);

            var sizes = wsStats.Range("J2:K" + statsRowCount);

            SetFormat(sizes, sizeSla);

            wsStats.Range("L2:L" + statsRowCount).Style.Font.SetFontColor(XLColor.SeaGreen);
            wsStats.Range("M2:M" + statsRowCount).Style.Font.SetFontColor(XLColor.OrangeRed);

            try
            {
                wsStats.SheetView.Freeze(1, 1);
                wsStats.Columns().AdjustToContents();
            }
            catch (Exception)
            {
                // Expected on Windows Server
            }

            return(wsStats);
        }
コード例 #20
0
        private static void AddFooterToWorksheet(IXLWorksheet worksheet, int actualRowNumber)
        {
            var actualLetter = 'B';

            actualLetter = GetNextLetter(actualLetter);
            worksheet.Cell(actualLetter.ToString() + actualRowNumber).Value = "Instructores";

            var rngInst = worksheet.Range(actualLetter.ToString() + actualRowNumber);

            rngInst.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
            rngInst.Style.Font.Bold            = true;
            rngInst.Style.Font.Underline       = XLFontUnderlineValues.Single;
            rngInst.Style.Font.FontSize        = 28;
            rngInst.Style.Font.FontName        = "Calibri";

            actualLetter = GetNextLetter(actualLetter);
            worksheet.Cell(actualLetter.ToString() + actualRowNumber).Value       = "Sciancalepore, Juan C.";
            worksheet.Cell(actualLetter.ToString() + (actualRowNumber + 1)).Value = "Zechillo, Pablo";

            var rngNames =
                worksheet.Range(actualLetter.ToString() + actualRowNumber + ":" + actualLetter.ToString() + (actualRowNumber + 1));

            rngNames.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
            rngNames.Style.Font.Bold            = true;
            rngNames.Style.Font.FontSize        = 26;
            rngNames.Style.Font.FontName        = "Calibri";


            worksheet.Column(1).AdjustToContents(11, 12);
            worksheet.Columns(2, 7).AdjustToContents();
        }
コード例 #21
0
        /// <summary>
        /// Method used to add values to excel file
        /// </summary>
        /// <param name="jsonText">Is a json value for which excel needs to be created.</param>
        /// <param name="workSheet">Worksheet for which header needs to be added</param>
        private static void AddDataToExcel(string jsonText, IXLWorksheet workSheet)
        {
            //Add data rows
            int rowCount = 2;
            //make a new string list
            List <string> jsonList = jsonText.Split(Constants.CHAR_NEWLINE).ToList();

            foreach (string jsonListItem in jsonList)
            {
                List <string> rowValue = new List <string>(); //make a new string list
                rowValue.AddRange(jsonListItem.Split(Constants.CHAR_SEMICOLON));
                for (int iCount = 0; iCount < rowValue.Count; iCount++)
                {
                    if (0 > rowValue[iCount].Trim().Length)
                    {
                        workSheet.Cell(rowCount, iCount + 1).Value = string.Empty;
                    }
                    else
                    {
                        workSheet.Cell(rowCount, iCount + 1).Value = (string.IsNullOrWhiteSpace(Convert.ToString(rowValue[iCount], CultureInfo.InvariantCulture))) ? string.Empty : rowValue[iCount];
                    }
                }
                rowCount++;
                workSheet.Columns().AdjustToContents();
            }
        }
コード例 #22
0
        private static void createValvesSheet(XLWorkbook workbook, TECTemplates templates)
        {
            IXLWorksheet worksheet = workbook.Worksheets.Add("Valves");

            int x = 1, y = 2;

            y = addHardwareHeader(worksheet, x, y).nextColumn;
            y = addEndDeviceHeader(worksheet, x, y).nextColumn;
            worksheet.Cell(x, y).Value = "Actuator";
            worksheet.Cell(x, y).Style.Font.SetBold();
            worksheet.Cell(x, y).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
            x++;
            int startY = 2;

            foreach (TECValve valve in templates.Catalogs.Valves)
            {
                int rowY = startY;
                startY = addHardwareRow(worksheet, valve, x, startY).nextColumn;
                startY = addEndDeviceRow(worksheet, valve, x, startY).nextColumn;
                worksheet.Cell(x, startY).Value = valve.Actuator.Name;
                x++;
            }

            worksheet.Columns().AdjustToContents();
            worksheet.PageSetup.PageOrientation = XLPageOrientation.Landscape;
            worksheet.PageSetup.FitToPages(1, 1);
        }
コード例 #23
0
        private List <DiferenceWs> PaintDifferences(IXLWorksheet ws1, IXLWorksheet ws2)
        {
            var ws1CountRows            = ws1.Rows().Count();
            var ws2CountRows            = ws2.Rows().Count();
            var ws1CountCols            = ws1.Columns().Count();
            var ws2CountCols            = ws2.Columns().Count();
            List <DiferenceWs> diffList = new();

            if (ws1CountCols != ws2CountCols && ws1CountRows != ws2CountRows)
            {
                throw new Exception($"number cols/rows are different for worksheet target {ws1.Name} and worksheet source {ws2.Name} , table : " + TableName);
            }

            for (var irow = 1; irow <= ws1CountRows; irow++)
            {
                for (var icol = 1; icol <= ws1CountCols; icol++)
                {
                    if ((ws1.Cell(irow, icol).GetValue <string>() == ws2.Cell(irow, icol).GetValue <string>()))
                    {
                        continue;
                    }

                    ws1.Cell(irow, icol).Style.Fill.BackgroundColor = XLColor.OrangePeel;
                    ws2.Cell(irow, icol).Style.Fill.BackgroundColor = XLColor.OrangePeel;
                    diffList.Add(new DiferenceWs(irow, icol, ws1.Cell(irow, icol).GetValue <string>(), ws2.Cell(irow, icol).GetValue <string>(), TableName));
                }
            }
            return(diffList);
        }
コード例 #24
0
        private static void createSystemSheet(XLWorkbook workbook, TECTemplates templates)
        {
            IXLWorksheet worksheet = workbook.Worksheets.Add("Systems");

            int x = 1, y = 2;

            y = addScopeHeader(worksheet, x, y).nextColumn;
            y = addScopeHeader(worksheet, x, y).nextColumn;
            x = addSubScopeHeader(worksheet, x, y).nextRow;
            foreach (TECSystem system in templates.Templates.SystemTemplates)
            {
                foreach (TECEquipment equipment in system.Equipment)
                {
                    foreach (TECSubScope scope in equipment.SubScope)
                    {
                        y = 2;
                        y = addScopeRow(worksheet, system, x, y).nextColumn;
                        y = addScopeRow(worksheet, equipment, x, y).nextColumn;
                        x = addSubScopeRow(worksheet, scope, x, y).nextRow;
                    }
                }
            }

            worksheet.Columns().AdjustToContents();
            worksheet.PageSetup.PageOrientation = XLPageOrientation.Landscape;
            worksheet.PageSetup.FitToPages(1, 1);
        }
コード例 #25
0
        public async Task <XLWorkbook> WriteDataToFile()

        {
            string path                 = "";
            string actualPath           = path.SetDirectoryPath();
            string newlyCreatedFilePath = $@"{actualPath}\ExcelFiles\ClosedXMLGeneratedFile.xlsx";

            var          wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Primary", 1);
            //IXLWorksheet ws2 = wb.Worksheets.Add("Secondary", 2);

            var dataTable = await ReadExcelDataAsync();


            ws.Range(1, 1, 1, 5).Merge().AddToNamed("Titles");
            //ws2.Range(1, 1, 1, 5).Merge().AddToNamed("Workbook");
            var rangeWithData = ws.Cell(2, 1).InsertData(dataTable.AsEnumerable());

            //var rangeWithData2 = ws2.Cell(2, 1).InsertData(dataTable.AsEnumerable());

            ws.Column(1).SetDataType(XLDataType.Number);
            ws.Column(2).SetDataType(XLDataType.Text);
            ws.Column(3).SetDataType(XLDataType.Boolean);
            ws.Column(4).SetDataType(XLDataType.Text);
            ws.Column(5).Style.NumberFormat.Format = "mm/dd/yyyy";
            //ws2.Column(5).Style.NumberFormat.Format = "mm/dd/yyyy";

            //Adjust column widths to their content
            ws.Columns(1, 5).AdjustToContents();
            //ws2.Columns(1, 5).AdjustToContents();

            // Prepare the style

            var dataStyle = ws.Style;

            dataStyle.Alignment.Vertical = XLAlignmentVerticalValues.Center;

            // wingdings column
            var rangeForWingDings = ws.Range(2, 6, 100000, 6).AddToNamed("wingdings");

            rangeForWingDings.Value = char.ConvertFromUtf32(0x00002713);
            rangeForWingDings.Style.Fill.BackgroundColor = XLColor.Red;

            // Merge cells
            var mergedCellrange = ws.Range(2, 10, 2, 12);

            mergedCellrange.Cell(1, 1).Value = "merged";
            mergedCellrange.Merge();

            // outside border
            var outsideBorderRange = ws.Range(2, 10, 2, 12);

            outsideBorderRange.Style.Border.OutsideBorder = XLBorderStyleValues.Double;


            wb.SaveAs(newlyCreatedFilePath);

            return(wb);
        }
コード例 #26
0
        public async Task <byte[]> ExportTasksAsXlsl(Guid userId, bool restrictData)
        {
            List <Core.Entities.Task> tasks = null;

            if (!restrictData)
            {
                tasks = await taskRepository.GetAllTasksWithChildEntities();
            }
            else
            {
                tasks = await taskRepository.GetAllTasksByUserIdWithChildEntities(userId.ToString());
            }

            if (tasks == null)
            {
                return(null);
            }

            var tasksDto = tasks.Select(x => mapper.Map <TaskDto>(x))
                           .Where(x => !x.Removed)
                           .ToList();

            var          workBook  = new XLWorkbook();
            IXLWorksheet worksheet = workBook.Worksheets.Add($"Tarefas - {tasks.First().Attendant.FirstName}");

            worksheet.Cell(1, 1).Value = "Tarefa";
            worksheet.Cell(1, 2).Value = "Descrição";
            worksheet.Cell(1, 3).Value = "Status";
            worksheet.Cell(1, 4).Value = "Prioridade";
            worksheet.Cell(1, 5).Value = "Data de Criação";
            worksheet.Cell(1, 6).Value = "Data de Conclusão";
            worksheet.Cell(1, 7).Value = "Concluída em";

            var headerCells = worksheet.Range("A1:G1");

            headerCells.Style.Font.SetBold()
            .Fill.SetBackgroundColor(XLColor.BabyBlue)
            .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);

            for (int i = 1; i <= tasksDto.Count(); i++)
            {
                worksheet.Cell(i + 1, 1).Value = tasksDto[i - 1].Title;
                worksheet.Cell(i + 1, 2).Value = tasksDto[i - 1].Description;
                worksheet.Cell(i + 1, 3).Value = tasksDto[i - 1].Status;
                worksheet.Cell(i + 1, 4).Value = tasksDto[i - 1].Priority;
                worksheet.Cell(i + 1, 5).Value = tasksDto[i - 1].CreatedDate;
                worksheet.Cell(i + 1, 6).Value = tasksDto[i - 1].ConclusionDate.HasValue ? tasksDto[i - 1].ConclusionDate.Value.ToString("dd/MM/yyyy") : "Sem data de conclusão";
                worksheet.Cell(i + 1, 7).Value = tasksDto[i - 1].ConcludedDate.HasValue ? tasksDto[i - 1].ConcludedDate.Value.ToString("dd/MM/yyyy") : "Não concluída";
            }

            worksheet.Columns().AdjustToContents();

            using MemoryStream stream = new MemoryStream();
            workBook.SaveAs(stream);

            stream.Seek(0, SeekOrigin.Begin);

            return(stream.ToArray());
        }
コード例 #27
0
 public void FormatAllReport(IXLWorksheet ws, int currentRow, int totalCols)
 {
     ws.Range(1, 1, currentRow - 1, totalCols).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
     ws.Range(1, 1, currentRow - 1, totalCols).Style.Border.InsideBorder  = XLBorderStyleValues.Thin;
     ws.Range(1, 1, 1, totalCols).Style.Border.BottomBorder = XLBorderStyleValues.None;
     ws.Range(2, 1, 2, totalCols).Style.Border.TopBorder    = XLBorderStyleValues.None;
     ws.Columns(1, totalCols).AdjustToContents();
 }
コード例 #28
0
 public void WorkSheetExcelStyle(IXLWorksheet worksheet)
 {
     worksheet.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
     worksheet.Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
     worksheet.Rows().AdjustToContents();
     worksheet.Columns().AdjustToContents();
     worksheet.Style.Alignment.WrapText = true;
 }
コード例 #29
0
ファイル: FormMain.cs プロジェクト: Mogy/BeadsImageConverter
        /// <summary>
        ///     Resultシートに情報を書き込む
        /// </summary>
        /// <param name="result">Resultシート</param>
        public void setResult(IXLWorksheet result)
        {
            // タイトル
            result.Cell(1, 1).SetValue(Text)
            .Style.Font.SetFontSize(20.0)
            .Font.SetBold();

            // パレット情報
            result.Cell(3, 1).SetValue("パレット")
            .Style.Font.SetBold()
            .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right)
            .Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
            .Border.SetInsideBorder(XLBorderStyleValues.Dashed)
            .Fill.SetBackgroundColor(XLColor.LightGray);
            result.Cell(3, 2).SetValue(PaletteName);

            // ビーズ情報
            result.Cell(5, 1).SetValue("No");
            result.Cell(5, 2).SetValue("名前");
            result.Cell(5, 3).SetValue("個数");
            result.Range(5, 1, 5, 3).Style.Font.SetBold()
            .Fill.SetBackgroundColor(XLColor.LightGray);

            int i = 0;

            Palette.Where((x) => x.Total > 0).ForEach((bead) =>
            {
                int row = 6 + i++;
                result.Cell(row, 1).SetValue(bead.No)
                .Style.Fill.SetBackgroundColor(bead.Color)
                .Font.SetFontColor(bead.FontColor);
                result.Cell(row, 2).SetValue(bead.Name);
                result.Cell(row, 3).Value = bead.Total;
            });

            result.Range(result.Cell(5, 1), result.LastCellUsed())
            .Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
            .Alignment.SetVertical(XLAlignmentVerticalValues.Center)
            .Border.SetOutsideBorder(XLBorderStyleValues.Dashed)
            .Border.SetInsideBorder(XLBorderStyleValues.Dashed);

            // 行列幅
            result.Columns(1, 1).Width = 16.43;
            result.Columns(2, 2).Width = 23.57;
            result.Rows(1, 1).Height   = 26.25;
        }
コード例 #30
0
 private static void setColumnWidths(IXLWorksheet ws)
 {
     ws.Columns().Width = 5.22;
     ws.Column(1).Width = 6.89;
     ws.Column(2).Width = 6.89;
     ws.Column(3).Width = 6.89;
     ws.Column(4).Width = 1.67;
 }
コード例 #31
0
        public static double getTotalWidth(IXLWorksheet ws, int startCol)
        {
            var totalWidth = 0.0;
            foreach (var col in ws.Columns(startCol, ws.LastColumnUsed().ColumnNumber()))
            {
                totalWidth += col.Width * 5.69;
            }

            return totalWidth;
        }
コード例 #32
0
        /// <summary>
        /// Create a worksheet
        /// </summary>
        /// <param name="worksheet">IXLWorksheet</param>
        /// <param name="sheet">CExcelSheet</param>
        private void CreateAWorkSheet(IXLWorksheet worksheet, CExcelSheet sheet)
        {
            //title
            IXLAddress firstAdd = worksheet.Cell(1, COL_BEGIN).Address;
            worksheet.Cell(1, 1).Value = sheet.Title;
            worksheet.Cell(1, 1).Style.Font.Bold = true;
            worksheet.Cell(1, 1).Style.Font.FontSize = 15;
            worksheet.Cell(1, 1).Style.Font.FontColor = XLColor.White;
            worksheet.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.FromArgb(0x0066cc);

            #region Export By

            int rowIdx = ROW_BEGIN;
            if (sheet.ExportBy != null)
            {
                foreach (var item in sheet.ExportBy)
                {
                    // reset column
                    int colIdx = COL_BEGIN;

                    worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
                    worksheet.Cell(rowIdx, colIdx).Value = "'" + item.key;
                    worksheet.Range(
                        worksheet.Cell(rowIdx, colIdx).Address,
                        worksheet.Cell(rowIdx, colIdx + 2).Address).Merge();
                    colIdx += 3;
                    worksheet.Cell(rowIdx, colIdx).DataType = XLCellValues.Text;
                    worksheet.Cell(rowIdx, colIdx).Value = "'" + item.value;
                    // new row
                    rowIdx++;
                }
            }

            #endregion

            // Header
            int col = COL_BEGIN;

            #region Header
            //Add No
            if (sheet.IsRenderNo)
            {
                worksheet.Cell(rowIdx, col).Value = "No";
                worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                worksheet.Cell(rowIdx, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                col++;
            }
            foreach (string header in sheet.Header)
            {
                worksheet.Cell(rowIdx, col).Value = header;
                worksheet.Cell(rowIdx, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                col++;
            }
            //Style for header
            worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.Bold = true;
            worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Font.FontSize = 12;
            worksheet.Range(worksheet.Cell(rowIdx, COL_BEGIN).Address, worksheet.Cell(rowIdx, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
            //merge title
            IXLAddress secondAdd = worksheet.Cell(1, col - 1).Address;
            worksheet.Range(firstAdd, secondAdd).Merge();
            worksheet.Range(firstAdd, secondAdd).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            // Merge export by
            for (int i = ROW_BEGIN; i < rowIdx; i++)
            {
                worksheet.Range(
                    worksheet.Cell(i, 4).Address,
                    worksheet.Cell(i, col - 1).Address).Merge();
            }
            #endregion

            // detail
            // Comment when adding export by, rowIdx is the number of export by
            //int idx_row = ROW_BEGIN + 1;
            int idx_row = rowIdx + 1;
            string preGroup = string.Empty;
            ArrayList beginSubList = new ArrayList();
            ArrayList endSubList = new ArrayList();
            //linh.quang.le: Freeze panels
            worksheet.SheetView.FreezeRows(sheet.FreezeRow);
            worksheet.SheetView.FreezeColumns(sheet.FreezeColumn);
            //linh.quang.le number
            int no = 1;

            #region Detail
            foreach (Object row in sheet.List)
            {
                int idx_col = COL_BEGIN;
                int index = 0;
                bool hasMainColumnValue = HasMainColumnValue(row, sheet);
                //linh.quang.le
                #region GroupName
                if (sheet.IsGroup)
                {
                    string groupName = string.Empty;
                    groupName = row.GetType().GetProperty(sheet.GroupName).GetValue(row, null).ToString();
                    if (!String.IsNullOrEmpty(groupName) && preGroup != groupName)
                    {
                        if (beginSubList.Count != 0)
                            endSubList.Add(idx_row - 1);

                        worksheet.Cell(idx_row, COL_BEGIN).Value = groupName;
                        worksheet.Cell(idx_row, COL_BEGIN).Style.Font.Bold = true;
                        worksheet.Cell(idx_row, COL_BEGIN).Style.Font.FontSize = 12;
                        worksheet.Cell(idx_row, COL_BEGIN).Style.Font.FontColor = XLColor.Black;
                        worksheet.Cell(idx_row, COL_BEGIN).Style.Fill.BackgroundColor = XLColor.FromArgb(0xD9D9D9);

                        IXLAddress firstGroupAddr = worksheet.Cell(idx_row, COL_BEGIN).Address;
                        IXLAddress secondGroupAddr = worksheet.Cell(idx_row, (COL_BEGIN + (sheet.IsRenderNo ? sheet.ColumnList.Length : sheet.ColumnList.Length - 1))).Address;
                        worksheet.Range(firstGroupAddr, secondGroupAddr).Merge();
                        worksheet.Range(firstGroupAddr, secondGroupAddr).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        preGroup = groupName;
                        no = 1;
                        idx_row++;
                        beginSubList.Add(idx_row);
                    }

                    if (sheet.IsRenderNo && hasMainColumnValue)
                    {
                        worksheet.Cell(idx_row, idx_col).Value = no.ToString();
                        worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
                        no++;
                        idx_col++;
                    }
                }
                else
                {
                    if (sheet.IsRenderNo)
                    {
                        worksheet.Cell(idx_row, idx_col).Value = (idx_row - ROW_BEGIN).ToString();
                        worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.CenterContinuous;
                        idx_col++;
                    }
                }
                #endregion

                if (sheet.IsGroup)
                {
                    if (hasMainColumnValue)
                    {
                        foreach (string header in sheet.ColumnList)
                        {
                            string[] headerArr = header.Split(Convert.ToChar(":"));
                            Object obj = null;

                            if (row.GetType().GetProperty(headerArr[0].ToString()) != null)
                                obj = row.GetType().GetProperty(headerArr[0].ToString()).GetValue(row, null);
                            else
                            {
                                string[] arr = (string[])row;
                                if (arr != null)
                                    obj = arr[index++];
                            }
                            string strValue = string.Empty;

                            strValue = obj == null ? "" : obj.ToString();
                            #region  Format item
                            if (headerArr.Count() == 2)
                            {
                                switch (headerArr[1].ToString().ToLower())
                                {
                                    case "text":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                                        worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
                                        strValue = "'" + strValue;
                                        break;
                                    case "date":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_VIEW);
                                        worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_VIEW;
                                        break;
                                    case "datetime":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_TIME);
                                        worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_TIME;
                                        worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
                                        break;
                                    // Convert from Int of Hour and Minute to "Hour : Minute" string
                                    // Using in Time Mangement Module
                                    // @author : tai.pham
                                    case "hour":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
                                        strValue = (obj == null || obj == string.Empty) ? string.Empty : "'" + ConvertUtil.ConvertToDouble(obj).ToString("0#:##");
                                        break;
                                    // Convert from location code to location string
                                    // Using in Time Mangement Module
                                    // @author : tai.pham
                                    case "location":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                                        strValue = obj == null ? "" : CommonFunc.GenerateStringOfLocation((string)obj);
                                        break;
                                    case "gender":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        strValue = obj == null ? "" : (bool)obj == Constants.MALE ? "Male" : "Female";
                                        break;
                                    case "married":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        strValue = obj == null ? "" : (bool)obj == Constants.MARRIED ? "Married" : "Single";
                                        break;
                                    case "labor":
                                        strValue = obj == null ? "" : (bool)obj == Constants.LABOR_UNION_FALSE ? "No" : "Yes";
                                        break;
                                    case "hhmm":
                                        worksheet.Cell(idx_row, idx_col).Style.NumberFormat.NumberFormatId = 20;
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        strValue = (obj == null || obj == "") ? "" : CommonFunc.FormatTime((double)obj);
                                        break;
                                    case "jr":
                                        strValue = obj == null ? "" : Constants.JOB_REQUEST_PREFIX + obj;
                                        break;
                                    case "pr":
                                        strValue = obj == null ? "" : Constants.PR_REQUEST_PREFIX + obj;
                                        break;
                                    case "sr":
                                        strValue = obj == null ? "" : Constants.SR_SERVICE_REQUEST_PREFIX + obj;
                                        break;
                                    case "candidate":
                                        strValue = obj == null ? "" : CommonFunc.GetCandidateStatus((int)obj);
                                        break;
                                    case "actionsendmail":
                                        strValue = obj == null ? "" : (bool)obj != true ? "No" : "Yes";
                                        break;
                                    case "jr_request":
                                        strValue = obj == null ? "" : (int)obj == Constants.JR_REQUEST_TYPE_NEW ? "New" : "Replace";
                                        break;
                                    case "dayofweek":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        strValue = obj == null ? "" : ((DateTime)obj).DayOfWeek.ToString();
                                        break;
                                    case "number":
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                                        break;
                                    case "duration":
                                        strValue = obj == null ? "" : obj + " " + Constants.TC_DURATION_PREFIX;
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                        break;
                                    default:
                                        worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                                        break;
                                }

                                worksheet.Cell(idx_row, idx_col).Value = strValue;
                            }
                            #endregion
                            else
                            {
                                worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
                                worksheet.Cell(idx_row, idx_col).Value = "'" + strValue;
                            }

                            worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                            idx_col++;
                        }

                        worksheet.Columns(COL_BEGIN, idx_col).AdjustToContents();
                        idx_row++;
                    }
                }
                else
                {
                    foreach (string header in sheet.ColumnList)
                    {
                        string[] headerArr = header.Split(Convert.ToChar(":"));
                        Object obj = null;

                        if (row.GetType().GetProperty(headerArr[0].ToString()) != null)
                            obj = row.GetType().GetProperty(headerArr[0].ToString()).GetValue(row, null);
                        else
                        {
                            string[] arr = (string[])row;
                            if (arr != null)
                                obj = arr[index++];
                        }
                        string strValue = string.Empty;

                        strValue = obj == null ? "" : obj.ToString();
                        #region  Format item
                        if (headerArr.Count() == 2)
                        {
                            switch (headerArr[1].ToString().ToLower())
                            {
                                case "text":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                                    worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
                                    strValue = "'" + strValue;
                                    break;
                                case "date":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_VIEW);
                                    worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_VIEW;
                                    break;
                                case "datetime":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    strValue = obj == null ? "" : ((DateTime)obj).ToString(Constants.DATETIME_FORMAT_TIME);
                                    worksheet.Cell(idx_row, idx_col).Style.DateFormat.Format = Constants.DATETIME_FORMAT_TIME;
                                    worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
                                    break;
                                case "gender":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    strValue = obj == null ? "" : (bool)obj == Constants.MALE ? "Male" : "Female";
                                    break;
                                case "married":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    strValue = obj == null ? "" : (bool)obj == Constants.MARRIED ? "Married" : "Single";
                                    break;
                                case "labor":
                                    strValue = obj == null ? "" : (bool)obj == Constants.LABOR_UNION_FALSE ? "No" : "Yes";
                                    break;
                                // Convert from Int of Hour and Minute to "Hour : Minute" string
                                // Using in Time Management Module
                                // @author : tai.pham
                                case "hour":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
                                    strValue = (obj == null || obj == string.Empty) ? string.Empty : "'" + ConvertUtil.ConvertToDouble(obj).ToString("0#:##");
                                    break;
                                // Convert from location code to location string
                                // Using in Time Mangement Module
                                // @author : tai.pham
                                case "location":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                                    strValue = obj == null ? "" : CommonFunc.GenerateStringOfLocation((string)obj);
                                    break;
                                case "hhmm":
                                    worksheet.Cell(idx_row, idx_col).Style.NumberFormat.NumberFormatId = 20;
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    strValue = (obj == null || obj == "") ? "" : CommonFunc.FormatTime((double)obj);
                                    break;
                                case "jr":
                                    strValue = obj == null ? "" : Constants.JOB_REQUEST_PREFIX + obj;
                                    break;
                                case "pr":
                                    strValue = obj == null ? "" : Constants.PR_REQUEST_PREFIX + obj;
                                    break;
                                case "sr":
                                    strValue = obj == null ? "" : Constants.SR_SERVICE_REQUEST_PREFIX + obj;
                                    break;
                                case "candidate":
                                    strValue = obj == null ? "" : CommonFunc.GetCandidateStatus((int)obj);
                                    break;
                                case "actionsendmail":
                                    strValue = obj == null ? "" : (bool)obj != true ? "No" : "Yes";
                                    break;
                                case "jr_request":
                                    strValue = obj == null ? "" : (int)obj == Constants.JR_REQUEST_TYPE_NEW ? "New" : "Replace";
                                    break;
                                case "dayofweek":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    strValue = obj == null ? "" : ((DateTime)obj).DayOfWeek.ToString();
                                    break;
                                case "number":
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                                    break;
                                case "duration":
                                    strValue = obj == null ? "" : obj + " " + Constants.TC_DURATION_PREFIX;
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    break;
                                default:
                                    worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                                    break;
                            }

                            worksheet.Cell(idx_row, idx_col).Value = strValue;
                        }
                        #endregion
                        else
                        {
                            worksheet.Cell(idx_row, idx_col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                            worksheet.Cell(idx_row, idx_col).DataType = XLCellValues.Text;
                            worksheet.Cell(idx_row, idx_col).Value = "'" + strValue;
                        }

                        worksheet.Cell(idx_row, idx_col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                        idx_col++;
                    }

                    worksheet.Columns(COL_BEGIN, idx_col).AdjustToContents();
                    idx_row++;
                }
            }
            if (sheet.IsGroup)
            {
                if (!String.IsNullOrEmpty(sheet.GroupName))
                {
                    for (int i = 0; i < beginSubList.Count; i++)
                    {
                        worksheet.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;
                        if (i >= endSubList.Count)
                            worksheet.Rows((int)beginSubList[i], idx_row - 1).Group();
                        else
                            worksheet.Rows((int)beginSubList[i], (int)endSubList[i]).Group();
                    }
                }
            }

            #endregion
            #region Footer
            if (sheet.Footer != null)
            {
                col = COL_BEGIN;
                if (sheet.IsRenderNo)
                {
                    worksheet.Cell(idx_row, col).Value = "Total";
                    worksheet.Cell(idx_row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    worksheet.Cell(idx_row, col).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    col++;
                }
                foreach (string footer in sheet.Footer)
                {
                    worksheet.Cell(idx_row, col).Value = footer;
                    worksheet.Cell(idx_row, col).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    col++;
                }

                //Style for footer
                worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
                worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Font.Bold = true;
                worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Font.FontSize = 12;
                worksheet.Range(worksheet.Cell(idx_row, COL_BEGIN).Address, worksheet.Cell(idx_row, col - 1).Address).Style.Fill.BackgroundColor = XLColor.FromTheme(XLThemeColor.Accent1, 0.5);
            }
            #endregion
        }
コード例 #33
0
ファイル: InventoryForm.cs プロジェクト: renyh1013/dp2
        // parameters:
        //      output_columns    输出列定义。如果为空,表示全部输出。这是一个数字列表,例如 "1,2,3,5"
        // return:
        //      -1  出错
        //      0   放弃或中断
        //      1   成功
        public static int ExportToExcel(
            Stop stop,
            ListView list,
            List<string> output_columns,
            List<ListViewItem> items,
            IXLWorksheet sheet,
            out string strError)
        {
            strError = "";
#if NO
            if (items == null || items.Count == 0)
            {
                strError = "items == null || items.Count == 0";
                return -1;
            }
#endif

            // ListView list = items[0].ListView;
            if (stop != null)
                stop.SetProgressRange(0, items.Count);

            List<int> indices = new List<int>();
            if (output_columns == null && output_columns.Count == 0)
            {
                int i = 0;
                foreach (ColumnHeader header in list.Columns)
                {
                    indices.Add(i++);
                }
            }
            else
            {
                foreach (string s in output_columns)
                {
                    int v = 0;
                    if (Int32.TryParse(s, out v) == false)
                    {
                        strError = "output_columns 数组中有非数字的字符串,格式错误";
                        return -1;
                    }
                    indices.Add(v - 1);   // 从 0 开始计数
                }
            }

            // 每个列的最大字符数
            List<int> column_max_chars = new List<int>();

            List<XLAlignmentHorizontalValues> alignments = new List<XLAlignmentHorizontalValues>();
            //foreach (ColumnHeader header in list.Columns)
            foreach (int index in indices)
            {
                ColumnHeader header = list.Columns[index];

                if (header.TextAlign == HorizontalAlignment.Center)
                    alignments.Add(XLAlignmentHorizontalValues.Center);
                else if (header.TextAlign == HorizontalAlignment.Right)
                    alignments.Add(XLAlignmentHorizontalValues.Right);
                else
                    alignments.Add(XLAlignmentHorizontalValues.Left);

                column_max_chars.Add(0);
            }

            string strFontName = list.Font.FontFamily.Name;

            int nRowIndex = 1;
            int nColIndex = 1;
            // foreach (ColumnHeader header in list.Columns)
            foreach (int index in indices)
            {
                ColumnHeader header = list.Columns[index];

                IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(header.Text);
                cell.Style.Alignment.WrapText = true;
                cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                cell.Style.Font.Bold = true;
                cell.Style.Font.FontName = strFontName;
                cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
                nColIndex++;
            }
            nRowIndex++;

            //if (stop != null)
            //    stop.SetMessage("");
            foreach (ListViewItem item in items)
            {
                Application.DoEvents();

                if (stop != null
    && stop.State != 0)
                {
                    strError = "用户中断";
                    return 0;
                }

                // List<CellData> cells = new List<CellData>();

                nColIndex = 1;
                // foreach (ListViewItem.ListViewSubItem subitem in item.SubItems)
                foreach (int index in indices)
                {
                    string strText = "";
                    ListViewItem.ListViewSubItem subitem = null;
                    if (index < item.SubItems.Count)
                    {
                        subitem = item.SubItems[index];
                        strText = subitem.Text;
                    }
                    else
                    {
                    }

                    // 统计最大字符数
                    int nChars = column_max_chars[nColIndex - 1];
                    if (string.IsNullOrEmpty(strText) == false && strText.Length > nChars)
                    {
                        column_max_chars[nColIndex - 1] = strText.Length;
                    }
                    IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(strText);
                    cell.Style.Alignment.WrapText = true;
                    cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    cell.Style.Font.FontName = strFontName;
                    cell.Style.Alignment.Horizontal = alignments[nColIndex - 1];
                    nColIndex++;
                }

                if (stop != null)
                    stop.SetProgressValue(nRowIndex - 1);

                nRowIndex++;
            }

            if (stop != null)
                stop.SetMessage("正在调整列宽度 ...");
            Application.DoEvents();

            double char_width = ClosedXmlUtil.GetAverageCharPixelWidth(list);

            // 字符数太多的列不要做 width auto adjust
            const int MAX_CHARS = 30;   // 60
            {
                int i = 0;
                foreach (IXLColumn column in sheet.Columns())
                {
                    int nChars = column_max_chars[i];
                    if (nChars < MAX_CHARS)
                        column.AdjustToContents();
                    else
                        column.Width = (double)list.Columns[i].Width / char_width;  // Math.Min(MAX_CHARS, nChars);
                    i++;
                }
            }

            return 1;
        }
コード例 #34
0
ファイル: ExcelReporter.cs プロジェクト: rodrigoi/shipoopi
        private void AddPerformanceFormatting(IXLWorksheet performanceSheet)
        {
            int lastRowUsed = performanceSheet.LastRowUsed().RowNumber();
            //freeze panels
            performanceSheet.SheetView.Freeze(1, 2);
            //performance global styles
            performanceSheet.Range(1, 1, performanceSheet.LastCellUsed().Address.RowNumber, performanceSheet.LastCellUsed().Address.ColumnNumber)
                .Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
            //column specific formatting

            //date columns
            performanceSheet.Range(string.Format("A2:A{0}", lastRowUsed)).Style.DateFormat.Format = "dd/MM/yyyy";

            //percentage formatting (0.00%)
            performanceSheet.Ranges(string.Format("E2:E{0},H2:I{1},Y2:Y{2},AC2:AC{3},AE2:AF{4},AH2:AI{5},AK2:AL{6},AN2:AO{7}",
                lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.NumberFormatId = 10;

            //no decimal points
            performanceSheet.Range(string.Format("R2:R{0}", lastRowUsed)).Style.NumberFormat.Format = "0";

            //decimal format (0.00)
            performanceSheet.Ranges(string.Format("J2:J{0},L2:N{1}",
                lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "0.00";

            //three decimal points (0.000)
            performanceSheet.Range(string.Format("U2:U{0}",
                lastRowUsed
            )).Style.NumberFormat.Format = "0.000";

            //money with two decimals ($ 0.00)
            performanceSheet.Ranges(string.Format("T2:T{0},W2:W{1},Z2:Z{2}",
                lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "$ 0.00";

            //money with three decimals ($ 0.000)
            performanceSheet.Range(string.Format("V2:V{0}",
                lastRowUsed, lastRowUsed, lastRowUsed
            )).Style.NumberFormat.Format = "$ 0.000";

            // adjust to content
            performanceSheet.Columns().AdjustToContents();
        }