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;
}
}
