//這個(gè)用模板導(dǎo)出，省略了創(chuàng)建表頭的方法，可以把模板里面的全部復(fù)制下來(lái)，放到要導(dǎo)出的excel里面
//首先要記住，excel 第一行是從0開始的，列也是如此
//訪問方法
public String na(HttpServletResponse response,HttpServletRequest request){
????//查詢要到處的數(shù)據(jù)
List<Map<string,string>> dataSourceList= null;
try {
????????ExcelDownloadUtil.ExcelByModel("測(cè)試模板導(dǎo)出", modelURLString, dataSourceList, response, sheetNameStrings, keysStrings, 6);
} catch (Exception e) {
????????e.printStackTrace();
}
????return "SUCESS";
}
在模板中添加數(shù)據(jù)
public static void ExcelByModel(String ExcelName, String ModelURl, List<Map<String,String>> dataSource,HttpServletResponse response, String[] sheetNames, String[] keyNames, int rowNum) throws Exception {
// 設(shè)置導(dǎo)出Excel報(bào)表的導(dǎo)出形式
response.setContentType("application/vnd.ms-excel");
// 設(shè)置導(dǎo)出Excel報(bào)表的響應(yīng)文件名
String fileName = new String(ExcelName.getBytes("utf-8"), "ISO-8859-1");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");//導(dǎo)出的文件名稱
// 創(chuàng)建一個(gè)輸出流
OutputStream fileOut = response.getOutputStream();
// 讀取模板文件路徑
File file = new File(ModelURl);
FileInputStream fins = new FileInputStream(file);
POIFSFileSystem fs = new POIFSFileSystem(fins);
// 讀取Excel模板
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);//獲取第一頁(yè)sheet頁(yè)
sheet.autoSizeColumn(1);
HSSFRow rowCellStyle1 = sheet.getRow(2);//sheet頁(yè)的第二行
HSSFCellStyle columnOne01 = rowCellStyle1.getCell(0).getCellStyle();//獲取sheet頁(yè)第二行的樣式
// 設(shè)置邊框樣式（樣式自己選擇）
//? ? ? ? HSSFCellStyle style = wb.createCellStyle();
//? ? ? ? style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//? ? ? ? style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//? ? ? ? style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//? ? ? ? style.setBorderTop (HSSFCellStyle.BORDER_THIN);
// 設(shè)置邊框樣式的顏色
//? ? ? ? style.setBottomBorderColor(HSSFColor.BLACK.index);
//? ? ? ? style.setLeftBorderColor(HSSFColor.BLACK.index);
//? ? ? ? style.setRightBorderColor(HSSFColor.BLACK.index);
//? ? ? ? style.setTopBorderColor(HSSFColor.BLACK.index);
for (int j = 0; j <20; j++) {
HSSFRow row = sheet.getRow(j+3);// 創(chuàng)建第二行
HSSFCell cellHeard1 = row.getCell(1); ? ?//獲取模板的第2個(gè)單元格b
HSSFCell cellHeard2 = row.getCell(2);
HSSFCell cellHeard3 = row.getCell(3);
HSSFCell cellHeard4 = row.getCell(4);
HSSFCell cellHeard5 = row.getCell(5);
HSSFCell cellHeard6 = row.getCell(6);
HSSFCell cellHeard7 = row.getCell(7);
// 在該單元格內(nèi)輸入內(nèi)容
cellHeard1.setCellValue(j+1); //序號(hào)
cellHeard1.setCellStyle(columnOne01);//獲取模板單元格樣式
//單元格添加數(shù)據(jù)
cellHeard2.setCellValue("1");
cellHeard2.setCellStyle(columnOne01);
//單元格添加數(shù)據(jù)
cellHeard3.setCellValue("2");
cellHeard3.setCellStyle(columnOne01);
//單元格添加數(shù)據(jù)
cellHeard4.setCellValue(3);
cellHeard4.setCellStyle(columnOne01);
//單元格添加數(shù)據(jù)
cellHeard5.setCellValue(4);
cellHeard5.setCellStyle(columnOne01);
//單元格添加數(shù)據(jù)
cellHeard6.setCellValue(5);
cellHeard6.setCellStyle(columnOne01);
//單元格添加數(shù)據(jù)
cellHeard7.setCellValue(6);
cellHeard7.setCellStyle(columnOne01);
}
// 寫入流
wb.write(fileOut);
// 關(guān)閉流
fileOut.close();
}
