工作上常需要將資料庫的資料組合後產生Excel,利用Apache POI所提供的Library,這項工作可以輕鬆達成,這個Example比較重要的物件為HSSFWorkbook(產生workbook物件)、HSSFSheet(產生Sheet物件)、HSSFROW以及HSSFCell(產生儲存格物件)比較重要的物件,一開始必須產生HSSFWorkbook物件,接著產生HSSFSheet物件及HSSFRow物件或是HSSFCell物件,有了workbook物件就可以createSheet,有了sheet物件後就可以createRow,有了row物件後就可以createCell,有了Cell物件後呼叫setCellValue()指定值,透過迴圈,就可以把資料庫相關欄位讀入workbook中sheet下的某一cell中,最後在寫到檔案中,即完成了
import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import dao.GetDBInfo;
import org.apache.poi.hssf.usermodel.*;
public class ExportExcel {
public void createFDDRToFDI() throws SQLException{
String sqlFFF="SELECT fddr_log.`No`,fddr_log.Rev, fddr_log.`Status`, fddr_log.CommitDate, fddr_log.ReviewDate, fddr_log.IssuedDate, fdi_log.`No`,fdi_log.Rev,fdi_log.`Status`, fdi_log.CommitDate,fdi_log.CompletedDate "+
"FROM fddr_log INNER JOIN ge_refdoc ON fddr_log.CID = ge_refdoc.CidNo LEFT OUTER JOIN fdi_log ON ge_refdoc.RefCidNo = fdi_log.CID WHERE fddr_log.`No` not like '%LT2%' and fddr_log.rev >=100 "+
"ORDER BY fddr_log.`No` DESC, fddr_log.Rev DESC, fdi_log.`No` DESC, fdi_log.Rev DESC";
GetDBInfo dao=new GetDBInfo();
ResultSet set=dao.getResultSet(sqlFFF);
int colCount= dao.getSetMetaData(set).getColumnCount();
HSSFWorkbook workbook= new HSSFWorkbook();
HSSFSheet sheet= workbook.createSheet("FDDRToFDI");
int rowNum=0;
HSSFRow header= sheet.createRow(rowNum);
for(int i=0; i<colCount; i++){
HSSFCell cell= header.createCell(i);
cell.setCellValue((String)dao.getSetMetaData(set).getColumnLabel(i+1));
}
while(set.next()){
rowNum++;
HSSFRow row= sheet.createRow(rowNum);
for (int i=0;i<colCount;i++){
HSSFCell cell=row.createCell(i);
cell.setCellValue(set.getString(i+1));
}
}
try{
FileOutputStream out = new FileOutputStream("C:\\Documents and Settings\\joombuopre\\桌面\\1.xls");
workbook.write(out);
out.flush();
out.close();
System.out.println("Success");
}catch(Exception e){
e.printStackTrace();
}
}
}