导入和导出数据

将数据导入工作表

数据代表现实世界。为了理解数据,我们对其进行分析,并了解世界。数据转化为信息。

进行分析的方法有许多种:将数据放入电子表格并以不同方式操纵它是一种常见的方法。有了 Aspose.Cells,我们可以轻松创建电子表格,从各种外部来源获取数据并为分析做准备。

本文讨论了一些开发人员可以通过 Aspose.Cells 访问的数据导入技术。

使用 Aspose.Cells 导入数据

当您使用 Aspose.Cells 打开 Excel 文件时,文件中的所有数据会自动导入。Aspose.Cells 还可以从其他数据源导入数据:

Aspose.Cells提供了一个代表Microsoft Excel文件的类——WorkbookWorkbook类包含WorksheetCollection集合,该集合允许访问Excel文件中的每个工作表,工作表由Worksheet类表示,Worksheet类提供了Cells集合,Cells集合提供了从其他数据源导入数据的非常有用的方法。本文介绍了如何使用这些方法。

从数组导入

要从数组导入数据到电子表格,调用Cells集合的importArray方法。importArray方法有很多重载版本,但典型的重载需要以下参数:

  • Array,要从中导入内容的数组对象。
  • 行号,第一个单元格的行号,数据将被导入到该单元格。
  • 列号,第一个单元格的列号,数据将被导入到该单元格。
  • 是否垂直,指定是垂直还是水平导入数据的布尔值。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromArray.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(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
Cells cells = worksheet.getCells();
cells.importArray(names, 0, 0, false);
// Saving the Excel file
workbook.save(dataDir + "ImportingFromArray_out.xls");
// Printing the name of the cell found after searching worksheet
System.out.println("Process completed successfully");

从多维数组导入

要从多维数组导入数据到电子表格,调用Cells集合的相关importArray重载:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromMultiDimensionalArray.class) + "data/";
// Instantiate a new Workbook
Workbook workbook = new Workbook();
// Get the first worksheet (default sheet) in the Workbook
Cells cells = workbook.getWorksheets().get("Sheet1").getCells();
// Define a multi-dimensional array and store some data into it.
String[][] strArray = { { "A", "1A", "2A" }, { "B", "2B", "3B" } };
// Import the multi-dimensional array to the sheet
cells.importArray(strArray, 0, 0);
// Save the Excel file
workbook.save(dataDir + "IFMDA_out.xlsx");

从ArrayList导入

要从ArrayList导入数据到工作表,调用Cells集合的ImportArrayList方法。ImportArrayList方法需要以下参数:

  • ArrayList,要导入内容的ArrayList对象。
  • 行号,将导入内容的单元格范围的第一个单元格的行号。
  • 列号,将导入数据的第一个单元格的列号。
  • 是否垂直,指定是垂直还是水平导入数据的布尔值。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromArrayList.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(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.getCells().importArrayList(list, 0, 0, true);
// Saving the Excel file
workbook.save(dataDir + "IFromArrayList_out.xls");
// Printing the name of the cell found after searching worksheet
System.out.println("Process completed successfully");

将自定义对象导入到合并区域

要从包含合并单元格的工作表中导入对象集的数据,请使用ImportTableOptions.CheckMergedCells属性。如果Excel模板具有合并单元格,请将ImportTableOptions.CheckMergedCells属性的值设置为true。将ImportTableOptions对象与要显示的对象列表/属性一起传递给方法。以下代码示例演示了使用ImportTableOptions.CheckMergedCells属性从自定义对象导入数据到合并单元格。请参阅附加的source Excel文件和output Excel文件进行参考。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromArrayList.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(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.getCells().importArrayList(list, 0, 0, true);
// Saving the Excel file
workbook.save(dataDir + "IFromArrayList_out.xls");
// Printing the name of the cell found after searching worksheet
System.out.println("Process completed successfully");

从JSON导入数据

Aspose.Cells为处理JSON提供了JsonUtility类。JsonUtility类具有一个ImportData方法用于导入JSON数据。Aspose.Cells还提供了一个JsonLayoutOptions类,代表了JSON布局的选项。ImportData方法接受JsonLayoutOptions作为参数。JsonLayoutOptions类提供了以下属性。

下面给出的示例代码演示了使用JsonUtilityJsonLayoutOptions类来导入JSON数据的用法。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ImportingFromJson.class) + "Data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
// Read File
File file = new File(dataDir + "Test.json");
BufferedReader bufferedReader = new BufferedReader(new FileReader(file));
String jsonInput = "";
String tempString;
while ((tempString = bufferedReader.readLine()) != null) {
jsonInput = jsonInput + tempString;
}
bufferedReader.close();
// Set Styles
CellsFactory factory = new CellsFactory();
Style style = factory.createStyle();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.getFont().setColor(Color.getBlueViolet());
style.getFont().setBold(true);
// Set JsonLayoutOptions
JsonLayoutOptions options = new JsonLayoutOptions();
options.setTitleStyle(style);
options.setArrayAsTable(true);
// Import JSON Data
JsonUtility.importData(jsonInput, worksheet.getCells(), 0, 0, options);
// Save Excel file
workbook.save(dataDir + "ImportingFromJson.out.xlsx");

从工作表导出数据

Aspose.Cells不仅允许用户从外部数据源将数据导入到工作表中,还允许他们将工作表数据导出为数组。

使用Aspose.Cells导出数据 - 将数据导出到数组

Aspose.Cells提供了一个表示Microsoft Excel文件的Workbook类。Workbook类包含一个WorksheetCollection,允许访问Excel文件中的每个工作表。工作表由Worksheet类表示。Worksheet类提供了Cells集合。

数据可以通过Cells类的exportArray方法轻松地导出到一个数组对象。

包含强类型数据的列

电子表格将数据存储为一系列行和列。使用exportArray方法将工作表中的数据导出到数组。exportArray接受以下参数来将工作表数据导出为数组对象:

  • 行数,数据将从第一个单元格导出的行号。
  • 列数,数据将从第一个单元格导出的列号。
  • 行数,要导出的行数。
  • 列数,要导出的列数。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ExportingDataFromWorksheets.class) + "Data/";
// Creating a file stream containing the Excel file to be opened
FileInputStream fstream = new FileInputStream(dataDir + "book1.xls");
// Instantiating a Workbook object
Workbook workbook = new Workbook(fstream);
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
// Exporting the contents of 7 rows and 2 columns starting from 1st cell
// to Array.
Object dataTable[][] = worksheet.getCells().exportArray(0, 0, 7, 2);
// Printing the number of rows exported
System.out.println("No. Of Rows Exported: " + dataTable.length);
// Closing the file stream to free all resources
fstream.close();

高级主题