EXCEL POI XSSFWorkbook SXSSFWorkbook
[摘要] 配合EXCEL檔 讀寫檔
必須設定report.properties
必須有template 存放在WEB/INF下的excel 檔
必須與service 做資料連結
必須有reportUtility, reportbase
public class Report123Streaming extends ReportBase<Workbook> {
public void editReport(Workbook wb) throws Exception{
/**** 1. High level representation of a SpreadsheetML workbook.
This is the first object most users will construct whether they are reading or writing a workbook.
It is also the top level object for creating new sheets/etc.
2. 為什麼轉型兩次? 通過XSSFWorkbook來讀取excel file模板，然後用ㄐ來設置Streaming樣式和讀寫數據
3. 為什麼一般使用new ?? 因為是動態產生excel ,而不是像本例有template的excel來搭配
XSSFWorkbook workbook = new XSSFWorkbook(is);
XSSFSheet sheet = workbook.getSheetAt(0);
****/
XSSFWorkbook xwb = ((SXSSFWorkbook)wb).getXSSFWorkbook();
/****the first sheet for excel
XSSFWorkbook来"讀取" 原本就存在的excel檔案，所以寫好再excel裡面的格式要從這裡取得
****/
XSSFSheet xsheet = xwb.getSheetAt(0);
XSSFRow xrow = null;
/**** the first sheet for excel
SXSSFWorkbook來設置Streaming樣式和讀"寫入"數據
****/
Sheet sheet = wb.getSheetAt(0);
Row row = null;
int columnIndex = 0;
int rowIndex = 2;
// for pic
Drawing patriarch = sheet.createDrawingPatriarch();
short rowHeight = 1000;
// for saving efficiency
if(i == 0){
row = xsheet.getRow(rowIndex);
}else{
row = sheet.createRow(rowIndex);
xrow = xsheet.getRow(2);
//看上一列每一格的格式，然後複製到下一列去
for(int j = 0 ; j < 28 ; j++){
//利用XSSFWorkbook 把檔案的格式讀取出來
CellStyle cellstyle = xrow.getCell(j).getCellStyle();
row.createCell(j).setCellStyle(cellStyle);
}
}
//the below is fill out the data
row.getCell(columnIndex++).setCellValue(member.getmName());
//使用三元表示式
row.getCell(columnIndex++).setCellValue(Product == null ?"" :product.getPrice());
// 使用if else for pic
if(product != null) {
FileUpload fUpload = getUploadService().findLastImgAttachment
(product.getpId(), null,
Fileupload.FILE_IMAGE,FileUpload.Page_FALG_CUSTOMER_SHIP);
if( fileupload != null && fileupload.getFilePath()
!= null && fileupload.getFileName()!= null){
imagePath = StaticObject.WEB_PATH
+ fileupload.getFilePath() + "/" + fileupload.getFileName();
File imageFile = new File(imagePath);
if(imageFile.exists()){
String destPath = ReportUtility.extractPhoto(imagePath , "10");
row.setHeight(rowHeight);
ReportUtility.loadImage(wb, patriarh, destPath,
(short)columnIndex,4+i, (short)(columnIndex+1),5+i);
}
}
columnIndex++;
}else{
columnIndex++;
}
摘要:[JAVA] Excel POI 動態讀檔寫檔(XSSFWorkbook)
在java內可以透過org.apache.poi API 實現讀檔與寫檔的動作
目前常見的是用HSSFWorkbook ...etc
但所轉出的為Excel 97-2003 版的Excel
Excel 2007-10的版本為XSSFWorkbook
這邊假設data 已經轉成inputStream
整個excel的包裝是workbook 為最外層
裡面包含多格sheet ，sheet中包含很多列row
row 則由多個cell組成。
XSSFWbook=>XSSFSheet=>XSSFRow=>XSSFCell
從Excel讀取到後端處理
XSSFWorkbook workbook = new XSSFWorkbook(is);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row;
/* 第一列為title 所以i從1開始略過0 */
for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i); // 取得第 i Row
Certification cert = new Certification();
if (row.getCell(0) != null) {
cert.setName(row.getCell(Constants.ONE).toString().trim());
cert.setIdNo(row.getCell(Constants.TWO).toString().trim());
}
}
從後端資料寫成Excel
輸出為ByteArrayOutputStream
XSSFWorkbook workbook = new XSSFWorkbook(); // 建立Excel物件
ByteArrayOutputStream bos = new ByteArrayOutputStream();
//字體格式
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index); // 顏色
font.setBoldweight(Font.BOLDWEIGHT_NORMAL); // 粗細體
// 設定儲存格格式
XSSFCellStyle styleRow1 = workbook.createCellStyle();
// styleRow1.setFillForegroundColor(HSSFColor.GREEN.index);//填滿顏色
// styleRow1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleRow1.setFont(font); // 設定字體
styleRow1.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平置中
styleRow1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直置中
// 設定框線
styleRow1.setBorderBottom((short) 1);
styleRow1.setBorderTop((short) 1);
styleRow1.setBorderLeft((short) 1);
styleRow1.setBorderRight((short) 1);
styleRow1.setWrapText(true); // 自動換行
/* Title */
XSSFSheet sheet = workbook.createSheet("檢定名冊");
sheet.autoSizeColumn(0); // 自動調整欄位寬度
sheet.setColumnWidth(0, CHAR_SIZE * Constants.TEN);
sheet.setColumnWidth(Constants.ONE, CHAR_SIZE * Constants.TEN);
sheet.setColumnWidth(Constants.TWO, CHAR_SIZE * Constants.FIFTEEN);
XSSFRow rowTitle = sheet.createRow(0);
rowTitle.createCell(0).setCellValue("編號");
rowTitle.createCell(1).setCellValue("姓名");
rowTitle.createCell(2).setCellValue("身分證字號");
for (int i = 0; i < examineeList.size(); i++) {
XSSFRow rowContent = sheet.createRow(i + 1); // 建立儲存格
Calendar cal = Calendar.getInstance();
cal.setTime(examineeList.get(i).getBirthday());
XSSFCell cellContent = rowContent.createCell(0);
cellContent.setCellValue(examineeList.get(i).getGrantNo());
cellContent = rowContent.createCell(1);
cellContent.setCellValue(examineeList.get(i).getName());
cellContent = rowContent.createCell(2);
}
workbook.write(bos);
return bos;
https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html
引用 eric's 學習日記