Hi,
I have generated a CSV file but in the file each cell has to be expanded to view the complete information present in that cell. Is there a way to format the cells using code/java services so that all the information present in each cell can be viewed without manually expanding each cell in the CSV file? 
regards,
If it is a CSV file, no. CSV files do not contain formatting information. Only data.
CSV files are readable/writable by Excel but they are not Excel files per se.
Yes, it is Comma Separated Values file, containing data, which are separated by commas and spaces.
Hi Reamon, Suvigya
Its true that CSV files are just comma separated data file and can be opened with MS excel to view it but is there a way to format it so that the cells are larger and the data correctly fits in it?
thanks and regards
No. There is no way to do any formatting. A CSV file consists of data only. There is no place to store formatting information.
Hi Reamon,
Thanks for the info regarding csv files. Now can we generate a MS excel file(.xls) in webMethods, in which the cells can be formatted. If this is possible I would rather generate a formatted .xls file than generating a .csv file. The data generated in csv file is not very presentable without formatting.
thanks and regards
use /tab as the delimiter/separator
If you have the CSV file as comma separated or with any delimiter, directly open it with Microsoft MS Excel. It will pop up the Text Import Wizard where in you can choose Delimited in first tab, provide the appropriate delimiter in the second tab, choose next and finish. It will open up your CSV in Excel.
Senthil
Hi Senthil,
I am able to open CSV file without any problem using excel. But CSV is not properly formatted and it gets difficult for the user to read the file(for example he has to expand every cell to view the complete information) and members of this forum say that CSV files cannot be formatted. So now I want to create an original MS excel file(.xls extension) using webMethods, so that this .xls file can be formatted properly(again this formatting has to be done in wM) and is ready for the user to read it(i.e, without expanding or changing any cells length).
So can we create a original MS excel file using wM(or java service)?
thanks,
regards,
First of all you should check if generating an Excel file from within webMethods is the right way to go. I usually would advise against that.
There is no Service or adapter available from SAG for Excel, but you may code you own Java which does. There are already some posts about it, e.g. [URL]wmusers.com
Regards
Martin
As Martin mentions, yes it is possible to write an xls file from within IS-hosted facilities. But it is far from trivial. There are threads on these forums that cover some of the highlights. Do not underestimate the effort to do this. It will require good Java and Developer/IS knowledge skills. If you’re relatively new to either I would recommend getting help.
Hi
With a little bit of help from this forum and a lot of help from google I have written a java service using JExcel package/jxl.jar file. The service takes some inputs and generates a formatted(with different fonts/style/colors/cell sizes etc) MS Excel sheet and writes it to a location. For anyone who is stuck with generating formatted excel , here is a part of code. This may give you an idea …
[highlight=java]try
{
IDataCursor pipelineCursor2 = pipeline.getCursor();
String in1 = IDataUtil.getString( pipelineCursor2, “in1” );
String filename = “F:\Napster\output.xls”;
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale(“en”, “EN”));
WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), ws);
WritableSheet s = workbook.createSheet(“Sheet1”, 0);
String S1,T1,J1;
/* Format the Font */
WritableFont wf1 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat cfbold = new WritableCellFormat(wf1);
cfbold.setWrap(true);cfbold.setBackground(Colour.ICE_BLUE);
cfbold.setBorder(Border.ALL, BorderLineStyle.THIN ,Colour.BLACK);
WritableFont wf2 = new WritableFont(WritableFont.ARIAL, 10);
WritableCellFormat cf1 = new WritableCellFormat(wf2);
cf1.setWrap(true);cf1.setBorder(Border.ALL, BorderLineStyle.THIN ,Colour.BLACK);
IDataCursor pipelineCursor = pipeline.getCursor();
IData[] inputList = IDataUtil.getIDataArray( pipelineCursor, "final" );
for(int i=0;i<inputList.length;i++)
{
if ( inputList[i] != null)
{
WritableCellFormat cf;
if (i==0)
cf=cfbold;
else
cf=cf1;
int col=i;
IDataCursor inputCursor = inputList[i].getCursor();
S1 = IDataUtil.getString( inputCursor, "S1" );
Label l = new Label(0,col,S1,cf);
s.addCell(l);
s.setColumnView(0,15);
//s.setRowView(0,250);
T1 = IDataUtil.getString( inputCursor, "T1" );
l = new Label(1,col,T1,cf);
s.addCell(l);
J1 = IDataUtil.getString( inputCursor, "J1" );
l = new Label(5,col,J1,cf);
s.addCell(l);
s.setColumnView(5,40);
inputCursor.destroy();
}
}
int j=inputList.length+2;
s.mergeCells(0, j, 4, j);
Label name1 = new Label(0,j,"Name",cfbold);
s.addCell(name1);
//s.mergeCells(5, j, 6, j);
name1 = new Label(5,j,"id",cfbold);
s.addCell(name1);
j=j+1;
IDataCursor pipelineCursor12 = pipeline.getCursor();
String Name = IDataUtil.getString( pipelineCursor, "Name" );
String ID = IDataUtil.getString( pipelineCursor, "ID" );
s.mergeCells(0, j, 4, j);
name1 = new Label(0,j,Name,cf1);
s.addCell(name1);
name1 = new Label(5,j,ID,cf1);
s.addCell(name1);
workbook.write();
workbook.close();
}
catch (Exception ex)
{
IDataCursor pipelineCursor1 = pipeline.getCursor();
IDataUtil.put( pipelineCursor1, “exception”, ex.toString() );
pipelineCursor1.destroy();
}[/highlight]
thanks,
regards,
Napster
You can also create excel file using Apache HSSF-POI …
Thanks for sharing the code.
Readers should keep in mind that this is for an Excel file, not a CSV file (per the original post).
JExcel API provide the varies classes to create, read, write xls files.
For maore info you can check with the following link.
[url]http://www.java-tips.org/other-api-tips/jexcel/how-to-create-an-excel-file.html[/url]
Hi,
JExcel API provide the varies classes to create, read, write xls files.
For more info you can check with the following link.
[URL]http://www.java-tips.org/other-api-tips/jexcel/how-to-create-an-excel-file.html[/URL]
Cheers,