All geek questions in one place

Worksheet.SaveAs ("file.csv") works differently in .NET 3.5 and .NET 4.0

I have a project written in C # using a function Interop.Excel. The method Worksheet.SaveAs("file.csv", ...)works differently with respect to the .NET Framework for which the project is built.

  • When compiling for the .NET Framework 3.5, the method saves the CSV file using a semicolon ( ;) as a delimiter (this is what I need).
  • When compiling for the .NET Framework 4, it uses a comma ( ,) as a delimiter without any code changes.

Can anyone explain what is happening? And how can I make excel always use a semicolon as a separator in CSV files, regardless of the target .NET Framework?

+4
source share
2 answers

you can set certain regional settings for your excel instance like this to make it record CSV:

var cI = new CultureInfo(locale);
var excel = new Microsoft.Office.Interop.Excel.Application();

excel.UseSystemSeparators = false;

excel.ThousandsSeparator = cI.NumberFormat.NumberGroupSeparator;
excel.DecimalSeparator = cI.NumberFormat.NumberDecimalSeparator;
0
source

According to this Link, the Local variable works correctly when you use Workbook.SaveAs, but always use a comma as a separator when trying to save a specific worksheet (for example, Worksheet (2). SaveAs).

Therefore, creating a new book and copying data into it should work:

Orig.Sheets(2).Range("A1:Q999").Copy
Set Export = Workbooks.Add
With Export
    .Worksheets(1).Range("A1").PasteSpecial (xlPasteValues)
    .SaveAs Filename:=Path & "\Name.csv", FileFormat:=xlCSV, Local:=True
    .Close SaveChanges:=False
End With
0
source

Source: https://habr.com/ru/post/1606549/

More articles:


All Articles