poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Mangeng <michael.mang...@inexess.com>
Subject Poi (HSSF) creates corrupt xls file
Date Tue, 22 Apr 2003 17:41:06 GMT
Hi

I`ve encountered a strange problem...

I am creating a little class which task is to export data from a 
TableModel into a EXISTING excel sheet which has special marks in it to 
specify the position of the TableModel-Column in the Excel Column.

The schema is very simple:
"#1" means: start export of column 0 at this cell..
"#2" for column 1 and so on...

The class searches the next mark ("#"+(columnNum+1)) and then starts to 
copy the data to the excel file - creating rows and cells if neccessary...

But after the file is stored again - MS Excel XP drops a note that the 
file is corrupted and data may be lost... (in fact there is no lost data 
but it should not complain :))
It says: "File error. A few number formats are probably lost." 
(translated from german :-)) Original message:
"Dateifehler. Einige Zahlenformate sind möglicherweise verloren gegangen."


The code is the following:

##############################################

import java.io.*;
import javax.swing.table.*;

import org.apache.poi.hssf.usermodel.*;


public class TemplateExporter {
   
    private TemplateExporter() {}
   
    public static void export(TableModel sourceTable, File targetFile, 
String mark) {
        if(sourceTable == null) throw new 
IllegalArgumentException("SourceTable cannot be null!");
        if(targetFile == null) throw new 
IllegalArgumentException("TargetFile cannot be null!");
        if(!targetFile.exists()) throw new 
IllegalArgumentException("TargetFile must exist!");
        if(!targetFile.canWrite()) throw new 
IllegalArgumentException("TargetFile must be writeable!");
       
        HSSFWorkbook workbook = null;

        try {
            workbook = new HSSFWorkbook(new FileInputStream(targetFile));
        } catch (Exception e) {
            e.printStackTrace();
        }
      
        HSSFSheet sheet = workbook.getSheetAt(0);
        if(sheet == null) throw new IllegalArgumentException("Could not 
find a excel sheet!");

        //loop throught all columns
        for(int x = 0;x<sourceTable.getColumnCount();x++) {
            //search for "#"+(x+1)
            String nextMark = mark+(x+1);   //search for markers (#1, 
#2,...)
            //TableModel column with index 0 will be placed at #1, index 
1 to #2,...
            int[] pos = getXYPos(nextMark,sheet);
            if(pos.length>0) {
                int xlsPosX = pos[0];
                int xlsPosY = pos[1];
               
                //loop throught all rows for this column
                for(int y = 0;y<sourceTable.getRowCount();y++) {
                    setStringVal(""+sourceTable.getValueAt(y,x), 
xlsPosX, xlsPosY+y,sheet);
                }
            }
        }
    
        OutputStream out = null;
        try {
            out = new FileOutputStream(targetFile);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try { if( out != null ) out.close(); } catch (Exception e) {}
        }
       
    }

    //returns the pos. of "mark" in the specified sheet - returns a 
int[] with array[0]=column, array[1]=row or array.length=0 if not found...
    private static int[] getXYPos(String mark, HSSFSheet sheet) {
        for(int xlsRow = 
sheet.getFirstRowNum();xlsRow<=sheet.getLastRowNum();xlsRow++) {
            HSSFRow row = sheet.getRow(xlsRow);
            if(row != null) {
                for(int xlsCol = 
row.getFirstCellNum();xlsCol<=row.getLastCellNum();xlsCol++) {
                    HSSFCell cell = row.getCell((short)xlsCol);
                    if(cell!=null && cell.getCellType() == 
HSSFCell.CELL_TYPE_STRING && cell.getStringCellValue().equals(mark)) {
                        return new int[] {xlsCol,xlsRow};
                    }
                }
            }
        }
        return new int[] {};
    }

    private static void setStringVal(String val, int columnIndex, int 
rowIndex, HSSFSheet sheet) {
        HSSFRow row = sheet.getRow(rowIndex);
        if(row == null) row = sheet.createRow((short)rowIndex);
       
        HSSFCell cell = row.getCell((short)columnIndex);
        if(cell == null) cell = row.createCell((short)columnIndex);
       
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(val);
    }
   
      
    public static void main(String[] args) {
        if(args == null || args.length =0 )
            args = new String[1];
            args[0] = "test.xls";
        }
        try {
            export(new TestTableModel(), new File(args[0]), "#");
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("done");
    }
   
    static class TestTableModel implements TableModel {
        public void 
addTableModelListener(javax.swing.event.TableModelListener 
tableModelListener) {}
        public Class getColumnClass(int param) {return String.class;}
        public int getColumnCount() {return 8; }
        public String getColumnName(int param) {return "Column"+param;}
        public int getRowCount() {return 8;}
        public Object getValueAt(int param, int param1) {return 
param+"/"+param1;}
        public boolean isCellEditable(int param, int param1) {return 
false; }
        public void 
removeTableModelListener(javax.swing.event.TableModelListener 
tableModelListener) {}
        public void setValueAt(Object obj, int param, int param2) {}
    }
}

##############################################

I`ve no clue what i`ve done wrong....

thanks, greets
mike


Mime
View raw message