Right, here you go. I have created an archive and uploaded it to Rapidshare because I have had trouble before with people not receiving attachments.
Folow this link; http://rapidshare.com/files/176143730/Demo.rar Choose the free download option and save the archive away onto your PC. If you open the archive you should see two files. The first is a simple workbook with the first few cells populated on row one. If you check these cells, you will see that I have formatted subsequent cells to hold text, an integer, a floating point number, a date/time value and lastly a currency value. This sheet would be your template and instaed of setting the format for a column, just set it for a cell in the first row of each column. Next, there is a java file called BorkedFormula. Terrible name, I know, but I was re-using some code I already had rather than create a new file but I think you will be able to see what happens. The important bits are the two ArrayLists, one to hold the cell styles from the read cells and the other the cell types. Next, you should be able to see that the code opens a Workbook, gets the first sheet, gets the first row from that sheet and then gets an Iterator to step through the cells on that row. The Iterator is used to address each cell in turn and store it's type and style into the ArrayLists. After the cells types and styles have been discovered, the code enters nested for loop that create a few cells. Note how the two ArrayLists are interrogated for the cell type and cell style. I reckon you could quite easilly modify your code to do something similar and make use of the template file you are currently emplying. It would give you the informatio about the cells styles ans types and you only need to 'remember' this ans set it as each cell is created. Hope this helps. --- On Tue, 12/23/08, noobguru <[email protected]> wrote: From: noobguru <[email protected]> Subject: RE: reading excel - can I get column format? To: [email protected] Date: Tuesday, December 23, 2008, 5:27 AM No, I have not found a way to get/set the format of a column yet. I wish this feature was available and it would help out a lot. That is what I'm trying to do. Yes, I am trying to populate a template with data. Since the template only has formatting information, I cannot keep the same formatting information in the template whenever I populate the text data in it. What happens is when I try to get a row (say the first one at position 0), it ends up being null (simply because there is no data in it). So I'm screwed from the start. I have to create the row, then I have to create a cell as well. This loses all of my template formatting information. Yes, the information of what the column should be formatted to is known in advance. That's why I format the template's columns accordingly. However, now I think I need to just create a configuration file that contains the formatting information and as I create each cell and populate the data, I can set the formatting to what this configuration file has. Would this be the best approach since I cannot read the formatting from the template (since it is in essence, empty)? Thanks so far for the help! Ok, I understand now, you are basically populating a template with data. Must admit I was surprised by your message as I thought you had found a way to get/set the format of a column using HSSF and thought that was not possible yet. As you read the data, do you know how it should be formatted or is this information bound up in the sheet - the template - alone? Do you know in advance how each column should be formatted - for example column two should contain numeric values and show all negative numbers with a leading minus sign and in red? If so, you can use these cues to set the format for the sheet as you populate each cell, this tends to be the way I do it myself - I build all the HSSFCellStyle class instances I will need, add any formatting and then treat each cell appropriately as I add it to the row. I know that you can get the set of styles and formats applied to the Workbook but do not think that any column or cell information is bound to them and, as your template sheet is effectively empty, I do not know at this stage how you can establish that link - I do not know but think that HSSF will tell you that the sheet is empty but I could be wrong so the first thing I would try is to read a row and recover the HSSFCellStyle object - and it's associated format - for each cell into an associative list that links column number to cell style. Then you can simply read the data from your file, find out which column it should be written into, get that cell style from the list and apply it. If this does not work with empty cells, populate the first row of your spreadsheet with data as you create the template, open the sheet, read that row, etc. --- On Mon, 12/22/08, noobguru <[email protected]> wrote: From: noobguru <[email protected]> Subject: RE: reading excel - can I get column format? To: [email protected] Date: Monday, December 22, 2008, 8:48 AM Also, there may or may not be data in the excel sheet at all. I just format the columns (by excel) and then put data in there accordingly (using hssf or whatever classes). But I need to know the formatting of the columns in order to put data in the excel sheet. noobguru wrote: > > I set the columns in the excel file using Excel. Now I'm trying to read > those formats from the excel file using the libraries given so that I take > a text file (with data in it) and place it in the appropriate columns. > > -- View this message in context: http://www.nabble.com/reading-excel---can-I-get-column-format--tp21099031p21144496.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
