Queria saber se alguem tem algum exemplo para criação de um arquivo XLS usando o JExcel API.
Olhei a documentação e achei meio complicado, não vi nenhum exemplo simples para começar e tambem sou pessimo no ingles! hehheeh
Alguem pode me dar uma força?!!
nunca usei essa API.
server POI ? se servir tenho exemplos …
Serve sim!!
Onde eu baixo o POI??
Voce tem um exemplo, para ler um arquivo e pegar os dados e manipular etc…etc…
Se tiver manda!!
Valeu, obrigado.
http://jakarta.apache.org/poi/index.html
[code]package com.ibm.fsw.pr.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.rmi.RemoteException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import javax.ejb.CreateException;
import javax.naming.NamingException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import com.ibm.ccibm.infra.CCException;
import com.ibm.fsw.pr.vo.PriceVO;
/**
public static InputStream buildExcel( final String templatePath )
throws CCException, SQLException, NamingException, RemoteException, CreateException {
// SSPricingAccessBean ejb = new SSPricingAccessBean();
// RowSet rws = ejb.getPriceRowSet( txYear, txMonth, cdBrand );
File temp = new File( templatePath + "\\PriceTemplate.xls" );
if( temp.exists() )
temp.delete();
try {
if( !temp.createNewFile() )
System.out.println( "bichou ..." );
}
catch( final IOException e ) {}
// TODO: need a better solution to this.
// I have a input stream and a wb created, but - yet - need to write then in disk …
InputStream excel = null;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet( “Price List” );
sheet.setSelected( true );
// if( rws == null ) {
// }
// else {
// BrandVO brand = ejb.getBrand( cdBrand );
// BrandType bt = brand.getBrandType();
// boolean bPC = bt.equals( BrandType.PC );
// boolean bNORMAL = bt.equals( BrandType.NORMAL );
// if( !bPC && !bNORMAL )
// throw new CCException( “FactorHelper.BuildSampleExcel: Invalid Brand Type.” );
// Style the cell with borders all around.
HSSFCellStyle styleL = wb.createCellStyle();
styleL.setBorderTop( HSSFCellStyle.BORDER_MEDIUM );
styleL.setBorderLeft( HSSFCellStyle.BORDER_MEDIUM );
styleL.setBorderBottom( HSSFCellStyle.BORDER_MEDIUM );
styleL.setFillBackgroundColor( HSSFColor.GREY_25_PERCENT.index );
HSSFCellStyle styleM = wb.createCellStyle();
styleM.setBorderTop( HSSFCellStyle.BORDER_MEDIUM );
styleM.setBorderBottom( HSSFCellStyle.BORDER_MEDIUM );
styleM.setFillBackgroundColor( HSSFColor.GREY_25_PERCENT.index );
HSSFCellStyle styleR = wb.createCellStyle();
styleR.setBorderTop( HSSFCellStyle.BORDER_MEDIUM );
styleR.setBorderBottom( HSSFCellStyle.BORDER_MEDIUM );
styleR.setBorderRight( HSSFCellStyle.BORDER_MEDIUM );
styleR.setFillBackgroundColor( HSSFColor.GREY_25_PERCENT.index );
HSSFRow row;
HSSFCell cel;
short iRow = 1;
short iCel = 1;
row = sheet.createRow( iRow++ );
cel = row.createCell( iCel++ );
cel.setCellType( HSSFCell.CELL_TYPE_STRING );
cel.setCellValue( "Type" );
cel.setCellStyle( styleL );
cel = row.createCell( iCel++ );
cel.setCellType( HSSFCell.CELL_TYPE_STRING );
cel.setCellValue( "Model" );
cel.setCellStyle( styleM );
cel = row.createCell( iCel++ );
cel.setCellType( HSSFCell.CELL_TYPE_STRING );
cel.setCellValue( "Feature" );
cel.setCellStyle( styleM );
cel = row.createCell( iCel++ );
cel.setCellType( HSSFCell.CELL_TYPE_STRING );
cel.setCellValue( "Description" );
cel.setCellStyle( styleM );
cel = row.createCell( iCel++ );
cel.setCellType( HSSFCell.CELL_TYPE_STRING );
cel.setCellValue( "Category" );
cel.setCellStyle( styleM );
cel = row.createCell( iCel++ );
cel.setCellType( HSSFCell.CELL_TYPE_STRING );
cel.setCellValue( "List Price" );
cel.setCellStyle( styleM );
cel = row.createCell( iCel++ );
cel.setCellType( HSSFCell.CELL_TYPE_STRING );
cel.setCellValue( "Amt Retail" );
cel.setCellStyle( styleM );
cel = row.createCell( iCel++ );
cel.setCellType( HSSFCell.CELL_TYPE_STRING );
cel.setCellValue( "Amt Whlse" );
cel.setCellStyle( styleR );
// while( rws.next() ) {
// }
sheet.setColumnWidth( (short)1, (short)1500 ); // type
sheet.setColumnWidth( (short)2, (short)1500 ); // model
sheet.setColumnWidth( (short)3, (short)1500 ); // feature
sheet.setColumnWidth( (short)4, (short)3000 ); // description
sheet.setColumnWidth( (short)5, (short)3000 ); // category
sheet.setColumnWidth( (short)6, (short)2000 ); // vl1
sheet.setColumnWidth( (short)7, (short)2000 ); // vl2
sheet.setColumnWidth( (short)8, (short)2000 ); // vl3
// only to go home;
row = sheet.createRow( 0 ); cel = row.createCell( (short)0 );
// }
try {
FileOutputStream output = new FileOutputStream( temp );
wb.write( output );
output.close();
excel = new FileInputStream( temp );
}
catch( final FileNotFoundException e ) {
System.out.println( "Arquivo bichado ..." + e.getMessage() );
}
catch( final IOException e ) {
System.out.println( "Arquivo bichado ..." + e.getMessage() );
}
return excel;
}
public static Collection readExcel( final String fullFileName,
final String txMonthYearB, final String txMonthYearE, final Integer cdBrand,
final boolean bNORMAL )
throws CCException, SQLException, NamingException, RemoteException, CreateException {
Collection col = new ArrayList();
try {
FileInputStream fis = new FileInputStream( fullFileName );
POIFSFileSystem fs = new POIFSFileSystem( fis );
HSSFWorkbook wb = new HSSFWorkbook( fs );
HSSFSheet sheet = wb.getSheetAt( 0 );
Date d[] = XHelper.getDatesBetweenBeginEnd(
XHelper.formatStringMMyyyyToDate( txMonthYearB ),
XHelper.formatStringMMyyyyToDate( txMonthYearE )
);
short iRow = 0;
short iCel = 0;
HSSFRow row = null;
HSSFCell cel = null;
boolean error = false;
String value = "";
try {
row = sheet.getRow( iRow++ );
cel = row.getCell( iCel );
error = cel == null;
if( !error ) value = cel.getStringCellValue();
error = !value.equalsIgnoreCase( "Type" );
} catch( final Exception e ) { error = true; }
if( error ) throw new CCException( "Invalid Microsoft Excel Template." );
int er = 0, ec = 0;
boolean hasData = true;
while( hasData ) {
iCel = 0;
ec = 0;
PriceVO vo = new PriceVO( (short)( iRow + 1 ) );
vo.setCdBrand( cdBrand );
row = sheet.getRow( iRow++ );
for( int i = 1; i < 9; i ++ ) {
boolean unread = false;
value = "";
try {
cel = row.getCell( iCel++ );
int t = cel.getCellType();
switch( t ) {
case HSSFCell.CELL_TYPE_BLANK:
unread = true;
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = String.valueOf( cel.getBooleanCellValue() );
break;
case HSSFCell.CELL_TYPE_ERROR:
value = String.valueOf( cel.getErrorCellValue() ); // byte? what this thing means?
break;
case HSSFCell.CELL_TYPE_FORMULA:
try {
value = String.valueOf( cel.getNumericCellValue() ); break; }
catch ( final Exception e ) { /*/ can't get as numeric ... /*/ }
try {
value = cel.getStringCellValue(); break; }
catch ( final Exception e ) { /*/ can't get as string ... /*/ }
value = cel.getCellFormula();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value = String.valueOf( cel.getNumericCellValue() );
break;
case HSSFCell.CELL_TYPE_STRING:
value = cel.getStringCellValue();
break;
default:
/*/ Unknow cell data. /*/
unread = true;
}
} catch( final Exception e ) { unread = true; }
// empty cells raises error, this is normal ...
if( bNORMAL && i < 4 && value.indexOf( '.' ) > -1 ) {
// must ignore points in id fields (if have one, sure);
String n[];
n = XHelper.parseKey( value, "." );
value = n[0];
}
if( unread )
ec ++;
else
value = value.trim().toUpperCase();
try {
switch( i ) {
case 1:
vo.setCdMachineType( value );
break; // can be empty //
case 2:
vo.setCdModel( value );
break;
case 3:
vo.setCdFeature( value );
break;
case 4:
vo.setTxDescription( value );
break;
case 5:
vo.setTxCategory( value );
break;
case 6:
vo.setVlAmtHedge ( new BigDecimal( value.equals( "" )? "0.00": value ) );
break;
case 7:
vo.setVlAmtRetail( new BigDecimal( value.equals( "" )? "0.00": value ) );
break;
case 8:
vo.setVlAmtWhlse ( new BigDecimal( value.equals( "" )? "0.00": value ) );
break;
default:
}
} catch ( final Exception e ) { /*/ some dummy error inside /*/ }
}
// if have less than 6 empty columns (of 8 columns);
if( ec < 6 ) {
// iterate in array dates to build factors in interval;
int l = d.length;
for( int i = 0; i<l ; i ++ ) {
String s = XHelper.formatDateToStringMMyyyy( d[i] );
String a[] = XHelper.parseKey( s, "-" );
PriceVO clone = (PriceVO)vo.clone();
clone.setTxMonth( a[0] );
clone.setTxYear ( a[1] );
col.add( clone );
}
}
er = ( ec < 6? 0: er + 1 );
hasData = (er < 3);
}
// to help gc, this objects can be heavy;
sheet = null;
wb = null;
fs = null;
fis.close();
fis = null;
}
catch ( final Exception e ) {
throw new CCException( e );
}
return col;
}
}[/code]