Example T1275274
Visible to All Users

Spreadsheet for WinForms - How to Import Data to a Worksheet from Different Data Sources

This example illustrates how to use the Worksheet.Import method to import data to worksheet cells from different data sources (arrays, lists and data tables), and how to format a range of cells as a table.

[!NOTE]
Worksheet extensions are defined by the WorksheetExtensions class. To enable them, add a reference to the DevExpress.Docs.vX.Y.dll library. Note that distribution of this assembly requires a license to the Office File API or the DevExpress Universal Subscription.

Files to Review

Documentation

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)

Example Code

DataImportExample/Form1.cs(vb)
C#
using DevExpress.Spreadsheet; using System; using System.Collections.Generic; using System.Data; using System.IO; namespace DataImportExample { public partial class Form1 : DevExpress.XtraEditors.XtraForm { #region #ImageBytes byte[] imageBytes1 = File.ReadAllBytes("images//img.png"); byte[] imageBytes2 = File.ReadAllBytes("images//x-docserver.png"); #endregion #ImageBytes // ... public Form1() { InitializeComponent(); } private void btnImportDataTable_Click(object sender, EventArgs e) { spreadsheetControl1.Document.BeginUpdate(); try { Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; worksheet.Clear(worksheet.GetUsedRange()); ImportDataTable(); CreateTable(); } finally { spreadsheetControl1.Document.EndUpdate(); } } private void btnImportArray_Click(object sender, EventArgs e) { spreadsheetControl1.Document.BeginUpdate(); try { Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; worksheet.Clear(worksheet.GetUsedRange()); worksheet.Cells["A1"].ColumnWidthInCharacters = 35; worksheet.Cells["A1"].Value = "Import an array horizontally:"; worksheet.Cells["A3"].Value = "Import a two-dimensional array:"; ImportArrays(); } finally { spreadsheetControl1.Document.EndUpdate(); } } private void btnImportList_Click(object sender, EventArgs e) { spreadsheetControl1.Document.BeginUpdate(); try { Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; worksheet.Clear(worksheet.GetUsedRange()); worksheet.Cells["A1"].ColumnWidthInCharacters = 35; worksheet.Cells["A1"].Value = "Import data from List vertically:"; ImportList(); } finally { spreadsheetControl1.Document.EndUpdate(); } } private void btnArrayList_Click(object sender, EventArgs e) { spreadsheetControl1.Document.BeginUpdate(); try { Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; worksheet.Clear(worksheet.GetUsedRange()); ImportArrayList(); } finally { spreadsheetControl1.Document.EndUpdate(); } } #region #ImportDataTable void ImportDataTable() { Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; // Create a "Products" DataTable object with four columns. DataTable sourceTable = new DataTable("Products"); sourceTable.Columns.Add("Product", typeof(string)); sourceTable.Columns.Add("Price", typeof(float)); sourceTable.Columns.Add("Quantity", typeof(Int32)); sourceTable.Columns.Add("Discount", typeof(float)); sourceTable.Columns.Add("Image", typeof(byte[])); sourceTable.Rows.Add("Chocolade", 5, 15, 0.03, imageBytes1); sourceTable.Rows.Add("Konbu", 9, 55, 0.1, imageBytes1); sourceTable.Rows.Add("Geitost", 15, 70, 0.07, imageBytes2); // Import data from the data table into the worksheet and insert it, starting with the B2 cell. worksheet.Import(sourceTable, true, 1, 1); } #endregion #ImportDataTable #region #CreateTable void CreateTable() { Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; // Insert a table in the worksheet. Table table = worksheet.Tables.Add(worksheet["B2:G5"], true); // Format the table by applying a built-in table style. table.Style = spreadsheetControl1.Document.TableStyles[BuiltInTableStyleId.TableStyleMedium27]; // Access table columns. TableColumn productColumn = table.Columns[0]; TableColumn priceColumn = table.Columns[1]; TableColumn quantityColumn = table.Columns[2]; TableColumn discountColumn = table.Columns[3]; TableColumn imageColumn = table.Columns[4]; TableColumn amountColumn = table.Columns[5]; // Set the name of the last column. amountColumn.Name = "Amount"; // Set the formula to calculate the amount per product // and display results in the "Amount" column. amountColumn.Formula = "=[Price]*[Quantity]*(1-[Discount])"; // Display the total row in the table. table.ShowTotals = true; // Set the label and function to display the sum of the "Amount" column. discountColumn.TotalRowLabel = "Total:"; amountColumn.TotalRowFunction = TotalRowFunction.Sum; // Specify the number format for each column. priceColumn.DataRange.NumberFormat = "$#,##0.00"; discountColumn.DataRange.NumberFormat = "0.0%"; amountColumn.Range.NumberFormat = "$#,##0.00;$#,##0.00;\"\";@"; // Specify horizontal alignment for header and total rows of the table. table.HeaderRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center; table.TotalRowRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center; // Specify horizontal alignment to display data in all columns except the first one. for (int i = 1; i < table.Columns.Count; i++) { table.Columns[i].DataRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center; } // Set the width of table columns. table.Range.ColumnWidthInCharacters = 10; } #endregion #CreateTable void ImportArrays() { #region #ImportArrays Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; // Create an array containing string values. string[] array = new string[] { "AAA", "BBB", "CCC", "DDD" }; // Import the array into the worksheet and insert it horizontally, starting with the B1 cell. worksheet.Import(array, 0, 1, false); // Create a two-dimensional array containing string values. String[,] names = new String[2, 4]{ {"Ann", "Edward", "Angela", "Alex"}, {"Rachel", "Bruce", "Barbara", "George"} }; // Import the two-dimensional array into the worksheet and insert it, starting with the B3 cell. worksheet.Import(names, 2, 1); #endregion #ImportArrays } void ImportList() { #region #ImportList Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; // Create a List object containing string values. List<string> cities = new List<string> { "New York", "Rome", "Beijing", "Delhi" }; // Import the list into the worksheet and insert it vertically, starting with the B1 cell. worksheet.Import(cities, 0, 1, true); List<byte[]> imageList = new List<byte[]>(); { imageList.Add(imageBytes1); imageList.Add(imageBytes2); }; // Import the image list into the worksheet and insert it vertically worksheet.Import(imageList, 0, 2, true, new DataImportOptions()); #endregion #ImportList } void ImportArrayList() { #region #ImportArrayList Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; System.Collections.ArrayList listDataSource = new System.Collections.ArrayList() { new TestObject(1, "Jane", true, imageBytes1), new TestObject(2, "Joe", false, imageBytes2), new TestObject(3, "Bill", true, imageBytes1), new TestObject(4, "Michael", false, imageBytes2), }; worksheet.Import(listDataSource, 0, 0); #endregion #ImportArrayList } private void btnImportObject_Click(object sender, EventArgs e) { #region #ImportObject Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; worksheet.Clear(worksheet.GetUsedRange()); worksheet.Import(new TestObject(1, "1", true, imageBytes1), 0, 0); #endregion #ImportObject } private void btnUseOptions_Click(object sender, EventArgs e) { #region #ImportUsingOptions Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; worksheet.Clear(worksheet.GetUsedRange()); string[] arrayR1C1 = new string[] { "a", "b", "=R1C1&R1C2" }; worksheet.Import(arrayR1C1, 0, 0, false, new DataImportOptions() { ImportFormulas = true, ReferenceStyle = ReferenceStyle.R1C1 }); string[] arrayLocalized = new string[] { "a", "=1,2+1" }; worksheet.Import(arrayLocalized, 1, 0, false, new DataImportOptions() { ImportFormulas = true, FormulaCulture = new System.Globalization.CultureInfo("de-DE") }); #endregion #ImportUsingOptions } private void btnUseFields_Click(object sender, EventArgs e) { #region #ImportSpecifiedFields Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; worksheet.Clear(worksheet.GetUsedRange()); List<TestObject> list = new List<TestObject> { new TestObject(1, "1", true, imageBytes1), new TestObject(2, "2", false, imageBytes2) }; worksheet.Import(list, 0, 0, new DataSourceImportOptions() { PropertyNames = new string[] { "BoolValue", "ImageValue" } }); #endregion #ImportSpecifiedFields } private void btnUseConverter_Click(object sender, EventArgs e) { #region #ImportUsingConverter Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; worksheet.Clear(worksheet.GetUsedRange()); string imageBase64 = Convert.ToBase64String(imageBytes1); List<TestObject> list = new List<TestObject> { new TestObject(1, "1", true, imageBase64), new TestObject(2, "2", false, imageBase64) }; worksheet.Import(list, 0, 0, new DataSourceImportOptions() { Converter = new TestDataValueConverter(), PropertyNames = new string[] { "IntValue", "Value", "BoolValue", "ImageBase64" } }); worksheet.Import(list, 0, 0, new DataSourceImportOptions() { Converter = new TestDataValueConverter() }); #endregion #ImportUsingConverter } } }
DataImportExample/TestDataValueConverter.cs(vb)
C#
using DevExpress.Drawing; using System; namespace DataImportExample { #region #converter class TestDataValueConverter : DevExpress.Spreadsheet.IDataValueConverter { public bool TryConvert(object value, int columnIndex, out DevExpress.Spreadsheet.CellValue result) { switch (value) { case string strValue: try { result = DXImage.FromBase64String(strValue); } catch { int str2int = 0; if (Int32.TryParse(strValue, out str2int)) { result = str2int; } else { result = strValue; } } return true; case int intValue: result = intValue; return true; case bool boolValue: result = boolValue; return true; default: result = value == null ? null : value.ToString(); return true; } } } #endregion #converter }
DataImportExample/TestObject.cs(vb)
C#
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace DataImportExample { #region #testobject class TestObject { public TestObject(int intValue, string value, bool boolValue, byte[] imageValue) { this.intValue = intValue; this.Value = value; this.BoolValue = boolValue; this.ImageValue = imageValue; } public TestObject(int intValue, string value, bool boolValue, string imageBase64) { this.intValue = intValue; this.Value = value; this.BoolValue = boolValue; this.ImageBase64 = imageBase64; } public int intValue; private int privateValue { get { return 123; } } public int IntValue { get { return intValue + privateValue - 123; } } public string Value { get; set; } public bool BoolValue { get; set; } public byte[] ImageValue { get; set; } public int this[int index] { get { return index; } } public string ImageBase64 { get; set; } } #endregion #testObject }

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.