工作表中导入数据
如何将数据导入工作表
使用 Aspose.Cells 打开 Excel 文件时,文件中的所有数据都会自动导入。Aspose.Cells 还可以从其他数据源导入数据。
Aspose.Cells 提供了一个 Workbook 表示 Microsoft Excel 文件。Workbook 类包含一个 Worksheets 集合,该集合允许访问 Excel 文件中的每个工作表。工作表由 Worksheet 类表示。Worksheet 类提SUPPLIEDBYCells 集合。Cells 集合提供了从不同数据源导入数据的有用方法。本文介绍了如何使用这些方法。
通过实现 ICellsDataTable 来包装各种数据源,然后使用 Cells.ImportData() 将数据导入到 Excel 工作表。
通过实现 ICellsDataTable 来包装各种数据源,然后使用 Cells.ImportData() 将数据导入到 Excel 工作表。
示例代码
//Init data source | |
CustomerList customers = new CustomerList(); | |
customers.Add(new Customer("Thomas Hardy", "120 Hanover Sq., London")); | |
customers.Add(new Customer("Paolo Accorti", "Via Monte Bianco 34, Torino")); | |
//Create a new file. | |
var wb = new Workbook(); | |
ImportTableOptions options = new ImportTableOptions(); | |
options.IsFieldNameShown = true; | |
//Import ICellsDataTable with options | |
wb.Worksheets[0].Cells.ImportData(new CustomerDataSource(customers), 0, 0, options); | |
wb.Save("ICellsDataTable.xlsx"); |
给出了CustomerDataSource、Customer和CustomerList类的实现
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
public class CustomerDataSource : ICellsDataTable | |
{ | |
public CustomerDataSource(CustomerList customers) | |
{ | |
this.m_DataSource = customers; | |
this.m_Properties = customers[0].GetType().GetProperties(); | |
this.m_Columns = new string[this.m_Properties.Length]; | |
this.m_PropHash = new Hashtable(this.m_Properties.Length); | |
for (int i = 0; i < m_Properties.Length; i++) | |
{ | |
this.m_Columns[i] = m_Properties[i].Name; | |
this.m_PropHash.Add(m_Properties[i].Name, m_Properties[i]); | |
} | |
this.m_IEnumerator = this.m_DataSource.GetEnumerator(); | |
} | |
internal string[] m_Columns; | |
internal ICollection m_DataSource; | |
private Hashtable m_PropHash; | |
private IEnumerator m_IEnumerator; | |
private System.Reflection.PropertyInfo[] m_Properties; | |
public string[] Columns | |
{ | |
get | |
{ | |
return this.m_Columns; | |
} | |
} | |
public int Count | |
{ | |
get | |
{ | |
return this.m_DataSource.Count; | |
} | |
} | |
public void BeforeFirst() | |
{ | |
this.m_IEnumerator = this.m_DataSource.GetEnumerator(); | |
} | |
public object this[int index] | |
{ | |
get | |
{ | |
return this.m_Properties[index].GetValue(this.m_IEnumerator.Current, null); | |
} | |
} | |
public object this[string columnName] | |
{ | |
get | |
{ | |
return ((System.Reflection.PropertyInfo)this.m_PropHash[columnName]).GetValue(this.m_IEnumerator.Current, null); | |
} | |
} | |
public bool Next() | |
{ | |
if (this.m_IEnumerator == null) | |
return false; | |
return this.m_IEnumerator.MoveNext(); | |
} | |
} | |
public class Customer | |
{ | |
public Customer(string aFullName, string anAddress) | |
{ | |
FullName = aFullName; | |
Address = anAddress; | |
} | |
public string FullName { get; set; } | |
public string Address { get; set; } | |
} | |
public class CustomerList : ArrayList | |
{ | |
public new Customer this[int index] | |
{ | |
get { return (Customer)base[index]; } | |
set { base[index] = value; } | |
} | |
} |
如何将数组数据导入Excel
要从数组导入数据到电子表格,调用 ImportArray 集合的 Cells 方法。 ImportArray 方法有许多重载版本,但典型的重载采用以下参数:
- Array,要从中导入内容的数组对象。
- 行号,第一个单元格的行号,数据将被导入到该单元格。
- 列号,第一个单元格的列号,数据将被导入到该单元格。
- 是否垂直,指定是垂直还是水平导入数据的布尔值。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
// Obtaining the reference of the worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Creating an array containing names as string values | |
string[] names = new string[] { "laurence chen", "roman korchagin", "kyle huang" }; | |
// Importing the array of names to 1st row and first column vertically | |
worksheet.Cells.ImportArray(names, 0, 0, true); | |
// Saving the Excel file | |
workbook.Save(dataDir + "DataImport.out.xls"); |
如何将 ArrayList 中的数据导入 Excel
要从 ArrayList 导入数据到工作表,请调用 Cells 集合的 ImportArrayList 方法。ImportArray 方法需要以下参数:
- ArrayList,代表要导入的 ArrayList 对象。
- 行号,代表将要导入数据的第一个单元格的行号。
- 列号,代表将要导入数据的第一个单元格的列号。
- 是否垂直,指定是垂直还是水平导入数据的布尔值。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
// Obtaining the reference of the worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Instantiating an ArrayList object | |
ArrayList list = new ArrayList(); | |
// Add few names to the list as string values | |
list.Add("laurence chen"); | |
list.Add("roman korchagin"); | |
list.Add("kyle huang"); | |
list.Add("tommy wang"); | |
// Importing the contents of ArrayList to 1st row and first column vertically | |
worksheet.Cells.ImportArrayList(list, 0, 0, true); | |
// Saving the Excel file | |
workbook.Save(dataDir + "DataImport.out.xls"); |
如何从自定义对象导入数据到Excel
要从对象集合导入数据到工作表,请使用 ImportCustomObjects。向该方法提供一个列名/属性列表,以显示您所需的对象列表。
private void TestImportingFromCustomObject() | |
{ | |
//Examples-CSharp-Data-Handling-Importing-ImportingFromCustomObject-1.cs | |
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Instantiate a new Workbook | |
Workbook book = new Workbook(); | |
Worksheet sheet = book.Worksheets[0]; | |
// Define List | |
List<Person> list = new List<Person>(); | |
list.Add(new Person("Mike", 25)); | |
list.Add(new Person("Steve", 30)); | |
list.Add(new Person("Billy", 35)); | |
ImportTableOptions imp = new ImportTableOptions(); | |
imp.InsertRows = true; | |
// We pick a few columns not all to import to the worksheet | |
// We pick a few columns not all to import to the worksheet | |
sheet.Cells.ImportCustomObjects((System.Collections.ICollection)list, | |
new string[] { "Name", "Age" }, | |
true, | |
0, | |
0, | |
list.Count, | |
true, | |
"dd/mm/yyyy", | |
false); | |
// Auto-fit all the columns | |
book.Worksheets[0].AutoFitColumns(); | |
// Save the Excel file | |
book.Save(dataDir + "ImportedCustomObjects.out.xls"); | |
} | |
class Person | |
{ | |
int _age; | |
string _name; | |
public int Age | |
{ | |
get | |
{ | |
return _age; | |
} | |
set | |
{ | |
_age = value; | |
} | |
} | |
public string Name | |
{ | |
get | |
{ | |
return _name; | |
} | |
set | |
{ | |
_name = value; | |
} | |
} | |
public Person(string name, int age) | |
{ | |
Age = age; | |
Name = name; | |
} | |
} |
如何从自定义对象以及检查合并区域导入数据到Excel
要从对象集合导入数据到包含合并单元格的工作表中,请使用 ImportTableOptions.CheckMergedCells 属性。如果Excel模板中有合并单元格,请将 ImportTableOptions.CheckMergedCells 属性的值设置为 true。将 ImportTableOptions 对象与列名/属性列表一起传递给方法,以显示您所需的对象列表。以下代码示例演示了使用 ImportTableOptions.CheckMergedCells 属性将自定义对象的数据导入到合并单元格中。请参阅附加的 源Excel 文件和 输出Excel 文件以供参考。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
public class ImportCustomObjectsToMergedArea | |
{ | |
public static void Run() | |
{ | |
//Source directory | |
string sourceDir = RunExamples.Get_SourceDirectory(); | |
//Output directory | |
string outputDir = RunExamples.Get_OutputDirectory(); | |
Workbook workbook = new Workbook(sourceDir + "sampleMergedTemplate.xlsx"); | |
List<Product> productList = new List<Product>(); | |
//Creating collection of test items | |
for (int i = 0; i < 3; i++) | |
{ | |
Product product = new Product | |
{ | |
ProductId = i, | |
ProductName = "Test Product - " + i | |
}; | |
productList.Add(product); | |
} | |
ImportTableOptions tableOptions = new ImportTableOptions(); | |
tableOptions.CheckMergedCells = true; | |
tableOptions.IsFieldNameShown = false; | |
//Insert data to excel template | |
workbook.Worksheets[0].Cells.ImportCustomObjects((ICollection)productList, 1, 0, tableOptions); | |
workbook.Save(outputDir + "sampleMergedTemplate_out.xlsx", SaveFormat.Xlsx); | |
Console.WriteLine("ImportCustomObectsToMergedArea executed successfully.\r\n"); | |
} | |
} | |
public class Product | |
{ | |
public int ProductId { get; set; } | |
public string ProductName { get; set; } | |
} |
如何从DataTable导入数据到Excel
要从 DataTable 导入数据,请调用 Cells 集合的 ImportDataTable 方法。 ImportDataTable 方法有很多重载版本,但典型的重载接受以下参数:
- DataTable,要从中导入内容的 DataTable 对象。
- 是否显示字段名,指定 DataTable 的列名是否应导入到工作表作为首行。
- 起始单元格,表示应从其中导入 DataTable 内容的起始单元格的名称(例如“A1”)。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
// Obtaining the reference of the worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Instantiating a "Products" DataTable object | |
DataTable dataTable = new DataTable("Products"); | |
// Adding columns to the DataTable object | |
dataTable.Columns.Add("Product ID", typeof(Int32)); | |
dataTable.Columns.Add("Product Name", typeof(string)); | |
dataTable.Columns.Add("Units In Stock", typeof(Int32)); | |
// Creating an empty row in the DataTable object | |
DataRow dr = dataTable.NewRow(); | |
// Adding data to the row | |
dr[0] = 1; | |
dr[1] = "Aniseed Syrup"; | |
dr[2] = 15; | |
// Adding filled row to the DataTable object | |
dataTable.Rows.Add(dr); | |
// Creating another empty row in the DataTable object | |
dr = dataTable.NewRow(); | |
// Adding data to the row | |
dr[0] = 2; | |
dr[1] = "Boston Crab Meat"; | |
dr[2] = 123; | |
// Adding filled row to the DataTable object | |
dataTable.Rows.Add(dr); | |
ImportTableOptions tableOptions = new ImportTableOptions(); | |
// Importing the contents of DataTable to the worksheet starting from "A1" cell, | |
// Where true specifies that the column names of the DataTable would be added to | |
// The worksheet as a header row | |
tableOptions.IsFieldNameShown = true; | |
worksheet.Cells.ImportData(dataTable, 0, 0, tableOptions); | |
// Saving the Excel file | |
workbook.Save(dataDir + "DataImport.out.xls"); |
如何从动态对象作为数据源导入数据到Excel
Aspose.Cells提供了处理动态对象作为数据源的功能。它有助于在对象动态添加属性的情况下使用数据源。一旦属性被添加到对象中,Aspose.Cells将第一个条目视为模板并相应处理其他条目。这意味着如果某些动态属性仅添加到第一个项目而未添加到其他对象,则Aspose.Cells将认为集合中的所有项目应该是相同的。
在这个例子中,使用了最初仅包含两个变量的模板模型。这个列表被转换成动态对象列表。然后一些额外的字段被添加进去,最后加载到工作簿中。工作簿仅选择模板XLSX文件中的值。这个模板工作簿使用了智能标记器,它还包含参数。参数允许您修改信息布局的方式。关于智能标记器的详细信息可以从以下文章中获取:
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
using System.Collections.Generic; | |
using System.Dynamic; | |
using System.ComponentModel; | |
using System.Linq; | |
namespace Aspose.Cells.Examples.CSharp.Data.Handling.Importing | |
{ | |
public class ImportingFromDynamicDataTable | |
{ | |
//Source directory | |
static string sourceDir = RunExamples.Get_SourceDirectory(); | |
//Output directory | |
static string outputDir = RunExamples.Get_OutputDirectory(); | |
public static void Run() | |
{ | |
// ExStart:1 | |
// Here data is filled into a list of Model class containing two fields only | |
var data = GetData(); | |
// Based upon business some additional fields e.g. unique id is added | |
var modifiedData = new Converter().GetModifiedData(data); | |
// Modified data is still an object but it is a dynamic one now | |
modifiedData.First().Id = 20; | |
// Following field is added in the dynamic objects list, but it will not be added to workbook as template file does not have this field | |
modifiedData.First().Id2 = 200; | |
// Create workbook and fill it with the data | |
Workbook workbook = new Workbook(sourceDir + @"ExcelTemplate.xlsx"); | |
WorkbookDesigner designer = new WorkbookDesigner(workbook); | |
designer.SetDataSource("modifiedData", modifiedData); | |
designer.Process(); | |
designer.Workbook.Save(outputDir + @"ModifiedData.xlsx"); | |
// Base Model does work but doesn't have the Id | |
Workbook workbookRegular = new Workbook(sourceDir + @"ExcelTemplate.xlsx"); | |
WorkbookDesigner designerRegular = new WorkbookDesigner(workbookRegular); | |
designerRegular.SetDataSource("ModifiedData", data); | |
designerRegular.Process(); | |
designerRegular.Workbook.Save(outputDir + @"ModifiedDataRegular.xlsx"); | |
// ExEnd:1 | |
} | |
private static List<Model> GetData() | |
{ | |
return new List<Model> | |
{ | |
new Model{ Code = 1 , Name = "One" }, | |
new Model{ Code = 2 , Name = "Two" }, | |
new Model{ Code = 3 , Name = "Three" }, | |
new Model{ Code = 4 , Name = "Four" }, | |
new Model{ Code = 5 , Name = "Five" } | |
}; | |
} | |
} | |
public class Model | |
{ | |
public string Name { get; internal set; } | |
public int Code { get; internal set; } | |
} | |
public class Converter | |
{ | |
private int _uniqueNumber; | |
public List<dynamic> GetModifiedData(List<Model> data) | |
{ | |
var result = new List<dynamic>(); | |
result.AddRange(data.ConvertAll<dynamic>(i => AddId(i))); | |
return result; | |
} | |
private dynamic AddId(Model i) | |
{ | |
var result = TransformToDynamic(i); | |
result.Id = GetUniqueNumber(); | |
return result; | |
} | |
private int GetUniqueNumber() | |
{ | |
var result = _uniqueNumber; | |
_uniqueNumber++; | |
return result; | |
} | |
private dynamic TransformToDynamic(object dataObject) | |
{ | |
IDictionary<string, object> expando = new ExpandoObject(); | |
foreach (PropertyDescriptor property in TypeDescriptor.GetProperties(dataObject.GetType())) | |
expando.Add(property.Name, property.GetValue(dataObject)); | |
return expando as dynamic; | |
} | |
} | |
} |
如何将DataColumn导入Excel
一个 DataTable 或 DataView 对象由一个或多个列组成。开发人员还可以通过调用 ImportData 集合的 Cells 方法从 DataTable 或 DataView 中导入任何列/列的数据。 ImportData 方法接受 ImportTableOptions 类型的参数。 ImportTableOptions 类提供了 ColumnIndexes 属性,该属性接受一个列索引的数组。
下面提供的示例代码演示了使用 ImportTableOptions.ColumnIndexes 来导入选择性列。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Instantiating a "Products" DataTable object | |
DataTable dataTable = new DataTable("Products"); | |
// Adding columns to the DataTable object | |
dataTable.Columns.Add("Product ID", typeof(Int32)); | |
dataTable.Columns.Add("Product Name", typeof(string)); | |
dataTable.Columns.Add("Units In Stock", typeof(Int32)); | |
// Creating an empty row in the DataTable object | |
DataRow dr = dataTable.NewRow(); | |
// Adding data to the row | |
dr[0] = 1; | |
dr[1] = "Aniseed Syrup"; | |
dr[2] = 15; | |
// Adding filled row to the DataTable object | |
dataTable.Rows.Add(dr); | |
// Creating another empty row in the DataTable object | |
dr = dataTable.NewRow(); | |
// Adding data to the row | |
dr[0] = 2; | |
dr[1] = "Boston Crab Meat"; | |
dr[2] = 123; | |
// Adding filled row to the DataTable object | |
dataTable.Rows.Add(dr); | |
// Instantiate a new Workbook | |
Workbook book = new Workbook(); | |
Worksheet sheet = book.Worksheets[0]; | |
// Create import options | |
ImportTableOptions importOptions = new ImportTableOptions(); | |
importOptions.IsFieldNameShown = true; | |
importOptions.IsHtmlString = true; | |
// Importing the values of 2nd column of the data table | |
sheet.Cells.ImportData(dataTable, 1, 1, importOptions); | |
// Save workbook | |
book.Save(dataDir + "DataImport.out.xls"); |
如何将DataView导入Excel
要从DataView导入数据,请调用Cells集合的ImportData方法。 ImportData方法有多个重载版本,但DataView的版本需要以下参数:
- DataView: 您将要从中导入内容的 DataView 对象。
- First Row: 数据将要导入到的第一个单元格的行号。
- First Column: 数据将要导入到的第一个单元格的列号。
- ImportTableOptions: 导入选项。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
// Obtaining the reference of the worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Instantiating a "Products" DataTable object | |
DataTable dataTable = new DataTable("Products"); | |
// Adding columns to the DataTable object | |
dataTable.Columns.Add("Product ID", typeof(Int32)); | |
dataTable.Columns.Add("Product Name", typeof(string)); | |
dataTable.Columns.Add("Units In Stock", typeof(Int32)); | |
// Creating an empty row in the DataTable object | |
DataRow dr = dataTable.NewRow(); | |
// Adding data to the row | |
dr[0] = 1; | |
dr[1] = "Aniseed Syrup"; | |
dr[2] = 15; | |
// Adding filled row to the DataTable object | |
dataTable.Rows.Add(dr); | |
// Creating another empty row in the DataTable object | |
dr = dataTable.NewRow(); | |
// Adding data to the row | |
dr[0] = 2; | |
dr[1] = "Boston Crab Meat"; | |
dr[2] = 123; | |
// Adding filled row to the DataTable object | |
dataTable.Rows.Add(dr); | |
// Importing the contents of the data view to the worksheet | |
ImportTableOptions options = new ImportTableOptions(); | |
options.IsFieldNameShown = true; | |
worksheet.Cells.ImportData(dataTable.DefaultView, 0, 0, options); | |
// Saving the Excel file | |
workbook.Save(dataDir + "output.xls"); |
如何将DataGrid导入Excel
可以通过调用集合的Cells方法从DataGrid导入数据。 ImportDataGrid方法有多个重载版本,但典型的重载版本需要以下参数:
- Data grid, 要从中导入内容的 DataGrid 对象。
- Row Number, 数据将要导入到的第一个单元格的行号。
- Column Number, 数据将要导入到的第一个单元格的列号。
- Insert Rows, 一个布尔属性,指示是否应向工作表添加额外行以适应数据。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create a DataTable object and set it as DataSource of DataGrid. | |
DataTable dataTable = new DataTable("Products"); | |
dataTable.Columns.Add("Product ID", typeof(Int32)); | |
dataTable.Columns.Add("Product Name", typeof(string)); | |
dataTable.Columns.Add("Units In Stock", typeof(Int32)); | |
DataRow dr = dataTable.NewRow(); | |
dr[0] = 1; | |
dr[1] = "Aniseed Syrup"; | |
dr[2] = 15; | |
dataTable.Rows.Add(dr); | |
dr = dataTable.NewRow(); | |
dr[0] = 2; | |
dr[1] = "Boston Crab Meat"; | |
dr[2] = 123; | |
dataTable.Rows.Add(dr); | |
// Now take care of DataGrid | |
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid(); | |
dg.DataSource = dataTable; | |
dg.DataBind(); | |
// We have a DataGrid object with some data in it. | |
// Lets import it into our spreadsheet | |
// Creat a new workbook | |
Workbook workbook = new Workbook(); | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Importing the contents of the data grid to the worksheet | |
worksheet.Cells.ImportDataGrid(dg, 0, 0, false); | |
// Save it as Excel file | |
workbook.Save(dataDir + "output.xlsx"); |
如何将GridView导入Excel
要从GridView控件导入数据,请调用Cells集合的ImportGridView方法。
Aspose.Cells允许我们在导入数据到电子表格时尊重HTML格式化的值。当导入数据时启用HTML解析时,Aspose.Cells将HTML转换为相应的单元格格式。
如何将HTML格式化数据导入Excel
Aspose.Cells提供了一个Cells类,为从外部数据源导入数据提供了非常有用的方法。本文展示了在导入数据时如何解析HTML格式化文本并将HTML转换为格式化的单元格值。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
string output1Path = dataDir + "Output.out.xlsx"; | |
string output2Path = dataDir + "Output.out.ods"; | |
// Prepare a DataTable with some HTML formatted values | |
DataTable dataTable = new DataTable("Products"); | |
dataTable.Columns.Add("Product ID", typeof(Int32)); | |
dataTable.Columns.Add("Product Name", typeof(string)); | |
dataTable.Columns.Add("Units In Stock", typeof(Int32)); | |
DataRow dr = dataTable.NewRow(); | |
dr[0] = 1; | |
dr[1] = "<i>Aniseed</i> Syrup"; | |
dr[2] = 15; | |
dataTable.Rows.Add(dr); | |
dr = dataTable.NewRow(); | |
dr[0] = 2; | |
dr[1] = "<b>Boston Crab Meat</b>"; | |
dr[2] = 123; | |
dataTable.Rows.Add(dr); | |
// Create import options | |
ImportTableOptions importOptions = new ImportTableOptions(); | |
importOptions.IsFieldNameShown = true; | |
importOptions.IsHtmlString = true; | |
// Create workbook | |
Workbook workbook = new Workbook(); | |
Worksheet worksheet = workbook.Worksheets[0]; | |
worksheet.Cells.ImportData(dataTable, 0, 0, importOptions); | |
worksheet.AutoFitRows(); | |
worksheet.AutoFitColumns(); | |
workbook.Save(output1Path); | |
workbook.Save(output2Path); |
如何将数据从JSON导入Excel
Aspose.Cells为处理JSON提供了一个JsonUtility类。JsonUtility类具有一个用于导入JSON数据的ImportData方法。 Aspose.Cells还提供了一个代表JSON布局选项的JsonLayoutOptions类。 ImportData方法接受JsonLayoutOptions为参数。 JsonLayoutOptions类提供以下属性。
- ArrayAsTable:指示是否应将数组处理为表格。
- ConvertNumericOrDate: 获取或设置一个值,指示JSON中的字符串是否要转换为数字或日期。
- DateFormat:获取和设置日期值的格式。
- IgnoreArrayTitle: 指示是否忽略对象属性为数组时的标题。
- IgnoreNull:指示是否应忽略空值。
- IgnoreObjectTitle: 指示是否忽略对象属性为对象时的标题。
- NumberFormat:获取和设置数值值的格式。
- TitleStyle:获取和设置标题的样式。
下面给出的示例代码演示了使用 JsonUtility 和 JsonLayoutOptions 类来导入JSON数据。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Read File | |
string jsonInput = File.ReadAllText(dataDir + "Test.json"); | |
// Set Styles | |
CellsFactory factory = new CellsFactory(); | |
Style style = factory.CreateStyle(); | |
style.HorizontalAlignment = TextAlignmentType.Center; | |
style.Font.Color = System.Drawing.Color.BlueViolet; | |
style.Font.IsBold = true; | |
// Set JsonLayoutOptions | |
JsonLayoutOptions options = new JsonLayoutOptions(); | |
options.TitleStyle = style; | |
options.ArrayAsTable = true; | |
// Import JSON Data | |
JsonUtility.ImportData(jsonInput, worksheet.Cells, 0, 0, options); | |
// Save Excel file | |
workbook.Save(dataDir + "ImportingFromJson.out.xlsx"); |