Set Format to Cells

Aspose.Cells Product Family
1

I want to set Text format for cell in csv file by default but its is general by default and sometimes change automatically according to data.
image.png (15.8 KB)

2

@rohi001,

Thanks for the screenshot.

Yes, Aspose.Cells works the same as what MS Excel does for CSV file format. See the following sample code on how to specify text format (see the table) to the row/col cells for your reference.
e.g.
Sample code:

......
// Obtaining the reference of the first (default) worksheet by passing its sheet index
Worksheet worksheet = workbook.getWorksheets().get(0);

// Adding a new Style to the styles collection of the Workbook object
Style style = workbook.createStyle();

// Setting the Number property to 49 which corresponds to text format
style.setNumber(49);

// Creating an object of StyleFlag
StyleFlag flag = new StyleFlag();

// Setting NumberFormat property to true so that only this aspect takes effect from Style object
flag.setNumberFormat(true);

// Applying style to the first row of the worksheet
worksheet.getCells().getRows().get(0).applyStyle(style, flag);

// Re-initializing the Style object
style = workbook.createStyle();

// Setting the Custom property to the "@" which denotes text/string format
style.setCustom("@");

// Applying style to the first column of the worksheet
worksheet.getCells().getColumns().get(0).applyStyle(style, flag);
3

It is working fine for xls but for csv it is not working.

Here is the code :

Workbook wb = new Workbook();
Worksheet sheet = wb.getWorksheets().get(0);

Cell cell = sheet.getCells().get(“A1”);
cell.setValue(“Hello World!”);

Style style = wb.createStyle();
style.setNumber(49);

StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);

sheet.getCells().getRows().get(0).applyStyle(style, flag);

style = wb.createStyle();
style.setCustom("@");

sheet.getCells().getColumns().get(0).applyStyle(style, flag);

try {
wb.save(“C:\Users\admin\Desktop\MyBook.csv”, FileFormatType.CSV);
} catch (Exception e) {
e.printStackTrace();
}

Output :
image.png (20.1 KB)

4

@rohi001,

Thanks for the screenshot and code segment.

This is not an issue with the API rather it is limitation/behavior of CSV file format type. You cannot apply/specify formatting to the cells for a CSV format. For confirmation, perform the same operation as per your code in MS Excel manually, i.e., open a new workbook into MS Excel and input some string to a cell. Now specify “Text” formatting to the cell. Now save to “CSV (Comma Delimited)”, it will prompt some feature (e.g., regarding formattings) won’t be applied but forcefully save it. Close the file. Now open the saved CSV file again into Ms Excel and check the formatting of the cell using “Format Cells…” dialog. You will notice the formatting is set/changed to “General”.

As per the documentation about CSV file format type. it is already in plain text that contains records of data with comma separated values.

5

But, when I just change the value to 12/22 in same code. it’s get auto formatted to custom field but I don’t want it. I want the string(value) entered to cell either not gets auto formatted or by default it formatted to text.
…
Cell cell = sheet.getCells().get(“A1”);
cell.setValue(“12/22”);
…
Output :
image.png (18.3 KB)

6

@rohi001,

This is MS Excel application’s behavior and nothing else, MS Excel detects it as Date and auto-specify the (custom) formatting. There is no such information in the CSV file itself. Please try the code:
e.g.
Sample code:

Workbook wb = new Workbook();
Worksheet sheet = wb.getWorksheets().get(0);

Cell cell = sheet.getCells().get("A1");
cell.setValue("12/22");

try {
           wb.save("f:\\files\\MyBookasdfasdfasdf121.csv", FileFormatType.CSV);
      } catch (Exception e) {
          e.printStackTrace();
    }

There is no custom formatting details in the CSV file itself. Please open the output CSV file into notepad and you will see only “12/22” is there which is just a plain text.

7

Yes, I already know that. But I want to know is there any other way or property that can solve the problem.

Like if we can set the format to text by default.

8

@rohi001,

No, there is no specific attribute for it. Considering the nature of CSV file format itself, this is not possible either in MS Excel or by Aspose.Cells.

9

@rohi001,
If you need the value to be kept as 12/22(saved to the csv file) for the cell and also need the cell be displayed as “12/22” when opening the generated csv file in ms excel, we are afraid there is no available solution because it is ms excel’s behavior.

However, if you just want to see “12/22” for the cell when opening the generated csv file in ms excel but don’t care about the actual value for this cell, you may try:

  1. append some kind of special character which will be shown as blank to the tail of "12/22"
    or,
  2. set the value as: cell.setValue("=\"12/22\""); then the output value in the generated csv file will be taken as formula and show as what you expected.
10

@johnson.shi & @Amjad_Sahi,
Thankyou for your help :slightly_smiling_face:. It’s feel grateful that you both helped me out in understanding the base concept and also provide solution to resolve.

11

@rohi001,

You are welcome and good to know that our replies are helpful for your requirements.