Worksheet Methods

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Sample text in cell";</para><para>//Set auto fit</para><para>worksheet.AutoFitColumn(1);</para><para>//Save to file</para><para>workbook.SaveToFile("AutoFitColumn.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Value = "Sample text";</para><para>//Set Style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>IFont font = style.Font;</para><para>font.Size = 18;</para><para>worksheet["C2"].Style = style;</para><para>//Set auto fit</para><para>worksheet.AutoFitRow(2);</para><para>//Save to file</para><para>workbook.SaveToFile("AutoFitRow.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet.Range["A1"].Text = "Hello";</para><para>//Check the cell.Output will be true.</para><para>Console.Write(worksheet.CheckExistence(1, 1));</para><para>//Save to file</para><para>workbook.SaveToFile("CheckExistence.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["A1"].Text = "Sample text in cell";</para><para>//Set auto fit</para><para>worksheet.AutoFitColumn(1);</para><para>//Get column width</para><para>Console.WriteLine(worksheet.GetColumnWidthPixels(1));</para><para>//Save to file</para><para>workbook.SaveToFile("UsedRange.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>//Set Color</para><para>style.Color = Color.Red;</para><para>//Set default style</para><para>worksheet.SetDefaultColumnStyle(2,style);</para><para>//Get default style</para><para>CellStyle defaultStyle = worksheet.GetDefaultColumnStyle(2);</para><para>//Set color</para><para>defaultStyle.Color = Color.Blue;</para><para>worksheet.SetDefaultColumnStyle(3, defaultStyle);</para><para>//Save to file</para><para>workbook.SaveToFile("GetDefaultColumnStyle.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>//Set Color</para><para>style.Color = Color.Red;</para><para>//Set default style</para><para>worksheet.SetDefaultRowStyle(2,style);</para><para>//Get default style</para><para>CellStyle defaultStyle = worksheet.GetDefaultRowStyle(2);</para><para>//Set color</para><para>defaultStyle.Color = Color.Blue;</para><para>worksheet.SetDefaultRowStyle(3, defaultStyle);</para><para>//Save to file</para><para>workbook.SaveToFile("GetDefaultColumnStyle.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["C2"].Text = "Sample text";</para><para>worksheet["C2"].Style.Font.Size = 18;</para><para>//Set auto fit</para><para>worksheet.AutoFitRow(2);</para><para>//Get row height</para><para>Console.WriteLine(worksheet.GetRowHeightPixels(2));</para><para>//Save to file</para><para>workbook.SaveToFile("UsedRange.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Initialize the Object Array</para><para>object[,] arrayTwoDimen = new object[3, 2] { { "AND", "OR" }, { "NAND", "XOR" },{ "NOR", "NOT" } };</para><para>//Insert the Object Array to Sheet</para><para>worksheet.InsertArray(arrayTwoDimen, 1, 1);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertArray.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Initialize the DateTime Array</para><para>DateTime[] arrayDate = new DateTime[4] { DateTime.Parse("06:45"), DateTime.Parse("08:30"), DateTime.Parse("09:40"), DateTime.Parse("10:30") };</para><para>//Insert the DateTime Array to Sheet</para><para>worksheet.InsertArray(arrayDate, 1, 1, true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertArray.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Initialize the double Array</para><para>double[] arrayDouble = new double[4] { 344.0045, 345.0045, 346.0045, 347.0045 };</para><para>//Insert the double Array to Sheet</para><para>worksheet.InsertArray(arrayDouble, 1, 1, true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertArray.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Initialize the integer Array</para><para>int[] arrayInt = new int[4] {1000, 2000, 3000, 4000};</para><para>//Insert the integer Array to Sheet</para><para>worksheet.InsertArray(arrayInt, 1, 1, true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertArray.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Initialize the Object Array</para><para>object[] array = new object[4] { "Total Income", "Actual Expense", "Expected Expenses", "Profit" };</para><para>//Insert the Object Array to Sheet</para><para>worksheet.InsertArray(array, 1, 1, true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertArray.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Initialize the string Array</para><para>string[] arrayString = new string[4] { "Total Income", "Actual Expense", "Expected Expenses", "Profit" };</para><para>//Insert the string Array to Sheet</para><para>worksheet.InsertArray(arrayString, 1, 1, true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertArray.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Insert the DataColumn to worksheet</para><para>System.Data.DataColumn column = table.Columns[2];</para><para>worksheet.InsertDataColumn(column, true, 1, 1);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataColumn.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Insert the DataTable to worksheet</para><para>worksheet.InsertDataTable(table, true, 1, 1);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataTable.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Insert the DataTable to worksheet</para><para>worksheet.InsertDataTable(table, true, 1, 1 , true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataTable.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Insert the DataTable to worksheet</para><para>worksheet.InsertDataTable(table, true, 1 , 1 , 2 , 2);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataTable.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Insert the DataTable to worksheet</para><para>worksheet.InsertDataTable(table, true, 1 , 1 , 2 , 2 , true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataTable.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Initialize dataview of datatable</para><para>System.Data.DataView view = table.DefaultView;</para><para>//Import data from DataView</para><para>worksheet.InsertDataView(view, true, 1, 1);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataView.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Initialize dataview of datatable</para><para>System.Data.DataView view = table.DefaultView;</para><para>//Import data from DataView</para><para>worksheet.InsertDataView(view, true, 1, 1 , true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataView.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Initialize dataview of datatable</para><para>System.Data.DataView view = table.DefaultView;</para><para>//Import data from DataView</para><para>worksheet.InsertDataView(view, true, 1, 1 , 2 , 2);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataView.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create a DataTable</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("ID", typeof(int));
 table.Columns.Add("Item", typeof(string));
 table.Columns.Add("Name", typeof(string));</para><para>table.Rows.Add(1, "Soap", "David");
 table.Rows.Add(2, "Paste", "Sam");
 table.Rows.Add(3, "Cream", "Christoff");</para><para>//Initialize dataview of datatable</para><para>System.Data.DataView view = table.DefaultView;</para><para>//Import data from DataView</para><para>worksheet.InsertDataView(view, true, 1, 1 , 2 , 2 , true);</para><para>//Save to file</para><para>workbook.SaveToFile(InsertDataView.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Protects the first worksheet's content with password</para><para>worksheet.Protect("123456");</para><para>//Save to file</para><para>workbook.SaveToFile("Protect.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Replace the oldValue by dateTime</para><para>string oldValue = "Find";</para><para>DateTime dateTime = DateTime.Now;</para><para>worksheet.Replace(oldValue, dateTime);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Replace the oldValue by double</para><para>string oldValue = "Ten";</para><para>worksheet.Replace(oldValue, 10.0);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Replace the oldValue by newValue</para><para>string oldValue = "Find";</para><para>string newValue = "NewValue";</para><para>worksheet.Replace(oldValue, newValue);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Replace the oldValue by data column</para><para>string oldValue = "Find";</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("Dosage", typeof(int));</para><para>table.Rows.Add(1);</para><para>System.Data.DataColumn dataColumn = table.Columns[0];</para><para>worksheet.Replace(oldValue, dataColumn, true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Replace the oldValue by data table</para><para>string oldValue = "Find";</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("Dosage", typeof(int));</para><para>table.Rows.Add(1);</para><para>worksheet.Replace(oldValue, table, true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Replace the oldValue by array of double values</para><para>string oldValue = "Find";</para><para>double[] newValues = { 1.0, 2.0 };</para><para>worksheet.Replace(oldValue, newValues, true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Replace the oldValue by array of int values</para><para>string oldValue = "Find";</para><para>int[] newValues = { 1, 2 };</para><para>worksheet.Replace(oldValue, newValues, true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Replace the oldValue by array of string values</para><para>string oldValue = "Find";</para><para>string[] newValues = { "X values", "Y values" };</para><para>worksheet.Replace(oldValue, newValues , true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Save to file</para><para>worksheet.SaveToFile("SaveToFile.csv" , ",");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Creat stream</para><para>Stream stream = new MemoryStream();</para><para>//Save to HTML stream</para><para>worksheet.SaveToHtml(stream);</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Save to HTML file</para><para>worksheet.SaveToHtml("Output.html");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Creat stream</para><para>Stream stream = new MemoryStream();</para><para>//Save to HTML stream</para><para>worksheet.SaveToHtml(stream, Spire.Xls.Core.Spreadsheet.HTMLOptions.Default);</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Save to HTML file</para><para>worksheet.SaveToHtml("Sample.html" , Spire.Xls.Core.Spreadsheet.HTMLOptions.Default);</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Creat stream</para><para>Stream stream = new MemoryStream();</para><para>//Save to image</para><para>System.Drawing.Image image = worksheet.SaveToImage(stream,1, 1, 10, 20, Spire.Xls.ImageType.Bitmap);</para><para>image.Save("Sample.png", System.Drawing.Imaging.ImageFormat.Png);</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create stream</para><para>Stream stream = new MemoryStream();</para><para>//Save to stream</para><para>worksheet.SaveToStream(stream , ",");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set column width</para><para>worksheet.SetColumnWidthInPixels(2, 160);</para><para>//Save to file</para><para>workbook.SaveToFile("SetColumnWidthInPixels.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>//Set Color</para><para>style.Color = Color.Red;</para><para>//Set default style</para><para>worksheet.SetDefaultColumnStyle(2, style);</para><para>//Save to file</para><para>workbook.SaveToFile("SetDefaultColumnStyle.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>//Set Color</para><para>style.Color = Color.Red;</para><para>//Set default style</para><para>worksheet.SetDefaultColumnStyle(2, 5, style);</para><para>//Save to file</para><para>workbook.SaveToFile("SetDefaultColumnStyle.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>//Set Color</para><para>style.Color = Color.Red;</para><para>//Set default style</para><para>worksheet.SetDefaultRowStyle(2, style);</para><para>//Save to file</para><para>workbook.SaveToFile("SetDefaultRowStyle.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Create style</para><para>CellStyle style = workbook.Styles.Add("CustomStyle");</para><para>//Set Color</para><para>style.Color = Color.Red;</para><para>//Set default style</para><para>worksheet.SetDefaultRowStyle(2, 5, style);</para><para>//Save to file</para><para>workbook.SaveToFile("SetDefaultRowStyle.xlsx");</para>

<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set row height</para><para>worksheet.SetRowHeightPixels(3, 150);</para><para>//Save to file</para><para>workbook.SaveToFile("SetRowHeightPixels.xlsx");</para>