Thứ Năm, 26 tháng 9, 2013

Export Excel, PDF trong SpringMVC

Xuất file excel ta dùng thư viện apache poi: http://poi.apache.org/Xuất file pdf ta dùng thư viện itextpdf: http://itextpdf.com/

Ví dụ này ta sẽ xuất ra dưới dạng bảng thống kê, tức là có title, có các header cho các column và data cho bảng đó, như vậy, ta sẽ xây dựng 2 lớp builder nhận vào các đối là
Title: 1 chuỗi String
header: 1 Vector
data: 1 Vector
Đây là lớp ExcelBuilder để export ra file excel:

package vn.diepviends.util;

import java.util.Map;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.springframework.web.servlet.view.document.AbstractExcelView;

public class ExcelBuilder extends AbstractExcelView {
 @Override
 protected void buildExcelDocument(Map model,
   HSSFWorkbook workbook, HttpServletRequest request,
   HttpServletResponse response) throws Exception {
  // get data
  Vector vHeader = (Vector) model.get("header");
  Vector vData = (Vector) model.get("data");
  String sTitle = model.get("title").toString();
  // create a excel sheet
  HSSFSheet sheet = workbook.createSheet(sTitle);
  sheet.setDefaultColumnWidth(30);
  // create style for header cells
  CellStyle style = workbook.createCellStyle();
  Font font = workbook.createFont();
  font.setFontName("Times New Roman");
  style.setFillForegroundColor(HSSFColor.BLUE.index);
  style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  font.setColor(HSSFColor.WHITE.index);
  style.setFont(font);
  // create header row
  HSSFRow title = sheet.createRow(0);
  title.createCell(0).setCellValue(sTitle);
  HSSFRow header = sheet.createRow(1);  
  for (int i = 0; i < vHeader.size(); i++) {
   header.createCell(i).setCellValue(vHeader.get(i).toString());
   header.getCell(i).setCellStyle(style);
  }
  // fill data
  int r = 2;
  for (int i = 0; i < vData.size(); i++) {
   Vector v = (Vector) vData.get(i);
   HSSFRow row = sheet.createRow(r++);
   for (int j = 0; j < v.size(); j++)
    row.createCell(j).setCellValue(v.get(j).toString());
  }
 }
}

Controller ta sẽ viết như sau:
@RequestMapping("/registerReport/export2excel")
 public String exportExcel(@RequestParam(value = "from") String from,
   @RequestParam(value = "to") String to, Model model) {
  ProvisionReportDAO dao = new ProvisionReportDAO();
  String title = "Register Report";
  Vector header = new Vector();
  Vector data = new Vector();
  ArrayList list = dao.getByDate(from, to);  
  header.add("Date");
  header.add("Quantity");
  header.add("Provision Type");
  header.add("provision Code");
  for(ProvisionReport it : list){
   Vector v = new Vector();
   v.add(it.getDate());
   v.add(it.getQuantity());
   v.add(it.getType());
   v.add(it.getCode());
   data.add(v);
  }  
  model.addAttribute("title", title);
  model.addAttribute("header", header);  
  model.addAttribute("data",data); 
  return "excelView";
 }

Với PDF thì ta phải xây dựng một lớp Abstract trung gian:
package vn.diepviends.util;

import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.view.AbstractView;

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.pdf.PdfWriter;

public abstract class AbstractITextPdfView extends AbstractView {

 public AbstractITextPdfView() {
  setContentType("application/pdf");
 }

 @Override
 protected boolean generatesDownloadContent() {
  return true;
 }
  
 @Override
 protected void renderMergedOutputModel(Map model,
   HttpServletRequest request, HttpServletResponse response) throws Exception {
  // IE workaround: write into byte array first.
  ByteArrayOutputStream baos = createTemporaryOutputStream();

  // Apply preferences and build metadata.
  Document document = newDocument();
  PdfWriter writer = newWriter(document, baos);
  prepareWriter(model, writer, request);
  buildPdfMetadata(model, document, request);

  // Build PDF document.
  document.open();
  buildPdfDocument(model, document, writer, request, response);
  document.close();

  // Flush to HTTP response.
  writeToResponse(response, baos);
 }

 protected Document newDocument() {
  return new Document(PageSize.A4);
 }
 
 protected PdfWriter newWriter(Document document, OutputStream os) throws DocumentException {
  return PdfWriter.getInstance(document, os);
 }
 
 protected void prepareWriter(Map model, PdfWriter writer, HttpServletRequest request)
   throws DocumentException {

  writer.setViewerPreferences(getViewerPreferences());
 }
 
 protected int getViewerPreferences() {
  return PdfWriter.ALLOW_PRINTING | PdfWriter.PageLayoutSinglePage;
 }
 
 protected void buildPdfMetadata(Map model, Document document, HttpServletRequest request) {
 }
 
 protected abstract void buildPdfDocument(Map model, Document document, PdfWriter writer,
   HttpServletRequest request, HttpServletResponse response) throws Exception; 
}

Lớp PDFBuilder extends lớp Abstract trên:
package vn.diepviends.util;

import java.util.Map;
import java.util.Vector;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.Font;
import com.itextpdf.text.FontFactory;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;

public class PDFBuilder extends AbstractITextPdfView {

 @Override
 protected void buildPdfDocument(Map model, Document doc,
   PdfWriter writer, HttpServletRequest request,
   HttpServletResponse response) throws Exception {
  // get data
  Vector vHeader = (Vector) model.get("header");
  Vector vData = (Vector) model.get("data");
  String sTitle = model.get("title").toString();
  
  doc.add(new Paragraph(sTitle));
  PdfPTable table = new PdfPTable(vHeader.size());
  table.setWidthPercentage(100.0f); 
  table.setSpacingBefore(10);
  // define font for table header row
  Font font = FontFactory.getFont(FontFactory.HELVETICA);
  font.setColor(BaseColor.WHITE);

  // define table header cell
  PdfPCell cell = new PdfPCell();
  cell.setBackgroundColor(BaseColor.BLUE);
  cell.setPadding(5);
  for (int i = 0; i < vHeader.size(); i++) {
   // write table header
   cell.setPhrase(new Phrase(vHeader.get(i).toString(), font));
   table.addCell(cell);
  }  
  // write table row data
  int x = vData.size();
  int y = vHeader.size();
  for(int i = 0; i


Controller ta sẽ viết như sau:
@RequestMapping("/registerReport/export2pdf")
 public String exportPdf(@RequestParam(value = "from") String from,
   @RequestParam(value = "to") String to, Model model) {
  ProvisionReportDAO dao = new ProvisionReportDAO();
  String title = "Register Report";
  Vector header = new Vector();
  Vector data = new Vector();
  ArrayList list = dao.getByDate(from, to);  
  header.add("Date");
  header.add("Quantity");
  header.add("Provision Type");
  header.add("provision Code");
  for(ProvisionReport it : list){
   Vector v = new Vector();
   v.add(it.getDate());
   v.add(it.getQuantity());
   v.add(it.getType());
   v.add(it.getCode());
   data.add(v);
  }  
  model.addAttribute("title", title);
  model.addAttribute("header", header);  
  model.addAttribute("data",data); 
  return "pdfView";
 }

Định nghĩa các view để hiển thị ra excel và pdf

     
    
     


Trong dispatcher của spring ta gọi view.xml vào:

        
 
 
  
  
         
        
   
   
    



và đây là kết quả:



1 nhận xét: