Monday, 28 August 2017 06:41
Insert hyperlink in Excel
using NPOI.HSSF.Util; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOI { class Program { static void Main(string[] args) { //Load workbook IWorkbook workbook = new XSSFWorkbook(new FileStream("../../../Data/Sample.xlsx", FileMode.Open)); //Get the first sheet ISheet sheet = workbook.GetSheetAt(0); //Set the style of the cell ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); font.Underline = FontUnderlineType.Single; font.Color = HSSFColor.Red.Index; font.FontHeight = 15; style.SetFont(font); //Add an URL link ICell cell = sheet.CreateRow(1).CreateCell(1); cell.SetCellValue("Url link"); XSSFHyperlink UrlLink = new XSSFHyperlink(HyperlinkType.Url) { Address = "https://www.e-iceblue.com/" }; cell.Hyperlink = (UrlLink); cell.CellStyle = (style); //Add an e-mail link cell = sheet.CreateRow(3).CreateCell(1); cell.SetCellValue("Email link"); XSSFHyperlink MailLink = new XSSFHyperlink(HyperlinkType.Email) { Address = "mailto:support@e-iceblue.com" }; cell.Hyperlink = (MailLink); cell.CellStyle = (style); //Add an external file link cell = sheet.CreateRow(5).CreateCell(1); cell.SetCellValue("External file link"); XSSFHyperlink FileLink = new XSSFHyperlink(HyperlinkType.File) { Address = "ExternalFile.xlsx" }; cell.Hyperlink = (FileLink); cell.CellStyle = (style); //Save the file FileStream file = File.Create("ExcelHyperlink.xlsx"); workbook.Write(file); file.Close(); //Launch System.Diagnostics.Process.Start("ExcelHyperlink.xlsx"); } } }
Published in
NPOI
Monday, 28 August 2017 06:37
Create dropdown list in Excel
using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOI { class Program { static void Main(string[] args) { //Create workbook IWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("sheet"); //Create dropdown list IDataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidationConstraint constraint = validationHelper.CreateExplicitListConstraint(new String[] { "One", "Two", "Three", "Four" }); IDataValidation dataValidation = validationHelper.CreateValidation(constraint, addressList); dataValidation.SuppressDropDownArrow = true; sheet.AddValidationData(dataValidation); //Save the file FileStream file = File.Create("ExcelDropdownList.xlsx"); workbook.Write(file); file.Close(); //Launch the file System.Diagnostics.Process.Start("ExcelDropdownList.xlsx"); } } }
Published in
NPOI
Monday, 28 August 2017 06:33
Copy worksheet in Excel
using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOI { class Program { static void Main(string[] args) { //Load workbook IWorkbook workbook = new XSSFWorkbook(new FileStream("../../../Data/Sample.xlsx", FileMode.Open)); //Get the first worksheet ISheet sheet = workbook.GetSheetAt(0); //Copy to a new sheet sheet.CopySheet("copied sheet", true); //Save the file FileStream file = File.Create("Copied.xlsx"); workbook.Write(file); file.Close(); //Launch System.Diagnostics.Process.Start("Copied.xlsx"); } } }
Published in
NPOI
Monday, 28 August 2017 06:30
Add header and footer in Excel
using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOI { class Program { static void Main(string[] args) { //Load workbook IWorkbook workbook = new XSSFWorkbook(new FileStream("../../../Data/Sample.xlsx", FileMode.Open)); //Get the first worksheet ISheet sheet = workbook.GetSheetAt(0); //Set header IHeader header = sheet.Header; header.Right = "Header"; //Set footer IFooter footer = sheet.Footer; footer.Center = "Footer"; //Save the file FileStream file = File.Create("HeaderFooter.xlsx"); workbook.Write(file); file.Close(); //Launch the file System.Diagnostics.Process.Start("HeaderFooter.xlsx"); } } }
Published in
NPOI
Monday, 28 August 2017 06:28
Add formula in Excel
using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOI { class Program { static void Main(string[] args) { //Create workbook IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("MySheet"); //Create cells IRow row = sheet.CreateRow(0); ICell cell1 = row.CreateCell(0); ICell cell2 = row.CreateCell(1); ICell cell3 = row.CreateCell(2); ICell sumCell = row.CreateCell(3); //Set the value of the cells cell1.SetCellValue(10); cell2.SetCellValue(15); cell3.SetCellValue(20); //Add formula sumCell.SetCellFormula("sum(A1:C1)"); //Save the file FileStream file = File.Create("ExcelFormula.xlsx"); workbook.Write(file); file.Close(); //Launch the file System.Diagnostics.Process.Start("ExcelFormula.xlsx"); } } }
Published in
NPOI
Monday, 28 August 2017 06:24
Add comment in Excel
using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace NPOI { class Program { static void Main(string[] args) { //Create workbook IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("MySheet"); //Create the drawing patriarch IDrawing drawing = sheet.CreateDrawingPatriarch(); //Create cell and set its value ICell cell = sheet.CreateRow(2).CreateCell(2); cell.SetCellValue("Comment"); //Create comment IClientAnchor anchor = workbook.GetCreationHelper().CreateClientAnchor(); IComment comment = drawing.CreateCellComment(anchor); comment.String = new XSSFRichTextString("Spire.XLS"); comment.Author = ("E-iceblue"); cell.CellComment = (comment); //Save the file FileStream file = File.Create("ExcelComment.xlsx"); workbook.Write(file); file.Close(); //Launch the file System.Diagnostics.Process.Start("ExcelComment.xlsx"); } } }
Published in
NPOI
Thursday, 24 August 2017 01:51
Sort excel data
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; using Microsoft.Office.Tools.Excel; namespace VSTO { public partial class ThisAddIn { private void ThisAddIn_Startup(object sender, System.EventArgs e) { //Open the workbook file Excel.Application ExcelApp = Application; Excel.Workbook workbook = ExcelApp.Application.Workbooks.Open("C:\\Sample.xlsx"); //Get the first sheet Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets["Sheet1"]; //Sort the specified range Excel.Range range = sheet.get_Range("A1:A7"); range.Sort(range.Columns[1], Excel.XlSortOrder.xlAscending, missing, missing, Excel.XlSortOrder.xlAscending, missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlNo, missing, missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal); //Save workbook.SaveAs("SortedExcel.xlsx"); } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { } #region VSTO generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion } }
Published in
VSTO
Thursday, 24 August 2017 01:49
Set page break in Excel
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; using Microsoft.Office.Tools.Excel; namespace VSTO { public partial class ThisAddIn { private void ThisAddIn_Startup(object sender, System.EventArgs e) { //Open the workbook file Excel.Application excelApp = Application; Excel.Workbook workbook = excelApp.Application.Workbooks.Open("C:\\Sample.xlsx"); //Get the active worksheet Excel.Worksheet sheet = workbook.ActiveSheet; //Set page break horizontally Excel.Range range1 = sheet.get_Range("A7"); sheet.HPageBreaks.Add(range1); //Set page break vertically Excel.Range range2 = sheet.get_Range("B7"); sheet.VPageBreaks.Add(range2); //Save the file workbook.SaveAs("PageBreakExcel.xlsx"); } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { } #region VSTO generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion } }
Published in
VSTO
Thursday, 24 August 2017 01:46
Set cell style in Excel
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; using Microsoft.Office.Tools.Excel; using System.Drawing; using System.Reflection; namespace VSTO { public partial class ThisAddIn { private void ThisAddIn_Startup(object sender, System.EventArgs e) { //Open the workbook file Excel.Workbook workbook = this.Application.Workbooks.Add(); //Get the active sheet Excel.Worksheet sheet = workbook.ActiveSheet; //Add style of specified range Excel.Style style = workbook.Styles.Add("Style"); style.Font.Size = 16; style.Font.Color = Color.Red; style.Interior.Color = Color.Black; style.Interior.Pattern = Excel.XlPattern.xlPatternSolid; Excel.Range range = sheet.get_Range("A1","A7"); range.Value = "Spire"; range.Style = "Style"; //Save the file workbook.SaveAs("StyleExcel.xlsx"); } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { } #region VSTO generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion } }
Published in
VSTO
Thursday, 24 August 2017 01:40
Protect and Unprotect worksheets
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Linq; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; using Microsoft.Office.Tools.Excel; using System.Reflection; namespace VSTO { public partial class ThisAddIn { private void ThisAddIn_Startup(object sender, System.EventArgs e) { //Add a new workbook Excel.Workbook excelApp = this.Application.Workbooks.Add(); //Protect the worksheet with password ((Excel.Worksheet)excelApp.ActiveSheet).Protect("Spire"); //Unprotect the worksheet specifying its password ((Excel.Worksheet)excelApp.ActiveSheet).Unprotect("Spire"); //Save the file excelApp.SaveAs("Result.xlsx"); } private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { } #region VSTO generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InternalStartup() { this.Startup += new System.EventHandler(ThisAddIn_Startup); this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown); } #endregion } }
Published in
VSTO