将 CSV、TSV 和 TXT 转换为 Excel
打开 CSV 文件
逗号分隔的值(CSV)文件包含由逗号分隔或分隔的记录值。在 CSV 文件中,数据以字段由逗号字符分隔并由双引号字符引用的表格格式存储。如果字段的值包含双引号字符,则用一对双引号字符进行转义。您还可以使用 Microsoft Excel 将电子表格数据导出为 CSV 文件。
要打开CSV文件,请使用 LoadOptions 类并选择在 LoadFormat 枚举中预定义的 CSV 值。
示例
// 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(OpeningCSVFiles.class) + "loading_saving/"; | |
// Opening CSV Files | |
// Creating and CSV LoadOptions object | |
LoadOptions loadOptions4 = new LoadOptions(LoadFormat.CSV); | |
// Creating an Workbook object with CSV file path and the loadOptions | |
// object | |
Workbook workbook6 = new Workbook(dataDir + "Book_CSV.csv", loadOptions4); | |
// Print message | |
System.out.println("CSV format workbook has been opened successfully."); |
打开 CSV 文件并替换无效字符
在 Excel 中,当打开具有特殊字符的 CSV 文件时,这些字符会被自动替换。Aspose.Cells API 也会执行相同的操作,如下面给出的代码示例所示。
示例
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
//Source directory | |
String dataDir = Utils.getSharedDataDir(OpeningCSVFilesAndReplacingInvalidCharacters.class) + "LoadingSavingConvertingAndManaging/"; | |
LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV); | |
//Load CSV file | |
Workbook workbook = new Workbook(dataDir + "[20180220142533][ASPOSE_CELLS_TEST].csv", loadOptions); | |
System.out.println(workbook.getWorksheets().get(0).getName()); // (20180220142533)(ASPOSE_CELLS_T | |
System.out.println(workbook.getWorksheets().get(0).getName().length()); // 31 | |
System.out.println("CSV file opened successfully!"); |
使用首选解析器打开 CSV 文件并不总是必需的。有时导入 CSV 文件会得到意外的输出,例如日期格式不符合预期或空字段的处理方式不同。为此可以使用 TxtLoadOptions.PreferredParsers 来提供自己喜欢的解析器,以根据要求解析不同的数据类型。以下示例代码演示了首选解析器的用法。
并非总是需要使用默认的解析器设置来打开CSV文件。有时导入CSV文件的输出与预期不同,例如日期格式不符合期望或空字段处理方式不同。为此,可使用 TxtLoadOptions.PreferredParsers 来提供自己偏好的解析器,以根据需求解析不同的数据类型。以下示例代码演示了首选解析器的用法。
可以从以下链接下载示例源文件和输出文件,以测试此功能。
outputsamplePreferredParser.xlsx
示例
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
class TextParser implements ICustomParser | |
{ | |
@Override | |
public Object parseObject(String s) { | |
return s; | |
} | |
@Override | |
public String getFormat() { | |
return ""; | |
} | |
} | |
class DateParser implements ICustomParser { | |
@Override | |
public Object parseObject(String s) { | |
Date myDate = null; | |
SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); | |
try { | |
myDate = formatter.parse(s); | |
} catch (ParseException e) { | |
e.printStackTrace(); | |
} | |
return myDate; | |
} | |
@Override | |
public String getFormat() { | |
return "dd/MM/yyyy"; | |
} | |
} | |
public class OpeningCSVFilesWithPreferredParser { | |
//Source directory | |
private static String sourceDir = Utils.Get_SourceDirectory(); | |
private static String outputDir = Utils.Get_OutputDirectory(); | |
public static void main(String[] args) throws Exception { | |
// Initialize Text File's Load options | |
TxtLoadOptions oTxtLoadOptions = new TxtLoadOptions(LoadFormat.CSV); | |
// Specify the separatot character | |
oTxtLoadOptions.setSeparator(','); | |
// Specify the encoding scheme | |
oTxtLoadOptions.setEncoding(Encoding.getUTF8()); | |
// Set the flag to true for converting datetime data | |
oTxtLoadOptions.setConvertDateTimeData(true); | |
// Set the preferred parsers | |
oTxtLoadOptions.setPreferredParsers(new ICustomParser[] { new TextParser(), new DateParser() }); | |
// Initialize the workbook object by passing CSV file and text load options | |
Workbook oExcelWorkBook = new Workbook(sourceDir + "samplePreferredParser.csv", oTxtLoadOptions); | |
// Get the first cell | |
Cell oCell = oExcelWorkBook.getWorksheets().get(0).getCells().get("A1"); | |
// Display type of value | |
System.out.println("A1: " + getCellType(oCell.getType()) + " - " + oCell.getDisplayStringValue()); | |
// Get the second cell | |
oCell = oExcelWorkBook.getWorksheets().get(0).getCells().get("B1"); | |
// Display type of value | |
System.out.println("B1: " + getCellType(oCell.getType()) + " - " + oCell.getDisplayStringValue()); | |
// Save the workbook to disc | |
oExcelWorkBook.save(outputDir + "outputsamplePreferredParser.xlsx"); | |
System.out.println("OpeningCSVFilesWithPreferredParser executed successfully.\r\n"); | |
} | |
private static String getCellType(int type){ | |
if(type == CellValueType.IS_STRING){ | |
return "String"; | |
} else if(type == CellValueType.IS_NUMERIC){ | |
return "Numeric"; | |
} else if(type == CellValueType.IS_BOOL){ | |
return "Bool"; | |
} else if(type == CellValueType.IS_DATE_TIME){ | |
return "Date"; | |
} else if(type == CellValueType.IS_NULL){ | |
return "Null"; | |
} else if(type == CellValueType.IS_ERROR){ | |
return "Error"; | |
} else{ | |
return "Unknown"; | |
} | |
} |
打开 TSV(制表符分隔)文件
制表符分隔文件包含电子表格数据但不包含任何格式。数据以行和列的形式排列,就像表格和电子表格一样。简单来说,制表符分隔的文件是一种特殊的纯文本文件,在文本中的每一列之间都有一个制表符。
要打开制表符分隔的文件,开发人员应使用 LoadOptions 类并选择在 LoadFormat 枚举中预定义的 TSV 值。
示例
// 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(OpeningTabDelimitedFiles.class) + "loading_saving/"; | |
// Creating and TAB_DELIMITED LoadOptions object | |
LoadOptions loadOptions5 = new LoadOptions(LoadFormat.TSV); | |
// Creating an Workbook object with Tab Delimited text file path and the | |
// loadOptions object | |
Workbook workbook7 = new Workbook(dataDir + "Book1TabDelimited.txt", loadOptions5); | |
// Print message | |
System.out.println("Tab Delimited workbook has been opened successfully."); |