根據模板匯出到Excel(lp)

// 擷取模板
  String dir = servlet.getServletContext().getRealPath(
    "/template/test.xls");
  POIFSFileSystem fis = new POIFSFileSystem(new FileInputStream(dir));
  HSSFWorkbook wb = new HSSFWorkbook(fis);
  HSSFSheet sheet1 = wb.getSheetAt(0);

  // 設定儲存格值
      // ................
  
   // out to excel
  try {
   response.setContentType("application/ms-excel; charset=/"utf-8/"");
   response.setHeader("Content-disposition", "attachment;filename="
     + "testOut.xls");
   OutputStream out = response.getOutputStream();

   wb.write(out);
   out.close();

 } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  } 

附自用的excelutil

import java.util.Date;

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;

public class ExcelUtil {

//設定儲存格的值
 public static void setSheetCellValue(HSSFSheet sheet, int rowIndex,
   short colIndex, Object value) {
  HSSFCell cell = ExcelUtil.ensureCellExist(sheet, rowIndex, colIndex);
  if (value instanceof Date) {
   // date type
   cell.setCellValue((Date) value);
  } else if (value instanceof Number) {
   // numeric type: Double,Float,Integer and so on.
   cell.setCellValue(((Number) value).doubleValue());
  } else if (value instanceof String) {
   // String type
   
   // String encoding
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   //
   String strValue = (String) value;
   strValue = strValue.replaceAll("/r/n", "/n");
   cell.setCellValue(strValue);
  }
 }

//設定儲存格的規則 

public static void setSheetCellFormula(HSSFSheet sheet, int rowIndex,
   short colIndex, String formula) {
  HSSFCell cell = ExcelUtil.ensureCellExist(sheet, rowIndex, colIndex);
  cell.setCellFormula(formula);
 }

//設定儲存格的格式

 public static void setSheetCellStyle(HSSFSheet sheet, int rowIndex,
   short colIndex, HSSFCellStyle style) {
  HSSFCell cell = ExcelUtil.ensureCellExist(sheet, rowIndex, colIndex);
  cell.setCellStyle(style);
 }

//擷取儲存格的格式

 public static HSSFCellStyle getSheetCellStyle(HSSFSheet sheet,
   int rowIndex, short colIndex) {
  HSSFCell cell = ExcelUtil.ensureCellExist(sheet, rowIndex, colIndex);
  return cell.getCellStyle();
 }

//獲得一個儲存格，如果不存在，建立 

public static HSSFCell ensureCellExist(HSSFSheet sheet, int rowIndex,
   short colIndex) {
  HSSFRow row = sheet.getRow(rowIndex);
  if (row == null) {
   row = sheet.createRow(rowIndex);
  }
  HSSFCell cell = row.getCell(colIndex);
  if (cell == null) {
   cell = row.createCell(colIndex);
  }
  return cell;
 }

//將行rowIndex1的格式複製到行rowIndex2

 public static void copySheetRowStyle(HSSFSheet sheet, int rowIndex1,
   int rowIndex2) {
  // check paras
  if(rowIndex1 == rowIndex2){
   return;
  }
  
  HSSFRow row = sheet.getRow(rowIndex1);
  if (row == null) {
   row = sheet.createRow(rowIndex1);
  }
  short cols = row.getLastCellNum();
  HSSFCellStyle style = null;
  for (short colIndex = 0; colIndex < cols; colIndex++) {
   style = ExcelUtil.getSheetCellStyle(sheet, rowIndex1, colIndex);
   ExcelUtil.setSheetCellStyle(sheet, rowIndex2, colIndex, style);
  }
 }
}