You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by lili2 <la...@gmail.com> on 2008/03/19 14:16:55 UTC
Empty column Problem in excel file
Hi,
I create an excel file with POI et my data are well displayed (first line:
headers and the other lines are the body). I have an issue concerning the
fourth column - counting beginning at the end - is always empty. For
example, when I have 6 columns: the third is empty, when I have 10 columns:
the seventh is empty.
If anybody has already got this problem, I'm very interested because I don't
understand.
In the case nobody knows this problem could you please help to delete this
column? Does a function "remove the column where the first cell is empty"
exists?
Thank you very much.
As you notice, it's difficult for me to write and be clear in english so
here is the same message in french for those who are. :-)
Bonjour,
je génère un fichier excel avec POI et mes données sont bien affichées
(première ligne=entêtes et le reste est le corps). Je me heurte cependant à
un problème, à chaque fois que je génère mon fichier xls, la quatrième
colonne en partant de la fin est vide (et ce même si je génère plus de
colonnes).
Si quelqu'un s'est déjà heurté à ce problème, ça m'intéresse.
Sinon, je cherche un contournement qui serait: supprimer une colonne. Un
truc dans le genre "supprime la colonne où la première cellule est vide".
Est-il possible de supprimer une colonne entière? Si oui ,comment?
Merci beaucoup
--
View this message in context: http://www.nabble.com/Empty-column-Problem-in-excel-file-tp16143651p16143651.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Empty column Problem in excel file
Posted by Josh Micich <jo...@gmail.com>.
It would help much more if you could show us exactly how to reproduce
your first problem. As it stands, your solution does not shed much
light on this. Could you please post a simple (and fully working)
example of whatever code created a spreadsheet that would require this
'fix-up' that you are suggesting?
BTW - I tried to run solution you posted (to help understand what you
are doing), and I got this exception:
Exception in thread "main" java.lang.IllegalArgumentException: cell
must not be null
at org.apache.poi.hssf.usermodel.HSSFRow.removeCell(HSSFRow.java:153)
at example.poi.TestLili.deleteEmptyColumn(TestLili.java:70)
at example.poi.TestLili.processWorkbook(TestLili.java:44)
at example.poi.TestLili.main(TestLili.java:26)
(see attached code+spreadsheet to try for yourself)
In POI v3.0.2 I believe this same error will be NullPointerException.
-josh
Re: Empty column Problem in excel file
Posted by lili2 <la...@gmail.com>.
I found a solution to my problem. In case somebody has the same someday here
is what you can do:
(it was difficult because with a null cell you can't do anything so the
solution i found is to create a new cell instead when i found the null cell)
int colToDelete = 0;
for(short i=0;i<=r.getLastCellNum();i++)
{
HSSFCell c = r.getCell(i);
if(c==null)
{
colToDelete = i;
}
}
if(colToDelete!=0)
{
for(short i=(short)colToDelete;i<r.getLastCellNum();i++)
{
HSSFCell c2 = r.createCell( (short) i);
c2.setCellValue(r.getCell( (short) (i +
1)).getStringCellValue());
c2.setCellStyle(headerStyle);
}
r.removeCell(r.getCell( (short)r.getLastCellNum()));
for(short k=1;k<=sheetData.getLastRowNum();k++)
{
HSSFRow r2 = sheetData.getRow((short)k);
for(short i=(short)colToDelete;i<r2.getLastCellNum();i++)
{
HSSFCell c2 = r2.createCell( (short) i);
c2.setCellValue(r2.getCell( (short) (i +
1)).getStringCellValue());
c2.setCellStyle(bodyStyle);
}
r2.removeCell(r2.getCell( (short)r2.getLastCellNum()));
}
sheetData.autoSizeColumn((short)colToDelete);
}
Thank you for trying.
lili2 wrote:
>
> I go further in my problem:
> I don't know why but there is always a cell null (the fourth beginning by
> the end)
> So I can't read it, and the worse I can't even delete it!!!
> Do you have an idea on the solution to my problem: how could I erase a
> cell that exists but that is null!
> Thank you
>
> Nick Burch wrote:
>>
>> On Wed, 19 Mar 2008, lili2 wrote:
>>> I create an excel file with POI et my data are well displayed (first
>>> line: headers and the other lines are the body). I have an issue
>>> concerning the fourth column - counting beginning at the end - is always
>>> empty. For example, when I have 6 columns: the third is empty, when I
>>> have 10 columns: the seventh is empty.
>>
>> This does seem rather odd. Any chance you could write a small unit test
>> that shows up the problem, then upload this to bugzilla? That way, we'll
>> be able to take a look
>>
>>> In the case nobody knows this problem could you please help to delete
>>> this column? Does a function "remove the column where the first cell is
>>> empty" exists?
>>
>> There's nothing quite like that. You'd need to code it up yourself - test
>> the first row, then loop over the rest or the rows zapping columns
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
>> For additional commands, e-mail: dev-help@poi.apache.org
>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Empty-column-Problem-in-excel-file-tp16143651p16194943.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Empty column Problem in excel file
Posted by lili2 <la...@gmail.com>.
I go further in my problem:
I don't know why but there is always a cell null (the fourth beginning by
the end)
So I can't read it, and the worse I can't even delete it!!!
Do you have an idea on the solution to my problem: how could I erase a cell
that exists but that is null!
Thank you
Nick Burch wrote:
>
> On Wed, 19 Mar 2008, lili2 wrote:
>> I create an excel file with POI et my data are well displayed (first
>> line: headers and the other lines are the body). I have an issue
>> concerning the fourth column - counting beginning at the end - is always
>> empty. For example, when I have 6 columns: the third is empty, when I
>> have 10 columns: the seventh is empty.
>
> This does seem rather odd. Any chance you could write a small unit test
> that shows up the problem, then upload this to bugzilla? That way, we'll
> be able to take a look
>
>> In the case nobody knows this problem could you please help to delete
>> this column? Does a function "remove the column where the first cell is
>> empty" exists?
>
> There's nothing quite like that. You'd need to code it up yourself - test
> the first row, then loop over the rest or the rows zapping columns
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Empty-column-Problem-in-excel-file-tp16143651p16177820.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Empty column Problem in excel file
Posted by Nick Burch <ni...@torchbox.com>.
On Wed, 19 Mar 2008, lili2 wrote:
> I create an excel file with POI et my data are well displayed (first
> line: headers and the other lines are the body). I have an issue
> concerning the fourth column - counting beginning at the end - is always
> empty. For example, when I have 6 columns: the third is empty, when I
> have 10 columns: the seventh is empty.
This does seem rather odd. Any chance you could write a small unit test
that shows up the problem, then upload this to bugzilla? That way, we'll
be able to take a look
> In the case nobody knows this problem could you please help to delete
> this column? Does a function "remove the column where the first cell is
> empty" exists?
There's nothing quite like that. You'd need to code it up yourself - test
the first row, then loop over the rest or the rows zapping columns
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org