// 获取模板
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);
}
}
}