Hi,
I am applying custom formatting “€#,##0.00” . But when the csv file is opened the euro symbol is replaced by €. I even tried specifying the encoding but still, it is not working
string FileName = @"C:\Users\xyz\Desktop\input.xlsx";
string destFileName = @"C:\Users\xyz\Desktop\output.csv";
List<string> LstReportObject = new List<string>();
LstReportObject.Add("Date");
LstReportObject.Add("Spend in USD");
//Load your source workbook
Workbook workbook1 = new Workbook(FileName);
MemoryStream msFormatting = workbook1.SaveToStream();
Workbook workbook = new Workbook(msFormatting);
Worksheet ws = workbook.Worksheets[0];
// ws.Cells[0,0].Value = "SEP=;" ;
//0-byte array
TxtSaveOptions opts = new TxtSaveOptions();
opts.Encoding = Encoding.Unicode;
opts.QuoteType = TxtValueQuoteType.Always;
opts.Separator = ';';
byte[] workbookData = new byte[0];
string[] columnName = new string[2];
string[] columnHeader = new string[2];
for (int i = 0; i < 2; i++)
{
columnName[i] = CellsHelper.ColumnIndexToName(i) + 4;
Cell cell = ws.Cells[columnName[i]];
columnHeader[i] = cell.Value.ToString();
}
foreach (var item in LstReportObject)
{
for (int i = 0; i < columnHeader.Length; i++)
{
if (columnHeader[i] == item)
{
if (item == "Spend in USD")
{
StyleFlag NumberFlag = new StyleFlag();
NumberFlag.NumberFormat = true;
Style NumberStyle = workbook.CreateStyle();
NumberStyle.Custom = "€#,##0.00";
ws.Cells.Columns[i].ApplyStyle(NumberStyle, NumberFlag);
}
break;
}
}
}
//Text save options. You can use any type of separator
//opts.Encoding = Encoding.GetEncoding("ISO - 8859 - 1");
//Copy each worksheet data in text format inside workbook data array
for (int idx = 0; idx < workbook.Worksheets.Count; idx++)
{
//Save the active worksheet into text format
MemoryStream ms = new MemoryStream();
workbook.Worksheets.ActiveSheetIndex = idx;
workbook.Save(ms, opts);
//Save the worksheet data into sheet data array
ms.Position = 0;
byte[] sheetData = ms.ToArray();
string input = "SEP= ";
byte[] prependArray = Encoding.ASCII.GetBytes(input);
//Combine this worksheet data into workbook data arra
byte[] combinedArray = new byte[workbookData.Length + sheetData.Length + prependArray.Length];
Array.Copy(prependArray, 0, combinedArray, 0, prependArray.Length);
Array.Copy(workbookData, 0, combinedArray, prependArray.Length, workbookData.Length);
Array.Copy(sheetData, 0, combinedArray, prependArray.Length + workbookData.Length, sheetData.Length);
workbookData = combinedArray;
}
//Save entire workbook data into file
File.WriteAllBytes(destFileName, workbookData);
}
}
}
PFA the input excel files and output csv fileexcelforaspose.zip (100.5 KB)