static void Main() { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); var workbook = new ExcelFile(); var worksheet = workbook.Worksheets.Add("Cell Referencing"); worksheet.Cells[0].Value = "Cell referencing examples:"; worksheet.Cells["B2"].Value = "Cell B2."; worksheet.Cells[6, 0].Value = "Cell in row 7 and column A."; worksheet.Rows[2].Cells[0].Value = "Cell in row 3 and column A."; worksheet.Rows["4"].Cells["B"].Value = "Cell in row 4 and column B."; worksheet.Columns[2].Cells[4].Value = "Cell in column C and row 5."; worksheet.Columns["AA"].Cells["6"].Value = "Cell in AA column and row 6."; // Referencing Excel row's cell range. var cellRange = worksheet.Rows[7].Cells; cellRange[0].Value = cellRange.IndexingMode.ToString(); cellRange[3].Value = "D8"; cellRange["B"].Value = "B8"; // Referencing Excel column's cell range. cellRange = worksheet.Columns[7].Cells; cellRange[0].Value = cellRange.IndexingMode.ToString(); cellRange[2].Value = "H3"; cellRange["5"].Value = "H5"; // Referencing arbitrary Excel cell range. cellRange = worksheet.Cells.GetSubrange("I2", "L8"); cellRange.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromArgb(0, 0, 128), LineStyle.Dashed); cellRange["J7"].Value = cellRange.IndexingMode.ToString(); cellRange[0, 0].Value = "I2"; cellRange["J3"].Value = "J3"; cellRange[4].Value = "I3"; // Cell range width is 4 (I J K L). // Set column widths and some print options (for better look when exporting to PDF, XPS and printing). var columnWidths = new double[] { 175, 174, 174, 24, double.NaN, double.NaN, double.NaN, 54, 19, 81 }; for (int i = 0; i < columnWidths.Length; i++) { if (!double.IsNaN(columnWidths[i])) { worksheet.Columns[i].SetWidth(columnWidths[i], LengthUnit.Pixel); } } worksheet.PrintOptions.PrintGridlines = true; worksheet.PrintOptions.PrintHeadings = true; workbook.Save("Cell Referencing.xlsx"); }
public static void StandartHeaderGroupCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(218, 227, 250), SpreadsheetColor.FromName(ColorName.LightBlue)); x.Style.VerticalAlignment = VerticalAlignmentStyle.Top; x.Style.Font.Weight = ExcelFont.MaxWeight; x.Style.Font.Name = "Calibri"; x.Style.Font.Size = 220; x.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black); }
public static void SCHeaderCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(81, 118, 232), SpreadsheetColor.FromArgb(81, 118, 232)); x.Style.VerticalAlignment = VerticalAlignmentStyle.Top; x.Style.Font.Weight = ExcelFont.MaxWeight; x.Style.Font.Name = "Arial"; x.Style.Font.Size = 201; x.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black); }
static void Main(string[] args) { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile ef = new ExcelFile(); ExcelWorksheet ws = ef.Worksheets.Add("Cell Referencing"); ws.Cells[0].Value = "Cell referencing examples:"; ws.Cells["B2"].Value = "Cell B2."; ws.Cells[6, 0].Value = "Cell in row 7 and column A."; ws.Rows[2].Cells[0].Value = "Cell in row 3 and column A."; ws.Rows["4"].Cells["B"].Value = "Cell in row 4 and column B."; ws.Columns[2].Cells[4].Value = "Cell in column C and row 5."; ws.Columns["AA"].Cells["6"].Value = "Cell in AA column and row 6."; // Referencing Excel row's cell range. CellRange cr = ws.Rows[7].Cells; cr[0].Value = cr.IndexingMode.ToString(); cr[3].Value = "D8"; cr["B"].Value = "B8"; // Referencing Excel column's cell range. cr = ws.Columns[7].Cells; cr[0].Value = cr.IndexingMode.ToString(); cr[2].Value = "H3"; cr["5"].Value = "H5"; // Referencing arbitrary Excel cell range. cr = ws.Cells.GetSubrange("I2", "L8"); cr.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromArgb(0, 0, 128), LineStyle.Dashed); cr["J7"].Value = cr.IndexingMode.ToString(); cr[0, 0].Value = "I2"; cr["J3"].Value = "J3"; cr[4].Value = "I3"; // Cell range width is 4 (I J K L). // Autofit columns and some print options (for better look when exporting to pdf, xps and printing). var columnCount = ws.CalculateMaxUsedColumns(); for (int i = 0; i < columnCount; i++) { ws.Columns[i].AutoFit(); } ws.PrintOptions.PrintGridlines = true; ws.PrintOptions.PrintHeadings = true; ef.Save("Cell Referencing.xlsx"); }
public static void StandartInlineCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { var borders = x.Style.Borders; x.Style.Borders.SetBorders(MultipleBorders.All, SpreadsheetColor.FromArgb(105, 105, 105), LineStyle.Thin); //borders[XlBordersIndex.xlInsideHorizontal].Color = XlRgbColor.rgbDarkSlateGrey; //borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin; //borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous; x.Style.VerticalAlignment = VerticalAlignmentStyle.Top; x.Style.Font.Weight = ExcelFont.NormalWeight; x.Style.Font.Name = "Calibri"; x.Style.Font.Size = 201; x.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Black); //x.Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); //IF 1 then Value must be between ExcelFont.MinWeight and ExcelFont.MaxWeight. }
private void formatSheet() { var workbook = ExcelFile.Load("finalReport.csv"); var worksheet = workbook.Worksheets[0]; for (int i = 0; i < 7; i++) { var cell = worksheet.Cells[0, i]; cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(0, 0, 0)); cell.Style.Font.Color = SpreadsheetColor.FromArgb(255, 255, 255); } for (int i = 1; i < worksheet.Rows.Count; i++) { var cell = worksheet.Cells[i, 1]; double res = 0; try { res = (double)cell.Value; } catch (Exception) { } if (res > 125) { cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(255, 155, 155)); cell.Style.Font.Color = SpreadsheetColor.FromArgb(140, 20, 20); } if (res > 75 && res <= 125) { cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(255, 235, 120)); cell.Style.Font.Color = SpreadsheetColor.FromArgb(150, 130, 20); } if (res <= 75) { cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(115, 255, 110)); cell.Style.Font.Color = SpreadsheetColor.FromArgb(30, 140, 25); } } workbook.Save("formatted.xlsx"); }
public static void HeaderGroup3Cell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(0, 112, 192), SpreadsheetColor.FromArgb(0, 112, 192)); }
public static void StandartInlineRecordNumberCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; x.Style.Font.Color = SpreadsheetColor.FromArgb(105, 105, 105); }
public static void EmptyWeaponStyle(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { if (dataRow != null) { //Link to description COLUMN INDEX ["Description" is 8th]; TODO: fast way to link by column name int mean_index = 8; if (dataRow.Length > mean_index && dataRow[mean_index] == null) { x.Offset(mean_index).Resize(4).Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(255, 188, 192), SpreadsheetColor.FromArgb(255, 188, 192)); } //paint next 4 columns } }
public static void CostGroupCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(255, 148, 57), SpreadsheetColor.FromArgb(255, 148, 57)); }
public static void StrengthGroupCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(107, 127, 188), SpreadsheetColor.FromArgb(107, 127, 188)); }
public static void SurvivabilityGroupCell(GemboxTableHandler.Table.Range x, object[] dataRow = null, object GlobalConditionsObject = null) { x.Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(3, 163, 5), SpreadsheetColor.FromArgb(3, 163, 5)); }
public static void Write() { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); // Create new empty workbook. var workbook = new ExcelFile(); // Add new sheet. var worksheet = workbook.Worksheets.Add("Skyscrapers"); // Write title to Excel cell. worksheet.Cells["A1"].Value = "List of tallest buildings (2021):"; // Tabular sample data for writing into an Excel file. var skyscrapers = new object[, ] { { "Rank", "Building", "City", "Country", "Metric", "Imperial", "Floors", "Built (Year)" }, { 1, "Burj Khalifa", "Dubai", "United Arab Emirates", 828, 2717, 163, 2010 }, { 2, "Shanghai Tower", "Shanghai", "China", 632, 2073, 128, 2015 }, { 3, "Abraj Al-Bait Clock Tower", "Mecca", "Saudi Arabia", 601, 1971, 120, 2012 }, { 4, "Ping An Finance Centre", "Shenzhen", "China", 599, 1965, 115, 2017 }, { 5, "Lotte World Tower", "Seoul", "South Korea", 554.5, 1819, 123, 2016 }, { 6, "One World Trade Center", "New York City", "United States", 541.3, 1776, 104, 2014 }, { 7, "Guangzhou CTF Finance Centre", "Guangzhou", "China", 530, 1739, 111, 2016 }, { 8, "Tianjin CTF Finance Centre", "Tianjin", "China", 530, 1739, 98, 2019 }, { 9, "China Zun", "Beijing", "China", 528, 1732, 108, 2018 }, { 10, "Taipei 101", "Taipei", "Taiwan", 508, 1667, 101, 2004 }, { 11, "Shanghai World Financial Center", "Shanghai", "China", 492, 1614, 101, 2008 }, { 12, "International Commerce Centre", "Hong Kong", "China", 484, 1588, 118, 2010 }, { 13, "Central Park Tower", "New York City", "United States", 472, 1550, 98, 2020 }, { 14, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2019 }, { 15, "Landmark 81", "Ho Chi Minh City", "Vietnam", 461.2, 1513, 81, 2018 }, { 16, "Changsha IFS Tower T1", "Changsha", "China", 452.1, 1483, 88, 2018 }, { 17, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 }, { 18, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 }, { 19, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2010 }, { 20, "Suzhou IFS", "Suzhou", "China", 450, 1476, 98, 2019 } }; // // Set row formatting. worksheet.Rows["1"].Style = workbook.Styles[BuiltInCellStyleName.Heading1]; // // Set columns width. worksheet.Columns["A"].SetWidth(8, LengthUnit.ZeroCharacterWidth); // Rank worksheet.Columns["B"].SetWidth(30, LengthUnit.ZeroCharacterWidth); // Building worksheet.Columns["C"].SetWidth(16, LengthUnit.ZeroCharacterWidth); // City worksheet.Columns["D"].SetWidth(20, LengthUnit.ZeroCharacterWidth); // Country worksheet.Columns["E"].SetWidth(9, LengthUnit.ZeroCharacterWidth); // Metric worksheet.Columns["F"].SetWidth(11, LengthUnit.ZeroCharacterWidth); // Imperial worksheet.Columns["G"].SetWidth(9, LengthUnit.ZeroCharacterWidth); // Floors worksheet.Columns["H"].SetWidth(9, LengthUnit.ZeroCharacterWidth); // Built (Year) worksheet.Columns["I"].SetWidth(4, LengthUnit.ZeroCharacterWidth); // Top 10 worksheet.Columns["J"].SetWidth(5, LengthUnit.ZeroCharacterWidth); // Top 20 // Write header data to Excel cells. for (var col = 0; col < skyscrapers.GetLength(1); col++) { worksheet.Cells[3, col].Value = skyscrapers[0, col]; } worksheet.Cells["E3"].Value = "Height"; worksheet.Cells.GetSubrange("A3:A4").Merged = true; // Rank worksheet.Cells.GetSubrange("B3:B4").Merged = true; // Building worksheet.Cells.GetSubrange("C3:C4").Merged = true; // City worksheet.Cells.GetSubrange("D3:D4").Merged = true; // Country worksheet.Cells.GetSubrange("E3:F3").Merged = true; // Height worksheet.Cells.GetSubrange("G3:G4").Merged = true; // Floors worksheet.Cells.GetSubrange("H3:H4").Merged = true; // Built (Year) // // Set header cells formatting. var style = new CellStyle { HorizontalAlignment = HorizontalAlignmentStyle.Center, VerticalAlignment = VerticalAlignmentStyle.Center }; style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(237, 125, 49)); style.Font.Weight = ExcelFont.BoldWeight; style.Font.Color = SpreadsheetColor.FromName(ColorName.White); style.WrapText = true; style.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); worksheet.Cells.GetSubrange("A3:H4").Style = style; // // Write "Top 10" cells. style = new CellStyle { HorizontalAlignment = HorizontalAlignmentStyle.Center, VerticalAlignment = VerticalAlignmentStyle.Center, Font = { Weight = ExcelFont.BoldWeight } }; var mergedRange = worksheet.Cells.GetSubrange("I5:I14"); mergedRange.Merged = true; mergedRange.Value = "T o p 1 0"; style.Rotation = -90; style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(198, 239, 206)); mergedRange.Style = style; // // Write "Top 20" cells. mergedRange = worksheet.Cells.GetSubrange("J5:J24"); mergedRange.Merged = true; mergedRange.Value = "T o p 2 0"; style.IsTextVertical = true; style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(255, 235, 156)); mergedRange.Style = style; mergedRange = worksheet.Cells.GetSubrange("I15:I24"); mergedRange.Merged = true; mergedRange.Style = style; // Write sample data and formatting to Excel cells. for (var row = 0; row < skyscrapers.GetLength(0) - 1; row++) { for (var col = 0; col < skyscrapers.GetLength(1); col++) { var cell = worksheet.Cells[row + 4, col]; cell.Value = skyscrapers[row + 1, col]; cell.Style.Borders[IndividualBorder.Right].LineStyle = LineStyle.Thin; if (row % 2 == 0) { cell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(221, 235, 247)); } if (col == 0) { cell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; } if (col > 3) { cell.Style.Font.Name = "Courier New"; } cell.Style.NumberFormat = col switch { 4 => "#\" m\"", 5 => "#\" ft\"", _ => cell.Style.NumberFormat }; } } worksheet.Cells.GetSubrange("A5", "J24").Style.Borders.SetBorders( MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium); worksheet.Cells.GetSubrange("A3", "H4").Style.Borders.SetBorders( MultipleBorders.Vertical | MultipleBorders.Top, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium); worksheet.Cells.GetSubrange("A5", "I14").Style.Borders.SetBorders( MultipleBorders.Bottom | MultipleBorders.Right, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Medium); worksheet.PrintOptions.FitWorksheetWidthToPages = 1; // Save workbook as an Excel file. var path = Assembly.GetCallingAssembly().CodeBase; if (path == null) { return; } var projectPath = new Uri(path.Substring(0, path.LastIndexOf("bin", StringComparison.Ordinal))).LocalPath; var excelFilePath = new Uri(projectPath).LocalPath + @"\Excel_With_Styles.xlsx"; workbook.Save(excelFilePath); } }
static void Main(string[] args) { // If using Professional version, put your serial key below. SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile ef = new ExcelFile(); ExcelWorksheet ws = ef.Worksheets.Add("Styles and Formatting"); ws.Cells[0, 1].Value = "Cell style examples:"; ws.PrintOptions.PrintGridlines = true; int row = 0; // Column width of 4, 30 and 36 characters. ws.Columns[0].Width = 4 * 256; ws.Columns[1].Width = 30 * 256; ws.Columns[2].Width = 36 * 256; ws.Cells[row += 2, 1].Value = ".Style.Borders.SetBorders(...)"; ws.Cells[row, 2].Style.Borders.SetBorders(MultipleBorders.All | MultipleBorders.Diagonal, SpreadsheetColor.FromArgb(252, 1, 1), LineStyle.Thin); ws.Cells[row += 2, 1].Value = ".Style.FillPattern.SetPattern(...)"; ws.Cells[row, 2].Style.FillPattern.SetPattern(FillPatternStyle.ThinHorizontalCrosshatch, SpreadsheetColor.FromName(ColorName.Green), SpreadsheetColor.FromName(ColorName.Yellow)); ws.Cells[row += 2, 1].Value = ".Style.Font.Color ="; ws.Cells[row, 2].Value = "Color.Blue"; ws.Cells[row, 2].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue); ws.Cells[row += 2, 1].Value = ".Style.Font.Italic ="; ws.Cells[row, 2].Value = "true"; ws.Cells[row, 2].Style.Font.Italic = true; ws.Cells[row += 2, 1].Value = ".Style.Font.Name ="; ws.Cells[row, 2].Value = "Comic Sans MS"; ws.Cells[row, 2].Style.Font.Name = "Comic Sans MS"; ws.Cells[row += 2, 1].Value = ".Style.Font.ScriptPosition ="; ws.Cells[row, 2].Value = "ScriptPosition.Superscript"; ws.Cells[row, 2].Style.Font.ScriptPosition = ScriptPosition.Superscript; ws.Cells[row += 2, 1].Value = ".Style.Font.Size ="; ws.Cells[row, 2].Value = "18 * 20"; ws.Cells[row, 2].Style.Font.Size = 18 * 20; ws.Cells[row += 2, 1].Value = ".Style.Font.Strikeout ="; ws.Cells[row, 2].Value = "true"; ws.Cells[row, 2].Style.Font.Strikeout = true; ws.Cells[row += 2, 1].Value = ".Style.Font.UnderlineStyle ="; ws.Cells[row, 2].Value = "UnderlineStyle.Double"; ws.Cells[row, 2].Style.Font.UnderlineStyle = UnderlineStyle.Double; ws.Cells[row += 2, 1].Value = ".Style.Font.Weight ="; ws.Cells[row, 2].Value = "ExcelFont.BoldWeight"; ws.Cells[row, 2].Style.Font.Weight = ExcelFont.BoldWeight; ws.Cells[row += 2, 1].Value = ".Style.HorizontalAlignment ="; ws.Cells[row, 2].Value = "HorizontalAlignmentStyle.Center"; ws.Cells[row, 2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; ws.Cells[row += 2, 1].Value = ".Style.Indent"; ws.Cells[row, 2].Value = "five"; ws.Cells[row, 2].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left; ws.Cells[row, 2].Style.Indent = 5; ws.Cells[row += 2, 1].Value = ".Style.IsTextVertical = "; ws.Cells[row, 2].Value = "true"; // Set row height to 60 points. ws.Rows[row].Height = 60 * 20; ws.Cells[row, 2].Style.IsTextVertical = true; ws.Cells[row += 2, 1].Value = ".Style.NumberFormat"; ws.Cells[row, 2].Value = 1234; ws.Cells[row, 2].Style.NumberFormat = "#.##0,00 [$Krakozhian Money Units]"; ws.Cells[row += 2, 1].Value = ".Style.Rotation"; ws.Cells[row, 2].Value = "35 degrees up"; ws.Cells[row, 2].Style.Rotation = 35; ws.Cells[row += 2, 1].Value = ".Style.ShrinkToFit"; ws.Cells[row, 2].Value = "This property is set to true so this text appears shrunk."; ws.Cells[row, 2].Style.ShrinkToFit = true; ws.Cells[row += 2, 1].Value = ".Style.VerticalAlignment ="; ws.Cells[row, 2].Value = "VerticalAlignmentStyle.Top"; // Set row height to 30 points. ws.Rows[row].Height = 30 * 20; ws.Cells[row, 2].Style.VerticalAlignment = VerticalAlignmentStyle.Top; ws.Cells[row += 2, 1].Value = ".Style.WrapText"; ws.Cells[row, 2].Value = "This property is set to true so this text appears broken into multiple lines."; ws.Cells[row, 2].Style.WrapText = true; ef.Save("Styles and Formatting.xlsx"); }
/// <summary> /// /// </summary> /// <param name="days"></param> /// <param name="progressCallback"></param> /// <returns></returns> public static void CreateDocument(string fname, bool createXlsxFile, List <Day> days, Action <int> progressCallback) { if (string.IsNullOrEmpty(fname)) { throw new ArgumentNullException("fname"); } var firstDay = days.First(); string monthAndYearString = GetDateStringForDate(firstDay.DateTime); SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); ExcelFile excel = new ExcelFile(); ExcelWorksheet ws = excel.Worksheets.Add("Расписание"); using (DocX document = DocX.Create(fname)) { var t = document.AddTable(HeaderRowsCount + days.Count, ColumnCount); t.Alignment = Alignment.center; t.AutoFit = AutoFit.Window; t.Rows[0].MergeCells(0, ColumnCount - 1); t.Rows[1].MergeCells(0, ColumnCount - 1); // excel var range = ws.Cells.GetSubrange("A1", GetCellName(0, ColumnCount - 1)); range.Merged = true; range = ws.Cells.GetSubrange("A2", GetCellName(1, ColumnCount - 1)); range.Merged = true; range = ws.Cells.GetSubrangeAbsolute(0, 0, HeaderRowsCount + days.Count - 1, ColumnCount - 1); range.Style.Borders.SetBorders(MultipleBorders.All, Color.Black, LineStyle.Thin); // // // Шапка таблицы. // var p = t.Rows[0].Cells[0].Paragraphs[0].Append("Расписание Богослужений"); p.Alignment = Alignment.center; p.Bold(); p.FontSize(20.0); p.Color(Color.White); t.Rows[0].Cells[0].FillColor = ColorTranslator.FromHtml("#2ec0fd"); t.Rows[0].Cells[0].VerticalAlignment = VerticalAlignment.Center; // excel // Дата. ws.Columns["A"].Width = 10 * 256; // День недели. ws.Columns["B"].Width = 14 * 256; // Месяцеслов. ws.Columns["C"].Width = 24 * 256; // Переходящие праздники. ws.Columns["D"].Width = 20 * 256; // Богослужение. ws.Columns["E"].Width = 25 * 256; var tmpCell = ws.Cells["A1"]; tmpCell.Style.Font.Size = 20 * 20; tmpCell.Style.Font.Color = Color.White; tmpCell.Style.Font.Weight = ExcelFont.MaxWeight; tmpCell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(0x2e, 0xc0, 0xfd)); tmpCell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; tmpCell.Style.VerticalAlignment = VerticalAlignmentStyle.Center; tmpCell.Value = "Расписание Богослужений"; // p = t.Rows[1].Cells[0].Paragraphs[0].Append(monthAndYearString); p.Alignment = Alignment.center; p.Bold(); t.Rows[1].Cells[0].FillColor = ColorTranslator.FromHtml("#cccccc"); t.Rows[1].Cells[0].VerticalAlignment = VerticalAlignment.Center; // excel tmpCell = ws.Cells["A2"]; tmpCell.Style.FillPattern.SetSolid(SpreadsheetColor.FromArgb(0xcc, 0xcc, 0xcc)); tmpCell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; tmpCell.Style.VerticalAlignment = VerticalAlignmentStyle.Center; tmpCell.Value = monthAndYearString; // t.Rows[2].Cells[0].Paragraphs[0].Append("Дата").Alignment = Alignment.center; t.Rows[2].Cells[1].Paragraphs[0].Append("День недели").Alignment = Alignment.center; t.Rows[2].Cells[2].Paragraphs[0].Append("Месяцеслов").Alignment = Alignment.center; t.Rows[2].Cells[3].Paragraphs[0].Append("Переходящие праздники").Alignment = Alignment.center; t.Rows[2].Cells[4].Paragraphs[0].Append("Богослужение").Alignment = Alignment.center; // excel var a3 = ws.Cells["A3"]; var b3 = ws.Cells["B3"]; var c3 = ws.Cells["C3"]; var d3 = ws.Cells["D3"]; var e3 = ws.Cells["E3"]; a3.Style.HorizontalAlignment = b3.Style.HorizontalAlignment = c3.Style.HorizontalAlignment = d3.Style.HorizontalAlignment = e3.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; a3.Style.VerticalAlignment = b3.Style.VerticalAlignment = c3.Style.VerticalAlignment = d3.Style.VerticalAlignment = e3.Style.VerticalAlignment = VerticalAlignmentStyle.Center; a3.Style.WrapText = b3.Style.WrapText = c3.Style.WrapText = d3.Style.WrapText = e3.Style.WrapText = true; a3.Value = "Дата"; b3.Value = "День недели"; c3.Value = "Месяцеслов"; d3.Value = "Переходящие праздники"; e3.Value = "Богослужение"; // for (int i = 0; i < days.Count; i += 1) { /** Праздник. */ bool isCeleb; var line = days[i].GetRowContent(out isCeleb); Color colorDay; if (isCeleb || days[i].DateTime.DayOfWeek == DayOfWeek.Sunday) { colorDay = Color.Red; } else { colorDay = Color.Black; } // // Добавить строки. // for (int j = 0; j < line.Length; j += 1) { var para = t.Rows[HeaderRowsCount + i].Cells[j].Paragraphs[0].Append(line[j]); para.Alignment = Alignment.center; para.Color(colorDay); para.Culture(RussianCulture); para.FontSize(10.0); //excel var cell = ws.Cells[GetCellName(HeaderRowsCount + i, j)]; cell.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; cell.Style.VerticalAlignment = VerticalAlignmentStyle.Center; cell.Style.Font.Color = colorDay; cell.Style.Font.Size = 20 * 10; cell.Style.WrapText = true; cell.Value = line[j]; // if (j == 2) { para.Bold(); //excel cell.Style.Font.Weight = ExcelFont.MaxWeight; // } } progressCallback(GetProgress(i, days.Count)); } // // Вставить таблицу и сохранить документ. // document.InsertTable(t); document.Save(); excel.Save(Path.ChangeExtension(fname, ".xlsx")); } }
public void setTemplateFormat(int whatTemplate, ref ExcelFile workbook) { if (whatTemplate == 1) { // Row 1 Text/Cell Size and boldness workbook.Worksheets[0].Rows[0].Height = (workbook.Worksheets[0].Rows[0].Height) + 2000; workbook.Worksheets[0].Columns[0].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Rows[0].Style.Font.Size = 560; workbook.Worksheets[0].Rows[0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Columns[3].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; // Alignments workbook.Worksheets[0].Columns[0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Left; workbook.Worksheets[0].Cells[0, 0].Style.VerticalAlignment = VerticalAlignmentStyle.Center; workbook.Worksheets[0].Columns[6].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right; workbook.Worksheets[0].Cells.GetSubrange("A20:G29").Style.VerticalAlignment = VerticalAlignmentStyle.Center; workbook.Worksheets[0].Cells.GetSubrange("A20:F28").Style.HorizontalAlignment = HorizontalAlignmentStyle.Center; workbook.Worksheets[0].Cells["F29"].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right; // Invoice details text left side workbook.Worksheets[0].Cells[2, 0].Value = "ABN:"; workbook.Worksheets[0].Cells[2, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[4, 0].Value = "Email:"; workbook.Worksheets[0].Cells[4, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[6, 0].Value = "Contact No:"; workbook.Worksheets[0].Cells[6, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[8, 0].Value = "Address:"; workbook.Worksheets[0].Cells[8, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[13, 0].Value = "Bank BSB:"; workbook.Worksheets[0].Cells[13, 0].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[14, 0].Value = "Account No:"; workbook.Worksheets[0].Cells[14, 0].Style.Font.Weight = ExcelFont.BoldWeight; // Invoice details text right side workbook.Worksheets[0].Cells[0, 6].Value = "Invoice"; workbook.Worksheets[0].Cells[0, 6].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[2, 6].Value = "Date of Invoice:"; workbook.Worksheets[0].Cells[2, 6].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[4, 6].Value = "Invoice No:"; workbook.Worksheets[0].Cells[4, 6].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[7, 6].Value = "Invoice To:"; workbook.Worksheets[0].Cells[7, 6].Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells[10, 6].Value = "Address:"; workbook.Worksheets[0].Cells[10, 6].Style.Font.Weight = ExcelFont.BoldWeight; // Table Colum descriptions workbook.Worksheets[0].Cells.GetSubrange("A20:G20").Style.Font.Weight = ExcelFont.BoldWeight; workbook.Worksheets[0].Cells.GetSubrange("A20:C20").Merged = true; workbook.Worksheets[0].Cells.GetSubrange("G21:G29").Style.NumberFormat = "$#,##0.00"; workbook.Worksheets[0].Cells["A20"].Value = "Description"; workbook.Worksheets[0].Cells["D20"].Value = "Date of Work"; workbook.Worksheets[0].Cells["E20"].Value = "Price Per Unit"; workbook.Worksheets[0].Cells["F20"].Value = "Quanitity"; workbook.Worksheets[0].Cells["G20"].Value = "Price"; workbook.Worksheets[0].Cells["F29"].Value = "Total - "; // Job Item table formatting workbook.Worksheets[0].Rows[19].Height = 600; workbook.Worksheets[0].Rows[20].Height = 500; workbook.Worksheets[0].Rows[21].Height = 500; workbook.Worksheets[0].Rows[22].Height = 500; workbook.Worksheets[0].Rows[23].Height = 500; workbook.Worksheets[0].Rows[24].Height = 500; workbook.Worksheets[0].Rows[25].Height = 500; workbook.Worksheets[0].Rows[26].Height = 500; workbook.Worksheets[0].Rows[27].Height = 500; workbook.Worksheets[0].Rows[28].Height = 500; workbook.Worksheets[0].Rows[29].Height = 500; workbook.Worksheets[0].Columns[1].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[2].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[3].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[4].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[5].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; workbook.Worksheets[0].Columns[6].Width = (workbook.Worksheets[0].Columns[0].Width) + 500; // Table fill and borders workbook.Worksheets[0].Cells.GetSubrange("A20:G20").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A22:G22").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A24:G24").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A26:G26").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A28:G28").Style.FillPattern.SetPattern(FillPatternStyle.Solid, SpreadsheetColor.FromArgb(252, 228, 214), SpreadsheetColor.FromArgb(252, 228, 214)); workbook.Worksheets[0].Cells.GetSubrange("A20:G28").Style.Borders.SetBorders(MultipleBorders.InsideHorizontal, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); workbook.Worksheets[0].Cells.GetSubrange("A20:G28").Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); workbook.Worksheets[0].Cells.GetSubrange("D20:D28").Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); workbook.Worksheets[0].Cells.GetSubrange("F20:G28").Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); workbook.Worksheets[0].Cells.GetSubrange("G20:G29").Style.Borders.SetBorders(MultipleBorders.Outside, SpreadsheetColor.FromName(ColorName.Black), LineStyle.Thin); // Paid Cell workbook.Worksheets[0].Cells["B32"].Style.Font.Color = SpreadsheetColor.FromName(ColorName.Red); workbook.Worksheets[0].Cells["B32"].Style.Font.Size = 1200; } }