/// <summary>
/// This demo shows the usage of merging cells, protecting cells, worksheet password protection and workbook protection
/// </summary>
private static void Demo6()
{
Workbook workbook = new Workbook("test6.xlsx", "Sheet1"); // Create new workbook
workbook.CurrentWorksheet.AddNextCell("Merged1"); // Add cell A1
workbook.CurrentWorksheet.MergeCells("A1:C1"); // Merge cells from A1 to C1
workbook.CurrentWorksheet.GoToNextRow(); // Go to next row
workbook.CurrentWorksheet.AddNextCell(false); // Add cell A2
workbook.CurrentWorksheet.MergeCells("A2:D2"); // Merge cells from A2 to D1
workbook.CurrentWorksheet.GoToNextRow(); // Go to next row
workbook.CurrentWorksheet.AddNextCell("22.2d"); // Add cell A3
workbook.CurrentWorksheet.MergeCells("A3:E4"); // Merge cells from A3 to E4
workbook.AddWorksheet("Protected"); // Add a new worksheet
workbook.CurrentWorksheet.AddAllowedActionOnSheetProtection(Worksheet.SheetProtectionValue.sort); // Allow to sort sheet (worksheet is automatically set as protected)
workbook.CurrentWorksheet.AddAllowedActionOnSheetProtection(Worksheet.SheetProtectionValue.insertRows); // Allow to insert rows
workbook.CurrentWorksheet.AddAllowedActionOnSheetProtection(Worksheet.SheetProtectionValue.selectLockedCells); // Allow to select cells (locked cells caused automatically to select unlocked cells)
workbook.CurrentWorksheet.AddNextCell("Cell A1"); // Add cell A1
workbook.CurrentWorksheet.AddNextCell("Cell B1"); // Add cell B1
workbook.CurrentWorksheet.Cells["A1"].SetCellLockedState(false, true); // Set the locking state of cell A1 (not locked but value is hidden when cell selected)
workbook.AddWorksheet("PWD-Protected"); // Add a new worksheet
workbook.CurrentWorksheet.AddCell("This worksheet is password protected. The password is:", 0, 0); // Add cell A1
workbook.CurrentWorksheet.AddCell("test123", 0, 1); // Add cell A2
workbook.CurrentWorksheet.SetSheetProtectionPassword("test123"); // Set the password "test123"
workbook.SetWorkbookProtection(true, true, true, null); // Set workbook protection (windows locked, structure locked, no password)
workbook.Save(); // Save the workbook
}
/// <summary>
/// This demo shows the usage of several data types, the method AddCell, more than one worksheet and the SaveAs method
/// </summary>
private static void Demo2()
{
Workbook workbook = new Workbook(false); // Create new workbook
workbook.AddWorksheet("Sheet1"); // Add a new Worksheet and set it as current sheet
workbook.CurrentWorksheet.AddNextCell("月曜日"); // Add cell A1 (Unicode)
workbook.CurrentWorksheet.AddNextCell(-987); // Add cell B1
workbook.CurrentWorksheet.AddNextCell(false); // Add cell C1
workbook.CurrentWorksheet.GoToNextRow(); // Go to Row 2
workbook.CurrentWorksheet.AddNextCell(-123.456d); // Add cell A2
workbook.CurrentWorksheet.AddNextCell(-123.789f); // Add cell B2
workbook.CurrentWorksheet.AddNextCell(DateTime.Now); // Add cell C3
workbook.AddWorksheet("Sheet2"); // Add a new Worksheet and set it as current sheet
workbook.CurrentWorksheet.AddCell("ABC", "A1"); // Add cell A1
workbook.CurrentWorksheet.AddCell(779, 2, 1); // Add cell C2 (zero based addresses: column 2=C, row 1=2)
workbook.CurrentWorksheet.AddCell(false, 3, 2); // Add cell D3 (zero based addresses: column 3=D, row 2=3)
workbook.CurrentWorksheet.AddNextCell(0); // Add cell E3 (direction: column to column)
List <object> values = new List <object>()
{
"V1", true, 16.8
}; // Create a List of values
workbook.CurrentWorksheet.AddCellRange(values, "A4:C4"); // Add a cell range to A4 - C4
workbook.SaveAs("test2.xlsx"); // Save the workbook
}
public static void Borders(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("Borders");
worksheet.Columns[0].WidthCharacters = 11;
worksheet["A1"].Value = "Left Thick Border";
worksheet["B1"].Borders.Left = new Border(BorderStyle.Thick);
worksheet["A3"].Value = "Right Thick Border";
worksheet["B3"].Borders.Right = new Border(BorderStyle.Thick);
worksheet["A5"].Value = "Top Thick Border";
worksheet["B5"].Borders.Top = new Border(BorderStyle.Thick);
worksheet["A7"].Value = "Bottom Thick Border";
worksheet["B7"].Borders.Bottom = new Border(BorderStyle.Thick);
worksheet["A9"].Value = "Diagonal Up Border";
worksheet["B9"].Borders.SetDiagonalUpBorder(BorderStyle.Thick);
worksheet["A11"].Value = "Diagonal Down Border";
worksheet["B11"].Borders.SetDiagonalDownBorder(BorderStyle.Thick);
worksheet["A13"].Value = "Slant Dash Dot";
worksheet["B13"].Borders.SetBoxBorder(BorderStyle.SlantDashDot);
worksheet["A15"].Value = "Box Red";
worksheet["B15"].Borders.SetBoxBorder(BorderStyle.Thick, SpreadsheetColor.Red);
worksheet["A17"].Value = "Thin Cross Green";
worksheet["B17"].Borders.SetDiagonalCrossBorder(BorderStyle.Thin, SpreadsheetColor.Green);
}
/// <summary>
/// Resolves the workbook with all worksheets from the loaded file
/// </summary>
/// <returns>Workbook object</returns>
public Workbook GetWorkbook()
{
Workbook wb = new Workbook(false);
Worksheet ws;
int index = 1;
foreach (KeyValuePair <int, WorksheetReader> reader in worksheets)
{
ws = new Worksheet(workbook.WorksheetDefinitions[reader.Key], index, wb);
foreach (KeyValuePair <string, Cell> cell in reader.Value.Data)
{
cell.Value.WorksheetReference = ws;
if (reader.Value.StyleAssignment.ContainsKey(cell.Key))
{
Style style = styleReaderContainer.GetStyle(reader.Value.StyleAssignment[cell.Key], true);
if (style != null)
{
cell.Value.SetStyle(style);
}
}
ws.AddCell(cell.Value, cell.Key);
}
wb.AddWorksheet(ws);
index++;
}
return(wb);
}
/// <summary>
/// This method shows the usage of AddNextCell with several data types and formulas. Furthermore, the several types of Addresses are demonstrated
/// </summary>
private static void Demo1()
{
Workbook workbook = new Workbook("test1.xlsx", "Sheet1"); // Create new workbook
workbook.CurrentWorksheet.AddNextCell("Test"); // Add cell A1
workbook.CurrentWorksheet.AddNextCell(123); // Add cell B1
workbook.CurrentWorksheet.AddNextCell(true); // Add cell C1
workbook.CurrentWorksheet.GoToNextRow(); // Go to Row 2
workbook.CurrentWorksheet.AddNextCell(123.456d); // Add cell A2
workbook.CurrentWorksheet.AddNextCell(123.789f); // Add cell B2
workbook.CurrentWorksheet.AddNextCell(DateTime.Now); // Add cell C2
workbook.CurrentWorksheet.GoToNextRow(); // Go to Row 3
workbook.CurrentWorksheet.AddNextCellFormula("B1*22"); // Add cell A3 as formula (B1 times 22)
workbook.CurrentWorksheet.AddNextCellFormula("ROUNDDOWN(A2,1)"); // Add cell B3 as formula (Floor A2 with one decimal place)
workbook.CurrentWorksheet.AddNextCellFormula("PI()"); // Add cell C3 as formula (Pi = 3.14.... )
workbook.AddWorksheet("Addresses"); // Add new worksheet
workbook.CurrentWorksheet.CurrentCellDirection = Worksheet.CellDirection.Disabled; // Disable automatic addressing
workbook.CurrentWorksheet.AddCell("Default", 0, 0); // Add a value
Cell.Address address = new Cell.Address(1, 0, Cell.AddressType.Default); // Create Address with default behavior
workbook.CurrentWorksheet.AddCell(address.ToString(), 1, 0); // Add the string of the address
workbook.CurrentWorksheet.AddCell("Fixed Column", 0, 1); // Add a value
address = new Cell.Address(1, 1, Cell.AddressType.FixedColumn); // Create Address with fixed column
workbook.CurrentWorksheet.AddCell(address.ToString(), 1, 1); // Add the string of the address
workbook.CurrentWorksheet.AddCell("Fixed Row", 0, 2); // Add a value
address = new Cell.Address(1, 2, Cell.AddressType.FixedRow); // Create Address with fixed row
workbook.CurrentWorksheet.AddCell(address.ToString(), 1, 2); // Add the string of the address
workbook.CurrentWorksheet.AddCell("Fixed Row and Column", 0, 3); // Add a value
address = new Cell.Address(1, 3, Cell.AddressType.FixedRowAndColumn); // Create Address with fixed row and column
workbook.CurrentWorksheet.AddCell(address.ToString(), 1, 3); // Add the string of the address
workbook.Save(); // Save the workbook
}
/// <summary>
/// This demo shows the usage of hiding rows and columns, auto-filter and worksheet name sanitizing
/// </summary>
private static void Demo7()
{
Workbook workbook = new Workbook(false); // Create new workbook without worksheet
String invalidSheetName = "Sheet?1"; // ? is not allowed in the names of worksheets
String sanitizedSheetName = Worksheet.SanitizeWorksheetName(invalidSheetName, workbook); // Method to sanitize a worksheet name (replaces ? with _)
workbook.AddWorksheet(sanitizedSheetName); // Add new worksheet
Worksheet ws = workbook.CurrentWorksheet; // Create reference (shortening)
List <object> values = new List <object>()
{
"Cell A1", "Cell B1", "Cell C1", "Cell D1"
}; // Create a List of values
ws.AddCellRange(values, "A1:D1"); // Insert cell range
values = new List <object>()
{
"Cell A2", "Cell B2", "Cell C2", "Cell D2"
}; // Create a List of values
ws.AddCellRange(values, "A2:D2"); // Insert cell range
values = new List <object>()
{
"Cell A3", "Cell B3", "Cell C3", "Cell D3"
}; // Create a List of values
ws.AddCellRange(values, "A3:D3"); // Insert cell range
ws.AddHiddenColumn("C"); // Hide column C
ws.AddHiddenRow(1); // Hider row 2 (zero-based: 1)
ws.SetAutoFilter(1, 3); // Set auto-filter for column B to D
workbook.SaveAs("test7.xlsx"); // Save the workbook
}
/// <summary>
/// This demo shows the usage of cell and worksheet selection, auto-sanitizing of worksheet names
/// </summary>
private static void Demo8()
{
Workbook workbook = new Workbook("test8.xlsx", "Sheet*1", true); // Create new workbook with invalid sheet name (*); Auto-Sanitizing will replace * with _
workbook.CurrentWorksheet.AddNextCell("Test"); // Add cell A1
workbook.CurrentWorksheet.SetSelectedCells("A5:B10"); // Set the selection to the range A5:B10
workbook.AddWorksheet("Sheet2"); // Create new worksheet
workbook.CurrentWorksheet.AddNextCell("Test2"); // Add cell A1
Cell.Range range = new Cell.Range(new Cell.Address(1, 1), new Cell.Address(3, 3)); // Create a cell range for the selection B2:D4
workbook.CurrentWorksheet.SetSelectedCells(range); // Set the selection to the range
workbook.AddWorksheet("Sheet2", true); // Create new worksheet with already existing name; The name will be changed to Sheet21 due to auto-sanitizing (appending of 1)
workbook.CurrentWorksheet.AddNextCell("Test3"); // Add cell A1
workbook.CurrentWorksheet.SetSelectedCells(new Cell.Address(2, 2), new Cell.Address(4, 4)); // Set the selection to the range C3:E5
workbook.SetSelectedWorksheet(1); // Set the second Tab as selected (zero-based: 1)
workbook.Save(); // Save the workbook
}
/// <summary>
/// Resolves the workbook with all worksheets from the loaded file
/// </summary>
/// <returns>Workbook object</returns>
public Workbook GetWorkbook()
{
Workbook wb = new Workbook(false);
wb.SetImportState(true);
Worksheet ws;
foreach (KeyValuePair <int, WorksheetReader> reader in worksheets)
{
WorkbookReader.WorksheetDefinition definition = workbook.WorksheetDefinitions[reader.Key];
ws = new Worksheet(definition.WorksheetName, definition.SheetID, wb);
ws.Hidden = definition.Hidden;
foreach (KeyValuePair <string, Cell> cell in reader.Value.Data)
{
cell.Value.WorksheetReference = ws;
if (reader.Value.StyleAssignment.ContainsKey(cell.Key))
{
Style style = styleReaderContainer.GetStyle(reader.Value.StyleAssignment[cell.Key], true);
if (style != null)
{
cell.Value.SetStyle(style);
}
}
ws.AddCell(cell.Value, cell.Key);
}
wb.AddWorksheet(ws);
}
wb.SetImportState(false);
return(wb);
}
/// <summary>
/// Build Excel file
/// </summary>
/// <param name="items">product</param>
/// <param name="title"></param>
/// <returns></returns>
public static string Build(List <Ttn> items, string title = "Без заголовка")
{
var workbook = new Workbook();
PropertiesBuilder.Apply(workbook);
StylesBuilder.Apply(workbook);
// create first worksheet
var worksheet = new Worksheet("Лист 1");
worksheet.AddCellWithStyle(0, 0, title, StylesBuilder.TitleStyle, 6U);
// adding headers
worksheet.AddCellWithStyle(1, 0, "ТТН № ", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 1, "Дата", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 2, "Время", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 3, "Продукция", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 4, "Кол", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 5, "Цена", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 6, "Сумма", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 7, "Модель", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 8, "Автобаза", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 9, "Гос. №", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 10, "Водитель", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 11, "Адрес", StylesBuilder.Headerstyle);
var row = 2;
foreach (var item in items)
{
worksheet.AddCell(row, 0, item.Num);
worksheet.AddCell(row, 1, item.Dat.ToString());
worksheet.AddCell(row, 2, item.Tm);
worksheet.AddCell(row, 3, item.Good);
worksheet.AddCell(row, 4, item.Kol);
worksheet.AddCell(row, 5, item.Price);
worksheet.AddCell(row, 6, item.Sm);
worksheet.AddCell(row, 7, item.Amodel);
worksheet.AddCell(row, 8, item.Ab);
worksheet.AddCell(row, 9, item.Gn);
worksheet.AddCell(row, 10, item.Driv);
worksheet.AddCell(row, 11, item.Adres);
row++;
}
// appending footer with formulas
//worksheet.AddCellWithStyle(row, 0, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyle(row, 1, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyle(row, 2, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyle(row, 3, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyle(row, 4, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyleAndFormula(row, 5, 0, "=AVERAGE(R[-" + (row - 1) + "]C:R[-1]C)", StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyleAndFormula(row, 6, 0, "=SUM(R[-" + (row - 1) + "]C:R[-1]C)", StylesBuilder.SummaryStyle);
workbook.AddWorksheet(worksheet);
return(workbook.ExportToXML());
}
public Excel()
{
wb.ExcelWorkbook.ActiveSheet = 1;
wb.ExcelWorkbook.DisplayInkNotes = false;
wb.ExcelWorkbook.FirstVisibleSheet = 1;
wb.ExcelWorkbook.ProtectStructure = false;
ws = new Worksheet("Анкеты");
wb.AddWorksheet(ws);
}
static void Main(string[] args)
{
var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
worksheet.Name = "View1";
worksheet.Columns["A"].WidthPixels = 250;
worksheet["A1"].Value = "Show formulas";
worksheet["A2"].Formula = "=SUM(1)";
worksheet["A2"].Value = "Do not show row and column headers";
worksheet["A3"].Value = "Zoom 125%";
worksheet["A4"].Value = "Tab color: Red";
worksheet["A5"].Value = "Gridline color: Green";
worksheet.View.ShowFormulas = true;
worksheet.View.ShowRowColumnHeaders = false;
worksheet.View.ZoomScale = 125;
worksheet.Properties.TabColor = SpreadsheetColor.Red;
worksheet.View.GridlineColor = SpreadsheetColor.Green;
var worksheet2 = workbook.AddWorksheet("View2");
worksheet2.Columns["A"].WidthPixels = 250;
worksheet2["A1"].Value = "Do not show zeros";
worksheet2["B1"].Value = 0;
worksheet2["A2"].Value = "Do not show gridlines";
worksheet2["A3"].Value = "Page Layout View Zoom 75%";
worksheet2["A4"].Value = "Page Layout View";
worksheet2.View.ShowZeros = false;
worksheet2.View.ShowGridLines = false;
worksheet2.View.ZoomScalePageLayoutView = 75;
worksheet2.View.ViewType = SheetViewType.PageLayout;
var worksheet3 = workbook.AddWorksheet("Right to Left");
worksheet3.View.RightToLeft = true;
workbook.SaveAs(@"..\Out\WorksheetView.xlsx");
}
public ActionResult Export()
{
string result = string.Empty;
Workbook wb = new Workbook();
// properties
wb.Properties.Author = "Calabonga";
wb.Properties.Created = DateTime.Today;
wb.Properties.LastAutor = "Calabonga";
wb.Properties.Version = "14";
// options sheets
wb.ExcelWorkbook.ActiveSheet = 1;
wb.ExcelWorkbook.DisplayInkNotes = false;
wb.ExcelWorkbook.FirstVisibleSheet = 1;
wb.ExcelWorkbook.ProtectStructure = false;
wb.ExcelWorkbook.WindowHeight = 800;
wb.ExcelWorkbook.WindowTopX = 0;
wb.ExcelWorkbook.WindowTopY = 0;
wb.ExcelWorkbook.WindowWidth = 600;
// get data
List <EntranceComposition> entranceCompositions = db.EntranceComposition.ToList();
Worksheet ws3 = new Worksheet("Поставки");
ws3.AddCell(0, 0, "Поставка");
ws3.AddCell(0, 1, "Ингредиент");
ws3.AddCell(0, 2, "Количество");
ws3.AddCell(0, 3, "Цена");
int totalRows = 0;
// appending rows with data
for (int i = 0; i < entranceCompositions.Count; i++)
{
ws3.AddCell(i + 1, 0, entranceCompositions[i].Entrance);
ws3.AddCell(i + 1, 1, entranceCompositions[i].Ingredient1.Name.ToString());
ws3.AddCell(i + 1, 2, entranceCompositions[i].Count);
ws3.AddCell(i + 1, 3, entranceCompositions[i].Cost);
totalRows++;
}
wb.AddWorksheet(ws3);
// generate xml
string workstring = wb.ExportToXML();
// Send to user file
return(new ExcelResult("Entrances.xls", workstring));
}
public static void Fills(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("Fills");
worksheet.Columns[0].WidthCharacters = 20;
worksheet["A1"].Value = "Solid Blue";
worksheet["B1"].Fill = CellFill.CreateSolidFill(SpreadsheetColor.Blue);
worksheet["A3"].Value = "Green Red Dark Trellis";
worksheet["B3"].Fill = CellFill.CreatePattern(SpreadsheetColor.Green, SpreadsheetColor.Red, PatternType.DarkTrellis);
worksheet["A5"].Value = "Linear Gradient";
worksheet["B5"].Fill = CellFill.CreateGradient(GradientType.Linear, 180, SpreadsheetColor.Blue, SpreadsheetColor.Red);
}
/// <summary>
/// This demo shows the usage of hiding workbooks and worksheets
/// </summary>
private static void Demo13()
{
Workbook wb = new Workbook("demo13(hidden_workbook).xlsx", "hiddenWB"); // Create a new workbook
wb.CurrentWorksheet.AddNextCell("Hidden Workbook");
wb.Hidden = true; // Set the workbook hidden (Set visible again in another, visible workbook)
wb.Save(); // Save the workbook
Workbook wb2 = new Workbook("demo13(hidden_worksheet).xlsx", "visible"); // Create a new workbook
wb2.CurrentWorksheet.AddNextCell("Visible Worksheet");
wb2.AddWorksheet("hidden"); // Create new worksheet
wb2.CurrentWorksheet.AddNextCell("Hidden Worksheet");
wb2.CurrentWorksheet.Hidden = true; // Set the current worksheet hidden
wb2.Save(); // Save the workbook
}
static void Main(string[] args)
{
var workbook = new Workbook();
var freezeSheet = workbook.Worksheets[0];
freezeSheet.Name = "Freeze";
//Freeze the first column and the top 2 rows
freezeSheet.View.Panes.FreezePanes("B3");
var splitSheet = workbook.AddWorksheet("Split");
//Create four panes by splitting at F10
splitSheet.View.Panes.SplitPanes("F10");
workbook.SaveAs(@"..\Out\FreezeSplit.xlsx");
}
public static void ThreeDLine(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("3-D Line");
var cellOffset = new PointDoubleUnit(19050, 19050, ScreenMeasurementUnit.Emu);
worksheet.GetRange("A1:A4").SetRowsHeight(66);
worksheet.GetRange("A8:A11").SetRowsHeight(66);
var chart1 = worksheet.Charts.AddLine3DChart(2, "Chart 1", "A1", cellOffset);
chart1.Size.HeightInches = 2.5;
chart1.Size.WidthInches = 3.5;
chart1.DataSource = "Data!$A$1:$D$5";
}
/// <summary>
/// This method show the shortened style of writing cells
/// </summary>
private static void ShortenerDemo()
{
Workbook wb = new Workbook("shortenerDemo.xlsx", "Sheet1"); // Create a workbook (important: A worksheet must be created as well)
wb.WS.Value("Some Text"); // Add cell A1
wb.WS.Value(58.55, Style.BasicStyles.DoubleUnderline); // Add a formatted value to cell B1
wb.WS.Right(2); // Move to cell E1
wb.WS.Value(true); // Add cell E1
wb.AddWorksheet("Sheet2"); // Add a new worksheet
wb.CurrentWorksheet.CurrentCellDirection = Worksheet.CellDirection.RowToRow; // Change the cell direction
wb.WS.Value("This is another text"); // Add cell A1
wb.WS.Formula("=A1"); // Add a formula in Cell A2
wb.WS.Down(); // Go to cell A4
wb.WS.Value("Formatted Text", Style.BasicStyles.Bold); // Add a formatted value to cell A4
wb.Save(); // Save the workbook
}
public static string Build(List <Ttn> items, string title = "Без заголовка")
{
var workbook = new Workbook();
PropertiesBuilder.Apply(workbook);
StylesBuilder.Apply(workbook);
// create first worksheet
var worksheet = new Worksheet("Лист 1");
worksheet.AddCellWithStyle(0, 0, title, StylesBuilder.TitleStyle, 6U);
// adding headers
worksheet.AddCellWithStyle(1, 0, "Номер заказа", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 1, "Дата заказа", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 2, "Артикул товара", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 3, "Наименование товара", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 4, "Количество", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 5, "Цена", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 6, "Сумма", StylesBuilder.Headerstyle);
var row = 2;
foreach (var item in items)
{
worksheet.AddCell(row, 0, item.Id);
worksheet.AddCell(row, 1, item.OrderDate.ToString());
worksheet.AddCell(row, 2, item.ProductId);
worksheet.AddCell(row, 3, item.ProductName);
worksheet.AddCell(row, 4, item.Quantity);
worksheet.AddCell(row, 5, item.UnitPrice);
worksheet.AddCellWithFormula(row, 6, 0, "=RC[-2]*RC[-1]");
row++;
}
// appending footer with formulas
worksheet.AddCellWithStyle(row, 0, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyle(row, 1, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyle(row, 2, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyle(row, 3, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyle(row, 4, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyleAndFormula(row, 5, 0, "=AVERAGE(R[-" + (row - 1) + "]C:R[-1]C)", StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyleAndFormula(row, 6, 0, "=SUM(R[-" + (row - 1) + "]C:R[-1]C)", StylesBuilder.SummaryStyle);
workbook.AddWorksheet(worksheet);
return(workbook.ExportToXML());
}
public static void LookupReference(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("LookupReference");
worksheet["A1"].Value = "COLUMN";
worksheet["B1"].Formula = "=COLUMN()";
worksheet["A2"].Value = "COLUMNS";
worksheet["B2"].Formula = "=COLUMNS(A5:C5)";
worksheet["C2"].Formula = "=COLUMNS({1,2;3,4})";
worksheet["A3"].Value = "INDEX";
worksheet["B3"].Formula = "=INDEX({\"Apples\",\"Lemons\";\"Bananas\",\"Pears\"},2,2)";
worksheet["C3"].Formula = "=INDEX({\"Apples\",\"Lemons\";\"Bananas\",\"Pears\"},2,1)";
worksheet["D3"].Formula = "=INDEX({\"Apples\",\"Lemons\"},,2)";
worksheet["E3"].Formula = "=INDEX({\"Apples\";\"Bananas\"},1)";
}
public ActionResult Export()
{
string result = string.Empty;
Workbook wb = new Workbook();
// properties
wb.Properties.Author = "Calabonga";
wb.Properties.Created = DateTime.Today;
wb.Properties.LastAutor = "Calabonga";
wb.Properties.Version = "14";
// options sheets
wb.ExcelWorkbook.ActiveSheet = 1;
wb.ExcelWorkbook.DisplayInkNotes = false;
wb.ExcelWorkbook.FirstVisibleSheet = 1;
wb.ExcelWorkbook.ProtectStructure = false;
wb.ExcelWorkbook.WindowHeight = 800;
wb.ExcelWorkbook.WindowTopX = 0;
wb.ExcelWorkbook.WindowTopY = 0;
wb.ExcelWorkbook.WindowWidth = 600;
// get data
List <Machinist> people = db.Machinist.ToList();
Worksheet ws3 = new Worksheet("Пользователи");
ws3.AddCell(0, 0, "Фио");
ws3.AddCell(0, 1, "Аддрес");
ws3.AddCell(0, 2, "Телефон");
// appending rows with data
for (int i = 0; i < people.Count; i++)
{
ws3.AddCell(i + 1, 0, people[i].FIO);
ws3.AddCell(i + 1, 1, people[i].Address);
ws3.AddCell(i + 1, 2, people[i].Telephone);
}
wb.AddWorksheet(ws3);
// generate xml
string workstring = wb.ExportToXML();
// Send to user file
return(new ExcelResult("Persons.xls", workstring));
}
public static void NumberFormats(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("NumberFormats");
worksheet.Columns[0].WidthCharacters = 20;
double doubleValue = 1234.5678;
worksheet["A1"].Value = "General";
worksheet["A2"].Value = "'0";
worksheet["A3"].Value = "'0.00";
worksheet["A4"].Value = "#,##0";
worksheet["A5"].Value = "#,##0.00";
worksheet["B1"].Value = doubleValue;
worksheet["B2"].Value = doubleValue;
worksheet["B3"].Value = doubleValue;
worksheet["B4"].Value = doubleValue;
worksheet["B5"].Value = doubleValue;
worksheet["B1"].Format = DRIT.Spreadsheet.Office.Model.Formats.General;
worksheet["B2"].Format = new NumberFormat("0");
worksheet["B3"].Format = new NumberFormat("0.00");
worksheet["B4"].Format = new NumberFormat("#,##0");
worksheet["B5"].Format = new NumberFormat("#,##0.00");
worksheet["A7"].Value = "#,##0.00;[Red]-#,##0.00";
worksheet["B7"].Value = doubleValue * -1;
worksheet["B7"].Format = new NumberFormat("#,##0.00;[Red]-#,##0.00");
worksheet["A9"].Value = "Scientific 0.E+00";
worksheet["B9"].Value = doubleValue;
worksheet["B9"].Format = new NumberFormat("0.E+00");
worksheet["A10"].Value = "Scientific 0.00E+0";
worksheet["B10"].Value = doubleValue;
worksheet["B10"].Format = new NumberFormat("0.00E+0");
worksheet["A12"].Value = "Percent 0%";
worksheet["B12"].Value = doubleValue;
worksheet["B12"].Format = new NumberFormat("0%");
worksheet["A13"].Value = "Scientific 0.00%";
worksheet["B13"].Value = doubleValue;
worksheet["B13"].Format = new NumberFormat("0.00%");
}
/// <summary>
/// Resolves the workbook with all worksheets from the loaded file
/// </summary>
/// <returns>Workbook object</returns>
public Workbook GetWorkbook()
{
Workbook wb = new Workbook(false);
Worksheet ws;
int index = 1;
foreach (KeyValuePair <int, WorksheetReader> reader in worksheets)
{
ws = new Worksheet(workbook.WorksheetDefinitions[reader.Key], index, wb);
foreach (KeyValuePair <string, Cell> cell in reader.Value.Data)
{
ws.AddCell(cell.Value, cell.Key);
}
wb.AddWorksheet(ws);
index++;
}
return(wb);
}
/// <summary>
/// Resolves the workbook with all worksheets from the loaded file
/// </summary>
/// <returns>Workbook object</returns>
public Workbook GetWorkbook()
{
Workbook wb = new Workbook(false);
WorksheetReader wr;
Worksheet ws;
for (int i = 0; i < worksheets.Count; i++)
{
wr = worksheets[i];
ws = new Worksheet(workbook.WorksheetDefinitions[i + 1], i + 1, wb);
foreach (KeyValuePair <string, Cell> cell in wr.Data)
{
ws.AddCell(cell.Value, cell.Key);
}
wb.AddWorksheet(ws);
}
return(wb);
}
public static void Shadow(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("Shadow");
worksheet.Columns["B"].WidthPixels = 90;
worksheet.GetRange("A1:A10").SetRowsHeight(53);
Position cellOffsetPixel = new Position(5, 5, ScreenMeasurementUnit.Pixel);
Size size = new Size(738000, 428625, ScreenMeasurementUnit.Emu);
worksheet["A1"].Value = "Outer Shadow";
var rectangle1 = worksheet.CellShapes.AddShape(ShapeType.Rectangle, "B1", cellOffsetPixel, size);
rectangle1.Effects.Shadow.Type = ShadowType.Outer;
rectangle1.Effects.Shadow.Color = OfficeColor.Black;
rectangle1.Effects.Shadow.Transparency = 0.4m;
rectangle1.Effects.Shadow.Size = 1.2m;
rectangle1.Effects.Shadow.Blur = 4;
rectangle1.Effects.Shadow.Angle = 45;
rectangle1.Effects.Shadow.Distance = 6;
var rectangle2 = worksheet.CellShapes.AddShape(ShapeType.Rectangle, "B2", cellOffsetPixel, size);
rectangle2.Effects.Shadow.Type = ShadowType.Outer;
rectangle2.Effects.Shadow.Color = OfficeColor.Red;
rectangle2.Effects.Shadow.Transparency = 0.2m;
rectangle2.Effects.Shadow.Size = 1;
rectangle2.Effects.Shadow.Blur = 4;
rectangle2.Effects.Shadow.Angle = 45;
rectangle2.Effects.Shadow.Distance = 6;
worksheet["A3"].Value = "Inner Shadow";
var rectangle3 = worksheet.CellShapes.AddShape(ShapeType.Rectangle, "B3", cellOffsetPixel, size);
rectangle3.Fill.SolidOfficeColor = OfficeColor.Window;
rectangle3.Effects.Shadow.Type = ShadowType.Inner;
rectangle3.Effects.Shadow.Color = OfficeColor.Black;
rectangle3.Effects.Shadow.Transparency = 0.4m;
rectangle3.Effects.Shadow.Blur = 5;
rectangle3.Effects.Shadow.Angle = 135;
rectangle3.Effects.Shadow.Distance = 6;
}
public static void Fill(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("Fill");
worksheet.Columns["A"].WidthPixels = 150;
worksheet.Columns["B"].WidthPixels = 90;
worksheet.GetRange("A1:A10").SetRowsHeight(53);
Position cellOffsetPixel = new Position(5, 5, ScreenMeasurementUnit.Pixel);
Size size = new Size(738000, 428625, ScreenMeasurementUnit.Emu);
worksheet["A1"].Value = "Fill Red";
var rectangle1 = worksheet.CellShapes.AddShape(ShapeType.Rectangle, "B1", cellOffsetPixel, size);
rectangle1.Fill.SolidOfficeColor = OfficeColor.Red;
worksheet["A2"].Value = "Fill Red 50% Transparent";
var rectangle2 = worksheet.CellShapes.AddShape(ShapeType.Rectangle, "B2", cellOffsetPixel, size);
rectangle2.Fill.SolidOfficeColor = OfficeColor.Red;
rectangle2.Fill.Transparency = 0.5m;
var rectangle3 = worksheet.CellShapes.AddShape(ShapeType.Rectangle, "B3", cellOffsetPixel, size);
rectangle3.Fill.Gradient = new GradientFill();
rectangle3.Fill.Gradient.GradientStops.Add(new DRIT.Spreadsheet.Office.Drawing.GradientStop(0, OfficeColor.Text1));
rectangle3.Fill.Gradient.GradientStops.Add(new DRIT.Spreadsheet.Office.Drawing.GradientStop(0.5m, OfficeColor.Yellow));
rectangle3.Fill.Gradient.GradientStops.Add(new DRIT.Spreadsheet.Office.Drawing.GradientStop(1, OfficeColor.Red));
var rectangle4 = worksheet.CellShapes.AddShape(ShapeType.Rectangle, "B4", cellOffsetPixel, size);
rectangle4.Fill.Gradient = GradientPresets.Horizon();
var rectangle5 = worksheet.CellShapes.AddShape(ShapeType.Rectangle, "B5", cellOffsetPixel, size);
rectangle5.Fill.Type = FillType.PatternFill;
rectangle5.Fill.Pattern = new PatternFill();
rectangle5.Fill.Pattern.Preset = DRIT.Spreadsheet.Office.Drawing.PatternType.DiagonalBrick;
rectangle5.Fill.Pattern.BackgroundColor = OfficeColor.Background;
rectangle5.Fill.Pattern.ForegroundColor = OfficeColor.Red;
}
public static void Logical(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("Logical");
worksheet["A1"].Value = "AND";
worksheet["B1"].Formula = "=AND(TRUE)";
worksheet["C1"].Formula = "=AND(TRUE,FALSE)";
worksheet["D1"].Formula = "=AND(10>5,3=1+2,5)";
worksheet["A2"].Value = "FALSE";
worksheet["B2"].Formula = "=FALSE()";
worksheet["A3"].Value = "IF";
worksheet["B3"].Formula = "=IF(10>5,\"Yes\",\"No\")";
worksheet["C3"].Formula = "=IF(10>5,\"Yes\")";
worksheet["D3"].Formula = "=IF(10>5,\"Yes\",)";
worksheet["E3"].Formula = "=IF(10<5,\"Yes\")";
worksheet["F3"].Formula = "=IF(10<5,\"Yes\",)";
worksheet["A4"].Value = "IFERROR";
worksheet["B4"].Formula = "=IFERROR(1/0,\"Error in calculation\")";
}
/// <summary>
/// This demo shows the usage of splitting and freezing a worksheet into several panes
/// </summary>
private static void Demo12()
{
Workbook wb = new Workbook("demo12.xlsx", "splitXchars"); // Create a new workbook
wb.CurrentWorksheet.SetVerticalSplit(30f, new Cell.Address("D1"), Worksheet.WorksheetPane.topRight); // Split worksheet vertically by characters
wb.AddWorksheet("SplitXcols"); // Create new worksheet
wb.CurrentWorksheet.SetVerticalSplit(4, false, new Cell.Address("E1"), Worksheet.WorksheetPane.topRight); // Split worksheet vertically by columns
wb.CurrentWorksheet.SetColumnWidth(0, 15f); // Define column width
wb.CurrentWorksheet.SetColumnWidth(1, 20f); // Define column width
wb.CurrentWorksheet.SetColumnWidth(2, 35f); // Define column width
wb.AddWorksheet("SplitYchars"); // Create new worksheet
wb.CurrentWorksheet.SetHorizontalSplit(20f, new Cell.Address("C1"), Worksheet.WorksheetPane.bottomLeft); // Split worksheet horizontally by characters
wb.AddWorksheet("SplitYcols"); // Create new worksheet
wb.CurrentWorksheet.SetHorizontalSplit(5, false, new Cell.Address("A6"), Worksheet.WorksheetPane.bottomLeft); // Split worksheet horizontally by rows
wb.CurrentWorksheet.SetRowHeight(0, 10f); // Define row height
wb.CurrentWorksheet.SetRowHeight(3, 35f); // Define row height
wb.CurrentWorksheet.SetRowHeight(2, 25f); // Define row height
wb.AddWorksheet("SplitXYchars"); // Create new worksheet
wb.CurrentWorksheet.SetSplit(30f, 20f, new Cell.Address("D3"), Worksheet.WorksheetPane.bottomRight); // Split worksheet horizontally and vertically by characters
wb.AddWorksheet("SplitXYColRow"); // Create new worksheet
wb.CurrentWorksheet.SetSplit(3, 10, false, new Cell.Address("D11"), Worksheet.WorksheetPane.bottomRight); // Split worksheet horizontally and vertically by rows and columns
wb.AddWorksheet("FreezeXcols"); // Create new worksheet
wb.CurrentWorksheet.SetVerticalSplit(4, true, new Cell.Address("E1"), Worksheet.WorksheetPane.topRight); // Split and freeze worksheet vertically by columns
wb.AddWorksheet("FreezeYcols"); // Create new worksheet
wb.CurrentWorksheet.SetHorizontalSplit(1, true, new Cell.Address("A2"), Worksheet.WorksheetPane.bottomLeft); // Split and freeze worksheet horizontally by rows
wb.AddWorksheet("FreezeXYColRow"); // Create new worksheet
wb.CurrentWorksheet.SetSplit(3, 10, true, new Cell.Address("D11"), Worksheet.WorksheetPane.bottomRight); // Split and freeze worksheet horizontally and vertically by rows and columns
wb.Save(); // Save the workbook
}
public static void Text(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("Text");
worksheet["A1"].Value = "CHAR";
worksheet["D1"].Value = 65;
worksheet["B1"].Formula = "=CHAR(65)";
worksheet["C1"].Formula = "=CHAR(D1)";
worksheet["A2"].Value = "CLEAN";
worksheet["B2"].Formula = "=CLEAN(\"A\" & CHAR(2) & \"BC\")";
worksheet["A3"].Value = "CODE";
worksheet["B3"].Formula = "=CODE(\"abc\")";
worksheet["A4"].Value = "CONCATENATE";
worksheet["B4"].Formula = "=CONCATENATE(3,\" + \",4,\" = \",3+4)";
worksheet["A5"].Value = "DOLLAR";
worksheet["B5"].Formula = "=DOLLAR(1234.567)";
worksheet["C5"].Formula = "=DOLLAR(1234.567,-2)";
worksheet["D5"].Formula = "=DOLLAR(-1234.567,4)";
worksheet["A6"].Value = "EXACT";
worksheet["B6"].Formula = "=EXACT(\"ABC\",\"ABC\")";
worksheet["C6"].Formula = "=EXACT(\"ABC\",\"ABCD\")";
worksheet["D6"].Formula = "=EXACT(\"Abc\",\"aBC\")";
worksheet["E6"].Formula = "=EXACT(\"\",\"\")";
worksheet["A7"].Value = "FIND";
worksheet["B7"].Formula = "=FIND(\"de\",\"abcdef\")";
worksheet["A8"].Value = "FIXED";
worksheet["B8"].Formula = "=FIXED(1234567)";
worksheet["C8"].Formula = "=FIXED(1234567.555555,4,TRUE)";
worksheet["D8"].Formula = "=FIXED(.555555,10)";
worksheet["E8"].Formula = "=FIXED(1234567,-3)";
}
public static void TwoDArea(Workbook workbook)
{
var worksheet = workbook.AddWorksheet("2-D Area");
var cellOffset = new PointDoubleUnit(19050, 19050, ScreenMeasurementUnit.Emu);
worksheet.GetRange("A1:A4").SetRowsHeight(66);
worksheet.GetRange("A8:A11").SetRowsHeight(66);
var chart1 = worksheet.Charts.AddAreaChart(2, "Chart 1", "A1", cellOffset);
chart1.Size.HeightInches = 2.5;
chart1.Size.WidthInches = 3.5;
chart1.DataSource = "Data!$A$1:$D$5";
var chart2 = worksheet.Charts.AddAreaChart(3, "Chart 2", "A7", cellOffset);
chart2.Size.HeightInches = 2.5;
chart2.Size.WidthInches = 3.5;
chart2.Grouping = Grouping.Stacked;
chart2.DataSource = "Data!$A$1:$D$5";
}
private static void TestAddList()
{
MsExcel.GetInstance().Open(@"E:\Temp\ToDelete\FirstWorbook.xlsx");
Workbook w = MsExcel.GetInstance().GetOpenWorkbookByName(@"FirstWorbook.xlsx");
w.AddWorksheet("TestWs");
List <List <string> > lls = new List <List <string> >();
for (int i = 0; i < 10; i++)
{
var ls = new List <string>();
for (int j = 0; j < 20; j++)
{
ls.Add((i + j).ToString());
}
lls.Add(ls);
}
Worksheet ws = w.GetWorksheetByName("TestWs");
ws.SetTable(lls, 5, 3);
w.Save();
w.Close();
}
