在java后臺(tái)使用Apache poi 實(shí)現(xiàn)生成一個(gè)excel文檔并提供前臺(tái)下載

function exportBatch() { 
    //get請(qǐng)求，可以傳遞參數(shù)，比方說(shuō)我這里就傳了一堆卷號(hào)，我只生成傳過(guò)去的這堆卷號(hào)的檢驗(yàn)記錄 
    //參數(shù)rollNumbers的細(xì)節(jié)就不展示了，業(yè)務(wù)相關(guān) 
    window.location.href = '../ir/exportSurface?rollNumberList=' + rollNumbers; 
  } 

<dependency> 
      <groupId>org.apache.poi</groupId> 
      <artifactId>poi</artifactId> 
      <version>3.14</version> 
    </dependency> 

/*** 
   * 批量導(dǎo)出表面質(zhì)量檢驗(yàn)記錄 
   * 
   * @return 
   * @throws Exception 
   */ 
  @RequestMapping(value = "exportSurface", method = RequestMethod.GET) 
  @ResponseBody 
  public void exportSurface(HttpServletRequest request, 
              HttpServletResponse response) throws Exception { 
    //參數(shù)獲取及處理，業(yè)務(wù)相關(guān)不展示 
    //把要填寫的數(shù)據(jù)放在一個(gè)map里 
    Map<String, Object> map = new HashMap<String, Object>(); 
    map.put("sequence", "0001");//mock編號(hào) 
    map.put("date", DateUtils.toDateStr(new Date(), DateUtils.DEFAULT_DATE_PATTERN_CHINESE)); 
    map.put("chetaihao", "1#");//mock車臺(tái)號(hào) 
    map.put("productName", "預(yù)應(yīng)力鋼絞線");//mock品名 
    map.put("specification", "規(guī)格");//mock規(guī)格 
    map.put("memo", "備注");//mock備注 
    map.put("inspectRecordBizList", inspectRecodeBizList); 
    ExcelUtils.exportInspectionRecordSurface(request, response, map); 
  } 

public static void exportInspectionRecordSurface(HttpServletRequest request, HttpServletResponse response, Map map) throws IOException { 
    //模板的路徑，這個(gè)在自己的項(xiàng)目中很容易弄錯(cuò)，相對(duì)位置一定要寫對(duì)啊 
   String psth = request.getRealPath("/") + INSPECTIONRECORD_SURFACE_TEMPLET_PATH; 
    Workbook webBook = readExcel(psth); 
    createCellStyle(webBook); 
    Sheet sheet = webBook.getSheetAt(0); 
   //開始操作模板，找到某行某列（某個(gè)cell），需要注意的是這里有個(gè)坑，行和列的計(jì)數(shù)都是從0開始的 
   //一次數(shù)據(jù)插入的位置不對(duì)，別灰心，多試幾次就好啦，你要是能看懂我下面的代碼，數(shù)據(jù)插在了什么位置，你就明白了 
    int rows = 1; 
    Row row = sheet.getRow(rows); 
    row.createCell(1).setCellValue((String) map.get("sequence")); 
    row.createCell(3).setCellValue((String) map.get("date")); 
    row.createCell(9).setCellValue((String) map.get("chetaihao")); 
    rows = 2; 
    row = sheet.getRow(rows); 
    row.createCell(1).setCellValue((String) map.get("productName")); 
    row.createCell(3).setCellValue((String) map.get("specification")); 
    row.createCell(9).setCellValue((String) map.get("memo")); 
   //檢驗(yàn)記錄的插入業(yè)務(wù)相關(guān)，不展示，其實(shí)就是for循環(huán)在合適的行合適的列插入一個(gè)個(gè)對(duì)象的屬性即可，你這么聰明，沒(méi)問(wèn)題的 
    writeExcel(response, webBook, "表面質(zhì)量檢驗(yàn)記錄"); 
  } 

//這里得有你自己的package名 
 
import org.apache.poi.hssf.usermodel.HSSFCellStyle; 
import org.apache.poi.hssf.usermodel.HSSFFont; 
import org.apache.poi.hssf.usermodel.HSSFRichTextString; 
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
import org.apache.poi.ss.usermodel.*; 
 
import javax.servlet.http.HttpServletRequest; 
import javax.servlet.http.HttpServletResponse; 
import java.io.*; 
import java.net.URLEncoder; 
import java.util.ArrayList; 
import java.util.List; 
import java.util.Map; 
 
/** 
 * Created by bwju on 2016/12/06. 
 */ 
public class ExcelUtils { 
  private static final String INSPECTIONRECORD_SURFACE_TEMPLET_PATH = "/asserts/templete/InspectionRecordSurface.xls"; 
  private static HSSFCellStyle cellstyle = null; 
 
  public static void exportInspectionRecordSurface(HttpServletRequest request, HttpServletResponse response, Map map) throws IOException { 
    //實(shí)現(xiàn)上文里有，改個(gè)函數(shù)名，寫你的操作模板函數(shù)吧！ 
  } 
 
  private static Workbook readExcel(String filePath) { 
    InputStream in = null; 
    Workbook work = null; 
    try { 
      in = new FileInputStream(filePath); 
      work = new HSSFWorkbook(in); 
    } catch (FileNotFoundException e) { 
      System.out.println("文件路徑錯(cuò)誤"); 
      e.printStackTrace(); 
    } catch (IOException e) { 
      System.out.println("文件輸入流錯(cuò)誤"); 
      e.printStackTrace(); 
    } 
    return work; 
  } 
 
  private static void writeExcel(HttpServletResponse response, Workbook work, String fileName) throws IOException { 
    OutputStream out = null; 
    try { 
      out = response.getOutputStream(); 
      response.setContentType("application/ms-excel;charset=UTF-8"); 
      response.setHeader("Content-Disposition", "attachment;filename=" 
          .concat(String.valueOf(URLEncoder.encode(fileName + ".xls", "UTF-8")))); 
      work.write(out); 
    } catch (IOException e) { 
      System.out.println("輸出流錯(cuò)誤"); 
      e.printStackTrace(); 
    } finally { 
      out.close(); 
    } 
  } 
 
  private static Cell setCellStyleWithStyleAndValue(CellStyle style, Cell cell, String value) { 
    cell.setCellStyle(style); 
    cell.setCellValue(value); 
    return cell; 
  } 
 
  private static Cell setCellStyleWithValue(Cell cell, String value) { 
    cell.setCellStyle(cellstyle); 
    cell.setCellValue(value); 
    return cell; 
  } 
 
 
  private static Cell setCellStyleWithStyleAndValue(CellStyle style, Cell cell, RichTextString value) { 
    cell.setCellStyle(style); 
    cell.setCellValue(value); 
    return cell; 
  } 
 
  private static Cell setCellStyleWithValue(Cell cell, int value) { 
    cell.setCellStyle(cellstyle); 
    cell.setCellValue(value); 
    return cell; 
  } 
 
  private static Cell setCellStyleWithValue(Cell cell, double value) { 
    cell.setCellStyle(cellstyle); 
    cell.setCellValue(value); 
    return cell; 
  } 
 
  private static HSSFCellStyle createCellStyle(Workbook wb) { 
    cellstyle = (HSSFCellStyle) wb.createCellStyle(); 
    cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
    cellstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
    cellstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
    cellstyle.setBorderRight(HSSFCellStyle.BORDER_THIN); 
    cellstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); 
    cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
    return cellstyle; 
  } 
}