JAVA POI 导出excel

浏览数：39 / 时间：2015年06月08日

poi 导出excel，以前用jxl，每次保存excel都需要先下载到服务器，在返回连接，提供用户保存，或则，先让用户输入保存的路径，在下载到指定路径。无法实现让浏览器弹出保存框，让用户选择保存路径。但POI可以。

java代码：

import java.io.IOException;
import java.util.Calendar;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.springside.modules.web.ServletUtils;
import org.springside.modules.web.struts2.Struts2Utils;
/**
* Jxl 的 Excel写数据器.
*/
public class ExcelManager {   
    /**
     * @param datas 封装着Object[]的列表, 一般是String内容.
     * @param title 每个sheet里的标题.
     */
    public void writeExcel(List<Object[]> rows, String[] title) {
        try {
            String filename = "";            
            // 创建工作簿实例
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建工作表实例
            HSSFSheet sheet = workbook.createSheet("sheet1");
            HSSFCellStyle style = this.createTitleStyle(workbook);
            int rowNum = 0;    //要写的行
            if(title != null) {
                putData(sheet, rowNum, title,null);//压入标题
                rowNum = 1;
            }
            
            if(rows !=null && rows.size()>0){
                for(int i=0;i<rows.size();i++,rowNum++){
                    putData(sheet,rowNum,rows.get(i),null);
                }
            }

            Calendar c = Calendar.getInstance();
            int year = c.get(Calendar.YEAR);
            int month = c.get(Calendar.MONTH) + 1;
            String month_ = new String("" + month);
            if (month < 10) {
                month_ = "0" + month;
            }
            int day = c.get(Calendar.DAY_OF_MONTH);
            String day_ = new String("" + day);
            if (day < 10) {
                day_ = "0" + day;
            }
            //设置文件名获取输入流
            filename = year + month_ + "" + day_ + ".xls";
            HttpServletResponse response = Struts2Utils.getResponse();
            response.setContentType(ServletUtils.EXCEL_TYPE);
            ServletUtils.setFileDownloadHeader(response, filename);
            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();
        } catch (WriteException e) {
            System.out.println("jxl write WriteException: "+e.getMessage());
        } catch (IOException e) {
            System.out.println("jxl write file i/o exception!, cause by: "+e.getMessage());
        }
    }

    private void putData(HSSFSheet ws, int rowNum, Object[] cells,HSSFCellStyle style) throws RowsExceededException, WriteException {
        HSSFRow row = ws.createRow((short) rowNum);// 建立新行
        for (int i = 0; i < cells.length; i++) {
            ws.autoSizeColumn(i);
            this.createCell(row, i, style, HSSFCell.CELL_TYPE_STRING,(cells[i]));
        }        
    }
       
    private void createCell(HSSFRow row, int column, HSSFCellStyle style,
            int cellType, Object value) {
        HSSFCell cell = row.createCell((short) column);
        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        if (style != null) {
            cell.setCellStyle(style);
        }
        switch (cellType) {
        case HSSFCell.CELL_TYPE_BLANK: {
        }
            break;
        case HSSFCell.CELL_TYPE_STRING: {
            if (value != null)
                cell.setCellValue(value.toString() + "");
        }
            break;
        case HSSFCell.CELL_TYPE_NUMERIC: {
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(Double.parseDouble(value.toString()));
        }
            break;
        default:
            break;
        }

    }
 // 设置excel的样式
     private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
         HSSFFont boldFont = wb.createFont();
         boldFont.setFontHeightInPoints((short) 12);
         HSSFCellStyle style = wb.createCellStyle();
         style.setFont(boldFont);
         style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
         return style;
     }
    
}