将 CSV、TSV 和 TXT 转换为 Excel
打开 CSV 文件
逗号分隔值(CSV)文件包含记录,其中值由逗号分隔。 数据存储为表格,其中每列由逗号字符分隔并由双引号字符引用。 如果字段值包含双引号字符,则用一对双引号字符进行转义。 您还可以使用 Microsoft Excel 将电子表格数据导出到 CSV。
// 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); | |
// Instantiate LoadOptions specified by the LoadFormat. | |
LoadOptions loadOptions4 = new LoadOptions(LoadFormat.Csv); | |
// Create a Workbook object and opening the file from its path | |
Workbook wbCSV = new Workbook(dataDir + "Book_CSV.csv", loadOptions4); | |
Console.WriteLine("CSV file 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-.NET | |
//Source directory | |
string sourceDir = RunExamples.Get_SourceDirectory(); | |
var filename = sourceDir + "[20180220142533][ASPOSE_CELLS_TEST].csv"; | |
//Load CSV file | |
var workbook = new Workbook(filename, new TxtLoadOptions() { Separator = ';', LoadFilter = new LoadFilter(LoadDataFilterOptions.CellData), CheckExcelRestriction = false, ConvertNumericData = false, ConvertDateTimeData = false }); | |
Console.WriteLine(workbook.Worksheets[0].Name); // (20180220142533)(ASPOSE_CELLS_T | |
Console.WriteLine(workbook.Worksheets[0].Name.Length); // 31 | |
Console.WriteLine("CSV file opened successfully!"); |
使用首选解析器
不总是需要使用CSV文件的默认解析器设置。有时,导入CSV文件不会生成预期的输出,例如日期格式与预期不同或者对待空字段的方式不同。为此目的,TxtLoadOptions.PreferredParsers可用于根据要求提供自己的首选解析器来解析不同数据类型。以下示例代码演示了首选解析器的使用。
可以从以下链接下载示例源文件和输出文件,以测试此功能。
outputsamplePreferredParser.xlsx
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
class TextParser : ICustomParser | |
{ | |
public object ParseObject(string value) | |
{ | |
return value; | |
} | |
public string GetFormat() | |
{ | |
return ""; | |
} | |
} | |
class DateParser : ICustomParser | |
{ | |
public object ParseObject(string value) | |
{ | |
DateTime myDate = DateTime.ParseExact(value, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture); | |
return myDate; | |
} | |
public string GetFormat() | |
{ | |
return "dd/MM/yyyy"; | |
} | |
} | |
public static void Main() | |
{ | |
// Initialize Text File's LoadFormat | |
LoadFormat oLoadFormat = LoadFormat.Csv; | |
// Initialize Text File's Load options | |
TxtLoadOptions oTxtLoadOptions = new TxtLoadOptions(oLoadFormat); | |
// Specify the separatot character | |
oTxtLoadOptions.Separator = Convert.ToChar(","); | |
// Specify the encoding scheme | |
oTxtLoadOptions.Encoding = System.Text.Encoding.UTF8; | |
// Set the flag to true for converting datetime data | |
oTxtLoadOptions.ConvertDateTimeData = true; | |
// Set the preferred parsers | |
oTxtLoadOptions.PreferredParsers = new ICustomParser[] { new TextParser(), new DateParser() }; | |
// Initialize the workbook object by passing CSV file and text load options | |
Workbook oExcelWorkBook = new Aspose.Cells.Workbook(sourceDir + "samplePreferredParser.csv", oTxtLoadOptions); | |
// Get the first cell | |
Cell oCell = oExcelWorkBook.Worksheets[0].Cells["A1"]; | |
// Display type of value | |
Console.WriteLine("A1: " + oCell.Type.ToString() + " - " + oCell.DisplayStringValue); | |
// Get the second cell | |
oCell = oExcelWorkBook.Worksheets[0].Cells["B1"]; | |
// Display type of value | |
Console.WriteLine("B1: " + oCell.Type.ToString() + " - " + oCell.DisplayStringValue); | |
// Save the workbook to disc | |
oExcelWorkBook.Save(outputDir + "outputsamplePreferredParser.xlsx"); | |
Console.WriteLine("OpeningCSVFilesWithPreferredParser executed successfully.\r\n"); | |
} |
使用自定义分隔符打开文本文件
文本文件用于在不格式化的情况下保存电子表格数据。 文件是一种可以具有一些自定义分隔符的纯文本文件。
// 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 filePath = dataDir + "Book11.csv"; | |
// Instantiate Text File's LoadOptions | |
TxtLoadOptions txtLoadOptions = new TxtLoadOptions(); | |
// Specify the separator | |
txtLoadOptions.Separator = Convert.ToChar(","); | |
// Specify the encoding type | |
txtLoadOptions.Encoding = System.Text.Encoding.UTF8; | |
// Create a Workbook object and opening the file from its path | |
Workbook wb = new Workbook(filePath, txtLoadOptions); | |
// Save file | |
wb.Save(dataDir+ "output.txt"); |
打开制表符分隔文件
制表符分隔(文本)文件包含电子表格数据,但没有任何格式。数据按行和列排列,就像在表格和电子表格中一样。基本上,制表符分隔文件是一种带有每个列之间制表符的特殊类型的纯文本文件。
// 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); | |
// Opening Tab Delimited Files | |
// Instantiate LoadOptions specified by the LoadFormat. | |
LoadOptions loadOptions5 = new LoadOptions(LoadFormat.TabDelimited); | |
// Create a Workbook object and opening the file from its path | |
Workbook wbTabDelimited = new Workbook(dataDir + "Book1TabDelimited.txt", loadOptions5); | |
Console.WriteLine("Tab delimited file opened successfully!"); |
打开制表符分隔数值(TSV)文件
制表符分隔数值(TSV)文件包含电子表格数据,但没有任何格式。它与制表符分隔文件相同,其中数据按行和列排列,就像在表格和电子表格中一样。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
//Source directory | |
string sourceDir = RunExamples.Get_SourceDirectory(); | |
// Instantiate LoadOptions specified by the LoadFormat. | |
LoadOptions loadOptions = new LoadOptions(LoadFormat.Tsv); | |
// Create a Workbook object and opening the file from its path | |
Workbook workbook = new Workbook(sourceDir + "SampleTSVFile.tsv", loadOptions); | |
// Using the Sheet 1 in Workbook | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Accessing a cell using its name | |
Cell cell = worksheet.Cells["C3"]; | |
Console.WriteLine("Cell Name: " + cell.Name + " Value: " + cell.StringValue); |